jdbc预处理对象&连接池&DBUtils

jdbc预处理对象&连接池&DBUtils

• 能够描述SQL注入原理和解决方案
• 能够通过PreparedStatement完成CURD代码
• 能够理解连接池的原理
• 能够使用C3P0连接池
• 能够编写连接池工具类
• 掌握DBUtils实现增删改
• 掌握DBUtils实现查询

第一章 PreparedStatement

1.1 SQL注入问题

SQL注入：用户输入的内容作为了SQL语句语法的一部分，改变了原有SQL真正的意义。 假设有登录案例SQL语句如下:

SELECT * FROM 用户表 WHERE NAME = 用户输入的用户名 AND PASSWORD = 用户输的密码;

此时，当用户输入正确的账号与密码后，查询到了信息则让用户登录。但是当用户输入的账号为XXX 密码为：XXX’ OR ‘a’=’a 时，则真正执行的代码变为:

SELECT * FROM 用户表 WHERE NAME = ‘XXX’ AND PASSWORD =’ XXX’ OR ’a’=’a’;

此时，上述查询语句时永远可以查询出结果的。那么用户就直接登录成功了，显然我们不希望看到这样的结果，这便是SQL注入问题。 为此，我们使用PreparedStatement来解决对应的问题。

1.2 API详解：预处理对象

preparedStatement：预编译对象，是Statement对象的子类。
特点:

• 性能高
• 会把sql语句先编译
• 能过滤掉用户输入的关键字。
  PreparedStatement预处理对象，处理的每条sql语句中所有的实际参数，都必须使用占位符?替换。

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

PreparedStatement使用，需要通过以下3步骤完成：

1. PreparedStatement预处理对象代码：

// 获得预处理对象，需要提供已经使用占位符处理后的SQL语句
PreparedStatement psmt = conn.prepareStatement(sql)

2. 设置实际参数

void setXxx(int index, Xxx xx) 将指定参数设置指定类型的值
参数1：index 实际参数序列号，从1开始。
参数2：xxx 实际参数值，xxx表示具体的类型。
例如：
setString(2, "1234") 把SQL语句中第2个位置的占位符?替换成实际参数 "1234"

3. 执行SQL语句:

int executeUpdate(); --执行insert update delete语句.
ResultSet executeQuery(); --执行select语句.
boolean execute(); --执行select返回true 执行其他的语句返回false.

第二章 使用连接池重写工具类

连接池理解为存放多个连接的集合。

2.1 连接池原理

使用连接池技术的目的：解决建立数据库连接耗费资源和时间很多的问题，提高性能。

2.2 编写标准的数据源(规范)

java为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商需要让自己的连接池实现这个接口。这样应用程序可以方便的切换不同厂商的连接池！
常见的连接池：C3P0、DRUID。

2.3 C3P0连接池

2.3.1 C3P0连接池工具类编写

C3P0开源免费的连接池！目前使用它的开源项目有：Spring、Hibernate等。使用C3P0连接池需要导入jar包，c3p0使用时还需要添加配置文件“c3p0-config.xml”
使用步骤

1. 添加jar包
2. 编写配置文件 c3p0-config.xml，放在src中（注：文件名一定不要写错）
3. 编写工具类

• 编写配置文件 c3p0-config.xml

<c3p0-config>
<!-- 使用默认的配置读取连接池对象 -->
<default-config>
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day05</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">2000</property>
<property name="maxIdleTime">1000</property>
</default-config>
</c3p0-config>

c3p0连接池常用的配置参数：

参数	说明
initialPoolSize	初始连接数
maxPoolSize	最多连接数
checkoutTimeout	最大等待时间
maxIdleTime	最大空闲回收时间

初始连接数:创建好的连接池准备的连接数量最多连接数:连接池中最多可以放多少个连接最大等待时间:当连接池里的连接都被占用的时候,超过最大等待时间的时候,就会自动再创建几个连接供使用最大空闲回收时间:连接池有多余的连接,如果一直没人使用,隔段时间就会关闭几个

• 编写C3P0工具类

/*
    C3P0连接池的工具类XML版本:使用C3P0连接池获取数据库连接对象Connection并返回

    连接池有一个规范接口
        javax.sql.DataSource接口
        定义了一个从连接池中获取连接的方法
        Connection getConnection() 尝试建立与此 DataSource 对象所表示的数据源的连接。
     C3P0实现了连接池的规范接口DataSource
        com.mchange.v2.c3p0.ComboPooledDataSource类 implements DataSource接口
        重写了getConnection方法
     使用步骤:
        1.在成员位置创建一个静态的ComboPooledDataSource对象
        2.把c3p0-config.xml复制到当前模块的src下;
            C3P0就会自动的解析xml,获取数据库连接信息给ComboPooledDataSource对象赋值
        3.定义一个静态方法,从ComboPooledDataSource对象中获取数据库连接对象Connection并返回
        4.定义一个释放资源的方法
 */
