JDBC访问数据库

 1、设计数据库表

先设计一个数据库表，用于保存用户信息，建表语句如下：

[sql] view plain copy

1. CREATE TABLE  
2.      tbl_user_info  
3.     (  
4.         id INT NOT NULL AUTO_INCREMENT,  
5.         user_name VARCHAR(20) NOT NULL,  
6.         age INT NOT NULL,  
7.         sex INT(1) NOT NULL,  
8.         create_dt DATE NOT NULL,  
9.         PRIMARY KEY (id)  
10.     )  
11.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  

在用户表中定义了几个字段，分别是id，user_name，age，sex，create_dt，其中id是主键，是自增长的，user_name表示用户名，age表示用户年龄，sex表示用户的性别，这里的性别用数字表示，0表示女性，1表示男性，create_dt表示创建的时间。预先在数据库中插入几条数据，数据如下。

代码：

CREATE TABLE  

  tbl_user_info  

  (  

      id INT NOT NULL  ,  

      user_name VARCHAR(20) NOT NULL,  

      age INT NOT NULL,  

        sex INT  NOT NULL,  

     create_dt DATE NOT NULL,  

      PRIMARY KEY (id)  

     )  ;

    insert into tbl_user_info  values('4','Mary','25','0','2016-06-24');

 insert into tbl_user_info  values('5','Jack','22','1','2016-06-24');

 insert into tbl_user_info  values('6','John','19','1','2016-06-24')

截图：

2、定义实体类

定义一个Bean，与数据库表中的各个字段对应：

[java] view plain copy

1. package com.imooc.jdbc;  
2.   
3. import java.util.Date;  
4.   
5. public class UserVO {  
6.     private int id;  
7.     private String userName;  
8.     private int age;  
9.     private int sex;  
10.     private Date createDt;  
11.   
12.     public int getId() {  
13.         return id;  
14.     }  
15.   
16.     public void setId(int id) {  
17.         this.id = id;  
18.     }  
19.   
20.     public String getUserName() {  
21.         return userName;  
22.     }  
23.   
24.     public void setUserName(String userName) {  
25.         this.userName = userName;  
26.     }  
27.   
28.     public int getAge() {  
29.         return age;  
30.     }  
31.   
32.     public void setAge(int age) {  
33.         this.age = age;  
34.     }  
35.   
36.     public int getSex() {  
37.         return sex;  
38.     }  
39.   
40.     public void setSex(int sex) {  
41.         this.sex = sex;  
42.     }  
43.   
44.     public Date getCreateDt() {  
45.         return createDt;  
46.     }  
47.   
48.     public void setCreateDt(Date createDt) {  
49.         this.createDt = createDt;  
50.     }  
51.   
52.     @Override  
53.     public String toString() {  
54.         return "UserVO [id=" + id + ", userName=" + userName + ", age=" + age  
55.                 + ", sex=" + sex + ", createDt=" + createDt + "]";  
56.     }  
57.   
58. }  

  代码：

   import java.util.Date;

public class userVo {

    public int id;

    private String user_name;

    private int age;

    private int sex;

    private Date create_dt;

    public int getId(){

     return id;

    }

    public void setId(int id){

     this.id=id;

    }

    public String getUserName(){

     return user_name;

    }

    public void setUserName(String userName){

     this.user_name=userName;

    }

    public int getAge(){

     return age;

    }

    public void setAge(int age){

     this.age=age;

    }

    public int getSex(){

     return sex;

    }

    public void setSex(int sex){

     this.sex=sex;

    }

    public Date getCreateDt(){

     return create_dt;

    }

    public void setCreateDt(Date createDt){

     this.create_dt=createDt;

    }

public String toString(){

return "userVo[id="+id+",userName="+user_name+",age="+age+",sex="+sex+",createDt="+create_dt+"]";

}

}

3、定义数据库连接类

定义一个数据库连接类，用于获取MySQL的连接

[java] view plain copy

