Java操作数据库简便框架 Common Dbutils
jopen
13年前
Common Dbutils是操作数据库的组件,对传统操作数据库的类进行二次封装,可以把结果集转化成List。传统操作数据库的类指的是JDBC(java database connection:java数据库连接,java的数据库操作的基础API。)。 <br /> DBUtils是java编程中的数据库操作实用工具,小巧简单实用, <br /> 特色: <br /> 1.对于数据表的读操作,他可以把结果转换成List,Array,Set等java集合,便于程序员操作; <br /> 2.对于数据表的写操作,也变得很简单(只需写sql语句) <br /> 3.可以使用数据源,使用JNDI,数据库连接池等技术来优化性能--重用已经构建好的数据库连接对象,而不像php,asp那样,费时费力的不断重复的构建和析构这样的对象。 <br /> DBUtils包括3个包: <br /> org.apache.commons.dbutils <br /> org.apache.commons.dbutils.handlers <br /> org.apache.commons.dbutils.wrappers <br /> DBUtils封装了对JDBC的操作,简化了JDBC操作,可以少写代码。 <br /> org.apache.commons.dbutils <br /> DbUtils 关闭链接等操作 <br /> QueryRunner 进行查询的操作 <br /> org.apache.commons.dbutils.handlers <br /> ArrayHandler :将ResultSet中第一行的数据转化成对象数组 <br /> ArrayListHandler将ResultSet中所有的数据转化成List,List中存放的是Object[] <br /> BeanHandler :将ResultSet中第一行的数据转化成类对象 <br /> BeanListHandler :将ResultSet中所有的数据转化成List,List中存放的是类对象 <br /> ColumnListHandler :将ResultSet中某一列的数据存成List,List中存放的是Object对象 <br /> KeyedHandler :将ResultSet中存成映射,key为某一列对应为Map。Map中存放的是数据 <br /> MapHandler :将ResultSet中第一行的数据存成Map映射 <br /> MapListHandler :将ResultSet中所有的数据存成List。List中存放的是Map <br /> ScalarHandler :将ResultSet中一条记录的其中某一列的数据存成Object <br /> org.apache.commons.dbutils.wrappers <br /> SqlNullCheckedResultSet :对ResultSet进行操作,改版里面的值 <br /> StringTrimmedResultSet :去除ResultSet中中字段的左右空格。Trim() <br /> 主要方法: <br /> DbUtils类:启动类 <br /> ResultSetHandler接口:转换类型接口 <br /> MapListHandler类:实现类,把记录转化成List <br /> BeanListHandler类:实现类,把记录转化成List,使记录为JavaBean类型的对象 <br /> Qrery Runner类:执行SQL语句的类 <br /> 建立三个Java文件 <br /> 命名为BeanListExample.java <br /> Guestbook.java <br /> MapListExample.java <br /> 源码: <br /> <pre class="brush:java; toolbar: true; auto-links: false;"> BeanListExample.java 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 public class Guestbook { private Integer id; private String name; public Integer getId() { return id; } /**get,set方法*/ } MapListExample.java Code 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); } }</pre> <p><strong>项目主页:</strong><a href="http://www.open-open.com/lib/view/home/1328245286061" target="_blank">http://www.open-open.com/lib/view/home/1328245286061</a></p>