# HelloMySQL **Repository Path**: osxcn/HelloMySQL ## Basic Information - **Project Name**: HelloMySQL - **Description**: 网易云课堂java web微专业数据库开发 - **Primary Language**: Java - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2018-10-16 - **Last Updated**: 2020-12-19 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # HelloMySQL 网易云课堂java web微专业数据库开发 ## 云课堂昵称 偶是小菜鸟 ============================ ## 总体说明 > 本项目为网易云课堂java web微专业数据库开发的课件部分. 本项目一共包含五个部分，分别为JDBC、数据库连接池、SQL注入与防范、事务以及MyBatis. 目录 ================= * [1. JDBC](#1-jdbc) * [1.1 JDBC重要意义](#11-jdbc重要意义) * [1.2 JDBC优势](#12-jdbc优势) * [1.3 JDBC体系架构](#13-jdbc体系架构) * [1.4 JDBC安装](#14-jdbc安装) * [1.5 JDBC API](#15-jdbc-api) * [1.5.1 Driver & DriverManager](#151-driver--drivermanager) * [1.5.2 Connection](#152-connection) * [1.5.3 Statement](#153-statement) * [1.5.4 ResultSet](#154-resultset) * [1.5.5 SQLException](#155-sqlexception) * [1.6 JDBC URL](#16-jdbc-url) * [1.6.1 介绍](#161-介绍) * [1.6.2 常用JDBC URL](#162-常用jdbc-url) * [1.7 构建步骤](#17-构建步骤) * [1.8 JDBC高级功能](#18-jdbc高级功能) * [1.8.1 游标](#181-游标) * [1.8.2 流方式](#182-流方式) * [1.8.3 批处理](#183-批处理) * [1.8.4 字符集设置](#184-字符集设置) * [2. 数据库连接池](#2-数据库连接池) * [2.1 连接池存在的重要原因](#21-连接池存在的重要原因) * [2.2 连接池](#22-连接池) * [2.2.1 什么是连接池？](#221-什么是连接池) * [2.2.2 使用连接池](#222-使用连接池) * [2.2.3 高级配置](#223-高级配置) * [3. SQL注入与防范](#3-sql注入与防范) * [3.1 数据库注入](#31-数据库注入) * [3.2 问题根源](#32-问题根源) * [3.3 解决方案](#33-解决方案) * [3.4 其他注意事项](#34-其他注意事项) * [4. 事务](#4-事务) * [4.1 事务原理与开发](#41-事务原理与开发) * [4.1.1 事务特性](#411-事务特性) * [4.1.1.1 原子性](#4111-原子性) * [4.1.1.2 一致性](#4112-一致性) * [4.1.1.3 隔离性](#4113-隔离性) * [4.1.1.4 持久性](#4114-持久性) * [4.1.2 什么是事务？](#412-什么是事务) * [4.1.3 JDBC事务控制](#413-jdbc事务控制) * [4.1.4 检查点](#414-检查点) * [4.1.5 事务并发执行](#415-事务并发执行) * [4.1.5.1 脏读](#4151-脏读) * [4.1.5.2 不可重复读](#4152-不可重复读) * [4.1.5.3 幻读](#4153-幻读) * [4.1.6 事务隔离级别](#416-事务隔离级别) * [4.1.7 设置隔离级别](#417-设置隔离级别) * [4.2 死锁分析与解决](#42-死锁分析与解决) * [4.2.1 事务并发执行](#421-事务并发执行) * [4.2.2 死锁产生的必要条件](#422-死锁产生的必要条件) * [4.2.3 MySQL中的锁](#423-mysql中的锁) * [4.2.4 加锁方式](#424-加锁方式) * [4.2.5 哪些SQL需要持有锁](#425-哪些sql需要持有锁) * [4.2.6 SQL加锁分析](#426-sql加锁分析) * [4.2.7 分析死锁的常用方法](#427-分析死锁的常用方法) * [5. MyBatis](#5-mybatis) * [5.1 前言](#51-前言) * [5.1.1 面向对象的世界与关系数据库的鸿沟](#511-面向对象的世界与关系数据库的鸿沟) * [5.1.2 ORM](#512-orm) * [5.2 MyBatis](#52-mybatis) * [5.2.1 介绍](#521-介绍) * [5.2.2 功能架构](#522-功能架构) * [5.2.3 工作流机制](#523-工作流机制) * [5.2.4 环境搭建](#524-环境搭建) * [5.2.5 MyBatis优势与劣势](#525-mybatis优势与劣势) * [5.3 MyBatis进阶](#53-mybatis进阶) * [5.3.1 ResultMap](#531-resultmap) * [5.3.2 DataSource](#532-datasource) * [5.3.3 数据库连接生命周期](#533-数据库连接生命周期) * [5.3.4 连接池常用配置选项](#534-连接池常用配置选项) ## 1. JDBC ### 1.1 JDBC重要意义   应用程序通过调用统一接口可以访问任意数据库。JDBC屏蔽了客户端与服务器端交互协议的实现细节，只要能熟练的使用JDBC提供的标准接口，无需关心底层数据库的实现方式。   对于Java应用程序，JDBC就是一个普通的架包，在应用程序中引用架包提供的类和方法，通过操作Java对象的方式，就可以获取数据库中的数据。   对数据库厂商来说，JDBC就是一套接口规范，每个数据库厂商都必须实现JDBC定义的接口，确保用户通过JDBC正确的访问数据库。 ### 1.2 JDBC优势 1. 简单。掌握一套接口就可以实现对任意数据库的访问。 2. 便捷。提高了程序开发的效率，压缩了开发的时间，让Java Web的开发变得更加快捷。 3. 移植性。有了统一的接口和规范，使得Java Web程序面向不同的数据库时，具备跨平台的可移植性。 4. 框架。JDBC仅仅提供了基本的接口和功能，满足最基本的功能需求。基于JDBC功能之上，可以定制更加强大的框架。 ### 1.3 JDBC体系架构

JDBC体系架构图

1. JDBC API层。负责与Java Web进行通信。 2. JDBC Driver API数据库驱动层。负责与数据库建立连接。一般来说，下层的Driver都是由数据库厂商来提供的，负责和实现与自己提供的数据库的通信。 ### 1.4 JDBC安装 安装方式有两种： 1. 数据库官网下载。从数据库官网下载JDBC驱动，下载下来的是一个JAR包，然后加入到Java Web项目中。 2. Maven管理。通过Maven配置JDBC驱动。 ### 1.5 JDBC API