1. package com.imooc.jdbc;  
2.   
3. import java.sql.Connection;  
4. import java.sql.DriverManager;  
5.   
6. public class DBUtil {  
7.   
8.     private static final String URI = "jdbc:mysql://192.168.2.207:3306/jdbc_demo?"  
9.             + "user=root&password=root&useUnicode=true&characterEncoding=UTF-8";  
10.       
11.     private static final String DRIVER = "com.mysql.jdbc.Driver";  
12.       
13.     public static Connection connectDB() throws Exception {  
14.         //1、加载数据库驱动  
15.         Class.forName(DRIVER);  
16.         //2、获取数据库连接  
17.         Connection conn = DriverManager.getConnection(URI);  
18.           
19.         return conn;  
20.     }  
21.       
22. }  

 MySQL的JDBC URL编写方式为：jdbc:mysql://主机名称：连接端口/数据库的名称?参数=值，在这个例子中我连接的数据库主机是一台远程主机，所以主机名称为远程主机的ip地址，如果数据库主机为本机，则可以定义为localhost，在参数中指定用户名为root，密码也是root，为了避免中文乱码要指定useUnicode和characterEncoding。因为连接的是MySQL数据库，所以程序一开始需要加载MySQL的数据库驱动，然后通过DriverManager.getConnection(String URL)方法获取数据库的连接。

代码：

package ch;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class DBUtil {

public static void main(String[] args) {

   System.out.println("数据库的内容");

       Connection conn=null;

       Statement stmt=null;

       try{

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";

        String user="sa";

        String password="123456";

        conn=DriverManager.getConnection(url, user, password);

        stmt=conn.createStatement();

        String sql="Select* from tbl_user_info";

        ResultSet rs=stmt.executeQuery(sql);

        while(rs.next())

        {

        String name=rs.getString("user_name");

        System.out.println(name);

        }}catch(Exception e){

      System.out.println(e.toString());  

        }

        finally{

        if(stmt!=null){

        try{

        stmt.close();

        }catch(Exception e){}

        }

        if(conn!=null){

        try{

        conn.close();

        }catch(Exception e){}

        }

        }

        System.out.println("End");

}

}

截图：

4、实现数据库的增删改查

在获取了数据库的连接之后，就可以操作数据库了，下面分别实现数据库的增删改查操作，定义一个UserDao类用于操作数据库。

1）查询

先看查询操作，查询可以一次查询出所有的数据，也可以根据相应的条件查询。

查询所有的数据，在UserDao中定义一个queryAll()方法：

[java] view plain copy

1. public List<UserVO> queryAll() throws Exception {  
2.     Connection conn = DBUtil.connectDB();  
3.     String sql = "SELECT * FROM tbl_user_info";  
4.     List<UserVO> userList = new ArrayList<UserVO>();  
5.       
6.     Statement stmt = conn.createStatement();  
7.     ResultSet rs = stmt.executeQuery(sql);  
8.     while(rs.next()) {  
9.         UserVO user = new UserVO();  
10.         user.setId(rs.getInt("id"));  
11.         user.setUserName(rs.getString("user_name"));  
12.         user.setAge(rs.getInt("age"));  
13.         user.setSex(rs.getInt("sex"));  
14.         user.setCreateDt(rs.getDate("create_dt"));  
15.           
16.         userList.add(user);  
17.     }  
18.       
19.     return userList;  
20. }  

这里使用Connection.createStatement()方法获取一个Statement对象，这个对象里面有很多的方法可以操作数据库，使用excuteQuery(String sql)执行查询操作，查询结果为一个结果集ResultSet，可以通过这个结果集获取相关的信息。

定义main函数：

[java] view plain copy

1. public static void main(String[] args) {  
2.     UserDao dao = new UserDao();  
3.       
4.     try {  
5.         List<UserVO> userList = dao.queryAll();  
6.         for(UserVO user : userList) {  
7.             System.out.println(user);  
8.         }  
9.     } catch (Exception e) {  
10.         e.printStackTrace();  
11.     }  
12. }  

执行结果：

UserVO [id=4, userName=Mary, age=25, sex=0, createDt=2016-06-24]
UserVO [id=5, userName=Jack, age=22, sex=1, createDt=2016-06-24]
UserVO [id=6, userName=John, age=19, sex=1, createDt=2016-06-24]

  

 代码：

  package ch;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.sql.ResultSet;

import java.sql.Statement;

import ch.userVo;

public class User {

