dbutils原理

dbutils总结

案例：

package cn.cmlx.c3p0;

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

import org.apache.commons.dbutils.QueryRunner;
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 org.junit.Test;

import cn.cmlx.jdbcutils.JdbcUtil2;

public class DbutilsUsing {
    /**
     * update方法，可执行增、删、改语句。
     * @throws SQLException
     */
    @Test
    public void fun1() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "insert into stu values(?,?,?,?)";
        Object[] params = {2,"郑强",22,"male"};
        qr.update(sql,params);
    }
    /**
     * query方法，可执行查询语句。
     */
    /**
     * BeanHandler的应用
     * 把一行结果集转换成指定类型的javaBean对象
     * @throws SQLException
     */
    @Test
    public void fun2() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "select * from stu where id=?";
        Object[] params = {2};
        Stu stu = qr.query(sql, new BeanHandler<Stu>(Stu.class),params);
        System.out.println(stu);
    }
    /**
     * BeanListHandler的应用，它是多行结果集处理器
     * 每行对应一个Stu对象
     * @throws SQLException
     */
    @Test
    public void fun3() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "select * from stu";
        List<Stu> stuList = qr.query(sql, new BeanListHandler<Stu>(Stu.class));
        System.out.println(stuList);
    }
    /**
     * MapListHandler,它是单行处理器，把一行转换成一个Map对象
     * @throws SQLException
     */
    @Test
    public void fun4() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "select * from stu where id=?";
        Object[] params = {1};
        Map map = qr.query(sql, new MapHandler(),params);
        System.out.println(map);
    }
    /**
     * MapListHandler,它是多行处理器，把每行都转换成一个Map，即List<Map>
     * @throws SQLException
     */
    @Test
    public void fun5() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "select * from stu";
        List<Map<String,Object>> mapList = qr.query(sql, new MapListHandler());
        System.out.println(mapList);
    }
    /**
     * ScanlarHandler，它是单行单列时使用，最为合适！
     * @throws SQLException
     */
    @Test
    public void fun6() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "select count(*) from stu";
        /**
         * Integer、Long、BigInteger，他们有共同的父类Number
         */
        Number cnt = (Number)qr.query(sql, new ScalarHandler());
        Long c = cnt.longValue();
        System.out.println(cnt);
    }
}

 

自己写dbutils

QR.java

package cn.cmlx.c3p0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class QR<T> {
    private DataSource dataSource;

    public QR(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public QR() {
        super();
    }

    /**
     * 做insert】update、delete
     */
    public int update(String sql, Object... params) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = dataSource.getConnection();// 通过连接池得到连接对象
            pstmt = con.prepareStatement(sql);// 使用sql创建pstmt
            initParams(pstmt, params);// 设置参数
            return pstmt.executeUpdate();// 执行
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (SQLException e1) {
            }
        }
    }

    // 给参数赋值
    private void initParams(PreparedStatement pstmt, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i + 1, params[i]);
        }
    }

    public T query(String sql, RsHandler<T> rh, Object... params) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = dataSource.getConnection();// 通过连接池得到连接对象
            pstmt = con.prepareStatement(sql);// 使用sql创建pstmt
            initParams(pstmt, params);// 设置参数
            rs = pstmt.executeQuery();// 执行
            return rh.handle(rs);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();
                if (con != null)
                    con.close();
            } catch (SQLException e1) {
            }
        }
    }
}

// 用来把结果集转换成需要的类型！
interface RsHandler<T> {
    public T handle(ResultSet rs) throws SQLException;
}

应用：

DbutilsPrinciple.java

package cn.cmlx.c3p0;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import cn.cmlx.jdbcutils.JdbcUtil2;

public class DbutilsPrinciple {
    @Test
    public void fun1() {
//        Stu s = new Stu(1, "赤名莉香", 22, "male");
//        addStu(s);
//        
        Stu s1 = load(1);
        System.out.println(s1);
    }

    public void addStu(Stu stu) {
        QR qr = new QR(JdbcUtil2.getDataSource());// 创建对象时给出连接池
        String sql = "insert into stu values(?,?,?,?)";// 给出sql模板
        // 给出参数
        Object[] params = { stu.getSid(), stu.getSname(), stu.getAge(), stu.getGender() };
        // 调用update执行增、删、改！
        qr.update(sql, params);

    }

    public Stu load(int sid) {
        QR qr = new QR(JdbcUtil2.getDataSource());// 创建对象是给出连接池
        String sql = "select * from stu where id=?";// 给出sql语句
        Object[] params = { sid };

        RsHandler<Stu> rh = new RsHandler<Stu>() {

            @Override
            public Stu handle(ResultSet rs) throws SQLException {
                if (!rs.next())
                    return null;
                Stu stu = new Stu();
                stu.setSid(rs.getInt("id"));
                stu.setSname(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                stu.setGender(rs.getString("gender"));
                return stu;
            }
        };
        return (Stu) qr.query(sql, rh, params);
    }
}

利用现成的dbutils包

DbutilsUsing.java

package cn.cmlx.c3p0;

import java.sql.SQLException;

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

import cn.cmlx.jdbcutils.JdbcUtil2;

public class DbutilsUsing {
    @Test
    public void fun1() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "insert into stu values(?,?,?,?)";
        Object[] params = {2,"郑强",22,"male"};
        qr.update(sql,params);
    }
    @Test
    public void fun2() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil2.getDataSource());
        String sql = "select * from stu where id=?";
        Object[] params = {2};
        Stu stu = qr.query(sql, new BeanHandler<Stu>(Stu.class),params);
        System.out.println(stu);
    }
}