MySQL基础知识---第五天

末蓝、 2022-11-25 13:17 262阅读 0赞

# MySQL基础学习—第五天 #

## 1. 事务机制管理 ##

默认情况下,是执行一条SQL语句就保存一次,那么比如我需要  
有三条数据同时成功同时失败,这个时候就需要开启事务机制了  
如果开启事务机制,执行中发生问题,会回滚到没有操作之前,相当于什么也没有发生过

### 1.1 没有事务处理的操作 ###

Connection conn = null;
    
           PreparedStatement prst = null;
    
           Statement stmt = null;
    
           try {
    
               conn = DBUtil.getConnection();
    
               String sql = "insert into test_jdbc (id,name,money) values(?,?,?)";
    
               prst = conn.prepareStatement(sql);
    
               prst.setInt(1, 31);
    
               prst.setString(2, "事务测试1");
    
               prst.setDouble(3, 11.1);
    
               prst.addBatch();
    
               prst.setInt(1, 32);
    
               // 这里故意写成1,让报错
    
               prst.setString(1, "事务测试2");
    
               prst.setDouble(3, 21.1);
    
               prst.addBatch();
    
               prst.setInt(1, 33);
    
               prst.setString(2, "事务测试3");
    
               prst.setDouble(3, 31.1);
    
               prst.addBatch();
    
               prst.executeBatch();
    
           }
    catch (Exception e) {
    
               e.printStackTrace();
    
           }
    finally {
    
               DBUtil.close(stmt);
    
               DBUtil.close(prst);
    
               DBUtil.close(conn);
    
           }

> 插入三条数据,但是第二天添加失败,但是1和3都能添加成功,因为默认是一条SQL就提交一次如何才能让这三条数据同时成功失败呢?

### 1.2 有事务处理的操作 ###

Connection conn = null;
    
           PreparedStatement prst = null;
    
           Statement stmt = null;
    
           try {
    
               conn = DBUtil.getConnection();
    
               // 取消自动提交
    
               conn.setAutoCommit(false);
    
               String sql = "insert into test_jdbc (id,name,money) values(?,?,?)";
    
               prst = conn.prepareStatement(sql);
    
               prst.setInt(1, 31);
    
               prst.setString(2, "事务测试1");
    
               prst.setDouble(3, 11.1);
    
               prst.addBatch();
    
               prst.setInt(1, 32);
    
               // 这里故意写成1,让报错
    
               prst.setString(1, "事务测试2");
    
               prst.setDouble(3, 21.1);
    
               prst.addBatch();
    
               prst.setInt(1, 33);
    
               prst.setString(2, "事务测试3");
    
               prst.setDouble(3, 31.1);
    
               prst.addBatch();
    
               prst.executeBatch();
    
               // 提交
    
               conn.commit();
    
               conn.setAutoCommit(true);
    
           }
    catch (Exception e) {
    
               e.printStackTrace();
    
               try {
    
                  // 事务回滚
    
                  conn.rollback();
    
                  conn.setAutoCommit(true);
    
               }
    catch (SQLException e1) {
    
                  e1.printStackTrace();
    
               }
    
           
    
           }
    finally {
    
               DBUtil.close(stmt);
    
               DBUtil.close(prst);
    
               DBUtil.close(conn);
    
           }

## 2. SQL注入 ##

> 所谓SQL注入，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据  
> 库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。

先模拟应用场景

### 2.1 导入示例数据 ###

DROP TABLE IF EXISTS `t_user`;
    
    CREATE TABLE `t_user` (
    
      `id` int(11) NOT NULL AUTO_INCREMENT,
    
      `username` varchar(255) NOT NULL,
    
      `password` varchar(255) NOT NULL,
    
      PRIMARY KEY (`id`)
    
    )
    ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
     
    
    INSERT INTO
    `t_user` VALUES ('1', 'admin', 'admin');
    
    INSERT INTO
    `t_user` VALUES ('2', 'root', 'root');
    
    INSERT INTO
    `t_user` VALUES ('3', 'zrz', '123');
    
    2.2 创建User实体类

### 2.2 创建User实体类 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70]

### 2.3 创建IUserDao接口 ###

提供一个登录和更新密码的功能  
![在这里插入图片描述][20200809154753870.png]

### 2.4 使用Statement技术实现IUserDao接口 ###

public class UserDao_Statement implements IUserDao {
    
     
    
        @Override
    
