DBUtils 使用示例

一、使用示例
Java代码  收藏代码

    package com.exam.test;  
      
    import java.sql.Connection;  
    import java.sql.SQLException;  
    import java.util.Iterator;  
    import java.util.List;  
    import java.util.Map;  
    import java.util.Map.Entry;  
    import junit.framework.TestCase;  
    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.ArrayListHandler;  
    import org.apache.commons.dbutils.handlers.BeanHandler;  
    import org.apache.commons.dbutils.handlers.ColumnListHandler;  
    import org.apache.commons.dbutils.handlers.MapHandler;  
    import org.apache.commons.dbutils.handlers.MapListHandler;  
    import org.apache.commons.dbutils.handlers.ScalarHandler;  
    import com.exam.db.DbManager;  
    import com.exam.util.BasicRowProcessorEx;  
      
    public class TestSomething extends TestCase {  
        public void testDBUtilSelect() {  
            Connection conn = DbManager.getInstance().getConnection();  
      
            QueryRunner queryRunner = new QueryRunner();  
      
            try {  
                // 返回单行记录，使用Map  
                System.out.println("使用Map处理单行记录！");  
                Map<String, Object> map = queryRunner.query(conn,  
                        "select * from tab where rownum=1", new MapHandler(),  
                        (Object[]) null);  
      
                for (Iterator<Entry<String, Object>> i = map.entrySet().iterator(); i  
                        .hasNext();) {  
                    Entry<String, Object> e = i.next();  
                    System.out.println(e.getKey() + "=" + e.getValue());  
                }  
      
                System.out.println("处理多行记录！");  
                List<Map<String, Object>> list = queryRunner.query(conn,  
                        "select * from tab where rownum<=3", new MapListHandler(),  
                        (Object[]) null);  
      
                for (Iterator<Map<String, Object>> li = list.iterator(); li  
                        .hasNext();) {  
                    System.out.println("--------------");  
                    Map<String, Object> m = li.next();  
                    for (Iterator<Entry<String, Object>> mi = m.entrySet()  
                            .iterator(); mi.hasNext();) {  
                        Entry<String, Object> e = mi.next();  
                        System.out.println(e.getKey() + "=" + e.getValue());  
                    }  
                }  
      
                System.out.println("使用Bean处理单行记录！");  
      
                // com.exam.test.TestSomething.Tab  
                Tab tab = queryRunner.query(conn,  
                        "select tname from tab where rownum=1",  
                        new BeanHandler<Tab>(Tab.class));  
                System.out.println("tname=" + tab.getTname());  
                System.out.println("tabtype=" + tab.getTabtype());  
      
                System.out.println("使用Array处理单行记录！");  
                Object[] array = queryRunner.query(conn,  
                        "select * from tab where rownum=1", new ArrayHandler());  
      
                for (int i = 0; i < array.length; i++) {  
                    System.out.println(array[i]);  
                }  
      
                System.out.println("使用Array处理多行记录！");  
                List<Object[]> arraylist = queryRunner  
                        .query(conn, "select * from tab where rownum<=3",  
                                new ArrayListHandler());  
      
                for (Iterator<Object[]> itr = arraylist.iterator(); itr.hasNext();) {  
                    Object[] a = itr.next();  
                    System.out.println("--------------");  
                    for (int i = 0; i < a.length; i++) {  
                        System.out.println(a[i]);  
      
                    }  
                }  
      
                System.out.println("使用ColumnListHandler处理单行记录，返回其中指定的一列！");  
                List<Object> colList = queryRunner.query(conn,  
                        "select * from tab where rownum=1", new ColumnListHandler(  
                                "tname"));  
                for (Iterator<Object> itr = colList.iterator(); itr.hasNext();) {  
                    System.out.println(itr.next());  
                }  
      
                System.out  
                        .println("使用ScalarHandler处理单行记录，只返回结果集第一行中的指定字段，如未指定字段，则返回第一个字段！");  
                Object scalar1 = queryRunner.query(conn, "select * from tab",  
                        new ScalarHandler("tname"));  
                System.out.println(scalar1);  
                Object scalar2 = queryRunner.query(conn,  
                        "select tname,tabtype from tab", new ScalarHandler());  
                System.out.println(scalar2);  
      
                // 使用自定义的行处理器  
                // Map中的KEY可按输入顺序输出  
                System.out.println("使用Map处理单行记录(使用自定义行处理器)！");  
                Map<String, Object> linkedmap = queryRunner  
                        .query(  
                                conn,  
                                "select tabtype,tname,'wallimn' as programmer from tab where rownum=1",  
                                new MapHandler(new BasicRowProcessorEx()),  
                                (Object[]) null);  
      
                for (Iterator<Entry<String, Object>> i = linkedmap.entrySet()  
                        .iterator(); i.hasNext();) {  
                    Entry<String, Object> e = i.next();  
                    System.out.println(e.getKey() + "=" + e.getValue());  
                }  
      
                // 使用自定义的行处理器  
                // Map中的KEY可按输入顺序输出  
                System.out.println("处理多行记录(使用自定义行处理器)！");  
                List<Map<String, Object>> listLinedMap = queryRunner  
                        .query(  
                                conn,  
                                "select tabtype,tname,'wallimn' as programmer from tab where rownum<=3",  
                                new MapListHandler(new BasicRowProcessorEx()),  
                                (Object[]) null);  
      
                for (Iterator<Map<String, Object>> li = listLinedMap.iterator(); li  
                        .hasNext();) {  
                    System.out.println("--------------");  
                    Map<String, Object> m = li.next();  
                    for (Iterator<Entry<String, Object>> mi = m.entrySet()  
                            .iterator(); mi.hasNext();) {  
                        Entry<String, Object> e = mi.next();  
                        System.out.println(e.getKey() + "=" + e.getValue());  
                    }  
                }  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
            DbUtils.closeQuietly(conn);  
        }  
      
        public void testDBUtilInsertDeleteUpdateSelect() {  
            // 建一个简单的测试表，建表脚本如下  
            // create table T_DBUTILTEST(  
            // id integer,  
            // name varchar2(255)  
            // );  
            Connection conn = DbManager.getInstance().getConnection();  
      
            QueryRunner queryRunner = new QueryRunner(true);  
      
            try {  
                queryRunner.update(conn, "delete from T_DBUTILTEST");  
                // queryRunner.update(conn, "truncate table T_DBUTILTEST");  
                // 插一条  
                for (int i = 0; i < 10; i++) {  
                    queryRunner.update(conn,  
                            "insert into T_DBUTILTEST (id,name) values (?,?)", i,  
                            "http://wallimn.iteye.com");  
                }  
      
                // 再插多条  
                queryRunner.batch(conn,  
                        "insert into T_DBUTILTEST (id,name) values (?,?)",  
                        new Object[][] { { 11, "batch:wallimn@sohu.com" },  
                                { 12, "batch:wallimn@sohu.com" } });  
      
                // 删除示例  
                queryRunner.update(conn, "delete from T_DBUTILTEST where id=1");  
                queryRunner.update(conn, "delete from T_DBUTILTEST where id=?", 2);  
                queryRunner.batch(conn, "delete from T_DBUTILTEST where id=?",  
                        new Object[][] { { 3 }, { 4 } });  
      
                // 修改示例  
                queryRunner.update(conn,  
                        "update T_DBUTILTEST set name = ? where id=?", "修改后的新值", 5);  
      
                System.out.println("最终结果显示结果");  
                List<Map<String, Object>> list = queryRunner.query(conn,  
                        "select name,id from T_DBUTILTEST", new MapListHandler(),  
                        (Object[]) null);  
      
                for (Iterator<Map<String, Object>> li = list.iterator(); li  
                        .hasNext();) {  
                    System.out.println("--------------");  
                    Map<String, Object> m = li.next();  
                    for (Iterator<Entry<String, Object>> mi = m.entrySet()  
                            .iterator(); mi.hasNext();) {  
                        Entry<String, Object> e = mi.next();  
                        System.out.print(e.getValue());  
                        System.out.print(",");  
                    }  
                    System.out.println();  
                }  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
            DbUtils.closeQuietly(conn);  
        }  
    }  



二、自定义扩展
　　如果用户想要依照存入Map的顺序显示内容，好像不能直接支持。看了看源码，自定义了一个扩展类。用法在前的代码中有示例。
Java代码  收藏代码

    package com.exam.util;  
      
    import java.sql.ResultSet;  
    import java.sql.ResultSetMetaData;  
    import java.sql.SQLException;  
    import java.util.HashMap;  
    import java.util.LinkedHashMap;  
    import java.util.Map;  
      
    import org.apache.commons.dbutils.BasicRowProcessor;  
      
    /** 
     * 用于apache的dbutil类的功能改进，当需要Map中的Key能按输入的顺序输出时，使用这个类来进行处理。简单改了一下基类。<br/> 
     * 编码：wallimn　时间：2012-7-25　上午11:07:06<br/> 
     * 版本：V1.0<br/> 
     */  
    public class BasicRowProcessorEx extends BasicRowProcessor {  
      
        private static class CaseInsensitiveLinkedHashMap extends HashMap<String, Object> {  
            private final Map<String, String> lowerCaseMap = new LinkedHashMap<String, String>();  
            private static final long serialVersionUID = -2848100435296897392L;  
      
            /** {@inheritDoc} */  
            @Override  
            public boolean containsKey(Object key) {  
                Object realKey = lowerCaseMap.get(key.toString().toLowerCase());  
                return super.containsKey(realKey);  
            }  
      
            /** {@inheritDoc} */  
            @Override  
            public Object get(Object key) {  
                Object realKey = lowerCaseMap.get(key.toString().toLowerCase());  
                return super.get(realKey);  
            }  
      
            /** {@inheritDoc} */  
            @Override  
            public Object put(String key, Object value) {  
                Object oldKey = lowerCaseMap.put(key.toLowerCase(), key);  
                Object oldValue = super.remove(oldKey);  
                super.put(key, value);  
                return oldValue;  
            }  
      
            /** {@inheritDoc} */  
            @Override  
            public void putAll(Map<? extends String, ?> m) {  
                for (Map.Entry<? extends String, ?> entry : m.entrySet()) {  
                    String key = entry.getKey();  
                    Object value = entry.getValue();  
                    this.put(key, value);  
                }  
            }  
      
            /** {@inheritDoc} */  
            @Override  
            public Object remove(Object key) {  
                Object realKey = lowerCaseMap.remove(key.toString().toLowerCase());  
                return super.remove(realKey);  
            }  
        }  
        @Override  
        public Map<String, Object> toMap(ResultSet rs) throws SQLException {  
            Map<String, Object> result = new CaseInsensitiveLinkedHashMap();  
            ResultSetMetaData rsmd = rs.getMetaData();  
            int cols = rsmd.getColumnCount();  
      
            for (int i = 1; i <= cols; i++) {  
                result.put(rsmd.getColumnName(i), rs.getObject(i));  
            }  
      
            return result;  
        }  
      
    }