MySql之QueryRunner

使用原生的JDBC进行开发,发现代码过于冗余

为了简化开发apache commons组件诞生了一个新成员dbutils !

dbutils 的三个核心功能简介:

• QueryRunner中提供对sql语句操作的API
• ResultSetHandler接口，用于定义select操作后，怎样封装结果集
• DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法

一.QueryRunner

提供数据源

方法名	方法描述
QueryRunner(DataSource)	构造方法,提供数据源，内部自己维护Connection
update(String sql , Object ... params)	执行DML语句(数据操纵语言,这里指增删改)
query(String sql , ResultSetHandler , Object ... params)	执行DQL语句(数据查询语言)

提供连接

方法名	方法描述
QueryRunner()	空参构造方法
update(Connection conn , String sql , Object ... params)	使用提供的Connection，完成DML语句
query(Connection conn , String sql , ResultSetHandler , Object ... params)	使用提供的Connection，执行DQL语句，并将查询结果封装到对象中

二.ResultSetHandler

详情查询

1.概述

ResultSetHandler是一个接口,它的所有实现类如下:

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

2.测试准备

• ①MySql数据库一枚
• ②database名为mysqlstudy且表名为admin

• 

• ③项目工程

3.代码展示

***pom.xml

<properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <!--数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--C3P0连接池-->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--servlet-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!--dbutils-->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
    </dependencies>

***c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
	<property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql://localhost:3306/mysqlstudy?characterEncoding=utf8</property>
	<property name="user">root</property>
	<property name="password">root</property>
    <property name="initialPoolSize">10</property>
  </default-config>
</c3p0-config>

***Admin.java

package com.howie.pojo;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 19:18
 * @Version 1.0
 */
public class Admin {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "admin{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

***C3P0Utils.java

package com.howie.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 {
	private static ComboPooledDataSource cpds = new ComboPooledDataSource();

	/*
        相比于直接向AccountDao的方法中传入connection对象,
        这里创建一个静态的ThreadLocal更加方便
        ThreadLocal的底层是一个map,存储的是当前线程的名字和当前的connection
        由于map集合是静态的,一开便加载在内存中了,想使用的话便可直接调取使用
        所以说这里可以不用ThreadLocal也行,不用的话就创建一个静态的map集合也一样能达到效果
        只不过ThreadLocal里面已经封装好了这个map,我们用它就好
     */
	private static ThreadLocal<Connection> tl = new ThreadLocal<>();
	public static Connection getConnection() {
		try {
			Connection conn = tl.get();
			if (conn == null) {
				conn = cpds.getConnection();
				tl.set(conn);
			}
			return conn;
		} catch (SQLException e) {
			throw new RuntimeException("获取数据库连接失败!");
		}
	}

	public static DataSource getDataSource() {
		return cpds;
	}

	public static void close(ResultSet rs, Statement stat, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

***Operation.java

package com.howie.resultset;

import com.howie.pojo.Admin;
import com.howie.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Before;
import org.junit.Test;

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

/**
 * @Author weihuanwen
 * @Date 2019/8/6 8:18
 * @Version 1.0
 */
public class Operation {

    private QueryRunner qr;

    @Before
    public void excuteBefore(){
        qr = new QueryRunner(C3P0Utils.getDataSource());
    }

    /**
     * 将结果集中第一行数据封装成对象数组
     * [1, Jhon, 12345]
     * @throws SQLException
     */
    @Test
    public void getDataAH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        Object[] resultArr = qr.query(sql, new ArrayHandler());
        System.out.println(Arrays.toString(resultArr));
    }

    /**
     * 将结果集中每一行数据转为对象数组并存储于List集合中
     * [[Ljava.lang.Object;@2d554825, [Ljava.lang.Object;@68837a77]
     * @throws SQLException
     */
    @Test
    public void getDataALH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        List<Object[]> resultList = qr.query(sql, new ArrayListHandler());
        System.out.println(resultList);
    }

    /**
     * 将结果集第一行数据封装成JavaBean对象
     * admin{id=1, username='Jhon', password='12345'}
     * @throws SQLException
     */
    @Test
    public void getDataBH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        Admin admin = qr.query(sql, new BeanHandler<Admin>(Admin.class));
        System.out.println(admin);
    }

    /**
     * 将结果集中每一行封装为JavaBean存储于List集合中
     * [admin{id=1, username='Jhon', password='12345'},
     * admin{id=2, username='Lily', password='12345'}]
     * @throws SQLException
     */
    @Test
    public void getDataBLH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        List<Admin> adminList = qr.query(sql, new BeanListHandler<Admin>(Admin.class));
        System.out.println(adminList);
    }

    /**
     * 将结果集中某一列的数据存放入List集合中
     * [Jhon, Lily]
     * @throws SQLException
     */
    @Test
    public void getDataCLH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        List<Object> query = qr.query(sql, new ColumnListHandler<>("username"));
        System.out.println(query);
    }

    /**
     * 将结果集中每一行数据封装至map中,再根据指定的Key把每个封装好的map存放至另一个map中
     * {Jhon={id=1, username=Jhon, password=12345},
     * Lily={id=2, username=Lily, password=12345}}
     * 不指定key的话,默认为每行第一个字段作为key
     * @throws SQLException
     */
    @Test
    public void getDataKH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        Map<Admin, Map<String, Object>> result = qr.query(sql, new KeyedHandler<Admin>("username"));
        System.out.println(result);
    }

    /**
     * 将结果集中第一行数据封装至map中
     * {id=1, username=Jhon, password=12345}
     * @throws SQLException
     */
    @Test
    public void getDataMH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        Map<String, Object> result = qr.query(sql, new MapHandler());
        System.out.println(result);
    }

    /**
     * 将结果集中第一行数据封装至map中再存放至List中
     * [{id=1, username=Jhon, password=12345},
     * {id=2, username=Lily, password=12345}]
     * @throws SQLException
     */
    @Test
    public void getDataMLH() throws SQLException {
        String sql = "SELECT * FROM `admin`";
        List<Map<String, Object>> result = qr.query(sql, new MapListHandler());
        System.out.println(result);
    }

    /**
     * 将结果集中某一行某一列的值进行封装
     * EP:查询表中的总记录数
     * 2
     * @throws SQLException
     */
    @Test
    public void getDataSH() throws SQLException {
        String sql = "SELECT COUNT(*) FROM `admin`";
        Long count = qr.query(sql, new ScalarHandler<Long>());
        System.out.println(count);
    }
}

4.测试:

测试结果见代码Operation的方法注释