WEB03_Day02数据库连接池、SQL注入、JDBC批量操作、分页查询、JDBC获取新增数据的自增主键值

一、数据库连接池

1.1 定义：

　　数据库连接池就是一个包含多个数据库连接对象的容器。

1.2 优势：

• 每个连接对象都是需要进行创建，使用，关闭，销毁，如果反复的对于连接对象进行频繁创建和销毁操作，可能会导致程序出现内存泄漏和内存溢出的情况发生。

  
  



• 需要创建多个连接对象，此时使用数据库连接池可以预先进行创建多个数据库连接对象，当使用的时候，可以直接从数据库连接池中进行获取，使用完毕以后，会进行归还，该操作可以让对象进行反复的使用，进行可以提高程序的执行效率，避免内存不足。

  
  

1.3 数据库连接池的使用

• 在项目的 pom.xml 文件中导入数据库连接池的 jar 包

  
  

当前所使用的数据库连接池为 dbcp，后期还学学到阿里公司自研的数据库连接池 druid。DBCP(DataBase Collection Pool)

 
 <dependency>
     <groupId>commons-dbcpgroupId>
     <artifactId>commons-dbcpartifactId>
     <version>1.4version>
 dependency>

• 重构 DBUtils 工具类，结合 DBCP数据库连接池进行使用

  
  

 package cn.tedu.dbcp;
 ​
 import java.io.IOException;
 import java.io.InputStream;
 import java.sql.Connection;
 import java.util.Properties;
 import org.apache.commons.dbcp.BasicDataSource;
 ​
 /**
  * JDBC连接MySQL工具类
  * @author Tedu
  *
  */
 public class DBUtils {
  //声明一个数据库连接池对象
  private static BasicDataSource ds;
 
  //将初始化数据库连接池对象的操作书写到静态块中
  static {
  //1.进行解析jdbc.properties配置文件，用户获取里面的连接参数
  //1.1获取输入流对象，用户读取项目的配置文件(磁盘->内存)
  InputStream in = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
  //1.2使用Properties对象进行解析配置文件
  Properties p = new Properties();
  //1.3 加载配置文件的输入流
  try {
  p.load(in);
  } catch (IOException e) {
  e.printStackTrace();
  } catch (Exception e) {
  e.printStackTrace();
  }
 
  //2.使用解析得到的参数值进行获取连接对象
  String driver = p.getProperty("driver");
  String url = p.getProperty("url");
  String userName = p.getProperty("username");
  String password = p.getProperty("password");
 
  //创建数据库连接池对象，并将链接参数赋值给数据库连接池
  ds = new BasicDataSource();
  ds.setDriverClassName(driver);
  ds.setUrl(url);
  ds.setUsername(userName);
  ds.setPassword(password);
  //设置数据库连接池的初始数量
  ds.setInitialSize(5);
  //设置数据库连接池的最大活跃数量
  ds.setMaxActive(5);
  //设置最大的空闲连接数量
  ds.setMaxIdle(3);
 
  }
 
  /**
   * 静态方法，用户获取连接对象
  * @return 返回连接对象
  * @throws Exception
  */
  public static Connection getConn() throws Exception {
 
  //获取连接对象
  Connection conn = ds.getConnection();
  return conn;
 
  }
 
 }

• 进行测试工具类中获取的连接对象

  
  

 package cn.tedu.dbcp;
 ​
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.Statement;
 import org.junit.Test;
 ​
 public class JdbcDemo01 {
 
 
  @Test
  public void testSelect() {
  try (Connection conn = DBUtils.getConn();){
  Statement stat = conn.createStatement();
  String str = "SELECT deptno,dname,loc FROM dept";
  ResultSet rs = stat.executeQuery(str);
  while (rs.next()) {
  int deptno = rs.getInt(1);
  String dname = rs.getString(2);
  String loc = rs.getString(3);
  System.out.println("部门编号：" + deptno + ",部门名字：" + dname + ",地址：" + loc);
  }
 
  } catch (Exception e) {
  e.printStackTrace();
  }
  }
 ​
 ​
 }

