Java进阶-JDBC（八）

一 、 JDBC入门

1.1 JDBC简述及原理

1.1.1 简述

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API（工具）。
JDBC是Java访问数据库的标准规范。
规范的直接体现是java中接口
可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和工具类组成。

1.1.2 原理

是由各大数据库厂商来实现对应接口。(jar包来实现)
驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。
Java和数据库想要连接，必须有一个前提，遵循数据库的格式，格式由数据库厂商来实现
今天使用mysql数据库的驱动（点我下载如下所示）

1.2、JDBC的入门案例

需求：查询数据表中数据，并显示在控制台上

1.2.1、流程分析

1.2.2、案例准备

（1）创建数据库（jdbc）和表

#创建部门表
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
#创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), 
salary DOUBLE, 
join_date DATE, 
dept_id INT
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-04',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1)

（2）创建项目，导入环境

package com.zql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Demo1 {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        //3.获取执行sql语句对象
        Statement statement = connection.createStatement();
        String sql = "select * from emp";
        //4.执行sql
        ResultSet rs = statement.executeQuery(sql);
        //5.处理结果集
        while (rs.next()){
            String name = rs.getString("name");
            System.out.println(name);
        }
        //6.释放资源
        rs.close();
        statement.close();
        connection.close();
    }
}

运行结果显示

JDBC固定实现思路：
1、注册驱动
2、获取连接
3、获取执行sql语句的对象
4、执行sql语句
5、处理结果集
6、释放资源

二、JDBC-API详解

2.1、注册驱动

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

2.2、获取连接

就是通过驱动管理器里的驱动程序，连接数据库，连接数据库完成，就会返回Connection对象
DrvierManager 驱动管理器。 注册驱动，数据库链接等

2.3、获取发送SQL语句对象(交通工具)

//3.获取执行sql语句对象
 Statement statement = connection.createStatement();

2.4、执行SQL语句

Statement

2.5、处理结果集

2.6、释放资源

关闭资源，建议从后往前关闭（从小到大关闭）

//6.释放资源
        rs.close();
        statement.close();
        connection.close();

三、完成JDBC增删改查操作

3.1、从数据库查询所有数据（入门案例）

3.2、向数据库增加数据

3.3、向数据库修改数据

3.4、从数据库根据ID删除数据

3.5、从数据库根据ID查询某条数据

package com.zql;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Demo2 {

    /*向数据库增加数据*/
    @Test
    public void addEmp() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        //3.获取sql对象
        Statement statement = connection.createStatement();
        //4.执行sql语句
        String sql = "insert into emp values(null,'Daniel','男',1000.0,'2022-06-18',2)";

        int i = statement.executeUpdate(sql);
        //5.处理结果集
        if(i > 0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
        //关闭资源
        statement.close();
        connection.close();
    }


    /*向数据库修改数据*/
    @Test
    public void updateEmp() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        //3.获取sql对象
        Statement statement = connection.createStatement();
        //4.执行sql语句
        String sql = "update emp set name = '送悟空' where id = 1";

        int i = statement.executeUpdate(sql);
        //5.处理结果集
        if(i > 0){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }
        //关闭资源
        statement.close();
        connection.close();
    }


    /*从数据库根据ID删除数据*/
    @Test
    public void deleteEmp() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        //3.获取sql对象
        Statement statement = connection.createStatement();
        //4.执行sql语句
        String sql = "delete from emp where id = 8";

        int i = statement.executeUpdate(sql);
        //5.处理结果集
        if(i > 0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
        //关闭资源
        statement.close();
        connection.close();
    }


    /*从数据库根据ID查询某条数据*/
    @Test
    public void selectByIdEmp() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        //3.获取sql对象
        Statement statement = connection.createStatement();
        //4.执行sql语句
        String sql = "select * from emp where id = 3";

        ResultSet resultSet = statement.executeQuery(sql);
        //5.处理结果集
        while (resultSet.next()){
            System.out.println(resultSet.getString("name"));
        }
        //关闭资源
        statement.close();
        connection.close();
    }

}

3.6 定义一个方法，查询表中的数据将其封装为对象，然后装载集合，返回

（1）创建Emp类

package com.zql;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Emp {

    private int id;
    private String name;
    private String gender;
    private Double salary;
    private String join_date;

    public Emp() {
    }

    public Emp(int id, String name, String gender, Double salary, String join_date) {
        this.id = id;
        this.name = name;
        this.gender = gender;
        this.salary = salary;
        this.join_date = join_date;
    }

    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 getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public String getJoin_date() {
        return join_date;
    }

    public void setJoin_date(String join_date) {
        this.join_date = join_date;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", salary=" + salary +
                ", join_date='" + join_date + '\'' +
                '}';
    }
}

