DBUtils 是对JDBC简单封装的开源工具类
详细介绍地址： https://baike.baidu.com/item/Dbutils/10655914?fr=aladdin
在使用DBUtils工具类之前需要DBUtil jar包 登录官方网址 下载最新jar包和API 也可以直接点击
解压下载的压缩包
主要用到 QueryRuner ResultSetHander 这些类
ResultSetHander 接口 有许多实现类
AbstractKeyedHandler, AbstractListHandler, ArrayHandler, ArrayListHandler, BaseResultSetHandler, BeanHandler, BeanListHandler, BeanMapHandler, ColumnListHandler, KeyedHandler, MapHandler, MapListHandler, ScalarHandler
QueryRuner 这个类有7个构造方法
| Constructor and Description |
|---|
QueryRunner()
Constructor for QueryRunner.
|
QueryRunner(boolean pmdKnownBroken)
Constructor for QueryRunner that controls the use of
ParameterMetaData.
|
QueryRunner(DataSource ds)
Constructor for QueryRunner that takes a
DataSource to use.
|
QueryRunner(DataSource ds, boolean pmdKnownBroken)
Constructor for QueryRunner that takes a
DataSource and controls the use of
ParameterMetaData.
|
QueryRunner(DataSource ds, boolean pmdKnownBroken, StatementConfiguration stmtConfig)
Constructor for QueryRunner that takes a
DataSource, a
StatementConfiguration, and controls the use of
ParameterMetaData.
|
QueryRunner(DataSource ds, StatementConfiguration stmtConfig)
Constructor for QueryRunner that takes a
DataSource to use and a
StatementConfiguration.
|
QueryRunner(StatementConfiguration stmtConfig)
Constructor for QueryRunner that takes a
StatementConfiguration to configure statements when preparing them.
|
今天主要用到queryRunner(DataSource ds) 构造
方法
int[] |
batch(Connection conn, String sql, Object[][] params)
Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
|
int[] |
batch(String sql, Object[][] params)
Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
|
int |
execute(Connection conn, String sql, Object... params)
Execute an SQL statement, including a stored procedure call, which does not return any result sets.
|
<T> List<T> |
execute(Connection conn, String sql, ResultSetHandler<T> rsh,Object... params)
Execute an SQL statement, including a stored procedure call, which returns one or more result sets.
|
int |
execute(String sql, Object... params)
Execute an SQL statement, including a stored procedure call, which does not return any result sets.
|
<T> List<T> |
execute(String sql, ResultSetHandler<T> rsh, Object... params)
Execute an SQL statement, including a stored procedure call, which returns one or more result sets.
|
<T> T |
insert(Connection conn, String sql, ResultSetHandler<T> rsh)
Execute an SQL INSERT query without replacement parameters.
|
<T> T |
insert(Connection conn, String sql, ResultSetHandler<T> rsh,Object... params)
Execute an SQL INSERT query.
|
<T> T |
insert(String sql, ResultSetHandler<T> rsh)
Executes the given INSERT SQL without any replacement parameters.
|
<T> T |
insert(String sql, ResultSetHandler<T> rsh, Object... params)
Executes the given INSERT SQL statement.
|
<T> T |
insertBatch(Connection conn, String sql, ResultSetHandler<T> rsh, Object[][] params)
Executes the given batch of INSERT SQL statements.
|
<T> T |
insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params)
Executes the given batch of INSERT SQL statements.
|
<T> T |
query(Connection conn, String sql, Object[] params, ResultSetHandler<T> rsh)
Deprecated.
|
<T> T |
query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh)
Deprecated.
|
<T> T |
query(Connection conn, String sql, ResultSetHandler<T> rsh)
Execute an SQL SELECT query without any replacement parameters.
|
<T> T |
query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
Execute an SQL SELECT query with replacement parameters.
|
<T> T |
query(String sql, Object[] params, ResultSetHandler<T> rsh)
Deprecated.
|
<T> T |
query(String sql, Object param, ResultSetHandler<T> rsh)
Deprecated.
|
<T> T |
query(String sql, ResultSetHandler<T> rsh)
Executes the given SELECT SQL without any replacement parameters.
|
<T> T |
query(String sql, ResultSetHandler<T> rsh, Object... params)
Executes the given SELECT SQL query and returns a result object.
|
int |
update(Connection conn, String sql)
Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
|
int |
update(Connection conn, String sql, Object... params)
Execute an SQL INSERT, UPDATE, or DELETE query.
|
int |
update(Connection conn, String sql, Object param)
Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter.
|
int |
update(String sql)
Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters.
|
int |
update(String sql, Object... params)
Executes the given INSERT, UPDATE, or DELETE SQL statement.
|
int |
update(String sql, Object param)
Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter.
|
public Object query(String sql, ResultSetHandler rsh)
public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException 之间的区别就是它不将数据库连接提供给方法，并且它是从提供给构造方法的数据源(DataSource) 或使用的setDataSource 方法中重新获得 Connection。
ResultSetHandler接口的实现类
- ArrayHandler：把结果集中的第一行数据转成对象数组。
- ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
- BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
- BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
- ColumnListHandler：将结果集中某一列的数据存放到List中。
- KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
- MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
- MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
方法的使用
使用前准备工具类 和配置文件
src 下添加 db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bookstore
username=root
password=root
使用DBCP连接池
DBCPUtils.java
package com.study.jdbc.Utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DBCPUtils {
private static DataSource dataSource;
static{
try {
//加载db.properties 配置文件
InputStream is=DBCPUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties props=new Properties();
props.load(is);
//创建数据源
dataSource=BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
操作：
package com.study.jdbc.test;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.sql.DataSource;
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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;
import com.study.jdbc.Utils.DBCPUtils;
public class DBUtilsQueryRunnerDBCP {
@Test
public void test1() throws SQLException{
DataSource dataSource=DBCPUtils.getDataSource();
QueryRunner qr=new QueryRunner(dataSource);
String sql="select * from user";
//ArrayHandler()把结果集第一行转化为对象数组
// qr.query() 执行查询sql语句
//qr.update() 执行更新sql语句
// qr.execute()执行sql语句
Object[] list=qr.query(sql, new ArrayHandler() );
for (Object i : list) {
System.out.print(i +" ");
}
}
@Test
public void test2() throws SQLException{
DataSource dataSource=DBCPUtils.getDataSource();
QueryRunner qr=new QueryRunner(dataSource);
String sql="select * from user";
List<Object[]> rs=qr.query(sql, new ArrayListHandler());
for (Object i : rs) {
for (Object[] j : rs) {
for (Object object : j) {
System.out.print(object);
}
System.out.println();
}
}
}
@Test
public void test3() throws SQLException{
DataSource dataSource=DBCPUtils.getDataSource();
QueryRunner qr=new QueryRunner(dataSource);
String sql="select * from user";
//MapListHandler把每条数据转化为Map对象
List<Map<String, Object>> map =qr.query(sql, new MapListHandler());
for (Map<String, Object> map2 : map) {
for (Entry<String, Object> en: map2.entrySet()) {
System.out.print(en.getKey()+" ="+en.getValue() +" ");
}
System.out.println();
}
}
public static void main(String[] args) throws SQLException {
DataSource dataSource=DBCPUtils.getDataSource();
QueryRunner qr=new QueryRunner(dataSource);
String sql="select * from user where userId=?";
Map<String, Object> map = qr.query(sql, new MapHandler() ,2);
for ( Entry<String, Object> i : map.entrySet()) {
System.out.print(i.getKey()+"="+i.getValue()+" ");
}
}
}
BeanListHandler
@Test
public void test2() throws SQLException{
DataSource dataSource=DBCPUtils.getDataSource();
QueryRunner qr=new QueryRunner(dataSource);
String sql="select * from user";
List<User> rs=qr.query(sql, new BeanListHandler<>(User.class));
for (Object user : rs) {
System.out.println(user.toString());
}
}