DBUtils
简介
DBUtils是Java编程中的数据库操作实用工具,小巧简单实用。DBUtils封装了对JDBC的操作,简化了JDBC操作,可以减少60%以上的代码。
DBUtils三个核心功能介绍
- QueryRunner:核心类提供对sql语句操作的API
- QueryRunner(DataSourcr ds):提供数据源(连接池),DbUtils底层自动维护连接connection
- update(String sql,Obj…params):执行更新操作
- query(String sql,ResultSetHandlerrsh,Object…panrams):执行查询操作
- ResultSetHandler:结果集处理类,用于定义select操作后,怎样封装结果集
- ArrayHandler:把查询结果集中的第一行数据放到一个数组中。适合取1条记录。
- ArrayListHandler:将查询的结果的每一行数据都放到一个数组中,然后再将数组放到集合List中。适合取多条记录。
- BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中
- BeanListHandler:将结果集中的每一行数据都封装到一个对应的JavaBean实例中,然后再将这些对象存放到List里。
- MapHandler:将结果集中的第一行数据封装到一个Map里,key是列名,value就是对应的值。
- MapListHandler:将查询的结果的每一行存入到一个map中,键为列名,值为各列值;然后再将map存入list中
- ColumnListHandler:将结果集中某一列的数据存放到List中。
- KeyedHandler(name):将结果集中的每一行数据都封装到一个Map里(List
- ScalarHandler:将结果集第一行的某一列放到某个对象中。//重点
- DBUtils:工具类,定义了关闭资源与事务处理的方法。
示例:采用Druid和dbutils实现对Dept的CRUD
-
第一步:Dept.java:Dept源代码
-
第二步:新建项目,加入Maven依赖:
<dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.7</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.17</version> </dependency>
-
第二步:在resources目录下创建mysql.properties,代码如下:
driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&user=root&password=&useUnicode=true&characterEncoding=UTF8&autoReconnect=true&failOverReadOnly=false username=root password=root filters=stat initialSize=2 maxActive=300 maxWait=60000 timeBetweenEvictionRunsMillis=60000 minEvictableIdleTimeMillis=300000 validationQuery=SELECT 1 testWhileIdle=true testOnBorrow=false testOnReturn=false poolPreparedStatements=false maxPoolPreparedStatementPerConnectionSize=200
-
第三步:创建数据库连接工具类:
public class DBUtil { private static DataSource dataSource = null; private DBUtil() { } public static DataSource getDataSource(){ return this.dataSource; } static { //配置文件加载,只执行一次 try (InputStream is = DBUtil.class.getResourceAsStream("/mysql.properties");) { Properties properties = new Properties(); properties.load(is); dataSource = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e1) { throw new RuntimeException("读取配置文件异常", e1); } } public static void release(Connection conn, Statement stmt, ResultSet rs) { DbUtils.closeQuietly(conn); DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(rs); } }
-
第四步:测试事务(重点)
public static void main(String[] args) {
Connection conn = null;
try {
//获取连接池
DataSource dataSource = DBUtil.getDataSource();
QueryRunner queryRunner = new QueryRunner(dataSource);
//获取连接,用来控制事务
conn = dataSource.getConnection();
conn.setAutoCommit(false); // 设置事务提交为手动
String sql = "insert into tb_dept (dname,loc) values(?,?)";
queryRunner.update(conn, sql, "aa", "aaaaa");
System.out.println(3 / 0);
queryRunner.update(sql, "bb", "bbbbbbbbbb");
//提交事务
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
if(conn !=null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (conn != null) {
//恢复事务
conn.setAutoCommit(true);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
-
第五步:DeptDaoImpl.java
public class DeptDaoImpl { //返回数据库中受影响的记录的行数 public int insertDept1(Dept dept) throws SQLException { QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); String sql = "insert into tb_dept (deptno, dname, loc) values (?, ?, ?)"; int res = queryRunner.update(sql, dept.getDeptno(), dept.getDname(), dept.getLoc()); return res; } public int batchInsertDept(List<Dept> deptList) throws SQLException { Object[][] params = new Object[deptList.size()][3]; for (int i = 0; i < params.length; i++) { Dept dept = deptList.get(i); params[i][0] = dept.getDeptno(); params[i][1] = dept.getDname(); params[i][2] = dept.getLoc(); } StringBuilder wenHao = new StringBuilder(); for (int i = 0; i < params[0].length; i++) { wenHao.append("?,"); } String sql = "insert into tb_dept values(" + wenHao.deleteCharAt(wenHao.length() - 1) + ")"; QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); queryRunner.batch(sql, params); return 1; // 如果不抛出异常,就返回1,表示删除成功 } public int deleteDeptByDeptno(Byte deptno) throws SQLException { QueryRunner queryRunner = new QueryRunner(dataSource); String sql = "delete from tb_dept where deptno = ?"; return queryRunner.update(sql, deptno); } public int deleteDeptByCondition(Dept dept) throws SQLException { List<Object> paramValueList = new ArrayList<>(); StringBuffer paramBuf = new StringBuffer("1=1 "); if (dept.getDeptno() != null) { paramBuf.append("and deptno= ? "); paramValueList.add(dept.getDeptno()); } if (dept.getDname() != null) { paramBuf.append("and dname= ? "); paramValueList.add(dept.getDname()); } if (dept.getLoc() != null) { paramBuf.append("and loc= ? "); paramValueList.add(dept.getLoc()); } String sql = "delete from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3); QueryRunner queryRunner = new QueryRunner(dataSource); return queryRunner.update(sql, paramValueList.toArray()); } public int batchDeleteDeptByDeptnos(String deptnos) throws SQLException { String[] split = deptnos.split(","); Object[][] params = new Object[1][]; StringBuilder wenHao = new StringBuilder(); for (int i = 0; i < split.length; i++) { wenHao.append("?,"); } params[0] = split; String sql = "delete from tb_dept where deptno in (" + wenHao.deleteCharAt(wenHao.length() - 1) + ")"; QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); queryRunner.batch(sql, params); return 1; // 如果不抛出异常,就返回1,表示删除成功 } public int updateDept(Dept dept) throws SQLException { QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); String sql = "update tb_dept set dname= ? ,loc= ? where deptno = ?"; return queryRunner.update(sql, dept.getDname(), dept.getLoc(), dept.getDeptno()); } public long selectCount() throws SQLException { QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); String sql = "select count(*) from tb_dept"; // ScalarHandler:将查询的结果的第一行的某一列放到一个对象中;精确定位到某个值 Long query = queryRunner.query(sql, new ScalarHandler<Long>()); return query.intValue(); } public long selectCountByCondition(Dept dept) throws SQLException { List<Object> paramValueList = new ArrayList<>(); StringBuffer paramBuf = new StringBuffer("1=1 "); if (dept.getDeptno() != null) { paramBuf.append("and deptno= ? "); paramValueList.add(dept.getDeptno()); } if (dept.getDname() != null) { paramBuf.append("and dname= ? "); paramValueList.add(dept.getDname()); } if (dept.getLoc() != null) { paramBuf.append("and loc= ? "); paramValueList.add(dept.getLoc()); } String sql = "select count(*) from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3); QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); Long query = queryRunner.query(sql, new ScalarHandler<Long>(), paramValueList.toArray()); return query.intValue(); } public Dept selectDeptByDeptno(Byte deptno) throws SQLException { String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept where deptno = ?"; QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); return queryRunner.query(sql, new BeanHandler<>(Dept.class), deptno); } public List<Dept> selectAllDept() throws SQLException { String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept"; QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); return queryRunner.query(sql, new BeanListHandler<>(Dept.class)); } public List<Dept> selectDeptByCondition(Dept dept) throws SQLException { List<Object> paramValueList = new ArrayList<>(); StringBuffer paramBuf = new StringBuffer("1=1 "); if (dept.getDeptno() != null) { paramBuf.append("and deptno= ? "); paramValueList.add(dept.getDeptno()); } if (dept.getDname() != null) { paramBuf.append("and dname= ? "); paramValueList.add(dept.getDname()); } if (dept.getLoc() != null) { paramBuf.append("and loc= ? "); paramValueList.add(dept.getLoc()); } String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3); QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); return queryRunner.query(sql, new BeanListHandler<>(Dept.class), paramValueList.toArray()); } public List<Dept> selectDeptWithPagination(int page, int rows) throws SQLException { QueryRunner queryRunner = new QueryRunner(dataSource); String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept limit ?, ?"; return queryRunner.query(sql, new BeanListHandler<>(Dept.class), (page - 1) * rows, rows); } public List<Dept> selectDeptWithPaginationByCondition(int page, int rows, Dept dept) throws SQLException { List<Object> paramValueList = new ArrayList<>(); StringBuffer paramBuf = new StringBuffer("1=1 "); if (dept.getDeptno() != null) { paramBuf.append("and deptno= ? "); paramValueList.add(dept.getDeptno()); } if (dept.getDname() != null) { paramBuf.append("and dname= ? "); paramValueList.add(dept.getDname()); } if (dept.getLoc() != null) { paramBuf.append("and loc= ? "); paramValueList.add(dept.getLoc()); } String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3) + " limit ?, ?"; paramValueList.add((page - 1) * rows); paramValueList.add(rows); QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource()); return queryRunner.query(sql, new BeanListHandler<>(Dept.class), paramValueList.toArray()); } }