1.Apache-DBUtils引出
为什么要有Apache-DBUtils？
返回resultSet的时候：
- 结果集合和connection时关联的，如果关闭连接就不能使用结果集
- 结果集不利于数据管理（只能使用一次）
- 使用放回信息也不方便
Apache-DBUtils图示：
2.Apache-DBUtils查询语句
commons-dbutils是apache组织提供的一个开源JDBC工具类，它是对JDBC的封装
使用dbutils能极大简化jdbc编码的工作量🎈
示例：使用DBUtils + Druid的方式，完成对于表的CRUD
Apache-Utils下载地址
或者更简单的，使用Maven：
<!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils --> <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.7</version> </dependency>
使用commons-dbutils工具类，需要先创建一个数据库表的映射类，放在domain层
User.java：
/** * User数据库表的对应类 */ public class User { private Integer id; private String username; private String password; private String school; /** * 一定要给一个无参构造器 */ public User() { } public User(Integer id, String username, String password, String school) { this.id = id; this.username = username; this.password = password; this.school = school; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getSchool() { return school; } public void setSchool(String school) { this.school = school; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", school='" + school + '\'' + '}'; } }
Apache-DBUtils测试类（查询多条记录的情况）：
import jdbc.domain.User; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; /** * Apache - DBUtils演示 */ public class ApDBUtils { public static void main(String[] args) { Connection connection = null; String sql = "select id,username,password,school from user where id >= ?"; try { connection = DruidJdbcUtils.getConnection(); // 创建一个QueryRunner QueryRunner queryRunner = new QueryRunner(); // 执行查询语句 // connection：连接 // sql：sql语句 // new BeanHandler<>(User.class)：将ResultSet --> User对象 --> 封装到ArrayList中 // 底层使用反射机制去获取User类的属性，然后封装 // 100：填充sql的参数，为可变参数 // 结果集得到的list query函数底层会自动关闭 List<User> list = queryRunner.query(connection, sql, new BeanListHandler<>(User.class), 100); for (User user : list) { System.out.println(user); } } catch (SQLException e) { throw new RuntimeException(e); } finally { // 关闭连接 DruidJdbcUtils.close(null, null, connection); } } }
Apache-DBUtils测试类（查询单条记录的情况）：
/** * 查询单条记录 */ @Test public void testQuerySingle() { Connection connection = null; String sql = "select id,username,password,school from user where id = ?"; try { connection = DruidJdbcUtils.getConnection(); QueryRunner queryRunner = new QueryRunner(); User user = queryRunner.query(connection, sql, new BeanHandler<>(User.class), 50); System.out.println(user); } catch (SQLException e) { throw new RuntimeException(e); } finally { // 关闭连接 DruidJdbcUtils.close(null, null, connection); } }
Apache-DBUtils测试类（查询单条单列记录的情况）：
/** * 查询单行单列 */ @Test public void testQueryScalar() { Connection connection = null; String sql = "select username from user where id = ?"; try { connection = DruidJdbcUtils.getConnection(); QueryRunner queryRunner = new QueryRunner(); Object query = queryRunner.query(connection, sql, new ScalarHandler<>(), 100); System.out.println(query); } catch (SQLException e) { throw new RuntimeException(e); } finally { // 关闭连接 DruidJdbcUtils.close(null, null, connection); } }
3.Apache-DBUtils DML操作
先来我们来演示一下Apache-DBUtils DML操作
/** * 演示DML语句 */ @Test public void testDML() { Connection connection = null; String sql = "delete from user where id = ?"; try { connection = DruidJdbcUtils.getConnection(); QueryRunner queryRunner = new QueryRunner(); // 返回值为受影响的行数 int affectedRow = queryRunner.update(connection, sql, 50); System.out.println(affectedRow); } catch (SQLException e) { throw new RuntimeException(e); } finally { // 关闭连接 DruidJdbcUtils.close(null, null, connection); } }
