JDBC&数据库连接池

目录

• 1、JDBC简介
• 2、JDBC快速入门
• 3、JDBC API
  • 3.1 DriverManager
  • 3.2 Connection
    • 3.2.1 获取执行对象
    • 3.2.2 事务管理
  • 3.3 Statement
    • 3.3.1 概述
    • 3.3.2 示例
  • 3.4ResultSet
    • 3.4.1 概述
    • 3.4.2 代码实现
  • 3.5 PreparedStatement
    • 3.5.1 SQL注入
    • 3.5.2 代码模拟SQL注入
    • 3.5.3 PreparedStatement概述
    • 3.5.4 使用PreparedStatement改进SQL注入问题
    • 3.5.5 PreparedStatement原理
• 4、数据库连接池
  • 4.1 数据库连接池介绍
  • 4.2 数据库连接池实现
  • 4.3 Driud使用
• 5、练习
  • 5.1需求
  • 5.2案例
    • 5.2.1 环境准备
    • 5.2.2 查询所有
    • 5.2.3 添加数据
    • 5.2.4 修改数据
    • 5.2.5 删除数据
• 6、附录
  • 6.1 driud配置详解

---

1、JDBC简介

JDBC 就是使用Java语言操作关系型数据库的一套API

全称：( Java DataBase Connectivity ) Java 数据库连接

• JDBC是使用同一套Java代码操作不同数据库的一套标准接口
• 接口的实现类由数据库品牌自己实现（驱动 jar 包）

2、JDBC快速入门

• 基本操作

  // 导入对应版本的jar包，Add as library
  
  // 注册驱动，MySQL 5 之后的驱动包可以省略
  Class.forName("com.mysql.jdbc.Driver");
  
  // 获取连接
  String url = "jdbc:mysql://localhost:3306/db1";
  String username = "root";
  String passwd = "123456";
  Connection connection = DriverManager.getConnection(url, username, passwd);
  
  // 定义sql
  String sql = "update account set money = 5000 where id = 1";
  
  // 创建执行sql的对象Statement
  Statement statement = connection.createStatement();
  
  // 执行sql语句，返回受影响的行数
  int count = statement.executeUpdate(sql);
  
  // 处理返回结果
  System.out.println(count);
  
  // 释放资源
  statement.close();
  connection.close();
  

3、JDBC API

3.1 DriverManager

• 作用：驱动管理类

  • 注册驱动
  • 获取数据库连接

• 注册驱动

  • 源码

    package com.mysql.jdbc;
    
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    public class Driver extends NonRegisteringDriver implements java.sql.Driver {
        public Driver() throws SQLException {
        }
    
        static {
            try {
                DriverManager.registerDriver(new Driver());
            } catch (SQLException var1) {
                throw new RuntimeException("Can't register driver!");
            }
        }
    }
    

  • 注册驱动方式

    由上述源码可知，该类中的静态代码块已经实现了 DriverManager 对象的 registerDriver() 方法进行驱动的注册，故在使用中，仅需要加载Driver类即可，故采用反射来注册驱动。

    Class.forName("com.mysql.jdbc.Driver");
    

    MySQL 5 之后的驱动包可以省略注册驱动的步骤，其自动加载jar包中META-INF/services/java.sql.Driver文件中的驱动类

• 获取数据库连接

  • 函数定义

    Connection getConnection(String url, String user, String password);
    

  • 参数说明

    • url：连接路径

      • 语法：jdbc:mysql://ip地址(域名):端口/数据库名称?参数键值对1&参数键值对2...

        示例：jdbc:mysql://localhost:3306/db_name

        • 如果是本机mysql服务器，则可简写为jdbc:mysql:///db_name?参数键值对1&参数键值对2...
        • 配置useSSL=false参数，禁用安全连接方式，解决警告提示，例：jdbc:mysql:///db_name?useSSL=false

    • user：数据库用户名

    • password：数据库密码

3.2 Connection

3.2.1 获取执行对象

// 普通执行SQL对象
Statement createStatement();
// 预编译SQL的执行SQL对象：防止SQL注入	(详见3.6.1)
PreparedStatement prepareStatement(String sql);
// 执行存储过程的对象
CallableStatement prepareCall(String sql);

3.2.2 事务管理