JDBC API

#### 1.5.1 Driver & DriverManager * Driver是一个接口，定义了各个驱动程序都必须实现的功能，是驱动程序的抽象。通过操作Driver接口即可以实现对各个驱动程序的操作。 * DriverManager是Java的管理类，用户通过Class.forName的方式就可以向DriverManager注册一个驱动程序，然后通过DriverManager的getConnection方法就可以调用该驱动程序，建立到后端数据库的物理链接。 #### 1.5.2 Connection 代表Java应用程序对后端数据库的一条物理链接，基于这条链接可以执行一些SQL语句。 * 常用方法 ```Java Statment stmt = conn.createStatement(); ``` #### 1.5.3 Statement Statement对象是一个SQL的容器，容器中可以执行诸如insert、delete、update、select也就是增删改查等操作。 容器可以承载放入的一些SQL语句： 1. 通过Statement的executeQuery方法可以执行一个数据库查询，得到数据库查询结果的一个集合，集合是以一个ResultSet对象来表示； 2. 通过Statement对象执行更新、删除语句，这时候调用execute和executeUpdate方法，它返回的是一个int值的对象，它代表的是执行的语句影响了多少条数据库记录。 * 常用方法 ```Java ResultSet rs = stmt.executeQuery("select userName form user"); ``` #### 1.5.4 ResultSet ResultSet代表了一个SQL的查询结果。关系型数据库的本质是一个二元表，所以ResultSet对象实际也是一个由行和列所组成的二元表。 ResultSet对象的内部存在一个指针，用来指向当前的一个行记录，默认该指针指向第一行记录。 移动指针的方法： * .next() 将指针指向下一行 * .previous() 将指针指向上一行 * .absolute() 将指针指向某一行 * .beforeFirst()将指针指向第一行的最开始部分。通过调用.beforeFirst().next()获取第一行记录 * .afterLast() 将指针指向最后一条记录的下一条记录 获取列结果： * .getString(ColumnName/Index) * .getInt(ColumnName/Index) * .getObject(ColumnName/Index) 每个方法都有两种方式：获取列名和获取列序号（从0开始排序）。建议采用列名的方式获取结果，因为更加直观。 #### 1.5.5 SQLException 通过SQLException来表示异常。在应用程序的处理过程中，要通过捕获SQLException来进行相应的异常处理。 ### 1.6 JDBC URL #### 1.6.1 介绍 JDBC URL是后端数据库的唯一标识符，应用程序通过该标识符即可唯一的确定后端的某个数据库实例。它是由三个部分构成的： 1. 协议：这是固定和统一的，为jdbc； 2. 子协议：getConnection方法就是通过URL中子协议部分确定调用对应的驱动程序，来建立到后端数据库的物理链接。以MySQL数据库为例，就是mysql； 3. 子名称：由三个部分组成：主机、端口、数据库. 以下是一个JDBC URL（后文例子中称之为DB_URL）的示例： 
jdbc:mysql://10.164.172.20:3306/cloud_study
* 协议：jdbc * 子协议：mysql * 子名称：10.164.172.20:3306/cloud_study * 主机IP：10.164.172.20 * 端口：3306 * 数据库：cloud_study #### 1.6.2 常用JDBC URL 1. MySQL ```Java jdbc:mysql://:/database ``` 2. ORACLE ```Java jdbc:oracle:thin@:/database ``` 3. SQL Server ```Java jdbc:microsoft:sqlserver://:;DatabaseName=database ``` ### 1.7 构建步骤 构建一个完整的Java Web程序至少应该包含以下五个步骤： 1. 装载驱动程序 2. 建立数据库连接 3. 执行SQL语句 4. 获取执行结果 5. 清理环境 [构建实例：JDBC基础-HelloJDBC](/src/main/java/com/micro/profession/jdbc/practice/HelloJDBC.java) ### 1.8 JDBC高级功能 #### 1.8.1 游标 游标提供一种客户端能够部分读取服务器端结果集的功能支持，允许分批读取SQL查询的结果。 * 如何使用游标 1. DB_URL中新增一个参数 
jdbc:mysql://<ip>:<port>/<database>?useCursorFetch=true
2. 使用`PreparedStatement`接口   `PreparedStatement`继承自`Statement`接口，可以使用`PreparedStatement`接口来替代`Statement`接口，`PreparedStatement`接口相比`Statement`接口要求程序员在生成`PreparedStatement`的时候就要传入SQL语句，这个SQL语句是一个`参数格式化`的SQL，也就是说，SQL的`WHERE`过滤条件的参数都是通过`?`的形式来表示的，后续是通过`PreparedStatement`的`setString`和`setInt`方法来设置这些参数，然后进行执行。   `PreparedStatement`有个`setFetchSize`接口，这个接口可以实现游标的功能。通过`setFetchSize`，就可以设置客户端JDBC每次从服务器端取回的记录的数量。 应用场景：查询时返回记录过多。 [构建实例：JDBC进阶-游标-HelloJDBC_cursor](/src/main/java/com/micro/profession/jdbc/practice/HelloJDBC_cursor.java) #### 1.8.2 流方式 流方式就是将大字段的内容以二进制的方式按照区间进行划分，划分为多个区间，每次读取一个区间中的内容，在处理结束后再读取下一个区间。 应用场景：读取大字段数据。 [构建实例：JDBC进阶-流方式-LiuFang](/src/main/java/com/micro/profession/jdbc/practice/LiuFang.java) #### 1.8.3 批处理 批处理就是通过发送一次SQL可以插入多条数据，即将多条SQL一次性进行发送。 批处理使用涉及到`Statement`的三个函数： * addBatch() 把SQL打包成一个执行单元(Batch) * executeBatch() 执行SQL * clearBatch() 清空Batch中的SQL语句，准备下次执行 注：`PreparedStatement`同样可以使用这三个函数 应用场景：大量数据插入操作。 [构建实例：JDBC进阶-批处理-BatchTest](/src/main/java/com/micro/profession/jdbc/practice/BatchTest.java) #### 1.8.4 字符集设置 1. 获取数据库编码设置 ```mysql show variables like '%character%'; ``` 2. 编码级别 * 实例级别：character_set_server * 数据库级别：character_set_database * 表级别：DEFAULT CHARSET=utf8 * 列级别：CHARACTER SET utf8 ` 编码级别优先顺序：Server < Database < Table < Column ` 3. JDBC设置 `DB_URL` = `DB_URL` + `characterEncoding=utf8` 注：为了保证中文不出错，无论是数据库的还是JDBC的，建议设置为`utf8` ## 2. 数据库连接池 ### 2.1 连接池存在的重要原因 1. **连接复用** 1. 建立连接 通过`DriverManager`的`getConnection`方法可以建立一条Java应用程序到后端数据库的物理链接。虽然对我们来说，仅仅是一个方法的调用，但是在JDBC的数据库驱动中完成了大量客户端与服务器端的交互，这里以MySQL为例：

