JDBC

• 定义

  全称为Java数据库连接（Java DataBase Connectivity）：是使用java语句来操作所有关系型数据库的一套API

• JDBC本质

  • 它是官方定义的一套操作所有关系型数据库的规则（即接口），各个数据库厂商会去实现这套接口并提供数据库驱动的jar包，而我们可以使用这套接口（JDBD）进行编程，真正执行的代码是驱动jar包中的实现类

• JDBC好处

  • 各数据库厂商使用相同的接口，Java代码不需要针对不同数据库分别开发
  • 可随时替换底层数据库，访问数据库的Java代码基本不变（即假设我们使用MYSQL数据库则我们导入MYSQL的驱动jar包即可，同理使用其他关系型数据库时导入其他关系型数据库的驱动jar包即可）

• JDBC步骤

  • 创建工程，导入驱动jar包（由于用的是MySQL，所以导入MySQL驱动jar包，导入步骤详见javese基础day17日志部分）
  • 注册驱动------Class.forName("com.mysql.cj.jdbc.Driver");MySQL5.0之后该步可省略，因为MySQL驱动jar包中有个java.sql.Driver文件，该文件记录了驱动全类名，在你获取数据库连接对象时会自动进行驱动注册。
  • 获取数据库连接对象Connection
  • 定义SQL语句
  • 获取执行SQL语句的对象Statement
  • 执行SQL
  • 处理返回结果
  • 释放资源

  其中创建工程，导入驱动jar包的步骤如下：

  Step1： File—>new—>Project—>Empty Project

  

  Step2： 右键工程名—>Open Module Settings—>Project—>选择所用的JDK版本（最好为JDK1.8），然后将编译版本也设置为JDK1.8的版本

  

  

  Step3： 在该工程下创建模块Module，然后在该模块下创建一个lib文件夹并把MySQL驱动jar包放入—>右键jar包—>Add as Library,将该jar包设置为当前模块下有效—>OK。即可在该工程下的模块jdbc-demo中导入MySQLjar包

  

  

  JDBC创建工程，导入驱动jar包后的过程代码如下：

  import java.sql.*;public class TestOne {public static void main(String[] args) throws SQLException, ClassNotFoundException {// 1.注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.DriverClass.forName("com.mysql.cj.jdbc.Driver");// 2.获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.定义SQLString sql = "update account set money = 500 where id = 1";// 4.获取执行sql的对象Statement stmt = conn.createStatement();// 5.执行sqlint count = stmt.executeUpdate(sql);// 6.处理返回结果---返回的是受影响的行数System.out.println(count);// 7.释放资源stmt.close();conn.close();}
  }
  

JDBCAPI详解

DriverManager—工具类

• 注意：该类中均是静态方法

• 作用

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

• 方法

  静态方法	解释
  static void registerDriver(Driver driver)	注册驱动程序
  static Connection getConnection(String url, String user, String password)	尝试建立与给定数据库URL的连接。（即与建立建立连接）

  • static Connection getConnection(String url, String user, String password)参数详解

    • url：连接路径，代码格式如下

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

      2. 示例：jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai

      3. 注意

         （1）若连接的是本机的MySQL服务器则端口号默认为3306，此时代码格式为：jdbc:mysql:///数据库名称?参数键值对1&参数键值对2...

         （2）配置useSSL=false参数，作用：禁用安全连接方式，解决警告（若用安全连接方式则需要复杂配置并会降低效率）

Connection（有事务管理的方法）

• Connection数据库连接对象的作用

  • 获取执行SQL语句的对象
  • 管理事务

• 方法

  获取执行SQL对象的方法	解释
  public Statement createStatement()	创建一个 Statement对象，用于将SQL语句发送到数据库。 (普通执行SQL对象)
  public PreparedStatement prepareStatement(sql)	创建一个 PreparedStatement对象，用于将参数化SQL语句发送到数据库。 (预编译SQL的执行SQL对象，以此来防止SQL注入)
  public CallableStatement prepareCall(String sql)	创建一个用于调用数据库存储过程的 CallableStatement对象。 (执行存储过程的对象)
  JDBC事务管理的方法	解释
  public void setAutoCommit(boolean autoCommit)	开启事务。true为自动提交事务；false为手动提交事务(若设为手动提交事务即相当于开启事务)
  public void commit()	提交事务
  public void rollback()	回滚事务

  package at.guigu.jdbc;import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;public class TestOne {public static void main(String[] args) throws Exception{// 1.注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2.获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.定义SQL语句String sqlOne = "update account set money = 1000 where id = 1";String sqlTwo = "update account set money = 1000 where id = 2";// 4.获取执行sql的对象Statement stmt = conn.createStatement();//事务管理try {//开启事务conn.setAutoCommit(false);//5.1 执行sql语句int countOne = stmt.executeUpdate(sqlOne);// 6.1 处理返回结果System.out.println(countOne);//5.2 执行sql语句int countTwo = stmt.executeUpdate(sqlTwo);//6.2 处理返回结果System.out.println(countTwo);//若无异常则提交事务conn.commit();} catch (SQLException e) {//若出现异常则回滚事务e.printStackTrace();conn.rollback();}// 7.释放资源stmt.close();conn.close();}
  }
  

Statement

• Statement作用

  执行SQL语句

• 方法

  方法	解释
  public int executeUpdate(sql)	执行DML、DDL语句，返回值：若执行DML语句则返回DML语句影响的行数；若执行DDL语句则执行成功后返回的结果可能为0
  public ResultSet executeQuery(sql)	执行DQL语句并返回ResultSet结果集对象

• public int executeUpdate(sql)

  1. 执行DML语句时，若执行成功则会返回受影响的行数，若执行失败该方法通常会抛出SQLException异常，而不是返回一个特定的值
  2. 若执行DDL语句则执行成功后返回的结果可能为0，详见如下代码

  执行DDL语句成功后返回结果大于0

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;public class TestTwo {public static void main(String[] args) throws Exception{// 1.注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2.获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.定义DDL SQL语句String sqlOne = "CREATE DATABASE IF NOT EXISTS db2";// 4.获取执行sql的对象Statement stmt = conn.createStatement();try {//开启事务conn.setAutoCommit(false);//5.1 执行sql语句int countOne = stmt.executeUpdate(sqlOne);// 6.1 处理返回结果System.out.println(countOne);//若无异常则提交事务conn.commit();} catch (SQLException e) {//若出现异常则回滚事务e.printStackTrace();conn.rollback();}// 7.释放资源stmt.close();conn.close();}
  }
  

  

  执行DDL语句成功后返回结果等于0

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;public class TestTwo {public static void main(String[] args) throws Exception{// 1.注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2.获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.定义SQL语句String sqlOne = "DROP DATABASE IF EXISTS db2";// 4.获取执行sql的对象Statement stmt = conn.createStatement();try {//开启事务conn.setAutoCommit(false);//5.1 执行sql语句int countOne = stmt.executeUpdate(sqlOne);// 6.1 处理返回结果System.out.println(countOne);//若无异常则提交事务conn.commit();} catch (SQLException e) {//若出现异常则回滚事务e.printStackTrace();conn.rollback();}// 7.释放资源stmt.close();conn.close();}
  }
  

  

结果集对象ResultSet

• 作用

  • 封装DQL查询语句的结果

• 通过ResultSet类中的方法获取查询结果（其他本类方法详见API）

  方法	解释
  public boolean next()	将光标向前移动一行并判断当前行是否为有效行。若返回true则为有效行，代表当前行有数据；若返回false则为无效行，代表当前行没有数据
  public xxx getXxx(参数)	获取参数获取数据。

  public xxx getXxx(参数)解释：

  ​ xxx：即数据类型 ； 如：int getInt(参数) 、String getString(参数)

  ​ 参数：

  ​ int：代表列的编号，从1开始

  ​ String：代表列的名称

  ​ 当游标移动到有效行时，此时可用int或String来充当参数以此来获取当前行指定列的数据

• 通过结果集对象获取数据的解释

  执行DQL语句后返回的ResultSet结果集对象会将结果封装为一张表的形式且表中有一个游标（默认指向当前数据行的上一行，如图所示），所以若要查询数据则需要利用next()方法来将游标向下移动一行并判断改行是否为有效行，然后再获取数据。

  

• 使用步骤及代码格式

  //1.游标向下移动一行并判断该行是否有数据
  //循环判断游标是否是最后一行末尾
  while(re.next()){//获取数据：getXxx(参数)rs.getXxx(参数);
  }
  

• 示例1：查询test02数据库中的account表的所有数据

  import java.sql.*;public class TestResultSet {public static void main(String[] args) throws Exception{// 1.注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2.获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.定义SQL DQL语句String sqlOne = "select * from test02.account";// 4.获取执行sql的对象Statement stmt = conn.createStatement();ResultSet rs = null;try {//开启事务conn.setAutoCommit(false);//5 执行sql语句rs = stmt.executeQuery(sqlOne);// 6 处理返回结果--即遍历re中的所有数据//6.1 循环判断游标是否是最后一行末尾while (rs.next()) {//6.2 获取数据：getXxx(参数)int id = rs.getInt(1);//等同于int id = rs.getInt("id");String name = rs.getString("name");//等同于String name = rs.getString(2);int money = rs.getInt(3);//等同于int money = rs.getInt("money");System.out.println("id:" + id + "name:" + name + "money:" + money);}//若无异常则提交事务conn.commit();} catch (SQLException e) {//若出现异常则回滚事务e.printStackTrace();conn.rollback();}// 7.释放资源rs.close();stmt.close();conn.close();}
  }
  

  

• 示例2：查询test02数据库中的account账户表数据并将其封装为Account对象中，最后存储到ArrayList集合中

  

  • Step1：创建一个Account类来存放查询出来的表中的数据（一般你所创建的类名需要与你所查询的表的表名一致，目的是为了清楚知道你查询的哪个表）

    • 对于数据封装的对象一般放在名为pojo的包中（该包一般用来存放实体类）

    • Account类中的属性要求与Account表中的字段名一致

      

  package at.guigu.pojo;public class Account {private int id;private String name;private int money;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getMoney() {return money;}public void setMoney(int money) {this.money = money;}@Overridepublic String toString() {return "Account{" +"id=" + id +", name='" + name + '\'' +", money=" + money +'}';}
  }
  

  • Step2：查询account账户表数据并将其封装到该实体类的对象中去

  • Step3：将Account对象存储到ArrayList集合中

    • 注意：执行二三步的测试类不能pojo包中进行创建

      

  • 第二三步代码如下：

  package at.guigu.jdbc;import at.guigu.pojo.Account;import java.sql.*;
  import java.util.ArrayList;public class JdbcAccount {public static void main(String[] args) throws Exception{// 1.注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2.获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.定义SQL DQL语句String sqlOne = "select * from test02.account";// 4.获取执行sql的对象Statement stmt = conn.createStatement();ResultSet rs = null;ArrayList<Account> list = null;try {//开启事务conn.setAutoCommit(false);//5 执行sql语句rs = stmt.executeQuery(sqlOne);//6 创建ArrayList集合来存储ACccount对象list = new ArrayList<>();//7 处理返回结果--即遍历re中的所有数据//7.1 循环判断游标是否是最后一行末尾while (rs.next()) {Account account = new Account();//7.2 获取数据：getXxx(参数)int id = rs.getInt(1);//等同于int id = rs.getInt("id");String name = rs.getString("name");//等同于String name = rs.getString(2);int money = rs.getInt(3);//等同于int money = rs.getInt("money");//给对象赋值account.setId(id);account.setName(name);account.setMoney(money);list.add(account);}//若无异常则提交事务conn.commit();} catch (SQLException e) {//若出现异常则回滚事务e.printStackTrace();conn.rollback();}//查看是否将数据存入到集合中System.out.println(list);//8 释放资源rs.close();stmt.close();conn.close();}
  }
  

  

  注意：

  ​ 页面上进行数据查询并显示出的查询数据（如下图为一个查询数据的页面）就是通过以上步骤，先查询数据然后将其封装为对应对象并将封装数据的对象存储到集合中，最后再通过某种技术来展示到页面上（后续会学习）

  

PreparedStatement接口

• 注意

  • 继承自Statement类
  • 可通过Connection类中的public PreparedStatement prepareStatement(sql)方法来获取此接口的对象

• 作用

  • 预防SQL注入问题（SQL注入是通过操作输入来修改事先定义好的SQL语句，以此来达到执行代码对服务器进行攻击的方法）：将敏感字符进行转义

  SQL注入白话解释：

  ​ 假设一个网站需要用户名及密码才能登录，黑客攻击时随意输入完一个用户名后，此时若在密码输入框输入一行特殊的SQL语句就登录了，则登录后就会对其中的信息进行更改等危险操作

  • 预编译SQL语句并执行，性能更高
    • 预编译功能开启：useServerPreStmts=true

• 用到的该接口中的方法

  方法	解释
  public void setXxx(参数1,参数2)	通过参数进行赋值。 Xxx：数据类型；如：setInt(参数1,参数2)。参数解释详见本次学习内容中的解决SQL注入部分
  public int executeUpdate()	执行DML、DDL语句，返回值：若执行DML语句则返回DML语句影响的行数；若执行DDL语句则执行成功后返回的结果可能为0
  public ResultSet executeQuery()	执行DQL语句并返回ResultSet结果集对象

SQL注入演示

• SQL创建tb_user表

  DROP TABLE IF EXISTS tb_user;
  CREATE TABLE IF NOT EXISTS tb_user(id int,username VARCHAR(20),password VARCHAR(32)
  );
  INSERT INTO tb_user VALUES(1, 'zhangsan', '123'), (2, 'lisi', '123');
  SELECT * FROM tb_user;
  

• java用户登录

  package at.guigu.jdbc;import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  import java.util.Scanner;public class TestSQL {public static void main(String[] args) throws Exception{Scanner input = new Scanner(System.in);// 1 注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2 获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3 接收用户输入的用户名及密码System.out.println("请输入用户名：");String name = input.next();System.out.println("请输入密码");String pwd = input.next();// 4 定义SQL语句String sql = "SELECT * FROM test02.tb_user where username = '"+name+"' and password = '"+pwd+"'";//获取stmt对象Statement stmt = conn.createStatement();//执行SQL语句ResultSet rs = stmt.executeQuery(sql);//判断是否登录成功if (rs.next()) {System.out.println("登陆成功");} else System.out.println("登陆失败");//释放资源rs.close();stmt.close();conn.close();}
  }
  

  

• SQL注入代码示例—随便写的一个用户名，而密码在此处用的是特殊的SQL语句

  package at.guigu.jdbc;import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  import java.util.Scanner;public class TestSQL {public static void main(String[] args) throws Exception{Scanner input = new Scanner(System.in);// 1 注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2 获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3 接收用户输入的用户名及密码String name = "123nihao@";String pwd = "' or '1'='1";// 4 定义SQL语句String sql = "SELECT * FROM test02.tb_user where username = '"+name+"' and password = '"+pwd+"'";//获取stmt对象Statement stmt = conn.createStatement();//执行SQL语句ResultSet rs = stmt.executeQuery(sql);//判断是否登录成功if (rs.next()) {System.out.println("登陆成功");} else System.out.println("登陆失败");//释放资源rs.close();stmt.close();conn.close();}
  }
  

  

• SQL注入发生原因

  java定义SQL语句时若要与String类型的变量进行连接则格式为'" +变量名+"'，此时你在java中所写的SQL语句在SQL中会保留与String类型的变量进行连接时的单引号（即'" +变量名+"'中的''）

  java定义的SQL语句如下：

  // 3 接收用户输入的用户名及密码String name = "123nihao@";String pwd = "' or '1'='1";// 4 定义SQL语句String sql = "SELECT * FROM test02.tb_user where username = '"+name+"' and password = '"+pwd+"'";
  

  其中密码' or '1'='1中的第一个单引号与'" +变量名+"'中的第一个单引号配套形成一个完整的单引号，密码' or '1'='1中的最后一个单引号与'" +变量名+"'中的最后一个单引号配套形成一个完整的单引号。则所输入的用户名和密码最终形成的SQL语句如下：

  SELECT * FROM test02.tb_user where username = '123nihao@' and password = '' or '1'='1'
  

  此时WHERE后加的条件为username = '123nihao@' and password = '' or '1'='1'，其中会先执行username = '123nihao@' and password = '' 的结果然后用其结果与or '1'='1'来判断得出最后结果：username = '123nihao@' and password = ''结果为false，因为没有用户名为123nihao@的用户且密码不可能为空；而or '1'='1'结果为true，因为字符串1本来就等于其本身，所以最后where后的条件为真，此时系统则会允许查找出test02库中的tb_user表中的数据，如下图所示，在Navicat中可无障碍执行

  

  产生如上结果的原因是：在构建动态 SQL 查询时，未对特殊字符进行转义，从而使这些字符被误解为 SQL 语句的一部分

解决SQL注入

• 原理

  在构建动态 SQL 查询时，应该对特殊字符进行转义，以防止这些字符被误解为 SQL 语句的一部分。这样做可以确保用户输入的数据仅被解释为数据，而不是 SQL 代码。

• 利用PreparedStatement接口进行处理，步骤如下：

  • 获取PreparedStatement对象

    // SQL语句中的参数值用？占位符替代
    String sql = "SELECT * FROM 表名 WHERE username = ? AND password = ?";
    //通过Connection获取对象并传入对应的sql语句
    PreparedStatement pstmt = conn.prepareStatement(sql);
    

  • 设置参数值—利用方法public void setXxx(参数1,参数2)

    • 参数1代表？的位置编号，从1开始
    • 参数2代表？的值

  • 执行SQL

    • 利用方法preparedStatement接口中的方法executeUpdate()或executeQuery()来执行SQL语句

• 代码示例

  package at.guigu.jdbc;import java.sql.*;
  import java.util.Scanner;public class TestSQL {public static void main(String[] args) throws Exception{Scanner input = new Scanner(System.in);// 1 注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2 获取连接对象String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3 接收用户输入的用户名及密码String name = "123nihao@";String pwd = "' or '1'='1";// 4 定义SQL语句String sql = "SELECT * FROM test02.tb_user where username = ? and password = ?";//通过Connection获取对象并传入对应的sql语句PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setString(1, name);pstmt.setString(2, pwd);//执行SQL语句ResultSet rs = pstmt.executeQuery();if (rs.next()) {System.out.println("登录成功");} else System.out.println("登陆失败");//释放资源rs.close();pstmt.close();conn.close();}
  }
  

  

开启预编译SQL语句

预编译是在你将SQL语句传入到PreparedStatement对象时就开启了预编译，并不是在执行SQL语句时开始预编译

• 预编译功能开启代码：useServerPreStmts=true

  • 需要将开启代码加入到DriverManager类中的static Connection getConnection(String url, String user, String password)方法中的参数url中作为参数键值对，详见代码示例

• 代码示例

  package at.guigu.jdbc;import java.sql.*;
  import java.util.Scanner;public class TestSQL {public static void main(String[] args) throws Exception{Scanner input = new Scanner(System.in);// 1 注册驱动---该方法中的参数是驱动全类名，在驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内，即为com.mysql.cj.jdbc.Driver
  //        Class.forName("com.mysql.cj.jdbc.Driver");// 2 获取连接对象并开启预编译String url = "jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useServerPreStmts=true;";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3 接收用户输入的用户名及密码String name = "123nihao@";String pwd = "' or '1'='1";// 4 定义SQL语句String sql = "SELECT * FROM test02.tb_user where username = ? and password = ?";//通过Connection获取对象并传入对应的sql语句PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setString(1, name);pstmt.setString(2, pwd);//执行SQL语句ResultSet rs = pstmt.executeQuery();if (rs.next()) {System.out.println("登录成功");} else System.out.println("登陆失败");//释放资源rs.close();pstmt.close();conn.close();}
  }
  

Statement与PreparedStatement原理解析

• Statement原理

  Java代码首先会将SQL语句发送给MySQL，然后MySQL检查SQL语法，若没问题则编译SQL（将其编译成可执行的函数），编译完成后开始执行SQL，最后返回执行结果

  • 缺点
    • 在Statement类的操作原理的整套流程中检查SQL语法以及编译SQL需要耗费大量时间，因为你每改变一次SQL语句则会进行一次SQL语法的检查以及SQL的编译
    • 比如上图中Statement使用的两个SQL语句中username不同，此时若执行这两条SQL语句的话则需要重复两次SQL语法的检查以及SQL的执行

• PreparedStatement原理

  • Java代码首先会在获取PreparedStatement对象时将SQL语句发送给MySQL服务器，然后MySQL服务器检查SQL语法，若没问题则编译SQL（将其编译成可执行的函数），编译完成后将?占位符替换为具体的SQL语句开始执行SQL，最后返回执行结果
    • 优点
      • 由于使用?占位符来代替你要更改的SQL语句来查找数据，所以不需要频繁的对Java中的SQL语句进行更改，这也就代表SQL语法的检查以及SQL编译都只需进行一次，之后只需将占位符替换为你要的SQL语句即可执行SQL（原因是：SQL模板一样，不一样的部分用?占位符代替，所以只需要进行一次检查和编译）
      • 比如上图中PreparedStatement使用的SQL语句中，用?占位符来替代不同的username，此时系统会只进行一次SQLSQL语法的检查以及SQL编译，在编译完成之后在将username的值传入进行SQL的执行
      • 若要使性能更高则可开启预编译功能，详见本节开启预编译SQL语句

数据库连接池

• 简介
  • 它是一个容器（即集合），负责分配、管理数据库连接。数据库连接池中的为提前申请的数据库连接
  • 它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个，当使用完毕之后则会将其归还给数据库连接池，供其他的应用程序重复使用
  • 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏
    • 解释：假设数据库连接池中的数据库连接目前已经被用户全部占用，此时若有一个新用户要使用数据库连接，则该新用户就属于被遗漏了。当新用户被遗漏时，数据库连接池会去判断其他用户是否在占用资源时未进行任何操作，若存在此情况，则会强制将该用户的数据库连接给断开并归还到数据库连接池中，此时被遗漏的新用户即可使用该数据库连接
• 作用
  • 资源重用
  • 提升系统响应速度
  • 避免数据库连接遗漏

数据库连接池的实现

• 标准接口：DataSource------所有的连接池技术都要实现该接口
  • 此为官方（SUN）提供的数据库连接池标准接口，需要第三方组织来实现该接口
  • 功能：获取连接
  • 该接口中获取连接的方法------public Connection getConnection()
• 常见的数据库连接池------都实现了DataSource接口，且都需利用public Connection getConnection()方法来获取连接
  • DBCP
  • C3P0
  • Driud（德鲁伊）
    • 该连接池是阿里巴巴开源的数据库连接池项目
    • 功能强大，性能优秀，是Java语言最好的数据库连接池之一

Driud（德鲁伊）数据库连接池

• 使用步骤

  • 导入jar包druid-1.1.12.jar

  • 定义配置文件------druid.properties

  • 加载配置文件

  • 获取数据库连接池对象

  • 获取数据库连接

  import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
  import java.io.FileInputStream;
  import java.sql.Connection;
  import java.util.Properties;public class DruidOne {public static void main(String[] args) throws Exception {//加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("Jdbc-demo/src/druid.properties"));//获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//获取数据库连接Connection conn = dataSource.getConnection();System.out.println(conn);}
  }
  

  

• Druid配置文件详解

  #driverClassName代表数据库驱动，后跟驱动全类名（在MySQL驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内）
  driverClassName=com.mysql.cj.jdbc.Driver
  # 数据库连接URL
  url=jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
  # 数据库用户名
  username=root
  # 数据库密码
  password=123456
  # 初始化连接数量---即容器中初始的数据库连接数量
  initialSize=5
  # 最大活跃连接数量---容器中初始为5个，但若5个用完了，此时可以在申请5个数据库连接数量
  #也就是说容器中最多存放10个数据库连接
  maxActive=10
  # 获取连接时的最大等待时间，单位：毫秒。---与数据库进行连接时若超过3s仍未连接成功，则会报错
  maxWait=3000
  #最小空闲连接数量---minIdle=5
  # 配置检测连接是否有效的SQL，可以是一个查询语句，如果不指定则默认为"SELECT 1"---validationQuery=SELECT 1
  # 是否开启自动提交事务---defaultAutoCommit=true
  

  属性	解释	备注
  url	数据库的JDBC连接地址，一般为连接Oracle/MySQL。格式为：jdbc:mysql://ip:port/dbname?option1&option2...	如：url=jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
  username	登录数据库的用户名
  password	登录数据库的用户密码
  initialSize	启动程序时，在连接池中初始化多少个连接	10-50已足够
  maxActive	连接池中最多支持多少个活动会话
  maxWait	程序向连接池中申请连接超时，超过maxWait的值后，认为本次请求失败，即连接池没有可用连接。单位：毫秒	建议值为：100。设置为-1时表示无限等待
  minEvictableIdleTimeMillis	池中某个连接的空闲时长达到N毫秒后，连接池在下次检查空闲连接时会将其断开连接（即回收该连接），单位：毫秒	该时间设置要小于防火墙超时设置net.netfilter.nf_conntrack_tcp_timeout_established的设置值
  timeBetweenEvictionRunsMillis	检查空闲连接的频率，若为非正整数时表示不进行检查。单位：毫秒
  keepAlive	程序没有close连接且空闲时长超过minEvictableIdleTimeMillis则会执行validationQuery指定的SQL，以保证该程序连接不会被杀掉，其范围不会超过minldle指定的连接个数	建议值为true
  minldle	回收空闲连接时将保证至少有minldle个连接	与initialSize相同
  removeAbandoned	要求程序从池中get到连接后，N秒必须close，否则druid会强制回收该连接，不管该连接中是活动还是空闲，以防进程不会进行close而霸占连接	正常close连接时设置为true
  removeAbandonedTimeout	设置druid强制回收连接的时限，当程序从池中get到连接开始算起，超过此值后，druid将强制回收该连接，单位：秒	应大于业务运行最长时间
  logAbandoned	当druid强制回收连接后，是否将stack trace记录到日志中	默认为false，建议为true
  testWhileIdle	当程序请求连接，池在分配连接时，是否先检查该连接是否有效（该检查比较高效）	默认为false，建议为true
  validationQuery	检查从连接池获取的数据库连接是否仍然有效的SQL查询语句。Druid会连接到数据库执行该SQL，若正常返回则表示连接可用；反之则表示不可用
  testOnBorrow	程序申请连接时进行连接的有效性检查（该检查比较低效，影响性能）	默认为false
  testOnReturn	程序返还连接时进行连接有效性检查（该检查比较低效，影响性能）	默认为false
  poolPreparedStatements	缓存通过以下两个方法发起的SQL语句：public PreparedStatement prepareStatement(String sql)和public PreparedStatement prepareStatement(String sql,int resultSetType, int resultSetConcurrency)	默认为false，建议为true
  maxPoolPrepareStatementPerConnectionSize	每个数据库连接最多缓存多少个SQL语句	建议为20
  filters	这里配置的是插件,常用的插件有如下几种：监控统计: filter:stat、日志监控: filter:log4j 或者 slf4j、防御SQL注入: filter:wall	建议为stat``,wall,slf4j
  connectProperties	指定连接属性。它是一个键值对的形式，可以设置一些数据库连接的额外属性。

练习------完成商品品牌数据的增删改查操作

• 需要完成的操作如下：

  • 查询：查询所有数据
  • 添加：添加品牌
  • 修改：根据id修改
  • 删除：根据id删除

• 完成该例题的准备工作如下：

  • 准备环境
    • 建立商品的数据库表，表名为tb_brand
    • 设置数据库连接池
    • 写出实体类Brand
  • 写出测试用例

环境准备

• 创建商品数据库表

  DROP TABLE IF EXISTS tb_brand;-- 创建品牌表brand
  CREATE TABLE IF NOT EXISTS 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;
  

  注意：一些不需要顾客添加数据的字段需要设置为自增，比如id

• 设置数据库连接池

  #driverClassName代表数据库驱动，后跟驱动全类名（在MySQL驱动jar包下的META-INF下的services文件夹下的java.sql.Driver文件内）
  driverClassName=com.mysql.cj.jdbc.Driver
  # 数据库连接URL
  url=jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
  # 数据库用户名
  username=root
  # 数据库密码
  password=123456
  # 初始化连接数量---即容器中初始的数据库连接数量
  initialSize=5
  # 最大活跃连接数量---容器中初始为5个，但若5个用完了，此时可以在申请5个数据库连接数量
  #也就是说容器中最多存放10个数据库连接
  maxActive=10
  # 获取连接时的最大等待时间，单位：毫秒。---与数据库进行连接时若超过3s仍未连接成功，则会报错
  maxWait=3000
  #最小空闲连接数量---minIdle=5
  # 配置检测连接是否有效的SQL，可以是一个查询语句，如果不指定则默认为"SELECT 1"---validationQuery=SELECT 1
  # 是否开启自动提交事务---defaultAutoCommit=true
  

• 写出实体类Brand

  • 注意事项
    • 由于需要将数据库表brand中的字段写到Brand实体类中，一次写一次比较麻烦，所以可以直接选中数据库中的SQL创建表的SQL语句中的字段将其复制到idea中，然后用快捷键Ctrl+Shift+Alt：idea同时操作多行来进行属性的定义，这样更快更便捷
    • 数据库中的基本数据类型在实体类中建议使用包装类（原因两点：第一，包装类的默认值均为null；第二，以字段status为例，数据库中定义为int类型，0代表禁用，若在java中直接定义为int类型，由于int默认值为0，则会自动将其status设置为0，所以需要用其包装类）

  package at.guigu.example;public class Brand {// id 主键private Integer id;// 品牌名称private String brandName;// 企业名称private String companyName;// 排序字段 用于将某个品牌显示在最前面让消费者看到private Integer ordered;// 描述信息private String description;// 状态：0：禁用  1：启用private Integer status;public Brand(Integer id, String brandName, String companyName, Integer ordered, String description, Integer status) {this.id = id;this.brandName = brandName;this.companyName = companyName;this.ordered = ordered;this.description = description;this.status = status;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getBrandName() {return brandName;}public void setBrandName(String brandName) {this.brandName = brandName;}public String getCompanyName() {return companyName;}public void setCompanyName(String companyName) {this.companyName = companyName;}public Integer getOrdered() {return ordered;}public void setOrdered(Integer ordered) {this.ordered = ordered;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}public Integer getStatus() {return status;}public void setStatus(Integer status) {this.status = status;}@Overridepublic String toString() {return "Brand{" +"id=" + id +", brandName='" + brandName + '\'' +", companyName='" + companyName + '\'' +", ordered=" + ordered +", description='" + description + '\'' +", status=" + status +'}';}
  }
  

写出测试用例

例题1：查询数据库表tb_brand的所有数据

• 查询所有数据步骤

  • 获取Connection连接对象

    • 加载配置文件
    • 获取连接池对象
    • 获取数据库Connection连接

  • 定义SQL：select * from tb_brand

  • 获取PreparedStatement对象

  • 设置参数：不需要

  • 执行SQL

    • 由于查询数据属于DQL SQL语句，所以需要利用PreparedStatement接口中的executeQuery()方法来执行SQL语句，并返回Result结果集对象

  • 处理结果：List<Brand>

    • 当查询所有数据时由于结果很多，所以可将一个个的结果封装到集合中去，在经过特殊方法展现到页面上，步骤如下：

      Step1：获取数据

      Step2：将数据封装为Brand对象

      Step3：装载集合

  • 释放资源

• 查询所有数据代码

  import com.alibaba.druid.pool.DruidDataSourceFactory;
  import org.junit.Test;
  import javax.sql.DataSource;
  import java.io.FileInputStream;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Properties;public class TestOne {/*** 查询所有数据*/@Testpublic void methodOne() throws Exception{//1 获取Connection连接对象//1.1 加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("F:/node/idea/Jdbc/Jdbc-demo/src/druid.properties"));//1.2 获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//1.3 获取数据库Connection连接对象Connection conn = dataSource.getConnection();//2 定义SQL语句String sql = "SELECT * FROM tb_brand";//3 通过Connection连接对象获取PreparedStatement对象并传入即将要执行的SQL语句PreparedStatement pstmt = conn.prepareStatement(sql);/*4 设置参数---此处不需要pstmt.setString(int, String);*///5 执行传入PreparedStatement对象的SQL语句ResultSet rs = pstmt.executeQuery();//6 处理结果List<Brand> list = new ArrayList<>();while (rs.next()) {//6.1 获取数据---获取数据时参数要与数据库表中的字段名一致int id = rs.getInt("id");String brandName = rs.getString("brand_Name");String companyName = rs.getString("company_Name");int ordered = rs.getInt("ordered");String description = rs.getString("description");int status = rs.getInt("status");//6.2 将数据封装为Brand对象Brand brand = new Brand(id, brandName, companyName, ordered, description, status);//6.3 装载集合list.add(brand);}//打印集合for (Brand brand : list) {System.out.println(brand);}//7 释放资源rs.close();pstmt.close();conn.close();}
  }
  

  

例题2：向数据库表tb_brand中添加数据

• 添加数据步骤

  • 接受页面提交的参数
  • 获取Connection连接对象
    • 加载配置文件
    • 获取连接池对象
    • 获取数据库Connection连接
  • 定义SQL：INSERT INTO tb_brand(brand_name, company_name, ordered, description, status) vALUES (?, ?, ?, ?, ?);
  • 获取PreparedStatement对象
  • 设置参数—设置除了id之外的所有字段
  • 执行SQL
    • 由于添加数据属于DML SQL语句，所以需要利用PreparedStatement接口中的executeUpdate()方法来执行SQL语句，并返回执行结果影响的行数
  • 处理结果—返回布尔类型
    • 若影响的行数大于0则代表执行成功；反之，执行失败
  • 释放资源
    • 注意：增删改没有ResultSet对象，所以释放资源时不用释放该对象

• 添加数据代码

  import com.alibaba.druid.pool.DruidDataSourceFactory;
  import org.junit.Test;
  import javax.sql.DataSource;
  import java.io.FileInputStream;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Properties;public class TestOne {/*** 添加数据*/@Testpublic void methodTwo() throws Exception{//1 接收页面提交的参数String brandName = "香飘飘";String companyName = "香飘飘";int ordered = 1;String description = "绕地球一圈";int status = 1;//2 获取Connection连接对象//2.1 加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("F:/node/idea/Jdbc/Jdbc-demo/src/druid.properties"));//2.2 获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//2.3 获取数据库Connection连接对象Connection conn = dataSource.getConnection();//3 定义SQL语句String sql = "INSERT INTO tb_brand(brand_name, company_name, ordered, description, status) vALUES (?, ?, ?, ?, ?);";//4 通过Connection连接对象获取PreparedStatement对象并传入即将要执行的SQL语句PreparedStatement pstmt = conn.prepareStatement(sql);//5 设置参数---设置除了id之外的所有字段pstmt.setString(1, brandName);pstmt.setString(2, companyName);pstmt.setInt(3, ordered);pstmt.setString(4, description);pstmt.setInt(5, status);//6 执行传入PreparedStatement对象的SQL语句int count = pstmt.executeUpdate();// count代表影响的行数//7 处理结果---若为true则执行成功；反之则执行失败System.out.println(count > 0);//8 释放资源pstmt.close();conn.close();}
  }
  

  

例题3：修改数据库表tb_brand的数据

注意：修改数据时，虽然未对id进行操作，但是是根据id修改数据库表tb_brand中的数据的，所以在你对数据进行修改时，系统会自动返回一个对应的id，该id对应了数据库表中修改数据所在行的id

• 修改数据步骤

  • 接受页面提交的参数
  • 获取Connection连接对象
    • 加载配置文件
    • 获取连接池对象
    • 获取数据库Connection连接
  • 定义SQL：UPDATE tb_brand SET brand_name = ?, company_name = ?, ordered = ?, description = ?, status = ? WHERE id = ?;
  • 获取PreparedStatement对象
  • 设置参数—需要数据库表tb_brand的所有字段
  • 执行SQL
    • 由于添加数据属于DML SQL语句，所以需要利用PreparedStatement接口中的executeUpdate()方法来执行SQL语句，并返回执行结果影响的行数
  • 处理结果—返回布尔类型
    • 若影响的行数大于0则代表执行成功；反之，执行失败
  • 释放资源
    • 注意：增删改没有ResultSet对象，所以释放资源时不用释放该对象

• 修改数据代码

  import com.alibaba.druid.pool.DruidDataSourceFactory;
  import org.junit.Test;
  import javax.sql.DataSource;
  import java.io.FileInputStream;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Properties;public class TestOne {/*** 修改数据*/@Testpublic void methodThree() throws Exception{//1 接收页面提交的参数int id = 4;String brandName = "香飘飘";String companyName = "飘飘";int ordered = 15;String description = "真香真奶茶";int status = 1;//2 获取Connection连接对象//2.1 加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("F:/node/idea/Jdbc/Jdbc-demo/src/druid.properties"));//2.2 获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//2.3 获取数据库Connection连接对象Connection conn = dataSource.getConnection();//3 定义SQL语句String sql = "UPDATE tb_brand SET brand_name = ?, company_name = ?, ordered = ?, description = ?, status = ? WHERE id = ?;";//4 通过Connection连接对象获取PreparedStatement对象并传入即将要执行的SQL语句PreparedStatement pstmt = conn.prepareStatement(sql);//5 设置参数---设置除了id之外的所有字段pstmt.setString(1, brandName);pstmt.setString(2, companyName);pstmt.setInt(3, ordered);pstmt.setString(4, description);pstmt.setInt(5, status);pstmt.setInt(6, id);//6 执行传入PreparedStatement对象的SQL语句int count = pstmt.executeUpdate();// count代表影响的行数//7 处理结果---若为true则执行成功；反之则执行失败System.out.println(count > 0);//8 释放资源pstmt.close();conn.close();}
  }
  

  

例题4：删除数据库表tb_brand中的数据

注意：删除数据时，虽然未对id进行操作，但是在数据库表中是根据id进行数据的删除，所以在删除数据时系统会返回你所删除的数据所在的id

• 删除数据步骤

  • 接受页面提交的参数
  • 获取Connection连接对象
    • 加载配置文件
    • 获取连接池对象
    • 获取数据库Connection连接
  • 定义SQL：DELETE FROM tb_brand WHERE id = ?;
  • 获取PreparedStatement对象
  • 设置参数—需要数据库表tb_brand的所有字段
  • 执行SQL
    • 由于添加数据属于DML SQL语句，所以需要利用PreparedStatement接口中的executeUpdate()方法来执行SQL语句，并返回执行结果影响的行数
  • 处理结果—返回布尔类型
    • 若影响的行数大于0则代表执行成功；反之，执行失败
  • 释放资源
    • 注意：增删改没有ResultSet对象，所以释放资源时不用释放该对象

• 删除数据代码

  import com.alibaba.druid.pool.DruidDataSourceFactory;
  import org.junit.Test;
  import javax.sql.DataSource;
  import java.io.FileInputStream;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Properties;public class TestOne {/*** 删除表中数据*/@Testpublic void methodFour() throws Exception{//1 接收页面提交的参数int id = 4;//2 获取Connection连接对象//2.1 加载配置文件Properties prop = new Properties();prop.load(new FileInputStream("F:/node/idea/Jdbc/Jdbc-demo/src/druid.properties"));//2.2 获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//2.3 获取数据库Connection连接对象Connection conn = dataSource.getConnection();//3 定义SQL语句String sql = "DELETE FROM tb_brand WHERE id = ?;";//4 通过Connection连接对象获取PreparedStatement对象并传入即将要执行的SQL语句PreparedStatement pstmt = conn.prepareStatement(sql);//5 设置参数---设置除了id之外的所有字段pstmt.setInt(1, id);//6 执行传入PreparedStatement对象的SQL语句int count = pstmt.executeUpdate();// count代表影响的行数//7 处理结果---若为true则执行成功；反之则执行失败System.out.println(count > 0);//8 释放资源pstmt.close();conn.close();}
  }