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个构造方法

Constructors
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());
        }
    }

posted @ 2017-11-01 16:24 逆水行舟！阅读(1236) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

变局

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

ResultSetHandler接口的实现类

公告