getConnection交互

  该方法首先会在客户端（也就是应用程序所在的服务器）发起一个到服务器端的TCP请求，然后服务器端随机生成一个密码种子返回给客户端，客户端利用这个密码种子和自己保存的数据库密码按照约定的加密算法可以计算得到一个加密的密码，然后再将这个加密的密码发送给MySQL服务器端进行验证，MySQL服务器端通过验证以后，返回给客户端确认该连接建立成功。一个`getConnection`方法需要四次客户端与服务器端的网络传输，由于跨机器的网络传输有较大的时间开销，所以`getConnection`也需要花费较多的时间，建立连接时间开销大。 2. 多线程数据库访问   在实际业务场景中，当用户要访问Java程序时，会启动一个线程去处理用户的请求。在处理过程中，如果涉及到要访问后端数据库，需要创建一个`Connection`对象，建立到后端数据库的物理连接，在SQL执行完成以后，随着`Close`方法调用，数据库连接会被销毁。业务处理完成后，线程也会被释放。 在用户再一次发起请求时，又会被分配一个新的线程去处理。同样，涉及到数据库访问时，又会去创建一个新的数据库连接。连接执行完成之后，又被销毁。这样一来，每次用户请求，都需要创建一个数据库连接，每次都需要花费大量的时间在建立连接上，用户的响应时间会变的很慢。 3. 连接复用   每个线程在使用数据库连接后并不是立即销毁，而是把它交给下一个需要访问数据库的线程，多个线程共用后端相同的物理连接，实现连接的复用，这就是为什么需要连接池的最重要的一个原因。

连接复用

  以连接池的形式来管理数据库连接，每个需要访问数据库的线程每次从连接池中租借数据库连接，使用完毕后，归还给连接池，这样就可以实现连接的重复使用，避免了每次访问数据库都要去创建数据库连接，从创建改变为租借。 2. **限制连接**   数据库服务器端在处理数据库请求时，会在服务器端分配一定的资源，比如说内存，用来保存数据库查询的结果。在请求处理结束后，这些资源也会被释放。服务器端的资源是有限的，不能无限制的分配，当同时有多个数据库请求访问数据库的时候，服务器端能处理的连接数是有限的。当超过最大可分配的资源时，就会出现服务器宕机的故障。为了限制并发访问的连接数，数据库服务器端一般会设置最大的并发连接数。如果超过最大连接数，就会抛`too many connections`的异常。

限制连接

  虽然服务器端做了必要的保护限制，但是对于应用程序，一方面服务器端直接抛SQL异常，对Java程序的处理不够友好，Java程序必须要捕获这些SQL异常进行异常处理；另一方面，不应该仅仅依靠服务器端的最大连接限制，应该在数据库访问客户端的时候，就应该实现这种限流，数据库连接必须有序、可控的被线程使用，一旦出现异常，也多了一种保护手段。   所以需要在客户端的Java程序中，就实现业务线程排队获取数据库连接，限制同时获得数据库连接的线程数，起到一个限流对后端数据库保护的作用。同时，连接数过多也会对后端数据库的性能造成严重的影响，因为连接数的增多，后端数据库就会存在更多的锁的冲突与检测，加大数据库服务器端的资源的消耗，所以应该保证应用程序有序、可控的获得数据库连接。 ### 2.2 连接池 #### 2.2.1 什么是连接池？   连接池本质上是一组Java架包，介于Java应用程序和JDBC数据库物理连接之间，负责帮助应用程序来管理JDBC连接。通过连接池暴露的接口，业务程序可以获取数据库连接，使用完毕以后可以将数据库连接归还到连接池，供下一个线程使用。

连接池

  连接池对JDBC连接实行有效的管理。在连接池中，JDBC连接不足时，会自动创建连接；在空闲的连接较多时，也会自动的销毁连接。在由多个线程同时获得数据库连接时，连接池还提供了排队等待的功能，能够保证应用程序有序的获得数据库连接。 #### 2.2.2 使用连接池   以DBCP连接池为例：   DBCP连接池是Apache的一个开源的Java连接池项目，是Tomcat使用的连接池组件。DBCP连接池包括三个Java架包，分别是： * commons-dbcp.jar * commons-pool.jar * commons-logging.jar 1. 创建连接池对象   DBCP使用`BasicDataSource`对象来表示一个连接池，所以首先要创建一个`BasicDataSource`对象。因为连接池只是JDBC连接的一个管理单位，它的底层数据库访问依然是通过JDBC来实现的，所以必须告诉DBCP必要的信息才能让DBCP帮助我们自动的创建连接。这些信息与我们创建一个JDBC连接是一致的，首先要包括一个`DB_URL`、数据库的名称、数据库的用户名和密码。

连接池BasicDataSource

