Mysql 基本使用2 -Jdbc和线程池C3P0

╰+哭是因爲堅強的太久メ 2022-04-23 22:50 33阅读 0赞

### JDBC 的概述 ###

> JDBC(Java DataBase Connectivity, java 数据库连接) 是一种用于执行 SQL 语句的 Java API 可以为多种关系数据库提供统一访问 它由一组用 java 语言编写的类和接口组成 。JDBC 提供了一个种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序

### 核心API的功能 ###

<table> 
 <thead> 
  <tr> 
   <th>类名</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DriverManager 实现类</td> 
   <td>注册驱动，创建连接</td> 
  </tr> 
  <tr> 
   <td>Connection 接口</td> 
   <td>表示数据的连接</td> 
  </tr> 
  <tr> 
   <td>Statement 接口</td> 
   <td>执行Sql 语句的对象</td> 
  </tr> 
  <tr> 
   <td>ResultSet 接口</td> 
   <td>数据库返回的结果集</td> 
  </tr> 
 </tbody> 
</table>

#### Connection 接口的方法 ####

<table> 
 <thead> 
  <tr> 
   <th>方法名</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>createStatement</td> 
   <td>获取Statement 对象</td> 
  </tr> 
 </tbody> 
</table>

#### Statement 接口中的方法 ####

<table> 
 <thead> 
  <tr> 
   <th>方法</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>int executeUpdate((String Sql))</td> 
   <td>更新 insert ， update ，delete， DML 语句</td> 
  </tr> 
  <tr> 
   <td>ResultSet executeQuery(String Sql)</td> 
   <td>查询Select</td> 
  </tr> 
 </tbody> 
</table>

#### ResultSet 接口方法 ####

<table> 
 <thead> 
  <tr> 
   <th>resultSet接口方法</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>boolean next()</td> 
   <td>获取游标判断是否有下一行数据</td> 
  </tr> 
  <tr> 
   <td>getXXX(参数)</td> 
   <td>获取这条记录的某个字段</td> 
  </tr> 
 </tbody> 
</table>

### JDBC 的流程步骤 ###

1.  注册驱动
2.  获取链接
3.  获取执行平台
4.  执行sql 语句
5.  处理对象
6.  释放资源 先开够放 rs -> stmt 、ptmt -> conn

#### 代码实现 ####

