P53 QueryRunner的使用

QueryRunner实现添加数据的操作

@Test
public void testInsert()  {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils1.getConnection2();
        String sql="insert into customers(name,email,birth)values(?,?,?) ";
        int insertCount=runner.update(conn,sql, "蔡徐坤","cxk@qq.com","1990-01-02");
        System.out.println("添加了"+insertCount+"条记录");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, null);
    }
}

QueryRunner查询表中一条或多条记录

//测试查询
//BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
@Test
public void testQuery1()  {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils1.getConnection2();
        String sql="select id,name,email,birth from customers where id=?";
        BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
        Customer cust = runner.query(conn, sql, 23, handler);
        System.out.println(cust);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, null);
    }

}
//返回多条记录，要用BeanListHandler
@Test
public void testQuery2()  {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils1.getConnection2();
        String sql="select id,name,email,birth from customers where id<?";

        BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);

        List<Customer> cust = runner.query(conn, sql, 23, handler);
        cust.forEach(System.out::println);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, null);
    }
}
//3.MapHandeler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
//将字段及相应字段的值作为map中的key和value
@Test
public void testQuery3()  {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils1.getConnection2();
        String sql="select id,name,email,birth from customers where id=?";
        MapHandler mapHandler = new MapHandler();
        Map<String, Object> map = runner.query(conn, sql, 23, mapHandler);
        System.out.println(map);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, null);
    }
}

//4.MapListHandeler:是ResultSetHandler接口的实现类，对应表中的多条记录
//将字段及相应字段的值作为map中的key和value，将这些map添加到list中
@Test
public void testQuery4()  {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils1.getConnection2();
        String sql="select id,name,email,birth from customers where id<?";
        MapListHandler mapListHandler = new MapListHandler();
        List<Map<String, Object>> list = runner.query(conn, sql, 23, mapListHandler);
        list.forEach(System.out::println);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, null);
    }
}

QueryRunner查询表中特殊值

//5.ScalarHandler:用于查询特殊值
@Test
public void testQuery5()  {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils1.getConnection2();
        String sql="select count(*) from customers";

        ScalarHandler scalarHandler = new ScalarHandler();

        Long count = (Long) runner.query(conn, sql, scalarHandler);
        System.out.println(count);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, null);
    }
}
//5.ScalarHandler:用于查询特殊值
@Test
public void testQuery6()  {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils1.getConnection2();
        String sql="select max(birth) from customers";

        ScalarHandler scalarHandler = new ScalarHandler();

        Date date = (Date) runner.query(conn, sql, scalarHandler);
        System.out.println(date);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, null);
    }
}