创建BasicDataSource： ```Java public static BasicDataSource ds = null; public final static String DRIVER_NAME = "com.mysql.dbc.Driver"; public final static String USER_NAME = "root"; public final static String PASSWORD = "root"; public final static String DB_URL = "jdbc:mysql://localhost/cloud_study"; public static void dbpoolInit() { // 创建BasicDataSource对象 ds = new BasicDataSource(); // 给BasicDataSource对象传入数据库驱动、数据库URL、数据库用户名和密码 ds.setUrl(DB_URL); ds.setDriverClassName(DRIVER_NAME); ds.setUsername(USER_NAME); ds.setPassword(PASSWORD); } ``` 2. 获取数据库连接 在创建完`BasicDataSource`对象后，应用程序拥有了一个数据库连接池，然后就可以从连接池中获取数据库连接。通过`BasicDataSource`对象提供的`getConnection`方法，可以从连接池中租借一个数据库连接，然后通过这个连接访问后端的数据库。 3. 释放数据库连接 使用完毕后，需要将连接归还给连接池供下一个线程使用。同样，调用`Connection`的`close`方法进行释放。 注：使用JDBC时，`Connection`的`close`方法实际上是销毁了连接；这里同样调用了`Connection`的`close`方法，却是将连接归还给了连接池，原因是在DBCP的实现中，将`Connection`的`close`方法进行了重写，将之前的销毁连接的逻辑改为了将数据库连接归还给数据库连接池的逻辑，所以虽然同样是调用`close`方法，但是实现的是不一样的。 [构建实例：数据库连接池-DBPoolTest](/src/main/java/com/micro/profession/jdbc/practice/DBPoolTest.java) #### 2.2.3 高级配置 在实际开发过程中，会去设置一些数据库连接的参数来帮助我们优化连接池，提高数据库访问的性能。 BasicDataSource参数： * .setInitialSize() * .setMaxTotal() * .setMaxWaitMillis() * .setMaxIdle() * .setMinIdle() 1. setInitialSize   当我们的应用第一次访问数据库的时候，往往会出现很慢的情况，这是因为连接池中没有数据库连接，需要去创建连接，这个过程是需要花费大量时间的。当连接创建完成后，后续的访问就不再需要重新创建连接，所以速度就变得很快了。   如何提高第一次访问数据库的速度呢？   可以在应用程序启动的时候，就向连接池中预置一定数量的数据库连接，来保证应用程序第一次访问的时候连接池中就有一定数量的连接。这样一来，第一次访问就不会变得很慢。可以通过`InitialSize`参数来设置连接池创建时预置的连接数，一般设置为`预期业务平均访问量`是比较合适的。 2. setMaxTotal   当连接池没有空闲的连接，又有线程需要去访问数据库的时候，此时连接池会去创建一个新的数据库连接。但是如果此时的连接数已经达到了`MaxTotal`设定的最大值，则连接池就不会为线程新建一个数据库连接，而是强制该线程进入一个等待队列进行等待，直到有其他线程归还数据库连接时再分配。`MaxTotal`实际是去设置了客户端的一个最大连接数，起到一个限流保护数据库的作用。 3.setMaxWaitMillis 进入队列的线程不可能无限制的等待，可以通过设置一个叫做`MaxWaitMillis`的参数来设置一个最大的等待时间。如果超过该时间，则应用程序会得到一个`SQLException`异常。 4. setMaxIdle 当应用程序的线程使用完连接后，将连接归还给连接池。如果此时的连接池空闲连接数超过了`MaxIdle`设置的值后，则连接池会自动的销毁这个数据库连接。这样做的目的就是可以减少后端数据库的连接数来减少不必要的资源损耗。 5. setMinIdle 如果连接池的空闲连接低于`MinIdle`设定的值后，连接池也会自动的触发去创建数据库连接，来保证连接池有足够的连接可以被租借。一般来说，为了避免连接池频繁的创建和销毁数据库连接，建议将`MinIdle`和`MaxIdle`设定为一样的值。 **DBCP定期检查** BasicDataSource定期检查参数： * .setTestWhileIdle(True) * .setMinEvictableIdleTimeMillis() * .setTimeBetweenEvictionRunsMills()   为了实现定期检查的功能，数据库服务端为了释放空闲等待的资源，默认会自动关闭空闲时间超过一定阈值的数据库连接。以MySQL数据库为例： MySQL数据库默认的服务器端会自动关闭空闲时间超过8小时的数据库连接。服务器端关闭连接以后，客户端的连接池却不知道连接已经被服务器端关闭，当应用程序的线程向连接池租借连接的时候，连接池有可能将这个失效的数据库连接租借给应用程序。当线程使用该连接的时候就会抛出一个`SQLException`的异常。   为了避免上述情况的发生，尽量保证连接池中的连接都是有效的，可以定期的对连接池中的连接的空闲时间进行一个检查，在服务器端关闭连接之前，就保证把这个连接销毁掉，重新补充新的连接，来保证应用程序从连接池中租借的连接都是有效的。`TestWhileIdle`参数可以开启该功能。`MinEvictableIdleTimeMillis`来表示销毁连接的最小空闲时间，也就是说，只有当空闲时间超过该值的时候，会被连接池自动的销毁。`TimeBetweenEvictionRunsMills`表示检查运行时间的间隔。 注：建议将`MinEvictableIdleTimeMillis`的值一定要小于服务器端自动关闭连接的阈值时间，也就是说一定要小于8小时，这样才能有效的检测空闲时间超过该值的一个空闲连接，然后去主动的关闭它，补充新的连接。 [构建实例：数据库连接池-DBPoolDbcpImpl](/src/main/java/com/micro/profession/jdbc/practice/DBPoolDbcpImpl.java) ## 3. SQL注入与防范 ### 3.1 数据库注入   在Web应用架构下，用户无法直接访问数据库，必须发送HTTP请求到Java应用服务器，然后由Java应用服务器来访问后端的数据库，所以恶意用户想要获取数据库中的核心价值数据就绕不开Java应用程序，唯一的途径就是利用业务程序的一个漏洞伪装自己的请求，欺骗业务程序，达到最终获取到数据库数据的目的。 以下为根据之前JDBC内容所编写的根据用户名和密码获取用户信息的一段应用程序： ```Java User user = null; String sql = "select * from user where userName = '" + userName + "' and password = '" + password + "'"; rs = stmt.executeQuery(sql); while (rs.next()) { user = new User(); user.setUserName(rs.getString("userName")); user.setCardNum(rs.getString("cardNum")); } return user; ```   在这段程序中，程序会根据用户名和密码去查询后端数据库的User表，看是否有跟用户名和密码匹配的用户，如果数据库返回的记录不为空，这个应用程序也就会返回一个不为空的User对象，将用户的信息返回给调用者。 这段代码在Web应用中经常用于用户登录的场景。 | 用户名 | 密码 | | :----: | :----: | | ZhangSan | 123456 | ```mysql select * from user where userName = 'ZhangSan' and password = '123456'; ```   用户在表单中输入用户名和密码，然后发送给Java应用程序所在的业务服务器，然后Java应用程序利用SQL语句去检索数据库，根据用户名和密码去匹配相应的数据库记录，如果能找到响应的用户，这条数据库记录返回就不为空，Java应用程序就获得了用户的相关信息，也就确认了这个用户为合法用户。如果User对象为空的话，则会用户认证失败，这说明用户在表单中提交的用户名和密码不正确。这是理想的用户登录的场景，看似天衣无缝，非常完美，但是事实并非如此。 | 用户名 | 密码 | | :----: | :----: | | ZhangSan';-- | 111 |   使用如上表所示的用户名和密码，该用户名和密码实际并不存在于数据库，但是依旧登录成功了，看似严格的用户登录程序认证失效了，这是为什么呢？ 问题出在访问后端数据库的SQL语句上，如下所示的发送到后端数据库的SQL语句： ```mysql select * from user where userName = 'ZhangSan';--' and password = '123456'; ```   原先设计的SQL语句是包括UserName和password这两个检索条件的，但是实际上Java应用程序发送给后端数据库的SQL语句，已经不再是原先设计的场景的SQL语句的语义。实际上，由于用户名中的分号，导致原先的一条SQL语句变成了两条SQL语句，并且在第一条语句中去掉了SQL的检索条件。同时，SQL语句的后半部分，第二条SQL语句，因为两个`-`的注释符导致被数据库认为是注释的内容，自动被忽略掉了。最终，数据库检索的仅仅是用户名为`ZhangSan`的数据库记录，不再附有密码的检索条件，利用Java应用程序动态拼接SQL的漏洞，破坏了原先Java程序设定的SQL语义，欺骗了业务服务器，恶意获取了数据库中的数据。这样，他也获得了User对象，返回给了数据库，但是检索条件只是userName，并没有对password进行检索，这就导致了应用程序出现了漏洞，他在不知道密码的情况下也可以实现合法用户的认证登录。   `SQL注入`就是用户输入表单或者URL参数中输入SQL命令达到欺骗Java应用程序的目的，破坏原有SQL的语义，发送恶意的SQL语句到数据库，导致数据库信息遭到泄露的一个Java应用程序的漏洞。 ### 3.2 问题根源   `SQL注入`漏洞的根源在于SQL语句本身是动态拼接而成的，在用户注入参数前，SQL本身的语义是不确定的，用户输入的参数如果带有SQL命令或者特殊字符，可能会导致原有的SQL的语义发生改变，原先设定SQL的语义是根据用户名密码作为WHERE的两个过滤条件，而实际执行的时候SQL语义只检索了用户名，密码被注释掉了。 ### 3.3 解决方案   参数化SQL的实现方式：首先，确定SQL的语义；随后，传入SQL的参数，能够保证SQL传入的参数不改变原先SQL的语义。这里的实现方式是利用`Connection`的`.preparedStatement`方法来创建一个`preparedStatement`对象来实现的。   `preparedStatement`对象实现了`Statement`接口定义的所有方法，但是相对于`Statement`，它最大的优势在于提供了参数化SQL的实现方式。 ```mysql Select * from user where userName = ? AND password = ? ```   调用`Connection`的`preparedStatement`方法传入一个格式化的SQL，格式化SQL与平时写的SQL不同的地方在于：所有外部需要输入的参数都使用一个`?`来代替，这样就生成了一个`preparedStatement`对象，SQL语义伴随着对象也就确定了。这里的`?`号替代了参数，实现了一个占位符的功能。这条语句（`preparedStatement`函数）确定了SQL的语义。   然后开始向SQL传入参数：按照格式化SQL注入参数从做到右的顺序，根据参数的类型，如果是整形的话，用`setInt`；字符型的话，用`setString`；如果是布尔类型的话，用`setBoolean`。参数有两个，第一个是序号，也就是参数从左到右的出现顺序，在上述所说的SQL中，userName在前，password在后，所以userName是1，password是2。第二个参数就是要输入的值了，比如UserName的值为`ZhangSan`，那么就写成`setString(1,'ZhangSan')`,同理password写成`setString(2,'123456')`。这样完成后，SQL里传入了参数，并且这个参数能保证不改变SQL语义，就可以防止SQL的注入了。`preparedStatement`是最基础也是最常用的预防SQL注入的方法。 [构建实例：SQL注入与防范-Login](/src/main/java/com/micro/profession/jdbc/practice/Login.java) ### 3.4 其他注意事项 * 严格的数据库权限管理 * 仅给予Web应用访问数据库的最小权限； * 避免Drop table等权限； | 权限 | 是否给予用户 | | :--- | :--------- | | Select | Y | | Update | Y | | Delete | 谨慎 | | Insert | Y | | All | No | * 封装数据库错误 * 禁止直接将后端数据库异常信息暴露给用户！ * 对后端异常信息进行必要的封装，避免用户直接查看到后端异常！