• MySQL事务管理

  #开启事务 两种方式
  begin;
  start transaction;
  #提交事务
  commit;
  #回滚事务
  rollback;
  
  #MySQL默认自动提交事务
  

• JDBC事务管理

  // 开启事务
  setAutoCommit(bollean autoCommit);	// true：动提交事务，false：手动提交事务（开启事务）
  // 提交事务
  commit();
  // 回滚事务
  rollback();
  

• 事务示例

  try {
      // 开启事务
      connection.setAutoCommit(false);
  
      // 执行sql语句，返回受影响的行数
      int count1 = statement.executeUpdate(sql1);
  
      // 制造异常
      int i = 3 / 0;
  
      int count2 = statement.executeUpdate(sql2);
  
      // 数据处理
      System.out.println(count1+"  "+count2);
  
      // 提交事务
      connection.commit();
  
  } catch (Exception throwable) {
      // 回滚事务
      connection.rollback();
      throwable.printStackTrace();
  }
  

3.3 Statement

3.3.1 概述

• Statement对象用于执行SQL语句

  // 通过数据库连接Connection创建Statement对象
  Statement statement = connection.createStatement();
  

• 执行DDL语句、DML语句

  // 执行DDL，返回DDL语句影响的行数（无影响时返回0）
  int executeUpdate(sql);
  

  DDL语句执行成功且有影响时可能返回0，如删除数据库

  实际开发中几乎不使用Java操作DDL语句

• 执行DQL语句

  // 执行DQL，返回ResultSet结果集对象
  ResultSet executeQuery(sql);
  

3.3.2 示例

@Test
public void testDML() throws SQLException, ClassNotFoundException {
    // 注册驱动, 获取连接, 返回数据库连接对象（细节不表）
    Connection connection = myCreateStatement(url, username, passwd);

    // 定义sql
    String sql = "update account set money = 3000";

    // 执行sql对象Statement
    Statement statement = connection.createStatement();

    // 执行DML语句
    try {
        // 执行sql语句，返回受影响的行数
        int count = statement.executeUpdate(sql);
        // 数据处理
        System.out.println(count > 0 ? "修改成功" : "修改失败");
    } catch (SQLException throwable) {
        System.out.println("修改失败");
        throwable.printStackTrace();
    }

    // 执行DDL语句
    try {
        // DDL 执行删除操作，返回结果为0，不可使用返回值判断执行情况
        System.out.println(statement.executeUpdate("drop database db2"));
    } catch (SQLException throwable) {
        System.out.println("修改失败");
        throwable.printStackTrace();
    }

    // 释放资源
    statement.close();
    connection.close();
}

/**
 *DQL Test 详见3.4.2
 */

3.4ResultSet

3.4.1 概述

• 作用：封装了SQL查询语句的结果，执行DQL语句后会返回 ResultSet 对象

• 操作

  // 将指针从当前数据移动到下一条数据，并判断当前行是否为有效行
  boolean next();		// true:有效行，当前行有数据; false:无效行，当前行无数据
  
  // 获取数据，xxx为数据类型
  xxx getxxx(int index);	// index为编号，从1开始
  xxx getxxx(String 字段名);
  // 举例
  int getint("gender");
  String getString(1);
  

3.4.2 代码实现

@Test
public void testDQL() throws SQLException, ClassNotFoundException {
    // 注册驱动, 获取连接, 返回数据库连接对象（细节不表）
    Connection connection = myCreateStatement(url, username, passwd);

    // 定义sql
    String sql = "select * from account";

    // 执行sql对象Statement
    Statement statement = connection.createStatement();

    // 执行DQL语句，处理返回数据
    ResultSet resultSet = statement.executeQuery(sql);
    ArrayList<Account> list = new ArrayList<>();
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        int money = resultSet.getInt("money");
        list.add(new Account(id, name, money));
    }
    System.out.println(list);

    // 释放资源
    statement.close();
    connection.close();
}

3.5 PreparedStatement

作用：

• 预编译SQL语句并执行：预防SQL注入问题

3.5.1 SQL注入

SQL注入是通过操作输入来修改实现定义好的SQL语句，用以执行SQL对服务器进行攻击的方式

• 若后台代码未解决SQL注入问题，用户在前端填写表单时提交内容为包含模糊查询的SQL语句，则数据库查询结果为真，可跳过后台的逻辑判断，直接通过身份认证。如填写1' OR '1'='1