public class C3P0UtilsXML {
    //1.在成员位置创建一个静态的ComboPooledDataSource对象
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    //3.定义一个静态方法,从ComboPooledDataSource对象中获取数据库连接对象Connection并返回
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            /*
                获取数据库连接对失败,让程序停止下来
                把编译异常,转换为运行时异常
             */
            throw new RuntimeException("获取数据库连接对象失败"+e);
        }
    }

    //定义一个方法,返回连接池对象,给QueryRunner使用
    public static DataSource getDataSource(){
        return dataSource;
    }

    //4.定义一个释放资源的方法
    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();
            }
        }
    }
}

第三章 DBUtils

如果只使用JDBC进行开发，我们会发现冗余代码过多，为了简化JDBC开发，本案例我们讲采用apache commons组件一个成员：DBUtils。
DBUtils就是JDBC的简化开发工具包。需要项目导入commons-dbutils-1.6.jar才能够正常使用DBUtils工具。

1.1 概述

DBUtils是java编程中的数据库操作实用工具，小巧简单实用。DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。
Dbutils三个核心功能介绍

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

1.2 准备数据

• 创建表：

create table product(
pid int primary key,
pname varchar(20),
price double,
category_id varchar(32)
);

• 插入表记录

INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

1.3 QueryRunner核心类介绍

1.3.1 提供数据源

• 构造方法
  • QueryRunner(DataSource)创建核心类，并提供数据源，内部自己维护Connection
• 普通方法
  • update(String sql , Object ... params) 执行DML语句
  • query(String sql , ResultSetHandler , Object ... params)执行DQL语句，并将查询结果封装到对象中。

1.3.2 提供连接

• 构造方法
  • QueryRunner()创建核心类，没有提供数据源，在进行具体操作时，需要手动提供Connection
• 普通方法
  • update(Connection conn , String sql , Object ... params)使用提供的Connection，完成DML语句
  • query(Connection conn , String sql , ResultSetHandler , Object ... params)使用提供的Connection，执行DQL语句，并将查询结果封装到对象中。

1.4 QueryRunner实现添加、更新、删除操作

1.4.1 添加

public void insert() throws SQLException{
//获取一个用来执行SQL语句的对象 QueryRunner
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = " INSERT INTO product(pid,pname,price,category_id) VALUES(?,?,?,?);";
Object[] params = {100,"百岁山", 5500, "c005"};
int line = qr.update(sql,params);// 用来完成表数据的增加、删除、更新操作
//结果集处理
System.out.println("line = " + line);
//把连接归还给连接池
DbUtils.closeQuietly(conn);
}

1.4.2 更新

public void update() throws SQLException{
//1 核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//2 准备sql语句
String sql = "update product set pname=?,price=?,category_id=? where pid=?";
//3 准备实际参数
Object[] params = {"芒果99","998","c009",13};
//4 执行
int r = queryRunner.update(sql, params);
System.out.println(r);
}

1.4.3 删除

public void delete() throws SQLException{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "delete from product where pid = ?";
Object[] params = {99};
int r = queryRunner.update(sql, params);
System.out.println(r);
}

1.5 QueryRunner实现查询操作

• query(String sql, ResultSetHandler<T> rsh, Object... params) 用来完成表数据的查询操作

1.5.1ResultSetHandler 结果集

• BeanHandler：将结果集中第一条记录封装到一个指定的javaBean中
• BeanListHandler：将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
• ScalarHandler：它是用于单数据。例如select count(*) from 表操作。
• ColumnListHandler：将结果集中指定的列的字段值，封装到一个List集合中

JavaBean

1. 需要实现接口：java.io.Serializable ，通常实现接口这步骤省略了，不会影响程序。
2. 提供私有字段：private 类型 字段名;
3. 提供getter/setter方法：
4. 提供无参构造

/*
    创建数据库表product的javabean类
    数据库表与java中类的对应关系
        有product表-->Product类
        表中的列-->类中的成员变量
        表中的行-->Product对象-->多个对象存储到集合中
 */
public class Product implements Serializable{
    //商品主键
    private int pid;
    //商品名称
    private String pname;
    //商品价格
    private double price;
    //商品分类
    private String category_id;

    public Product() {
    }

    public Product(int pid, String pname, double price, String category_id) {
        this.pid = pid;
        this.pname = pname;
        this.price = price;
        this.category_id = category_id;
    }

    @Override
    public String toString() {
        return "Product{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", price=" + price +
                ", category_id='" + category_id + '\'' +
                '}';
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getCategory_id() {
        return category_id;
    }

    public void setCategory_id(String category_id) {
        this.category_id = category_id;
    }
}

BeanHandler