后端异常

* 机密信息禁止明文存储 * 涉密信息需要加密处理 * mysql可以使用AES_ENCRYPT/AES_DECRYPT加密和解密 ## 4. 事务 ### 4.1 事务原理与开发 #### 4.1.1 事务特性 事务特性以银行转账为例: 业务逻辑为：开始交易->张三账户扣除100元->李四账户增加100元->结束交易 ##### 4.1.1.1 原子性 张三扣除100元和李四增加100元，这两个过程必须作为一个整体来完成，要么全部执行，要么一个都不执行，不可分割。 整个交易必须是一个整体，要么全做，要不都不做！ ##### 4.1.1.2 一致性 在整个交易中，交易前和交易后，钱的总量是不变的。交易前张三有100元，李四0元，交易后张三把钱给了李四，张三0元，李四100元。整个交易前后钱都是一致的状态，称之为一致性。 ##### 4.1.1.3 隔离性 在张三和李四交易的同时，此时赵五需要给张三转账200元。假设两个交易在并发的执行：

隔离性

首先，交易一种，银行系统读取张三的账户，余额为100元。假如此时交易二同时在发生，银行系统也是读取了张三账户的余额，为100元。然后交易一中，因为张三给李四转账100元，张三的账户被扣除了100元，银行系统更新张三账户的余额为0元。此时交易二，赵五给张三转账200元，张三的账户因为之前读取了100元，此时加上200元，实际变为了300元。如果此时银行更新账户为300元，则银行实际上亏本了。 实际上，张三原来有100元，赵五给了200元，张三给李四转了100元，实际的账户余额应为200元。因为两个交易是并发执行的，没有进行必要的隔离，导致出现了银行系统的错误。 所谓隔离性，就是指两个交易同时发生时，在一个交易执行之前，不能受到另一个交易的影响。 ##### 4.1.1.4 持久性 两个人去银行交易转账，如果说交易结束之后，过了一段时间，因为银行机器坏了，两个人的交易不生效了，这个是没办法接受的。 持久性：一旦交易结束，无论出现任何情况，交易都应该是永久生效的。 #### 4.1.2 什么是事务？ `事务（Transaction）`是并发控制的基本单位，指作为单个逻辑工作单元执行的一系列操作，而这些逻辑工作单元需要满足`ACID`特性。 * 原子性：atomicity * 一致性：consistency * 隔离性：isolation * 持久性：durability 如果一个业务场景，有这四个特性的需求，就可以使用事务来实现。 #### 4.1.3 JDBC事务控制 如何实现事务控制？JDBC的`Connection`对象提供了三个方法可以帮助实现 一个事务逻辑： * setAutoCommit() 开启事务 将这个方法设为false，则这个`Connection`对象后续所有的执行的SQL语句都会被当做JDBC的一个事务来执行。如果设置为true，则表示`Connection`对象后续的每一个SQL都作为一个单独的语句执行，都是以非事务的方式来执行的。 注：默认情况下，都是以非事务的方式执行的，除非开始事务，将`setAutoCommit`设置为`false`。 * commit() 提交事务 开启事务模式之后，`Connection`对象后续执行的所有SQL都将会作为一个事务来执行，直到调用`Connection`的`commit`方法。`commit`方法表示这个事务被提交，也就是说事务结束，整个事务执行的SQL语句的结果都将生效。 * rollback() 回滚事务 如果当事务执行的过程中出现问题，需要回滚这个事务的时候，可以调用`Connection`对象的`rollback`方法。回滚的意思就是虽然执行了事务中的语句，但是可以回滚到事务开始之前的一个状态。 [构建实例：事务-未使用事务-TransactionTestInit](/src/main/java/com/micro/profession/jdbc/practice/Transaction/TransactionTestInit.java) [构建实例：事务-使用事务-TransactionTest](/src/main/java/com/micro/profession/jdbc/practice/Transaction/TransactionTest.java) #### 4.1.4 检查点 JDBC提供了断点处理和控制的功能。 * setSavePoint() 保存断点 * rollback(SavePoint savePoint) 回滚到断点 [构建实例：事务-回滚断点-TransactionTestSavePoint](/src/main/java/com/micro/profession/jdbc/practice/Transaction/TransactionTestSavePoint.java) #### 4.1.5 事务并发执行 在事务的四个特性中，原子性、一致性、持久性都不难理解，但是对于隔离性，涉及到一个并发控制，理解起来较为困难。以下通过进一步的涉及隔离性的例子来帮助理解日常开发过程中经常涉及到的几个概念，依旧以银行转账为例： ##### 4.1.5.1 脏读 例子：张三转账100元给李四，同时张三存银行200元

