JDBC的基本使用2

1、maven项目使用JDBC

新建一个 maven JavaSE 项目，往 pom.xml 文件中添加以下依赖：

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.12</version>
</dependency>

依赖添加完成后就可以直接使用 JDBC 连接数据库并操作了：

public class JdbcTest01 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;

        try {
            //1.注册驱动（mysql5之后的驱动jar包可以省略注册驱动的步骤）
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            //3.定义sql语句
            String sql = "update student set name = 'hahaha' where id = 1";
            //4.获取执行sql的对象
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);

            System.out.println(count);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源。为了避免空指针异常，必须先判断是否为null
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }

            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}

上面执行完成后输出结果为 1 。

2、Statement 对象实现增删改查

2.1、statement对象基本介绍

我们可以通过 Connection 对象的 createStatement() 方法来创建一个 Statement 对象，以此来执行 SQL 语句：

Connection conn = DriverManager.getConnection(URL, 用户名, 密码);  //建立连接
Statement stmt = conn.createStatement();  //创建statement对象
stmt.execute(SqlStr);   //执行SQL

当你创建了一个 Statement 对象之后，你可以用它的三个执行方法的任一方法来执行 SQL 语句。

boolean execute(String SQL) : 可以执行任何SQL语句，但是这个语句较少使用。如果第一个结果是一个 ResultSet 对象，则返回的布尔值为 true ，否则都会返回 false 。当你需要使用真正的动态 SQL 时，可以使用这个方法来执行 SQL DDL 语句。
int executeUpdate(String SQL) : 常用该方法来执行DML语句（增删改），也可执行DDL语句（操作数据库和表结构）。它返回的是执行 SQL 语句影响的行的数目。
ResultSet executeQuery(String SQL) : 常用该方法执行DQL语句（查询），它返回一个 ResultSet 对象。

在使用后 statement 对象后我们应该关闭它，通过调用 close() 方法就可以关闭。其实在我们关闭了 Connection 对象后，它也会自动关闭 Statement 对象。但我们应该始终明确关闭 Statement 对象，以确保真正的清除。

Statement stmt = null;
try {
    stmt = conn.createStatement( );
    ...
}
catch (SQLException e) {
    ...
}
finally {
    stmt.close();
}

2.2、statement对象实现DML（增删改）

DML（增删改）语句都可以使用 execute() 或者 executeUpdate() 方法，一般我们可以使用 executeUpdate() 方法，因为该方法能返回 SQL 语句影响的行的数目。

boolean execute(String SQL) : 该方法可以执行任何的SQL语句。如果第一个结果是一个 ResultSet 对象，则返回的布尔值为 true ，否则返回 false 。当你需要使用真正的动态 SQL 时，可以使用这个方法来执行 SQL DDL 语句。
int executeUpdate(String SQL) : 常用该方法来执行DML语句（增删改），也可执行DDL语句（操作数据库和表结构）。它返回的是执行 SQL 语句影响的行的数目。

增删改语句执行方式都一样，只是SQL不一样而已：

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

String insertSql = "insert into student values (null, 'aaa', 22)";  //添加语句，自增主键可以赋值为null，数据库会自己处理
String delSql = "delete from student where name = 'aaa'";           //删除语句
String updateSql = "update student set name = 'newName' where name = 'aaaa'";  //修改语句

Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(updateSql);   //增删改只需直接替换sql语句就行，执行方式都一样
if(count > 0) {
    System.out.println("成功" + count);
}else {
    System.out.println("失败" + count);
}

2.3、statement对象实现DDL（操作数据库和表）

DDL 语句也同样可以使用 executeUpdate() 方法：

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

String insertSql = "alter table student add score varchar(20)";   //增加一列

Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(insertSql);

executeUpdate() 执行 DDL 语句时返回值为 0

2.4、statement对象实现DQL（查询）

查询语句应该使用 executeQuery() 方法，该方法返回一个 ResultSet 对象，我们可以通过遍历该对象来获取到查询到的结果集：