        public void login(User user) {
    
           Connection
    connection = null;
    
           Statement
    statement = null;
    
           ResultSet
    rs = null;
    
           try {
    
               connection = DBUtil.getConnection();
    
               statement = connection.createStatement();
    
               String
    sql = "select count(*) from t_user
    where username='"
    
                      +
    user.getUsername() + "' and password = '"
    
                      +
    user.getPassword() + "'";
    
               rs = statement.executeQuery(sql);
    
               rs.next();
    
               if (rs.getInt(1) != 0) {
    
                  System.out.println("登录成功");
    
               }
    else {
    
                  System.out.println("登录失败");
    
               }
    
           }
    catch (Exception e) {
    
               e.printStackTrace();
    
           }
    
        }
    
     
    
        @Override
    
        public void updatePwd(User user, String newPwd) {
    
     
    
           Connection
    connection = null;
    
           Statement
    statement = null;
    
           try {
    
               connection = DBUtil.getConnection();
    
               statement = connection.createStatement();
    
               String
    sql = "update t_user set password
    = '" + newPwd
    
                      +
    "'  where username = '" + user.getUsername() + "'";
    
               int count = statement.executeUpdate(sql);
    
               System.out.println("修改成功,修改了 " + count + " 条数据");
    
           }
    catch (Exception e) {
    
               e.printStackTrace();
    
           }
    
        }
    
    }

### 2.5 测试test ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][2020080915492674.png]  
![在这里插入图片描述][2020080915493814.png]

### 2.6 使用PreparedStatement技术实现IUserDao接口 ###

public class UserDao_PreparedStatement implements IUserDao {
    
     
    
        @Override
    
        public void login(User user) {
    
           Connection
    connection = null;
    
           PreparedStatement
    statement = null;
    
           ResultSet
    rs = null;
    
           try {
    
               connection = DBUtil.getConnection();
    
               String
    sql = "select count(*) from t_user
    where username=? and password = ?";
    
               statement = connection.prepareStatement(sql);
    
               statement.setString(1, user.getUsername());
    
               statement.setString(2, user.getPassword());
    
           
    
               rs = statement.executeQuery();
    
               rs.next();
    
               if (rs.getInt(1) != 0) {
    
                  System.out.println("登录成功");
    
               }
    else {
    
                  System.out.println("登录失败");
    
               }
    
           }
    catch (Exception e) {
    
               e.printStackTrace();
    
           }
    
        }
    
     
    
        @Override
    
        public void updatePwd(User user, String newPwd) {
    
     
    
           Connection
    connection = null;
    
           PreparedStatement
    statement = null;
    
           try {
    
               connection = DBUtil.getConnection();
    
               String
    sql = "update t_user set password
    = ?  where username =?";
    
               statement =connection.prepareStatement(sql);
    
               statement.setString(1, user.getPassword());
    
               statement.setString(2, user.getUsername());
    
           
    
               int count = statement.executeUpdate();
    
               System.out.println("修改成功,修改了 " + count + " 条数据");
    
           }
    catch (Exception e) {
    
               e.printStackTrace();
    
           }
    
        }
    
    }

### 2.7 测试test ###

重新导入示例数据,把示例数据还原

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 3]

![在这里插入图片描述][20200809155137360.png]

演示示例：SQL注入\_PreparedStatement

## 3. Properties优化硬代码 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 4]

如上，我们项目开发

完成测试通过后，通常是要打包发布给别人使用的，如果我们把一些配置信息写死到代码中（这种行为叫硬编码，hardcode），别人就无法修改了

比如别人使用的MySQL服务器对应的IP是10.0.0.1，端口号是9300，用户名是mysqluser、密码是123456。

那么我们只能改代码再重新打包发布，如果有多个用户，他们对应的配置信息都不同，那么我们要针对不同的用户打包发布多次。

以上显然是没必要的，因为我们开发的是程序，只要业务需求/逻辑不变，我们就无需多次打包发布。对此我们的解决方案是，尽量避免硬编码， 将数据与程序分离解耦，将配置信息存储到配置文件中，程序运行时读取配置文件，不同用户只需按自己的实际情况修改配置文件即可。

### 3.1 创建jdbc.properties配置文件 ###

这种方式 账号密码什么的如果发生改动,我只需要更改配置文件就可以