脏读

张三转账100元给李四:T1中，银行系统读取了张三的账户为100元，更新张三账户的余额为0元； 此时张三在银行又进行了存钱交易，这时候正好开始，同时也读取了张三账户余额，此时读取到的账户余额为0元，然后更新张三账户为200元； 如果此时T1张三转账给李四的交易失败了，交易进行了回滚，本来应该回滚到交易前的状态；但是此时由于余额为0元的状态被张三存钱的事务给读取了，对其他的事务产生了影响。本来最后应该有300元的张三，最后只有200元。 > 脏读：读取一个事务未提交的更新！ ##### 4.1.5.2 不可重复读 例子：T1张三读取两次账户余额，T2张三存了200元

不可重复读

T1中，张三读取了余额为100，然后在T2存钱后T1再次读取余额，变为300元，也就是说同一个事务两次读取同一个记录，结果不一样。 > 不可重复读：同一个事务中两次读取相同的记录，结果不一样！ ##### 4.1.5.3 幻读 例子：T1读取所有的用户，包含张三和李四两个用户，T2新增用户赵五

幻读

T1读取了所有的用户，包含张三和李四两个用户，但此时T2又新增了一个赵五的用户，然后T1再次读取用户列表的时候，发现读取的行记录的个数与之前读取的结果不一致。 > 幻读：两次读取的结果包含的行记录不一样！ > 幻读与不可重复读的区别是：幻读是读到的记录条数不一样，不可重复读是读到的值不一样。 #### 4.1.6 事务隔离级别 * 读未提交（read uncommitted） 该级别允许出现脏读的情况 * 读提交（read committed） 不允许出现脏读，但是允许出现不可重复读 * 重复读（repeatable read） 不允许出现不可重复读，但是允许出现幻读 * 串行化（serializable） 不允许出现幻读，但作为事务最高隔离级别，并发控制最为严格，所有的事务都是串行执行的，会导致数据库的性能变得极差。 > MySQL默认事务级别为repeatable read。 > 事务隔离级别越高，数据库性能越差，编程的难度越低。 #### 4.1.7 设置隔离级别 JDBC中，可以通过`Connection`的`getTransactionIsolation`和`setTransactionIsolation`来获得和设置事务的隔离级别。 ### 4.2 死锁分析与解决 #### 4.2.1 事务并发执行 数据库记录： | ID | UserName | Account | Corp | | :-- | :------ | :------ | :--- | | 1 | ZhangSan | 100 | Ali | | 2 | LiSi | 0 | Ali | 事务一： 张三需要给李四转账100元钱 事务二： 将张三和李四的单位改为Netease * 事务持锁 两事务并发执行： 首先：事务一更新记录一的Account数据，持有记录一的行锁；事务二更新记录二的Corp记录，持有记录二的行锁；此时两事务各自持有一个行锁 接着：事务一要更新记录二的Account数据，需要持有李四的行锁，但是李四的行锁被事务二占据，所以事务一只能等待事务二完成之后释放李四这行的行锁才能记录执行；而事务二此时需要更新张三的Corp数据，也需要持有张三这行的行锁，但是这个行锁被事务一占据，同样，事务二需要等待事务一执行完成之后才能继续执行。 这样就发生了事务一和事务二相互等待导致两个事务都无法执行下去的现象。 > 死锁： 指`两个`或者`两个以上`的事务在执行过程中，因`争夺锁资源`而造成的一种`互相等待`的现象。 #### 4.2.2 死锁产生的必要条件 1. 互斥（无法避免） * 定义：并发执行的事务为了进行必要的隔离保证执行正确，在事务结束前，需要对修改的数据库记录持锁，保证多个事务对相同数据库记录串行修改。 * 对于大型并发系统无法避免。 2. 请求和保持（无法避免） * 定义：一个事务需要申请多个资源，并且已经持有一个锁资源，在等待另一个锁资源 * 死锁仅发生在请求两个或者连个以上的锁对象 * 由于应用需要，难以消除 3. 不剥夺 * 定义：已经获得锁资源的事务，在未执行前，不能被其他事务强制剥夺，只能等本事务使用完时，由事务自己释放。 * 一般用于已经出现死锁时，通过破坏该条件达到解除死锁的目的 4. 环路等待

