·今日内容

零、 复习昨日
一、作业
二、SQL注入
三、PreparedStatement
四、事务
五、DBUtil

零、 复习昨日

见晨考

一、作业

package com.qf.homework;import com.qf.entity.User;import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/*** --- 天道酬勤 ---** @author QiuShiju* @desc*/
public class CRUD {public static void main(String[] args) throws ClassNotFoundException {User user = new User( );user.setId(4);user.setUsername("xiaoli");user.setPassword("123456");user.setSex("男");user.setAge(19);user.setMoney(1000);user.setBirthday(new Date());// 注册//addUser(user);// 删除//int i = deleteById(4);//System.out.println(i > 0 ?"OK":"ERROR" );// 根据id查询// User u = getUserById(20);// System.out.println(u );// 查全部//List<User> list = findAll( );// for (User user1 : list) {//// }//list.forEach(e -> System.out.println(e ));// 更新User user2 = new User( );user2.setId(4);user2.setUsername("小李子");user2.setPassword("666666");// user2.setSex("男");// user2.setAge(19);// user2.setMoney(1000);// user2.setBirthday(new Date());int i = updateById2(user2);System.out.println(i > 0 ?"OK":"ERROR" );}/*** 方案2: 动态更新*      根据对象属性值是否为空,动态的拼接sql*      update tb  set username= 1 where  id = 1*      update tb  set password = 1 where  id = 1*      update tb  set username= 1 , password = 1 where  id = 1*      update tb  set a = 1 ,b= 1 , c = 1where  id = 1*/public static int updateById2(User user) {Connection conn = null;Statement statement = null;int num = 0;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2307?useSSL=false", "root", "123456");statement = conn.createStatement( );String sql = "update tb_user set ";if (user.getUsername() != null) {sql += "username = '" + user.getUsername()+"',";}if (user.getPassword() != null) {sql += "password = '" + user.getPassword()+"',";}if (user.getSex() != null) {sql += "sex = '" + user.getSex()+"',";}if (user.getAge() != 0) {sql += "age = " + user.getAge()+",";}if (user.getBirthday() != null) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String format = sdf.format(user.getBirthday( ));sql += "birthday = '" +format+"',";}if (user.getMoney() != 0.0) {sql += "money = " + user.getMoney()+",  ";}// 找到最后一个逗号位置int index = sql.lastIndexOf(",");String sql2 = sql.substring(0, index);sql2 += " where id = " + user.getId();System.out.println("sql --> " + sql2 );num = statement.executeUpdate(sql2);}catch (Exception e) {e.printStackTrace();}finally {try {statement.close( );conn.close( );}catch (SQLException e) {e.printStackTrace();}}return num;}//通过id更改用户数据/*** 注意:根据id更新,即参数User对象中一定有id属性值* 更新有2种方案:* 方案1: 全表更新*      接收全部字段的数据(要求user对象的属性全部有值),更新全部字段*/public static int updateById(User user) {Connection conn = null;Statement statement = null;int num = 0;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2307?useSSL=false", "root", "123456");statement = conn.createStatement( );SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String format = sdf.format(user.getBirthday( ));String sql = "update tb_user set username = '"+user.getUsername()+"' , " +"password = '"+user.getPassword()+"'," +"sex = '"+user.getSex()+"'," +"age = "+user.getAge()+"," +"birthday = '"+format+"'," +"money = "+user.getMoney()+" " +"where id = "+user.getId();System.out.println("sql --> " + sql );num = statement.executeUpdate(sql);}catch (Exception e) {e.printStackTrace();}finally {try {statement.close( );conn.close( );}catch (SQLException e) {e.printStackTrace();}}return num;}// 设计方法,查询全部数据,返回值是List集合,集合中是全部用户数据public static List<User> findAll(){Connection conn = null;Statement statement = null;User user = null;// 创建集合,存储所有对象ArrayList<User> list = new ArrayList<>( );try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2307?useSSL=false", "root", "123456");statement = conn.createStatement( );ResultSet rs = statement.executeQuery("select * from tb_user");while(rs.next()) { // 判断成功,说明查到数据// 取出数据int id = rs.getInt("id");String username1 = rs.getString("username");String password1 = rs.getString("password");String sex = rs.getString("sex");int age = rs.getInt("age");java.sql.Date birthday = rs.getDate("birthday");double money = rs.getDouble("money");// 创建对象user = new User( );// 封装数据user.setId(id);user.setUsername(username1);user.setPassword(password1);user.setSex(sex);user.setAge(age);user.setBirthday(birthday);user.setMoney(money);// 将每个对象存储到集合list.add(user);}}catch (Exception e) {e.printStackTrace();}finally {try {statement.close( );conn.close( );}catch (SQLException e) {e.printStackTrace();}}return list;}// 根据id从数据库查询出用户信息public static User getUserById(int id) {Connection conn = null;Statement statement = null;User user = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2307?useSSL=false", "root", "123456");statement = conn.createStatement( );ResultSet rs = statement.executeQuery("select * from tb_user where id = " + id);if(rs.next()) { // 判断成功,说明查到数据// 取出数据String username1 = rs.getString("username");String password1 = rs.getString("password");String sex = rs.getString("sex");int age = rs.getInt("age");java.sql.Date birthday = rs.getDate("birthday");double money = rs.getDouble("money");// 创建对象user = new User( );// 封装数据user.setId(id);user.setUsername(username1);user.setPassword(password1);user.setSex(sex);user.setAge(age);user.setBirthday(birthday);user.setMoney(money);}}catch (Exception e) {e.printStackTrace();}finally {try {statement.close( );conn.close( );}catch (SQLException e) {e.printStackTrace();}}return user;}// 通过id删除用户数据,返回受影响行数// delete from tb_user where id =// TODO: 思考题 批量删除public static int deleteById(int id) {Connection conn = null;Statement statement = null;int num = 0;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2307?useSSL=false", "root", "123456");statement = conn.createStatement( );num = statement.executeUpdate("delete from tb_user where id = " + id);}catch (Exception e) {e.printStackTrace();}finally {try {statement.close( );conn.close( );}catch (SQLException e) {e.printStackTrace();}}return num;}// 向数据库插入一个用户,返回是否插入成功public static boolean addUser(User user) {Connection conn = null;Statement statement = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2307?useSSL=false", "root", "123456");statement = conn.createStatement( );// 因为new Date() 形式是Tue May 09 17:01:55 CST 2023// 插入时不识别,所以需要将这个日期格式改造成yyyy-MM-dd// 即格式化SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String format = sdf.format(user.getBirthday( ));String sql = "insert into tb_user values" +" ("+user.getId()+",'"+user.getUsername()+"'," +"'"+user.getPassword()+"','"+user.getSex()+"'," +""+user.getAge()+",'"+format+"'," +""+user.getMoney()+")";// 拼接完SQL要打印一下,确定SQL如何System.out.println(sql );int i = statement.executeUpdate(sql);if (i > 0) {return true;}}catch (Exception e) {e.printStackTrace();}finally {try {statement.close( );conn.close( );}catch (SQLException e) {e.printStackTrace();}}return false;}
}

二、SQL注入

2.1 什么是SQL注入

select * from tb_user where username = ‘111’ and password = ‘111’

select * from tb_user where username = ‘111’ and password = ‘111' or '1=1’

用户输入的数据中有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 QiuShiju* @desc 登录-使用预处理语句完成*/
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.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 插入

// 向数据库插入一个用户,返回是否插入成功public static boolean addUser(User user) throws Exception {// 1 注册驱动Class.forName("com.mysql.Driver");// 2 获得连接String url = "jdbc:mysql://localhost:3306/java2301?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.1 写sql,参数拼接,变?String sql = "insert into tb_user (username,password,phone,createTime,money,sex) " +"values (?,?,?,?,?,?)";// 3.2 处理?问PreparedStatement ps = conn.prepareStatement(sql);// 3.3 ?赋值ps.setString(1,user.getUsername());ps.setString(2,user.getPassword());ps.setString(3,user.getPhone());// 日期需要转换成java.sql.Date才能赋值java.sql.Date date = new java.sql.Date(user.getCreateTime( ).getTime( ));ps.setDate(4,date);ps.setDouble(5,user.getMoney());ps.setInt(6,user.getSex());// 4 执行int i = ps.executeUpdate( );return i > 0 ? true:false;}

4.2 更新

