一、commons-dbutils简介
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
commons-dbutilsAPI介绍：
- org.apache.commons.dbutils.QueryRunner
- org.apache.commons.dbutils.ResultSetHandler
工具类
- org.apache.commons.dbutils.DbUtils
二、QueryRunner类使用讲解
该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。
QueryRunner类提供了两个构造方法：
- 默认的构造方法
- 需要一个 javax.sql.DataSource 来作参数的构造方法。
2.1、QueryRunner类的主要方法
- public Object query(String sql, ResultSetHandler rsh, Object[] params) throws SQLException: 几乎与上面的方法一样；唯一的不同在于它不将数据库连接提供给方法，并且它是从提供给构造方法的数据源(DataSource) 或使用的setDataSource 方法中重新获得 Connection。
- public Object query(String sql, ResultSetHandler rsh) throws SQLException : 执行一个不需要置换参数的查询操作。
- public int update(String sql, Object[] params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
- public int update(String sql) throws SQLException：用来执行一个不需要置换参数的更新操作。如更改某列的所有数据(String sql = “update users set password=‘AK-007’;”; )。
2.2、使用QueryRunner类实现CRUD
package waf.yty.dbutils;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import waf.yty.jdbc.JdbcUtils;
public class QueryRunnerCRUDTest {
@Test
public void add() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
Object[] params = {"3","杨腾宇","123","603312698@qq.com","1992-11-12"};
int resultmd = qr.update(sql, params);
System.out.println(resultmd);
}
@Test
public void delete() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "delete from users where id=?";
qr.update(sql, 1);
}
@Test
public void udpate() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "update users set name=? where id=?";
Object[] params = {"许健康","3"};
int resultmd = qr.update(sql, params);
System.out.println(resultmd);
}
@Test
public void updateAll() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "update users set password='AK-007';";
int updateAllResult = qr.update(sql);
System.out.println(updateAllResult);
}
@Test
public void getOne() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users where id=?";
Object params[] = {6};
User user = qr.query(sql,new BeanHandler<User>(User.class), params);
System.out.println(user);
}
@Test
public void findAll() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
List<User> listUser = qr.query(sql, new BeanListHandler<User>(User.class));
System.out.println(listUser);
}
/**
* QR的批处理
* @throws SQLException
*/
@Test
public void testBatch() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into users(name,password,email,birthday) values (?,?,?,?)";
Object params[][] = new Object[6][];
for (int i = 0; i < 6; i++) {
params[i] = new Object[]{"abc" + i, "123", "abc@126.com", new Date()};
}
int[] batches = qr.batch(sql, params);
for (int batch : batches) {
System.out.println(batch);
}
}
}
package waf.yty.dbutils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
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 waf.yty.jdbc.JdbcUtils;
public class Demo3 {
@Test
public void fun1() throws SQLException {
// QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
// String sql = "insert into t_stu values(?,?,?,?)";
// Object[] params = {1002,"死城之谜",86,"男人海洋"};
// qr.update(sql, params);
// Connection conn = JdbcUtils.getConnection();
QueryRunner qr1 = new QueryRunner(JdbcUtils.getDataSource());
String sql1 = "insert into t_stu values(?,?,?,?)";
Object[] params1 = {1006,"炼狱小镇",6666,"老6"};
qr1.update(sql1,params1);
// conn.close();
}
@Test
public void fun2() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from t_stu where sid=?";
Object[] params = {166};
// Stu stu = qr.query(sql, params, new BeanHandler<Stu>(Stu.class));
Stu stu1 = qr.query(sql, new BeanHandler<Stu>(Stu.class), params);
// System.out.println(stu);
System.out.println(stu1);
}
@Test
public void fun3() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from t_stu";
List<Stu> stuList = qr.query(sql, new BeanListHandler<Stu>(Stu.class));
System.out.println(stuList.size());
}
@Test
public void fun4() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from t_stu where sid=?";
Object[] params = {1002};
Map<String, Object> map = qr.query(sql, new MapHandler(), params);
System.out.println(map);
}
@Test
public void fun5() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from t_stu";
List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler());
System.out.println(mapList);
}
@Test
public void fun6() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select count(*) from t_stu";
Number count = (Number) qr.query(sql, new ScalarHandler());
int c = count.intValue();
System.out.println(count);
System.out.println(c);
}
}
三、ResultSetHandler接口使用讲解
该接口用于处理java.sql.ResultSet，将数据按要求转换为另一种形式。
ResultSetHandler接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。
3.1、ResultSetHandler接口的实现类
- ArrayHandler：把结果集中的第一行数据转成对象数组。
- ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
- BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
- BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
- MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
- MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。
- ColumnListHandler：将结果集中某一列的数据存放到List中。
- KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
- ScalarHandler：返回值类型为java.lang.Long。一般用于查询表中所有列不都为NULL的记录的行数(String sql = “select count(*) from t_stu”; )。
3.2、测试dbutils各种类型的处理器
package waf.yty.dbutils;
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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
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 waf.yty.jdbc.JdbcUtils;
public class ResultSetHandlerTest {
@Test
public void testArrayHandler() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
Object[] result = qr.query(sql, new ArrayHandler());
System.out.println(Arrays.asList(result)); // list toString()
}
@Test
public void testArrayListHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
List<Object[]> list = qr.query(sql, new ArrayListHandler());
for (Object[] object : list) {
System.out.println(Arrays.asList(object));
}
}
@Test
public void testColumnListHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
List<Object> list = qr.query(sql, new ColumnListHandler("email"));
for (Object singleList : list) {
System.out.println(singleList);
}
}
@Test
public void testKeyedHandler() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
Map<Object, Map<String, Object>> query = qr.query(sql, new KeyedHandler("name"));
for (Map.Entry<Object, Map<String, Object>> me : query.entrySet()) {
Object name = me.getKey();
System.out.println(name + "笑哈哈");
Map<String, Object> innerMap = me.getValue();
for (Map.Entry<String, Object> innerme : innerMap.entrySet()) {
String colunmName = innerme.getKey();
Object value = innerme.getValue();
System.out.println(colunmName + "=" + value);
}
System.out.println("------");
}
}
@Test
public void testMapHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
Map<String, Object> map = qr.query(sql, new MapHandler());
for (Map.Entry<String, Object> me : map.entrySet()) {
System.out.println(me.getKey() + "=" + me.getValue());
}
}
@Test
public void testMapListHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from users";
List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
for (Map<String, Object> map : list) {
for (Map.Entry<String, Object> me : map.entrySet()) {
System.out.println(me.getKey() + "=" + me.getValue());
}
}
}
@Test
public void testScalarHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select count(*) from users";
int count = ((Long)qr.query(sql, new ScalarHandler())).intValue();
System.out.println(count);
}
}
四、JDBC开发中的事务处理
在开发中，对数据库的多个表或者对一个表中的多条数据执行更新操作时要保证对多个更新操作要么同时成功，要么都不成功，这就涉及到对多个更新操作的事务管理问题了。比如银行业务中的转账问题，A用户向B用户转账100元，假设A用户和B用户的钱都存储在Account表，那么A用户向B用户转账时就涉及到同时更新Account表中的A用户的钱和B用户的钱，用SQL来表示就是：
1 update account set money=money-100 where name='A';
2 update account set money=money+100 where name='B'
4.1、JdbcUtils.java(JdbcUtils的第三次修改)
package waf.yty.jdbc;
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 JdbcUtils {
// 配置文件的默认配置!必须在类路径下给出c3p0-config.xml!
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
// 事务专用连接!
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
/**
* 返回连接对象
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection con = tl.get();
// con!=null表示已经执行beginTransaction()方法，开启了事务!
if (con != null) return con;
return dataSource.getConnection();
}
/**
* 返回连接池对象
* @return
*/
public static DataSource getDataSource() {
return dataSource;
}
/**
* 开启事务
* 1. 获取一个Connection,设置setAutoCommit(false)
* @throws SQLException
*/
public static void beginTransaction() throws SQLException {
Connection con = tl.get();
if(con != null) throw new SQLException("请勿重复开启事务!");
con = getConnection();
con.setAutoCommit(false);
tl.set(con); // 把当前线程的连接保存起来!
}
/**
* 提交事务
* 1. 获取beginTransaction提供的Connection,调用commit()方法
* @throws SQLException
*/
public static void commitTransaction() throws SQLException {
Connection con = tl.get();
if(con == null) throw new SQLException("还没开启事务，不能提交!");
con.commit();
con.close();
tl.remove();
}
/**
* 提交事务
* 1. 获取beginTransaction提供的Connection,调用rollback()方法
* @throws SQLException
*/
public static void rollbackTransaction() throws SQLException {
Connection con = tl.get();
if(con == null) throw new SQLException("还没开启事务，不能回滚!");
con.rollback();
con.close();
tl.remove();
}
/**
* 释放连接
* @param con
* @throws SQLException
*/
public static void releaseConnection(Connection connection) throws SQLException {
Connection con = tl.get();
if (con == null) connection.close();
if (con != connection) connection.close();
}
}
4.2、TxQueryRunner类(继承QueryRunner类，重写其参数没有Connection的所有方法)
package waf.yty.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
/**
* 这个类中的方法，自己来处理连接的问题
* 无需外接传递！
* 怎么做到的呢？
* 通过JdbcUtils.getConnection()得到连接
* @author yangtengyu
*
*/
public class TxQueryRunner extends QueryRunner {
@Override
public int[] batch(String sql, Object[][] params) throws SQLException {
/*
* 1. 得到连接
* 2. 执行父类方法,传递连接对象
* 3. 释放连接
* 4. 返回值
*/
Connection con = JdbcUtils.getConnection();
int[] result = super.batch(con, sql, params);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public <T> T query(String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
Connection con = JdbcUtils.getConnection();
T result = super.query(con, sql, param, rsh);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
Connection con = JdbcUtils.getConnection();
T result = super.query(con, sql, params, rsh);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection con = JdbcUtils.getConnection();
T result = super.query(con, sql, rsh, params);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection con = JdbcUtils.getConnection();
T result = super.query(con, sql, rsh);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public int update(String sql) throws SQLException {
Connection con = JdbcUtils.getConnection();
int result = super.update(con, sql);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public int update(String sql, Object param) throws SQLException {
Connection con = JdbcUtils.getConnection();
int result = super.update(con, sql, param);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public int update(String sql, Object... params) throws SQLException {
Connection con = JdbcUtils.getConnection();
int result = super.update(con, sql, params);
JdbcUtils.releaseConnection(con);
return result;
}
}
4.3、AccountDao.java
在开发中，DAO层的职责应该只涉及到基本的CRUD，不涉及具体的业务操作。
package waf.yty.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
public class AccountDao {
@Test
public static void update(String name,double money) throws SQLException {
QueryRunner qr = new TxQueryRunner();
String sql = "update account set balance=balance+? where name=?";
Object[] params = {money,name};
// 自己提供连接,以保证多次使用的Connection为同一个连接!
qr.update(sql, params);
}
}
4.4、Demo1.java
package waf.yty.jdbc;
import java.sql.SQLException;
import org.junit.Test;
public class Demo1 {
private AccountDao dao = new AccountDao();
@Test
public void serviceMethod() throws Exception {
try {
JdbcUtils.beginTransaction();
dao.update("zs", -100);
if(true) throw new RuntimeException();
dao.update("ls", 100);
JdbcUtils.commitTransaction();
} catch (Exception e) {
try {
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
}
throw e;
}
}
}
1128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