• 参考资料：

3.5.2 代码模拟SQL注入

@Test
public void Login() throws SQLException, ClassNotFoundException {
    // 注册驱动, 获取连接, 返回数据库连接对象（细节不表）
    Connection connection = myCreateStatement(url, username, passwd);

    // 模拟接收到表单数据
    String name = "' OR '1'='1";
    String password = "' OR '1'='1";

    // SQL注入
    String sql = "select * from account where name = '" + 
        name + "' and password = '" + password + "'";

    // 执行sql对象Statement
    Statement statement = connection.createStatement();

    // 执行DQL语句
    ResultSet resultSet = statement.executeQuery(sql);

    ArrayList<Account> list = new ArrayList<>();

    if(resultSet.next()) System.out.println("登录成功");
    else    System.out.println("登录失败");

    // 释放资源
    statement.close();
    connection.close();
}

注入后SQL语句为：

#where 后条件语句恒为真，即查询到全部数据
select * from account where name = '' or '1' = '1' and password = '' or '1' = '1';

3.5.3 PreparedStatement概述

• 获取PreparedStatement对象

  // SQL语句中的参数，使用?占位符进行替代
  String sql = "select * from account where username = ? and password = ?";
  // 通过Connection对象获取，并传入SQL语句
  PreparedStatement preparedStatement = connection.prepareStatement(sql);
  

• 设置参数值

  • 参数值为 ? 占位符位置的值
  • 使用setXxx(参数1,参数2)进行赋值
  • 参数：
    • 参数1：? 的位置编号，从1开始
    • 参数2：? 的值

• 执行SQL

  • 调用这两个方法时不需要传递SQL语句，因为获取SQL语句执行对象时SQL语句已经进行了预编译

  // 执行DDL和DML语句
  preparedStatement.executeUpdate();
  // 执行DQL语句
  preparedStatement.executeQuery();
  

3.5.4 使用PreparedStatement改进SQL注入问题

@Test
public void testPreparedStatement() throws SQLException, ClassNotFoundException {
    // 注册驱动, 获取连接, 返回数据库连接对象（细节不表）
    Connection connection = myCreateStatement(url, username, passwd);

    // 模拟接收到表单数据
    String name = "1' OR '1'='1";
    String password = "1' OR '1'='1";

    // 定义sql
    // String sql = "select * from account where name = '" + name + "' and password = '" + password + "'";
    String sql = "select * from account where name = ? and password = ?";

    // 执行sql对象Statement
    // Statement statement = connection.createStatement();

    // 获取PreparedStatement对象
    PreparedStatement preparedStatement = connection.prepareStatement(sql);

    // 设置参数值
    preparedStatement.setString(1, name);
    preparedStatement.setString(2, password);

    // 执行DQL语句
    // ResultSet resultSet = statement.executeQuery(sql);
    ResultSet resultSet = preparedStatement.executeQuery();

    ArrayList<Account> list = new ArrayList<>();

    if(resultSet.next()) System.out.println("登录成功");
    else    System.out.println("登录失败");

    // 释放资源
    // statement.close();
    preparedStatement.close();
    connection.close();
}

使用PreparedStatement不会出现SQL注入的问题，其对特殊字符进行了转义

#转义后的SQL语句
select * from account where name = '\'or \'1\' = \'1' and password = '\'or \'1\' = \'1';

3.5.5 PreparedStatement原理

• PrepareStatement的优势

  • 预编译SQL，性能更高

    • 在将含有占位符的SQL传入并创建PreparedStatement对象时就已经进行了预编译

    • 该对象仅执行一次预编译。之后将参数传递给通配符时不会重复编译，效率更高

  • 防止SQL注入

• Java代码操作数据库的流程

  • 将SQL语句发送到MySQL服务器端

  • MySQL服务端会对SQL语句执行操作

    1. 检查SQL语句：检查语法

    2. 编译SQL语句：将SQL编译成可执行函数

       检查和编译比执行消耗的时间更长。

       如果知识重新设置参数，则检查SQL语句和编译SQL语句将不需要重复执行，进而提高性能

    3. 执行SQL语句

4、数据库连接池

4.1 数据库连接池介绍