// 注册驱动 反射原理
    Class.forName("com.mysql.cj.jdbc.Driver");
    
    // 获取链接
    //mysql 8.0 url 建议这么写 
     String url="jdbc:mysql://localhost:3306/jdbcTest?useUnicode=true&characterEncoding=UTF-8" +               "&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL";
            String user = "root";
            String pass = "userroot";
    Connection connection = DriverManager.getConnection(url, user, pass);
    //获取执行对象
    Statement statement = connection.createStatement();
    // 准备Sql 语句
    String sql = "SELECT * FROM user";
    //执行sql 语句 返回过对象
     ResultSet resultSet = statement.executeQuery(sql);
    // 处理返回的结果集对象 
     while (resultSet.next()) { 
                    System.out.print("uid:" + resultSet.getInt("uid") + ",");
                    System.out.print("username:" + resultSet.getString("username") + ",");
                    System.out.print("password:" + resultSet.getString("password") + ",");
                    System.out.print("name:" + resultSet.getString("name") + "!");
                    System.out.println();
                }
    // 释放资源
    
    if (resultSet != null) { 
                try { 
                    resultSet.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
                resultSet = null;
            }
            if (statement != null) { 
                try { 
                    statement.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
                statement = null;
            }
            if (connection != null) { 
                try { 
                    connection.close();
                } catch (SQLException e) { 
                    e.printStackTrace();
                }
            }
        }

### Sql 注入 ###

#### 什么事Sql 注入 ####

> 造成SQL注入的原因是因为程序没有有效过滤用户的输入，使攻击者成功的向服务器提交恶意的SQL查询代码，程序在接收后错误的将攻击者的输入作为查询语句的一部分执行，导致原始的查询逻辑被改变，额外的执行了攻击者精心构造的恶意代码

#### PreparedSteament 的执行原理 ####

![AWAEvQ.png][]

#### PreparedSteament 使用代码 ####

String sql = "INSERT INTO users VALUES (?, ?, ?);";
    // 会先将SQL语句发送给数据库预编译。PreparedStatement会引用着预编译后的结果。
    PreparedStatement pstmt = conn.prepareStatement(sql);
    
    // 设置参数
    pstmt.setString(1, 1);
    pstmt.setInt(2, "张三");
    pstmt.setString(3, "123456");
    pstmt.executeUpdate();
    
    // 再次设置参数
    pstmt.setString(1, 2);
    pstmt.setInt(2, "李四");
    pstmt.setString(3, "66666");
    pstmt.executeUpdate();

\#\#\#\#PreparedSteament 的好处

1.  `prepareStatement()`会先将SQL语句发送给数据库预编译。`PreparedStatement`会引用着预编译后的结果。可以多次传入不同的参数给`PreparedStatement`对象并执行。减少SQL编译次数，提高效率
2.  安全性高，没有sql 注入隐患
3.  提高可读性

### 链接池 ###

> [数据库][Link 1]连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请，使用，释放。  
> 个人理解：创建数据库连接是一个很耗时的操作，也容易对数据库造成安全隐患。所以，在程序初始化的时候，集中创建多个数据库连接，并把他们集中管理，供程序使用，可以保证较快的数据库读写速度，还更加安全可靠

1.  之前JDBC 访问数据库的步骤
    
    创建数据库 —> 执行sql 语句 —> 关闭链接
2.  获取数据库连接需要消耗比较多的资源，而每次操作都要重新获取新的连接对象，执行一次操作就把连接关闭，而数据库创建连接通常需要消耗相对较多的资源，创建时间也较长。这样数据库连接对象的使用率低。

#### 连接池解决的问题 ####

1.  程序一开始就创建一定数量的链接放入一个容器中，这个容器就是连接池
2.  使用时直接从连接池取出一个已经创建的链接对象
3.  关闭的时候并不是真正关闭连接 而是将链接对象再次放回连接池中
4.  连接的重复使用节省数据库的资源消耗
    
    1.  `javax.sql.DataSource` 表示数据库连接池 是JDK 提供的一个接口

### C3P0 的连接池使用 ###

> C3P0是一个开源的连接池。Hibernate框架，默认推荐使用C3P0作为连接池实现

#### C3P0 常用的配置参数 ####

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>initialPoolSize</td> 
   <td>刚创建连接池的链接数量</td> 
  </tr> 
  <tr> 
   <td>maxPoolSize</td> 
   <td>连接池 最多放多少链接</td> 
  </tr> 
  <tr> 
   <td>checkoutTimeout</td> 
   <td>连接池中没有连接时最长等待时间</td> 
  </tr> 
  <tr> 
   <td>maxIdleTime</td> 
   <td>连接池中的空闲连接多久没有使用就会回收。默认是0，0表示不回收</td> 
  </tr> 
 </tbody> 
</table>

#### C3P0配置文件 ####

#### 配置文件的要求 ####

1.  文件名：c3p0-config.xml
2.  放在源代码即src目录下
3.  配置文件 c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
    <c3p0-config>
        
        <default-config>
            <property name="user">root</property>
            <property name="password">userroot</property>
            <property name="jdbcUrl">jdbc:mysql://localhost:3306/imooc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL</property>
            <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
            <property name="checkoutTimeout">30000</property>
            <property name="idleConnectionTestPeriod">30</property>
            <property name="initialPoolSize">3</property>
            <property name="maxIdleTime">30</property>
            <property name="maxPoolSize">100</property>
            <property name="minPoolSize">2</property>
            <property name="maxStatements">200</property>
        </default-config>
        
        <named-config name="test">
            <property name="user">zhanghanlun</property>
            <property name="password">123456</property>
            <property name="jdbcUrl">jdbc:mysql://localhost:3306/zhanghanlun</property>
            <property name="driverClass">com.mysql.jdbc.Driver</property>
            
            <property name="acquireIncrement">5</property>
            
            <property name="initialPoolSize">20</property>
            
            <property name="maxPoolSize">25</property>
            
            <property name="minPoolSize">5</property>
        </named-config>
    </c3p0-config>

#### C3P0 API 介绍 ####

`com.mchange.v2.c3p0.ComboPooledDataSource` 表示C3P0 的连接对象 常用两种方式

1.  无参构照方法 使用默认配置

public ComboPooledDataSource()
    无参构造使用默认配置（使用xml中default-config标签中对应的参数）

1.  有参构照

public Connection getConnection() throws SQLException
    从连接池中取出一个连接

#### 使用步骤 ####

1.  导入jar 包 `c3p0-0.9.1.2.jar`
2.  编写`c3p0-0.9.1.2.jar` 的配置文件 配置相对参数
3.  将配置文件放在 resources 文件夹下

![AWZM5t.png][]

1.  创建连接池对象`ComboPooledDataSource`，
2.  从连接池中获取连接对象
3.  使用连接对象操作数据库
4.  关闭资源

注：配置文件的名称必须c3p0-config.xml

#### 代码实现 ####

//创建连接池对象
    DataSource dataSource = new ComboPooledDataSource();
    //连接池中获取链接对象
                Connection connection = dataSource.getConnection();
                String sql = "delete * FROM student where  id=?;";
    //执行sql 语句操作数据库
                PreparedStatement psm = connection.prepareStatement(sql);
                psm.setInt(1, 1);
                int i = psm.executeUpdate();
                System.out.println(i);
               *//* ResultSet resultSet = psm.executeQuery();
                while (resultSet.next()) {
                    System.out.print(resultSet.getInt("id"));
                    System.out.print(resultSet.getString("name"));
                }*//*
    
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                connection.close();
                resultSet.close();
                psm.close();
                comboPooledDataSource.close();
    
            }