    public static List<userVo>queryAll()throws SQLException,ClassNotFoundException{

        String sql="SELECT *FROM tbl_user_info";

        List<userVo>userList=new ArrayList<userVo>();

        String url="jdbc:sqlserver://localhost:1433; DatabaseName=student";

        String user="sa";

        String password="123456";

        Connection conn=DriverManager.getConnection(url, user, password);

Statement stmt=conn.createStatement();

ResultSet rs=stmt.executeQuery(sql);

        while(rs.next()){

         userVo user1=new userVo();

         user1.setId(rs.getInt("id"));

         user1.setUserName(rs.getString("user_name"));

         user1.setAge(rs.getInt("age"));

         user1.setSex(rs.getInt("sex"));

         user1.setCreateDt(rs.getDate("create_dt"));

         userList.add(user1);

        }

        return userList;

    }

    public static void main(String[] args){

     User u=new User();

     try{

     List<userVo>userList=u.queryAll();

     for(userVo user:userList){

     System.out.println(user);

     }

     }catch(Exception e){

     e.printStackTrace();

     }

    }

}

截图：

根据条件查询，定义一个queryByParams方法：

[java] view plain copy

1. public List<UserVO> queryByParams(List<Map<String, Object>> params) throws Exception {  
2.     Connection conn = DBUtil.connectDB();  
3.     StringBuilder sql = new StringBuilder("SELECT * FROM tbl_user_info WHERE 1=1 ");  
4.       
5.     for(Map<String, Object> param : params) {  
6.         sql.append(" and ");  
7.         sql.append(" " + param.get("col") + " ");  
8.         sql.append(" " + param.get("rel") + " ");  
9.         sql.append(" " + param.get("value") + " ");  
10.     }  
11.     System.out.println(sql.toString());  
12.       
13.     List<UserVO> userList = new ArrayList<UserVO>();  
14.       
15.     Statement stmt = conn.createStatement();  
16.     ResultSet rs = stmt.executeQuery(sql.toString());  
17.     while(rs.next()) {  
18.         UserVO user = new UserVO();  
19.         user.setId(rs.getInt("id"));  
20.         user.setUserName(rs.getString("user_name"));  
21.         user.setAge(rs.getInt("age"));  
22.         user.setSex(rs.getInt("sex"));  
23.         user.setCreateDt(rs.getDate("create_dt"));  
24.           
25.         userList.add(user);  
26.     }  
27.       
28.     return userList;  
29. }  

这个方法可以自由选择查询的条件，只需要向方法中传入一个条件的List即可，这些条件都是由Map组成的，每一个Map包含三个元素，col表示查询条件对应哪一列，rel表示查询条件的关系是什么，value是指查询条件的值。这样写集成了多查询条件的方法，很多的业务下，查询的逻辑可能很多，这样写只用一个统一的方法就可以解决多种不同查询条件的业务逻辑。

再写一个简单的main函数测试一下：

[java] view plain copy

1. public static void main(String[] args) {  
2.     UserDao dao = new UserDao();  
3.       
4.     List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();  
5.     Map<String, Object> param1 = new HashMap<String, Object>();  
6.     param1.put("col", "user_name");  
7.     param1.put("rel", "like");  
8.     param1.put("value", "'%John%'");  
9.     params.add(param1);  
10.       
11.     Map<String, Object> param2 = new HashMap<String, Object>();  
12.     param2.put("col", "sex");  
13.     param2.put("rel", "=");  
14.     param2.put("value", 1);  
15.     params.add(param2);  
16.     try {  
17.         List<UserVO> userList = dao.queryByParams(params);  
18.         for(UserVO user : userList) {  
19.             System.out.println(user);  
20.         }  
21.     } catch (Exception e) {  
22.         e.printStackTrace();  
23.     }  
24.       
25. }  

在这个main方法中设定了两个查询条件，一是user_name like %John%，另一个是sex=1，当然条件也可以是其他的，执行程序运行结果为：

SELECT * FROM tbl_user_info WHERE 1=1  and  user_name  like  '%John%'  and  sex  =  1 
UserVO [id=6, userName=John, age=19, sex=1, createDt=2016-06-24]

代码：

package ch;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

public class User2 {

