• MySQL jdbc,事务,连接池

    ​​​

    3-MySQL jdbc,事务,连接

    1 jdbc

    1.1 jdbc概述#

            JDBC（Java DataBase Connectivity,java数据库连接技术）是一种用于执行SQL语句的Java API。

            JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。

            JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。

    JDBC与数据库驱动的关系：接口与实现的关系。

    file

    1.2 jdbc原理#

    Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动。  

    file

    其中：

            DriverManager：用于注册驱动

            Connection：表示与数据库创建的连接

            Statement：操作数据库sql语句的对象

            ResultSet：结果集或一张虚拟表

    1.3 jdbc编程步骤#

    file

    1.3.1 加载数据库驱动#

    Class.forName(“com.mysql.jdbc.Driver”);

    创建 com.mysql.jdbc.Driver 这个类的对象供连接数据库使用

    JAVA 规范中明确规定：所有的驱动程序必须在静态初始化代码块中将驱动注册到驱动程序管理器中。

    注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。在jar包中，存在一个java.sql.Driver配置文件，文件中指定了com.mysql.jdbc.Driver

    1.3.2 通过DriverManager 获取数据库连接#

    1. // 获取数据库连接
    2. Connection conn = DriverManager.getConnection(String url, String user, String password);
    3. //参数说明：
    4. //        1）url：需要连接数据库的位置（网址） 
    5. //        2）user：登录数据库用户名
    6. //        3）password：登录数据库密码

    Connection 连接是通过 DriverManager 的静态方法 getConnection(.....)来得到的， 这个方法的实质是把参数传到实际的 Driver 中的 connect()方法中来获得数据库连接的。

    Mysql 的 url 格式：jdbc:mysql://localhost或ip:3306/要连接的数据库名称 [?characterEncoding=UTF-8]

    如：

            本地连接：jdbc:mysql://localhost:3306/hainiudb

            远程连接：jdbc:mysql://192.168.31.131:3306/hainiudb

    注意：如果出现关于SSL的警告，可以在连接后添加useSSL=false

    1.3.3 获得一个 Statement 对象#

    Statement stmt = conn.createStatement();

    1.3.4 通过 Statement 执行 Sql 语句#

    file

    1.3.5 处理结果集#

            使用 Connection 对象获得一个 Statement， Statement 中的 executeQuery(String sql) 方法可以使用 select 语句查询，并且返回一个结果集 ResultSet 通过遍历这个结果集，可以获得 select语句的查寻结果，.

            ResultSet 的 next()方法会操作一个游标从第一条记录的前面开始读取，直到最后一条记录。

            executeUpdate(String sql) 方法用于执行 DDL 和 DML 语句， 比如可以update， delete 操作。

    只有执行 select 语句才有结果集返回。

    1.3.6 关闭数据库连接（释放资源）#

    1. rs.close(); //关闭结果集ResultSet 
    2. stmt.close();// 关闭Statement
    3. con.close();// 关闭数据库连接Connection 
    4. //ResultSet Statement Connection 是依次依赖的。

    注意：要按先 ResultSet 结果集，后 Statement，最后 Connection 的顺序关闭资源，

    因为 Statement 和 ResultSet 是需要连接时才可以使用的，所以在使用结束之后有

    可能其它的 Statement 还需要连接，所以不能先关闭 Connection。

    2 jdbc编程实例

    2.1 环境准备#

    创建mysql工程项目

    file

    加载mysql驱动时，要添加mysql 的驱动包。

    在pom文件汇总引入mysql驱动包

    1. <dependency>
    2.     <groupId>mysql</groupId>
    3.     <artifactId>mysql-connector-java</artifactId>
    4.     <version>5.1.49</version>
    5. </dependency>

    2.2 数据准备#

    沿用 hainiudb 数据库的 product 表 和 category 表

    2.3 jdbc添加一行数据#

    需求：用java jdbc给category表添加一行图书类别

    1. package com.hainiu.demo;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.DriverManager;
    5. import java.sql.Statement;
    6.  
    7. /**
    8.  * 需求：用java jdbc给category表添加一行图书类别
    9.  */
    10. public class JDBCInsertDemo {
    11.     public static void main(String[] args) {
    12.         // 数据库连接四大属性
    13.         String driver = "com.mysql.jdbc.Driver";
    14.         String url = "jdbc:mysql://10.88.10.9:3306/hainiudb?characterEncoding=UTF-8";
    15.         String user = "root";
    16.         String password = "12345678";
    17.  
    18.         // 获取数据库连接
    19.         Connection conn = null;
    20.         Statement stat = null;
    21.         try{
    22.             // 加载驱动
    23.             Class.forName(driver);
    24.             conn = (Connection) DriverManager.getConnection(url, user, password);
    25.             // 编写SQL语句
    26.             String sql = "insert into category(cid, cname) values (7, '图书');";
    27.             // 创建SQL语句的执行对象
    28.             stat = conn.createStatement();
    29.             // 执行SQL语句并获取结果
    30.             int executeUpdate = stmt.executeUpdate(sql);
    31.             if(executeUpdate > 0){
    32.                 System.out.println("插入成功");
    33.             }else{
    34.                 System.out.println("插入失败");
    35.             }
    36.         }catch(Exception e){
    37.             e.printStackTrace();
    38.         }finally {            
    39.             //--关闭连接            
    40.             if(conn!=null) {                
    41.                 try {                    
    42.                     conn.close();                
    43.                 } catch (SQLException e) {                                 
    44.                         e.printStackTrace();                
    45.                 }finally {                    
    46.                     conn=null;                
    47.                 }            
    48.             }            
    49.  
    50.             if(stat!=null) {                
    51.                 try {                    
    52.                     stat.close();                
    53.                 } catch (SQLException e) {                                       
    54.                         e.printStackTrace();                
    55.                 }finally {                    
    56.                     stat=null;                
    57.                 }            
    58.             }        
    59.         }   
    60.     }
    61. }

    添加完毕，去数据库查看结果。

    2.4 jdbc修改数据#

    需求：用java jdbc 把category表中 图书 类别 改成  图书/电子书 类别

    1. package com.hainiu.demo;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.DriverManager;
    5. import java.sql.Statement;
    6.  
    7. /**
    8.  * 需求：用java jdbc 把category表中 图书 类别 改成  图书/电子书 类别
    9.  */
    10. public class JDBCUpdateDemo {
    11.     public static void main(String[] args) {
    12.         // 数据库连接四大属性
    13.         String driver = "com.mysql.jdbc.Driver";
    14.         String url = "jdbc:mysql://127.0.0.1:3306/hainiudb?characterEncoding=UTF-8";
    15.         String user = "root";
    16.         String password = "12345678";
    17.  
    18.         // 获取数据库连接
    19.         Connection conn = null;
    20.         Statement stat = null;
    21.         try{
    22.             // 加载驱动
    23.             Class.forName(driver);
    24.  
    25.             conn = (Connection) DriverManager.getConnection(url, user, password);
    26.             // 编写SQL语句
    27.             String sql = "update category set cname='图书/电子书' where cname='图书';";
    28.             // 创建SQL语句的执行对象
    29.             stat = conn.createStatement();
    30.             // 执行SQL语句并获取结果
    31.             int executeUpdate = stmt.executeUpdate(sql);
    32.             if(executeUpdate > 0){
    33.                 System.out.println("修改成功");
    34.             }else{
    35.                 System.out.println("修改失败");
    36.             }
    37.         }catch(Exception e){
    38.             e.printStackTrace();
    39.         }finally {            
    40.             //--关闭连接            
    41.             if(conn!=null) {                
    42.                 try {                    
    43.                     conn.close();                
    44.                 } catch (SQLException e) {                                 
    45.                         e.printStackTrace();                
    46.                 }finally {                    
    47.                     conn=null;                
    48.                 }            
    49.             }            
    50.  
    51.             if(stat!=null) {                
    52.                 try {                    
    53.                     stat.close();                
    54.                 } catch (SQLException e) {                                       
    55.                         e.printStackTrace();                
    56.                 }finally {                    
    57.                     stat=null;                
    58.                 }            
    59.             }      
    60.         }
    61.     }
    62. }

    修改完毕，去数据库查看结果。

    2.5 jdbc删除数据#

    需求：用java jdbc 把category表中  图书/电子书 类别删除

    1. package com.hainiu.demo;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.DriverManager;
    5. import java.sql.Statement;
    6.  
    7. public class JDBCDeleteDemo {
    8.     public static void main(String[] args) {
    9.         // 数据库连接四大属性
    10.         String driver = "com.mysql.jdbc.Driver";
    11.         String url = "jdbc:mysql://127.0.0.1:3306/hainiudb?characterEncoding=UTF-8";
    12.         String user = "root";
    13.         String password = "12345678";
    14.  
    15.         // 获取数据库连接
    16.         Connection conn = null;
    17.         Statement stat = null;
    18.         try{
    19.             // 加载驱动
    20.             Class.forName(driver);
    21.  
    22.             conn = DriverManager.getConnection(url, user, password);
    23.             // 编写SQL语句
    24.             String sql = "delete from category where cname='图书/电子书';";
    25.             // 创建SQL语句的执行对象
    26.             stat = conn.createStatement();
    27.             // 执行SQL语句并获取结果
    28.             int executeUpdate = stmt.executeUpdate(sql);
    29.             if(executeUpdate > 0){
    30.                 System.out.println("删除成功");
    31.             }else{
    32.                 System.out.println("删除失败");
    33.             }
    34.         }catch(Exception e){
    35.             e.printStackTrace();
    36.         }finally {            
    37.             //--关闭连接            
    38.             if(conn!=null) {                
    39.                 try {                    
    40.                     conn.close();                
    41.                 } catch (SQLException e) {                                 
    42.                         e.printStackTrace();                
    43.                 }finally {                    
    44.                     conn=null;                
    45.                 }            
    46.             }            
    47.             if(stat!=null) {                
    48.                 try {                    
    49.                     stat.close();                
    50.                 } catch (SQLException e) {                                       
    51.                         e.printStackTrace();                
    52.                 }finally {                    
    53.                     stat=null;                
    54.                 }            
    55.             }      
    56.         }
    57.     }
    58. }

    删除完毕，去数据库查看结果。

    2.6 jdbc查询数据#

    需求：用java jdbc查询product表中价格>5000的商品， 并把多条商品信息封装成Product对象列表返回。

    通过查询SQL ： select pname, price from product where price>5000; 

    发现查询会两个字段，要把这两个字段的数据封装到对象中

            首先要定义Product类

            然后再将查询结果放到该对象中

            最后再把对象放到列表中返回

    1）定义Product类

    1. package com.hainiuxy;
    2.  
    3. /**
    4.  * 描述商品的实体类
    5.  */
    6. public class Product {
    7.     /**
    8.      * 商品名称
    9.      */
    10.     private String pname;
    11.  
    12.     /**
    13.      * 商品价格
    14.      */
    15.     private int price;
    16.  
    17.     public Product() {
    18.     }
    19.  
    20.     public Product(String pname, int price) {
    21.         this.pname = pname;
    22.         this.price = price;
    23.     }
    24.  
    25.     public String getPname() {
    26.         return pname;
    27.     }
    28.  
    29.     public void setPname(String pname) {
    30.         this.pname = pname;
    31.     }
    32.  
    33.     public int getPrice() {
    34.         return price;
    35.     }
    36.  
    37.     public void setPrice(int price) {
    38.         this.price = price;
    39.     }
    40.  
    41.     @Override
    42.     public String toString() {
    43.         return "Product[" + this.pname + ", " + this.price + "]";
    44.     }
    45. }

    2）代码实现

    1. package com.hainiuxy;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.DriverManager;
    5. import java.sql.ResultSet;
    6. import java.sql.Statement;
    7. import java.util.ArrayList;
    8. import java.util.List;
    9.  
    10. /**
    11.  * 需求：用java jdbc查询product表中价格>5000的商品， 并把多条商品信息封装成Product对象列表返回。
    12.  */
    13. public class JDBCSearchDemo {
    14.  
    15.     public static void main(String[] args) throws Exception {
    16.         // 数据库连接四大属性
    17.         String driver = "com.mysql.jdbc.Driver";
    18.         String url = "jdbc:mysql://127.0.0.1:3306/hainiudb?characterEncoding=UTF-8";
    19.         String user = "root";
    20.         String password = "12345678";
    21.  
    22.         // 加载驱动
    23.         Class.forName(driver);
    24.  
    25.         // 采用自动关闭连接方式
    26.         try (
    27.                 // 获取数据库连接
    28.                 Connection conn = (Connection) DriverManager.getConnection(url, user, password);
    29.         ) {
    30.  
    31.             List<Product> products = getProducts(conn);
    32.             for (Product p : products) {
    33.                 System.out.println(p);
    34.             }
    35.  
    36.         } catch (Exception e) {
    37.             e.printStackTrace();
    38.         }
    39.     }
    40.  
    41.     /**
    42.      * 将查询结果封装成Product对象列表返回
    43.      *
    44.      * @param conn 连接
    45.      */
    46.     private static List<Product> getProducts(Connection conn) {
    47.         // 编写SQL语句
    48.         String sql = "select pname, price from product where price>5000";
    49.         List<Product> list = new ArrayList<Product>();
    50.         try (
    51.                 // 创建SQL语句的执行对象
    52.                 Statement stmt = conn.createStatement();
    53.                 // 执行SQL语句并获取结果
    54.                 ResultSet rs = stmt.executeQuery(sql);
    55.         ) {
    56.             // 遍历数据
    57.             while (rs.next()) {
    58.                 String pname = rs.getString(1);
    59.                 int price = rs.getInt(2);
    60.                 // 将一行的结果封装成 Product 对象
    61.                 Product product = new Product(pname, price);
    62.                 list.add(product);
    63.             }
    64.  
    65.         } catch (Exception e) {
    66.             e.printStackTrace();
    67.         }
    68.         return list;
    69.     }
    70. }

    在控制台查看输出结果。

    2.7 封装ConnectionUtil工具用于获取连接#

    发现上面的代码除了业务逻辑外，就剩下获取连接的代码了

    为了使程序员更专注于业务，现封装ConnectionUtil工具类来获取连接

    ConnectionUtil工具类代码如下：

    1. public class ConnectionUtil {
    2.     // 私有构造 不让其他人创建对象
    3.     private ConnectionUtil() {}
    4.  
    5.     // 数据库连接四大属性
    6.     private static final String DRIVER = "com.mysql.jdbc.Driver";
    7.     private static final String URL = "jdbc:mysql://127.0.0.1:3306/hainiudb?characterEncoding=UTF-8";
    8.     private static final String USERNAME = "root";
    9.     private static final String PASSWORD = "12345678";
    10.  
    11.     // 静态代码块 -- 加载驱动
    12.     static {
    13.         try {
    14.             Class.forName(DRIVER);
    15.         } catch (ClassNotFoundException e) {
    16.             e.printStackTrace();
    17.         }
    18.     }
    19.  
    20.     /**
    21.      * 获取数据库连接
    22.      * @return
    23.      * @throws SQLException
    24.      */
    25.     public static Connection getConnection() throws SQLException {
    26.         Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    27.         return connection;
    28.     }
    29.  
    30.     /**
    31.      * 关闭结果集、SQL处理对象和连接
    32.      * @param rs
    33.      * @param stat
    34.      * @param conn
    35.      * @throws SQLException
    36.      */
    37.     public static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException {
    38.        //--关闭连接            
    39.             if(conn!=null) {                
    40.                 try {                    
    41.                     conn.close();                
    42.                 } catch (SQLException e) {                                 
    43.                         e.printStackTrace();                
    44.                 }finally {                    
    45.                     conn=null;                
    46.                 }            
    47.             }            
    48.             if(rs!=null) {                
    49.                 try {                    
    50.                     rs.close();             
    51.                 } catch (SQLException e) {                                       
    52.                         e.printStackTrace();                
    53.                 }finally {                    
    54.                     rs=null;                
    55.                 }            
    56.             }            
    57.             if(stat!=null) {                
    58.                 try {                    
    59.                     stat.close();                
    60.                 } catch (SQLException e) {                                       
    61.                         e.printStackTrace();                
    62.                 }finally {                    
    63.                     stat=null;                
    64.                 }            
    65.             }      
    66.     }
    67.  
    68. }

    改造后代码如下：

    1. /**
    2.  * 需求：用java jdbc查询product表中价格>5000的商品， 并把多条商品信息封装成Product对象列表返回。
    3.  */
    4. public class JDBCSearchDemo1 {
    5.     public static void main(String[] args) throws Exception {
    6.         try (
    7.                 // 获取数据库连接
    8.                 Connection conn = ConnectionUtil.getConnection();
    9.         ) {
    10.  
    11.             List<Product> products = getProducts(conn);
    12.             for (Product p : products) {
    13.                 System.out.println(p);
    14.             }
    15.  
    16.         } catch (Exception e) {
    17.             e.printStackTrace();
    18.         }
    19.     }
    20.  
    21.     /**
    22.      * 将查询结果封装成Product对象列表返回
    23.      * 
    24.      * @param conn
    25.      *            连接
    26.      */
    27.     private static List<Product> getProducts(Connection conn) {
    28.         // 编写SQL语句
    29.         String sql = "select pname, price from product where price>5000";
    30.         List<Product> list = new ArrayList<Product>();
    31.         try (
    32.                 // 创建SQL语句的执行对象
    33.                 Statement stmt = conn.createStatement();
    34.                 // 执行SQL语句并获取结果
    35.                 ResultSet rs = stmt.executeQuery(sql);) {
    36.             // 遍历数据
    37.             while (rs.next()) {
    38.                 String pname = rs.getString(1);
    39.                 int price = rs.getInt(2);
    40.                 // 将一行的结果封装成 Product 对象
    41.                 Product product = new Product(pname, price);
    42.                 list.add(product);
    43.             }
    44.         } catch (Exception e) {
    45.             e.printStackTrace();
    46.         }
    47.         return list;
    48.     }
    49. }

    3 预处理对象

    3.1 SQL注入问题#

    SQL注入：用户输入的内容作为了SQL语句语法的一部分，改变了原有SQL真正的意义。

    比如：

    1. package com.hainiu.test;
    2. import java.sql.Connection; 
    3. import java.sql.ResultSet; 
    4. import java.sql.Statement; 
    5. import java.util.Scanner;
    6. import com.hainiu.util.JdbcUtil;
    7. /** @author 作者 薪牛: 
    8. @version 创建时间：2022年12月5日 下午6:49:30 
    9.  **/
    10. public class LoginTest {    
    11. public static void main(String[] args) {        
    12.     // 要求用户输入用户名和密码        
    13.     Scanner sc = new Scanner(System.in);        
    14.     System.out.println("请求输入用户名：");        
    15.     String name = sc.nextLine();  
    16.     String name = sc.nextLine();        
    17.     System.out.println("请输入密码：");        
    18.     String password = sc.nextLine();        
    19.     loginTest(name, password);        
    20.     // loginTestPrepared(name,password);    
    21. }
    22.     private static void loginTest(String name, String password) {        
    23.     // 将获取到的值和数据库中的数据作比对        
    24.     Connection conn = null;        
    25.     Statement stat = null;        
    26.     ResultSet rs = null;        
    27.     try {            
    28.     conn = JdbcUtil.getConnection();            
    29.     stat = conn.createStatement();            
    30.     rs = stat.executeQuery("select * from user1 where name='" + name + "' and password='" + password + "'");            
    31.     if (rs.next()) {                
    32.     System.out.println("登录成功");            
    33.     } else {                
    34.     System.out.println("登录失败");            }
    35.         } catch (Exception e) {            
    36.         e.printStackTrace();        
    37.         } finally {            
    38.         JdbcUtil.close(conn, stat, rs);        
    39.         }
    40.     }
    41. }

    SQL注入是很危险的，如果有个user表，登录时需要校验用户名和密码，那要是被SQL注入，系统就可以随意登录了。

    如何解决？

    3.2 预处理对象#

    PreparedStatement：

        预编译对象，是Statement对象的子类。

    特点：

        性能高

        会把sql语句先编译

        能过滤掉用户输入的关键字。

    PreparedStatement预处理对象，处理的每条sql语句中所有的实际参数，都必须使用占位符?替换。

    使用预处理对象解决SQL注入问题

    示例：

    1. private static void loginTest2(String name, String password) {
    2. // 将获取到的值和数据库中的数据作比对
    3. Connection conn = null;
    4. PreparedStatement ps = null;
    5. ResultSet rs = null;
    6. try {
    7.     conn = JdbcUtil.getConnection();
    8.     ps = conn.prepareStatement("select * from user1 where name=? and password=?");
    9.     ps.setString(1, name);
    10.     ps.setString(2, password);
    11.     rs=ps.executeQuery();
    12.     if (rs.next()) {
    13.  
    14. System.out.println("登录成功");
    15.     } else {
    16.  
    17. System.out.println("登录失败");
    18.     }
    19.  
    20. } catch (Exception e) {
    21.     e.printStackTrace();
    22. } finally {
    23.     JdbcUtil.close(conn, ps, rs);
    24. }
    25. }

    4 JDBC事务

    4.1 什么是事务#

    数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令；

    事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

    比如转账业务：   

            小强 给 小亮 转账 100 元， 小强-100， 小亮+100，只有两个update操作都成功时转账才能成功。

            如果有一方update 失败， 转账都会失败。

            为了 保证两个update操作都成功，需要把这两个操作放在一个事务中。 

    不是所有的存储引擎支持事务，MyISAM不支持事务；InnoDB支持事务。

    4.2 事务的4个特性#

    • 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

    • 一致性（Consistency） :执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的

    • 隔离性（Isolation） :多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

    • 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

    4.3 MySQL事务操作#

    MySQL数据库是默认自动提交事务的。

    准备数据：

    1. -- 创建账号表
    2. create table account(
    3.     id int primary key auto_increment,
    4.     `name` varchar(20),
    5.     money double
    6. );
    7. -- 添加数据
    8. insert into account values (null,'小明',10000);
    9. insert into account values (null,'小强',10000);
    10. insert into account values (null,'小亮',10000);

    查看事务的提交方式：

    1. -- 查询结果：1 表示自动提交； 0 表示手动提交
    2. SELECT @@autocommit;
    3.  
    4. -- 修改事务的提交方式
    5. SET @@autocommit = 0; -- 设置手动提交

    事务操作：

    1. -- 开启事务
    2. START TRANSACTION;
    3. 或者
    4. BEGIN;
    5.  
    6. -- 增删改操作
    7.  
    8. -- 提交事务
    9. COMMIT; -- 操作生效，数据发生变化 -- 持久化
    10.  
    11. -- 回滚事务
    12. ROLLBACK; -- 操作无效，数据回滚到开启事务之前的状态

    注意：当autocommit的值为0时，可以省略START TRANSACTION 或者 BEGIN 。此时当前连接下的所有SQL语句都是事务的形式，需要手动提交或回滚。

    示例：

    1. -- 开启事务（提交或者回滚事务会关闭事务）
    2. START TRANSACTION;
    3.  
    4. -- 给小明的账户减少1000元
    5. UPDATE account SET money = money - 1000 WHERE `name` = '小明';
    6.  
    7. -- 给小强的账户增加1000元
    8. UPDATE account SET money = money + 1000 WHERE `name` = '小强';
    9.  
    10. -- 提交事务
    11. COMMIT;

    此时，两个sql语句执行成功，我们提交事务，数据被持久化到数据库。

    file

    1. -- 开启事务（提交或者回滚事务会关闭事务）
    2. START TRANSACTION;
    3.  
    4. -- 给小明的账户减少1000元
    5. UPDATE account SET money = money - 1000 WHERE `name` = '小明';
    6.  
    7. -- 出错了
    8.  
    9. -- 给小强的账户增加1000元
    10. UPDATE account SET money = money + 1000 WHERE `name` = '小强';
    11.  
    12. -- 回滚事务
    13. ROLLBACK;

    如果在多条SQL语句执行过程中，产生错误，回滚事务，此时数据回退到开启事务之前的状态。

    file

    4.4 JDBC事务操作#

    语法：

    1. try {
    2.     // 设置开启事务
    3.     conn.setAutoCommit(false);
    4.     // jdbc操作1
    5.     ...
    6.     // jdbc操作n
    7.     // 提交事务 -- 如果SQL语句全部执行成功，没有异常，提交事务
    8.     conn.commit();    
    9. } catch(Exception e) {
    10.     // 事务回滚 -- 如果捕获到异常， 回滚事务
    11.     conn.rollback();
    12. }

    代码示例：

    版本一：不加入事务管理

    1. public class TransactionDemo01 {
    2.     public static void main(String[] args) throws SQLException {
    3.         // 获取连接
    4.         Connection conn = ConnectionUtil.getConnection();
    5.         int money = 1000;
    6.         String sql1 = "update account set money = money-? where name=?";
    7.         String sql2 = "update account set money = money-? where name=?";
    8.  
    9.         try (
    10.                 PreparedStatement state1 = conn.prepareStatement(sql1);
    11.                 PreparedStatement state2 = conn.prepareStatement(sql2);
    12.         ) {
    13.             // 第一个操作
    14.             state1.setInt(1, money);
    15.             state1.setString(2, "小明");
    16.             // 执行
    17.             int result1 = state1.executeUpdate();
    18.             System.out.println(result1);
    19.  
    20.             // 出现异常
    21.             System.out.println(1/0);
    22.  
    23.             // 第二个操作
    24.             state2.setInt(1, money);
    25.             state2.setString(2, "小强");
    26.             // 执行
    27.             int result2 = state1.executeUpdate();
    28.             System.out.println(result2);
    29.         } catch (Exception e) {
    30.             e.printStackTrace();
    31.         }
    32.     }
    33. }

    此时，我们查看数据库就会发现数据出现问题，小明的账户减少了，但小强的账户却没有增加。

    版本2：加入事务管理

    1. public class TransactionDemo02 {
    2.     public static void main(String[] args) throws SQLException {
    3.         // 获取连接
    4.         Connection conn = ConnectionUtil.getConnection();
    5.         int money = 1000;
    6.         String sql1 = "update account set money = money-? where name=?";
    7.         String sql2 = "update account set money = money-? where name=?";
    8.  
    9.         try (
    10.                 PreparedStatement state1 = conn.prepareStatement(sql1);
    11.                 PreparedStatement state2 = conn.prepareStatement(sql2);
    12.         ) {
    13.             // 开启事务 -- 通过连接对象设置自动提交为false
    14.             conn.setAutoCommit(false);
    15.             // 第一个操作
    16.             state1.setInt(1, money);
    17.             state1.setString(2, "小明");
    18.             // 执行
    19.             int result1 = state1.executeUpdate();
    20.             System.out.println(result1);
    21.  
    22.             // 出现异常
    23.             System.out.println(1/0);
    24.  
    25.             // 第二个操作
    26.             state2.setInt(1, money);
    27.             state2.setString(2, "小强");
    28.             // 执行
    29.             int result2 = state1.executeUpdate();
    30.             System.out.println(result2);
    31.  
    32.             // 如果SQL语句全部执行成功，提交事务
    33.             conn.commit();
    34.         } catch (Exception e) {
    35.             e.printStackTrace();
    36.             // 如果捕获到异常，回滚事务
    37.             conn.rollback();
    38.         }
    39.     }
    40. }

    此时，我们查看数据库，发现数据仍然保持原来的状态。

    4.5 事务的隔离级别#

    4.5.1 事务的并发访问问题#

    如果不考虑隔离性，事务存在3种并发访问问题。

    1）脏读：一个事务读到了另一个事务未提交的数据。

    file

    2）不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结果不一致。

    file

    3）虚读 /幻读：一个事务读到了另一个事务已经提交(insert)的数据。导致另一个事务，在事务中多次查询的结果不一致。

    file

    4.5.2 事务并发问题的解决方案——隔离级别#

    数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

    1. read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。
    2. read committed 读已提交，一个事务读到另一个事务已经提交的数据。
    3. repeatable read  可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。
    4. serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。

    这四种隔离级别解决的并发访问问题如下：

    file

    安全和性能对比

    安全性：serializable > repeatable read > read committed > read uncommitted

    性能： serializable < repeatable read < read committed < read uncommitted

    SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。 
    Read Uncommitted（读取未提交内容）

           在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
    Read Committed（读取提交内容）

           这是大多数数据库系统的默认隔离级别（例如oracle，但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
    Repeatable Read（可重读）

           这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

    Serializable（可串行化） 
           这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

             这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

             脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

             不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新了原有的数据。

             幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

    查询和设置数据库隔离级别的命令：

    1. -- 查询数据库事务隔离级别
    2. SELECT @@tx_isolation;
    3.  
    4. -- 修改数据库事务隔离级别
    5. -- 设置全局事务隔离级别 -- 在重启MySQL数据库之后失效
    6. -- 设置全局事务隔离级别 读未提交: READ UNCOMMITTED
    7. SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    8.  
    9. -- 设置全局事务隔离级别 读已提交: READ COMMITTED
    10. SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    11.  
    12. -- 设置全局事务隔离级别 可重复读: REPEATABLE READ
    13. SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    14.  
    15. -- 设置全局事务隔离级别 串行化: SERIALIZABLE
    16. SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    17.  
    18. -- ----------------------------------------------------
    19.  
    20. -- 设置会话事务隔离级别 -- 在重新建立连接之后失效
    21. -- 设置会话事务隔离级别 读未提交: READ UNCOMMITTED
    22. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    23.  
    24. -- 设置会话事务隔离级别 读已提交: READ COMMITTED
    25. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    26.  
    27. -- 设置会话事务隔离级别 可重复读: REPEATABLE READ
    28. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    29.  
    30. -- 设置会话事务隔离级别 串行化: SERIALIZABLE
    31. SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    4.5.3 四种隔离级别演示#

    脏读问题：

    file

    再开启一个事务进行一下修改金额操作

    file

    此时查看第一个事务，虽然第二个事务还没有提交事务操作，但是数据发生了变化

    file

    怎么解决这种问题？--将隔离级别改为读已提交

    脏读问题解决：

    file

    通过第二个会话窗口开启事务，并修改小明账户的金额，此时不提交事务，发现两次查看到的信息一直

    file

    如果第二个事务发生了commit操作，则看到的数据则不一致，这样就解决了一个事务读取到另一个事务还未提交的脏读的情况。

    file

    file

    但是对于第一个事务来说，第二个事务的操作是透明的，但是当第一个事务多次的时候，看到的东西是不一样的，这就是不可重复读的问题。

    怎么解决呢--》将隔离级别改为可重复读即可

    不可重复读问题解决：

    第一个事务修改隔离级别并查看账户信息。

    file

    第二个事务，修改隔离级别，并进行账户金额修改，并且commit

    file

    发现第一个事务多次查看账户信息，是不变的，这样就解决了不可重复读的问题。

    file

    知道第一个事务结束后，才能看到第二个事务修改后的结果

    file

    但是不可重复读的问题解决了，新的问题出现了，幻读的问题出现了

    第一个事务开启事务查看账户信息

    file

    第二个账户开启事务，新增’小赵‘的账户信息

    file

    第一个事务此时想新增‘小王’信息，发现报错，但是对于小王来说，查的信息是没有id为4的信息，为什么不让插入，难道有幻觉吗？这就是幻读的问题

    file

    怎么解决幻读的问题，将隔离级别改为串行化

    幻读问题解决：

    第一个事务修改隔离级别为 串行化 并开启事务进行查询账户信息

    file

    第二个事务修改隔离级别并开启事务添加小王的账户信息，发现事务卡着不动，添加不进去

    file

    知道第一个事务进行了commit之后，第二个事务才能进行操作，串行化能保证同一时刻有一个事务进行操作，避免了幻读的问题，但是性能不高，所以一般不用

    file

    第二个事务执行成功

    file

    5 数据库连接池

    5.1 为什么用连接池#

    file

     如果每一次数据访问请求都需经历下面的过程：

             建立数据库连接 --> 打开数据库 --> 存取数据 --> 关闭数据库连接

            而连接并打开数据库是一件既消耗资源又费时的工作，那么频繁发生这种数据库操作时，系统的性能必然会急剧下降。

    5.2 连接池原理#

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

    file

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

        连接池的使用：

                连接池初始化时，会创建多个连接放池子里

                在使用连接时，从连接池中申请连接使用，使用完成之后，再将连接交还给连接池，以备后续重复利用

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

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

    常见的连接池技术

            DBCP（DataBase Connection Pool）数据库连接池，是java数据库连接池的一种，由Apache开发，通过数据库连接池，可以让程序自动管理数据库连接的释放和断开。

            Druid 是Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。

            C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。

            Tomcat-JDBC是Spring Boot中自动配置优先级最高的连接池方案，它的出现是用来替代Apache早期的连接池产品——DBCP 1.x。

            HikariCP同样是一个十分快速、简单、可靠的及十分轻量级的连接池，只有130KB，在GitHub上看到的是"光HikariCP"的名称，光就是说明它十分快。

    下面从连接性能和查询性能上比较

    file

    file

    结论 : 

            性能表现：hikariCP > druid > tomcat-jdbc > dbcp > c3p0。

            根据几种数据源的对比 hikari 无疑性能最优秀的,但是因为是最新技术可能存在潜在的bug，所以我们要使用目前比较稳定的阿里的druid数据源;

    5.4 druid数据源配置#

    1）添加jar包

    1. <dependency>
    2.     <groupId>com.alibaba</groupId>
    3.     <artifactId>druid</artifactId>
    4.     <version>1.1.9</version>
    5. </dependency>

    2）修改工具类：

    1. public class JDBCUtil {
    2.     // 私有构造 不让其他人创建对象
    3.     private JDBCUtil() {}
    4.  
    5.     // 数据库连接四大属性
    6.     private static final String DRIVER = "com.mysql.jdbc.Driver";
    7.     private static final String URL = "jdbc:mysql://127.0.0.1:3306/hainiu_43_test05?useSSL=false";
    8.     private static final String USERNAME = "root";
    9.     private static final String PASSWORD = "root";
    10.  
    11.     // 初始化druid连接池
    12.     private static DruidDataSource dataSource = null;
    13.  
    14.     // 静态代码块 -- 初始化连接池
    15.     static {
    16.         // 创建连接池对象
    17.         dataSource = new DruidDataSource();
    18.         dataSource.setDriverClassName(DRIVER);
    19.         dataSource.setUrl(URL);
    20.         dataSource.setUsername(USERNAME);
    21.         dataSource.setPassword(PASSWORD);
    22.     }
    23.  
    24.     /**
    25.      * 获取数据库连接
    26.      * @return
    27.      * @throws SQLException
    28.      */
    29.     public static Connection getConnection() {
    30.         // 从连接池数据源 获取一条连接
    31.         DruidPooledConnection conn = null;
    32.         try {
    33.             conn = dataSource.getConnection();
    34.         } catch (SQLException e) {
    35.             e.printStackTrace();
    36.         }
    37.         return conn;
    38.     }
    39.  
    40.     /**
    41.      * 关闭结果集、SQL处理对象和连接
    42.      * @param resultSet
    43.      * @param statement
    44.      * @param connection
    45.      * @throws SQLException
    46.      */
    47.     public static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException {
    48.         //--关闭连接            
    49.             if(conn!=null) {                
    50.                 try {                    
    51.                     conn.close();                
    52.                 } catch (SQLException e) {                                 
    53.                         e.printStackTrace();                
    54.                 }finally {                    
    55.                     conn=null;                
    56.                 }            
    57.             }            
    58.             if(rs!=null) {                
    59.                 try {                    
    60.                     rs.close();             
    61.                 } catch (SQLException e) {                                       
    62.                         e.printStackTrace();                
    63.                 }finally {                    
    64.                     rs=null;                
    65.                 }            
    66.             }            
    67.             if(stat!=null) {                
    68.                 try {                    
    69.                     stat.close();                
    70.                 } catch (SQLException e) {                                       
    71.                         e.printStackTrace();                
    72.                 }finally {                    
    73.                     stat=null;                
    74.                 }            
    75.             }      
    76.     }
    77.  
    78. }

    3）编写测试类

    1. public class DruidTest {
    2.     public static void main(String[] args) {
    3.  
    4.         // 获取连接
    5.         Connection conn = JDBCUtil.getConnection();
    6.  
    7.         String sql = "insert into account(id, name, money) values (?,?,?)";
    8.  
    9.         PreparedStatement statement = null;
    10.         try {
    11.             statement = conn.prepareStatement(sql);
    12.             // 设置参数
    13.             statement.setInt(1, 0);
    14.             statement.setString(2, "测试x");
    15.             statement.setDouble(3, 7777);
    16.  
    17.             // 执行操作
    18.             int num = statement.executeUpdate();
    19.             System.out.println(num);
    20.         } catch (SQLException e) {
    21.             e.printStackTrace();
    22.         } finally {
    23.             try {
    24.                 JDBCUtil.close(statement, conn);
    25.             } catch (SQLException throwables) {
    26.                 throwables.printStackTrace();
    27.             }
    28.         }
    29.     }
    30. }

    最终优化：将配置信息放到配置文件中，读取配置文件传递参数。

    druid.properties文件配置如下

    1. driverClassName=com.mysql.jdbc.Driver
    2. url=jdbc:mysql://localhost:3306/hainiudb
    3. username=root
    4. password=12345678
    5. #初始化连接数10个
    6. initialSize=10
    7. # 连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接
    8. minIdle=5
    9. # 连接池在同一时间能够分配的最大活动连接的数量 20
    10. maxActive=10
    11. #我最多等待6s，6s的时间还没拿到，就放弃本次的索求
    12. maxWait=6000

    修改工具类：

    1. public class JDBCUtil {
    2.     // 私有构造 不让其他人创建对象
    3.     private JDBCUtil() {}
    4.  
    5.     // 初始化druid连接池
    6.     private static DruidDataSource dataSource = null;
    7.  
    8.     // 静态代码块 -- 初始化连接池
    9.     static {
    10.            Properties properties = new Properties();
    11.             //使用ClassLoader加载配置文件，获取字节输入流
    12.             InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties");
    13.             properties.load(is)
    14.  
    15.         // 创建连接池对象
    16.         dataSource = DruidDataSourceFactory.createDataSource(properties);
    17.  
    18.     }
    19.  
    20.     /**
    21.      * 获取数据库连接
    22.      * @return
    23.      * @throws SQLException
    24.      */
    25.     public static Connection getConnection() {
    26.         // 从连接池数据源 获取一条连接
    27.         DruidPooledConnection conn = null;
    28.         try {
    29.             conn = dataSource.getConnection();
    30.         } catch (SQLException e) {
    31.             e.printStackTrace();
    32.         }
    33.         return conn;
    34.     }
    35.  
    36.     /**
    37.      * 关闭结果集、SQL处理对象和连接
    38.      * @param resultSet
    39.      * @param statement
    40.      * @param connection
    41.      * @throws SQLException
    42.      */
    43.     public static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException {
    44.         //--关闭连接            
    45.             if(conn!=null) {                
    46.                 try {                    
    47.                     conn.close();                
    48.                 } catch (SQLException e) {                                 
    49.                         e.printStackTrace();                
    50.                 }finally {                    
    51.                     conn=null;                
    52.                 }            
    53.             }            
    54.             if(rs!=null) {                
    55.                 try {                    
    56.                     rs.close();             
    57.                 } catch (SQLException e) {                                       
    58.                         e.printStackTrace();                
    59.                 }finally {                    
    60.                     rs=null;                
    61.                 }            
    62.             }            
    63.             if(stat!=null) {                
    64.                 try {                    
    65.                     stat.close();                
    66.                 } catch (SQLException e) {                                       
    67.                         e.printStackTrace();                
    68.                 }finally {                    
    69.                     stat=null;                
    70.                 }            
    71.             }      
    72.     }
    73.  
    74. }

    ​​​​

  • 相关阅读:
    鸿蒙OS运行报错 ‘ToDoListItem({ item })‘ does not meet UI component syntax.
    C++ 网络编程 建立简单的TCP通信
    Pandas常用操作命令（七）——表格样式
    解决尚医通com.aliyun.oss 和com.aliyun 爆红
    图文详解MapReduce工作机制
    简述JDK，JRE，JVM的关系
    分享2022流畅运行Solidworks的电脑配置清单
    ps显示msvcp140.dll丢失的解决方法，四个解决方法分享
    使用 PyTorch 的计算机视觉简介 （1/6）
    @Mapper与@MapperScan注解
  • 原文地址：https://blog.csdn.net/2301_76522810/article/details/133844679