### JdbcTemplate 的基本使用 ###

> JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。他帮助我们避免一些常见的错误，比如忘了总要关闭连接。他运行核心的JDBC工作流，如Statement的建立和执行，而我们只需要提供SQL语句和提取结果。

#### JdbcTemplate 执行sql 的方法 ####

1.  `execute`：可以执行所有SQL语句，一般用于执行DDL语句。
2.  `update`：用于执行`INSERT`、`UPDATE`、`DELETE`等DML语句。
3.  `queryXxx`：用于DQL数据查询语句。

public JdbcTemplate(DataSource dataSource)
    创建JdbcTemplate对象，方便执行SQL语句

#### JdbcTemplate 使用步骤 ####

1.  准备DataSource连接池
2.  导入spring依赖的jar包
    
     *  `spring-beans-4.1.2.RELEASE.jar`
     *  `spring-core-4.1.2.RELEASE.jar`
     *  `spring-jdbc-4.1.2.RELEASE.jar`
     *  `spring-tx-4.1.2.RELEASE.jar`
3.  创建JdbcTemplate 对象 传入Druid 连接池
4.  调用执行语句`execute`、`update`、`queryXxx`

#### 代码实现CRUD ####

//        准备C3P0 链接池
            ComboPooledDataSource dataSource = new ComboPooledDataSource();
    //        创建jdbctemplate 对象
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    //        插入sql 语句
            String sql = "INSERT INTO teacher VALUES(?,?);";
    //        删除语句
            String delsql = "DELETE  FROM teacher WHERE id=?;";
    //        update 语句
            String upsql = "UPDATE  teacher SET name=? WHERE id=?;";
    //        查询单行多列语句
            String selectsql = "Select * from teacher where  id=?;";
    //        查询多行多列语句
            String selectsql2 = "Select * from teacher;";
    //        查询单行单列
            String selctsql3 = "SELECT COUNT(*) from teacher;";
    //         执行sql 语句
            int jdbc1 = jdbcTemplate.update(sql, 4, "诸葛亮");
            int update = jdbcTemplate.update(delsql, 4);
            int wh = jdbcTemplate.update(upsql, "吴欢", 1);
    //        传入javaBean 对象进去返回整个数据的字段相对于的对象
            List<teacher> list = jdbcTemplate.query(selectsql, new BeanPropertyRowMapper<teacher>(teacher.class), 1);
            List<teacher> selectlist = jdbcTemplate.query(selectsql2, new BeanPropertyRowMapper<teacher>(teacher.class));
    //       queryForObject 的使用  返回单个字段
            String s = jdbcTemplate.queryForObject(selctsql3, String.class);
    //        queryForMap 的使用 返回一行多列字段
            Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(selectsql, 2);
    //            queryForList 的使用 返回多行多列字段
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(selectsql2);
    //
    //        关闭线程池
            dataSource.close();
            System.out.println("插入影响行数"+jdbc1);
            System.out.println("删除影响行数"+update);
            System.out.println("更新影响行数"+wh);
            System.out.println("query查询的单行集合"+list);
            System.out.println("query查询的多行集合"+selectlist);
            System.out.println("queryForObject查询单个字段返回的"+s);
            System.out.println("queryForMap查询单行返回的集合"+stringObjectMap);
            System.out.println("queryForList 查询多行返回集合"+maps);

![AWnIzV.png][]

#### 小结 ####

JDBCTemplate的`query`方法用于执行SQL语句，简化JDBC的代码。同时还可以在SQL语句中使用`？`占位，在`query`方法的`Object... args`可变参数中传入对应的参数。

[AWAEvQ.png]: /images/20220222/c9d2f727ca9c4db4a74d986aae486eab.png
[Link 1]: http://lib.csdn.net/base/mysql
[AWZM5t.png]: /images/20220222/27aeeade225842b696524ce79baca6d2.png
[AWnIzV.png]: /images/20220222/e3408cf7ea884e849a7ef3f9ec720b01.png