03_JDBC

数据库的访问流程

JDBC

• Java Database Connection，Java数据库连接。其实就是在Java中，帮助我们去连接数据库的一个“东西”。
• 具体指的就是 Java的一套标准的连接数据库的接口。
• 标准的接口具体指：java.sql、javax.sql（rt.jar内部的）

---

JDBC实现流程

1. 新建项目
2. 下载jar包，下载网址：https://mvnrepository.com/
3. 把下载好的jar包拷贝到项目中，再右键jar包选择add as library这个选项
4. 写代码
   • 1. 注册驱动
     • DriverManager.registerDriver(new Driver());，这里的Driver()一定是mysql驱动包里面的。
   • 2. 获取连接
     • 传三个参数：url、username、password
   • 3. 获取statement对象，用它来执行SQL
   • 4. 执行SQL
   • 5. 解析结果
   • 6. 断开连接

public class Demo {
    public static void main(String[] args) throws SQLException {
        // 1. 注册驱动
        DriverManager.registerDriver(new Driver());

        // 2.获取连接
        /*
        url包括：
        1. 协议
        2. 域名/ip+端口
        3. 服务器内部路径
        4. 参数
         */
        String url = "jdbc:mysql://localhost:3306/test_20240301?
        useSSL=false&characterEncoding=utf8";
        // 如果要中文编码，必须要设置:characterEncoding=utf8
        // useSSL=false用来解决warning问题
        String username = "root";
        String password = "123456";

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

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

        // 4. 执行SQL
        // 增删改都是在update方法里面
        int affectedRows = statement.executeUpdate("insert into t_staff 
        values (126, 'Jack', '教授', '数据挖掘')");

        // 5. 解析结果
        System.out.println(affectedRows);

        // 6. 断开连接
        statement.close();
        connection.close();

    }
}

---

使用JDBC进行增删改查

增

删

改

增、删、改都是一样的，都是使用 statement.executeUpdate(String sql) 来执行SQL语句，返回的结果也是一样的，都是影响的行数。

eg：

// 增
statement.executeUpdate("insert into student values (1, 'zs', 25)");

// 删
statement.executeUpdate("delete from student where id = 1");

// 改
statement.executeUpdate("update student set name = 'ls' where id = 1");

查

通过statment.executeQuery()方法

eg：

public class Demo2 {
    public static void main(String[] args) throws SQLException {
        // 1.注册驱动
        DriverManager.registerDriver(new Driver());

        // 2. 建立连接
        String url = "jdbc:mysql://localhost:3306" +
                "/test_20240301?characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, username, password);

        // 3. 获取statement
        Statement statement = connection.createStatement();

        // 4. 执行SQL
        // 查询方法 executeQuery
        ResultSet resultSet = statement.executeQuery("select * from t_staff");

        // 刚刚经过的有没有元素
        boolean next = resultSet.next();

        // 现在通过resultSet对象获取的就是刚刚经过的那一行的数据
        int stfid = resultSet.getInt("stfid");
        String name = resultSet.getString("name");
        String title = resultSet.getString("title");
        String direction = resultSet.getString("direction");

        System.out.println(stfid + " -- " + name + " -- " + title + " -- " + direction);

        // 想获取resultSet里面的所有数据
        while(resultSet.next()){
            int stfid1 = resultSet.getInt("stfid");
            String name1 = resultSet.getString("name");
            String title1 = resultSet.getString("title");
            String direction1 = resultSet.getString("direction");

            System.out.println(stfid1 + " -- " + name1 
            + " -- " + title1 + " -- " + direction1);

        }

        // 5. 解析结果

        // 6. 关闭连接
        statement.close();
        connection.close();

或者：

public class Demo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Connection connection = JdbcUtil.getConnection();

        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery("select * from t_staff");

        // 解析结果，用到了getMetaData
        ResultSetMetaData metaData = resultSet.getMetaData();

        // 可以从元数据里面拿到 返回多少列
        int columnCount = metaData.getColumnCount();

        List<String> columns = new ArrayList<>();

        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);

            columns.add(columnName);
        }
        System.out.println(columns);