环路等待

* 定义：发生死锁时，必然存在一个事务——锁的环形链。 * 按照统一顺序获得锁，可以破坏该条件。 * 通过分析死锁事务之间的锁竞争关系，调整SQL的顺序，达到消除死锁的目的。 #### 4.2.3 MySQL中的锁

MySQL中的锁

X：排他锁：跟其他任何锁都是冲突的 S：共享锁：多个事务可以共享一把锁，多个锁可以兼容 #### 4.2.4 加锁方式 1. 外部加锁 * 由应用程序添加，锁依赖关系系比较容易分析 * 共享锁（S）：select * from table lock in share mode * 排他锁（X）：select * from table for update 2. 内部加锁 * 为了实现ACID特性，由数据库系统内部自动添加 * 加锁规则繁琐，与SQL执行计划、事务隔离级别、表索引结构有关 #### 4.2.5 哪些SQL需要持有锁 1. 快照读 * Innodb实现了多版本控制（MVCC），支持不加锁快照度 * select * from table where …… * 能够保证同一个Select结果集是一致的 * 不能保证同一个事务内部，Select语句和其他语句的数据一致性，如果业务需要，需通过外部显式加锁。 2. 当前读 * Select * from table lock in share mode * Select * from table for update * Update form table set …… * Insert into …… * Delete from table …… #### 4.2.6 SQL加锁分析 | ID | UserName | Account | Corp | | :-- | :------ | :------ | :--- | | 1 | ZhangSan | 100 | Ali | | 2 | LiSi | 0 | Ali | ```mysql Update user set account = 0 where id = 1 ``` 这个Update操作就是直接在记录上加排他锁，此时如果是Select操作，是不会被阻塞的。因为是快照读，但是如果是`Select for update`或者`Select for share mode`的方式，都是会被阻塞的。 ```mysql Select UserName from user where id=1 in share mode ``` 当然，如果执行`Select for share mode`这条语句的话，是对行记录加了一个共享锁。此时如果其他事务要执行`Select for share mode`的话，对同一行记录还是可以执行的，不会被阻塞。但是如果外部其他事务要执行一个`Select for update`的话，则一定会被阻塞。 #### 4.2.7 分析死锁的常用方法 MySQL数据库会自动的检测死锁并强制回滚代价最小的事务，这个不需要开发人员关心。死锁的解除，MySQL会自动的帮助我们去做，但是我们需要在死锁解除之后去分析死锁产生的SQL语句，避免死锁再次产生。 捕获死锁的命令： ```mysql show engine innodb status ``` 执行完这个命令后，会有一大段命令出现。其中有一个部分是包括死锁的，里面会列出发生死锁时，所等待的两个SQL语句，然后也会列出系统强制回滚的是哪个事务。知道了这些SQL语句，可以分析SQL语句的加锁方式，来调整SQL语句的顺序，或者改变SQL语句来保证按序获取锁资源，这样就可以有效避免死锁的产生。 ## 5. MyBatis ### 5.1 前言 #### 5.1.1 面向对象的世界与关系数据库的鸿沟 * 面向对象的世界中数据是对象。 * 关系型数据库中数据是以行、列的二元表。 #### 5.1.2 ORM 1. 介绍 * ORM(Object/Relation Mapping) * 持久化类与数据库表之间的映射关系 * 对持久化对象的操作自动转换成对关系数据库操作 2. 关系型数据库和对象的映射

关系数据库和对象的映射

* 关系数据库的每一行映射为每一个对象 * 关系数据库的每一列映射为对象的每个属性 ### 5.2 MyBatis #### 5.2.1 介绍 * 项目前身是Apache基金会表的一个开源项目iBatis * 支持自定义SQL、存储过程和高级映射的持久化框架 * 使用XML或者注解配置 * 能够映射基本数据元素、接口、Java对象到数据库 #### 5.2.2 功能架构

MyBatis功能架构

从Mybatis的功能架构来看，主要分为三大层： * 第一层为API的接口层。主要是提供给外部使用接口的API，主要是提供给程序开发人员。开发人员通过本地API来操作数据库，接口层接到调用请求后，将数请求给数据处理层，来完成具体的数据处理。 * 第二层为数据处理层。它负责具体的SQL的查找、SQL解析、SQL执行和执行结果的映射处理，它的主要目的是根据调用请求来完成一次数据库的操作。 * 第三层为基础支撑层。主要负责最基础的功能支撑，包括数据库连接管理、事务管理、配置加载、缓存处理。将它们抽象出来作为最基础的组件，为上层的数据处理层提供基础的支撑。 #### 5.2.3 工作流机制

MyBatis工作流机制

