对数据表进行CRUD操作：dbutils

 前提：导入数据

提供javabean:Customer类 ：

/**
 * ORM（object relational mapping)的编程思想
 * 数据库中的一个表与java中的一个类对应
 * 数据表的一行与java类的一个对象对应
 * 数据表的一列与java类的一个属性对应
 */
public class Customer {

    private int id;
    private String name;
    private String email;
    private Date birth;

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public Customer() {
    }
}

1、添加数据

//插入数据
@Test
public void testInsert()  {

    Connection connection = null;
    try {
        //1.获取数据库的连接
        connection = JDBCUtils.getConnection();
        //2.使用QueryRunner实现添加操作
        QueryRunner queryRunner = new QueryRunner();
        //提供带占位符的sql语句
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        //略：SimpleDateFormat / DataTimeFormatter :解析：字符串-->日期
        int insertCount = queryRunner.update(connection, sql, "梁圳", "liangz@126.com", new Date(2343243234324L));

        System.out.println("添加了" + insertCount + "条记录");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //3.关闭连接
        JDBCUtils.close(connection);
    }

}

2、删除数据

//删除一条记录
@Test
public void testDelete(){
    Connection connection = null;
    try {
        //1.获取数据库的连接
        connection = JDBCUtils.getConnection();
        //2.使用QueryRunner实现删除操作
        QueryRunner queryRunner = new QueryRunner();
        String sql = "delete from customers where id <= ?";
        int deleteCount = queryRunner.update(connection, sql, 3);

        System.out.println("删除了" + deleteCount + "条记录");


    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //3.关闭连接
        JDBCUtils.close(connection);
    }


}

3、修改数据

//修改一条记录
    @Test
    public void testUpdate(){
        Connection connection = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.使用QueryRunner实现修改操作
            QueryRunner queryRunner = new QueryRunner();
            String sql = "update customers set name = ? where id = ?";
            int updateCount = queryRunner.update(connection, sql, "梁振",19);

            System.out.println("修改了" + updateCount + "条记录");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //3.关闭连接
            JDBCUtils.close(connection);
        }

    }

4、查询数据:

1)使用BeanHandler 

//查询表中一条记录
    //如果返回一条数据对应的javabean的对象，则使用BeanHandler
    @Test
    public void testGetInstance(){
        Connection connection = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.使用QueryRunner实现查询操作
            QueryRunner queryRunner = new QueryRunner();
            String sql = "SELECT id,NAME,email,birth FROM customers WHERE id = ?";
            BeanHandler<Customer> hander = new BeanHandler<>(Customer.class);

            Customer customer = queryRunner.query(connection, sql, hander, 10);

            System.out.println(customer);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //3.关闭连接
            JDBCUtils.close(connection);
        }

    }

2)使用BeanListHandler

//返回表中的多条记录
    //如果返回多条数据构成的List，则使用BeanHandler
    @Test
    public void testGetForList(){
        Connection connection = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.使用QueryRunner实现查询操作
            QueryRunner queryRunner = new QueryRunner();
            String sql = "SELECT id,NAME,email,birth FROM customers WHERE id < ?";
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> list = queryRunner.query(connection, sql, handler, 10);

            list.forEach(c -> System.out.println(c));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //3.关闭连接
            JDBCUtils.close(connection);
        }
    }

3）MapHandler

 //查询表中一条记录,将不同的字段封装到Map的key-value中
    @Test
    public void testGetForMap(){
        Connection connection = null;
        try {
            //1.获取数据库的连接
            connection = JDBCUtils.getConnection();
            //2.使用QueryRunner实现查询操作
            QueryRunner queryRunner = new QueryRunner();
            String sql = "SELECT id,NAME,email,birth FROM customers WHERE id = ?";
            ResultSetHandler<Map<String,Object>> handler = new MapHandler();
            Map<String, Object> map = queryRunner.query(connection, sql, handler, 10);

//            Set<Map.Entry<String, Object>> entries = map.entrySet();
//            for(Map.Entry<String,Object> e : entries){
//                System.out.println(e);
//            }

            System.out.println(map);


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //3.关闭连接
            JDBCUtils.close(connection);
        }
    }

4）ScalarHandler

//查询表中的特殊值：
//select count(*) from customers; select max(birth) from cutomers
@Test
public void testGetValue(){
    Connection connection = null;
    try {
        //1.获取数据库的连接
        connection = JDBCUtils.getConnection();
        //2.使用QueryRunner实现查询操作
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select count(*) from customers";
        ResultSetHandler<Object> handler = new ScalarHandler();
        long count = (long) queryRunner.query(connection, sql, handler);

        System.out.println("查询到表中的总记录数为：" + count);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //3.关闭连接
        JDBCUtils.close(connection);
    }
}