        while (resultSet.next()) {
            // 数据库里的数据从1开始
            List<String> list = new ArrayList<>();

            for (int i = 1; i <= columnCount; i++) {
                String value = resultSet.getString(i);
                list.add(value);
            }
            System.out.println(list);
        }

        JdbcUtil.closeConnection(connection, statement, resultSet);
    }
}


--->

[stfid, name, title, direction]
[123, 邹华, 教授, 机器识别]
[124, 李克, 讲师, 机器识别]
[125, 王益, 讲师, 人工智能]
[126, 李华, 教授, 数据挖掘]

---

重要的API

DriverManager

驱动管理器。可以帮助我们管理驱动，获取连接

// 注册驱动
DriverManager.registerDriver(new Driver);


// 获取连接
// 获取到的连接对象实际上是 JDBC4Connection 对象
Connection conn = DriverManager.getConnection(String url,String username,String password);

Connection

指代连接对象。
在JDBC中是一个接口，在MySQL运行过程中，实际上实现类是 com.mysql.jdbc.JDBC4Connection 对象。

// 获取statement
Statement stat = connection.createStatement();

// 关闭连接
connection.close();


// 事务相关的API
connection.commit();
connection.rollback();
connection.setAutoCommit(false);

Statement

statement对象其实就是用来去执行SQL语句，并且返回这个SQL语句产生的结果集。
实际上我们在使用的时候，其实是用的Statement接口的实现类 com.mysql.jdbc.StatementImpl

// 增删改的方法
statement.executeUpdate("sql语句");

// 查询方法
statement.executeQuery("sql语句");

// execute方法会返回一个boolean值，boolean值其实是代表了是查询还是增删改
// 如果为true，代表是查询，则可以通过statement.getResultSet()来获取查询的结果
// 如果为false，代表是增删改，则可以通过statement.getUpdateCount()来获取影响的行数
statement.execute("sql语句");

eg:
// 执行sql语句
Boolean ret = statement.execute(String sql);

// 如果 ret == true，那么说明执行的是查询语句
// 如果 ret == false，那么说明执行的是增删改语句

获取影响的行数: statement.getUpdateCount();
int affactedRows = statement.getUpdateCount();
System.out.println(affactedRows);

获取返回的结果集：statement.getResultSet();

ResultSet

• 表示查询的结果集
• 在查询的结果集中，有一个游标，游标可以移动，移动的时候会扫描一些行，那么对于这些扫描到的行，我们就可以获取对应的列的值

// 移动游标方法

// 向下移动
Boolean ret = resultSet.next();

// 向上移动
Boolean ret = resultSet.previous();

// 定位到第一行之前
resultSet.beforeFirst();

// 定义到最后一行之后
resultSet.afterLast();


// 获取值的方法
resultSet.getInt(String columnName);
resultSet.getString(String columnName);
resultSet.getDate(String columnName);

JDBC实现流程的优化

• 注册驱动的方式抽取成一个方法
  • 之前是强依赖MySQL驱动包
  • 现在换成反射的方式，只有实际运行的时候才需要

JdbcUtils.java

public class JdbcUtil {

    static String url;
    static String username;
    static String password;

    static {
        Properties properties = new Properties();

        try {
            properties.load(new FileInputStream("jdbc.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
    }

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        // 1.注册驱动
        // 用到mysql包中的类，不要直接依赖mysql的包
        // DriverManager.registerDriver(new Driver());
        Class.forName("com.mysql.jdbc.Driver");

        // 2.获取连接
        return DriverManager.getConnection(url, username, password);
    }
}

• 获取连接的方式换成一个properties文件
  • properties文件不要加" "

url=jdbc:mysql://localhost:3306/test_20240301?useSSL=false&characterEncoding=utf8
username=root
password=123456

• main函数

public class Demo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        Connection connection = JdbcUtil.getConnection();

        // 3. 获取statement
        Statement statement = connection.createStatement();

        // 4. 执行SQL
        // 查询方法 executeQuery
        boolean execute = statement.execute("update t_staff set name = '李华' 
        where stfid = 126");


        // 5. 解析结果
        System.out.println("execute = " + execute);

        // 6. 关闭连接
        statement.close();
        connection.close();
    }
}

但是有bug，不知道怎么解决

优化的好处：

1. 注册驱动的时候，改成了反射，运行的时候才需要
2. 把获取连接，抽成了一个工具类
3. 数据库的配置信息，写成了配置文件
4. 优化了关闭连接的方法

---

数据库注入问题

产生的原因：
因为SQL语句是通过字符串拼接的，这个时候用户可能输入一些字符，这些字符中包含有SQL语句中的关键字，那么通过字符串拼接SQL语句之后，可能会改变SQL语句的格式，进而引发安全性的问题
eg：select * from user where name = 'xxx' and password = 'xxx' or '1=1'

根本的原因：MySQL把用户输入的参数当做关键字来解析了

解决SQL注入：最关键是把用户输入的东西当作一个纯字符串

预编译：

• 首先先写SQL的架子，预编译的过程，我才会把给的SQL当SQL关键字解析
• 设置参数，这个时候设置的参数一律当普通字符串处理
• 这个时候可以防止SQL注入

eg：

public class Demo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        boolean loginSuccess = login("admin", "admin");

