Python 官方文档：入门教程 => 点击学习
commons-dbutils 是 Apache 组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢
commons-dbutils 是 Apache 组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
DbUtils组件的主要作用是简化jdbc操作。
项目准备
1. 引入Mysql驱动：mysql-connector-java-5.1.38-bin.jar
2. 引入jar文件 : commons-dbutils-1.6.jar
下载dbutils组件: Http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi
DbUtils类的主要作用是：关闭资源、加载驱动。
QueryRunner类，位于org.apache.commons.dbutils包下，全名org.apache.commons.dbutils.QueryRunner
QueryRunner类是组件的核心工具类：定义了所有的与数据库操作的方法(查询、更新)
对于QueryRunner类的描述是Executes sql queries with pluggable strategies for handling ResultSets. This class is thread safe.
下面的话，要多读几遍：
【Executes SQL queries】 with 【pluggable strategies】 for 【handling ResultSets】.
QueryRunner类提供了默认的构造函数：public QueryRunner() ，在进行update、query等方式法需要传入Connection对象
QueryRunner类还提供了另外一个构造函数，接受DataSource类型的对象：public QueryRunner(DataSource ds)
对这个构造函数的描述是：Constructor for QueryRunner that takes a DataSource to use. Methods that do not take a Connection parameter will retrieve connections from this DataSource.使用不带Connnection参数的方法时，将从DataSource中获取Connection对象。
提供的update方法（带有Connection）
（1）public int update(Connection conn, String sql)
//描述：Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
（2）public int update(Connection conn, String sql, Object param)
//描述：Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter.
（3）public int update(Connection conn, String sql, Object... params)
//描述：Execute an SQL INSERT, UPDATE, or DELETE query.
提供的update方法（不带有Connection）
（1）public int update(String sql)
//描述：Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
（2）public int update(String sql, Object param)
//描述：Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
（3）public int update(String sql, Object... params)
//描述：Executes the given INSERT, UPDATE, or DELETE SQL statement. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
提供的query方法（带Connection对象）
（1）public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh)
//描述：Execute an SQL SELECT query without any replacement parameters. The caller is responsible for closing the connection.
（2）public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
//描述：Execute an SQL SELECT query with replacement parameters. The caller is responsible for closing the connection.
提供的query方法（不带有Connection）
（1）public <T> T query(String sql, ResultSetHandler<T> rsh)
//描述：Executes the given SELECT SQL without any replacement parameters. The Connection is retrieved from the DataSource set in the constructor.
（2）public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
//描述：Executes the given SELECT SQL query and returns a result object. The Connection is retrieved from the DataSource set in the constructor.
执行一次
package com.rk.demo;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import com.rk.utils.JDBCUtils;
//1. 更新
public class Demo01
{
public static void main(String[] args)
{
Connection conn = null;
try
{
//SQL语句
String sql = "INSERT INTO T_Dogs(Name,Age,BirthDay) VALUES(?,?,?)";
// 获取连接对象
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
//执行新增操作
qr.update(conn, sql, "旺财","2","2015-08-08");
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
// 关闭
DbUtils.closeQuietly(conn);
}
}
}批处理
package com.rk.demo;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import com.rk.utils.JDBCUtils;
//2. 批处理
public class Demo02
{
public static void main(String[] args)
{
Connection conn = null;
try
{
//SQL语句
String sql = "INSERT INTO T_Dogs(Name,Age,BirthDay) VALUES(?,?,?)";
// 获取连接对象
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 执行批量新增
qr.batch(conn, sql, new Object[][]{{"小狗1","1","2015-08-09"},{"小狗2","1","2015-08-10"}});
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
}1.自定义结果封装数据(自己写代码实现)
package com.rk.demo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFORMat;
import java.util.Date;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import com.rk.entity.DogInfo;
import com.rk.utils.JDBCUtils;
//1.查询， 自定义结果集封装数据
public class Demo03
{
public static void main(String[] args)
{
Connection conn = null;
try
{
String sql = "SELECT * FROM T_Dogs WHERE Id=?";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
DogInfo dog = qr.query(conn,sql, new ResultSetHandler<DogInfo>(){
// 如何封装一个DogInfo对象
@Override
public DogInfo handle(ResultSet rs) throws SQLException
{
DogInfo dog = null;
if (rs.next())
{
dog = new DogInfo();
dog.setId(rs.getInt("Id"));
dog.setName(rs.getString("Name"));
dog.setAge(rs.getInt("Age"));
String strBirthDay = rs.getDate("BirthDay").toString();
Date birthDay = null;
try
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
birthDay = sdf.parse(strBirthDay);
}
catch (ParseException e)
{
e.printStackTrace();
}
dog.setBirthDay(birthDay);
}
return dog;
}}, 2);
// 打印输出结果
System.out.println(dog);
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
//关闭Connection对象
DbUtils.closeQuietly(conn);
}
}
}2.使用BeanHandler、BeanListHandler和ScalarHandler
package com.rk.demo;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
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.ScalarHandler;
import org.junit.Test;
import com.rk.entity.DogInfo;
import com.rk.utils.JDBCUtils;
//2.查询， 使用组件提供的结果集对象封装数据
public class Demo04
{
@Test
// 1）BeanHandler: 查询返回单个对象
public void testBeanHandler()
{
Connection conn = null;
try
{
String sql = "SELECT * FROM T_Dogs WHERE Id=?";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询返回单个对象
DogInfo dog = qr.query(conn,sql, new BeanHandler<DogInfo>(DogInfo.class), 2);
//打印结果
System.out.println(dog);
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
// 2）BeanListHandler: 查询返回list集合，集合元素是指定的对象
public void testBeanListHandler()
{
Connection conn = null;
try
{
String sql = "SELECT * FROM T_Dogs";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询全部数据
List<DogInfo> list = qr.query(conn,sql, new BeanListHandler<DogInfo>(DogInfo.class));
//打印结果
for(DogInfo dog : list)
{
System.out.println(dog);
}
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
// 3）ScalarHandler: 查询返回指定“列名”或“索引”的值
public void testScalarHandler()
{
Connection conn = null;
try
{
String sql = "SELECT count(*) FROM T_Dogs";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询数量
Long count = qr.query(conn,sql, new ScalarHandler<Long>(1));//索引从1开始
//打印结果
System.out.println("共有"+count.intValue()+"个记录");
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
}3.使用其它的ResultSetHandler
package com.rk.demo;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.DbUtils;
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.BeanMapHandler;
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.junit.Test;
import com.rk.entity.DogInfo;
import com.rk.utils.JDBCUtils;
public class Demo05
{
@Test
//ArrayHandler返回的是一个Object[]对象，其中是同一条记录中各个列的值，如[3, 汪汪, 2, 2016-06-08]
public void testArrayHandler()
{
Connection conn = null;
try
{
String sql = "SELECT * FROM T_Dogs WHERE Id=?";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
Object[] objs = qr.query(conn,sql, new ArrayHandler(), 3);
//打印结果
System.out.println(Arrays.toString(objs));
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
//ArrayListHandler返回的是List<Object[]>对象
public void testArrayListHandler()
{
Connection conn = null;
try
{
String sql = "SELECT * FROM T_Dogs";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
List<Object[]> list = qr.query(conn,sql, new ArrayListHandler());
//打印结果
for(Object[] dog : list)
{
System.out.println(Arrays.toString(dog));
}
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
//BeanMapHandler: Creates a new instance of BeanMapHandler. The value of the first column of each row will be a key in the Map.
public void testBeanMapHandler()
{
Connection conn = null;
try
{
String sql = "SELECT Name,Age,BirthDay FROM T_Dogs";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
Map<String, DogInfo> map = qr.query(conn,sql, new BeanMapHandler<String, DogInfo>(DogInfo.class));
//打印结果
for(Map.Entry<String,DogInfo> item : map.entrySet())
{
System.out.println(item.geTKEy() + ":" + item.getValue());
}
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
//ColumnListHandler: Converts one ResultSet column into a List of Objects.
public void testColumnListHandler()
{
Connection conn = null;
try
{
String sql = "SELECT Name,Age,BirthDay FROM T_Dogs";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
List<String> list = qr.query(conn,sql, new ColumnListHandler<String>(1));
//打印结果
for(String item : list)
{
System.out.println(item);
}
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
//KeyedHandler: Returns a Map of Maps. ResultSet rows are converted into Maps which are then stored in a Map under the given key.
public void testKeyedHandler()
{
Connection conn = null;
try
{
String sql = "SELECT Id, Name,Age,BirthDay FROM T_Dogs";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
Map<String, Map<String, Object>> map = qr.query(conn,sql, new KeyedHandler<String>("Name"));
Map<String, Object> wcMap = map.get("旺财");
//打印结果
System.out.println(wcMap.get("Id"));
System.out.println(wcMap.get("Name"));
System.out.println(wcMap.get("Age"));
System.out.println(wcMap.get("BirthDay"));
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
//MapHandler: 将一条记录以Key/Value的形式保存于Map中，Key中值就是Column的值
public void testMapHandler()
{
Connection conn = null;
try
{
String sql = "SELECT Id, Name,Age,BirthDay FROM T_Dogs WHERE Id=?";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
Map<String, Object> map = qr.query(conn,sql, new MapHandler(),3);
//打印结果
for(Map.Entry<String,Object> item : map.entrySet())
{
System.out.println(item.getKey() + ":" + item.getValue());
}
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
@Test
//MapListHandler
public void testMapListHandler()
{
Connection conn = null;
try
{
String sql = "SELECT Id, Name,Age,BirthDay FROM T_Dogs WHERE Id";
// 获取连接
conn = JDBCUtils.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
// 查询
List<Map<String, Object>> list = qr.query(conn,sql, new MapListHandler());
//打印结果
for(Map<String, Object> map : list)
{
for(Map.Entry<String,Object> item : map.entrySet())
{
System.out.print(item.getKey() + ":" + item.getValue() + "\t");
}
System.out.println();
}
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
finally
{
DbUtils.closeQuietly(conn);
}
}
}DbUtils类，位于org.apache.commons.dbutils包下，全名org.apache.commons.dbutils.DbUtils
DbUtils类的主要作用是：关闭资源、加载驱动
DbUtils类的部分源码：
package org.apache.commons.dbutils;
import static java.sql.DriverManager.reGISterDriver;
import java.io.PrintWriter;
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverPropertyInfo;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.util.logging.Logger;
import java.util.Properties;
public final class DbUtils {
public DbUtils() {
// do nothing
}
public static void close(Connection conn) throws SQLException {
if (conn != null) {
conn.close();
}
}
public static void close(ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
}
public static void close(Statement stmt) throws SQLException {
if (stmt != null) {
stmt.close();
}
}
public static void closeQuietly(Connection conn) {
try {
close(conn);
} catch (SQLException e) { // NOPMD
// quiet
}
}
public static void closeQuietly(Connection conn, Statement stmt,
ResultSet rs) {
try {
closeQuietly(rs);
} finally {
try {
closeQuietly(stmt);
} finally {
closeQuietly(conn);
}
}
}
public static void closeQuietly(ResultSet rs) {
try {
close(rs);
} catch (SQLException e) { // NOPMD
// quiet
}
}
public static void closeQuietly(Statement stmt) {
try {
close(stmt);
} catch (SQLException e) { // NOPMD
// quiet
}
}
public static void commitAndClose(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.commit();
} finally {
conn.close();
}
}
}
public static void commitAndCloseQuietly(Connection conn) {
try {
commitAndClose(conn);
} catch (SQLException e) { // NOPMD
// quiet
}
}
public static boolean loadDriver(String driverClassName) {
return loadDriver(DbUtils.class.getClassLoader(), driverClassName);
}
public static boolean loadDriver(ClassLoader classLoader, String driverClassName) {
try {
Class<?> loadedClass = classLoader.loadClass(driverClassName);
if (!Driver.class.isAssignableFrom(loadedClass)) {
return false;
}
@SuppressWarnings("unchecked") // guarded by previous check
Class<Driver> driverClass = (Class<Driver>) loadedClass;
Constructor<Driver> driverConstructor = driverClass.getConstructor();
// make Constructor accessible if it is private
boolean isConstructorAccessible = driverConstructor.isAccessible();
if (!isConstructorAccessible) {
driverConstructor.setAccessible(true);
}
try {
Driver driver = driverConstructor.newInstance();
registerDriver(new DriverProxy(driver));
} finally {
driverConstructor.setAccessible(isConstructorAccessible);
}
return true;
} catch (RuntimeException e) {
return false;
} catch (Exception e) {
return false;
}
}
public static void rollback(Connection conn) throws SQLException {
if (conn != null) {
conn.rollback();
}
}
public static void rollbackAndClose(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.rollback();
} finally {
conn.close();
}
}
}
public static void rollbackAndCloseQuietly(Connection conn) {
try {
rollbackAndClose(conn);
} catch (SQLException e) { // NOPMD
// quiet
}
}
}update部分的源码
所有public修饰的update方法都会调用丰面这个方法。注意第二个参数boolean closeConn
private int update(Connection conn, boolean closeConn, String sql, Object... params)
public int update(Connection conn, String sql) throws SQLException {
return this.update(conn, false, sql, (Object[]) null);
}
public int update(Connection conn, String sql, Object param) throws SQLException {
return this.update(conn, false, sql, new Object[]{param});
}
public int update(Connection conn, String sql, Object... params) throws SQLException {
return update(conn, false, sql, params);
}
public int update(String sql) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, (Object[]) null);
}
public int update(String sql, Object param) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, new Object[]{param});
}
public int update(String sql, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, params);
}
private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
}
PreparedStatement stmt = null;
int rows = 0;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rows = stmt.executeUpdate();
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return rows;
}query部分的源码
所有的public修饰的query方法都会调用下面的方法。注意第二个参数boolean closeConn
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
return this.<T>query(conn, false, sql, rsh, params);
}
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
return this.<T>query(conn, false, sql, rsh, (Object[]) null);
}
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.<T>query(conn, true, sql, rsh, params);
}
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection conn = this.prepareConnection();
return this.<T>query(conn, true, sql, rsh, (Object[]) null);
}
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
}
if (rsh == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null ResultSetHandler");
}
PreparedStatement stmt = null;
ResultSet rs = null;
T result = null;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rs = this.wrap(stmt.executeQuery());
result = rsh.handle(rs);
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
try {
close(rs);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
}
return result;
}--结束END--
本文标题: 开源组件：（3）dbutils
本文链接: https://www.lsjlt.com/news/191141.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-03-01
2024-03-01
2024-03-01
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0