• 测试结果：

  
  

 部门编号：1,部门名字：神仙,地址：天庭
 部门编号：2,部门名字：妖怪,地址：盘丝洞
 部门编号：3,部门名字：普通人,地址：北京
 部门编号：4,部门名字：赛亚人,地址：外星球

二、SQL 注入

2.1 定义：

　　SQL 注入就是指用户在可填写的内容中包含了可以进行运行的 sql 语句，如果包含了可以运行的 sql 语句有可能程序会被进行篡改。

2.2 SQL注入演示

• 准备 user 数据表，表中存储用户的 id，用户名，密码。

  
  

 -- 如果存在 user 数据表，进行删除
 DROP TABLE IF EXISTS `user`;
 -- 创建 user 数据表
 CREATE TABLE `user`(
  id int AUTO_INCREMENT,
  username varchar(20) NOT NULL,
  password varchar(20) NOT NULL,
  PRIMARY KEY(id)
 )charset=utf8;
 -- 插入数据
 INSERT INTO `user` VALUES(null,'baojiaqi','12345678'),(null,'zhangyun','888888');
 -- 查询数据
 SELECT count(*) FROM `user` WHERE username='baojiaqi' AND password='12345678';

• 通过 jdbc 连接 MySQL 数据库，进行查询某位用户是都可以进行登录。

  
  

 package cn.tedu.dbcp;
 ​
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.Statement;
 import java.util.Scanner;
 import org.junit.Test;
 ​
 public class JdbcDemo02 {
 
  @Test
  public void testSQLInjection() {
  /*
  * 测试 SQL 注入风险演示
  */
  Scanner scan = new Scanner(System.in);
  //接收用户在控制台中输入的用户名和密码
  System.out.println("请输入用户名：");
  String userName = scan.nextLine();
  System.out.println("请输入密码：");
  String password = scan.nextLine();
 
  //连接 MySQL
  try (Connection conn = DBUtils.getConn();){
  //创建命令对象
  Statement stat = conn.createStatement();
  String sqlStr = "SELECT count(*) FROM user WHERE username='"
  +userName+"' AND password='"+password+"'";
  System.out.println("SQL语句：" + sqlStr);
  //''or'123'='123'
  //执行 sql，返回结果集
  ResultSet rs = stat.executeQuery(sqlStr);
  while (rs.next()) {
  int count = rs.getInt(1);
  if (count > 0) {
  System.out.println("登录成功");
  } else {
  System.out.println("登录失败");
  }
  }
 
  } catch (Exception e) {
 
  }
  scan.close();
 
  }
 
 
 }

 请输入用户名：
 baojiaqi
 请输入密码：
 12345678
 SQL语句：SELECT count(*) FROM user WHERE username='baojiaqi' AND password='12345678'
 登录成功

 请输入用户名：
 baojiaqi
 请输入密码：
 'or'123'='123
 SQL语句：SELECT count(*) FROM user WHERE username='baojiaqi' AND password=''or'123'='123'
 登录成功

2.3 解决 SQL 注入风险

2.3.1 PreparedStatement

　　该接口的功能和 Statement 接口的功能大致相同，但是PreparedStatement接口在Statement接口的基础之上做了改进。

2.3.2 优点：

• 防止 SQL 注入

  在创建 SQL 命令对象的时候，对 SQL 语句进行锁定，不会让用户进行字符串拼接，只会将用户输入的内容当成是一个数值。

  
  



• 预编译机制：

  预先进行编译，当前写好的 SQL 进行提前编译，这样有助于提高运行的速度。

  
  



• 代码可读性好

  在进行书写的时候并不需要进行对 SQL 做字符串拼接，而是进行 SQL 传值操作。

  
  

2.3.3 代码案例：

