JDBC【封装工具类、SQL注入问题】

day54

JDBC

封装工具类01

创建配置文件

DBConfig.properties

driverName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/qnz01?characterEncoding=utf8&serverTimezone=UTC
username=root
password=root

新建配置文件，不用写后缀名

创建工具类

将变化的配置信息搬到配置文件中
工具类提供获取连接的方法
工具类提供关闭资源的方法

package com.qf.utils;/*** 数据库工具类*/
public class DBUtil {private static String url;private static String username;private static String password;static{//负责加载配置文件，只加载一次Properties properties = new Properties();try {properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));} catch (IOException e) {throw new RuntimeException(e);}String driverName = properties.getProperty("driverName");url = properties.getProperty("url");username = properties.getProperty("username");password = properties.getProperty("password");try {//加载驱动只加载一次Class.forName(driverName);} catch (ClassNotFoundException e) {throw new RuntimeException(e);}}/*** 获取连接对象*/public static Connection getConnection() throws SQLException {Connection connection = DriverManager.getConnection(url,username,password);return connection;}/*** 关闭资源*/public static void close(Connection connection, Statement statement, ResultSet resultSet){if(resultSet != null){try {resultSet.close();} catch (SQLException e) {throw new RuntimeException(e);}}if(statement != null){try {statement.close();} catch (SQLException e) {throw new RuntimeException(e);}}if(connection != null){try {connection.close();} catch (SQLException e) {throw new RuntimeException(e);}}}}

理解

(1)新建配置文件
把导入驱动包和获取连接对象的参数url、root、password写入配置文件
(2)将获取连接对象方法封装到工具类DBUtil
01：加载配置文件
new 一个配置文件对象，配置文件对象调用加载方法加载进来【报异常先用try-catch】
之后就可以配置文件对象调用方法拿到配置信息【注意要与配置文件的名字相同，否则找不到就是null】
02：导入驱动包

03：在获取连接对象方法里，调用方法获取连接对象，返回连接对象

04：对于关闭资源，不用每次都写，直接在工具类写个关闭资源方法传入可能关的资源，里面非空判断哪些要关就行了

注意：

通常会把加载配置文件和导入驱动包放在静态代码块里，提高效率，不用获取一次连接加载一次，加载一次就行
对于重复使用的属性先静态定义【注意静态代码块只能初始化静态的属性】

基本操作报错try-catch【例如类这里的forName类未找到异常就要么资源文件没导入，要么配置文件对应的名称没写对】，对于需要对应不同业务处理异常就需要抛异常，在外面处理

现在对于先前的增删改查就不用那么麻烦，每个都写导入驱动包和获取连接对象的一大坨代码，当然这里就涉及封装工具类
直接用工具类调用获取连接对象的方法，关闭资源方法

测试类

对day53的增删改查进行进一步优化

package com.qf.jdbc01;import com.qf.utils.DBUtil;
import org.junit.Test;import java.sql.*;public class Test01 {/*** 知识点：封装DBUtil - v1.0*///添加数据@Testpublic void test01() throws SQLException {//获取连接对象Connection connection = DBUtil.getConnection();//获取发送指令对象Statement statement = connection.createStatement();//发送SQL指令String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES('柳如烟','女',28,6000,'HTML');";int num = statement.executeUpdate(sql);System.out.println("对于" + num + "行造成了影响");//关闭资源DBUtil.close(connection,statement,null);}//删除数据@Testpublic void test02() throws SQLException {//获取连接对象Connection connection = DBUtil.getConnection();//获取发送指令对象Statement statement = connection.createStatement();//发送SQL指令String sql = "DELETE FROM student WHERE id>10;";int num = statement.executeUpdate(sql);System.out.println("对于" + num + "行造成了影响");//关闭资源DBUtil.close(connection,statement,null);}//修改数据@Testpublic void test03() throws SQLException {//获取连接对象Connection connection = DBUtil.getConnection();//获取发送指令对象Statement statement = connection.createStatement();//发送SQL指令String sql = "UPDATE student SET age=21,salary=50000 WHERE id=3;";int num = statement.executeUpdate(sql);System.out.println("对于" + num + "行造成了影响");//关闭资源DBUtil.close(connection,statement,null);}//查询数据@Testpublic void test04() throws SQLException {//获取连接对象Connection connection = DBUtil.getConnection();//获取发送指令对象Statement statement = connection.createStatement();//发送SQL指令，并获取结果集对象String sql = "select * from student";ResultSet resultSet = statement.executeQuery(sql);//遍历结果集while(resultSet.next()){//判断是否有可迭代的数据行int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");int age = resultSet.getInt("age");float salary = resultSet.getFloat("salary");String course = resultSet.getString("course");System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course);}//关闭资源DBUtil.close(connection,statement,resultSet);}}

SQL注入问题

理解

ps：select * from student where name=‘’ or 1=1 #’ and passwd=‘111111’;

当输入用户名和密码
’ or 1=1 # '【对于它分不清sql命令和数据，就会or 1=1判断为true，#后面理解为注释，就直接登录进去了】

sql注入问题

package com.qf.jdbc02;import com.qf.utils.DBUtil;public class Test01 {/*** 知识点：SQL注入问题** 出现原因：数据库分不清哪些是sql命令，哪些是数据** 需求：模拟登录功能*/public static void main(String[] args) throws SQLException {Connection connection = DBUtil.getConnection();Statement statement = connection.createStatement();Scanner scan = new Scanner(System.in);System.out.println("请输入账号：");String usernameVal = scan.nextLine();System.out.println("请输入密码：");String passwordVal = scan.nextLine();//select * from user where username='' or 1=1 #' and password='12312345'String sql = "select * from user where username='"+usernameVal+"' and password='"+passwordVal+"'";ResultSet resultSet = statement.executeQuery(sql);if(resultSet.next()){String username = resultSet.getString("username");String password = resultSet.getString("password");String nikeName = resultSet.getString("nike_name");System.out.println("登录成功");System.out.println(username);System.out.println(password);System.out.println(nikeName);}else{System.out.println("登录失败");}DBUtil.close(connection,statement,resultSet);}
}

解决

采用预编译对象来编程：告诉他，使用prepareStatement

sql命令中用？表示数据
注意：prepareStatement继承于statement
现在用prepareStatement拿到的sql是没有数据的，得到它的对象
再将输入的数据设置给这个对象，注意方法SetString的两个参数(下标，值)【注意下标从1开始】

安全性，避免了SQL注入
性能，预编译，语句-编译-执行

package com.qf.jdbc02;import com.qf.utils.DBUtil;public class Test02 {/*** 知识点：SQL注入问题** 出现原因：数据库分不清哪些是sql命令，哪些是数据* 解决方案：告诉数据库哪些是sql命令，哪些是数据** 需求：模拟登录功能*/public static void main(String[] args) throws SQLException {Connection connection = DBUtil.getConnection();String sql = "select * from user where username=? and password=?";PreparedStatement statement = connection.prepareStatement(sql);//输入数据后，将数据设置给statement对象Scanner scan = new Scanner(System.in);System.out.println("请输入账号：");String usernameVal = scan.nextLine();System.out.println("请输入密码：");String passwordVal = scan.nextLine();statement.setString(1,usernameVal);statement.setString(2,passwordVal);ResultSet resultSet = statement.executeQuery();if(resultSet.next()){String username = resultSet.getString("username");String password = resultSet.getString("password");String nikeName = resultSet.getString("nike_name");System.out.println("登录成功");System.out.println(username);System.out.println(password);System.out.println(nikeName);}else{System.out.println("登录失败");}DBUtil.close(connection,statement,resultSet);}
}

封装工具类02

更新数据(添加、删除、修改):

01方法传的参数(一个sql命令，另一个数据不确定用可变参数)
02由于不知道下标和值用setObject设置给statement,但有多个参数需要遍历处理就写个方法来专门处理【处理statement对象参数数据的处理器，注意setObject下标从1开始】直接调用处理
03添加操作返回影响行数
04而考虑到关闭资源，处理异常就不会关闭资源，对代码进行try-finally,考虑作用域把定义对象放在之外

添加–主键回填:

就是添加数据后，不再是返回影响的行数，加一个查询返回主键
主键回填理解：

查询：

以前查询就打印出来结果，现在查询的数据封装成对象，不同表封装不同的对象
01学生类：有参无参自动生成时，window+alt+回车快速生成，生成时涉及属性选择ctrl+1全选
02方法返回值设置成list集合，类型用泛型；传的参数(哪个类的对象clazz【反射创建】，一个sql，一个可变参数)
03对于拿到的结果集，需要遍历，一个数据创建一个对象
先泛型创建对象【无数据】，获取数据字段名才可以利用反射去设置对象的属性
怎么获取：先用结果集对象调用方法获取表数据对象，再表数据对象调用方法获取字段个数，再在遍历中通过字段个数循环获取字段名，字段值

反射获取类的属性对象和设置对象的属性【对于反射设置属性回顾以前反射讲到的方法】

04然后将封装的对象添加到对象集合再返回

05而考虑到关闭资源，处理异常就不会关闭资源，对代码进行try-finally,考虑作用域把定义对象放在之外

package com.qf.utils;/*** 数据库工具类*/
public class DBUtil {private static String url;private static String username;private static String password;static{//负责加载配置文件，只加载一次Properties properties = new Properties();try {properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));} catch (IOException e) {throw new RuntimeException(e);}String driverName = properties.getProperty("driverName");url = properties.getProperty("url");username = properties.getProperty("username");password = properties.getProperty("password");try {//加载驱动只加载一次Class.forName(driverName);} catch (ClassNotFoundException e) {throw new RuntimeException(e);}}/*** 获取连接对象*/public static Connection getConnection() throws SQLException {Connection connection = DriverManager.getConnection(url,username,password);return connection;}/*** 关闭资源*/public static void close(Connection connection, Statement statement, ResultSet resultSet){if(resultSet != null){try {resultSet.close();} catch (SQLException e) {throw new RuntimeException(e);}}if(statement != null){try {statement.close();} catch (SQLException e) {throw new RuntimeException(e);}}if(connection != null){try {connection.close();} catch (SQLException e) {throw new RuntimeException(e);}}}/*** 更新数据（添加、删除、修改）*/public static int commonUpdate(String sql,Object... params) throws SQLException {Connection connection = null;PreparedStatement statement = null;try {connection = getConnection();statement = connection.prepareStatement(sql);paramHandler(statement,params);int num = statement.executeUpdate();return num;}finally {close(connection,statement,null);}}/*** 添加数据 - 主键回填(主键是int类型可以返回)*/public static int commonInsert(String sql,Object... params) throws SQLException {Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {connection = getConnection();statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);paramHandler(statement,params);statement.executeUpdate();resultSet = statement.getGeneratedKeys();int primaryKey = 0;if(resultSet.next()){primaryKey = resultSet.getInt(1);}return primaryKey;}finally {close(connection,statement,resultSet);}}/*** 查询数据*/public static <T> List<T> commonQuery(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {connection = getConnection();statement = connection.prepareStatement(sql);paramHandler(statement,params);resultSet = statement.executeQuery();//获取表数据对象ResultSetMetaData metaData = resultSet.getMetaData();//获取字段个数int count = metaData.getColumnCount();List<T> list = new ArrayList<>();while(resultSet.next()){T t = clazz.newInstance();//获取字段名及数据for (int i = 1; i <= count; i++) {String fieldName = metaData.getColumnName(i);Object fieldVal = resultSet.getObject(fieldName);setField(t,fieldName,fieldVal);}list.add(t);}return list;} finally {DBUtil.close(connection,statement,resultSet);}}/*** 处理statement对象参数数据的处理器*/private static void paramHandler(PreparedStatement statement,Object... params) throws SQLException {for (int i = 0; i < params.length; i++) {statement.setObject(i+1,params[i]);}}/*** 获取当前类及其父类的属性对象* @param clazz class对象* @param name 属性名* @return 属性对象*/private static Field getField(Class<?> clazz,String name){for(Class<?> c = clazz;c != null;c = c.getSuperclass()){try {Field field = c.getDeclaredField(name);return field;} catch (NoSuchFieldException e) {} catch (SecurityException e) {}}return null;}/*** 设置对象中的属性* @param obj 对象* @param name 属性名* @param value 属性值*/private static void setField(Object obj,String name,Object value){Field field = getField(obj.getClass(), name);if(field != null){field.setAccessible(true);try {field.set(obj, value);} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}}
}

调用工具类的方法实现增删改查

package com.qf.jdbc03;import com.qf.utils.DBUtil;public class Test01 {/*** 知识点：封装数据库 - v2.0*///添加数据@Testpublic void test01() throws SQLException {String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES(?,?,?,?,?)";int num = DBUtil.commonUpdate(sql, "天使萌", "女", 23, 10000, "HTML");System.out.println("对于" + num + "行造成了影响");}//删除数据@Testpublic void test02() throws SQLException {String sql = "delete from student where id=?";int num = DBUtil.commonUpdate(sql, 3);System.out.println("对于" + num + "行造成了影响");}//修改数据@Testpublic void test03() throws SQLException {String sql = "update student set salary=? where id=?";int num = DBUtil.commonUpdate(sql, 15000,1);System.out.println("对于" + num + "行造成了影响");}//添加数据 - 主键回填@Testpublic void test04() throws SQLException {String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES(?,?,?,?,?)";int primaryKey = DBUtil.commonInsert(sql, "铃原爱蜜莉", "女", 23, 10000, "HTML");System.out.println("返回的主键是：" + primaryKey);}//查询数据@Testpublic void test05() throws SQLException, InstantiationException, IllegalAccessException {String sql = "select * from student where id<?";List<Student> list = DBUtil.commonQuery(Student.class, sql, 8);for (Student stu : list) {System.out.println(stu);}}
}

学生类

package com.qf.jdbc03;public class Student {private int id;private String name;private String sex;private int age;private float salary;private String course;public Student() {}public Student(int id, String name, String sex, int age, float salary, String course) {this.id = id;this.name = name;this.sex = sex;this.age = age;this.salary = salary;this.course = course;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public float getSalary() {return salary;}public void setSalary(float salary) {this.salary = salary;}public String getCourse() {return course;}public void setCourse(String course) {this.course = course;}@Overridepublic String toString() {return "Student{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", age=" + age +", salary=" + salary +", course='" + course + '\'' +'}';}
}