commons.dbutils1.2介绍及使用

一、结构介绍

高层结构图：

wrappers包：

handlers包（部分）:

二、功能介绍

commons.dbutils是一个对JDBC操作进行封装的类集，其有如下几个优点：

（1）没有可能的资源泄漏，避免了繁锁的JDBC代码

（2）代码更整洁

（3）从ResultSet自动生成JavaBeans属性

（4）无其他依赖包

三、基本使用

基本用到的类有：QueryRunner、ResultSetHandler及其子类等

QueryRunner – 执行查询的类，可以执行SELECT、INSERT、UPDATE、DELETE等语句，QueryRunner用ResultSetHandler的子类来处理ResultSet并返回结果；而包提供的ResultSetHandler子类使用RowProcessor的子类来处理ResultSet中的每一行；RowProcessor的默认实现为BasicRowProcessor；BeanProcessor不是RowProcessor，可以看作一个工具类

ResultHandler及其子类 – 实现了Object handle(ResultSet rs) throws SQLException方法

AbstractListHandler – 返回多行List的抽象类

ArrayHandler –  返回一行的Object[]

ArrayListHandler – 返回List，每行是Object[]

BeanHandler – 返回第一个Bean对象

BeanListHandler – 返回List，每行是Bean

ColumnListHandler – 返回一列的List

KeyedHandler – 返回Map，具体见代码

MapHandler – 返回单个Map

MapListHandler – 返回List，每行是Map

ScalarHandler – 返回列的头一个值

代码：

[java] view plaincopy

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
//建表语句
DROP TABLE IF EXISTS `test`.`user`;
CREATE TABLE  `test`.`user` (
  `name` varchar(10) DEFAULT NULL,
  `password` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
表中数据
'user1', 'pwd1'
'user2', 'pwd2'
//User类
public class User {
	private String name;
	private String pwd;
	public User(){
	}
	public void setName(String val) {
		this.name = val;
	}
	public void setPassword(String val) {
		this.pwd = val;
	}
	public String getName() {
		return name;
	}
	public String getPassword() {
		return pwd;
	}
}
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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
package dbutiltest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
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.ResultSetHandler;
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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
public class TestDbUtils {
	
	static {
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	public Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "pwd");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void main(String[] args) {
		TestDbUtils u = new TestDbUtils();
		u.testArrayHandler();
		u.testArrayListHandler();
		u.testBeanListHandler();
		u.testMapListHandler();
		u.testColumnListHandler();
		u.testNonQuery();
	}
	
	public void testArrayHandler() {
		System.out.println("----testArrayHandler----");
		String sql = "select * from user";
		ResultSetHandler handler = new ArrayHandler();
		QueryRunner query = new QueryRunner();
		Connection conn = null;
		try {
			conn = getConnection();
			Object[] arr = (Object[])query.query(conn, sql, handler);
			for (int i = 0; i < arr.length; i++) {
				System.out.println(arr[i].toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// ignore
			}
		}
		
	}
	
	public void testArrayListHandler() {
		System.out.println("----testArrayListHandler----");
		String sql = "select * from user";
		ResultSetHandler handler = new ArrayListHandler();
		QueryRunner query = new QueryRunner();
		Connection conn = null;
		try {
			conn = getConnection();
			List list = (List)query.query(conn, sql, handler);
			for (int i = 0; i < list.size(); i++) {
				Object[] arr = (Object[])list.get(i);
				for (int j = 0; j < arr.length; j++) {
					System.out.print(arr[j] + "  ");
				}
				System.out.println();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// ignore
			}
		}
		
	}
	
	public void testBeanListHandler() {
		System.out.println("----testBeanListHandler----");
		String sql = "select * from user where name=?";
		Object[] params = new Object[]{"user1"};
		ResultSetHandler handler = new BeanListHandler(User.class);
		QueryRunner query = new QueryRunner();
		Connection conn = null;
		try {
			conn = getConnection();
			List list = (List)query.query(conn, sql, params, handler);
			for (int i = 0; i < list.size(); i++) {
				User user = (User)list.get(i);
				System.out.println(user.getName() + "  " + user.getPassword());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// ignore
			}
		}
	}
	
	public void testMapListHandler() {
		System.out.println("----testMapListHandler----");
		String sql = "select * from user where name=?";
		Object[] params = new Object[]{"user1"};
		ResultSetHandler handler = new MapListHandler();
		QueryRunner query = new QueryRunner();
		Connection conn = null;
		try {
			conn = getConnection();
			List list = (List)query.query(conn, sql, params, handler);
			for (int i = 0; i < list.size(); i++) {
				Map user = (Map)list.get(i);
				System.out.println(user.get("name") + "  " + user.get("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// ignore
			}
		}
	}
	
	public void testColumnListHandler() {
		System.out.println("----testColumnListHandler----");
		String sql = "select * from user";
		ResultSetHandler handler = new ColumnListHandler("name");
		QueryRunner query = new QueryRunner();
		Connection conn = null;
		try {
			conn = getConnection();
			List list = (List)query.query(conn, sql, handler);
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// ignore
			}
		}
	}
	
	public void testKeyedHandler() {
		System.out.println("----testKeyedHandler----");
		String sql = "select * from user";
		ResultSetHandler handler = new KeyedHandler("name");
		QueryRunner query = new QueryRunner();
		Connection conn = null;
		try {
			conn = getConnection();
			Map map = (Map)query.query(conn, sql, handler);
			Map user = (Map)map.get("user2");
			System.out.println(user.get("password"));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// ignore
			}
		}
	}
	
	public void testNonQuery() {
		System.out.println("----testNonQuery----");
		String sql = "insert into `user` values('user_test','pwd_test')";
		QueryRunner query = new QueryRunner();
		Connection conn = null;
		try {
			conn = getConnection();
			query.update(conn, sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// ignore
			}
		}
	}
}

关于wrappers包:

在新建 QueryRunner时，覆盖父类的方法wrap

1
2
3
4
5
QueryRunner query = new QueryRunner() {
			protected ResultSet wrap(ResultSet rs) {
				return StringTrimmedResultSet.wrap(rs);
			}
		};

四、扩展

1.RowProcessor接口

2.ResultSetHandler接口