前言
本文是有介绍JDBC操作blob类型字段，批量插入，数据库连接池技术，以及使用封装好的DButils技术的使用。关于JDBC基础相关介绍，可以参考我前面两篇文章哦
JDBC之操作数据库实现CURD操作_秋雨绵绵-CSDN博客
另外，需要巩固一下数据库事务相关知识
MySQL数据库之TCL——事物语言_秋雨绵绵-CSDN博客
目录
4.2.2、测试查询一条记录，可以使用beanhandler
一、JDBC操作Blob类型字段
1.1、MySQL Blob类型介绍
MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
| 类型 | 大小（单位：字节） |
|---|---|
| TinyBlob | 最大 255 |
| Blob | 最大 65k |
| MediumBlob | 最大 16M |
| LongBlob | 最大 4G |
实际使用中根据需要存入的数据大小定义不同的BLOB类型。
需要注意的是：如果存储的文件过大，数据库的性能会下降。
如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。
1.2、向数据表中插入，读取大数据类型代码演示
这里演示的是修改图片，插入修改一下SQL语句即可
/**
* @Date 2021/12/20 15:12
* @Param
* @Return void
* @MetodName insertTest
* @Author wang
* @Description 测试修改blob数据
*/
@Test
public void insertTest() {
Connection conn = null;
PreparedStatement ps = null;
FileInputStream is = null;
try {
//获取数据库的连接
conn = JdbcUtils.getConnection();
//预编译SQL语句
String sql = "update customers set photo = ? where id = 8";
ps = conn.prepareStatement(sql);
//填充占位符，使用流的方式
is = new FileInputStream(new File("E:\\java练习\\11phase\\JDBC\\src\\bigPicture.png"));
ps.setBlob(1, is);
//执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JdbcUtils.closeResource(conn, ps);
try {
if (is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}这里演示读取
/**
* @Date 2021/12/20 15:13
* @Param
* @Return void
* @MetodName queryInfo
* @Author wang
* @Description 测试查询blob数据
*/
@Test
public void queryInfo() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
InputStream is = null;
FileOutputStream fos = null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//预编译SQL语句
String sql = "select id,name as name,email,birth,photo from customers where id = ?";
ps = conn.prepareStatement(sql);
//填充占位符
ps.setObject(1,16);
//执行
rs = ps.executeQuery();
if(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer cus = new Customer(id, name, email, birth);
System.out.println(cus);
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("E:\\java练习\\11phase\\JDBC\\src\\com\\jdbcLearn_4\\blob\\朱茵.jpg");
byte[] bys = new byte[1024];
int len;
while((len = is.read(bys)) != -1) {
fos.write(bys,0,len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if(fos != null) {
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
JdbcUtils.closeResource(conn,ps,rs);
}
}读取和操作都需要结合流来使用。
二、批量插入
2.1、批量执行SQL语句
当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
JDBC的批量处理语句包括下面三个方法：
addBatch(String)：添加需要批量处理的SQL语句或是参数；
executeBatch()：执行批量处理语句；
clearBatch():清空缓存的数据
2.2、高效的批量插入
如果我们向数据表中插入20000条数据
使用PreparedStatement来操作：该方法我耗时12秒左右
/**
* @Date 2021/12/20 16:42
* @Param
* @Return void
* @MetodName insertTest
* @Author wang
* @Description 批量插入方式一：耗时12秒左右
*/
@Test
public void insertTest() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
//获取数据库连接
conn = JdbcUtils.getConnection();
//预编译SQL语句
String sql = "insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 20000; i++) {
ps.setObject(1, "name_" + i);
ps.execute();
}
long end = System.currentTimeMillis();
System.out.println("最后所耗时间为：" + ((end - start) / 1000) + "秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResource(conn, ps);
}
}使用三个方法：该方法我耗时323毫秒，不同机型有不同的硬件配置，时间是不同的，这个方法主要是将数据累计到500（或者你自定义的某个数，但这个数一定要是你要添加的数量的可除尽数，不然你就没办法添加满）进行一次执行
/**
* @Date 2021/12/20 17:04
* @Param
* @Return void
* @MetodName insertInfoTest2
* @Author wang
* @Description 测试addBatch(), executeBatch(), clearBatch()等方法加入对插入测试的时间 323毫秒
*/
@Test
public void insertInfoTest2() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
//获取数据库连接
conn = JdbcUtils.getConnection();
//预编译SQL语句
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
//填充占位符
for (int i = 1; i <= 20000; i++) {
ps.setObject(1, "name_" + i);
//累计SQL达到500之后，再执行
ps.addBatch();
if (i % 500 == 0) {
//执行Batch
ps.executeBatch();
//清空Batch
ps.clearBatch();
}
}
Long end = System.currentTimeMillis();
System.out.println("所耗费时间：" + ((end - start)) + "毫秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JdbcUtils.closeResource(conn, ps);
}
}优化到这一步时，上面这一步，我们MySQL数据库中事务有一个自动提交，他默认是开启的，也就是我们每批量处理500条数据之后，就要提交一次，那么我们可以将MySQL数据库事务的自动提交关闭，等所有的数据处理完之后，再统一提交。
最终版本（金色传说！！！）
/**
* @Date 2021/12/20 17:10
* @Param
* @Return void
* @MetodName insertInfoTest3
* @Author wang
* @Description 测试方法三，批量插入，关闭事务的自动提交：296毫秒
* 该方法最快，通用
*/
@Test
public void insertInfoTest3() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
//获取数据库连接
conn = JdbcUtils.getConnection();
//关闭事物的自动提交
conn.setAutoCommit(false);
//预编译SQL语句
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
//填充占位符
for (int i = 1; i <= 20000; i++) {
ps.setObject(1, "name_" + i);
//累计SQL达到500之后，再执行
ps.addBatch();
if (i % 500 == 0) {
//执行Batch
ps.executeBatch();
//清空Batch
ps.clearBatch();
}
}
//提交数据
conn.commit();
Long end = System.currentTimeMillis();
System.out.println("所耗费时间：" + ((end - start)) + "毫秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JdbcUtils.closeResource(conn, ps);
}
}三、数据库连接池
3.1 JDBC数据库连接池的必要性
在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：
在主程序（如servlet、beans）中建立数据库连接
进行sql操作
断开数据库连接
这种模式开发，存在的问题:
-
普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
-
对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。（回忆：何为Java的内存泄漏？）
-
这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。
3.2、数据库连接池技术：
为了解决上述传统开发中的数据库连接问题，可以采用数据库连接池技术
数据库连接池的基本思想：
就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
工作原理：
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
数据库连接池技术的优点 ：
| 优点 | 说明 |
|---|---|
| 1. 资源重用 | 由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。 |
| 2. 更快的系统反应速度 | 数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间 |
| 3. 新的资源分配手段 | 对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源 |
| 4. 统一的连接管理，避免数据库连接泄漏 | 在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露 |
3.3、几种常用的开源数据库连接池介绍
JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
DBCP： 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
C3P0： 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用
Proxool ：是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
BoneCP： 是一个开源组织提供的数据库连接池，速度快
Druid ：是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快 （开发中主要使用这种连接池）
DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
需要注意的是：
-
数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
-
当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。
由于篇幅限制且我们日常主要使用德鲁伊，那么我就只演示德鲁伊的用法，其实其他数据库连接池的用法都是大差不差的。
3.3.1、Druid数据库连接池
德鲁伊数据库连接池技术的介绍
Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。
使用数据库连接池技术首先要做的就是导入各自的jar包，各位可以去网上搜一下
使用德鲁伊，首先就得导入德鲁伊的jar包
再然后就得写一个properties配置文件
例如
url=jdbc:mysql://localhost/test
username=root
password=2395
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=10里面的配置参数由自己的需要更改，详细的配置参数如下
| 配置 | 缺省 | 说明 |
|---|---|---|
| name | 配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this) | |
| url | 连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto | |
| username | 连接数据库的用户名 | |
| password | 连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter | |
| driverClassName | 根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下) | |
| initialSize | 0 | 初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时 |
| maxActive | 8 | 最大连接池数量 |
| maxIdle | 8 | 已经不再使用，配置了也没效果 |
| minIdle | 最小连接池数量 | |
| maxWait | 获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁 | |
| poolPreparedStatements | false | 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。 |
| maxOpenPreparedStatements | -1 | 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100 |
| validationQuery | 用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。 | |
| testOnBorrow | true | 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 |
| testOnReturn | false | 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能 |
| testWhileIdle | false | 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 |
| timeBetweenEvictionRunsMillis | 有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明 | |
| numTestsPerEvictionRun | 不再使用，一个DruidDataSource只支持一个EvictionRun | |
| minEvictableIdleTimeMillis | ||
| connectionInitSqls | 物理连接初始化的时候执行的sql | |
| exceptionSorter | 根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接 | |
| filters | 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall | |
| proxyFilters | 类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系 | |
测试代码：
package com.jdbcLearn_7.connection;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
/**
* @author wang
* @version 1.0
* @packageName com.jdbcLearn_7.connection
* @className DruidTest
* @date 2021/12/23 9:45
* @Description 德鲁伊Druid数据库连接池技术测试
*/
public class DruidTest {
@Test
public void druidTest() throws Exception {
Properties prop = new Properties();
InputStream rs = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
prop.load(rs);
DataSource source = DruidDataSourceFactory.createDataSource(prop);
Connection conn = source.getConnection();
System.out.println(conn);
}
}
输出结果：
十二月 24, 2021 9:10:49 上午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
com.mysql.jdbc.JDBC4Connection@66d2e7d9连接成功
四、Apache-DBUtils实现CRUD操作
4.1、Apache-DBUtils简介
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
4.2、主要API的使用
①、DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：
public static void close(…) throws java.sql.SQLException： DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。
public static void commitAndClose(Connection conn)throws SQLException： 用来提交连接的事务，然后关闭连接
public static void commitAndCloseQuietly(Connection conn)： 用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做了判断
public static void rollbackAndClose(Connection conn)throws SQLException
rollbackAndCloseQuietly(Connection)
public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。
②、QueryRunner类
该类封装了JDBC大部分CURD操作，可直接使用
QueryRunner类提供了两个构造器：
-
默认的构造器
-
需要一个 javax.sql.DataSource 来作参数的构造器
QueryRunner类的主要方法：
更新
public int update(Connection conn, String sql, Object... params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
......
插入
public <T> T insert(Connection conn,String sql,ResultSetHandler<T> rsh, Object... params) throws SQLException：只支持INSERT语句，其中 rsh - The handler used to create the result object from the ResultSet of auto-generated keys. 返回值: An object generated by the handler.即自动生成的键值
....
批处理
public int[] batch(Connection conn,String sql,Object params)throws SQLException： INSERT, UPDATE, or DELETE语句
public <T> T insertBatch(Connection conn,String sql,ResultSetHandler<T> rsh,Object params)throws SQLException：只支持INSERT语句
.....
查询
public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。
......
4.2.1、 测试添加，删改同理
/**
* @Date 2021/12/23 10:17
* @Param
* @Return void
* @MetodName testInsert
* @Author wang
* @Description 测试增删改操作，封装好的
*/
@Test
public void testInsert(){
Connection conn = null;
try {
//获取数据库连接
conn = JdbcUtils.getConnectionDruid();
//创建runner对象
QueryRunner runner = new QueryRunner();
//SQL语句
String sql = "insert into customers(name,email,birth)values(?,?,?)";
//执行update操作
int insertCount = runner.update(conn, sql, "诸葛亮", "zhugeliang@qq.com", "2012-2-9");
System.out.println("添加了" + insertCount + "条数据");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//关闭资源
JdbcUtils.closeResource(conn,null);
}
}
4.2.2、测试查询一条记录，可以使用beanhandler
/**
* @Date 2021/12/23 10:53
* @Param
* @Return void
* @MetodName testQuery1
* @Author wang
* @Description beanHandler是ResultSetHandler接口的一个实现类用于封装表中的一条记录
* 所以该方法也就是查询返回一条记录的方法
*/
@Test
public void testQuery1() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JdbcUtils.getConnectionDruid();
String sql = "select id,name,birth,email from customers where id = ?";
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
Customer customer = runner.query(conn, sql, handler, 22);
System.out.println(customer);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}4.2.3、测试查询多条语句，使用beanhandler
/**
* @Date 2021/12/23 10:58
* @Param
* @Return void
* @MetodName testQueryMany
* @Author wang
* @Description BeanListHandler：resultSetHandler接口的实现类，用于封装表中多条记录构成的集合
* 该方法用来返回查询的多条语句
*/
@Test
public void testQueryMany() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JdbcUtils.getConnectionDruid();
String sql = "select id,name,birth,email from customers where id < ?";
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
List<Customer> query = runner.query(conn, sql, handler, 22);
query.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}还有一个maphandler，可查询一条或者多条记录，使用方法类似。
4.2.4、特殊查询操作
要使用一个scalarhandler实现类来完成
例如，查询某表中数据记录数
/**
* @Date 2021/12/23 11:18
* @Param
* @Return void
* @MetodName testQueryCount
* @Author wang
* @Description ScalarHandler，ResultSetHandler 接口中的一个实现类：用于查询特殊值
* 该方法测试表中有多少条数据记录
*/
@Test
public void testQueryCount() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JdbcUtils.getConnectionDruid();
String sql = "select count(*) from customers";
ScalarHandler handler = new ScalarHandler();
Long count = (Long)runner.query(conn, sql, handler);
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.closeResource(conn,null);
}
}以上就是我给大家分享的我所学习的JDBC相关知识点了，欢迎各位大佬指正，评论区交流哦，另外，学习JDBC应注意数据库事务问题，关于数据库事务，不熟悉的小伙伴可以翻看这篇文章哦
MySQL数据库之TCL——事物语言_秋雨绵绵-CSDN博客
下期再见啦。
111
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
