QueryRunner类实战

先上一个登录代码---判断登录是否成功

1、c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

    <default-config>

        <property name="user">root</property>

        <property name="password"></property>

        <property name="driverClass">com.mysql.jdbc.Driver</property>

        <property name="jdbcUrl">jdbc:mysql:///filter</property>

    </default-config>

</c3p0-config>

2.C3P0Utils

package com.ithiema.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class C3P0Utils {

//import com.mchange.v2.c3p0.ComboPooledDataSource;

    private static DataSource dataSource = new ComboPooledDataSource();

    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    // 直接可以获取一个连接池

    public static DataSource getDataSource() {

        return dataSource;

    }

    public static Connection getConnection() throws SQLException{

        return dataSource.getConnection();

    }

    // 获取连接对象

    public static Connection getCurrentConnection() throws SQLException {

        Connection con = tl.get();

        if (con == null) {

            con = dataSource.getConnection();

            tl.set(con);

        }

        return con;

    }

    // 开启事务

    public static void startTransaction() throws SQLException {

        Connection con = getCurrentConnection();

        if (con != null) {

            con.setAutoCommit(false);

        }

    }

    // 事务回滚

    public static void rollback() throws SQLException {

        Connection con = getCurrentConnection();

        if (con != null) {

            con.rollback();

        }

    }

    // 提交并且 关闭资源及从ThreadLocall中释放

    public static void commitAndRelease() throws SQLException {

        Connection con = getCurrentConnection();

        if (con != null) {

            con.commit(); // 事务提交

            con.close();// 关闭资源

            tl.remove();// 从线程绑定中移除

        }

    }

    // 关闭资源方法

    public static void closeConnection() throws SQLException {

        Connection con = getCurrentConnection();

        if (con != null) {

            con.close();

        }

    }

    public static void closeStatement(Statement st) throws SQLException {

        if (st != null) {

            st.close();

        }

    }

    public static void closeResultSet(ResultSet rs) throws SQLException {

        if (rs != null) {

            rs.close();

        }

    }

}

3.UserDao.java

package com.ithiema.dao;

import com.ithiema.domain.User;

import com.ithiema.utils.C3P0Utils;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;

public class UserDao {

    public User login(String username, String password) throws SQLException {

        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());

        String sql = "select * from user where username=? and password=?";

        return runner.query(sql, new BeanHandler<User>(User.class), username,password);

    }

}

QueryRunner类

QueryRunner中提供对sql语句操作的API
它主要有三个方法
　　query() 用于执行select
　　update() 用于执行insert/update/delete
　　batch() 批处理

1,Query语句
先来看下query的两种形式, 我们这里主要讲第一个方法, 因为我们用C3P0来统一管理connection.(QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()))
query(sql,ResultSetHandler,Object...params);
query(conn,sql,ResultSetHandler,Object...params);

第一种: 不需要params

//查询所有图书

public List<Book> selectAllBooks() throws SQLException {

    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());

    return qr.query("select * from books", new BeanListHandler<Book>(Book.class));

}

第二种: 需要一个参数查询

//根据id查询指定的书

public Book selectBookById(String id) throws SQLException {

    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());

    return qr.query("select * from books where id=?", new BeanHandler(Book.class),id);

}

第三种:需要多个参数查询

//多条件查询图书信息

public List<Book> findBookByManyCondition(String id, String category,

        String name, String minprice, String maxprice) throws SQLException {

    StringBuilder sql = new StringBuilder("select * from books where 1=1");

    List list = new ArrayList();

    if(!"".equals(id)){

        sql.append(" and id like ?");

        list.add("%"+id+"%");

    }

    if(!"".equals(category)){

        sql.append(" and category=?");

        list.add(category);

    }

    if(!"".equals(name)){

        sql.append(" and name like ?");

        list.add("%"+name+"%");

    }

    if(!"".equals(minprice)){

        sql.append(" and price > ?");

        list.add(minprice);

    }

    if(!"".equals(maxprice)){

        sql.append(" and price < ?");

        list.add(maxprice);

    }

    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());

    return qr.query(sql.toString(),new BeanListHandler<Book>(Book.class),list.toArray());

}

