package com.lizhou.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.lizhou.Domain.Account;

 import com.lizhou.Util.MysqlUtil;

 /**

  * DbUtils框架的使用

  * 目的：减轻CURD操作

  * DbUtils框架最核心的类，就是QueryRunner类，构造其有空参构造和带连接池(DataSource)的构造

  *

  *

  *

  * @author bojiangzhou

  *

  */

 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();

         }

     }    

 }