    public static int updateById(User user) throws Exception {// 1 注册驱动Class.forName("com.mysql.Driver");// 2 获得连接String url = "jdbc:mysql://localhost:3306/java2301?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.1 写sql,参数变?号String sql = "update tb_user set username = ?," +"password = ?," +"phone = ?," +"createTime = ?," +"money = ?," +"sex = ? " +"where id = ?";// 3.2 处理?问PreparedStatement ps = conn.prepareStatement(sql);// 3.3 ?赋值ps.setString(1,user.getUsername());ps.setString(2,user.getPassword());ps.setString(3,user.getPhone());// 日期需要转换成java.sql.Date才能赋值java.sql.Date date = new java.sql.Date(user.getCreateTime( ).getTime( ));ps.setDate(4,date);ps.setDouble(5,user.getMoney());ps.setInt(6,user.getSex());ps.setInt(7,user.getId());// 4 执行int i = ps.executeUpdate( );ps.close();conn.close();return i;}

4.3 删除

/// 通过id删除用户数据,返回受影响行数public static int deleteById(int id) throws Exception{// 1 注册驱动Class.forName("com.mysql.Driver");// 2 获得连接String url = "jdbc:mysql://localhost:3306/java2301?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);// 3.1 写sql,参数写?String sql = "delete from tb_user where id = ?";// 3.2 处理?PreparedStatement ps = conn.prepareStatement(sql);// 3.3 给?赋值ps.setInt(1,id);// 4 执行int i = ps.executeUpdate( );return i;}

4.4 查询

// 根据id从数据库查询出用户信息public static User getUserById(int id) throws Exception {// 1 注册驱动Class.forName("com.mysql.Driver");// 2 获得连接String url = "jdbc:mysql://localhost:3306/java2301?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);User user = null;// 3 预处理// 3.1 写sql,参数用?占位String sql = "select  * from tb_user where id = ?";// 3.2 预处理?号PreparedStatement ps = conn.prepareStatement(sql);// 3.3 给?赋值ps.setInt(1,id);// 4 执行sqlResultSet rs = ps.executeQuery( );if(rs.next()) {// 取出数据String username1 = rs.getString("username");String password1 = rs.getString("password");String phone = rs.getString("phone");Date createTime = rs.getDate("createTime");double money = rs.getDouble("money");int sex = rs.getInt("sex");// 封装数据user = new User();user.setId(id);user.setUsername(username1);user.setPassword(password1);user.setPhone(phone);user.setCreateTime(createTime);user.setMoney(money);user.setSex(sex);}return user;}

 // 设计方法,查询全部数据,返回值是List集合,集合中是全部用户数据public static List<User> findAll() throws Exception{// 1 注册驱动Class.forName("com.mysql.Driver");// 2 获得连接String url = "jdbc:mysql://localhost:3306/java2301?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);ArrayList<User> list = new ArrayList<>( );User user = null;// 3.1 写sqlString sql = "select * from tb_user";// 3.2 处理sqlPreparedStatement ps = conn.prepareStatement(sql);// 4 执行ResultSet rs = ps.executeQuery( );while(rs.next()) {// 取出数据int id = rs.getInt("id");String username1 = rs.getString("username");String password1 = rs.getString("password");String phone = rs.getString("phone");Date createTime = rs.getDate("createTime");double money = rs.getDouble("money");int sex = rs.getInt("sex");// 封装数据user = new User();user.setId(id);user.setUsername(username1);user.setPassword(password1);user.setPhone(phone);user.setCreateTime(createTime);user.setMoney(money);user.setSex(sex);// 【重点】 将对象存储入集合list.add(user);}return list;}

五、事务处理【了解】

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

---

使用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 QiuShiju* @desc 以转账为案例演示* --------------------* Statement语句和PreparedStatement语句* 与事务操作没有影响*/
public class Demo6_TX {// 张三转账给李四public static void main(String[] args) {Connection conn = null;PreparedStatement ps1 = null;PreparedStatement ps2 = null;try {Class.forName("com.mysql.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 QiuShiju* @desc*/
public class DBUtil {// 创建Properties类对象,专用于操作properties文件private static final Properties properties = new Properties();/*** 加载驱动的目的是为了在JVM中有sql运行的环境* 该环境有一份就行了,不用重复加载* ------------------------------------* static 静态代码块* 1) 保证内存中只有一份* 2) 保证随着类加载而加载,即该代码块会执行*/static {// 通过反射的技术获得字节码文件// 再通过字节码文件将配置文件读取成输入流InputStream inputStream = DBUtil.class.getResourceAsStream("/db.properties");try {// 再通过流获得其中数据properties.load(inputStream);// 从properties对象取值Class.forName(properties.getProperty("driverClass"));} catch (Exception e) {System.out.println("加载驱动异常!!" );e.printStackTrace( );}}/*** 一般会将关于JDBC配置信息,抽取出来,形成一个配置文件,方便维护* 文件类型是properties文件,该文件类似map,键值对类型* 名字 properties* 位置 src/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下创建db.properties文件

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