package cn.tedu.dbcp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
import org.junit.Test;
public class JdbcDemo03 {

@Test
public void testSQLInjection() {
/*
* 测试解决SQL注入风险
*/
Scanner scan = new Scanner(System.in);
//接收用户在控制台中输入的用户名和密码
System.out.println("请输入用户名：");
String userName = scan.nextLine();
System.out.println("请输入密码：");
String password = scan.nextLine();

//连接 MySQL
try (Connection cOnn= DBUtils.getConn();){

/*
* PreparedStatement接口在创建的时候需要指定后期要执行的 SQL 语句，
* 进行提前编译，在 SQL 语句中需要条件的位置使用占位符？进行站位
*/
String sqlStr = "SELECT count(*) FROM user WHERE username=? AND password=?";
//创建命令对象
PreparedStatement ps = conn.prepareStatement(sqlStr);
//对 SQL 中的占位符进行传值
ps.setString(1, userName);
ps.setString(2, password);

//执行 sql，返回结果集
ResultSet rs = ps.executeQuery();
while (rs.next()) {
int count = rs.getInt(1);
if (count > 0) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
}

} catch (Exception e) {

}
scan.close();

}


}

• 测试结果：

  
  

请输入用户名：
baojiaqi
请输入密码：
'or'123'='123
登录失败

三、JDBC 批量操作

3.1 定义：

　　使用 JDBC 技术进行多条 SQL 语句的一次执行操作，这个就是批量操作。

3.2 场景：

　　假设项目的业务需求中需要一次性执行某条 SQL 时，需要执行多次，那么像之前的操作，每次执行 SQL 语句都需要进行创建命令对象，通过命令对象对 SQL 语句进行执行，往往整体的效率是比较低下的，所以说当前提供了可以进行批量操作的相关 API。

　　当前命令对象中提供了 addBatch()方法和 executeBatch()方法，允许我们一次执行 SQL 语句时，可以进行批量的添加需要执行的 SQL 语句，然后统一的进行发送给数据库运行，从而可以进行提高执行效率。

3.3 使用 Statement 命令对象进行对数据的批量操作

package cn.tedu.batch;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Arrays;
import org.junit.Test;
public class BatchOperation01 {

@Test
public void batch() {
//获取连接对象
try (Connection cOnn= DBUtils.getConn();){
//准备 sql
String sqlStr1 = "INSERT INTO user VALUES(null,'刘想','666')";
String sqlStr2 = "INSERT INTO user VALUES(null,'刘朝辉','888')";
String sqlStr3 = "INSERT INTO user VALUES(null,'孟沙','333')";
String sqlStr4 = "INSERT INTO user VALUES(null,'顾有鹏','555')";

/*
* addBatch()方法
* 一次性进行往命令对象中添加多条 SQL 语句
*
* executeBatch()方法
* 批量执行添加到命令对象中的一批 SQL 语句，
* 返回值是int[],数组中的每个元素表示每条 sql 语句在执行时受影响的行数
*/
Statement stat = conn.createStatement();

stat.addBatch(sqlStr1);
stat.addBatch(sqlStr2);
stat.addBatch(sqlStr3);
stat.addBatch(sqlStr4);

int[] nums = stat.executeBatch();
System.out.println(Arrays.toString(nums));


/*
* 以下的代码书写方案执行效率低下
*/
// Statement stat = conn.createStatement();
// stat.executeUpdate(sqlStr1);
// stat.executeUpdate(sqlStr2);
// stat.executeUpdate(sqlStr3);
// stat.executeUpdate(sqlStr4);


} catch (Exception e) {
e.printStackTrace();
}

}


}

• 测试结果：

  
  

[1, 1, 1, 1]

3.4 使用 PreparedStatement 命令对象进行对数据的批量操作

