六、操作BLOB类型字段

6.1、BLOB类型简介

   MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。**插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。**MySQL有四种BLOB类型，他们除了在存储的最大信息量上不同外，除此之外他们是等同的。

   如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

6.2、插入BLOB类型

//获取连接
Connection conn = JDBCUtils.getConnection();
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
// 填充占位符
ps.setString(1, "张三");
ps.setString(2, "zs@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("zs.png");
ps.setBlob(4, fis);
//执行
ps.execute();
fis.close();
JDBCUtils.closeResource(conn, ps);
复制代码

6.3、修改BLOB类型

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
// 填充占位符
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);
ps.execute();
fis.close();
JDBCUtils.closeResource(conn, ps);
复制代码

6.4、从数据库表中读取BLOG类型

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if(rs.next()){
  Integer id = rs.getInt(1);
    String name = rs.getString(2);
  String email = rs.getString(3);
    Date birth = rs.getDate(4);
  Customer cust = new Customer(id, name, email, birth);
    System.out.println(cust); 
    //读取Blob类型的字段
  Blob photo = rs.getBlob(5);//这里也可以通过列的索引来读取
  InputStream is = photo.getBinaryStream();
  OutputStream os = new FileOutputStream("c.jpg");
  byte [] buffer = new byte[1024];
  int len = 0;
  while((len = is.read(buffer)) != -1){
    os.write(buffer, 0, len);
  }
    JDBCUtils.closeResource(conn, ps, rs);
  if(is != null){
    is.close();
  }
  if(os !=  null){
    os.close();
  }
}
复制代码

七、批量处理

八、数据库事务

8.1、问题引出

   案例: 银行转账, 从张无忌账户上给赵敏转 1000 块钱。

   我们有一张account（账户表）。然后我们开始转账。

 转账的步骤大概细分为以下几个步骤：

1. 查询张无忌的账户余额是否大于等于1000。余额小于1000就提示温馨提示：亲，你的余额不足。 如果余额大于等于1000转账。

SELECT * FROM account WHERE name = '张无忌' AND balance >= 1000;
复制代码

2. 从张无忌的账户余额中减少1000。

UPDATE account SET balance = balance - 1000 WHERE name = '张无忌';
复制代码

3. 在赵敏的账户余额中增加1000。

UPDATE account SET balance = balance + 1000 WHERE name = '赵敏';
复制代码

   这个时候问题来了，当程序执行到第②步和第③步之间,突然出现一个异常,此时会造成转账前后数据不一致的问题，会造成转账了，但是对方的账户上没有多钱。     造成这个问题的根本原因是因为转入转出是两个单独的操作，其中一个失败后，不会影响到另一个的执行。但是在转账这个业务中，我们需要保证进出两个操作要么都成功，要么都失败。

   这个时候就需要引出事务的概念。

import org.junit.Test;
@Test
public void testTx() throws Exception {
// 贾琏欲执事
// 1 查询张无忌的账户余额是否大于等于1000
    Connection conn = JDBCUtil.getConnection();
    String sql = "SELECT * FROM account WHERE balance>=? AND name=?";
    PreparedStatement pst = conn.prepareStatement(sql);
// 给 ? 设置数据
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"张无忌");
    ResultSet rs = pst.executeQuery();
    if(!rs.next()){
    System.out.println("余额不足");
    return;
    }
// 2 从张无忌的账户余额中减少1000.
    sql = "UPDATE account SET balance = balance-? WHERE name=?";
    pst = conn.prepareStatement(sql);
//设置? 的数据
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"张无忌");
    pst.executeUpdate();
// 模拟出异常
    int a = 10/0;
// 3 在赵敏的账户余额中增加1000.
    sql = "UPDATE account SET balance = balance+? WHERE name=?";
    pst = conn.prepareStatement(sql);
//设置? 的数据
    pst.setBigDecimal(1,new BigDecimal("1000"));
    pst.setString(2,"赵敏");
    pst.executeUpdate();
// 释放资源
    JDBCUtil.close(conn,pst,rs);
    }
复制代码

8.2、事务

   事务(Transaction,简写为tx)：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

   事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务**回滚(rollback)**到最初状态。回滚可以看成是撤销操作。

   为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

8.3、事务的ACID属性

1. 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
   
2. 一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
   
3. 隔离性（Isolation）事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
   
4. 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

8.4、数据库的并发问题

   对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题：

1. 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
2. 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
3. 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。

8.4.1、事务的隔离性

    数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。

   一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

8.4.2、数据库的隔离级别

   MySQL数据库支持4种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

8.4.3、设置隔离级别

   每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。

8.4.3.1、查看当前的隔离级别

SELECT @@tx_isolation;
复制代码

8.4.3.2、设置当前mysql隔离级别

set  transaction isolation level read committed;
复制代码

8.4.3.3、设置mysql的全局隔离级别

set global transaction isolation level read committed;
复制代码

8.5、事务的操作步骤

1. 先定义开始一个事务，然后对数据作修改操作。
2. 执行过程中，如果没有问题就提交(commit)事务，此时的修改将永久地保存下来。
3. 如果执行过程中有问题(异常)，回滚事务(rollback)，数据库管理系统将放弃所作的所有修改而回到 开始事务时的状态。

