DBUtils工具

package com.xin.Test;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

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.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.xin.Domain.Account;
import com.xin.Util.MysqlUtil;


/**
 * DbUtils框架的使用
 * 目的：减轻CURD操作
 * DbUtils框架最核心的类，就是QueryRunner类，构造其有空参构造和带连接池(DataSource)的构造
 * 
 * 
 * 
 * 
 *
 */
public class DbUtils {
    
    public static void main(String[] args) {
        /**
         * 数据库为test,在c3p0-config.xml中配置
         */
        
//        queryRunner();
        
        //下面是ResultSetHandler的接口的各种实现类的用法(7个实现类)
        
//        beanHandler();        //针对JavaBean
        
//        beanListHandler();    //针对JavaBean
        
//        arrayHandler();        //针对数组
        
//        arrayListHandler();    //针对数组
        
//        mapHandler();        //针对Map
        
//        mapListHandler();    //针对Map
        
        scalarHandler();    //针对Long
        
    }
    
    //QueryRunner
    public static void queryRunner(){
        //获取连接池
        DataSource ds = MysqlUtil.getPool();
        //用连接池构造一个QueryRunner
        QueryRunner qr = new QueryRunner(ds);
        
//        String sql = "update account set name=? where id=?";
        String sql = "insert into account(name, money) values(?, ?)";
        
        try {
//            qr.update(sql, new Object[]{"O(∩_∩)O哈哈~", 2});
            qr.update(sql, new Object[]{"一生有你", 25000});
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    //BeanHandler
    public static void beanHandler(){
        QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
        
        String sql = "select * from account where id = ?";
        
        try {
            Account acc = (Account) qr.query(sql, new Object[]{3}, new BeanHandler(Account.class));
            System.out.println(acc.getId()+"    "+acc.getName()+"    "+acc.getMoney());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }
    
    //BeanListHandler
    public static void beanListHandler(){
        QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
        
        String sql = "select * from account";
        
        try {
            List<Account> accounts =  (List<Account>) qr.query(sql, new BeanListHandler(Account.class));
            for(Account a : accounts){
                System.out.println(a.getId()+"    "+a.getName()+"    "+a.getMoney());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }        
    
    //ArrayHandler
    public static void arrayHandler(){
        QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
        
        String sql = "select * from account where money=?";
        
        try {
            Object[] a = (Object[]) qr.query(sql, 10000, new ArrayHandler()); 
            for(int i = 0;i < a.length;i++){
                System.out.print(a[i]+"    ");
            }
            System.out.println("\n------------------------------");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }    
    
    //ArrayListHandler
    public static void arrayListHandler(){
        QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
        
        String sql = "select * from account";
        
        try {
             List<Object[]> accounts = (List<Object[]>) qr.query(sql, new ArrayListHandler()); 
            for(Object[] obj : accounts){
                for(int i = 0;i < obj.length;i++){
                    System.out.print(obj[i]+"    ");
                }
                System.out.println("\n------------------------------");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }    
    
    //MapHandler
    public static void mapHandler(){
        QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
        
        String sql = "select * from account";
        
        try {
             Map<Object, Object> map = (Map<Object, Object>) qr.query(sql, new MapHandler());
             Set<Entry<Object, Object>> entry = map.entrySet();
             for(Entry e : entry){
                 System.out.print(e.getKey()+"    ");
                 System.out.println(e.getValue());
             }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }    
    
    //MapListHandler
    public static void mapListHandler(){
        QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
        
        String sql = "select * from account";
        
        try {
             List< Map<Object, Object> > maps = (List<Map<Object, Object>>) qr.query(sql, new MapListHandler());
             for(Map<Object, Object> map : maps){
                 Set<Entry<Object, Object>> entry = map.entrySet();
                 for(Entry e : entry){
                     System.out.print(e.getKey()+"    ");
                     System.out.println(e.getValue());
                 }
                 System.out.println("-------------------------");
             }
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }    
    
    
    //ScalarHandler 只返回一行一列数据
    public static void scalarHandler(){
        QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
        
        String sql = "select count(*) from account";
        
        try {
             Object obj = qr.query(sql, new ScalarHandler());
             System.out.println(obj);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }    
    
}