第三章 JDBC
3.1 JDBC的API
下载MySQL 8版本的jdbc驱动
下载之后解压，找到里面的驱动，将驱动复制到项目工程的lib文件夹
进行工程配置，点击Project Settings，点击Module
JDBC应用本身是CS架构的
实际使用时，一般是完整的格式，不会使用默认值，数据库是独立部署的，为了安全，端口号也不会使用3306
useSSL：网络传输使用非对称加密useUnicode：一般含有中文的网页都需要使用unicode编码characterEncoding：具体编码方式serverTimezone：服务器时区allowPublicKeyRetrieval：允许从客户端获取公钥加密传输，一般开启
创建数据库连接以及异常处理：
public static void main(String[] args) {
try {
//1. 注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 使用DriverManager获取新的数据库连接
String url = "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "zzzz";
Connection connection = DriverManager.getConnection(url,username,password);
System.out.println(connection);
} catch (ClassNotFoundException e) {
//ClassNotFoundException：JDBC驱动不存在的时候抛出
throw new RuntimeException(e);
} catch (SQLException e) {
//SQLException：所有JDBC操作失败的异常
throw new RuntimeException(e);
}
}
3.2 JDBC的查询操作以及SQL注入漏洞
3.2.1 实现按部门查询员工的功能
public class QueryCommand implements Command {
public void execute() {
System.out.print("请输入部门名称：");
Scanner in = new Scanner(System.in);
String pdname = in.nextLine();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
/**
* 在实现类中实现按部门查询的方法
* 标准的JDBC五个步骤
*/
//1. 加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 创建数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "zzzz");
//3. 创建Statement对象（用于执行sql语句）
stmt = conn.createStatement();
//结果集
System.out.println("select * from employee where dname='" + pdname + "'");
rs = stmt.executeQuery("select * from employee where dname='" + pdname + "'");
//4. 遍历查询结果
//rs.next()返回布尔值，代表是否存在下一条记录
//如果有，返回true,同时结果集提取下一条记录
//如果没有，返回false，循环就会停止
while (rs.next()) {
Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5. 关闭连接,释放资源
try {
if(rs != null){ //!=null说明已经被实例化了
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt != null){
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null && !conn.isClosed() ) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.2.2 SQL注入攻击
SQL注入起因是没有对引号进行处理，从而改变原有的SQL语句。导致原有的查询条件失效。虽然不会影响程序的运行，但是会造成数据泄露。
3.2.3 PreparedStatement预编译SQL
public class PstmtQueryCommand implements Command {
public void execute() {
System.out.print("请输入部门名称：");
Scanner in = new Scanner(System.in);
String pdname = in.nextLine();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
/**
* 在实现类中实现按部门查询的方法
* 标准的JDBC五个步骤
*/
//1. 加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 创建数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "zzzz");
//3. 创建Statement对象（用于执行sql语句）
String sql = "select * from employee where dname=? and eno > ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,pdname);
pstmt.setInt(2, 3500);
//结果集
rs = pstmt.executeQuery();
//4. 遍历查询结果
//rs.next()返回布尔值，代表是否存在下一条记录
//如果有，返回true,同时结果集提取下一条记录
//如果没有，返回false，循环就会停止
while (rs.next()) {
Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5. 关闭连接,释放资源
try {
if(rs != null){ //!=null说明已经被实例化了
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(pstmt != null){
pstmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null && !conn.isClosed() ) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.3 JDBC工具类的抽取以及增删改
3.3.1 封装DBUtils工具类
DBUtils主要是封装打开连接和关闭连接
public class DBUtils {
/**
* 创建新的数据库连接
* @return 新的Connection对象
* @throws SQLException
* @throws ClassNotFoundException
*/
public static Connection getConnection() throws SQLException, ClassNotFoundException {
//异常先直接抛出，实际调用的时候再去捕获
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "zzzz");
return conn;
}
/**
* 关闭连接，释放资源
* @param rs 结果集对象
* @param stmt Statement对象
* @param conn Connection对象
*/
public static void closeConnection(ResultSet rs, Statement stmt, Connection conn) {
try {
if(rs != null){ //!=null说明已经被实例化了
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt != null){
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null && !conn.isClosed() ) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3.3.2 JDBC实现新增数据
cnt是影响数据库记录的条数
public class InsertCommand implements Command {
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工编号");
int eno = in.nextInt();
System.out.println("请输入员工姓名");
String ename = in.next();
System.out.println("请输入员工薪资");
float salary = in.nextFloat();
System.out.println("请输入员工部门");
String dname = in.next();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtils.getConnection();
String sql = "insert into employee(eno, ename, salary, dname) values (?,?,?,?);";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, eno);
pstmt.setString(2, ename);
pstmt.setFloat(3, salary);
pstmt.setString(4, dname);
int cnt = pstmt.executeUpdate(); //所有改变数据表的操作都是executeUpdate()
System.out.println("数据库更新的记录条数为："+cnt);
System.out.println(ename + "员工入职手续已办理");
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
DBUtils.closeConnection(null,pstmt,conn);
}
}
}
3.3.3 实现JDBC更新与删除数据的操作
public class UpdateCommond implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工编号");
int eno = in.nextInt();
System.out.println("请输入员工薪资");
float salary = in.nextFloat();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtils.getConnection();
String sql = "update employee set salary = ? where eno = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1, salary);
pstmt.setInt(2, eno);
int cnt = pstmt.executeUpdate();
if (cnt == 1) {
System.out.println("员工薪资调整完毕");
} else {
System.out.println("未找到"+ eno + "编号员工数据");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
DBUtils.closeConnection(null, pstmt, conn);
}
}
}
public class DeleteCommond implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工编号");
int eno = in.nextInt();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtils.getConnection();
String sql = "delete from employee where eno = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, eno);
int cnt = pstmt.executeUpdate();
if (cnt == 1) {
System.out.println("员工离职手续办理完成");
} else {
System.out.println("未找到"+ eno + "编号员工数据");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
DBUtils.closeConnection(null, pstmt, conn);
}
}
}
3.4 JDBC的事务操作
事务：要么把事情一次性做完，要么什么都不做
使用批量导入员工举例，要么全部导入，要么全部取消
/**
* JDBC中的事务控制
*/
public class TransactionSample {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = pstmt.getConnection();
conn.setAutoCommit(false); //开启手动提交事务
String sql = "insert into employee(eno, ename, salary, dname) values (?, ?, ?, ?)";
for (int i = 1000; i < 1999; i++) {
if (i == 1005) {
throw new RuntimeException("提交失败");
}
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, i);
pstmt.setString(2,"员工"+i);
pstmt.setFloat(3,4000);
pstmt.setString(4, "市场部");
pstmt.executeUpdate(); //手动提交时，中间数据会放入到事务区中
}
conn.commit(); //提交数据
} catch (SQLException e) {
e.printStackTrace();
try {
if(conn != null && !conn.isClosed()) {
conn.rollback(); //数据回滚
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
DBUtils.closeConnection(null, pstmt, conn);
}
}
}
3.5 基于实体类的封装分页
while (rs.next()) {
Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
}
之前在查询时，只是把查询结果放入一个一个变量，而Java是变量对象的语言，实际开发中应该将查询结果封装到一个实体类中，再将一个一个实体类放入一个集合当中进行存储
/**
* 员工实体类
*/
public class Employee {
/**
* JavaBean有书写格式的要求：
* 1. 具备默认构造函数
* 2. 属性私有
* 3. 存在getter和setter
*/
public Employee() {}
//私有属性通常与数据库字段相同
private int eno;
private String ename;
private float salary;
private String dname;
public int getEno() {
return eno;
}
public void setEno(int eno) {
this.eno = eno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public float getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
}
封装完实体类后，要进行分页查询逻辑
/**
* 分页查询员工数据
*/
public class PaginationCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入页号：");
int page = in.nextInt();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List list = new ArrayList<Employee>();
try {
conn = DBUtils.getConnection();
//mysql分页依靠limit关键字实现
String sql = "select * from employee limit ?, 10"; //从？开始，取10条记录
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, (page - 1) * 10);
rs = pstmt.executeQuery();
while(rs.next()) {
Integer eno = rs.getInt("eno");
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
//封装到实体类中
Employee emp = new Employee();
emp.setEno(eno);
emp.setEname(ename);
emp.setSalary(salary);
emp.setDname(dname);
//将实体类放入集合中
list.add(emp);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
DBUtils.closeConnection(rs, pstmt, conn);
}
}
}
3.6 JDBC数据批处理
pstmt = conn.prepareStatement(sql);
for (int i=200000;i<300000;i++){
pstmt.setInt(1, i);
pstmt.setString(2, "员工" + i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.addBatch();//将参数加入批处理任务
//pstmt.executeUpdate();
}
pstmt.executeBatch();//执行批处理任务
conn.commit();//提交数据
一条SQL附带100000组sql参数，节省了解析sql的时间
3.7 Druid连接池
程序启动时，统一对数据库连接进行统一管理；当程序需要数据库连接时，不是由程序自己创建，而是由连接池进行分配。
在druid的github官网下载jar包，然后加入到项目依赖中；随后在src根目录下创建配置文件druid-config.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username=root
password=zzzz
initialSize=10
maxActive=20 #最大连接数，超过最大连接数，其他应用程序将进行等待
/**
* Druid连接池配置与使用
*/
public class DruidSample {
public static void main(String[] args) {
//1. 加载属性文件
Properties properties = new Properties();
//DruidSample.class.getResource获取当前类路径下对象文件的路径
String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
try {
//防止路径中的中文和空格干扰
propertyFile = new URLDecoder().decode(propertyFile,"UTF-8");
properties.load(new FileInputStream(propertyFile));
} catch (Exception e) {
throw new RuntimeException(e);
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//2. 获取DataSource数据源对象（用DataSource指代要操作的数据库是什么）
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//3. 创建数据库连接
conn = dataSource.getConnection();
pstmt = conn.prepareStatement("select * from employee limit 0, 10");
rs = pstmt.executeQuery();
while (rs.next()) {
Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
//不使用连接池，conn.close直接关闭连接，而使用连接池，是将连接回收至连接池当中
DBUtils.closeConnection(rs, pstmt, conn);
}
}
}
3.8 数据库工具组件
public class DBUtilsSample {
public static void query() {
Properties properties = new Properties();
String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
try {
//防止路径中的中文和空格干扰
propertyFile = new URLDecoder().decode(propertyFile,"UTF-8");
//加载配置文件
properties.load(new FileInputStream(propertyFile));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//传入dataSource，就知道对哪一个数据库进行操作
QueryRunner qr = new QueryRunner(dataSource);
//BeanListHandler自动的将结果转为实体类
List<Employee> list = qr.query("select * from employee limit ?, 10", //sql
new BeanListHandler<>(Employee.class), //每一条记录转换为那个实体对象
new Object[]{10}); //数组中的值与？一一对应
for(Employee emp: list) {
System.out.println(emp.getEname());
}
//不需要关闭连接
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static void update() {
Properties properties = new Properties();
String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
Connection conn = null;
try {
//防止路径中的中文和空格干扰
propertyFile = new URLDecoder().decode(propertyFile,"UTF-8");
//加载配置文件
properties.load(new FileInputStream(propertyFile));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//读写离不开事务，事务基于连接的，update就需要获取连接
conn = dataSource.getConnection();
//手动提交
conn.setAutoCommit(false);
String sql1 = "update employee set salary = salary + 1000 where eno = ?";
String sql2 = "update employee set salary = salary - 500 where eno = ?";
QueryRunner qr = new QueryRunner(); //查询需要传入数据源 写入的时候不需要
qr.update(conn, sql1, new Object[]{1000});
qr.update(conn, sql2, new Object[]{1001});
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
if(conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
query();
update();
}
}
