DbUtils类

DbUtils :提供如关闭连接、装载JDBC驱动程序等常规工作的工具类,里面的所有方法都是静态的。主要方法如下:
public static void close(…) throws java.sql.SQLException: DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL,如果不是的话,它们就关闭Connection、Statement和ResultSet。
public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭,还能隐藏一些在程序中抛出的SQLEeception。
public static void commitAndCloseQuietly(Connection conn): 用来提交连接,然后关闭连接,并且在关闭连接时不抛出SQL异常。
public static boolean loadDriver(java.lang.String driverClassName):这一方装载并注册JDBC驱动程序,如果成功就返回true。使用该方法,你不需要捕捉这个异常ClassNotFoundException。

QueryRunner类 :
该类简单化了SQL查询,它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作,能够大大减少编码量。
QueryRunner类提供了两个构造方法:
默认的构造方法
需要一个 javax.sql.DataSource 来作参数的构造方法。

QueryRunner类的主要方法:
public Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) throws SQLException:执行一个查询操作,在这个查询中,对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。
public Object query(String sql, Object[] params, ResultSetHandler rsh) throws SQLException: 几乎与第一种方法一样;唯一的不同在于它不将数据库连接提供给方法,并且它是从提供给构造方法的数据源(DataSource) 或使用的setDataSource 方法中重新获得 Connection。

public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException : 执行一个不需要置换参数的查询操作。

public int update(Connection conn, String sql, Object[] params) throws SQLException:用来执行一个更新(插入、更新或删除)操作。

public int update(Connection conn, String sql) throws SQLException:用来执行一个不需要置换参数的更新操作。

 

c3p0config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
        <property name="user">root</property>
        <property name="password">root</property>
    
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
 -->    
    <named-config name="lcp">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day17</property>
        <property name="user">root</property>
        <property name="password">lcp8090</property>
    
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
    
    <!--  
    <named-config name="oracle">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
        <property name="user">root</property>
        <property name="password">root</property>
    
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
    -->
</c3p0-config>

 