package cn.tedu.batch;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
public class BatchOperation02 {

@Test
public void testBath() {
//获取连接对象
try (Connection cOnn= DBUtils.getConn();){
//准备 sql
String sqlStr = "INSERT INTO user VALUES(null,?,?)";
//创建命令对象
PreparedStatement ps = conn.prepareStatement(sqlStr);

//循环向命令对象中进行添加需要执行的 sql 语句
for (int i = 1; i <= 130; i++) {
//向 sql 中的占位符进行传值
ps.setString(1, "user" + i);
ps.setString(2, "password" + i);
//将 sql 语句批量添加到命令对象
ps.addBatch();
/*
* 将需要执行的 sql 语句批量发送给数据库,
* 原因是为了减轻数据库的压力，防止内存占用过大。
*/
if (i % 20 == 0) {
ps.executeBatch();
}
}
//防止有未发送给数据库执行的 sql 语句，再次提交
ps.executeBatch();

} catch (Exception e) {
e.printStackTrace();
}
}

}

四、分页查询

4.1 定义：

　　当查询的数据信息较多时，会在不同的网页中进行展示，称之为分页查询。

4.2 优点：

• 可以节约用户的流量

  按照页数进行查询数据，未进行访问的页面并不会进行消耗用户的流量

  
  



• 提升用户体验感

  当将所有的数据都在一页中进行展示，用户往往不需要全部都查看，只需要查看部分信息即可得到自己想要的答案。

  
  



• 节约服务器资源

  并不需要全部查询出数据信息，进而可以节约服务的资源

  
  

4.3 Java 代码实现分页

package cn.tedu.page;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
import org.junit.Test;
public class JdbcPage {

@Test
public void page() {
/*
* 希望根据用户在控制台中输入页号和每页显示的数据条数进行查询
* 需求：每页显示 5 条数据， 查询第 3 页的数据
* 第一页 跳过数据条数 每页显示数据条数
* 1 0 5
* 2 1*5 5
* 3 2*5 5
* 4 3*5 5
* n (n-1)*5 5
*/
Scanner scan = new Scanner(System.in);
System.out.println("请输入每页显示的数据条数：");
int size = scan.nextInt();
System.out.println("请输入查询数据的页号：");
int pageNum = scan.nextInt();

try (Connection cOnn= DBUtils.getConn();){
String sqlStr = "SELECT id,username,password FROM user limit ?,?";
PreparedStatement ps = conn.prepareStatement(sqlStr);
int count = (pageNum-1)*size;
ps.setInt(1, count);
ps.setInt(2, size);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
int id = rs.getInt(1);
String username = rs.getString(2);
String password = rs.getString(3);
System.out.println(id + "," + username + "," + password);

}

} catch (Exception e) {
e.printStackTrace();
}
scan.close();


}


}

• 测试结果：

  
  

请输入每页显示的数据条数：
5
请输入查询数据的页号：
3
11,user5,password5
12,user6,password6
13,user7,password7
14,user8,password8
15,user9,password9

五、JDBC 获取新增数据的自增主键值

5.1 获取新增数据的主键值

业务：当向 user 表中插入一条数据以后，如果在没有进行书写 SELECT 查询语句的时候如何获取新增数据的主键值？

package cn.tedu.primary;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import org.junit.Test;
public class JdbcPrimary01 {

@Test
public void getPrimaryKey() {

/*
* 通过控制台输入需要进行新增的用户名和密码
*/
Scanner scan = new Scanner(System.in);
System.out.println("请输入用户名：");
String name = scan.nextLine();
System.out.println("请输入密码：");
String password = scan.nextLine();

try (Connection cOnn= DBUtils.getConn();){
String sqlStr = "INSERT INTO user VALUES(null,?,?)";
/*
* 在使用prepareStatement方法的时候，选择两个参数的重载方法，
* 参数一：进行预编译的 sql 语句
* 参数二：表示设定需要查询新增数据生成的主键值
*/
PreparedStatement ps = conn.prepareStatement(sqlStr,Statement.RETURN_GENERATED_KEYS);
ps.setString(1,name);
ps.setString(2, password);

ps.executeUpdate();

/*
* 通过命令对象调用getGeneratedKeys()方法获取刚刚新增数据的主键值，返回结果是 ResultSet
* 当前可以获取新增数据主键值的前提一定是在创建命令对象的时候设置了Statement.RETURN_GENERATED_KEYS常量
*/
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
//因为结果对象中只有一个值，就是刚刚进行新增数据的主键
int id = rs.getInt(1);
System.out.println("新增数据的主键为：" + id);
}

} catch (Exception e) {
e.printStackTrace();
}

scan.close();
}

}

