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 -- 返回列的頭一個值

代碼：

//建表語句<br />DROP TABLE IF EXISTS `test`.`user`;<br />CREATE TABLE `test`.`user` (<br /> `name` varchar(10) DEFAULT NULL,<br /> `password` varchar(10) DEFAULT NULL<br />) ENGINE=MyISAM DEFAULT CHARSET=latin1;<br />表中資料<br />'user1', 'pwd1'<br />'user2', 'pwd2'<br />//User類<br />public class User {<br />private String name;<br />private String pwd;<br />public User(){<br />}<br />public void setName(String val) {<br />this.name = val;<br />}<br />public void setPassword(String val) {<br />this.pwd = val;<br />}<br />public String getName() {<br />return name;<br />}<br />public String getPassword() {<br />return pwd;<br />}<br />}

package dbutiltest;<br />import java.sql.Connection;<br />import java.sql.DriverManager;<br />import java.sql.SQLException;<br />import java.util.List;<br />import java.util.Map;<br />import org.apache.commons.dbutils.DbUtils;<br />import org.apache.commons.dbutils.QueryRunner;<br />import org.apache.commons.dbutils.ResultSetHandler;<br />import org.apache.commons.dbutils.handlers.ArrayHandler;<br />import org.apache.commons.dbutils.handlers.ArrayListHandler;<br />import org.apache.commons.dbutils.handlers.BeanHandler;<br />import org.apache.commons.dbutils.handlers.BeanListHandler;<br />import org.apache.commons.dbutils.handlers.ColumnListHandler;<br />import org.apache.commons.dbutils.handlers.KeyedHandler;<br />import org.apache.commons.dbutils.handlers.MapListHandler;<br />public class TestDbUtils {</p><p>static {<br />try {<br />Class.forName("org.gjt.mm.mysql.Driver");<br />} catch (ClassNotFoundException e) {<br />e.printStackTrace();<br />System.exit(1);<br />}<br />}</p><p>public Connection getConnection() {<br />Connection conn = null;<br />try {<br />conn = DriverManager.getConnection(<br />"jdbc:mysql://localhost:3306/test", "root", "pwd");<br />} catch (SQLException e) {<br />e.printStackTrace();<br />}<br />return conn;<br />}</p><p>public static void main(String[] args) {<br />TestDbUtils u = new TestDbUtils();<br />u.testArrayHandler();<br />u.testArrayListHandler();<br />u.testBeanListHandler();<br />u.testMapListHandler();<br />u.testColumnListHandler();<br />u.testNonQuery();<br />}</p><p>public void testArrayHandler() {<br />System.out.println("----testArrayHandler----");<br />String sql = "select * from user";<br />ResultSetHandler handler = new ArrayHandler();<br />QueryRunner query = new QueryRunner();<br />Connection conn = null;<br />try {<br />conn = getConnection();<br />Object[] arr = (Object[])query.query(conn, sql, handler);<br />for (int i = 0; i < arr.length; i++) {<br />System.out.println(arr[i].toString());<br />}<br />} catch (SQLException e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />DbUtils.close(conn);<br />} catch (SQLException e) {<br />// ignore<br />}<br />}</p><p>}</p><p>public void testArrayListHandler() {<br />System.out.println("----testArrayListHandler----");<br />String sql = "select * from user";<br />ResultSetHandler handler = new ArrayListHandler();<br />QueryRunner query = new QueryRunner();<br />Connection conn = null;<br />try {<br />conn = getConnection();<br />List list = (List)query.query(conn, sql, handler);<br />for (int i = 0; i < list.size(); i++) {<br />Object[] arr = (Object[])list.get(i);<br />for (int j = 0; j < arr.length; j++) {<br />System.out.print(arr[j] + " ");<br />}<br />System.out.println();<br />}<br />} catch (SQLException e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />DbUtils.close(conn);<br />} catch (SQLException e) {<br />// ignore<br />}<br />}</p><p>}</p><p>public void testBeanListHandler() {<br />System.out.println("----testBeanListHandler----");<br />String sql = "select * from user where name=?";<br />Object[] params = new Object[]{"user1"};<br />ResultSetHandler handler = new BeanListHandler(User.class);<br />QueryRunner query = new QueryRunner();<br />Connection conn = null;<br />try {<br />conn = getConnection();<br />List list = (List)query.query(conn, sql, params, handler);<br />for (int i = 0; i < list.size(); i++) {<br />User user = (User)list.get(i);<br />System.out.println(user.getName() + " " + user.getPassword());<br />}<br />} catch (SQLException e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />DbUtils.close(conn);<br />} catch (SQLException e) {<br />// ignore<br />}<br />}<br />}</p><p>public void testMapListHandler() {<br />System.out.println("----testMapListHandler----");<br />String sql = "select * from user where name=?";<br />Object[] params = new Object[]{"user1"};<br />ResultSetHandler handler = new MapListHandler();<br />QueryRunner query = new QueryRunner();<br />Connection conn = null;<br />try {<br />conn = getConnection();<br />List list = (List)query.query(conn, sql, params, handler);<br />for (int i = 0; i < list.size(); i++) {<br />Map user = (Map)list.get(i);<br />System.out.println(user.get("name") + " " + user.get("password"));<br />}<br />} catch (SQLException e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />DbUtils.close(conn);<br />} catch (SQLException e) {<br />// ignore<br />}<br />}<br />}</p><p>public void testColumnListHandler() {<br />System.out.println("----testColumnListHandler----");<br />String sql = "select * from user";<br />ResultSetHandler handler = new ColumnListHandler("name");<br />QueryRunner query = new QueryRunner();<br />Connection conn = null;<br />try {<br />conn = getConnection();<br />List list = (List)query.query(conn, sql, handler);<br />for (int i = 0; i < list.size(); i++) {<br />System.out.println(list.get(i));<br />}<br />} catch (SQLException e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />DbUtils.close(conn);<br />} catch (SQLException e) {<br />// ignore<br />}<br />}<br />}</p><p>public void testKeyedHandler() {<br />System.out.println("----testKeyedHandler----");<br />String sql = "select * from user";<br />ResultSetHandler handler = new KeyedHandler("name");<br />QueryRunner query = new QueryRunner();<br />Connection conn = null;<br />try {<br />conn = getConnection();<br />Map map = (Map)query.query(conn, sql, handler);<br />Map user = (Map)map.get("user2");<br />System.out.println(user.get("password"));<br />} catch (SQLException e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />DbUtils.close(conn);<br />} catch (SQLException e) {<br />// ignore<br />}<br />}<br />}</p><p>public void testNonQuery() {<br />System.out.println("----testNonQuery----");<br />String sql = "insert into `user` values('user_test','pwd_test')";<br />QueryRunner query = new QueryRunner();<br />Connection conn = null;<br />try {<br />conn = getConnection();<br />query.update(conn, sql);<br />} catch (SQLException e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />DbUtils.close(conn);<br />} catch (SQLException e) {<br />// ignore<br />}<br />}<br />}<br />}<br />

 

 

關於wrappers包:

在建立
QueryRunner時，覆蓋父類的方法wrap

QueryRunner query = new QueryRunner() {<br />protected ResultSet wrap(ResultSet rs) {<br />return StringTrimmedResultSet.wrap(rs);<br />}<br />};

 

四、擴充

1.RowProcessor介面

2.ResultSetHandler介面