DBUtiles是一個很好的處理JDBC的工具類。(DbUtils is a small set of classes designed to make working with JDBC easier )
DBUtiles中的QueryRunner和ResultSetHandler的手動實作
其中比較常用的就是QueryRunner類和ResultSetHandler接口。通過它們可以很友善的實作JDBC的功能。
QueryRunner類，有四個構造方法，其中有的構造方法可以接受一個DataSource
例如：QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
當我們獲得QueryRunner的執行個體對象時，就能通過QueryRunner類的方法友善的操作資料庫。QueryRunner類主要有三類方法，batch()方法，query()方法，update()方法。
例如：
QueryRunner runner=new QueryRunner(new ComboPooledDataSource());
runner.update("insert into account values(null,?,?)","e",888);
runner.update("update account set money=0 where name=?", "e");
查詢的方法稍微麻煩一點，因為我們需要對查詢到的結果集進行設定。通常需要把結果集ResultSet封裝到JavaBean或者集合或者數組中。
檢視一個方法： <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
這裡第一個參數是sql語句字元串，第二個參數是一個實作了ResultSetHandler接口的類對象，第三個參數是Object類型的可變參數。傳回值是一個T類型。
如果我們用的eclipse或者MyEclipse 滑鼠放到ResutlSetHandlet上面，按F2，會有針對T的說明。<T> the target type the input ResultSet will be converted to.
意思是，T 代表 ResultSet結果集要裝入的目标類型。也就是我們前面提到的數組，集合，甚至javabean.
下面用一段代碼來實作把結果集裝入一個List數組中。其中Account是一個javaBean，符合account表。
public static List test2() throws Exception{
QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
return runner.query("select * from account where name=?",new ResultSetHandler<List<Account>>(){
public List<Account> handle(ResultSet rs) throws SQLException {
List<Account> list = new ArrayList<Account>();
while(rs.next()){
Account acc = new Account();
acc.setId(rs.getInt("id"));
acc.setName(rs.getString("name"));
acc.setMoney(rs.getDouble("money"));
list.add(acc);
}
return list;
}
} , "a");
}
接下來，我們用兩段代碼來模拟QueryRunner和ResultSetHandler的實作原理。
package cn.itheima.dbutils;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import cn.itheima.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class MyQueryRunner {
private DataSource source = null;
public MyQueryRunner(DataSource source) {
this.source = source;
}
//查詢原理：利用MyResourceHandler處理利用sql和objs拼寫出來的sql語句查詢出來的resultSet，處理
public <T> T query(String sql,MyResultSetHandler<T> handler,Object ...objs){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = source.getConnection();
ps = conn.prepareStatement(sql);
ParameterMetaData metaData = ps.getParameterMetaData();
for(int i=1;i<=metaData.getParameterCount();i++){
ps.setObject(i, objs[i-1]);
}
rs = ps.executeQuery();
return handler.handle(rs);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ps = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
}
public int update(String sql,Object ...objs){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = source.getConnection();
ps = conn.prepareStatement(sql);
ParameterMetaData metaData = ps.getParameterMetaData();
for(int i=1;i<=metaData.getParameterCount();i++){
ps.setObject(i, objs[i-1]);
}
return ps.executeUpdate();
}catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}finally{
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
ps = null;
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
}
}
MyResultSetHandler接口
package cn.itheima.dbutils;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface MyResultSetHandler <T>{
T handle(ResultSet rs)throws SQLException;
}
當然，實際應用中沒有這麼麻煩。因為DBUtils已經幫我們實作了很多ResultSetHandler的實作類。通過這些類可以很友善的對結果集進行封裝。
ResultSetHandler的實作類
//1.ArrayHandler 将查詢結果每一行轉換為一個數組對象傳回
//ResultSetHandler implementation that converts a ResultSet into an Object[]. This class is thread safe.
Object[] objs = runner.query("select * from account where name=?",new ArrayHandler() , "c");
System.out.println(objs);
//2.ArrayListHandler 将查詢結果的每一行轉換為一個Object[]數組，然後裝入一個ArrayList集合
//ResultSetHandler implementation that converts the ResultSet into a List of Object[]s. This class is thread safe.
List<Object[]> list = runner.query("select * from account",new ArrayListHandler() );
System.out.println(list);
//手動實作ArrayListHandler的功能
public static List test2() throws Exception{
QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
return runner.query("select * from account where name=?",new ResultSetHandler<List<Account>>(){
public List<Account> handle(ResultSet rs) throws SQLException {
List<Account> list = new ArrayList<Account>();
while(rs.next()){
Account acc = new Account();
acc.setId(rs.getInt("id"));
acc.setName(rs.getString("name"));
acc.setMoney(rs.getDouble("money"));
list.add(acc);
}
return list;
}
} , "a");
}
//3.BeanHandler,将查詢結果的第一行轉換為一個JavaBean對象傳回
//ResultSetHandler implementation that converts the first ResultSet row into a JavaBean. This class is thread safe.
Account acc = runner.query("select * from account where name=?",new BeanHandler<Account>(Account.class) , "c");
System.out.println(acc);
//4.BeanListHandler：将結果集中的每一行資料都封裝到一個對應的JavaBean執行個體中，存放到List裡。
//ResultSetHandler implementation that converts a ResultSet into a List of beans. This class is thread safe.
List<Account> acclist = runner.query("select * from account",new BeanListHandler<Account>(Account.class) );
System.out.println(acclist);
//5.MapHandler：将結果集中的第一行資料封裝到一個Map裡，key是列名，value就是對應的值。
//ResultSetHandler implementation that converts the first ResultSet row into a Map. This class is thread safe.
Map map = runner.query("select * from account",new MapHandler() );
System.out.println(map);
//6.MapListHandler：将結果集中的每一行資料都封裝到一個Map裡，然後再存放到List
//ResultSetHandler implementation that converts a ResultSet into a List of Maps. This class is thread safe
List<Map<String, Object>> maplist = runner.query("select * from account",new MapListHandler() );
System.out.println(maplist);
//7.ColumnListHandler：将結果集中某一列的資料存放到List中。
//ResultSetHandler implementation that converts one ResultSet column into a List of Objects. This class is thread safe.
List<Object> columnList = runner.query("select * from account",new ColumnListHandler(2) );
System.out.println(columnList);
//8.KeyedHandler(name)：将結果集中的每一行資料都封裝到一個Map裡(List<Map>)，再把這些map再存到一個map裡，其key為指定的列。
//ResultSetHandler implementation that returns a Map of Maps. ResultSet rows are converted into Maps which are then stored in a Map under the given key.
Map<Object, Map<String, Object>> keymap = runner.query("select * from account",new KeyedHandler("id") );
System.out.println(keymap);
//9.ScalarHandler: 單值查詢
//ResultSetHandler implementation that converts one ResultSet column into an Object. This class is thread safe.
//select count(*) from account;
Long count = (Long)runner.query("select count(*) from account",new ScalarHandler(1) );
System.out.println(count);
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)