DBUtils-对JDBC简单封装的开源工具类库

DBUtils 是对JDBC简单封装的开源工具类

详细介绍地址：  https://baike.baidu.com/item/Dbutils/10655914?fr=aladdin

 

在使用DBUtils工具类之前需要DBUtil  jar包   登录官方网址    下载最新jar包和API  也可以直接点击    下载    

 

解压下载的压缩包 

主要用到 QueryRuner    ResultSetHander  这些类 

   ResultSetHander 接口 有许多实现类

     AbstractKeyedHandler, AbstractListHandler, ArrayHandler, ArrayListHandler, BaseResultSetHandler, BeanHandler, BeanListHandler, BeanMapHandler, ColumnListHandler, KeyedHandler, MapHandler, MapListHandler, ScalarHandler

 

QueryRuner 这个类有7个构造方法

Constructor and Description

QueryRunner()

Constructor for QueryRunner.

QueryRunner(boolean pmdKnownBroken)

Constructor for QueryRunner that controls the use of ParameterMetaData.

QueryRunner(DataSource ds)

Constructor for QueryRunner that takes a DataSource to use.

QueryRunner(DataSource ds, boolean pmdKnownBroken)

Constructor for QueryRunner that takes a DataSource and controls the use of ParameterMetaData.

QueryRunner(DataSource ds, boolean pmdKnownBroken, StatementConfiguration stmtConfig)

Constructor for QueryRunner that takes a DataSource, a StatementConfiguration, and controls the use of ParameterMetaData.

QueryRunner(DataSource ds, StatementConfiguration stmtConfig)

Constructor for QueryRunner that takes a DataSource to use and a StatementConfiguration.

QueryRunner(StatementConfiguration stmtConfig)

Constructor for QueryRunner that takes a StatementConfiguration to configure statements when preparing them.

 

今天主要用到queryRunner(DataSource ds) 构造

方法

int[]	batch(Connection conn, String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
int[]	batch(String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
int	execute(Connection conn, String sql, Object... params) Execute an SQL statement, including a stored procedure call, which does not return any result sets.
<T> List<T>	execute(Connection conn, String sql, ResultSetHandler<T> rsh,Object... params) Execute an SQL statement, including a stored procedure call, which returns one or more result sets.
int	execute(String sql, Object... params) Execute an SQL statement, including a stored procedure call, which does not return any result sets.
<T> List<T>	execute(String sql, ResultSetHandler<T> rsh, Object... params) Execute an SQL statement, including a stored procedure call, which returns one or more result sets.
<T> T	insert(Connection conn, String sql, ResultSetHandler<T> rsh) Execute an SQL INSERT query without replacement parameters.
<T> T	insert(Connection conn, String sql, ResultSetHandler<T> rsh,Object... params) Execute an SQL INSERT query.
<T> T	insert(String sql, ResultSetHandler<T> rsh) Executes the given INSERT SQL without any replacement parameters.
<T> T	insert(String sql, ResultSetHandler<T> rsh, Object... params) Executes the given INSERT SQL statement.
<T> T	insertBatch(Connection conn, String sql, ResultSetHandler<T> rsh, Object[][] params) Executes the given batch of INSERT SQL statements.
<T> T	insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) Executes the given batch of INSERT SQL statements.
<T> T	query(Connection conn, String sql, Object[] params, ResultSetHandler<T> rsh) Deprecated.  Use query(Connection,String,ResultSetHandler,Object...) instead
<T> T	query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh) Deprecated.  Use query(Connection, String, ResultSetHandler, Object...)
<T> T	query(Connection conn, String sql, ResultSetHandler<T> rsh) Execute an SQL SELECT query without any replacement parameters.
<T> T	query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) Execute an SQL SELECT query with replacement parameters.
<T> T	query(String sql, Object[] params, ResultSetHandler<T> rsh) Deprecated.  Use query(String, ResultSetHandler, Object...)
<T> T	query(String sql, Object param, ResultSetHandler<T> rsh) Deprecated.  Use query(String, ResultSetHandler, Object...)
<T> T	query(String sql, ResultSetHandler<T> rsh) Executes the given SELECT SQL without any replacement parameters.
<T> T	query(String sql, ResultSetHandler<T> rsh, Object... params) Executes the given SELECT SQL query and returns a result object.
int	update(Connection conn, String sql) Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
int	update(Connection conn, String sql, Object... params) Execute an SQL INSERT, UPDATE, or DELETE query.
int	update(Connection conn, String sql, Object param) Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter.
int	update(String sql) Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters.
int	update(String sql, Object... params) Executes the given INSERT, UPDATE, or DELETE SQL statement.
int	update(String sql, Object param) Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter.

 public Object query(String sql, ResultSetHandler rsh)