5.2 案例

　　业务需求：当前准备两张数据表，分别为球队表和球员表，然后编写一个Java 程序，让用户可以注册球队和球员的信息，当前需要确保新注册的球员能够对应已有的球队。

-- 球队表
CREATE TABLE team(
team_id int AUTO_INCREMENT,
team_name varchar(20),
PRIMARY KEY(team_id)
)charset=utf8;
-- 向球队表新增数据
INSERT INTO team VALUES(null,'中国队');
INSERT INTO team VALUES(null,'美国队');
INSERT INTO team VALUES(null,'西班牙队');
-- 球员表
CREATE TABLE player(
player_id int AUTO_INCREMENT,
player_name varchar(20),
palyer_team_id int,
PRIMARY KEY(player_id)
)charset=utf8;
-- 向球员表中新增数据
INSERT INTO player VALUES(null,'易建联',1);
INSERT INTO player VALUES(null,'杜兰特',2);
INSERT INTO player VALUES(null,'加索尔',3);

-- 新增球员成功，但是确实数据的完整性，并没有id为的4的球队
INSERT INTO player VALUES(null,'包佳奇',4);
-- 删除缺失完整性的数据
DELETE FROM player WHERE player_id=4;
-- 在球员表中添加外键,为了确保数据的完整性
ALTER TABLE player ADD FOREIGN KEY(palyer_team_id) REFERENCES team(team_id);
-- 添加外键以后再次进行执行新增球员表中的球队信息为 4 的数据，插入失败
INSERT INTO player VALUES(null,'包佳奇',4);
-- 报错结果
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`newdb3`.`player`, CONSTRAINT `player_ibfk_1` FOREIGN KEY (`palyer_team_id`) REFERENCES `team` (`team_id`))

5.3 作业：

　　业务：当前已经准备了球员表和球队表，希望用户在控台中输入需要新增的球员名字，和新增球队的名字，当前球员的数据中，针对于所在球队这列的值是新增球队的主键值，最终完整球员和球队信息的新增。

package cn.tedu.primary;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/**
* 球员和球队建立外键关系
* 1.新增球队信息
* 2.获取新增球队的主键值
* 3.新增球员信息
* 将新增球队的id作为新增球员的一个外键字段的值
* @author Tedu
*
*/
public class TeamPlayerDemo {
public static void main(String[] args) {
Scanner scan = new Scanner(System.in);
System.out.println("请输入球队名称：");
String team = scan.nextLine();
System.out.println("请输入球员姓名：");
String player = scan.nextLine();

try (Connection cOnn= DBUtils.getConn();){
//准备sql
String sql1 = "INSERT INTO team VALUES(null,?)";
//创建命令对象
PreparedStatement ps =
conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
//向sql中占位符赋值
ps.setString(1, team);
//执行插入球队sql语句
ps.executeUpdate();
//获取新增球队数据的主键值
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
//取出新增球队主键值
int teamId = rs.getInt(1);
String sql2 = "INSERT INTO player VALUES(null,?,?)";
//创建命令对象
PreparedStatement ps2 =
conn.prepareStatement(sql2);
ps2.setString(1, player);
ps2.setInt(2, teamId);
ps2.executeUpdate();
System.out.println("新增完毕");
}


} catch (Exception e) {
e.printStackTrace();
}
scan.close();

}
}