JdbcUtils.java
package cn.lcp.utils;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtils {
    private static ComboPooledDataSource ds = null;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal();
    
    static{
        ds = new ComboPooledDataSource("flx");
    }
    
    public static Connection getConnection() throws SQLException{
        
        Connection conn = threadLocal.get();
        if(conn==null){
            conn = getDataSource().getConnection();
            threadLocal.set(conn);
        }
        return conn;
    }
    
    public static DataSource getDataSource(){
        return ds;
    }
    
    
    public static void startTransaction(){
        try{
            Connection conn =  threadLocal.get();
            if(conn==null){
                conn = getConnection();
                threadLocal.set(conn);  //把 conn绑定到当前线程上
            }
            conn.setAutoCommit(false);
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    public static void rollback(){
        
        try{
            Connection conn =  threadLocal.get();
            if(conn!=null){
                conn.rollback();
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    public static void commit(){
        
        try{
            Connection conn =  threadLocal.get();
            if(conn!=null){
                conn.commit();
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    public static void release(){
        try{
            Connection conn =  threadLocal.get();
            if(conn!=null){
                conn.close();
                threadLocal.remove(); //解除当前线程上绑定conn
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

 

User.java
package cn.lcp.domain;

import java.util.Date;

public class User {
    
    private Integer id;
    private String name;
    private String password;
    private String email;
    private Date birthday;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

 

Demo1.java
package cn.lcp.demo;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.rowset.serial.SerialClob;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import cn.lcp.domain.User;
import cn.lcp.utils.JdbcUtils;

public class Demo1 {

    /*
     * 
     * 使用dbutils框架完成curd,以及批处理
     * 
     create database day17; use day17;
    
     create table user( id int primary key auto_increment, name varchar(40),
     password varchar(40), email varchar(60), birthday date );
     
     
     insert into user(name,password,email,birthday)values(‘zs‘,‘123456‘,‘[email protected]‘,‘1980-09-09‘); 
     insert into user(name,password,email,birthday) values(‘lisi‘,‘123456‘,‘[email protected]‘,‘1980-09-09‘); 
     insert into user(name,password,email,birthday) values(‘wangwu‘,‘123456‘,‘[email protected]‘,‘1980-09-09‘);
     * 
     */

    @Test
    public void add() throws SQLException {

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
        Object params[] = { "ccc", "123", "[email protected]", new Date() };
        qr.update(sql, params);
    }

    @Test
    public void delete() throws SQLException {

        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "delete from user where id=?";
        qr.update(sql, 1);

    }

    @Test
    public void update() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "update user set name=? where id=?";
        Object params[] = { "ddd", 5 };
        qr.update(sql, params);
    }

    @Test
    public void find() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user where id=?";
        Object params[] = { 2 };
        User user = (User) qr.query(sql, params, new BeanHandler(User.class));
        System.out.println(user.getBirthday());
    }

    @Test
    public void getAll() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user";
        List list = (List) qr.query(sql, new BeanListHandler(User.class));
        System.out.println(list.size());
    }

    @Test
    public void testBatch() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
        Object params[][] = new Object[10][];
        for (int i = 0; i < 10; i++) {
            params[i] = new Object[] { "aa" + i, "123", "[email protected]",
                    new Date() };
        }
        qr.batch(sql, params);
    }
}



 用dbutils完成大数据(不建议用)。在src同目录下建一个1.txt文本
    
create table testclob
(
    id int primary key auto_increment,
    resume text
);

// 用dbutils完成大数
    
    @Test
    public void testclob() throws SQLException, IOException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "insert into testclob(resume) values(?)";  //?需要的是clob字符大对象
        
        
        String path = Demo1.class.getClassLoader().getResource("1.txt").getPath();
        FileReader in = new FileReader(path);
        char[] buffer = new char[(int) new File(path).length()];
        in.read(buffer);
        SerialClob clob = new SerialClob(buffer);
        Object params[] = {clob};
        runner.update(sql, params);
        }
}

 

 

 

 

 

ResultSetHandler接口 :
该接口用于处理 java.sql.ResultSet,将数据按要求转换为另一种形式。
ResultSetHandler 接口提供了一个单独的方法:Object handle (java.sql.ResultSet .rs)。

 

ResultSetHandler 接口的实现类:
ArrayHandler:把结果集中的第一行数据转成对象数组。
ArrayListHandler:把结果集中的每一行数据都转成一个数组,再存放到List中。
BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler:将结果集中的每一行数据都封装到一个对应的JavaBean实例中,存放到List里。


ResultSetHandler 接口的实现类:
ColumnListHandler:将结果集中某一列的数据存放到List中。
KeyedHandler(name):将结果集中的每一行数据都封装到一个Map里,再把这些map再存到一个map里,其key为指定的key。
MapHandler:将结果集中的第一行数据封装到一个Map里,key是列名,value就是对应的值。
MapListHandler:将结果集中的每一行数据都封装到一个Map里,然后再存放到List

package cn.lcp.demo;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

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.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 cn.lcp.utils.JdbcUtils;

public class Demo2 {
    
    
    //测试dbutils各种类型的处理器

    @Test
    //ArrayHandler()
    public void testArrayHandler() throws SQLException{
        
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user";
        Object result[] = (Object[]) qr.query(sql, new ArrayHandler());
        System.out. println(Arrays.asList(result));  //list  重写toString()
    }
    
    @Test
    //ArrayListHandler()
    public void testArrayListHandler() throws SQLException{
        
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user";
        List<Object[]> list = (List) qr.query(sql, new ArrayListHandler());
        for(Object[] o : list){
            System.out.println(Arrays.asList(o));
        }
    }
    
    @Test
    public void testColumnListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user";
        
        List list = (List) qr.query(sql, new ColumnListHandler("id"));
        System.out.println(list);
    }
    
    @Test
    public void testKeyedHandler() throws Exception{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user";
        
        Map<Integer,Map> map = (Map) qr.query(sql, new KeyedHandler("id"));
        for(Map.Entry<Integer, Map> me : map.entrySet()){
            int  id = me.getKey();
            Map<String,Object> innermap = me.getValue();
            for(Map.Entry<String, Object> innerme : innermap.entrySet()){
                String columnName = innerme.getKey();
                Object value = innerme.getValue();
                System.out.println(columnName + "=" + value);
            }
            System.out.println("----------------");
        }
    }
    
    @Test
    public void testMapHandler() throws SQLException{
        
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user";
        
        Map<String,Object> map = (Map) qr.query(sql, new MapHandler());
        for(Map.Entry<String, Object> me : map.entrySet())
        {
            System.out.println(me.getKey() + "=" + me.getValue());
        }
    }
    
    
    @Test
    public void testMapListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user";
        List<Map> list = (List) qr.query(sql, new MapListHandler());
        for(Map<String,Object> map :list){
            for(Map.Entry<String, Object> me : map.entrySet())
            {
                System.out.println(me.getKey() + "=" + me.getValue());
            }
        }
    }
    
    //java.lang.ClassCastException: java.lang.Long
    @Test
    public void testScalarHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from user";  //[13]  list[13]
        /*long l = (Long) qr.query(sql, new ScalarHandler(1));
        int count = (int) l;*/
        
        //int count = Integer.parseInt(qr.query(sql, new ScalarHandler(1)).toString());
        
        int count = ((Long)qr.query(sql, new ScalarHandler(1))).intValue();
        System.out.println(count);
    }
    
}

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。