public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException 之间的区别就是它不将数据库连接提供给方法，并且它是从提供给构造方法的数据源(DataSource) 或使用的setDataSource 方法中重新获得 Connection。

• ResultSetHandler接口的实现类

• ArrayHandler：把结果集中的第一行数据转成对象数组。
• ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
• BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
• BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
• ColumnListHandler：将结果集中某一列的数据存放到List中。
• KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
• MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
• MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List

方法的使用

　　使用前准备工具类  和配置文件

　　src 下添加  db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bookstore
username=root
password=root

　使用DBCP连接池

 　DBCPUtils.java

package com.study.jdbc.Utils;

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

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtils {

    private static DataSource dataSource;
    static{
        
        try {
            //加载db.properties 配置文件
            InputStream is=DBCPUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties props=new Properties();
            props.load(is);
            //创建数据源
            dataSource=BasicDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
    public static Connection getConnection(){
        
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

操作：

package com.study.jdbc.test;



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

import javax.sql.DataSource;

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

import com.study.jdbc.Utils.DBCPUtils;

public class DBUtilsQueryRunnerDBCP {
    
    @Test
    public void test1() throws SQLException{
        DataSource dataSource=DBCPUtils.getDataSource();
        QueryRunner qr=new QueryRunner(dataSource);
        String sql="select * from user";
        //ArrayHandler()把结果集第一行转化为对象数组
        // qr.query() 执行查询sql语句
        //qr.update() 执行更新sql语句
        // qr.execute()执行sql语句
        Object[]  list=qr.query(sql, new ArrayHandler() );
        for (Object i : list) {
            System.out.print(i +" ");
        }
    }
    
    @Test
    public void test2() throws SQLException{
        DataSource dataSource=DBCPUtils.getDataSource();
        QueryRunner qr=new QueryRunner(dataSource);
        String sql="select * from user";
        List<Object[]> rs=qr.query(sql, new ArrayListHandler());
        for (Object i : rs) {
            for (Object[] j : rs) {
                for (Object object : j) {
                    System.out.print(object);
                }
                System.out.println();
            }
        }
    }
    
    @Test
    public void test3() throws SQLException{
        DataSource dataSource=DBCPUtils.getDataSource();
        QueryRunner qr=new QueryRunner(dataSource);
        String sql="select * from user";
        //MapListHandler把每条数据转化为Map对象
        List<Map<String, Object>> map =qr.query(sql, new  MapListHandler());
        for (Map<String, Object> map2 : map) {
            for (Entry<String, Object> en: map2.entrySet()) {
                System.out.print(en.getKey()+" ="+en.getValue() +"     ");
            }
            System.out.println();
        }
    }
    public static void main(String[] args) throws SQLException {
        DataSource dataSource=DBCPUtils.getDataSource();
        QueryRunner qr=new QueryRunner(dataSource);
        String sql="select * from user where userId=?";
        Map<String, Object>  map = qr.query(sql, new  MapHandler() ,2);
        for (  Entry<String, Object>  i  : map.entrySet()) {
            System.out.print(i.getKey()+"="+i.getValue()+"   ");
        }
        
        
    }
}

 BeanListHandler

@Test
    public void test2() throws SQLException{
        DataSource dataSource=DBCPUtils.getDataSource();
        QueryRunner qr=new QueryRunner(dataSource);
        String sql="select * from user";
        List<User> rs=qr.query(sql, new BeanListHandler<>(User.class));
        for (Object user : rs) {
            System.out.println(user.toString());
        }
    }