JDBC访问数据库实验

建表：

CREATE TABLE
tbl_user_info
(
 id INT NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL,
   age INT NOT NULL,
    sex INT(1) NOT NULL,
     create_dt DATE NOT NULL,
      PRIMARY KEY (id)
)
 ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

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');

 

1. 定义实体类

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

UserVO.Java

import java.util.Date;

public class UserVO {
    private int id;
    private String userName;
    private int age;
    private int sex;
    private Date createDt;

    public int getId()
    {return id;}

    public void setId(int id)
    {this.id=id;}

    public String getUserName()
    {return userName;}

    public void setUserName(String userName)
    {this.userName=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 getCreteDt()
    {return createDt;}

    public void setCreateDt(Date createDt)
    {this.createDt=createDt;}

    @Override
    public String toString() {
        return "UserName[id=" + id + ",userName=" + userName + ",age=" + age + ",sex=" + sex + ",createDt=" + createDt + "]";
    }
}

定义数据库连接类

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

DBUtil.java

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtil {
    private static final String URI = "jdbc:mysql://localhost:3306/text?" +
            "user=root&password=123456&useUnicode=true&characterEncoding=UTF-8";
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    public static Connection connectDB() throws Exception {

        // 1、加载数据库驱动
        Class.forName(DRIVER);

        // 2、获取数据库连接
        Connection conn = DriverManager.getConnection(URI);
        return conn;
    }
}

 

实现数据库的增删改查

 

查询

先看查询操作，查询可以一次查询出所有的数据，也可以根据相应的条件查询。 查询所有的数据，在 UserDao 中定义一个 queryAll()方法

UserDao.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    public static List<UserVO> queryAll() throws Exception {
        Connection conn = DBUtil.connectDB();
        String sql = "SELECT * FROM tbl_user_info";
        List<UserVO> userList = new ArrayList<UserVO>();

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            UserVO user = new UserVO();
            user.setId(rs.getInt("id"));
            user.setUserName(rs.getString("user_name"));
            user.setAge(rs.getInt("age"));
            user.setSex(rs.getInt("sex"));
            user.setCreateDt(rs.getDate("create_dt"));

            userList.add(user);
        }
        return userList;
    }
    public static void main(String[] args) {
        UserDao dao = new UserDao();
        try {
            List<UserVO> userList = dao.queryAll();
            for(UserVO user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

 

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

import java.sql.Connection;
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 UserDao {
    public List<UserVO>queryByParams(List<Map<String,Object>>params) throws Exception{
        Connection conn=DBUtil.connectDB();
        StringBuilder sql=new StringBuilder("SELECT * FROM tbl_user_info WHERE 1=1");
        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>();

        Statement stmt=conn.createStatement();
        ResultSet rs=stmt.executeQuery(sql.toString());
        while(rs.next()){
            UserVO user=new UserVO();
            user.setId(rs.getInt("id"));
            user.setUserName(rs.getString("user_name"));
            user.setAge(rs.getInt("age"));
            user.setSex(rs.getInt("sex"));
            user.setCreateDt(rs.getDate("create_dt"));

            userList.add(user);
        }
        return userList;
    }
    public static void main(String[] args){
        UserDao dao = new UserDao();
        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 = dao.queryByParams(params);
            for(UserVO user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

增加

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

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao {
    public void addUser(UserVO user) throws Exception {
        Connection conn = DBUtil.connectDB();
        String sql = "INSERT INTO tbl_user_info(user_name, age, sex, create_dt) " + " VALUES(?, ?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, user.getUserName());
        pstmt.setInt(2, user.getAge());
        pstmt.setInt(3, user.getSex());
        pstmt.setDate(4, new Date(new java.util.Date().getTime()));
        pstmt.execute();
    }
    public static void main(String[] args) {
        UserDao dao = new UserDao();
        UserVO user = new UserVO();
        user.setUserName("gong");
        user.setAge(20);
        user.setSex(1);
        try {
            dao.addUser(user);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

删除

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

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao {
        public void deleteUser(int id) throws Exception {
            Connection conn = DBUtil.connectDB();
            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) {
        UserDao dao = new UserDao();
         try {
             dao.deleteUser(7);
             } catch (Exception e) {
            e.printStackTrace();
             }
        }

}

更新数据库

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao {
    public void updateUser(UserVO user)throws Exception{
        Connection conn = DBUtil.connectDB();
        String sql = "UPDATE tbl_user_info SET user_name=?, age=?, sex=?"
                + " WHERE id=?";

        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, user.getUserName());
        pstmt.setInt(2, user.getAge());
        pstmt.setInt(3, user.getSex());
        pstmt.setInt(4, user.getId());

        pstmt.executeUpdate();
    }

    public static void main(String[] args) {
        UserDao dao=new UserDao();
        UserVO user=new UserVO();

        user.setUserName("Jack");
        user.setAge(30);
        user.setSex(0);
        user.setId(4);

        try {
            dao.updateUser(user);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}