（2）创建测试

package com.zql;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Demo3 {

    @Test
    public void queryAll() throws Exception {

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        Statement statement = connection.createStatement();
        String sql = "select * from emp";
        ResultSet re = statement.executeQuery(sql);
        ArrayList<Emp> list = new ArrayList<>();
        while (re.next()){
            Emp emp = new Emp();
            emp.setId(re.getInt("id"));
            emp.setName(re.getString("name"));
            emp.setGender(re.getString("gender"));
            emp.setSalary(re.getDouble("salary"));
            emp.setJoin_date(re.getString("join_date"));
            System.out.println(emp);
            list.add(emp);
            System.out.println(list);
        }
        re.close();
        statement.close();
        connection.close();
    }
}

运行结果：

四、抽取工具类

4.1 、完成工具类抽取

package com.utils;

import java.sql.*;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class JdbcUtils {

    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/jdbc";
    private static String username = "root";
    private static String password = "root";

    /*注册驱动*/
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /*获取连接*/
    public static Connection getConnection() {

        try {
            return DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    /*释放资源*/
    public static void colseAll(ResultSet rest, Statement s, Connection conn){

        if(rest != null){
            try {
                rest.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(s != null){
            try {
                s.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

4.2、使用工具类改写以上案例

package com.zql;

import com.utils.JdbcUtils;
import org.junit.jupiter.api.Test;

import javax.sql.rowset.JdbcRowSet;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Demo4 {

    @Test
    public void selectEmp() throws Exception {

        Connection connection = JdbcUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from emp";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println(resultSet.getString("name"));
        }
        JdbcUtils.colseAll(resultSet,statement,connection);
    }
}

4.3、使用工具类完成用户登录操作

需求：模拟用户输入账号、密码登录网站
实现效果：

(1)创建表：

#创建用户表
create table user(
  uid int PRIMARY KEY AUTO_INCREMENT  ,
  username varchar(100),
  password varchar(100)
);
#初始化数据
insert into user values (null,'admin','1234');
insert into user values (null,'xiaoming','1234');
insert into user values (null,'laowang','1234');

（2）登录案例实现：

package com.zql;

import com.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Demo5 {
    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();

        Connection conn = JdbcUtils.getConnection();
        Statement state = conn.createStatement();
        String sql = "select * from user where username='"+username+"'and password='"+password+"'";
        ResultSet rest = state.executeQuery(sql);
            if(rest.next()){
                System.out.println("欢迎"+rest.getString("username")+"登录成功");
            }else{
                System.out.println("用户名或密码错误");
            }
       
        JdbcUtils.colseAll(rest,state,conn);
    }
}

4.4、SQL注入问题（解决看五）

我们让用户输入的密码和SQL语句进行字符串拼接。用户输入的内容作为了SQL语句语法的一部分，改变了原有SQL真正的意义，以上问题称为SQL注入。 要解决SQL注入就不能让用户输入的密码和我们的SQL语句进行简单的字符串拼接

解决可看 5.5

五、JDBC预处理对象(PreparedSatement)

PreparedStatement是Statement接口的子接口

5.1、PreparedSatement的执行原理

我们写的SQL语句让数据库执行，数据库不是直接执行SQL语句字符串。和Java一样，数据库需要执行编译后的SQL语句（类似Java编译后的字节码文件）。

1、Statement 对象每执行一条SQL语句都会先将这条SQL语句发送给数据库编译，数据库再执行。

上面2条SQL语句我们可以看到大部分内容是相同的，只是数据略有不一样。数据库每次执行都编译一次。
如果有1万条类似的SQL语句，数据库需要编译1万次，执行1万次，显然效率就低了
2、 prepareStatement() 会先将SQL语句发送给数据库预编译。 PreparedStatement 会引用着预编译后的结果。
可以多次传入不同的参数给 PreparedStatement 对象并执行。相当于调用方法多次传入不同的参数。

上面预编译好一条SQL，2次传入了不同的参数并执行。如果有1万条类似的插入数据的语句。数据库只需要预编译一次，传入1万次不同的参数并执行。减少了SQL语句的编译次数，提高了执行效率。

5.2、PreparedSatement的好处

1. prepareStatement() 会先将SQL语句发送给数据库预编译。 PreparedStatement 会引用着预编译 后的结果。
   可以多次传入不同的参数给 PreparedStatement 对象并执行。减少SQL编译次数，提高效率。
2. 安全性更高，没有SQL注入的隐患。
3. 提高了程序的可读性

5.3、 PreparedStatement 的使用

如何获取？以及如何执行？

5.4、使用PreparedStatement完成增删改查操作

package com.zql;

import com.utils.JdbcUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Demo6 {

        /*查询*/
        @Test
        public void seleUser() throws Exception {

            Connection conn = JdbcUtils.getConnection();
            String sql = "select * from user";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet res = ps.executeQuery();
            while (res.next()){
                System.out.println(res.getString("username"));
            }
            JdbcUtils.colseAll(res,ps,conn);
        }

    /*增加*/
    @Test
    public void addUser() throws Exception {

        Connection conn = JdbcUtils.getConnection();
        String sql = "insert into user values (null,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
       ps.setString(1,"Daniel");
       ps.setString(2,"baoqiang");
       ps.addBatch();

       ps.setString(1,"Jenny");
       ps.setString(2,"hollow");
       ps.addBatch();
        int[] i = ps.executeBatch();
        System.out.println(i.length);

    }

    /*增加*/
    @Test
    public void updateUser() throws Exception {

        Connection conn = JdbcUtils.getConnection();
        String sql = "update user set username=? where uid = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,"dd");
        ps.setInt(2,8);

        int i = ps.executeUpdate();
        if(i > 0){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }
    }

    /*增加*/
    @Test
    public void deleteUser() throws Exception {

        Connection conn = JdbcUtils.getConnection();
        String sql = "delete from user where uid = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,8);
        int i = ps.executeUpdate();
        if(i > 0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
    }
}

5.5、使用PreparedStatement改造登录案例

package com.zql;

import com.utils.JdbcUtils;

import java.sql.*;
import java.util.Scanner;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class Demo5 {
    public static void main(String[] args) throws Exception {

        Scanner scanner = new Scanner(System.in);
        System.out.println("Please input your username：");
        String username = scanner.nextLine();
        System.out.println("Please input your password：：");
        String password = scanner.nextLine();

        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from user where username=? and password=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,username);
        ps.setString(2,password);
        ResultSet rs = ps.executeQuery();

        if(rs.next()){
                System.out.println("Welcome-----"+rs.getString("username")+"---success!");
            }else{
                System.out.println("用户名或密码错误");
            }
    }
}

输入成功显示：

错误显示防止sql注入：

5.6、 使用三层架构去做登录案例

（1）案例结构：

（2）工具类：

package cn.login.utils;

import java.sql.*;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class JdbcUtils {

            private static String driver = "com.mysql.jdbc.Driver";
            private static String url = "jdbc:mysql://localhost:3306/jdbc";
            private static String username = "root";
            private static String password = "root";

                //1.注册驱动
                static{
                    try {
                        Class.forName(driver);
                    } catch (ClassNotFoundException e) {
                        throw new RuntimeException(e);
                    }
                }
             //2.获取连接
            public static  Connection getConnection() throws Exception {
                Connection conn = DriverManager.getConnection(url, username, password);
                return conn;
            }

            //释放资源
            public static void closeAll(ResultSet rs, Statement st,Connection conn){
                if(rs != null){
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                }
                if(st != null){
                    try {
                        st.close();
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    if(conn != null){
                        try {
                            conn.close();
                        } catch (SQLException e) {
                            throw new RuntimeException(e);
                        }
                    }
                }
            }
}

（3）实体：

package cn.login.domain;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class User {

    private int uid;
    private String username;
    private String password;

    public User() {
    }

    public User(int uid, String username, String password) {
        this.uid = uid;
        this.username = username;
        this.password = password;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

（4）视图（控制）：

package cn.login.main;

import cn.login.domain.User;
import cn.login.service.UserService;

import java.util.Scanner;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class AppLogin {

    public static void main(String[] args) throws Exception {

        Scanner sc = new Scanner(System.in);

        System.out.println("Please input your username:");

        String username = sc.nextLine();

        System.out.println("Please input your password:");

        String password = sc.nextLine();

        UserService userService = new UserService();

       User user =  userService.login(username,password);

       if(user != null){
           System.out.println("欢迎"+user.getUsername());
       }else{
           System.out.println("用户名或密码错误");
       }
    }
}

（5）service层：

package cn.login.service;

import cn.login.dao.UserDao;
import cn.login.domain.User;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class UserService {


    public User login(String username, String password) throws Exception {

        UserDao userDao = new UserDao();

        User user = userDao.login(username,password);

        return user;
    }
}

（6）dao层：

package cn.login.dao;

import cn.login.domain.User;
import cn.login.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author:Daniel
 * @Version 1.0
 */
public class UserDao {

    public User login(String username, String password) throws Exception {

        Connection conn = JdbcUtils.getConnection();

        String sql = "select * from user where username = ? and password =?";

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setString(1, username);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();
        User user = null;
        while (rs.next()) {
            user = new User();
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));

        }
        return user;
    }
}

输入正确显示：

输入错误显示（防止sql注入）