public void selectTest()  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet resultSet = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

        String selectSql = "select * from student";
        stmt = conn.createStatement();

        resultSet = stmt.executeQuery(selectSql);

        while (resultSet.next()){   //循环判断此时是否还有数据
            int id = resultSet.getInt("id");
            String username = resultSet.getString("name");
            int age = resultSet.getInt("age");

            System.out.println(id + "," + username + "," + age);
        }

    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        //释放资源,最后用的最先释放
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

ResultSet 资源也应该要主动释放。

2.4.1、ResultSet 对象

ResultSet 结果集对象，用于封装查询结果。

该对象的常用方法：

boolean resultsetObj.next()：游标向下移动一行，并且判断当前行是否是最后一行的末尾，即是否还有数据，有则返回 true，否则返回 false。游标在开始时指向的是查询到的数据集的第一行的上一行，也就是开始时就必须使用 next() 才有数据。
getXxx(参数）：获取数据。参数可以是列的编号（从1开始），比如 getString(1)，或者是列的名称，如 getString("username")。

游标，类似指针索引。可以理解为，最初一开始时游标指在“列名”上，要取到数据就需要让游标向下移动，移动后就指向了第一行数据，然后就可以把第一行的每一列都取出来，一次只能获取一行中的一列数据。

3、PreparedStatement对象实现增删改查

PreparedStatement 接口扩展了 Statement 接口，它让你用一个常用的 Statement 对象增加几个高级功能，这个 statement 对象可以提供灵活多变的动态参数。

创建 PreparedStatement 对象：

String sqlStr = "Update Employees SET age = ? WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sqlStr);

JDBC 中所有的参数都被用 ? 符号表示，这是已知的参数标记。在执行 SQL 语句之前，你必须赋予每一个参数确切的数值。

setXXX() 方法将值绑定到参数，其中 XXX 表示你希望绑定到输入参数的 Java 数据类型。如果你忘了赋予值，你将收到一个 SQLException。每个参数标记映射它的序号位置，第一标记表示位置 1 ，下一个位置为 2 等等。

String sql = "SELECT * FROM user WHERE login=? AND pass=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, name);
ps.setObject(2, pass);

所有的 Statement 对象的方法都与数据库交互，execute()、executeQuery()、及executeUpdate() 也能被 PreparedStatement 对象引用。然而，这些方法被 SQL 语句修改后是可以输入参数的。

PreparedStatement可以有效防止sql注入，所以生产环境上一定要使用PreparedStatement，而不能使用Statement。

3.1、PreparedStatement对象实现增删改

使用 PreparedStatement 对象来执行 SQL 跟使用 statement 对象的方式差不多。

@Test
public void jdbcTest() {
    System.out.println(1111);

    Connection conn = null;
    PreparedStatement ps = null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_test", "root", "123456");

        //改
        //String updatesql = "update students set name = ? where id = ?";
        //ps = conn.prepareStatement(updatesql);
        //ps.setObject(1, "newName");
        //ps.setInt(2, 1);

        //增
        //String insertSql = "insert into students2 values (?, ?)";
        //ps = conn.prepareStatement(insertSql);
        //ps.setInt(1, 3);
        //ps.setString(2, "lisi");

        //删
        String deleteSql = "delete from students2 where id = ?";
        ps = conn.prepareStatement(deleteSql);
        ps.setInt(1, 3);

        //执行sql。注意，这里无需传递sql作为参数
        int count = ps.executeUpdate();

        System.out.println(count);

    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        //6.释放资源。为了避免空指针异常，必须先判断是否为null
        if(ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

PreparedStatement 对象在使用后也需要关闭，只需简单调用 close() 方法就可以完成这项工作。如果你关闭了 Connection 对象，那么它也会关闭 PreparedStatement 对象。然而，你应该始终明确关闭 PreparedStatement 对象，以确保真正的清除。

4、SQL注入问题

使用Statement拼字符串非常容易引发SQL注入的问题，这是因为SQL参数往往是从方法参数传入的。

假设用户登录的验证方法如下：

User login(String name, String pass) {
    ...
    stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'");
    ...
}

其中，参数name和pass通常都是Web页面输入后由程序接收到的。

如果用户的输入是程序期待的值，就可以拼出正确的SQL。例如：name = "bob"，pass = "1234"：

SELECT * FROM user WHERE login='bob' AND pass='1234'

但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"：

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''

上面的SQL语句执行的时候，根本就不用判断密码是否正确，这样一来，登录就形同虚设。

要避免SQL注入攻击，一个办法是针对所有字符串参数进行转义，但是转义很麻烦，而且需要在任何使用SQL的地方增加转义代码。

还有一个办法就是使用PreparedStatement。使用PreparedStatement可以完全避免SQL注入的问题，因为PreparedStatement始终使用?作为占位符，并且把数据连同SQL本身传给数据库，这样可以保证每次传给数据库的SQL语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存。上述登录SQL如果用PreparedStatement可以改写如下：

User login(String name, String pass) {
    ...
    String sql = "SELECT * FROM user WHERE login=? AND pass=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setObject(1, name);
    ps.setObject(2, pass);
    ...
}

所以，PreparedStatement比Statement更安全，而且更快。

注意：使用Java对数据库进行操作时，必须使用PreparedStatement，严禁任何通过参数拼字符串的代码！

把上面使用Statement的代码改为使用PreparedStatement，如下：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
        ps.setObject(1, "M"); // 注意：索引从1开始
        ps.setObject(2, 3);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                long id = rs.getLong("id");
                long grade = rs.getLong("grade");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
            }
        }
    }
}