在src目录下创建jdbc.properties文件,内容如下

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/*06*

username=root

password=root

### 3.2 更改DBUtil工具类 ###

public static Connection getConnection() throws ClassNotFoundException,
    
               SQLException,
    IOException {
    
     
    
           Properties
    properties = new Properties();
    
           // getClassLoader : 获取类加载器
    
           // getResourceAsStream : 把资源转换为流
    
           // 这里 定位到src.所以可以直接写文件名,而流是定位到项目
    
         properties.load(DBUtil.class.getClassLoader().getResourceAsStream(
    
                  "jdbc.properties"));
    
           // properties 本质就是一个map,可以通过key获取value
    
           // username=root 读取到之后,以 = 分隔为数组,把username作为key,root作为value保存
    
           // 所以我们就可以通过username 来获取root
    
           String
    driver = properties.getProperty("driver");
    
           String
    username = properties.getProperty("username");
    
           String
    password = properties.getProperty("password");
    
           String
    url = properties.getProperty("url");
    
     
    
           Class.forName(driver);
    
           Connection
    connection = DriverManager.getConnection(url, username,
    
                  password);
    
     
    
           return connection;
    
        }

### 3.3 测试test ###

还是以刚才的SQL注入程序为例,只更改了DBUtil和添加了jdbc.properties

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][20200809155258221.png]

### 3.4 优化工具类 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 6]

以上工具类中,我们没有必要每次获取链接对象都创建一个Properties对象,每次都去加载资源吧?

所以对加载资源这些再做一个优化

#### 3.4.1创建PropertiesUtil ####

对Properties解析对象 进行单例创建

public class PropertiesUtil {
    
        private static Properties jdbcProperties = null;
    
     
    
        private PropertiesUtil() {
    
        }
    
     
    
        public static Properties getProperties() {
    
           try {
    
               if (jdbcProperties == null) {
    
                  jdbcProperties = new Properties();
    
                jdbcProperties.load(PropertiesUtil.class.getClassLoader()
    
                          .getResourceAsStream("jdbc.properties"));
    
               }
    
           }
    catch (Exception e) {
    
               e.printStackTrace();
    
           }
    
           return jdbcProperties;
    
    
        }
    
    }

#### 3.4.2 更改DBUtil工具类 ####

public static Connection getConnection() throws ClassNotFoundException,
    
               SQLException,
    IOException {
    
     
    
           Properties
    properties = PropertiesUtil.getProperties();
    
     
    
           String
    driver = properties.getProperty("driver");
    
           String
    username = properties.getProperty("username");
    
           String
    password = properties.getProperty("password");
    
           String
    url = properties.getProperty("url");
    
     
    
           Class.forName(driver);
    
            Connection connection = DriverManager.getConnection(url, username,
    
                  password);
    
     
    
           return connection;
    
    
        }

#### 3.4.3 测试test ####

以上面的Properties为例,添加PropertiesUtil工具类,更改DBUtil工具类

## 4. 连接池 ##

### 4.1 介绍 ###

> 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；
> 
> 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。
> 
> 这项技术能明显提高对数据库操作的性能

### 4.2 应用场景 ###

上面我们抽取了样板代码，将配置信息与代码分离解耦。JDBC编程的效率从一定程度上提高了，如果我们只是为了学习或者开发一些小的简单的项目，到这个地步其实就足够了。如果我们要想更上一层楼，支持如下场景：

场景一：电商网站如淘宝、京东每年都会有双11这种活动，同一时刻，会有上亿甚至上十亿的用户访问数据库（因为要生成订单等数据），如果我们还使用上面的思路，显然是捉襟见肘。

场景二：某服务器上除了运行MYSQL服务，还有其他一些服务比如WEB服务。我们知道，数据库连接的创建维持不只消耗我们客户端（个人PC）的系统资源（CPU、内存、IO设备），更消耗服务器的系统资源，而假如我们在周末时并不会去访问数据库，这时候服务器上依然还维持着一条空闲的连接，假设占用了2M内存，现在服务器上内存已经都被分配出去了，WEB服务却要求新申请1M内存，很显然，由于内存不足，WEB服务就无法正常运行了。

以上2个场景，我们上面的代码，是无法支持的。

连接池的引入，则主要解决了以上2类问题：

> 1.      能给多用户分配连接或者给一个用户分配多个连接；
> 2.      在适当的时候回收空闲连接以节省系统资源。

JDBC连接池有一个对应的接口javax.sql.DataSource。它也是一个标准一个规范，目前实现了这套规范的连接池产品主要有：

DBCP（MyBatis通常使用这个连接池）、C3P0（Hibernate通常使用这个连接池）、JNDI（Tomcat的默认连接池）。

我们使用DBCP来讲解。

DBCP内部提供了一个“池子”，程序启动的时候，先创建一些连接对象放到这个池子中，备用，当调用连接池的getConnection()方法时，就从池子取出连接对象分配给多个用户/线程。使用完毕调用close()方法时，DBCP重写了close方法，它并不真正关闭连接，而是返还到池子中，由DBCP自动管理池子中的连接对象，在适当的时候真正关闭这些连接。

**优点 :**

1.  资源复用 : 数据库连接得到重用,避免了频繁创建释放链接引起的大量性能开销

在减少系统消耗的基础上,也增进了系统运行环境的平稳性

1.  更快的系统响应速度 :  
    数据库连接池在初始化过程中,往往就已经创建了若干个数据库连接对象放到池中备用

这时,连接的初始化工作已完成,对于业务请求处理而言,直接利用现有的可用连接,避免了数据库连接初始化和释放过程的时间,从而缩减了系统整体的响应时间

1.  统一的连接管理,避免数据库连接遗漏 : 在较为完备数据库连接池中,可以根据预先的连接占用超时设定,强制回收占用连接,从而避免了常规数据库连接操作中可能出现的资源泄露情况

因为连接池组件不是JDK自带的，所以要导入相关的jar包。DBCP相关的jar包有3个，如下：

### 4.3 创建连接池工具类 ###

public class BasicDataSourceUtil {
    
        private BasicDataSource bds;
    
        private static BasicDataSourceUtil bdsu;
    
     
    
        /**
    
         * 把初始化数据库链接的代码,放到了 单例模式的构造方法中
    
         * 
    
         * 因为当前类是单例的,所以构造方法只能被执行一次,那么链接池对象也就成了单例
    
         */
    
        private BasicDataSourceUtil() {
    
           bds = new BasicDataSource();
    
           Properties
    properties = PropertiesUtil.getProperties();
    
         bds.setDriverClassName(properties.getProperty("driver"));
    
           bds.setUrl(properties.getProperty("url"));
    
            bds.setUsername(properties.getProperty("username"));
    
            bds.setPassword(properties.getProperty("password"));
    
        }
    
     
    
        /**
    
         * 主要为了解决高并发,所以需要线程安全的单例模式
    
         * 
    
         * @return
    
         */
    
        public static BasicDataSourceUtil getInstance()
    {
    
           if (bdsu == null) {
    
               synchronized (BasicDataSourceUtil.class) {
    
                  if (bdsu == null) {
    
                      bdsu = new BasicDataSourceUtil();
    
                  }
    
               }
    
           }
    
           return bdsu;
    
        }
    
     
    
        /**
    
         * 提供一个获取链接对象的方法
    
         * 
    
         * @return
    
         */
    
        public BasicDataSource getBasicDataSource() {
    
           return bds;
    
        }
    
    }