try{
  //取消事务的自动提交机制,设置为手动提交.
  connection对象.setAutoCommit(false);
  //操作1
  //操作2
  //异常
  //操作3
  //....
  //手动提交事务
  connection对象.commit();
}catch(Exception e){
  //处理异常
    //回滚事务
connection对象.rollback();
}
复制代码

8.6、事务的注意事项

1. 在默认情况下，事务会在执行完DML操作后会自动提交。
2. 在进行查询操作的时候一般是不需要事务的，但是我们一般也会在查询中写事务
3. 在写代码的时候，如果代码完全正常没有异常，但是数据库中的数据没有任何改变的话，说明是没有提交事务。
4. 在MySQL中，只有InnoDB存储引擎支持事务，支持外键，MyISAM是不支持事务的。
5. 以后处理事务的时候，必须在service层中进行控制。

九、连接池

9.1、JDBC数据库连接池的必要性

   在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：

1. 在主程序中建立数据库连接。
   
2. 进行sql操作。
   
3. 断开数据库连接。
   这种模式会存在几个很显著的问题：
   
4. 普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。**数据库的连接资源并没有得到很好的重复利用。**若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
   
5. **对于每一次数据库连接，使用完后都得断开，**否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。
   
6. 这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

9.2、数据库连接池

   为解决传统开发中的数据库连接问题，我们可以采用数据库连接池技术。

   数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。

   数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

   数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

9.2.1、数据库连接池的原理以及优势

使用数据库连接池的优点也是很明显的：

1. 资源重复使用
   由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

2. 更快的系统反应速度
   数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间。

3. 新的资源分配手段
   对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源。

4. 统一的连接管理，避免数据库连接泄漏
   在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露。

9.2.2、数据库连接池的属性

   基本属性：连接池存了连接对象，而连接对象依赖四要素，所以四要素（driverClassName,url,username,password）是基本要求。

   其他属性：对连接对象做限制的配置

1. 初始化连接数：在连接池中事先准备好初始化Connection对象。

2. 最多连接数：在连接池中最多有一定数量的Connection对象，其他客户端进入等待状态。

3. 最少连接数：在连接池中最少一定数量的Connection对象。

4. 最长等待时间：使用一定时间来申请获取Connection对象,如果时间到还没有申请到,则提示,自

动放弃。

5. 最长超时时间：如果你在一定时间之内没有任何动作，则认为是自动放弃Connection对象。

9.3、数据库连接池的分类

   JDBC 的数据库连接池使用javax.sql.DataSource来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：

1. DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
2. C3P0 是一个开源组织提供的一个数据库连接池，**速度相对较慢，稳定性还可以。**hibernate官方推荐使用。
3. Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点。
4. BoneCP 是一个开源组织提供的数据库连接池，速度快。
5. Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快。

   DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池，DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。

注意：

1. 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。

2. 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

9.4、DBCP连接池

   DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool。如需使用该连接池实现，应在系统中增加如下两个 jar 文件：

• Commons-dbcp.jar：连接池的实现
• Commons-pool.jar：连接池实现的依赖库

    **Tomcat 的连接池正是采用该连接池来实现的。**该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。

   数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。

   当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

9.4.1、DBCP属性说明

9.4.2、获取连接的方式

//使用dbcp数据库连接池的配置文件方式，获取数据库的连接：推荐
// 创建一个DataSource对象
private static DataSource source = null;
static{
  try {
        //创建一个Properties，用于读取配置文件
    Properties pros = new Properties();
        //读取配置文件
    InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("db.properties");
    //加载配置文件
    pros.load(is);
    //根据提供的BasicDataSourceFactory创建对应的DataSource对象
    source = BasicDataSourceFactory.createDataSource(pros);
  } catch (Exception e) {
    e.printStackTrace();
  }
}
public static Connection getConnection4() throws Exception {
  Connection conn = source.getConnection();
  return conn;
}
复制代码

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=123456
initialSize=10
#...
复制代码

9.4.3、注意事项

   由于使用了DBCP，所以配置文件的key我们必须按照他官方给定的要求来书写。

9.5、Druid（德鲁伊）连接池

   Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

9.5.1、Druid参数详解

9.5.2、获取连接方式

package com.utils;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
/**
 * @author Xiao_Lin
 * @date 2021/1/3 19:47
 */
public class DruidUtils {
  static DataSource ds = null;
  private DruidUtils(){
  }
  static {
    InputStream stream = Thread.currentThread().getContextClassLoader()
        .getResourceAsStream("db.properties");
    Properties properties = new Properties();
    try {
      properties.load(stream);
      ds = DruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  public static Connection getConnection(){
    try {
      return ds.getConnection();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
}
复制代码

DriverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///db?characterEncoding=utf-8&useSSL=false
username = root
password = 123456
复制代码

9.5.3、注意事项

   properties配置文件中的 key 一定要和 DruidDataSource 中对应的属性名一致。

port java.io.IOException; import java.io.InputStream; import java.net.URL; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; import javax.sql.DataSource;

/**

• @author Xiao_Lin
• @date 2021/1/3 19:47

*/ public class DruidUtils { static DataSource ds = null; private DruidUtils(){

}

static { InputStream stream = Thread.currentThread().getContextClassLoader() .getResourceAsStream("db.properties"); Properties properties = new Properties(); try { properties.load(stream); ds = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } }

public static Connection getConnection(){ try { return ds.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return null; } }

```properties
DriverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///db?characterEncoding=utf-8&useSSL=false
username = root
password = 123456