commons-dbutils
是Apache开源组织提供的用于操作数据库的工具包。今天为大家介绍一下该包的常用方法。
对于数据库的操作无外乎增删改查,而增删改本质上可以归为一类,操作方式相同,只是SQL语法不同而已,所以我将以修改和查询两类来介绍commons-dbutils
的用法。
首先我们来创建一个测试类,使用JUnit进行测试。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
public class DBUtilsTest
{
private Connection conn = null;
@Before
public void initConnection() throws SQLException, ClassNotFoundException
{
printCurrentMethodName();
Class.forName("org.h2.Driver");
conn = DriverManager.getConnection("jdbc:h2:h2.db", "test", "123");
}
@Before
public void initDatabase() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
runner.update(
conn,
"CREATE TABLE IF NOT EXISTS USER_INFO (userId VARCHAR(20) PRIMARY KEY, userName VARCHAR(50))");
}
@After
public void destory()
{
printCurrentMethodName();
DbUtils.closeQuietly(conn);
}
/**
* 打印当前运行方法名称
*/
public void printCurrentMethodName()
{
System.out.println(Thread.currentThread().getStackTrace()[2]
.getMethodName());
System.out.println("==================================================");
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
在这段测试代码中,我们是用h2
作为数据库进行测试以及定义了一些连接、数据库的初始化及销毁的方法。
在commons-dbutils
中操作数据库的常用类为:QueryRunner
。
QueryRunner
的常用方法如下:
返回值 | 方法名 | 说明 |
---|
int[] | batch(Connection conn, String sql, Object[][] params) | 批量执行INSERT、UPDATE或DELETE |
int[] | batch(String sql, Object[][] params) | 批量执行INSERT、UPDATE或DELETE |
T | insert(Connection conn, String sql, ResultSetHandler rsh) | 执行一个插入查询语句 |
T | insert(Connection conn, String sql, ResultSetHandler rsh, Object… params) | 执行一个插入查询语句 |
T | insert(String sql, ResultSetHandler rsh) | 执行一个插入查询语句 |
T | insert(String sql, ResultSetHandler rsh, Object… params) | 执行一个插入查询语句 |
T | insertBatch(Connection conn, String sql, ResultSetHandler rsh, Object[][] params) | 批量执行插入语句 |
T | insertBatch(String sql, ResultSetHandler rsh, Object[][] params) | 批量执行插入语句 |
T | query(Connection conn, String sql, ResultSetHandler rsh) | 查询 |
T | query(Connection conn, String sql, ResultSetHandler rsh, Object… params) | 查询 |
T | query(String sql, ResultSetHandler rsh) | 查询 |
T | query(String sql, ResultSetHandler rsh, Object… params) | 查询 |
int | update(Connection conn, String sql) | 执行INSERT、UPDATE或DELETE |
int | update(Connection conn, String sql, Object… params) | 执行INSERT、UPDATE或DELETE |
int | update(Connection conn, String sql, Object param) | 执行INSERT、UPDATE或DELETE |
int | update(String sql) | 执行INSERT、UPDATE或DELETE |
int | update(String sql, Object… params) | 执行INSERT、UPDATE或DELETE |
int | update(String sql, Object param) | 执行INSERT、UPDATE或DELETE |
修改
我们先来看一下如何使用QueryRunner
进行修改操作,在我们的测试代码中添加测试方法:
@Test
public void update() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
String suffix = Long.toHexString(System.currentTimeMillis());
Integer result = runner.update(conn,
"insert into USER_INFO(userId, userName) values(?, ?)", suffix,
"name" + suffix);
System.out.println("受影响记录条数:" + result);
}
修改的操作相比较而言还是很简单的,在这段测试代码中,我们向数据库中添加了一条记录,在QueryRunner
中也是支持动态参数的,可以很方便的绑定参数。
查询
ScalarHandler
ScalarHandler
会返回一个对象,用于读取结果集中第一行指定列的数据。这里我们以查询表中总记录数为例:
@Test
public void queryByScalarHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Number number = runner.query(conn, "select count(*) from USER_INFO",
new ScalarHandler<Number>());
System.out.println("总记录记录条数:" + number.intValue());
}
不知大家有没有发现,在ScalarHandler
的泛型中,我使用的是Number
,解释一下:之前我在使用该方法查询记录条数的时候,不同的数据库返回的数据类型可能不同,有的返回是Integer
,而有的却是Long
,为了代码的通用,所以在这里我使用了Number
。
ArrayHandler
ArrayHandler
会返回一个数组,用于将结果集第一行数据转换为数组。
@Test
public void queryByArrayHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Object[] results = runner.query(conn, "select * from USER_INFO",
new ArrayHandler());
System.out.println(Arrays.asList(results));
}
ArrayListHandler
ArrayListHandler
会返回一个集合,集合中的每一项对应结果集指定行中的数据转换后的数组。
@Test
public void queryByArrayListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<Object[]> results = runner.query(conn, "select * from USER_INFO",
new ArrayListHandler());
for (Object[] object : results)
{
System.out.println(Arrays.asList(object));
}
}
KeyedHandler
KeyedHandler
会返回一个Map
,我们可以指定某一列的值作为该Map
的键,Map
中的值为对应行数据转换的键值对,键为列名。
@Test
public void queryByKeyedHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Map<String, Map<String, Object>> results = runner.query(conn,
"select * from USER_INFO", new KeyedHandler<String>());
System.out.println(results);
}
ColumnListHandler
ColumnListHandler
会返回一个集合,集合中的数据为结果集中指定列的数据。
@Test
public void queryByColumnListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<String> results = runner.query(conn, "select * from USER_INFO",
new ColumnListHandler<String>());
System.out.println(results);
}
MapHandler
MapHandler
会将结果集中第一行数据转换为键值对,键为列名。
@Test
public void queryByMapHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Map<String, Object> results = runner.query(conn,
"select * from USER_INFO", new MapHandler());
System.out.println(results);
}
MapListHandler
MapHandler
会将结果集中的数据转换为一个集合,集合中的数据为对应行转换的键值对,键为列名
@Test
public void queryByMapListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<Map<String, Object>> results = runner.query(conn,
"select * from USER_INFO", new MapListHandler());
System.out.println(results);
}
BeanHandler
BeanHandler
实现了将结果集第一行数据转换为Bean
对象,在实际应用中非常方便。
在编写测试代码之前,我们先来编写一个对应的Bean
类:
import java.text.MessageFormat;
public class UserInfo
{
private String userId;
private String userName;
public String getUserId()
{
return userId;
}
public void setUserId(String userId)
{
this.userId = userId;
}
public String getUserName()
{
return userName;
}
public void setUserName(String userName)
{
this.userName = userName;
}
@Override
public String toString()
{
return MessageFormat
.format("[userId:{0},userName:{1}]", userId, userName);
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
接下来,我们来编写测试代码:
@Test
public void queryByBeanHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
UserInfo results = runner.query(conn, "select * from USER_INFO",
new BeanHandler<UserInfo>(UserInfo.class));
System.out.println(results);
}
BeanListHandler
BeanHandler
只转换结果集的第一行,而BeanListHandler
会将结果集的所有行进行转换,返回一个集合。
@Test
public void queryByBeanListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<UserInfo> results = runner.query(conn, "select * from USER_INFO",
new BeanListHandler<UserInfo>(UserInfo.class));
System.out.println(results);
}
BeanMapHandler
BeanMapHandler
也会将结果集转换为Bean
对象,不过返回的是已指定列的值作为键的键值对。
@Test
public void queryByBeanMapHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Map<String, UserInfo> results = runner.query(conn,
"select * from USER_INFO", new BeanMapHandler<String, UserInfo>(
UserInfo.class));
System.out.println(results);
}
完整测试代码
import java.sql.Connection;
import java.sql.DriverManager;
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.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.After;
import org.junit.Before;
public class DBUtilsTest
{
private Connection conn = null;
@Before
public void initConnection() throws SQLException, ClassNotFoundException
{
printCurrentMethodName();
Class.forName("org.h2.Driver");
conn = DriverManager.getConnection("jdbc:h2:h2.db", "test", "123");
}
@Before
public void initDatabase() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
runner.update(
conn,
"CREATE TABLE IF NOT EXISTS USER_INFO (userId VARCHAR(20) PRIMARY KEY, userName VARCHAR(50))");
}
public void update() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
String suffix = Long.toHexString(System.currentTimeMillis());
Integer result = runner.update(conn,
"insert into USER_INFO(userId, userName) values(?, ?)", suffix,
"name" + suffix);
System.out.println("受影响记录条数:" + result);
}
public void queryByScalarHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Number number = runner.query(conn, "select count(*) from USER_INFO",
new ScalarHandler<Number>());
System.out.println("总记录记录条数:" + number.intValue());
}
public void queryByArrayHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Object[] results = runner.query(conn, "select * from USER_INFO",
new ArrayHandler());
System.out.println(Arrays.asList(results));
}
public void queryByArrayListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<Object[]> results = runner.query(conn, "select * from USER_INFO",
new ArrayListHandler());
for (Object[] object : results)
{
System.out.println(Arrays.asList(object));
}
}
public void queryByKeyedHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Map<String, Map<String, Object>> results = runner.query(conn,
"select * from USER_INFO", new KeyedHandler<String>());
System.out.println(results);
}
public void queryByColumnListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<String> results = runner.query(conn, "select * from USER_INFO",
new ColumnListHandler<String>());
System.out.println(results);
}
public void queryByMapHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Map<String, Object> results = runner.query(conn,
"select * from USER_INFO", new MapHandler());
System.out.println(results);
}
public void queryByMapListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<Map<String, Object>> results = runner.query(conn,
"select * from USER_INFO", new MapListHandler());
System.out.println(results);
}
public void queryByBeanHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
UserInfo results = runner.query(conn, "select * from USER_INFO",
new BeanHandler<UserInfo>(UserInfo.class));
System.out.println(results);
}
public void queryByBeanListHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
List<UserInfo> results = runner.query(conn, "select * from USER_INFO",
new BeanListHandler<UserInfo>(UserInfo.class));
System.out.println(results);
}
public void queryByBeanMapHandler() throws SQLException
{
printCurrentMethodName();
QueryRunner runner = new QueryRunner();
Map<String, UserInfo> results = runner.query(conn,
"select * from USER_INFO", new BeanMapHandler<String, UserInfo>(
UserInfo.class));
System.out.println(results);
}
@After
public void destory()
{
printCurrentMethodName();
DbUtils.closeQuietly(conn);
}
/**
* 打印当前运行方法名称
*/
public void printCurrentMethodName()
{
System.out.println(Thread.currentThread().getStackTrace()[2]
.getMethodName());
System.out.println("==================================================");
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166