org.apache.commons.dbutils学习总结(2)

package com.wnl.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.InputStream;
import java.io.Reader;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.mysql.jdbc.Blob;
import com.mysql.jdbc.Clob;
import com.wnl.utils.DBCPUtil;
import com.wnl.utils.Person;
/**
 * 使用commom.dbutils的步骤：
 *   1 导jar包： commons-dbutils-1.4.jar
 *   2 如果使用开源数据源，还需要导入必要的jar包，如使用DBUP开源数据源
 *        需要导入commons-dbcp-1.4.jar commons-pool-1.5.6.jar两个包
 *   3 QueryRunner是commom.dbutils开源框架的核心类
 * @author wnl
 */
public class TestDBUtlis {


    private QueryRunner qr=new QueryRunner(DBCPUtil.getDataSource());
    /*
     * 练习心得：
     *    开始database.properties文件配置有错误，发生了java.lang.ExceptionInInitializerError错误。
     *原因是在DBCP开源数据源中Properties文件配置有要求：
     *
     *     driverClassName=com.mysql.jdbc.Driver
           username=root
           url=jdbc:mysql://localhost:3306/javaee
           password=root
           username、password、password、driverClassName名字只能这样写，比如username如果改成了user就报错
     * 
     */
    @Test
    public void test1() throws SQLException{
        //增加一条记录
        qr.update("insert into a (name,number) values (?,?)","WuNanliang",2021);
    }
    @Test
    public void test2() throws SQLException{
        //批处理
        Object[][] params=new Object[6][];//二位数组中，第一维决定了数组params.length的长度
        /*一维：执行SQL语句的次数
         * 二维：SQL语句中占位符的参数值*/
         for(int i=0;i<params.length;i++){
             //给每一行赋值（二维元素是一个数组）
             params[i]=new Object[]{"bank"+i,i};//name-->"bank"+i  number-->i
         }
//      System.out.println(params.length);// 返回6
        qr.batch("insert into a (name,number) values (?,?)", params);
    }

    @Test
    public void test3() throws SQLException{
        qr.update("insert into c (id, name, birthday) values(?,?,?)",9,"Lisi","2021-07-11"); 
    }


    @Test
    public void test4() throws Exception{
        /*
         * 写文本大数据
         */
        File file=new File("src/data");
        Reader reader=new FileReader(file);
        char[] ch=new char[(int)file.length()];
        reader.read(ch);
        reader.close();
        //按照给定 char 数组的序列化形式构造一个 SerialClob 对象。
        SerialClob clob=new SerialClob(ch);
        qr.update("insert into d(id,title) values (?,?)",1,clob);
    }

    @Test
    public void test5() throws Exception {
        /*
         * 写二进制大数据
         */
        File file = new File("src/1.png");
        InputStream is = new FileInputStream(file);
        byte buff[] = new byte[is.available()];
        is.read(buff);
        is.close();
        SerialBlob blob = new SerialBlob(buff);
        qr.update("insert into d(id,content) values (?,?)", 1, blob);
    }
    @Test
    public void test6() throws SQLException{
        /*查询一条数据，转成Bean数组
         *  1 "select * from a where id=?" 查询语句
         *  2 new BeanHandler<Person>(Person.class) 把查询到的结果封装到javabean
         *  3 查询条件参数值
         * 如果没有指定查询条件，则默认查询第一条 
         *  Person per= qr.query("select * from a", new BeanHandler<Person>(Person.class));
         */
    Person per= qr.query("select * from a where id=?", new BeanHandler<Person>(Person.class), 3);
    System.out.println(per);
    }
    @Test
    public void test7() throws SQLException{
        /*
         * 查询多条，转成Bean数组
         */
        List<Person> pers=  qr.query("select * from a ", new BeanListHandler<Person>(Person.class));
        System.out.println(pers);
        for(Person per:pers){
        System.out.println(per);
    }
}
   @Test
   public void test8() throws SQLException{
       //查询一行，转成Object[]数组
     Object[] objects=  qr.query("select * from a ", new ArrayHandler());
     System.out.println(objects.toString());//Ljava.lang.Object;@61bedd7d
     for(Object ojb:objects){
         System.out.println(ojb);//1 bank0 0 
     }
   }
   @Test
    public void test9() throws SQLException{
     List<Object[]> lsitObject = qr.query("select * from a ", new ArrayListHandler());
     for(Object[] obj:lsitObject){
         for(Object ob:obj){
             System.out.print(ob);
         }
         System.out.println("\n");
     }
   }
   @Test
   public void test10() throws SQLException{
    List<Object> list=   qr.query("select * from a", new ColumnListHandler("number"));
       for(Object obj:list){
           System.out.print(obj+"、");
       }
   }

   @Test
   public void test11() throws SQLException{
       //取出结果解所有行数据
       //<Map<Object, Map<String, Object>>> Map
     Map<Object, Map<String, Object>> m_map= qr.query("select * from a", new KeyedHandler());
     for(Entry<Object, Map<String, Object>> map:m_map.entrySet()){
         Map<String, Object> item_map=map.getValue();
            for( Entry<String, Object> s_map:item_map.entrySet()){
                System.out.println(s_map.getKey()+"="+s_map.getValue());
            }
     }
   }

    @Test
    public void test12() throws SQLException {
        // 取出结果集中的第一行数据,和KeyedHandler区别就是处理数据的行数的区别
        Map<String, Object> map = qr.query("select * from a", new MapHandler());
        for(Entry<String, Object> obj:map.entrySet()){
            System.out.println(obj.getKey()+"="+obj.getValue());
        }
    }

    @Test
    public void test13() throws SQLException {
        // 取出结果集中的所有行数据,和KeyedHandler区别就是MapListHandler最终把数据放在List中，KeyedHandler则放在Map中
        List<Map<String, Object>> map = qr.query("select * from a",
                new MapListHandler());
        for (Map<String, Object> s_map : map) {
            for (Entry<String, Object> item_map : s_map.entrySet()) {
                System.out.println(item_map.getKey() + "="
                        + item_map.getValue());
            }
        }

    }

    @Test
    public void test14() throws SQLException{
        //ScalarHandler() 把一个结果集的列转换成Object
    Object obj= qr.query("select count('0') from a ", new ScalarHandler(1));
        System.out.println(obj);

    } 
}

package com.wnl.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtil {
    private static DataSource ds;
    static{
        try {
            InputStream inStream = DBCPUtil.class.getClassLoader().getResourceAsStream("database.properties");
            Properties props = new Properties();
            props.load(inStream);
            ds = BasicDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }
    public static DataSource getDataSource(){
        return ds;
    }
    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void relase(ResultSet rs,Statement stmt,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

package com.wnl.utils;

import java.io.Serializable;

public class Person implements Serializable {

    private int id;
    private String name;
    private int number;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getNumber() {
        return number;
    }
    public void setNumber(int number) {
        this.number = number;
    }
    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + ", number=" + number
                + "]";
    }

}