DBUtils包括3個包:
org.apache.commons.dbutils
org.apache.commons.dbutils.handlers
org.apache.commons.dbutils.wrappers
DBUtils封裝了對JDBC的操作,簡化了JDBC操作,可以少寫代碼。
DbUtils 關閉連結等操作
QueryRunner 進行查詢的操作
ArrayHandler :将ResultSet中第一行的資料轉化成對象數組
ArrayListHandler将ResultSet中所有的資料轉化成List,List中存放的是Object[]
BeanHandler :将ResultSet中第一行的資料轉化成類對象
BeanListHandler :将ResultSet中所有的資料轉化成List,List中存放的是類對象
ColumnListHandler :将ResultSet中某一列的資料存成List,List中存放的是Object對象
KeyedHandler :将ResultSet中存成映射,key為某一列對應為Map。Map中存放的是資料
MapHandler :将ResultSet中第一行的資料存成Map映射
MapListHandler :将ResultSet中所有的資料存成List。List中存放的是Map
ScalarHandler :将ResultSet中一條記錄的其中某一列的資料存成Object
SqlNullCheckedResultSet :對ResultSet進行操作,改版裡面的值
StringTrimmedResultSet :去除ResultSet中中字段的左右空格。Trim[size=large]
dbutils 開源項目用法:
項目位址:http://commons.apache.org/dbutils/
最新版本為 1.1
英文文檔中這樣寫到:
DbUtils is a very small library of classes so it won't take long to go throughthe javadocs for each class.
The core classes/interfaces in DbUtils are QueryRunner and ResultSetHandler.
You don't need to know about any other DbUtils classes to benefit from usingthe library.
大概意思是: DbUtils 是一個非常小的類庫是以你不需要花費太長的時間去研究javadocs
它的核心接口是 QueryRunner 和ResultSetHandler
你不需要關心其他DbUtils的類如何使用
-------------------------------------------------------------------------------------------------------------------------
QueryRunner
構造 QueryRunner :
QueryRunner()
QueryRunner(javax.sql.DataSource ds)
有兩種方法來構造QueryRunner 一種是無參數的一種是有DataSource類型參數的構造方法這裡使用DataSource來構造它使用連接配接池來構造它的好處就是我們無需管Connection對象的建立與關閉了後面調用QueryRunner對象的方法也無需傳入Connection對象了那我們就要用到另外一個開源資料庫連接配接池元件dbcp以下為dbcp的用法:
Java代碼
public class DBUtils {
private static Properties properties;
private static DataSource dataSource;
private static Connection conn;
static {
try {
properties = new Properties();
//載入dbcp的配置檔案
properties.load(DBUtils.class..getResourceAsStream("/dbcpconfig.properties"));
//建立個BasicDataSourceFactory對象用于建立連接配接池對象
BasicDataSourceFactory b = new BasicDataSourceFactory();
//把properties對象設定給BasicDataSourceFactory
dataSource = b.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
public static DataSource getdataSource() {
return dataSource;
}
public class DBUtils {
private static Properties properties;
private static DataSource dataSource;
private static Connection conn;
static {
try {
properties = new Properties();
//載入dbcp的配置檔案
properties.load(DBUtils.class..getResourceAsStream("/dbcpconfig.properties"));
BasicDataSourceFactory b = new BasicDataSourceFactory();
//把properties對象設定給BasicDataSourceFactory
dataSource = b.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
public static DataSource getdataSource() {
return dataSource;
注意:需要導入dbcp連接配接池的jarcommons-dbcp-1.2.1.jar 和所依賴的commons-pool-1.2.jar
commons-collections-3.1.jar
//這樣我們就可以構造個queryRunner對象了
QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource());
QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource());
QueryRunner 主要方法:
//更新一列
queryRunner.update("update Users set username=?,password=? where id = ?", new String[]{"11","22","1"});
//删除一列
queryRunner.update("delete from Users where id =?", 1);
queryRunner.update("update Users setusername=?,password=? where id = ?", newString[]{"11","22","1"});
queryRunner.update("delete from Users where id =?", 1);
--------------------------------------------------------------------------------------------------------------
如果要使用 QueryRunner的查詢方法也就是query()方法的話那麼就需要有對象來接收查詢出來的ResultSet
用什麼來接收呢?要用到實作ResultSetHandler接口的類
1. BeanHandler 傳回個entitybean對象
//指定傳回哪個 entitybean
ResultSetHandler rsh = new BeanHandler(Users.class);
Users users = (Users) queryRunner.query("select * from Users where id=?",2, rsh);
System.out.println(users.getUsername());
//指定傳回哪個 entitybean
ResultSetHandler rsh = new BeanHandler(Users.class);
Users users = (Users) queryRunner.query("select * from Userswhere id=?",2, rsh);
System.out.println(users.getUsername());
2. BeanListHandler 傳回個List List裡裝的是entitybean對象
ResultSetHandler rsh = new BeanListHandler(Users.class);
List<Users> list = (List) queryRunner.query(
"select * from Users ", rsh);
for(Users user:list){
System.out.println(user.getUsername());
}
ResultSetHandler rsh = new BeanListHandler(Users.class);
List<Users> list = (List) queryRunner.query(
"select * from Users ", rsh);
for(Users user:list){
System.out.println(user.getUsername());
3. ArrayListHandler 傳回List 對象 list裡裝的為Object數組
// 使用ArrayListHandler 實作類來存放結果會傳回ArrayList對象
ResultSetHandler rsh = new ArrayListHandler();
//list裡裝的是對象數組
List<Object[]> list = (List) queryRunner.query(
for(Object[] user:list) {
for(Object obj:user)
{
System.out.println(obj);
}
ResultSetHandler rsh = new ArrayListHandler();
List<Object[]> list = (List) queryRunner.query(
for(Object[] user:list) {
for(Object obj:user)
{
System.out.println(obj);
-------------------------------------------------------------------------------------------------------------
4. ColumnListHandler 傳回List 對象
注意: 構造ColumnListHandler() 對象時候如果是無參數的話
像這樣 ResultSetHandler rsh= new ColumnListHandler();
查詢出來的List 裡放的類型為主鍵的類型值為主鍵的值;
ResultSetHandler rsh = new ColumnListHandler();
List<Integer> list = (List) queryRunner.query( "select * from Users ", rsh);
//System.out.println(list.get(0).getClass().getSimpleName());
for(Integer in:list){
System.out.println(in);
}
ResultSetHandler rsh = new ColumnListHandler();
List<Integer> list = (List) queryRunner.query("select *from Users ", rsh);
//System.out.println(list.get(0).getClass().getSimpleName());
for(Integer in:list){
System.out.println(in);
}
構造 ColumnListHandler() 對象時候如果是有參數的話像這樣 ResultSetHandler rsh = newColumnListHandler(主鍵值); 那麼查詢出來的List 裡放的是就是這個主鍵對應的一行的記錄; Java代碼
ResultSetHandler rsh = new ColumnListHandler(2);
List<String> list = (List) queryRunner.query(
"select * from Users ", rsh);
for(String in:list){
ResultSetHandler rsh = new ColumnListHandler(2);
List<String> list = (List) queryRunner.query("select *from Users ", rsh);
for(String in:list){
5. KeyedHandler 根據鍵傳回Map
ResultSetHandler rsh = new KeyedHandler();
//map裡裝的key為主鍵的值 value 為主鍵對應的那行記錄名稱和記錄
Map map = (Map) queryRunner.query("select id,username,password from Users ", rsh);
//根據主鍵值出去那一列
// Map t = (Map) map.get(2);
System.out.println(map.get(2));
ResultSetHandler rsh = new KeyedHandler();
//map裡裝的key為主鍵的值 value 為主鍵對應的那行記錄名稱和記錄
Map map = (Map)queryRunner.query("select id,username,password from Users ", rsh);
//根據主鍵值出去那一列
// Map t = (Map) map.get(2);
System.out.println(map.get(2));
---------------------------------------------------------------------------------------------------------------
6. MapHandler 根據鍵傳回Map
ResultSetHandler rsh = new MapHandler();
Map map = (Map) queryRunner.query("select * from Users ", rsh); System.out.println(map);
QueryRunner queryRunner = newQueryRunner(DBUtils.getdataSource());
ResultSetHandler rsh = new MapHandler();
Map map = (Map) queryRunner.query(
System.out.println(map);
7. ScalarHandler 根據鍵傳回第一行記錄
// 這樣就會傳回第一行記錄名字為username的值
ResultSetHandler rsh = new ScalarHandler("username");
//這的類型根據表裡資料的類型而定如果上面
ResultSetHandler rsh = new ScalarHandler("username");
//取出來的類型為int的話那麼這裡也必須由int 來接收
String result = (String) queryRunner.query("select * from Users where id=99 ", rsh);
System.out.println(result);