• 数据库连接池
  • 是一个容器，负责分配、管理数据库连接（Connection对象）
  • 允许应用程序重复使用一个现有的数据库连接，而非重新创建
  • 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引发的数据库连接遗漏
• 优势
  • 资源复用
  • 提升系统影响速度
  • 避免数据库连接遗漏（回收闲置的数据库连接，避免无意义的占用）

4.2 数据库连接池实现

• 标准接口：DataSource

  • 官方提供的数据库连接池标准接口，由第三方实现接口

    Connection getConnection();
    

    不再使用DriverManager对象回去数据库连接Connection对象

    使用连接池DataSource获取Connection对象

• 常见的数据库连接池

  • DBCP
  • C3P0
  • Druid

4.3 Driud使用

• 导入jar包

• 配置文件

  driverClassName=com.mysql.jdbc.Driver
  url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true
  username=root
  password=123456
  # 初始化连接数量
  initialSize=5
  # 最大连接数量
  maxActive=10
  # 最大等待时间
  maxWait=3000
  

• Java代码

  public static void main(String[] args) throws Exception {
      // 加载配置文件
      Properties properties = new Properties();
      properties.load(new FileInputStream("jdbc-demo/src/druid.properties"));
      // 获取连接池对象
      DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
      // 获取对应的数据库连接
      Connection connection = dataSource.getConnection();
      System.out.println(connection);
  }
  

5、练习

5.1需求

• 实现对tb_brand的CRUD基本操作

  数据库表结构

  • 名称	类型	长度	小数点	允许空	主键
    id	int	11	0	0	-1
    brand_name	varchar	20	0	-1	0
    company_name	varchar	20	0	-1	0
    ordered	int	11	0	-1	0
    description	varchar	100	0	-1	0
    status	int	11	0	-1	0

5.2案例

5.2.1 环境准备

• SQL

  -- 删除tb_brand表
  drop table if exists tb_brand;
  -- 创建tb_brand表
  create table tb_brand
  (
      -- id 主键
      id           int primary key auto_increment,
      -- 品牌名称
      brand_name   varchar(20),
      -- 企业名称
      company_name varchar(20),
      -- 排序字段
      ordered      int,
      -- 描述信息
      description  varchar(100),
      -- 状态：0：禁用  1：启用
      status       int
  );
  -- 添加数据
  insert into tb_brand (brand_name, company_name, ordered, description, status)
  values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
         ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
         ('小米', '小米科技有限公司', 50, 'are you ok', 1);
  
  
  SELECT * FROM tb_brand;
  

5.2.2 查询所有

@Test
public void selectAll() throws Exception {
    // 导入配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/druid.properties"));
    // 获取连接池对象
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("select * from tb_brand");

    ArrayList<Brand> brand = new ArrayList<>();
    while (resultSet.next()) {
        brand.add(new Brand(
            resultSet.getInt("id"),
            resultSet.getString("brand_name"),
            resultSet.getString("company_name"),
            resultSet.getInt("ordered"),
            resultSet.getString("description"),
            resultSet.getInt("status")));
    }
    System.out.println(brand);
    // 回收资源
    resultSet.close();
    statement.close();
    connection.close();
}

5.2.3 添加数据

@Test
public void insert() throws Exception {
    // 导入配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/druid.properties"));
    // 获取数据库连接池对象
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    // 获取PreparedStatement对象
    PreparedStatement preparedStatement = connection.prepareStatement(
        "insert into tb_brand" +
        "(brand_name, company_name, ordered, description, status) " +
        "values(?, ?, ?, ?, ?);"
    );
    // 设置参数
    preparedStatement.setString(1, "test01");
    preparedStatement.setString(2, "test01-xxx-c");
    preparedStatement.setInt(3, 1);
    preparedStatement.setString(4, "XxxXxx");
    preparedStatement.setInt(5, 1);
    // 执行SQL
    int count = preparedStatement.executeUpdate();
    // 添加是否成功
    System.out.println(count > 0);
    // 释放资源
    preparedStatement.close();
    connection.close();
}

5.2.4 修改数据

