package com.pansoft.utils;import java.io.PrintWriter;import java.sql.Connection;import java.sql.DriverManager;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Map.Entry;import org.apache.commons.dbutils.DbUtils;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.ArrayHandler;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.ColumnListHandler;import org.apache.commons.dbutils.handlers.KeyedHandler;import org.apache.commons.dbutils.handlers.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import com.pansoft.entity.Account;/** * * @author bxing * 参考文章： * http://wallimn.iteye.com/blog/1606930 * http://www.open-open.com/lib/view/open1342664230401.html * * 用到的工具包： * commons-dbutils-1.5.jar * commons-logging-api-1.1.jar * * 同事介绍地这个数据库工具，大体了解了下，他封装了jdbc的多数方法，与Hibernate，ibatis等框架相比更加灵活 * * 该demo数据库测试表位 * create table accout( * cardNo varchar2(64), * password varchar2(64), * balance varchar2(64) * ); * insert into accout values('bxing','bxing','0,99'); * * 可惜的是，我想打印出jdbc中DriverManager.setLogWriter(new PrintWriter(System.out,true))实现的 * 日志打印功能，目前尚未达成。 */public class DBTool {private static final Class Account = null;private static Log log = LogFactory.getLog(DBTool.class);public static void main(String[] args) {Connection conn = null;String url = "jdbc:oracle:thin:@localhost:1521:orcl";String driver = "oracle.jdbc.driver.OracleDriver";//抛弃DbUtils，成功打印日志。DriverManager.setLogWriter(new PrintWriter(System.out,true));try {log.info("加载驱动类");DbUtils.loadDriver(driver);log.info("获取Connection 连接");conn = DriverManager.getConnection(url, "bxing", "bxing");conn.setAutoCommit(false);log.info("获取QueryRunner 实例");//run在此工具中起着很重要的角色，犹如statement。但又强大的多。QueryRunner run = new QueryRunner();/** * 数据的增删改，DbUtils都通过update方法和batch方法实现，后者实现的是批量的操作 * 其参数为sql，param * 返回值 为int类型，没研究出其标识的是作用列，还是成功或者失败（失败的时候是0） */int flag = run.update(conn,"insert into accout values('zhang','huai','0.76')");conn.commit();System.out.println(flag);/** * 以下为查询demo，方便，灵活，但字段的排序，很让人纠结 * ArrayHandler/ArrayListHandler; * BeanHandler/BeanListHandler; * ColumnListHandler; * KeyedHandler; * MapHandler/MapListHandler; * ScalarHandler */log.info("Using MapHandler,获取单行记录");/*在查询方法中，query的参数很丰富，查询结果根据第三个参数的类型被封装成相应的结果集*/@SuppressWarnings("unchecked")Map<String,Object> map = (Map<String, Object>) run.query(conn,"select * from accout where rownum=1", new MapHandler());//map中存的是列名和列值for(Iterator<Entry<String, Object>> i = map.entrySet().iterator();i.hasNext();){Entry<String, Object> e = i.next();System.out.print(e.getKey()+"="+e.getValue()+"\t");}System.out.println();log.info("Using MapHandler and List ,获取多行记录");@SuppressWarnings("unchecked")List<Map<String, Object>> list = (List<Map<String, Object>>) run.query(conn,"select * from accout where rownum<=4", new MapListHandler());for(Iterator<Map<String, Object>> ls = list.iterator();ls.hasNext();){System.out.println("-----------------------------------------");Map<String, Object> m = ls.next();for(Iterator<Entry<String, Object>> i = m.entrySet().iterator();i.hasNext();){Entry<String, Object> e = i.next();System.out.print(e.getKey()+"="+e.getValue()+"\t");}System.out.println();}log.info("使用Bean处理单行记录！");//这种模式比Hibernate中的配置更为方便，灵活未必强多少，因为表中列名和类的属性一一对应，命名必须相同，估计使用反射重新构造了一个类的实例Account accout = (com.pansoft.entity.Account) run.query(conn, "select * from accout where rownum=1", new BeanHandler(Account.class));//这里Account的属性必须与表中字段名对应好，负责无法赋值，必须注意避免使用关键字，貌似也可以，但尽量避免吧System.out.println(accout.getCardNo()+accout.getPassword()+accout.getBalance());log.info("使用Array处理单行记录！");//与此对应的还有ArrayListHandler,这里可以灵活地设置参数，虽然该方法已经过时。Object[] array = (Object[]) run.query(conn, "select * from accout where rownum=?",1, new ArrayHandler());for(int i = 0;i<array.length;i++){System.out.println(array[i]);}log.info("使用ColumnListHandler处理单行记录，返回其中指定的一列！"); List<Object> colist = (List<Object>) run.query(conn, "select * from accout where rownum=1", new ColumnListHandler("cardNo"));for(Iterator<Object> it = colist.iterator();it.hasNext();){System.out.println(it.next());}//KeyedHandlerlog.info("keyedHandler 研究...");Map<Object, Map<String,Object>> keymap = (Map<Object, Map<String, Object>>) run.query(conn,"select * from accout where rownum=1", new KeyedHandler("cardNo"));System.out.println(keymap.get("bxing").get("password"));//map中存的是列名和列值for(Iterator<Entry<String, Object>> i = map.entrySet().iterator();i.hasNext();){Entry<String, Object> e = i.next();System.out.print(e.getKey()+"="+e.getValue()+"\t");}System.out.println();log.info("使用ScalarHandler处理单行记录，只返回结果集第一行中的指定字段，如未指定字段，则返回第一个字段！"); Object scalar1 = run.query(conn, "select * from accout", new ScalarHandler("password"));System.out.println(scalar1);} catch (Exception e) {// TODO: handle exception}finally{DbUtils.closeQuietly(conn);}}}