使用PreparedStatement和Statement稍有不同，必须首先调用setObject()设置每个占位符?的值，最后获取的仍然是ResultSet对象。另外注意到从结果集读取列时，使用String类型的列名比索引要易读，而且不易出错。

注意到JDBC查询的返回值总是ResultSet，即使我们写这样的聚合查询SELECT SUM(score) FROM ...，也需要按结果集读取：

ResultSet rs = ...
if (rs.next()) {
    double sum = rs.getDouble(1);
}

5、mysql数据类型和java数据类型映射关系

使用JDBC的时候，我们需要在SQL数据类型和Java数据类型之间进行转换。JDBC在java.sql.Types定义了一组常量来表示如何映射SQL数据类型，但是平时我们使用的类型通常也就以下几种：

只有最新的JDBC驱动才支持LocalDate和LocalTime。

详细表格：

6、流式查询

在 Java 中进行数据库数据的流式读取，意味着从数据库中逐行获取数据，每次只在内存中保留当前正在处理的一行数据，减少内存占用，而不是一次性将所有数据加载到内存中，这样可以有效减少内存占用，尤其适用于处理大量数据的场景。

以 MySQL 为例，在流式读取数据时，需要将PreparedStatement 的 ResultSet 获取方式设置为 ResultSet.TYPE_FORWARD_ONLY 和 ResultSet.CONCUR_READ_ONLY，并将 fetchSize 设置为 Integer.MIN_VALUE（对于 MySQL 数据库），以启用流式读取：

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

public class MysqlStreamQueryWithPreparedStatement {
    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/your_database";
        String username = "your_username";
        String password = "your_password";
        // 定义 SQL 查询语句
        String sql = "SELECT * FROM your_table WHERE some_column = ?";

        try (Connection connection = DriverManager.getConnection(url, username, password);
             // 创建 PreparedStatement 对象，设置结果集类型和并发模式
             PreparedStatement preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {

            // 设置流式查询的 fetchSize
            preparedStatement.setFetchSize(Integer.MIN_VALUE);
            // 设置 SQL 参数
            preparedStatement.setString(1, "your_condition_value");
            // 执行查询，获取结果集
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                // 遍历结果集，此时每次只会加载一行数据到内存
                while (resultSet.next()) {
                    // 从结果集中获取数据
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    System.out.println("ID: " + id + ", Name: " + name);
                }
            }
        } catch (Exception e) {
            // 打印异常信息
            e.printStackTrace();
        }
    }
}

ResultSet.TYPE_FORWARD_ONLY

ResultSet.TYPE_FORWARD_ONLY用于指定 ResultSet 对象的游标（Cursor）只能向前移动，也就是只能按顺序从第一行开始依次向后遍历结果集，不能向后滚动到之前已经访问过的行。

