commons-dbutils 工具使用

一、准备工作

1.1 环境准备

导入 commons-dbutils 工具包 ，本文采用：commons-dbutils-1.4.jar

1.2 数据库准备

本文采用 c3p0 技术创建 Connection 连接池

DataSourceUtils 代码实现：

package com.test.utils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DataSourceUtils {private static ComboPooledDataSource dataSource  = new ComboPooledDataSource();public static DataSource getDataSource() {return dataSource;}public static Connection getConnection() {try {return dataSource.getConnection();} catch (SQLException e) {throw new RuntimeException("数据连接获取失败！");}}/** * 释放资源 * @param conn * @param st * @param rs */public static void colseResource(Connection conn,Statement st,ResultSet rs) {closeResultSet(rs);closeStatement(st);closeConnection(conn);}/** * 释放连接 Connection * @param conn */public static void closeConnection(Connection conn) {if(conn !=null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}//等待垃圾回收conn = null;}/** * 释放语句执行者 Statement * @param st */public static void closeStatement(Statement st) {if(st !=null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}//等待垃圾回收st = null;}/** * 释放结果集 ResultSet * @param rs */public static void closeResultSet(ResultSet rs) {if(rs !=null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}//等待垃圾回收rs = null;}}

二、DML 操作

增加、修改、删除操作

package com.test.dbtest;import java.sql.SQLException;import org.apache.commons.dbutils.QueryRunner;import org.junit.Test;import com.test.daomain.User;import com.test.utils.DataSourceUtils;public class DBUtilsDemo1 {@Testpublic void addTest() throws SQLException {User user = new User(14,"小鑫","123");new DBUtilsDemo1().add(user);}@Testpublic void deleteTest() throws SQLException {User user = new User(14,"小鑫","123");new DBUtilsDemo1().delete(user);}@Testpublic void updateTest() throws SQLException {User user = new User(3,"黑嘿嘿","32434");new DBUtilsDemo1().update(user);}/** * 添加操作 * @throws SQLException  */public void add(User user) throws SQLException {QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());String sql = "insert into student (uid,username,password) values (?,?,?)";int i = qr.update(sql, user.getUid(),user.getUsername(),user.getPassword());if(i==1) {System.out.println("数据添加成功！");}else {System.out.println("数据添加失败！");}}/** * 删除操作 * @throws SQLException  */public void delete(User user) throws SQLException {QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());String sql = "delete from student where uid = ?";int i = qr.update(sql, user.getUid());if(i==1) {System.out.println("数据删除成功！");}else {System.out.println("数据删除失败！");}}/** * 修改操作 * @throws SQLException  */public void update(User user) throws SQLException {QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());String sql = "update student set username = ? where uid = ?";int i = qr.update(sql, user.getUsername(), user.getUid());if(i==1) {System.out.println("数据更新成功！");}else {System.out.println("数据更新失败！");}}}

三、查询操作

QueryRunner 类常用的查询操作方法

query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh)

query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)

query() 方法需要自己传入一个 ResultSetHandler 接口的实现类结果集，如果语句执行成功之后就返回给用户最开始指定的结果集类型的结果

常见的 ResultSetHandler 结果集：

package com.test.dbtest;import java.sql.SQLException;import java.util.Arrays;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.ArrayHandler;import org.apache.commons.dbutils.handlers.ArrayListHandler;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import org.junit.Test;import com.test.daomain.Student;import com.test.utils.DataSourceUtils;public class DBUtilsDemo2 {@Testpublic void arrayHandler() throws SQLException{QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());String sql="select * from student";// 只查询一条记录Object[] query = qr.query(sql, new ArrayHandler());System.out.println(Arrays.toString(query));}@Testpublic void arrayListHandler() throws SQLException{QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());String sql="select * from student";// 将查询到的结果封装到 List 集合中返回List<Object[]> list = qr.query(sql, new ArrayListHandler());for (Object[] obj : list) {System.out.println(Arrays.toString(obj));}}@Testpublic void beanHandler() throws SQLException{QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());String sql="select * from student where uid = ?";// 将查询到的结果封装到一个javabean中并返回// 要求javabean 名称和 数据库表名称一致// 必须有对属性有setter 和 getter 方法// 必须有无参的构造方法Student bean = qr.query(sql, new BeanHandler<>(Student.class),new Integer(3));System.out.println(bean);}@Testpublic void beanListHandler() throws SQLException{QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());String sql="select * from student";// 将查询到的数据封装到javabean中，并装在list 结合返回List<Student> students = qr.query(sql, new BeanListHandler<>(Student.class));for (Student stu : students) {System.out.println(stu);}}@Testpublic void mapHandler() throws SQLException{QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());String sql="select * from student";Map<String, Object> map = qr.query(sql, new MapHandler());for (String stu : map.keySet()) {System.out.println(map.get(stu));}System.out.println(map);}@Testpublic void mapListHandler() throws SQLException{QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());String sql="select * from student";List<Map<String, Object>> list = qr.query(sql, new MapListHandler());for (Map<String, Object> map : list) {System.out.println(map);}}@Testpublic void scalarHandler() throws SQLException{QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());String sql="select count(*) from student where username like ?";// 将查询的列结果封装到 ScalarHandler结果集中，返回Object obj = qr.query(sql, new ScalarHandler(),"%小%");System.out.println(obj);System.out.println(obj.getClass().getName());// obj 类型为 Long}}