@Test
public void updateByID() throws Exception {
    // 导入配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/druid.properties"));
    // 创建数据库连接
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    // 获取PreparedStatement对象
    PreparedStatement preparedStatement = connection.prepareStatement(
        "update tb_brand set " +
        "brand_name = ?," +
        "company_name = ?," +
        "ordered = ?," +
        "description = ?," +
        "status = ? " +
        "where id = ?"
    );
    // 设置参数
    int id = 4;
    preparedStatement.setString(1,"aaa");
    preparedStatement.setString(2,"aaa");
    preparedStatement.setInt(3,1);
    preparedStatement.setString(4,"aaa");
    preparedStatement.setInt(5,0);
    preparedStatement.setInt(6, id);
    // 执行SQL
    int count = preparedStatement.executeUpdate();
    // 处理结果
    System.out.println( count > 0 );
    // 回收资源
    preparedStatement.close();
    connection.close();
}

5.2.5 删除数据

@Test
public void dropByID() throws Exception {
    // 导入配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src/druid.properties"));
    // 创建数据库连接
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    Connection connection = dataSource.getConnection();
    // 获取PreparedStatement对象
    PreparedStatement preparedStatement = connection.prepareStatement(
        "delete from tb_brand where id = ?"
    );
    // 设置参数
    int id = 4;
    preparedStatement.setInt(1, id);
    // 执行SQL
    int count = preparedStatement.executeUpdate();
    // 返回结果
    System.out.println(count > 0);
    // 回收资源
    preparedStatement.close();
    connection.close();
}

6、附录

6.1 driud配置详解

属性	说明	建议值
url	数据库的jdbc连接地址。一般为连接oracle/mysql。	示例如下：mysql : jdbc:mysql://ip:port/dbname?option1&option2&… 或oracle : jdbc:oracle:thin:@ip:port:oracle_sid等
username	登录数据库的用户名
password	登录数据库的用户密码
initialSize	启动程序时，在连接池中初始化多少个连接	10-50已足够
maxActive	连接池中最多支持多少个活动会话
maxWait	程序向连接池中请求连接时,超过maxWait的值后，认为本次请求失败，即连接池没有可用连接，单位毫秒，设置-1时表示无限等待	100
minEvictableIdleTimeMillis	池中某个连接的空闲时长达到 N 毫秒后, 连接池在下次检查空闲连接时，将回收该连接	要小于防火墙超时设置net.netfilter.nf_conntrack_tcp_timeout_established的设置
timeBetweenEvictionRunsMillis	检查空闲连接的频率	单位毫秒, 非正整数时表示不进行检查
keepAlive	程序没有close连接且空闲时长超过 minEvictableIdleTimeMillis,则会执行validationQuery指定的SQL,以保证该程序连接不会池kill掉,其范围不超过minIdle指定的连接个数。	true
minIdle	回收空闲连接时，将保证至少有minIdle个连接.	与initialSize相同
removeAbandoned	要求程序从池中get到连接后, N 秒后必须close,否则druid 会强制回收该连接,不管该连接中是活动还是空闲, 以防止进程不会进行close而霸占连接。	false,当发现程序有未正常close连接时设置为true
removeAbandonedTimeout	设置druid 强制回收连接的时限，当程序从池中get到连接开始算起，超过此值后，druid将强制回收该连接，单位秒。	应大于业务运行最长时间
logAbandoned	当druid强制回收连接后，是否将stack trace 记录到日志中	true
testWhileIdle	当程序请求连接，池在分配连接时，是否先检查该连接是否有效。(高效)	true
validationQuery	检查池中的连接是否仍可用的 SQL 语句,drui会连接到数据库执行该SQL, 如果正常返回，则表示连接可用，否则表示连接不可用
testOnBorrow	程序 申请 连接时,进行连接有效性检查（低效，影响性能）	false
testOnReturn	程序 返还 连接时,进行连接有效性检查（低效，影响性能）	false
poolPreparedStatements	缓存通过以下两个方法发起的SQL: public PreparedStatement prepareStatement(String sql) public PreparedStatement prepareStatement(String sql,int resultSetType, int resultSetConcurrency)	true
maxPoolPrepareStatementPerConnectionSize	每个连接最多缓存多少个SQL	20
filters	这里配置的是插件,常用的插件有:监控统计: filter:stat；日志监控: filter:log4j 或者 slf4j；防御SQL注入: filter:wall	stat,wall,slf4j
connectProperties	连接属性。比如设置一些连接池统计方面的配置。	比如设置一些数据库连接属性:druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000