全栈架构师之路

Appearance

JDBC ​

JDBC概述 ​

JDBC(Java Database Connectivity) Java连接数据库的规范，使用Java语言完成数据库操作。

JDBC入门 ​

导入数据库脚本 ​

核心API ​

快速入门 ​

java
public class JdbcPractice {
    @Test
    public void first() throws SQLException, ClassNotFoundException {
        // 1. 注册驱动
        Driver driver = new Driver();
        DriverManager.registerDriver(driver);

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/company_db",
                "root",
                "root");

        // 3. 获取Statement对象
        Statement stmt = connection.createStatement();

        // 4. 获取结果集
        int i = stmt.executeUpdate("INSERT INTO tb_user(`username`, `password`, `balance`) " +
                                         "VALUES('zhangsan', '123456', 1000)");

        // 5. 处理结果集
        System.out.println(i);

        // 6. 释放资源
        stmt.close();
        connection.close();
    }
}

注册驱动的第二种形式 ​

java
public class JdbcPractice {
 @Test
    public void second() throws SQLException, ClassNotFoundException {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/company_db",
                "root",
                "root");

        // 3. 获取Statement对象
        Statement stmt = connection.createStatement();

        // 4. 获取结果集
        int i = stmt.executeUpdate("INSERT INTO tb_user(`username`, `password`, `balance`) " +
                                         "VALUES('lisi', '234567', 1000)");

        // 5. 处理结果集
        System.out.println(i);

        // 6. 释放资源
        stmt.close();
        connection.close();
    }
}

JDBC深入 ​

JDBC完成增删改查操作 ​

插入数据 ​

java
public class JdbcPractice {
 @Test
    public void testInsert() throws SQLException, ClassNotFoundException {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/company_db",
                "root",
                "root");

        // 3. 获取Statement对象
        Statement stmt = connection.createStatement();

        // 4. 获取结果集
  String username = "zhangsan";
  String password = "345678";
  String balance = "1000";
        int i = stmt.executeUpdate("INSERT INTO tb_user(`username`, `password`, `balance`) " +
                                         "VALUES('" + username + "', '" + password + "', " + balance + ")");

        // 5. 处理结果集
        System.out.println(i);

        // 6. 释放资源
        stmt.close();
        connection.close();
    }
}

删除数据 ​

java
public class JdbcPractice {
 @Test
    public void testDelete() throws SQLException, ClassNotFoundException {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/company_db",
                "root",
                "root");

        // 3. 获取Statement对象
        Statement stmt = connection.createStatement();

        // 4. 获取结果集
  String id = "3";
        int i = stmt.executeUpdate("DELETE FROM tb_user WHERE id=" + id);

        // 5. 处理结果集
        System.out.println(i);

        // 6. 释放资源
        stmt.close();
        connection.close();
    }
}

修改数据 ​

java
public class JdbcPractice {
 @Test
    public void testUpdate() throws SQLException, ClassNotFoundException {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/company_db",
                "root",
                "root");

        // 3. 获取Statement对象
        Statement stmt = connection.createStatement();

        // 4. 获取结果集
  String id = "3";
  String password = "newpassword";
        int i = stmt.executeUpdate("UPDATE tb_user SET password='" + password + "' WHERE id=" + id);

        // 5. 处理结果集
        System.out.println(i);

        // 6. 释放资源
        stmt.close();
        connection.close();
    }
}

查询数据 ​

java
public class JdbcPractice {
    @Test
    public void testQuery() throws ClassNotFoundException, SQLException {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/jdbc_practice",
                "root",
                "root");

        // 3. 获取Statement对象
        Statement stmt = connection.createStatement();

        // 4. 获取结果集
        ResultSet rs = stmt.executeQuery("SELECT * FROM tb_user");

        // 5. 处理结果集
        while (rs.next()) {
            // int id = rs.getInt(1);
            // String username = rs.getString(2);
            // String password = rs.getString(3);

            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            System.out.println("id: " + id + ", username: " + username + ", password: " + password);
        }

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

预编译Statement ​

SQL注入问题 ​

java
public class JdbcPractice {
    @Test
    public void testSQLInjection() throws ClassNotFoundException, SQLException{
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/jdbc_practice",
                "root",
                "root");

        // 3. 获取Statement对象
        Statement stmt = connection.createStatement();

        // 4. 获取结果集
        // String loginUsername = "zhangsan";
        // String loginPassword = "newpassword";
        String loginUsername = "x OR 1=1 #";
        String loginPassword = "...";
        ResultSet rs = stmt.executeQuery("SELECT * FROM tb_user WHERE username='" + loginUsername + "' AND password='" + loginPassword + "'");

        // 5. 处理结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            System.out.println("id: " + id + ", username: " + username + ", password: " + password);
        }

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

预编译Statement解决方案 ​

java
public class JdbcPractice {
    @Test
    public void testPrepareQuery() throws ClassNotFoundException, SQLException{
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/jdbc_practice",
                "root",
                "root");

        // 3. 获取Statement对象
        PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM tb_user WHERE username=? AND password=?");

        // 4. 获取结果集
        // String loginUsername = "zhangsan";
        // String loginPassword = "newpassword";
        String loginUsername = "x OR 1=1 #";
        String loginPassword = "...";
        pstmt.setString(1, loginUsername);
        pstmt.setString(2, loginPassword);
        ResultSet rs = pstmt.executeQuery();

        // 5. 处理结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            System.out.println("id: " + id + ", username: " + username + ", password: " + password);
        }

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

事务 ​

  1. connection.setAutoCommit(false)

设置为手动提交

  1. connection.commit()

提交事务

  1. connection.rollback()

回滚事务

  1. connection.setAutoCommit(true)

设置为自动提交

java
    @Test
    public void testTransaction() throws ClassNotFoundException, SQLException {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 获取Connection对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/jdbc_practice",
                "root",
                "root");

        connection.setAutoCommit(false);

        try {
            // 3. 获取Statement对象
            PreparedStatement pstmt1 = connection.prepareStatement("UPDATE tb_user SET balance=balance-500 WHERE id=1");
            pstmt1.executeQuery();

            PreparedStatement pstmt2 = connection.prepareStatement("UPDATE tb_user SET balance=balance+500 WHERE id=2");
            pstmt2.executeQuery();

            pstmt1.close();
            pstmt2.close();

            connection.commit();
        } catch (Exception e) {
            connection.rollback();
        }

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

学习目标总结 ​

  • 能够理解 JDBC 的概念
  • 能够使用 DriverManager 类
  • 能够使用 Connection 接口
  • 能够使用 Statement 接口
  • 能够使用 ResultSet 接口
  • 能够说出 SQL 注入原因和解决方案
  • 能够通过 PreparedStatement 完成增、删、改、查
  • 能够完成 PreparedStatement 改造登录案例