1.DbUtils简介
DbUtils是Apache下的一个开源项目,是一个小巧的JDBC操作库。这个库的最主要两个类是QueryRunner和ResultSetHandler类。QueryRunner用于执行数据库查询语句,ResultSetHandler用于处理数据库执行后返回的ResultSet结果。ResultSetHandler是一个接口,在使用的过程中下不能直接使用。DbUtils提供了几个ResultSetHandler的实现类,分别是
这些类都是在org.apache.commons.dbutils.handlers包下可以找到。
2.DbUtils的两种数据库连接方式
1.Datasource 连接方式
QueryRunner run = new QueryRunner(dataSource);
Object[] result = run.query("SELECT * FROM Person WHERE name=?", h, "John Doe");
dataSource是在创建QueryRunner时,作为参数传递进去的。datasource的创建具体过程,这里就不提供了,有需要的请自行搜索一下。
2.Connection连接方式
QueryRunner run = new QueryRunner();
Connection conn = ... // open a connection
Object[] result = run.query(conn, "SELECT * FROM Person WHERE name=?", h, "John Doe");
这种方式,开发者需要自行获得数据库的连接对象,并且在执行SQL语句的时候,作为query函数的一个参数。这也是和使用数据源连接的区别。
3.数据的插入或更新
public void test7() throws SQLException {
Object[]parms={"101","Bookname","jack","compnay","item"};
String sql = "insert into books(isbn,name,author,company,item) values(?,?,?,?,?)";
QueryRunner run = new QueryRunner();
run.update(DBHelper.getConncttion(), sql, parms);
}
如果了解JDBC的Preparstatement的是使用,上面的这段代码就很容易理解。update函数的实现就是用Preparstatement方式实现的。
4.数据的查询
先给出官方文档上给的一个例子.
Demo1
public void test() throws SQLException {
ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>() {
@Override
public Object[] handle(ResultSet rs) throws SQLException {
if (!rs.next()) {
return null;
}
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for (int i = 0; i < cols; i++) {
result[i] = rs.getObject(i + 1);
}
return result;
}
};
QueryRunner run = new QueryRunner();
String sql = "SELECT * FROM books";
//return a single row as a Object list,
//each field is stored in a elment in the list
Object[] result = run.query(DBHelper.getConncttion(), sql, h);
for (Object o : result) {
System.out.println(o.toString());
}
}
上面例子中,开发者实现自己的ResultSetHandler,它返回查询结果的第一条记录,这条记录是以Object数组保存的。
ArrayHandler, ArrayListHandler,
BaseResultSetHandler, BeanHandler, BeanListHandler, BeanMapHandler,
ColumnListHandler, KeyedHandler,
MapHandler, MapListHandler,
ScalarHandler
BaseResultSetHandler, BeanHandler, BeanListHandler, BeanMapHandler,
ColumnListHandler, KeyedHandler,
MapHandler, MapListHandler,
ScalarHandler
下面将介绍上面的几个类的使用。
1.ArrayHandler类
Demo2:
/**
* return the first row of the ResultSet
*
* @throws java.sql.SQLException
*/
public void test1() throws SQLException {
ResultSetHandler<Object[]> h2 = new ArrayHandler();
QueryRunner run = new QueryRunner();
String sql = "SELECT * FROM books";
//return a single row as a Object list,
//each field is stored in a element in the list
Object[] result = run.query(DBHelper.getConncttion(), sql, h2);
for (Object o : result) {
System.out.println(o.toString());
}
}
上面大代码执行后将返回查询结果的第一条记录,是ArrayList的形式。下面是执行的结果。实际上ArrayHandler的处理是和上面官方给的demo1的例子的效果是一样的。
1
数学建模简明教程
戴朝寿
高等教育出版社
理工
2008-06-10 21:39:53.0
0
2.ArrayListHandler类
Demo3:
/**
* fetch all rows as a arraylist; each row is stored the element in the
* array. each row is a array format. that is Object[] type.
*
* @throws SQLException
*/
public void test2() throws SQLException {
ResultSetHandler h = new ArrayListHandler();
QueryRunner run = new QueryRunner();
String sql = "SELECT * FROM books";
ArrayList<Object[]> list = (ArrayList<Object[]>) run.query(DBHelper.getConncttion(), sql, h);
//System.out.println(list.size());
for (Object b : list) {
Object[] arr1 = (Object[]) b;
for (Object arr11 : arr1) {
System.out.println(arr11);
}
System.out.println("===============================");
}
}
代码的执行结果是返回所有的记录行,这些记录行保存在ArrayList中,ArrayList中的每一个元素是一条记录。每条记录是一个Object数组。
ArrayHandler和ArrayListHandler的区别是,ArrayHandler只返回一条记录,ArrayListHandler返回所有的记录。
因此上面的Demo3的执行结果如下:
1
数学建模简明教程
戴朝寿
高等教育出版社
理工
2008-06-10 21:39:53.0
0
===============================
2
JavaEE编程技术
赫玉龙
清华大学出版社
理工
2008-06-10 21:59:14.0
0
===============================
3
开发者突击:Java Web整合开发(第2版)
刘中兵
电子工业出版社
理工
2008-06-10 23:48:09.0
0
===============================
4
软件设计与体系结构
齐治昌
高等教育出版社
理工
2008-06-10 23:48:19.0
0
===============================
3.MapHandler
Demo4:
/**
* return thr first row as a map object
*
* @throws SQLException
*/
public void test3() throws SQLException {
ResultSetHandler h = new MapHandler();
QueryRunner run = new QueryRunner();
String sql = "SELECT * FROM books";
HashMap map = (HashMap) run.query(DBHelper.getConncttion(), sql, h);
for (Object key : map.keySet()) {
System.out.println(map.get(key));
}
}
使用MapHandle类,query函数执行的结果是返回第一条记录,记录是以HashMap的形式保存的。执行结果:
戴朝寿
2008-06-10 21:39:53.0
1
理工
高等教育出版社
数学建模简明教程
0
4.MapListHandler
Demo5:
/**
* get all row and store in a list,in which all the elments are Map type.
* each map has all the field store as the key-value pair.
*
* @throws SQLException
*/
public void test4() throws SQLException {
ResultSetHandler h = new MapListHandler();
QueryRunner run = new QueryRunner();
String sql = "SELECT * FROM books";
ArrayList list = (ArrayList) run.query(DBHelper.getConncttion(), sql, h);
System.out.println(list.size());
for (Object b : list) {
HashMap map = (HashMap) b;
for (Object key : map.keySet()) {
System.out.println(map.get(key));
}
System.out.println("===============================");
}
}
使用MapListHandler时,query的执行结果将返回所有的记录,这些记录保存在 ArrayList对象中。ArrayList的每个元素都是Map类型的对象,代表一条记录。执行即结果:
16
戴朝寿
2008-06-10 21:39:53.0
1
理工
高等教育出版社
数学建模简明教程
0
===============================
赫玉龙
2008-06-10 21:59:14.0
2
理工
清华大学出版社
JavaEE编程技术
0
===============================
刘中兵
2008-06-10 23:48:09.0
3
理工
电子工业出版社
开发者突击:Java Web整合开发(第2版)
0
===============================
5.BeanHandler
Demo6:
/**
* can only get a row as a bean.
*
* @throws SQLException
*/
public void test5() throws SQLException {
ResultSetHandler h = new BeanHandler(Book.class);
QueryRunner run = new QueryRunner();
String sql = "SELECT * FROM books";
Book b = (Book) run.query(DBHelper.getConncttion(), sql, h);
System.out.println(b);
}
当使用BeanHandler作为ResultSetHandler时,query的实行结果将会返回查询结果集的第一条记录。并且这条自己保存在Book类中,相当于将数据库的一条记录映射到一个类中了。数据库中的每个数据库字段对应Book类的每一个字段。需要注意的是,数据库字段和类的字段名字需要一致(大小写不敏感),当数据库的字段和类的字段不一样时,可以在sql语句中使用
SELECT s_name AS name FROM book
这样的方法。Demo6的执行结果如下:
Book[isbn=1, name=数学建模简明教程, author=戴朝寿, company=高等教育出版社, item=理工, time=2008-06-10 21:39:53.0, number=0]
6.BeanListHandler
Demo7:
/**
* get all the rows from the table
*
* @throws SQLException
*/
public void test6() throws SQLException {
ResultSetHandler<List<Book>> h = new BeanListHandler(Book.class);
QueryRunner run = new QueryRunner();
String sql = "SELECT * FROM books";
List<Book> books = run.query(DBHelper.getConncttion(), sql, h);
for (Book b : books) {
System.out.println(b);
}
}
Book[isbn=1, name=数学建模简明教程, author=戴朝寿, company=高等教育出版社, item=理工, time=2008-06-10 21:39:53.0, number=0]
Book[isbn=2, name=JavaEE编程技术, author=赫玉龙, company=清华大学出版社, item=理工, time=2008-06-10 21:59:14.0, number=0]
Book[isbn=3, name=开发者突击:Java Web整合开发(第2版), author=刘中兵, company=电子工业出版社, item=理工, time=2008-06-10 23:48:09.0, number=0]
Book[isbn=4, name=软件设计与体系结构, author=齐治昌, company=高等教育出版社, item=理工, time=2008-06-10 23:48:19.0, number=0]
下面是例子中使用到的其他类:
package com.test.dbutil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author Justin
*/
public class DBHelper {
private final static String username = "root";
private final static String passwd = "admin";
private final static String driver = "com.mysql.jdbc.Driver";
private final static String url = "jdbc:mysql://localhost:3306/bookshop";
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static Connection getConncttion() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, passwd);
} catch (SQLException ex) {
Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
}
return conn;
}
}
Book类:
package com.test.dbutil;
import java.util.Date;
/**
*
* @author Justin
*/
public class Book {
private String isbn;
private String name;
private String author;
private String company;
private String item;
private Date time;
private long number;
/**
* @return the isbn
*/
public String getIsbn() {
return isbn;
}
/**
* @param isbn the isbn to set
*/
public void setIsbn(String isbn) {
this.isbn = isbn;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the author
*/
public String getAuthor() {
return author;
}
/**
* @param author the author to set
*/
public void setAuthor(String author) {
this.author = author;
}
/**
* @return the company
*/
public String getCompany() {
return company;
}
/**
* @param company the company to set
*/
public void setCompany(String company) {
this.company = company;
}
/**
* @return the item
*/
public String getItem() {
return item;
}
/**
* @param item the item to set
*/
public void setItem(String item) {
this.item = item;
}
/**
* @return the time
*/
public Date getTime() {
return time;
}
/**
* @param time the time to set
*/
public void setTime(Date time) {
this.time = time;
}
/**
* @return the number
*/
public long getNumber() {
return number;
}
/**
* @param number the number to set
*/
public void setNumber(long number) {
this.number = number;
}
@Override
public String toString() {
return "Book[" + "isbn=" + isbn + ", name=" + name + ", author="
+ author + ", company=" + company + ", item=" + item + ", "
+ "time=" + time + ", number=" + number + ']';
}
}