     /*
        使用DbUtils工具包对数据库表数据进行查询_结果集使用BeanHandler
        使用方式:
            把查询的多行结果的第一行取出来,存储到一个javabean对象中返回
        构造方法:
            BeanHandler(Class<T> type):传递javabean的class文件对象,传递Product.class
                内部使用反射技术,根据传递class文件对象创建javabean对象
                    Object obj = clazz.newInstance();
                使用反射技术,为对象的每个成员赋值
                    Method setNameMethod = clazz.getMethod("setXXX", String.class);
                    Object v2 = setNameMethod.invoke(obj, "小龙女");
        T query(String sql, ResultSetHandler<T> rsh, Object... params) 带连接池的构造方法使用
            返回值:
                T: 传递哪个javabean的class文件对象,就返回哪个javabean对象(含有第一行值)
                   传递Product.class就返回Product对象
        使用前提:
            类中必须有空参数构造方法
     */
     @Test
     public void testBeanHandler() throws SQLException {
         //1.创建QueryRunner对象
         QueryRunner qr = new QueryRunner(C3P0UtilsXML.getDataSource());
         //2.使用QueryRunner对象中的方法query,执行sql语句,获取结果
         Product p = qr.query("SELECT * FROM product;", new BeanHandler<>(Product.class));
         //3.处理结果
         System.out.println(p);
     }

BeanListHandler

 /*
        使用DbUtils工具包对数据库表数据进行查询_结果集使用BeanListHandler
        使用方式:
            把查询的多行结果存储到多个javabean对象中
            多个javabean对象存储到List集合中
        构造方法:
            BeanListHandler(Class<T> type) 传递javabean的class文件对象,传递Product.class
                内部使用的也是反射技术
         T query(String sql, ResultSetHandler<T> rsh, Object... params) 带连接池的构造方法使用
            返回值:
                T: 传递哪个javabean的class文件对象,就存储javabean对象的List集合
                   传递Product.class就返回List<Product>
        使用前提:
            类中必须有空参数构造方法
      */
     @Test
     public void testBeanListHandler() throws SQLException {
        //1.创建QueryRunner对象
         QueryRunner qr = new QueryRunner(C3P0UtilsXML.getDataSource());
         //2.使用QueryRunner对象中的方法query执行sql语句,获取结果
         List<Product> list = qr.query("SELECT * FROM product;", new BeanListHandler<>(Product.class));
         //3.处理结果
         for (Product p : list) {
             System.out.println(p);
         }
     }

ScalarHander

/*
        使用DbUtils工具包对数据库表数据进行查询_结果集使用ScalarHandler
        使用方式:
            用于接收sql语句是单一返回值的情况
            1.聚合函数
                sum,avg,count,max,min
            2.获取某一行的某一个字段的值
                SELECT pname FROM product WHERE pid = ?
        T query(String sql, ResultSetHandler<T> rsh, Object... params) 带连接池的构造方法使用
            返回值:
                T:返回值类型多种类型,所以使用Object类型来接收这个值
      */
     @Test
     public void testScalarHandler() throws SQLException {
         //1.创建QueryRunner对象
         QueryRunner qr = new QueryRunner(C3P0UtilsXML.getDataSource());
         //2.调用QueryRunner对象中的方法query执行sql语句,获取结果
         //Object o = qr.query("SELECT COUNT(*) FROM product;", new ScalarHandler());
         Object o = qr.query("SELECT pname FROM product WHERE pid = ?", new ScalarHandler(),8);
         //3.处理结果集
         System.out.println(o);
     }

ColumnListHandler

/*
        使用DbUtils工具包对数据库表数据进行查询_结果集使用ColumnListHandler
        使用方式:
            用于查询指定的列,把指定列的数据存储到一个List集合中返回
        构造方法:
            ColumnListHandler() 没有指定列,默认查询第一列
            ColumnListHandler(int columnIndex)指定列的索引,从1开始
            ColumnListHandler(String columnName)指定列名
        T query(String sql, ResultSetHandler<T> rsh, Object... params) 带连接池的构造方法使用
            返回值:
                T:返回存储列中数据的List集合,因为列的类型有多种,集合的泛型使用Object
                List<Object>
        注意:
            查询的结果必须包含指定的列,否则会报SQLException异常:Column 'price' not found.
      */
     @Test
     public void testColumnListHandler() throws SQLException {
        //1.创建QueryRunner对象
         QueryRunner qr = new QueryRunner(C3P0UtilsXML.getDataSource());
         //2.使用QueryRunner对象中的方法query执行sql语句,获取结果
         //List<Object> list = qr.query("SELECT * FROM product;", new ColumnListHandler());//没有指定列,默认查询第一列
         //List<Object> list = qr.query("SELECT * FROM product;", new ColumnListHandler(2));//列的索引为2 pname
         List<Object> list = qr.query("SELECT pid,pname FROM product;", new ColumnListHandler("price"));//列名
         //3.处理结果
         for (Object o : list) {
             System.out.println(o);
         }
     }