那么我们来看下源码的实现:
(1)QueryRunner.java

//第一种情况,无参数

public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {

    Connection conn = this.prepareConnection();

    return this.query(conn, true, sql, rsh, (Object[]) null);

}

//第二种和第三种使用同一方法: 需要参数

public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {

    Connection conn = this.prepareConnection();

    return this.query(conn, true, sql, rsh, params);

}

解读: 这里先是获取connection, 利用this.preparaConnection() 获取. 然后调用query()方法去执行查询语句. 接下来看源码是如何获取到当前传输过来的connection以及query()方法的内部实现.

protected Connection prepareConnection() throws SQLException {

    if (this.getDataSource() == null) {

        throw new SQLException("QueryRunner requires a DataSource to be " +

            "invoked in this way, or a Connection should be passed in");

    }

    return this.getDataSource().getConnection();

}

这里很简单, 因为我们用的C3P0数据库连接池获取的DataSource, 所以这里直就可以过去到当前的Connection.接下来就看下query()方法的内部实现.

private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)

            throws SQLException {

    if (conn == null) {

        throw new SQLException("Null connection");

    }

    if (sql == null) {

        if (closeConn) {

            close(conn);

        }

        throw new SQLException("Null SQL statement");

    }

    if (rsh == null) {

        if (closeConn) {

            close(conn);

        }

        throw new SQLException("Null ResultSetHandler");

    }

    PreparedStatement stmt = null;

    ResultSet rs = null;

    T result = null;

    try {

        stmt = this.prepareStatement(conn, sql);

        this.fillStatement(stmt, params);

        rs = this.wrap(stmt.executeQuery());

        result = rsh.handle(rs);

    } catch (SQLException e) {

        this.rethrow(e, sql, params);

    } finally {

        try {

            close(rs);

        } finally {

            close(stmt);

            if (closeConn) {

                close(conn);

            }

        }

    }

    return result;

}

解读: 在这里可以看出, 无论是否有传递参数params, 都调用的是同一个query方法, 接着来看this.fillStatement(stmt, params);是如何将参数赋予preparedStatement中的.

public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {

    // check the parameter count, if we can

    ParameterMetaData pmd = null;

    if (!pmdKnownBroken) {

        pmd = stmt.getParameterMetaData();

        int stmtCount = pmd.getParameterCount();

        int paramsCount = params == null ? 0 : params.length;

        if (stmtCount != paramsCount) {

            throw new SQLException("Wrong number of parameters: expected "

                    + stmtCount + ", was given " + paramsCount);

        }

    }

    // nothing to do here

    if (params == null) {

        return;

    }

    for (int i = 0; i < params.length; i++) {

        if (params[i] != null) {

            stmt.setObject(i + 1, params[i]);

        } else {

            // VARCHAR works with many drivers regardless

            // of the actual column type.  Oddly, NULL and

            // OTHER don't work with Oracle's drivers.

            int sqlType = Types.VARCHAR;

            if (!pmdKnownBroken) {

                try {

                    sqlType = pmd.getParameterType(i + 1);

                } catch (SQLException e) {

                    pmdKnownBroken = true;

                }

            }

            stmt.setNull(i + 1, sqlType);

        }

    }

}

这个方法就是核心所在.
第一种情况: 当params为null的时候, 直接return然后执行sql语句.
第二种第三种情况: 当params不为null时, 循环遍历传入的params, 然后将params赋值到preparedStatement中, 然后填充占位符进行sql查询. 这里我们也来回顾下直接使用preparedStatement来进行查询的方式:

@Test

public void update(){

    Connection conn = null;

    PreparedStatement st = null;

    ResultSet rs = null;

    try{

        conn = JdbcUtils.getConnection();

        String sql = "update users set name=?,email=? where id=?";

        st = conn.prepareStatement(sql);

        st.setString(1, "gacl");

        st.setString(2, "gacl@sina.com");

        st.setInt(3, 2);

        int num = st.executeUpdate();

        if(num>0){

            System.out.println("更新成功！！");

        }

    }catch (Exception e) {

        e.printStackTrace();

    }finally{

        JdbcUtils.release(conn, st, rs);

    }

}