* 根据XML或者注解加载SQL语句、参数映射、结果映射到内存 * 应用程序调用API传入参数和SQL ID * MyBatis自动生成SQL语句完成数据库访问，转换执行结果返回应用程序 首先，需要在应用程序启动的时候，加载XML文件，这个XML文件定义了后端数据库的地址，同时也定义了SQL和Java之间的映射关系； 然后，应用程序调用MyBatis提供的API接口，传入参数和SQL ID，MyBatis会自动的匹配相应的SQL语句，然后生成完整的SQL语句，访问后端的数据库，转换执行的结果为Java对象，最后返回给应用程序。 #### 5.2.4 环境搭建 1. 下载架包 * JAR： * mybatis-3.2.3.jar * mysql-connector-java-5.1.12.jar 2. 配置 每个MyBatis应用都是基于`SqlSessionFactory`的实例，以它为中心。通过`SqlSessionFactory`实例可以获取能够将对象操作转换成数据库SQL的`Session`，通过一个XML配置文件可以完成一个`SqlSessionFactory`的配置。 ```xml ``` 整个XML配置文件包含了MyBatis系统的核心配置，包括后端数据库连接实例的数据源和决定事务范围和控制方式的事务管理器——`transactionManager`。 首先就是事务管理器的设置。在`transactionManager`下有`type`属性，提供了两个选项，分别是`jdbc`和`manage`。 * `jdbc`表示事务控制直接使用的是jdcb的提交和回滚来设置的，它表示MyBatis依赖于数据库源获得的数据库连接来管理事务的范围，实际上是依赖jdbc来实现事务控制的。 * `manage`表示MyBatis的事务提交和回滚是本身MyBatis框架不做任何事情，也不会去调用jdbc的事务提交和回滚，事务的控制是交给外部的容器，比如spring的方式来完成。 第二个要配置的是后端的数据库源。与jdbc一样，它包括四个属性，分别是：数据库驱动、url、用户名和密码。这里要求将`transactionManager`的`type`改为`jdbc`，因为直接使用MyBatis来完成数据库的访问。第二个在数据源方面，需要将数据库驱动、url、用户名和密码配置正确。 3. Java对象 * 构造对象 * 构建接口 MyBatis是一个ORM框架，所以要定义一些Java对象，然后建立对象对对象操作和SQL语句之间的映射关系。定义Java对象包括一些属性，这里以User也就是用户对象为例： ```java public class User { private int id; private String userName; private String corp; public User(Integer id, String userName, String corp) { this.id = id; this.userName = userName; this.corp = corp; } public int getId() { return id; } public void setId(int id) { this.id = id; } …… } ``` 首先定义User对象，包括一些属性，比如用户ID、用户名和用户所在公司等三个属性，get和set方法，还有类的构造函数。 有了Java对象后，还需要定义对这个Java对象的操作，因为MyBatis与其他传统的ORM框架是不同的，不是直接建立对象和关系数据库表数据之间的映射，而是采取更加灵活的方式，将对对象的操作与数据库的SQL语句建立映射关系。所以需要定义一些对数据的操作，这里使用Java的interface接口的方式来定义对对象的操作。 ```java public interface GetUserInfo { public User getUser(int id); // 获取用户 public void addUser(User user); // 新增用户 public void updateUser(User user); // 修改用户 public void deleteUser(User user); // 删除用户 } ``` 4. 创建Java对象和SQL语句映射关系配置文件 * 映射文件 ```xml ``` 配置文件中最重要的是要包含一个`mapper`的标签，标签有一个`namespace`属性，它的值使用的是定义接口的操作类——接口名称为`GetUserInfo`的类名加上包名来完成`namespace`属性的定义。 接下来定义具体的SQL语句：要完成的是获取数据库的信息，检索数据库获得User信息，然后映射到Java的对象中，所以这里`select`标签的`id`就定义为`getUser`，然后`parameterType`定义为`int`，因为要传入检索哪个用户。然后`resultType`定义为之前定义的User类，因为希望MyBatis把返回的结果自动的转化前面定义的Java对象，注意这里要加完整的类型，并且要求类型与Java属性名必须是相同的。 5. 注册配置文件 ```xml ``` 需要将映射文件加载到之前配置的`SqlSessionFactory`配置文件中，所以在`SqlSessionFactory`配置中添加一个`mapper`的标签，然后将这个映射文件的完整目录地址放在`resource`属性中，这样就完成了完整的MyBatis的配置。 6. 完成数据库查询

完成数据库查询

#### 5.2.5 MyBatis优势与劣势 * 优势 * 入门门槛较低 * 更加灵活，SQL优化 * 劣势 * 需要自己编写SQL，工作量打 * 数据库移植性差 ### 5.3 MyBatis进阶 ##### 5.3.1 ResultMap * ResultMap元素是MyBatis中最重要最强大的元素 * 数据库永远不是你想要的或需要它们是怎么样的 * ResultMap可以实现复杂查询结果到复杂对象关联关系的转化 1. Constructor * 类在实例化时，用来注入结果到构造方法中： * idArg - ID参数；标记结果作为ID可以帮助提高整体效能 * arg - 注入到构造方法的一个普通结果 2. Collection * 实现一对多的关联 * id - 一个ID结果；标记结果作为ID可以帮助提高整体效能 * result - 注入到字段或JavaBean属性的普通结果 #### 5.3.2 DataSource * MyBatis 3.0 内置连接池 * dataSource type = POOLED启用连接池 #### 5.3.3 数据库连接生命周期

数据库连接生命周期

#### 5.3.4 连接池常用配置选项 * poolMaximumActiveConnections * 数据库最大活跃连接数 * 考虑到随着连接数的增加，性能可能达到拐点，不建议设置过大 * poolMaximumIdleConnections * 最大空闲连接数 * 经验值建议设置与`poolMaximum`相同即可 * poolMaxmumCheckoutTime * 获取连接时如果没有`idleConnections`同时`activeConnections`达到最大值，则从`activeConnections`列表第一个连接开始，检查是否超过`poolMaxmumCheckoutTime`，如果超过，则强制使其失效，返回该连接 * 由于SQL执行时间受服务器配置、表结构不同，建议设置为预期最大SQL执行时间 * poolTimeToWait * 获取服务器端数据库连接的超时时间，如果超过该时间，则打印日志，同时重新获取 * 建议使用默认值20s * poolPingEnabled * 启用连接侦测，检查连接池中的连接是否为有效连接 * 默认关闭，建议启用，防止服务器端异常关闭，导致客户端错误 * poolPingQuery * 侦测SQL，建议使用select1，开销小 * poolPingConnectionsNotUsedFor * 侦测时间，建议小于服务器端超时时间，MySQL默认超时8小时