二、SQL注入

2.1 什么是SQL注入

用户输入的数据中有SQL关键词,导致在执行SQL语句时出现一些不正常的情况.这就是SQL注入!

---

出现SQL注入是很危险

2.2 避免SQL注入

问题出现在用户输入数据时,里面有关键词,再配合字符串拼接导致出现SQL注入.所以为了避免SQL注入,可以在用户输入数据到SQL之前,先把SQL语句预编译,预处理后,JDBC就会知道此SQL需要几个参数,后续再将用户输入的数据给参数填充.

这就是PreparedStatement

三、PreparedStatement【重点】

PreparedStatement是Statement的子接口,用来预处理SQL语句

PreparedStatement使用

• 先写SQL语句,SQL语句中的参数不能直接拼接,而是使用?占位
• 使用ps预处理SQL语句,处理的?号,ps内部就会知道此SQL语句需要几个参数
• 再动态给?处填充值

package com.qf.jdbc;import java.sql.*;
import java.util.Scanner;
/*** @Author:二手Java程序员* @DateTime 2023/3/21 10:35**/public class Demo2_LoginPlus {public static void main(String[] args) throws Exception {Scanner scanner = new Scanner(System.in);System.out.println("请输入用户名:" );String username = scanner.nextLine( );System.out.println("请输入密码:" );String password = scanner.nextLine( );Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2217?useSSL=false", "root", "123456");// 改造SQL,将拼接变量,变成?占位String sql = "select * from tb_user where username = ? and password = ?";System.out.println("处理前:  " + sql);// 由之前的Statement换成PreparedStatement// 将改造好的SQL,传入方法PreparedStatement ps = conn.prepareStatement(sql);System.out.println("处理后: " + ps );// 给处理好的占位参数赋值// ps.setXxx() 给指定Xxx类型赋值// 第一个?,下标是1ps.setString(1,username);ps.setString(2,password);System.out.println("填充后: " + ps );//【特别注意!!!!】 此处executeQuery不需要再传入SQL参数!!!ResultSet rs = ps.executeQuery();if (rs.next()) {System.out.println("登录成功!!" );} else {System.out.println("用户名或密码错误!" );}rs.close();ps.close();conn.close();}
}

请输入用户名:
111
请输入密码:
111' or '1=1
处理前:  select * from tb_user where username = ? and password = ?
处理后: select * from tb_user where username = ** NOT SPECIFIED ** and password = ** NOT SPECIFIED **
填充后: select * from tb_user where username = '111' and password = '111\' or \'1=1'
用户名或密码错误!

四、预处理语句完成CRUD

4.1 插入

package com.hui.homework;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;/*** @Author:二手Java程序员* @DateTime 2023/3/21 13:44**/
public class insert {public static void main(String[] args) throws Exception {input();}private static void input() throws SQLException, ClassNotFoundException {Scanner scan = new Scanner(System.in);System.out.println("请输入用户名");String username = scan.next();System.out.println("请输入密码");String password = scan.next();in (username,password);}private static void in(String username, String password) throws ClassNotFoundException, SQLException {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2301","root","hui778203081");String sql = "select * from tb_user where username = ? and password = ?";System.out.println(sql);// 防止SQL注入PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1,username);ps.setString(2,password);}
}

4.2 更新

package com.hui.homework;import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;/*** @Author:二手Java程序员* @DateTime 2023/3/21 15:05**/
public class Update {public static void main(String[] args) throws Exception {input();}private static void input() throws Exception {Scanner scan = new Scanner(System.in);System.out.println("请输入要修改哪条数据：");int id = scan.nextInt();System.out.println("请输入要修改的用户名");String username = scan.next();System.out.println("请输入要修改的密码");String password = scan.next();User user = new User();user.setUsername(username);user.setPassword(password);user.setId(id);up(user);}private static void up(User user) throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2301?useSSL=false","root","hui778203081");String sql = "update tb_user set username = ?,password = ? where id = ?";System.out.println(sql);PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1,user.getUsername());ps.setString(2,user.getPassword());ps.setInt(3,user.getId());int n = ps.executeUpdate();if (n>0){System.out.println("修改成功！");}else {System.out.println("修改失败！");}conn.close();ps.close();}
}

4.3 删除

package com.hui.homework;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;/*** @Author:二手Java程序员* @DateTime 2023/3/21 14:56**/
public class Delete {public static void main(String[] args) throws Exception {input();}private static void input() throws SQLException, ClassNotFoundException {Scanner scan = new Scanner(System.in);System.out.println("请输入要删除的id");int id = scan.nextInt();// 获取User user = new User();user.setId(id);rm(user.getId());}private static void rm(int id) throws ClassNotFoundException, SQLException {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2301?useSSL=false","root","hui778203081");String sql = "delete from tb_user where id = ?";// 防止SQL注入PreparedStatement ps = conn.prepareStatement(sql);ps.setInt(1,id);int n = ps.executeUpdate();if (n > 0){System.out.println("删除成功！！！");}}
}

五、事务处理

事务是逻辑一组操作,要么全部成功,要么全部失败!

---

使用mysql客户端操作事务

• 因为mysql支持事务,且每句话都在事务内,且自动提交
• 所以关闭自动提交事务,手动开启事务 start transaction
• 正常写sql/执行sql
• 一切正常,提交事务 commit
• 如果不正常,要回滚 rollback

JDBC也可以完成事务操作

• conn.setAutoCommit(false) 关闭自动提交,就相当于是开启手动管理
• 正常的处理sql
• 一切正常,提交事务 conn.commit()
• 如果不正常,回滚 conn.rollback()

演示: 以转账案例演示

package com.qf.tx;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;/*** @Author:二手Java程序员* @DateTime 2023/3/21 10:35**/
public class Demo6_TX {// 张三转账给李四public static void main(String[] args) {Connection conn = null;PreparedStatement ps1 = null;PreparedStatement ps2 = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2217?useSSL=false", "root", "123456");// 【1 开启事务】conn.setAutoCommit(false);// 张三的钱减少100String sql1 = "update account set money = money - 100 where id = 1";ps1 = conn.prepareStatement(sql1);int num = ps1.executeUpdate( );if (num > 0) {System.out.println("张三转账(-100)完成!");}System.out.println(1/0 ); // 模拟在转账中,出现异常,后续不执行// 李四的钱要增加100String sql2 = "update account set money = money + 100 where id = 2";ps2 = conn.prepareStatement(sql2);int num2 = ps2.executeUpdate( );if (num2 > 0) {System.out.println("李四转账(+100)完成!");}// 【2 一切顺利,提交事务】conn.commit();} catch (Exception e) {try{// 【3 不顺利,中间有异常,回滚事务】conn.rollback();}catch (Exception e2) {System.out.println("回滚事务异常!!" );e2.printStackTrace();}System.out.println("SQL异常!!!");e.printStackTrace( );} finally {try {ps1.close( );ps2.close( );conn.close( );} catch (Exception e) {System.out.println("关流时有异常!!");e.printStackTrace( );}}}
}

另外发现: 建立与Mysql连接后,关流之前,可以执行很多次SQL语句

六、DBUtil【理解，会用】

DBUtil操作数据库的工具类,因为发现每次操作数据库,JDBC的步骤第1,2,5步完全重复的,即加载驱动,获得连接对象,已经最后的关流是每次都要写但每次都是一样的!!!

---

现在设计工具类,简化第1,2,5步

• 设计个方法,调用直接获得连接对象
• 设计个方法,调用直接关闭全部的流对象

package com.qf.util;import java.io.InputStream;
import java.sql.*;
import java.util.Properties;/*** @Author:二手Java程序员* @DateTime 2023/3/21 10:35**/
public class DBUtil {// 创建Properties类对象,专用于操作properties文件private static final Properties properties = new Properties();/*** 加载驱动的目的是为了在JVM中有sql运行的环境* 该环境有一份就行了,不用重复加载* ------------------------------------* static 静态代码块* 1) 保证内存中只有一份* 2) 保证随着类加载而加载,即该代码块会执行*/static {// 通过反射的技术获得字节码文件// 再通过字节码文件将配置文件读取成输入流InputStream inputStream = DBUtil.class.getResourceAsStream("/jdbc.properties");try {// 再通过流获得其中数据properties.load(inputStream);// 从properties对象取值Class.forName(properties.getProperty("driverClass"));} catch (Exception e) {System.out.println("加载驱动异常!!" );e.printStackTrace( );}}/*** 一般会将关于JDBC配置信息,抽取出来,形成一个配置文件,方便维护* 文件类型是properties文件,该文件类似map,键值对类型* 名字 jdbc.properties* 位置 src/jdbc.properties* 内容*/public static Connection getConnection() {Connection conn = null;try{conn = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username") ,properties.getProperty("password") );} catch (Exception e) {System.out.println("获得连接出异常!!!" );e.printStackTrace();}return conn;}/*** 关闭所有流*/public static void closeAll(Connection conn, Statement s) {if (conn != null) {try {conn.close();} catch (SQLException throwables) {throwables.printStackTrace( );}}if (s != null) {try {s.close();} catch (SQLException throwables) {throwables.printStackTrace( );}}}public static void closeAll(Connection conn, Statement s, ResultSet rs){if (conn != null) {try {conn.close();} catch (SQLException throwables) {throwables.printStackTrace( );}}if (s != null) {try {s.close();} catch (SQLException throwables) {throwables.printStackTrace( );}}if (rs != null) {try {rs.close();} catch (SQLException throwables) {throwables.printStackTrace( );}}}
}

在src下创建jdbc.properties文件

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java2217?useSSL=false
username=root
password=123456