java中jdbc查询有返回值_JDBC 使用 DBUtils 进行查询的实现原理和具体的 Handler

/**

* JDBC 使用 DBUtils 进行操作

* 测试 QueryRunner 类的 update 方法

* 该方法可用于 INSERT， UPDATE 和 DELETE

*/

QueryRunner queryRunner = new QueryRunner();

class MyResultSetHandler implements ResultSetHandler{

@Override

public Object handle(ResultSet resultSet) throws SQLException {

//   System.out.println("handle...");

//   return "passionfly";

List customers = new ArrayList();

while(resultSet.next()){

Integer id = resultSet.getInt(1);

String name = resultSet.getString(2);

String email = resultSet.getString(3);

Date hiredate = resultSet.getDate(4);

Customer customer = new Customer(id, name, email, hiredate);

customers.add(customer);

}

return customers;

}

}

@Test

public void testUpdate() {

//1. 创建 QueryRunner 的实现类   String sql = "DELETE FROM customer WHERE id IN (?, ?)";

Connection connection = null;

try {

connection = JDBCTools.GetConnection();

//2. 使用其 update 方法   queryRunner.update(connection, sql, 2, 4);

} catch (Exception e) {

e.printStackTrace();

} finally{

JDBCTools.release(null, null, connection);

}

}

/**

* QueryRunner 的 query 方法的返回值取决于其 ResultSetHandler 参数的 handle 方法的返回值

*/ @Test

public void testQuery(){

Connection connection = null;

try {

connection = JDBCTools.GetConnection();

String sql = "SELECT id, name, email, hiredate " +

"FROM customer";

Object obj = queryRunner.query(connection, sql,

new MyResultSetHandler());//query 方法的返回值即為handle 方法的返回值

System.out.println(obj);

} catch (Exception e) {

} finally{

JDBCTools.release(null, null, connection);

}

/**

* BeanHandler: 把结果集的第一条记录转为创建 BeamHandler 对象时传入 Class

* 的参数对应的对象。

*/ @Test

public void testBeanHandler(){

Connection connection = null;

try {

connection = JDBCTools.GetConnection();

String sql = "SELECT id, name, email, hiredate FROM customer" +

" WHERE id = ?";

Customer customer = queryRunner.query(connection, sql,

new BeanHandler(Customer.class), 1);

System.out.println(customer);

} catch (Exception e) {

e.printStackTrace();

} finally{

JDBCTools.release(null, null, connection);

}

}

}

/**

* BeanListHandler: 把结果集转为一个 List， 该 List 不为 null，但可能为 空集合(size() 方法返回 0)

* 若SQL 语句的确能够查询到记录，List 中存放创建 BeanListHandler 传入的 Class 对象对应的对象。

*/ @Test

public void testBeamListHandler(){

Connection connection = null;

try {

connection = JDBCTools.GetConnection();

String sql = "SELECT id, name, email, hiredate FROM customer" ;

List

customers = queryRunner.query(connection, sql,

new BeanListHandler(Customer.class));

System.out.println(customers);

} catch (Exception e) {

e.printStackTrace();

} finally{

JDBCTools.release(null, null, connection);

}

}

/**

* MapHandler: 返回 SQL 对应的第一条记录对应的 Map 对象

* Map 对应查询的一条记录： 键：SQL查询的列名(不是列的别名)，值：列的值

* 而 MapListHandler：返回的对条记录对应的 Map 集合

*/ @Test

public void testMapListHandler(){

Connection connection = null;

try {

connection = JDBCTools.GetConnection();

String sql = "SELECT id, name, email, hiredate FROM customer" ;

List> result = queryRunner.query(connection, sql,

new MapListHandler());

System.out.println(result);

} catch (Exception e) {

e.printStackTrace();

} finally{

JDBCTools.release(null, null, connection);

}

}

@Test

public void testMapHandler(){

Connection connection = null;

try {

connection = JDBCTools.GetConnection();

String sql = "SELECT id, name, email, hiredate FROM customer" ;

Map result = queryRunner.query(connection, sql,

new MapHandler());

System.out.println(result);

} catch (Exception e) {

e.printStackTrace();

} finally{

JDBCTools.release(null, null, connection);

}

}

@Test

public void testScalarHandler(){

Connection connection = null;

try {

connection = JDBCTools.GetConnection();

String sql = "SELECT name" +

" FROM customer WHERE id = ?" ;

Object result = queryRunner.query(connection, sql,

new ScalarHandler(), 1);

System.out.println(result);

} catch (Exception e) {

e.printStackTrace();

} finally{

JDBCTools.release(null, null, connection);

}

}