1 package com.lizhou.Test; 2 3 import java.sql.SQLException; 4 import java.util.List; 5 import java.util.Map; 6 import java.util.Map.Entry; 7 import java.util.Set; 8 9 import javax.sql.DataSource; 10 11 import org.apache.commons.dbutils.QueryRunner; 12 import org.apache.commons.dbutils.handlers.ArrayHandler; 13 import org.apache.commons.dbutils.handlers.ArrayListHandler; 14 import org.apache.commons.dbutils.handlers.BeanHandler;15 import org.apache.commons.dbutils.handlers.BeanListHandler; 16 import org.apache.commons.dbutils.handlers.MapHandler;17 import org.apache.commons.dbutils.handlers.MapListHandler; 18 import org.apache.commons.dbutils.handlers.ScalarHandler; 19 20 import com.lizhou.Domain.Account; 21 import com.lizhou.Util.MysqlUtil; 22 23 24 /** 25 * DbUtils框架的使用26 * 目的：减轻CURD操作 27 * DbUtils框架最核心的类，就是QueryRunner类，构造其有空参构造和带连接池(DataSource)的构造 28 * 29 * 30 * 31 * @author bojiangzhou 32 * 33 */ 34 public class DbUtils { 35 36 public static void main(String[] args) { 37 /** 38 * 数据库为test,在c3p0-config.xml中配置 39 */ 40 41 // queryRunner(); 42 43 //下面是ResultSetHandler的接口的各种实现类的用法(7个实现类) 44 45 // beanHandler(); //针对JavaBean 46 47 //beanListHandler(); //针对JavaBean 48 49 // arrayHandler();//针对数组 50 51 // arrayListHandler(); //针对数组 52 53 //mapHandler(); //针对Map 54 55 // mapListHandler(); //针对Map 56 57 scalarHandler(); //针对Long 58 59 } 60 61//QueryRunner 62 public static void queryRunner(){ 63 //获取连接池 64 DataSource ds = MysqlUtil.getPool(); 65 //用连接池构造一个QueryRunner 66 QueryRunner qr = new QueryRunner(ds); 67 68 // String sql = "update account set name=? where id=?"; 69 String sql = "insert into account(name, money) values(?, ?)"; 70 71 try { 72 //qr.update(sql, new Object[]{"O(∩_∩)O哈哈~", 2}); 73qr.update(sql, new Object[]{"一生有你", 25000}); 74 } catch (SQLException e) { 75 // TODO Auto-generated catch block76 e.printStackTrace(); 77 } 78 } 79 80 //BeanHandler 81public static void beanHandler(){ 82 QueryRunner qr = new QueryRunner(MysqlUtil.getPool()); 83 84 String sql = "select * from account where id = ?"; 85 86 try { 87 Account acc = (Account) qr.query(sql, new Object[]{3}, new BeanHandler(Account.class)); 88System.out.println(acc.getId()+" "+acc.getName()+" "+acc.getMoney()); 89 } catch (SQLException e) { 90e.printStackTrace(); 91 } 92 93 } 94 95 //BeanListHandler 96 public static void beanListHandler(){ 97 QueryRunner qr = new QueryRunner(MysqlUtil.getPool()); 98 99 String sql = "select * from account"; 100 101 try { 102 List<Account> accounts = (List<Account>) qr.query(sql, new BeanListHandler(Account.class)); 103 for(Account a : accounts){ 104 System.out.println(a.getId()+" "+a.getName()+" "+a.getMoney()); 105 } 106 } catch (SQLException e) { 107 e.printStackTrace(); 108 } 109 } 110 111 //ArrayHandler 112 public static void arrayHandler(){ 113 QueryRunner qr = new QueryRunner(MysqlUtil.getPool()); 114 115 String sql = "select * from account where money=?"; 116 117 try { 118Object[] a = (Object[]) qr.query(sql, 10000, new ArrayHandler()); 119 for(int i = 0;i < a.length;i++){ 120System.out.print(a[i]+" "); 121 } 122 System.out.println(" ------------------------------"); 123 } catch (SQLException e) { 124e.printStackTrace(); 125 } 126 } 127 128 //ArrayListHandler 129 public static void arrayListHandler(){ 130 QueryRunner qr = new QueryRunner(MysqlUtil.getPool()); 131 132 String sql = "select * from account"; 133 134 try { 135List<Object[]> accounts = (List<Object[]>) qr.query(sql, new ArrayListHandler()); 136 for(Object[] obj : accounts){ 137for(int i = 0;i < obj.length;i++){ 138 System.out.print(obj[i]+" "); 139 } 140 System.out.println(" ------------------------------"); 141 } 142 } catch (SQLException e) { 143e.printStackTrace(); 144 } 145 } 146 147 //MapHandler 148public static void mapHandler(){ 149 QueryRunner qr = new QueryRunner(MysqlUtil.getPool()); 150 151 String sql = "select * from account"; 152 153 try { 154 Map<Object, Object> map = (Map<Object, Object>) qr.query(sql, new MapHandler()); 155 Set<Entry<Object, Object>> entry = map.entrySet(); 156 for(Entry e : entry){ 157System.out.print(e.getKey()+" "); 158System.out.println(e.getValue()); 159 } 160 } catch (SQLException e) { 161 e.printStackTrace(); 162 } 163 } 164 165 //MapListHandler 166 public static void mapListHandler(){ 167 QueryRunner qr = new QueryRunner(MysqlUtil.getPool()); 168 169 String sql = "select * from account"; 170 171 try { 172 List< Map<Object, Object> > maps = (List<Map<Object, Object>>) qr.query(sql, new MapListHandler()); 173 for(Map<Object, Object> map : maps){ 174 Set<Entry<Object, Object>> entry = map.entrySet(); 175 for(Entry e : entry){ 176System.out.print(e.getKey()+" "); 177System.out.println(e.getValue()); 178 } 179System.out.println("-------------------------"); 180 } 181 182 } catch (SQLException e) { 183 e.printStackTrace(); 184 } 185 } 186 187 188 //ScalarHandler 只返回一行一列数据 189public static void scalarHandler(){ 190 QueryRunner qr = new QueryRunner(MysqlUtil.getPool()); 191 192 String sql = "select count(*) from account"; 193 194 try { 195 Object obj = qr.query(sql, new ScalarHandler()); 196System.out.println(obj); 197 } catch (SQLException e) { 198e.printStackTrace(); 199 } 200 } 201 202 }