你的位置:首页 > Java教程

[Java教程]DbUtils常用API的使用 方便以后查阅


 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. 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}); 73       qr.update(sql, new Object[]{"一生有你", 25000}); 74     } catch (SQLException e) { 75       // TODO Auto-generated catch block 76       e.printStackTrace(); 77     } 78   } 79    80   //BeanHandler 81   public 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)); 88       System.out.println(acc.getId()+"  "+acc.getName()+"  "+acc.getMoney()); 89     } catch (SQLException e) { 90       e.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   //ArrayHandler112   public static void arrayHandler(){113     QueryRunner qr = new QueryRunner(MysqlUtil.getPool());114     115     String sql = "select * from account where money=?";116     117     try {118       Object[] a = (Object[]) qr.query(sql, 10000, new ArrayHandler()); 119       for(int i = 0;i < a.length;i++){120         System.out.print(a[i]+"  ");121       }122       System.out.println("\n------------------------------");123     } catch (SQLException e) {124       e.printStackTrace();125     }126   }  127   128   //ArrayListHandler129   public static void arrayListHandler(){130     QueryRunner qr = new QueryRunner(MysqlUtil.getPool());131     132     String sql = "select * from account";133     134     try {135       List<Object[]> accounts = (List<Object[]>) qr.query(sql, new ArrayListHandler()); 136       for(Object[] obj : accounts){137         for(int i = 0;i < obj.length;i++){138           System.out.print(obj[i]+"  ");139         }140         System.out.println("\n------------------------------");141       }142     } catch (SQLException e) {143       e.printStackTrace();144     }145   }  146   147   //MapHandler148   public 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){157         System.out.print(e.getKey()+"  ");158          System.out.println(e.getValue());159        }160     } catch (SQLException e) {161       e.printStackTrace();162     }163   }  164   165   //MapListHandler166   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){176           System.out.print(e.getKey()+"  ");177            System.out.println(e.getValue());178          }179         System.out.println("-------------------------");180        }181       182     } catch (SQLException e) {183       e.printStackTrace();184     }185   }  186   187   188   //ScalarHandler 只返回一行一列数据189   public 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());196        System.out.println(obj);197     } catch (SQLException e) {198       e.printStackTrace();199     }200   }  201   202 }