        if(loginSuccess){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
    }

    private static boolean login(String username, String password) 
    throws SQLException, ClassNotFoundException {

        Connection connection = JdbcUtil.getConnection();

        Statement statement = connection.createStatement();

        // 要用到预编译
        // 预编译：就是首先把SQL的架子写好
        // 后面填充的数据一律按普通字符串处理

        // 使用 ? 作为占位符，预编译结束后，再填充参数
        PreparedStatement preparedStatement = 
        connection.prepareStatement("select * from user where name = ? and password = ?");

        // 对preparedStatement设置参数
        // 把index为1的username值填到第一个?
        // 把index为2的password值填到第二个?
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);

        ResultSet resultSet = preparedStatement.executeQuery();

        if(resultSet.next()){
            return true;
        }else{
            return false;
        }
    }
}

普通Statement的执行流程：

prepareStatement的执行流程：

---

批处理

是批量的处理SQL语句，典型的业务场景就是一次插入大量的数据

for循环逐条插入

发送SQL10次，编译SQL10次，执行10次

public void forInsert() throws SQLException {
    Statement statement = connection.createStatement();

    for (int i = 0; i < 10; i++) {
        // "insert into t values ("+ i + ",'for')"这里是字符串的拼接
        statement.executeUpdate("insert into t values ("+ i + ",'for')");
    }
    statement.close();
}

statement批处理

• 发送SQL1次，编译SQL10次，执行10次
• 先通过addBatch()往集合里面插入，等到executeBatch()时候再一起提交

public void statementInsert() throws SQLException {
    Statement statement = connection.createStatement();

    for (int i = 10; i < 20; i++) {
        String sql = "insert into t values ("+i+",'batchUseStatement')";

        // statement的addBatch是先把sql存起来
        statement.addBatch(sql);
    }
    statement.executeBatch();

    statement.close();
}

preparedStatement批处理

• 需要在数据库的url后面加上配置：rewriteBatchedStatements=true，表示开启批处理
  • 因为开了这个配置会把SQL转换一下，时间会显著加快

eg：

insert into t values(1, 'pre');
insert into t values(2, 'pre');
insert into t values(3, 'pre');

---->
会把这个SQL转换一下
insert into t values(1, 'pre'),(2, 'pre'),(3, 'pre');

• 发送SQL1次，编译SQL1次，执行10次
• 先给preparedStatement设置值，存起来，最后再一次性提交

// 使用prepareStatement 的批处理方法
    public void prepareStatementBatch() throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement
        ("insert into t values (?,'pre')");

        for (int i = 20; i < 30; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.addBatch();
        }

        preparedStatement.executeBatch();
        
        preparedStatement.close();
    }

假如批处理N条数据的对比图

	通信次数	编译次数	执行次数	时间
for循环	n	n	n	最长
Statement	1	n	n	次之
PreparedStatement	2	1	n	最短

---

数据库的事务

• 数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。
• 事务由事务开始与事务结束之间执行的全部数据库操作组成。
• 事务就是要保证一组数据库操作，要么全部成功，要么全部失败
• 应用：转账

事务的步骤

1. 开启事务
2. 提交事务
3. 回滚事务

事务的API

