模拟QueryRunner

原创于 2021-04-19 09:03:11 发布 · 142 阅读

0 ·

CC 4.0 BY-SA版权

DBUtils 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个基于C3P0连接池的数据库操作类模拟QueryRunner的实现细节，包括增删改查等基本操作，并通过具体示例展示了如何使用该类执行SQL语句。

package dbutils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class 模拟QueryRunner<T> {
    //线程池对象
    private DataSource dataSource;

    //默认使用c3p0-config.xml配置的连接池
    public 模拟QueryRunner() {
        dataSource = new ComboPooledDataSource();
    }

    //使用用户传入的连接池
    public 模拟QueryRunner(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    //执行对数据库的增删改操作
    public int update(String sql, Object... params) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            //使用C3P0连接池得到数据库连接对象
            con = dataSource.getConnection();
            //根据动态sql语句创建PreparedStatement对象
            pstmt = con.prepareStatement(sql);
            //为PreparedStatement绑定的sql语句设置参数
            initParams(pstmt, params);
            //执行sql命令并返回影响行数
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //为PreparedStatement绑定的sql语句设置参数
    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 {
            //使用C3P0连接池得到数据库连接对象
            con = dataSource.getConnection();
            //根据动态sql语句创建PreparedStatement对象
            pstmt = con.prepareStatement(sql);
            //为PreparedStatement绑定的sql语句设置参数
            initParams(pstmt, params);
            //执行sql命令并返回影响行数
            rs = pstmt.executeQuery();
            //使用handler()方法将结果集创建为指定对象并返回
            return rh.handler(rs);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

模拟QueryRunnerTest.java

package dbutils;

import org.junit.jupiter.api.Test;

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

//传入ResultSet对象返回指定对象
interface RsHandler<T> {
    T handler(ResultSet rs) throws SQLException;
}

public class 模拟QueryRunnerTest {
    //增删改测试
    @Test
    public void testUpdate() {
        Object[] params = {10002, "董小天二号"};
        new 模拟QueryRunner<Student>().update("INSERT INTO table2 VALUES(?, ?)", params);
    }

    //查询测试
    @Test
    public void testQuery() {
        //创建实现RsHandler接口的Student类型实现类
        RsHandler<Student> rh = new RsHandler<Student>() {
            @Override
            public Student handler(ResultSet rs) throws SQLException {
                if (rs == null) {
                    return null;
                }
                Student stu = new Student();
                rs.next();//!!!!!!!将结果集光标从beforeFirst 移动至 first 结果集首行
                stu.setId(rs.getInt(1));
                stu.setName(rs.getString(2));
                return stu;
            }
        };

        模拟QueryRunner<Student> qr = new 模拟QueryRunner();
        Student stu = qr.query("SELECT * FROM table2 WHERE id = ?", rh, 10002);
        System.out.println(stu);
    }
}