Day54 JDBC

JDBC：SUN公司提供的一套操作数据库的标准规范，就是使用Java语言操作关系型数据库的一套API

JDBC与数据库驱动的关系：接口与实现的关系

给大家画一个jdbc的工作模式图

在这里插入图片描述

1.JDBC的四大金刚

1.DriverManager：用于注册驱动

2.Connection: 表示与数据库创建的连接

3.Statement: 操作数据库sql语句的对象

4.ResultSet: sql语句执行后返回的结果集

2.JDBC操作步骤

1.创建工程，导入驱动jar包

2.注册驱动

Class.forName(“com.mysql.jdbc.Driver”);

3.获取连接

Connection conn = DriverManager.getConnection(url, username, password);

Java代码需要发送SQL给MySQL服务端，就需要先建立连接

Connection（数据库连接对象）作用：

获取执行 SQL 的对象

2.管理事务

4.定义SQL语句

String sql = “update…” ;

5.获取执行SQL对象

执行SQL语句需要SQL执行对象，而这个执行对象就是Statement对象

Statement stmt = conn.createStatement();

6.执行SQL

stmt.executeUpdate(sql);

ResultSet（结果集对象）作用：封装了SQL查询语句的结果

7.处理返回结果

8.释放资源

创建场景 ----------------------------------------------------------------------------

创建学生表，并添加数据

CREATE TABLE student(
	id INT(3) PRIMARY KEY auto_increment,
	name VARCHAR(32),
	sex VARCHAR(32),
	age INT(3),
	salary FLOAT(8,2),
	course VARCHAR(32)
)

INSERT INTO student(name,sex,age,salary,course) VALUES('龙俊','男',23,10000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('喻平','男',20,12000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('牛西燕','女',19,17000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('刘德华','男',21,15000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('马德华','男',27,10000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('霍建华','男',19,12000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('华晨宇','男',32,5000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('黄日华','男',45,4000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('任达华','男',28,7000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('周华健','男',30,8000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('欧阳震华','男',23,12000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('麻生希','女',30,7000,'HTML');
INSERT INTO student(name,sex,age,salary,course) VALUES('椎名空','女',23,6000,'HTML');
INSERT INTO student(name,sex,age,salary,course) VALUES('水野朝阳','女',28,8000,'HTML');

3.JDBC实现增删改查(最基本)

导包

在这里插入图片描述

项目结构

在这里插入图片描述

测试代码

public class Test01 {

    //添加数据
    @Test
    public void test01() throws ClassNotFoundException, SQLException {
        //导入驱动包
        Class.forName("com.mysql.cj.jdbc.Driver");

        //获取连接对象
        String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        //获取发送指令对象
        Statement statement = connection.createStatement();

        //发送SQL指令
        String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES('京香Julia','女',28,6000,'HTML');";
        int num = statement.executeUpdate(sql);
        System.out.println("对于" + num + "行造成了影响");

        //关闭资源
        statement.close();
        connection.close();
    }


    //删除数据
    @Test
    public void test02() throws ClassNotFoundException, SQLException {

        //导入驱动包
        Class.forName("com.mysql.cj.jdbc.Driver");

        //获取连接对象
        String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        //获取发送指令对象
        Statement statement = connection.createStatement();

        //发送SQL指令
        String sql = "DELETE FROM student WHERE id>10;";
        int num = statement.executeUpdate(sql);
        System.out.println("对于" + num + "行造成了影响");

        //关闭资源
        statement.close();
        connection.close();
    }

    //修改数据
    @Test
    public void test03() throws ClassNotFoundException, SQLException {

        //导入驱动包
        Class.forName("com.mysql.cj.jdbc.Driver");

        //获取连接对象
        String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        //获取发送指令对象
        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 + "行造成了影响");

        //关闭资源
        statement.close();
        connection.close();
    }

    //查询数据
    @Test
    public void test04() throws ClassNotFoundException, SQLException {

        //导入驱动包
        Class.forName("com.mysql.cj.jdbc.Driver");

        //获取连接对象
        String url = "jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTC";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        //获取发送指令对象
        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);
        }

        //关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }


}

4.资源的正确关闭

Finally ----- 关闭顺序和创建顺序相反

5.创建工具类

1.将变化的配置信息搬到配置文件中

2.工具类提供获取连接的方法

3.工具类提供关闭资源的方法

DBConfig.properties

在这里插入图片描述

driverName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/fy2401javaee?characterEncoding=utf8&serverTimezone=UTCuser
name=root
password=123456

数据库工具类

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();
            }
        }
    }
}

5.1 封装DBUtil - v1.0

package com.qf.jdbc;

public class Test01 {

    //添加数据
    @Test
    public void test01() throws SQLException {
        //获取连接对象
        Connection connection = DBUtil.getConnection();

        //获取发送指令对象
        Statement statement = connection.createStatement();

        //发送SQL指令
        String sql = "INSERT INTO student(name,sex,age,salary,course) VALUES('京香Julia','女',28,6000,'HTML');";
        int num = statement.executeUpdate(sql);
        System.out.println("对于" + num + "行造成了影响");

        //关闭资源
        DBUtil.close(connection,statement,null);
    }


    //删除数据
    @Test
    public 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);
    }

    //修改数据
    @Test
    public 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);
    }

    //查询数据
    @Test
    public 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);
    }

}