    public List<userVo>queryByParams(List<Map<String,Object>>params)throws Exception{

     StringBuilder sql=new StringBuilder("SELECT * FROM tbl_user_info where 1=1");

     String url="jdbc:sqlserver://localhost:1433; DatabaseName=student";

     String user="sa";

     String password="123456";

     for(Map<String,Object>param:params){

     sql.append("and");

     sql.append(" "+param.get("col")+" ");

     sql.append(" "+param.get("rel")+" ");

     sql.append(" "+param.get("value")+" ");

     }

     System.out.println(sql.toString());

     List<userVo>userList=new ArrayList<userVo>();

     Connection conn=DriverManager.getConnection(url, user, password);

     Statement stmt=conn.createStatement();

     ResultSet rs=stmt.executeQuery(sql.toString());

     while(rs.next()){

     userVo user1=new userVo();

     user1.setId(rs.getInt("id"));

         user1.setUserName(rs.getString("user_name"));

         user1.setAge(rs.getInt("age"));

         user1.setSex(rs.getInt("sex"));

         user1.setCreateDt(rs.getDate("create_dt"));

         userList.add(user1);

     }

     return userList;

    }

public static void main(String[] args) {

User2 u2=new User2();

List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();

Map<String,Object>param1=new HashMap<String,Object>();

param1.put("col","user_name");

param1.put("rel", "like");

param1.put("value", “’%John%’”);

params.add(param1);

Map<String,Object>param2=new HashMap<String,Object>();

param2.put("col","sex");

param2.put("rel", "=");

param2.put("value", 1);

params.add(param2);

try{

List<userVo>userList=u2.queryByParams(params);

for(userVo user:userList){

System.out.println(user);

}

}catch(Exception e){

e.printStackTrace();

}

 

}

 

}

截图：

2）增加

现在在UserDao中写一个addUser方法用于新增一条信息：

[java] view plain copy

1. public void addUser(UserVO user) throws Exception {  
2.     Connection conn = DBUtil.connectDB();  
3.     String sql = "INSERT INTO tbl_user_info(user_name, age, sex, create_dt) "  
4.             + " VALUES(?, ?, ?, ?)";  
5.       
6.     PreparedStatement pstmt = conn.prepareStatement(sql);  
7.     pstmt.setString(1, user.getUserName());  
8.     pstmt.setInt(2, user.getAge());  
9.     pstmt.setInt(3, user.getSex());  
10.     pstmt.setDate(4, new Date(new java.util.Date().getTime()));  
11.       
12.     pstmt.execute();  
13. }  

这个方法使用Connection.prepareStatement(String sql)方法获取一个PreparedStatement对象，使用这个方法可以传入带参数的SQL语句，而参数的值可以通过PreparedStatement.setXXX(int index, XXX value)的方法指定，其中XXX为各种不同的类型，index指定第几个参数的下标。指定了参数的值之后，便可以执行excute()方法执行SQL语句了。
接下来写一个main方法来验证这个增加的方法：

[java] view plain copy

1. public static void main(String[] args) {  
2.     UserDao dao = new UserDao();  
3.     UserVO user = new UserVO();  
4.       
5.     user.setUserName("Tom");  
6.     user.setAge(20);  
7.     user.setSex(1);  
8.     try {  
9.         dao.addUser(user);  
10.     } catch (Exception e) {  
11.         e.printStackTrace();  
12.     }  
13. }  

执行后再查看数据库，发现Tom这个用户已经插入成功了。

代码：

package ch;

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

 

public class User3 {

    public void addUser(userVo users,Connection conn)throws Exception

    {

     String sql="Insert into tbl_user_info(id,user_name,age,sex,create_dt)"+"values(?,?,?,?,?)";

     PreparedStatement pstmt=conn.prepareStatement(sql);

     pstmt.setInt(1,users.getId());

     pstmt.setString(2, users.getUserName());

     pstmt.setInt(3, users.getAge());

     pstmt.setInt(4, users.getSex());

     pstmt.setDate(5, new Date(new java.util.Date().getTime()));

     pstmt.execute();

    }

public static void main(String[] args) {

User3 u3=new User3();

userVo users=new userVo();

users.setId(7);

users.setUserName("Tom");

users.setAge(20);

users.setSex(1);

try{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String url="jdbc:sqlserver://localhost:1433; DatabaseName=student";

String user="sa";

String password="123456";

 Connection conn= DriverManager.getConnection(url,user,password);

     Statement stmt=conn.createStatement();

     String sql = "select * from tbl_user_info";

     ResultSet rs = stmt.executeQuery(sql);

     u3.addUser(users,conn);

 }catch(Exception ex){

  ex.printStackTrace();  

 }

System.out.println("End");

}

 

}

 

截图：

3）删除