流式查询的核心目标是逐行处理数据，避免一次性将大量数据加载到内存中。ResultSet.TYPE_FORWARD_ONLY 规定结果集的游标只能向前移动，数据库驱动程序无需为游标回滚保存之前读取的数据。这样一来，内存中仅需保留当前正在处理的行数据，大大降低了内存使用量。特别是在处理大规模数据集时，这种内存优化至关重要，可有效防止内存溢出错误。

ResultSet.CONCUR_READ_ONLY

ResultSet.CONCUR_READ_ONLY 用于指定 ResultSet 对象是只读的，即不能通过 ResultSet 对象对数据库中的数据进行更新、插入或删除操作。这简化了数据库驱动程序的实现，因为它无需处理复杂的并发控制和数据修改逻辑。对于流式查询，通常只关注数据的读取和处理，不涉及对数据的修改，所以只读模式可以避免不必要的复杂性。

fetchSize

流式查询需要将 fetchSize 设置为 Integer.MIN_VALUE（对于 MySQL 数据库），以启用流式查询。这样可以确保每次只从数据库获取一行数据。非流式查询一般不需要显式设置 fetchSize，数据库驱动会使用默认的获取大小，可能会一次性获取较多的数据到内存中。

mybatis 实现流式查询可参考：https://blog.csdn.net/weixin_52100990/article/details/137210798

6.1、流式查询和非流式查询的代码区别

非流式查询是 JDBC 的默认方式，以 MySQL 为示例代码如下：

String sql = "SELECT * FROM large_table WHERE id > ?";
try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    pstmt.setInt(1, 1000); // 设置参数
    try (ResultSet rs = pstmt.executeQuery()) {
        // 所有数据已加载到内存
        while (rs.next()) {
            // 处理一行数据（但所有数据已加载到内存！）
            System.out.println(rs.getInt("id"));
        }
    }
}

6.2、流式查询和非流式查询对比

特性	流式查询	非流式查询
数据加载方式	逐行从数据库读取（按需加载）	一次性加载所有结果到内存
内存占用	恒定（低内存消耗）	随结果集增大线性增长（高内存消耗）
适用场景	大数据量（如百万级记录）	小数据量
数据库连接占用时间	较长（需保持连接直到结果集遍历完成）	较短（数据加载后即可关闭）
默认行为	需显式配置	JDBC 默认方式
使用场景	大结果集（> 1万行）	小结果集（< 1万行）

APP服务器内存占用方面：传统查询方式往往会一次性把查询结果全部加载到内存里，若查询结果的数据量庞大，会占用大量内存资源，甚至可能引发内存溢出错误。而流式查询逐行处理数据，每次仅在内存中保留当前正在处理的那一行数据，大幅减少了内存的使用量。例如，在处理包含数百万条记录的日志数据时，流式查询可以避免一次性将所有数据加载到内存，使系统在有限的内存资源下也能稳定运行。
数据库服务器负载方面：流式查询减少了数据库服务器向客户端传输大量数据的压力。服务器不需要一次性准备和发送所有查询结果，而是按客户端的需求逐行发送，这有助于减轻服务器的 CPU、内存和网络 I/O 负载，提高数据库服务器的整体性能和稳定性。
响应速度方面：采用流式查询时，客户端无需等待所有数据都返回后才开始处理，在第一行数据返回时就能够立即开始处理。这在处理实时性要求较高的场景中尤为重要，能显著缩短用户等待的时间，提高系统的响应速度。比如在实时数据分析系统中，流式查询可以让分析任务尽快开始，及时为用户提供分析结果。

工作原理对比：

非流式查询：
[APP] <--- 一次性加载 100万行 --- [DB Server]
       (内存爆满！)

流式查询：
[APP] <--- 逐行/分批传输 --- [DB Server]
       (内存恒定，按需加载)

非流式查询一般只适用于小数据量查询，如果涉及大数据量，为避免内存溢出、性能等问题，建议使用流式查询。

流式查询会长时间占用数据库连接，需确保连接池配置合理，如增加超时时间避免超时。不同数据库使用流式查询的代码可能也不一致。

posted @ 2021-01-23 10:52 wenxuehai 阅读(267) 评论(0) 收藏举报

刷新页面返回顶部

提升自己，才有选择的能力和勇气