Common 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
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
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
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); } }