@Test

public void find(){

    Connection conn = null;

    PreparedStatement st = null;

    ResultSet rs = null;

    try{

        conn = JdbcUtils.getConnection();

        String sql = "select * from users where id=?";

        st = conn.prepareStatement(sql);

        st.setInt(1, 1);

        rs = st.executeQuery();

        if(rs.next()){

            System.out.println(rs.getString("name"));

        }

    }catch (Exception e) {

    }finally{

        JdbcUtils.release(conn, st, rs);

    }

}

2, Update语句
查看update语句:

//修改图书

public void updateBook(Book book) throws SQLException {

    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());

    qr.update(

            "UPDATE books SET NAME=? ,price=?,bnum=?,category=?,description=? WHERE id=?",

            book.getName(), book.getPrice(), book.getBnum(),

            book.getCategory(), book.getDescription(), book.getId())

}

接着是QueryRunner.java中的update 方法:

public int update(String sql, Object... params) throws SQLException {

    Connection conn = this.prepareConnection();

    return this.update(conn, true, sql, params);

}

private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {

    if (conn == null) {

        throw new SQLException("Null connection");

    }

    if (sql == null) {

        if (closeConn) {

            close(conn);

        }

        throw new SQLException("Null SQL statement");

    }

    PreparedStatement stmt = null;

    int rows = 0;

    try {

        stmt = this.prepareStatement(conn, sql);

        this.fillStatement(stmt, params);

        rows = stmt.executeUpdate();

    } catch (SQLException e) {

        this.rethrow(e, sql, params);

    } finally {

        close(stmt);

        if (closeConn) {

            close(conn);

        }

    }

    return rows;

}

到了参数赋值的时候又调用了上面的fillStatement方法, 这里就不再阐述了.

3, Batch语句
这里直接看batch方法的实例, 然后结合源码的实现.

//批量删除

public void delBooks(String[] ids) throws SQLException {

    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());

    Object[][] params = new Object[ids.length][];//高维确定执行sql语句的次数，低维是给？赋值

    for (int i = 0; i < params.length; i++) {

        params[i] = new Object[]{ids[i]};//给“？”赋值

    }

    qr.batch("delete from books where id=?", params);

}

然后看QueryRunner中的batch()方法:

public int[] batch(String sql, Object[][] params) throws SQLException {

    Connection conn = this.prepareConnection();

    return this.batch(conn, true, sql, params);

}

private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {

    if (conn == null) {

        throw new SQLException("Null connection");

    }

    if (sql == null) {

        if (closeConn) {

            close(conn);

        }

        throw new SQLException("Null SQL statement");

    }

    if (params == null) {

        if (closeConn) {

            close(conn);

        }

        throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");

    }

    PreparedStatement stmt = null;

    int[] rows = null;

    try {

        stmt = this.prepareStatement(conn, sql);

        for (int i = 0; i < params.length; i++) {

            this.fillStatement(stmt, params[i]);

            stmt.addBatch();

        }

        rows = stmt.executeBatch();

    } catch (SQLException e) {

        this.rethrow(e, sql, (Object[])params);

    } finally {

        close(stmt);

        if (closeConn) {

            close(conn);

        }

    }

    return rows;

}

解读: 因为params是一个二维数组, 所以往preparedStatement中赋值的时候使用了for循环, 然后通过preparedstatement.addBatch() 进行批量添加, 然后执行executeBatch()进行操作.

　　 /**

     * Adds a set of parameters to this <code>PreparedStatement</code>

     * object's batch of commands.

     *

     * @exception SQLException if a database access error occurs or

     * this method is called on a closed <code>PreparedStatement</code>

     * @see Statement#addBatch

     * @since 1.2

     */

    void addBatch() throws SQLException;

巴特西

QueryRunner类实战

最新文章

热门文章