JDBC - 学习8 - Apache-DBUtil类库的使用

1. 介绍

介绍： 简单的封装JDBC，简化JDBC编程的工作量，不会影响程序的性能

2. QueryRunner

2.1 更新操作 - 增删改

  QueryRunner.update( Connection conn, String sql, Object… params )

@Test
public void testInsert() throws Exception {

    QueryRunner qRunner = new QueryRunner();

    Connection conn = ConnectionTest.getConnection5();

    String sql = "update imgTable set money = money + 100 where name = ?";

    Integer rowCount = qRunner.update(conn, sql, 2);

    conn.close();

    System.out.println(rowCount);
}

 

2.2 查询操作

2.2.1 单条记录查询 - BeanHandler

将结果集的每一个结果转为 JavaBean对象

  

  QureyRunner.query( Connection conn, String sql, ResultSetHandler rsh, Object… params )

@Test
public void testSelect1() throws Exception {
    QueryRunner qRunner = new QueryRunner();

    Connection conn = ConnectionTest.getConnection5();

    String sql = "select * from emp where empno in (?)";

    Object[] params = { 1113 };
    
    // 2. 数据集的处理 -- query的返回类型跟泛型参数有关
    ResultSetHandler<Emp> rsh = new BeanHandler<Emp>(Emp.class);

    Emp emp = qRunner.query(conn, sql, rsh, params);

    System.out.println(emp);

    conn.close();
}

 
  运行结果

 

2.2.2 多条记录查询 - BeanListHandler

将结果集的每一个结果转为 JavaBean对象，并将JavaBean对象存入到ArrayList容器中

  QureyRunner.query( Connection conn, String sql, ResultSetHandler rsh, Object… params )

@Test
public void testSelect2() throws Exception {
    QueryRunner qRunner = new QueryRunner();

    Connection conn = ConnectionTest.getConnection5();

    String sql = "select * from emp where empno in (?, ?, ?)";
    Object[] params = { 1113, 1111, 7654 };

    BeanListHandler<Emp> blh = new BeanListHandler<Emp>(Emp.class);

    List<Emp> list = qRunner.query(conn, sql, blh, params);

    list.forEach(System.out::println);

    DbUtils.close(conn);

}

 
  运行结果

 

2.2.3 用于查询特殊值的单行函数 - ScalarHandler

@Test
public void testSelect5() throws Exception {
    Connection conn = ConnectionTest.getConnection5();

    String sql = "select count(*) from emp";

    Object[] params = {};

    ResultSetHandler<Object> rsh = new ScalarHandler<Object>();

    QueryRunner qRunner = new QueryRunner();

    Object query = qRunner.query(conn, sql, rsh, params);

    System.out.println(query);

    conn.close();
}

 
  运行结果

 

2.2.4 自定义结果集的处理器

@Test
public void testSelect6() throws Exception{
    Connection conn = ConnectionTest.getConnection5();

    String sql = "select * from emp where empno=1113";

    Object[] params = {};
    
    // 1. 实现ResultSetHandler接口的匿名类  --- 只要实现一个接口方法即可
    ResultSetHandler<Emp> rsh = new ResultSetHandler<Emp>() {
        @Override
        public Emp handle(ResultSet rs) throws SQLException {
            Emp emp = null;
            ResultSetMetaData metaData = rs.getMetaData();
            Integer columnCount = metaData.getColumnCount();

            if(rs.next()) {
                emp = new Emp();
                Class<Emp> empClass = (Class<Emp>)emp.getClass();
                for( int i = 0; i < columnCount; i++) {

                    String columnName = (metaData.getColumnName(i+1)).toLowerCase();

                    String methodName = "set" + columnName.substring(0, 1).toUpperCase() + columnName.substring(1);


                    if(columnName.equals("hiredate")) {
                        continue;
                    }

                    Object value = rs.getObject(columnName);

                    if(value instanceof BigDecimal) {
                        value = ((BigDecimal)value).intValue();
                        try {
                            Method method = empClass.getDeclaredMethod(methodName, value.getClass());
                            method.invoke(emp, (Integer)value);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    if (value instanceof String) {
                        try {
                            Method method = empClass.getDeclaredMethod(methodName, value.getClass());
                            method.invoke(emp, (String)value);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }

                }
            }

            return emp;
        }
    };

    QueryRunner qRunner = new QueryRunner();

    Object query = qRunner.query(conn, sql, rsh, params);

    System.out.println(query);

    conn.close();

}

 
  运行结果

 

2.2.5 单条记录查询 - MapHandler

@Test
public void testSelect3() throws Exception {

    Connection conn = ConnectionTest.getConnection5();

    String sql = "select * from emp where empno = 1111";

    Object[] params = {};

    ResultSetHandler<Map<String, Object>> rsh = new MapHandler();

    QueryRunner qRunner = new QueryRunner();

    Map<String, Object> mapEmp = qRunner.query(conn, sql, rsh, params);

    System.out.println(mapEmp);

    conn.close();

}

 
  运行结果

 

2.2.6 多条记录查询 - MapListHandler

@Test
public void testSelect4() throws Exception {
    Connection conn = ConnectionTest.getConnection5();

    String sql = "select * from emp";

    Object[] params = {};

    ResultSetHandler<List<Map<String, Object>>> rsh = new MapListHandler();

    QueryRunner qRunner = new QueryRunner();

    List<Map<String, Object>> query = qRunner.query(conn, sql, rsh, params);

    query.forEach(System.out::println);

    conn.close();
}

 
  运行结果

 

2. DbUtils