1. 作用
使用 Java 对数据库进行增、删、改、查操作
2. 环境
- commons-dbutils.jar
- c3p0.jar
- mchange-commons.jar
3. 使用
update()
- int update(String sql,Object… params)
- 此方法可以应用于 INSERT、UPDATE、DELETE 操作
@Test
public void testUpdate() {
//1. 创建 QueryRunner 的实现类
QueryRunner queryRunner = new QueryRunner();
//2. 使用其 update 方法
// String sql = "DELETE FROM customers WHERE id IN(?)"; //删除
// String sql = "INSERT INTO customers(name, email, birth) VALUES(?,?,?)"; //插入
String sql = "UPDATE customers SET name = ? WHERE id = ?";
Connection connection = null;
try {
connection = JDBCTools.getConnection();
queryRunner.update(connection, sql, "luw", 11);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, null, null);
}
}
query()
- 此方法取决于其
ResultSetHandler参数的 handel 方法的返回值
class MyResultHandler implements ResultSetHandler{
@Override
public Object handle(ResultSet resultSet) throws SQLException {
List<Customer> customers = new ArrayList<>();
while(resultSet.next()){
Integer id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
Customer customer = new Customer(id, name, email, birth);
customers.add(customer);
}
return customers;
}
}
@Test
public void testQuery() {
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name, email, birth FROM customers";
Object obj = queryRunner.query(connection, sql, new MyResultHandler());
System.out.println(obj);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, null, null);
}
}
ResultSetHandler 接口
- BeanHandler：用来把结果集的一条记录（默认为第一条记录）转为创建 BeanHandler 对象时传入的参数对应的对象，如下面的 Employee 对象
@Test
public void testBeanHandler(){
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name, email, birth FROM customers WHERE id = ?";
Customer customer = queryRunner.query(connection, sql, new BeanHandler<Customer>(Customer.class), 23);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, null, null);
}
}
- BeanListHandler：
- 把结果集转为一个 List，该 List 中包含的是对象的集合（该 List 不为 null，但可
能为空集合，即 size() 方法返回 0） - 若 SQL 语句的确能够查询到记录，List 中存放的对象为创建 BeanListHandler 时
传入的对象
@Test
public void testBeanListHandler(){
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT * FROM customers";
List<Customer> customers = queryRunner.query(connection, sql, new BeanListHandler<Customer>(Customer.class));
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, null, null);
}
}
- MapHandler：返回 SQL 语句的第一条记录对应的 Map 对象
键：SQL 查询的列名（不是列的别名） 值：列的值
@Test
public void testMapHandler(){
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT * FROM customers";
Map<String, Object> customers = queryRunner.query(connection, sql, new MapHandler());
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, null, null);
}
}
- MapListHandler：
- 将结果集转为一个 Map 的 List，返回多条记录的 Map 集合（List<Map<Object，Object>>）
- Map 对应的查询的一条记录：键：SQL 查询的列名（不是列的别名） 值：列的值
@Test
public void testMapListHandler(){
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT * FROM customers";
List<Map<String, Object>> customers = queryRunner.query(connection, sql, new MapListHandler());
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, null, null);
}
}
- ScalarHandler：把结果集转为一个数值(可以是任意基本数据类型和字符串, Date 等)
@Test
public void testScalarHandler(){
QueryRunner queryRunner = new QueryRunner();
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT name FROM customers WHERE id = ?";
Object customers = queryRunner.query(connection, sql, new ScalarHandler<Customer>(), 2);
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, null, null);
}
}