5.2 SQL注入问题

select * from student where name=’’ or 1=1 #’ and passwd=‘111111’; #：后面都是注释

出现原因：数据库分不清哪些是sql命令，哪些是数据

解决方案：告诉数据库哪些是sql命令，哪些是数据

需求：模拟登录功能

package com.qf.jdbc02;

public class Test01 {
    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);
    }
}

解决方案

采用预编译对象来编程

Statement

PreparedStatement

1.安全性，避免了SQL注入

2.性能，预编译，语句-编译-执行

注意：数据用？表示

package com.qf.jdbc02;

public class Test02 {
    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);
    }
}

* 设置参数值

上面的sql语句中参数使用 ? 进行占位，在之前之前肯定要设置这些 ? 的值。

> PreparedStatement对象：setXxx(参数1，参数2)：给 ? 赋值

> * Xxx：数据类型；如 setInt (参数1，参数2)

> * 参数：

> * 参数1：？的位置编号，从1 开始

> * 参数2：？的值

* 执行SQL语句

> executeUpdate(); 执行DDL语句和DML语句

> executeQuery(); 执行DQL语句

5.3 jdbc最终版本封装数据库 - v2.0

增删改都是一样的

在这里插入图片描述

添加数据 - 主键回填(主键是int类型可以返回)

在这里插入图片描述

数据库工具类

 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;

public class Student {

    private int id;
    private String name;
    private String sex;
    private int age;
    private float salary;
    private String course;

  //无参构造，有参构造，set，get，tostring省略

测试类封装数据库 - v2.0

package com.qf.jdbc03;

public class Test01 {
    /**
     * 知识点：封装数据库 - v2.0
     */

    //添加数据
    @Test
    public 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 + "行造成了影响");
    }

    //删除数据
    @Test
    public void test02() throws SQLException {
        String sql = "delete from student where id=?";
        int num = DBUtil.commonUpdate(sql, 3);
        System.out.println("对于" + num + "行造成了影响");
    }

    //修改数据
    @Test
    public void test03() throws SQLException {
        String sql = "update student set salary=? where id=?";
        int num = DBUtil.commonUpdate(sql, 15000,1);
        System.out.println("对于" + num + "行造成了影响");
    }

    //添加数据 -
    @Test
    public 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);
    }

    //查询数据
    @Test
    public 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);
        }
    }
}

intln(“对于” + num + “行造成了影响”);
}

//修改数据
@Test
public void test03() throws SQLException {
    String sql = "update student set salary=? where id=?";
    int num = DBUtil.commonUpdate(sql, 15000,1);
    System.out.println("对于" + num + "行造成了影响");
}

//添加数据 -
@Test
public 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);
}

//查询数据
@Test
public 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);
    }
}

}