Dbutils 详解
Common Dbutils是操作数据库的组件,对传统操作数据库的类进行二次封装,可以把结果集转化成List。
DBUtils包括3个包:
org.apache.commons.dbutils
org.apache.commons.dbutils.handlers
org.apache.commons.dbutils.wrappers
DBUtils封装了对JDBC的操作,简化了JDBC操作,可以少写代码。
org.apache.commons.dbutils
DbUtils 关闭链接等操作
QueryRunner 进行查询的操作
org.apache.commons.dbutils.handlers
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
org.apache.commons.dbutils.wrappers
SqlNullCheckedResultSet :对ResultSet进行操作,改版里面的值
StringTrimmedResultSet :去除ResultSet中中字段的左右空格。Trim()
主要方法:
DbUtils类:启动类
ResultSetHandler接口:转换类型接口
MapListHandler类:实现类,把记录转化成List
BeanListHandler类:实现类,把记录转化成List,使记录为JavaBean类型的对象
Qrery Runner类:执行SQL语句的类
建立三个Java文件
命名为BeanListExample.java
Guestbook.java
MapListExample.java
源码:
BeanListExample.java
Code
package com.sy;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
public class BeanListExample {
public static
void main(String[] args) {
Connection conn = null ;
String url = " jdbc:mysql://localhost:3306/people" ;
String jdbcDriver = " com.mysql.jdbc.Driver" ;
String user = " root " ;
String password = " hicc " ;
DbUtils.loadDriver(jdbcDriver);
try {
conn = DriverManager.getConnection(url, user, password);
QueryRunner qr = new QueryRunner();
List results = (List) qr.query(conn, " select id,name from guestbook" , new BeanListHandler(Guestbook.class ));
for ( int i = 0 ; i < results.size(); i++ ) {
Guestbook gb = (Guestbook) results.get(i);
System.out.println(" id: " + gb.getId() + " ,name: " + gb.getName());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
}
}
Guestbook.java
Code
package com.sy;
public class Guestbook {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this .id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this .name = name;
}
}
MapListExample.java
Code
package com.sy;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class MapListExample {
public static
void main(String[] args) {
Connection conn = null ;
String url = " jdbc:mysql://localhost:3306/people" ;
String jdbcDriver = " com.mysql.jdbc.Driver" ;
String user = " root " ;
String password = " hicc " ;
DbUtils.loadDriver(jdbcDriver);
try {
conn = DriverManager.getConnection(url, user, password);
QueryRunner qr = new QueryRunner();
List results = (List) qr.query(conn, " select id,name from guestmessage" , new MapListHandler());
for ( int i = 0 ; i < results.size(); i++ ) {
Map map = (Map) results.get(i);
System.out.println(" id: " + map.get( " id " ) + " ,name: " + map.get( " name " ));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
}
}
使用组建好需要添加commons - dbutils - 1.1 .jar和mysql - connector - java - 5.1 . 6 - bin.jar 两个jar包。
配置完毕!!!
// 另一种方法
// 使用dbutils1.0版本
import java.util. * ;
import java.util.logging.* ;
import java.sql.* ;
import org.apache.commons.dbutils.* ;
import org.apache.commons.dbutils.handlers.* ;
public class TestDBUnits {
public static
void main(String[]args) throws Exception {
TestDBUnits test = new TestDBUnits();
for ( int i = 0 ; i < 1 ; i ++ ) {
test.testQuery1();
test.testQuery2();
test.testUpdate();
}
}
public void testQuery1(){
try {
QueryRunner qr = new QueryRunner() ;
ResultSetHandler rsh = new ArrayListHandler();
String strsql = " select * from test1" ;
ArrayList result = (ArrayList)qr.query(getConnection() ,strsql ,rsh);
// System.out.print("");
} catch (Exception ex) {
ex.printStackTrace(System.out);
}
}
public void testQuery2(){
try {
QueryRunner qr = new QueryRunner() ;
ResultSetHandler rsh = new MapListHandler();
String strsql = " select * from test1" ;
ArrayList result = (ArrayList)qr.query(getConnection() ,strsql ,rsh);
for ( int i = 0 ; i < result.size() ; i++ ) {
Map map = (Map)result.get(i);
// System.out.println(map);
}
// System.out.print("");
} catch (Exception ex) {
ex.printStackTrace(System.out);
}
}
public void testUpdate(){
try {
QueryRunner qr = new QueryRunner() ;
ResultSetHandler rsh = new ArrayListHandler();
String strsql = " insert test1(page ,writable ,content)values('ttt','ttt','faskldfjklasdjklfjasdklj')" ;
qr.update(getConnection() ,strsql);
// System.out.print("");
} catch (Exception ex) {
ex.printStackTrace(System.out);
}
}
private Connection getConnection() throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException {
String strDriver = " org.gjt.mm.mysql.Driver" ;
String strUrl = " jdbc:mysql://localhost:3306/test" ;
String strUser = " root " ;
String strPass = "" ;
Class.forName(strDriver).newInstance();
return DriverManager.getConnection(strUrl, strUser, strPass);
}
}