开启事务:
connection.setAutoCommit(false);


提交事务:
connection.commit();


回滚事务:
connection.rollback();

如果用cmd命令行操作：

# 开始事务
start transaction;

# 提交事务
commit;

# 回滚事务
rollback;

eg：（演示转账的操作）

• 1. 需要从转账方扣钱 update account set money = money - ? where name = ? and money > ?
  • 扣转账方的钱，并且保证钱一定是够的
• 2. 给转入方加钱 update account set money = money + ? where name = ?

public class Demo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        transfer("zs", "ls", 1000);
    }

    private static void transfer(String fromName, String toName, int money)
            throws SQLException, ClassNotFoundException {
        Connection connection = JdbcUtil.getConnection();

        // 开启事务
        connection.setAutoCommit(false);
        try {
            // 建立一个account表
            // 扣A的钱
            PreparedStatement preparedStatement = connection.prepareStatement
                    ("update account set money = money - ? " +
                            "where name = ? and money > ?");

            preparedStatement.setInt(1, money);
            preparedStatement.setString(2, fromName);
            preparedStatement.setInt(3, money);

            int affactRows1 = preparedStatement.executeUpdate();
            if(affactRows1 != 1){
                // 没找到人
                throw new RuntimeException
                ("fromName not found people or money is not enough." + affactRows1);
            }

            // 给B加钱
            PreparedStatement preparedStatement1 = connection.prepareStatement
                    ("update account set money = money + ? " +
                            "where name = ? ");

            preparedStatement1.setInt(1, money);
            preparedStatement1.setString(2, toName);

            int affactRows2 = preparedStatement1.executeUpdate();
            if(affactRows2 != 1){
                // 没找到人
                throw new RuntimeException
                ("fromName not found people or money is not enough." + affactRows2);
            }

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

            // 回滚
            connection.rollback();

            e.printStackTrace();
        }
    }
}

事务的特性

• ACID

• 原子性（Atomicity）

  • 事务是一个不可分割的操作单元，事务中的操作要么就都成功，要么就都不成功。

• 一致性（Consistency）

  • 事务必须使数据库从一个一致性状态到另外一个一致性状态。

  • 在转账案例中，一致性是指在转账前和转账后，（无论怎么转账），钱的总金额是前后一致的，不变的

• 隔离性（Isolation）

  • 事务与事务之间是互相隔离的，互不影响的。

  • 数据库有为隔离性设置不同的隔离级别。不同的隔离级别对于隔离性的影响是不一样的。

• 持久性（Durability）

  • 一个事务一旦生效，那么对数据库的改变是永久的，不可逆转的。意思就是提交了事务之后，就已经对数据库产生的变化，那么后续再回滚就回滚不了了。

事务的隔离级别

当数据库有多个事务同时执行的时候，可能会出现问题：

• 脏读
  • 一个事务读取到了另外一个事务没有提交的数据。
    
• 不可重复读

  • 在同一个事务中，读取同一个数据，前后读取的数据不一致。

  • 通常指的是，在一个事务中，读取到了另外一个事务已经提交的数据。
    

• 幻读

  • 指在同一个事务中，读取同一个表数据，前后读取的数量不一致。

  • 通常指的是，在一个事务，读取到了另外一个事务插入或者删除的数据。
    

事务的隔离级别：

• 读未提交

  • 一个事务还没提交时，它做的变更就能被别的事务看到，会产生脏读。
  • 

• 读已提交

  • 一个事务提交之后，它做的变更才会被其他事务看到。
  • 可以避免脏读，但是没有避免不可重复读
  • 

• 可重复读

  • 这个是MySQL默认的隔离级别：可重复读
  • 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
  • 

• 串行化

  • 对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
  • 都可以避免
  • 但是效率太低，工作中的使用率低
  • 

eg：

演示隔离级别

直接在cmd命令行中演示

一些隔离级别的API：

# 获取当前数据库的隔离级别
select @@transaction_isolation;
select @@tx_isolation;

# 设置隔离级别
set global transaction isolation level 隔离级别;

隔离级别：
read uncommitted;
read committed;
repeatable read
serializable;

# 注意。设置了隔离级别。必须要重新启动一下客户端，才能生效。

---