### 4.4 测试连接池 ###

public class DBCPTest {
    
        public static void main(String[] args) throws SQLException {
    
           for (int i = 0; i < 10; i++) {
    
               System.out.println(BasicDataSourceUtil.getInstance()
    
                      .getBasicDataSource());
    
           }
    
           // 10次对象一样,已经单例模式
    
           BasicDataSourceUtil
    bdsu = BasicDataSourceUtil.getInstance();
    
           BasicDataSource
    bds = bdsu.getBasicDataSource();
    
           System.out.println(bds.getConnection());
    
     
    
        }
    
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70]: /images/20221124/315b9d39bbac4d9e8a0eca6e90ecef4d.png
[20200809154753870.png]: /images/20221124/3d85d6d9d883407eb5613602e3b29e9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 1]: /images/20221124/a8e68f1788454c1db9d2b3576f62ee2f.png
[2020080915492674.png]: /images/20221124/997ae01c0868438fb070bcf912b7a4f7.png
[2020080915493814.png]: /images/20221124/232ea78662af4d319637c742eb95bfb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 2]: /images/20221124/dd918f6c439641f3a9998e2f8818aa63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 3]: /images/20221124/00734dfc25b74696ba9c8a0c73c69e46.png
[20200809155137360.png]: /images/20221124/2e3962ec70344aa89486abd1bcabaf83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 4]: /images/20221124/320efb9818674b189ce6016e0beee5b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 5]: /images/20221124/2b328085323a40f79db7757d49b9c53d.png
[20200809155258221.png]: /images/20221124/4b74a01f1c9c48b38a22c8a0f5952005.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80ODQ0NDY1MA_size_16_color_FFFFFF_t_70 6]: /images/20221124/42488c0c9676432aaceb0cbae4caf6ab.png