·今日内容

上课同步视频:CuteN饕餮的个人空间_哔哩哔哩_bilibili
同步笔记沐沐霸的博客_CSDN博客-Java2301
零、 复习昨日

零、 复习昨日

见晨考

一、作业

package com.qf.homework;import com.qf.model.User;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/*** --- 天道酬勤 ---** @author QiuShiju* @desc*/
public class Demo_CRUD {public static void main(String[] args) throws Exception {// User user = getUserById(1);// System.out.println(user );// 创建对象,准备注册// User user2 = new User( );// user2.setUsername("周期");// user2.setPassword("123456");// user2.setPhone("120");// user2.setCreateTime(new Date( ));// user2.setMoney(4800.0);// user2.setSex(2);// boolean isok = addUser(user2);// System.out.println("注册是否成功" + isok );// List list = findAll( );// for (User user : list) {//     System.out.println(user );// }User user3 = new User( );user3.setId(5);user3.setUsername("admin");user3.setPassword("11111");//user3.setCreateTime(new Date(  ));updateById2(user3);}// 根据id从数据库查询出用户信息public static User getUserById(int id) throws Exception {// 1 注册驱动Class.forName("com.mysql.jdbc.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 获得执行语句Statement s = conn.createStatement( );User user = null;String sql = "select * from tb_user where id = " + id;System.out.println("拼接后的sql --> " + sql );ResultSet rs = s.executeQuery(sql);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;}// 向数据库插入一个用户,返回是否插入成功public static boolean addUser(User user) throws Exception {// 1 注册驱动Class.forName("com.mysql.jdbc.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 获得执行语句Statement s = conn.createStatement( );SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String date = sdf.format(user.getCreateTime( ));String sql = "insert into tb_user (username,password,phone,createTime,money,sex) " +"values ('"+user.getUsername()+"','"+user.getPassword()+"','"+user.getPhone()+"','"+date+"',"+user.getMoney()+","+user.getSex()+")";System.out.println("拼接后的sql ---> " + sql );int num = s.executeUpdate(sql);return num > 0 ? true:false;}/// 通过id删除用户数据,返回受影响行数public static int deleteById(int id){return 0;}// 设计方法,查询全部数据,返回值是List集合,集合中是全部用户数据public static List findAll() throws Exception{// 1 注册驱动Class.forName("com.mysql.jdbc.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 获得执行语句Statement s = conn.createStatement( );ArrayList list = new ArrayList<>( );User user = null;String sql = "select * from tb_user";ResultSet rs = s.executeQuery(sql);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;}// 通过id更改用户数据// 方案1: 更新全部字段public static int updateById(User user) throws Exception {// 1 注册驱动Class.forName("com.mysql.jdbc.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 获得执行语句Statement s = conn.createStatement( );// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// String date = sdf.format(user.getCreateTime( ));String sql = "update tb_user set username = '"+user.getUsername()+"'," +"password = '"+user.getPassword()+"'," +"phone = '"+user.getPhone()+"'," +//"createTime = '"+date+"'," +"money = "+user.getMoney()+"," +"sex = "+user.getSex()+" " +"where id = " + user.getId();System.out.println("拼接后的sql -->" + sql );int i = s.executeUpdate(sql);s.close();conn.close();return i;}// 方案2:// update tb_user set phone = '248589792' where id = 6// update tb_user set username = 'admin' where id = 6// update tb_user set username = 'admin', password = '123456' where id = 6// 这就是需要根据传入的User对象属性的值是否为null,判断是否要更新该字段// 即sql语句需要根据情况决定是否拼接某一个字段// 这就是动态sqlpublic static int updateById2(User user) throws Exception {// 1 注册驱动Class.forName("com.mysql.jdbc.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 获得执行语句Statement s = conn.createStatement( );String sql = "update tb_user set ";StringBuilder sb = new StringBuilder(sql);if (user.getUsername() != null) {// sql += " username = '" + user.getUsername()+"',";sb.append(" username = '" + user.getUsername()+"',");}if (user.getPassword() != null) {// sql += " password = '" + user.getPassword()+"',";sb.append(" password = '" + user.getPassword()+"',");}if (user.getPhone() != null) {// sql += " phone = '" + user.getPhone()+"',";sb.append(" phone = '" + user.getPhone()+"',");}if (user.getCreateTime() != null) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String date = sdf.format(user.getCreateTime( ));// sql += " createTime = '" + date+"',";sb.append(" createTime = '" + date+"',");}if (user.getMoney() != 0.0) {//sql += " money = " + user.getMoney()+",";sb.append(" money = " + user.getMoney()+",");}if (user.getSex() != 0) {// sql += " sex = " + user.getSex()+",";sb.append(" sex = " + user.getSex()+",");}int index = sb.lastIndexOf(",");sb.deleteCharAt(index);sb.append(" where id = " + user.getId());System.out.println(sb );// String newSql = sql.substring(0, index);// newSql += " where id = " + user.getId();// System.out.println("动态sql -->" + newSql );int i = s.executeUpdate(sb.toString( ));return i;}
}

二、SQL注入

2.1 什么是SQL注入

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

---

出现SQL注入是很危险

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J8MQXsEa-1679393807375)(C:/Users/qiush/AppData/Roaming/Typora/typora-user-images/image-20230321084020448.png)]

2.2 避免SQL注入

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

这就是PreparedStatement

三、PreparedStatement【重点】

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

PreparedStatement使用

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nrQNix7Y-1679393807377)(C:/Users/qiush/AppData/Roaming/Typora/typora-user-images/image-20230321113132176.png)]

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.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 插入

// 向数据库插入一个用户,返回是否插入成功public static boolean addUser(User user) throws Exception {// 1 注册驱动Class.forName("com.mysql.jdbc.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.jdbc.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.jdbc.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.jdbc.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 findAll() throws Exception{// 1 注册驱动Class.forName("com.mysql.jdbc.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 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.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 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("jdbc.driver"));} 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("jdbc.url"),properties.getProperty("jdbc.username") ,properties.getProperty("jdbc.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文件

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