接下来再写一个删除的方法，根据用户的id来删除数据：

[java] view plain copy

1. public void deleteUser(int id) throws Exception {  
2.     Connection conn = DBUtil.connectDB();  
3.     String sql = "DELETE FROM tbl_user_info WHERE id = ?";  
4.       
5.     PreparedStatement pstmt = conn.prepareStatement(sql);  
6.     pstmt.setInt(1, id);  
7.       
8.     pstmt.execute();  
9. }  

然后写一个main方法来验证：

[java] view plain copy

1. public static void main(String[] args) {  
2.     UserDao dao = new UserDao();  
3.       
4.     try {  
5.         dao.deleteUser(7);  
6.     } catch (Exception e) {  
7.         e.printStackTrace();  
8.     }  
9. }  

删除id为7的用户，也就是刚刚用新增方法创建的用户名为Tom的这个用户，运行后查看数据库：

 

代码：

package ch;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

 

public class User4 {

    public void deleteUser(int id,Connection conn)throws Exception

    {

     String sql="delete from tbl_user_info where id=?";

     PreparedStatement pstmt=conn.prepareStatement(sql);

     pstmt.setInt(1, id);

     pstmt.execute();

    }

public static void main(String[] args) {

User4 u4=new User4();

try{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String url="jdbc:sqlserver://localhost:1433; DatabaseName=student";

String user="sa";

String password="123456";

 Connection conn= DriverManager.getConnection(url,user,password);

     Statement stmt=conn.createStatement();

     String sql = "select * from tbl_user_info";

     ResultSet rs = stmt.executeQuery(sql);

     u4.deleteUser(7, conn);

}catch(Exception e){

e.printStackTrace();

}

System.out.println("End");

}

 

}

 

截图：

4）更新数据库

最后来看一下更新数据库：

[java] view plain copy

1. public void updateUser(UserVO user) throws Exception {  
2.     Connection conn = DBUtil.connectDB();  
3.     String sql = "UPDATE tbl_user_info SET user_name=?, age=?, sex=?"  
4.             + " WHERE id=?";  
5.       
6.     PreparedStatement pstmt = conn.prepareStatement(sql);  
7.     pstmt.setString(1, user.getUserName());  
8.     pstmt.setInt(2, user.getAge());  
9.     pstmt.setInt(3, user.getSex());  
10.     pstmt.setInt(4, user.getId());  
11.       
12.     pstmt.executeUpdate();  
13. }  

从SQL语句中可以看出更新也是根据用户的id进行选择性的更新的。

写一个main方法来验证：

[java] view plain copy

1. public static void main(String[] args) {  
2.     UserDao dao = new UserDao();  
3.     UserVO user = new UserVO();  
4.       
5.     user.setUserName("Mary");  
6.     user.setAge(30);  
7.     user.setSex(0);  
8.     user.setId(4);  
9.       
10.     try {  
11.         dao.updateUser(user);  
12.     } catch (Exception e) {  
13.         e.printStackTrace();  
14.     }  
15. }  

这个方法里将用户名为Mary的用户年龄改为30岁，执行程序，运行后查看数据库：

可以看到Mary的年龄确实变成了30,，删除成功。

代码：

package ch;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.List;

import java.util.Map;

 

public class User5 {

public void updateUser(userVo users,Connection conn)throws Exception

{

String sql="update tbl_user_info set user_name=?,age=?,sex=? where id=?";

PreparedStatement pstmt=conn.prepareStatement(sql);

pstmt.setString(1, users.getUserName());

pstmt.setInt(2, users.getAge());

pstmt.setInt(3, users.getSex());

pstmt.setInt(4, users.getId());

pstmt.execute();

}

    

public static void main(String[] args) {

User5 u5=new User5();

userVo users=new userVo();

users.setUserName("Mary");

users.setAge(30);

users.setSex(0);

users.setId(4);

try{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String url="jdbc:sqlserver://localhost:1433; DatabaseName=student";

String user="sa";

String password="123456";

 Connection conn= DriverManager.getConnection(url,user,password);

     Statement stmt=conn.createStatement();

     String sql = "select * from tbl_user_info";

     ResultSet rs = stmt.executeQuery(sql);

     u5.updateUser(users, conn);

}catch(Exception e){

e.printStackTrace();

}

System.out.println("End");

}

 

}

 

截图：