YashanDB Docs
search-icon Ctrl K
官网首页 立即体验 文档中心 search-icon Language

#处理结果集

ResultSet对象表示数据库查询返回的结果集。应用程序通过ResultSet游标逐行读取查询结果，也可以根据创建Statement时指定的类型和并发模式，使用可滚动或可更新结果集。

本文示例使用如下测试表和数据。实际使用时，请将连接地址、用户名和密码替换为目标环境的配置。

CREATE TABLE jdbc_resultset_customer (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(50),
    credit_limit DECIMAL(10, 2),
    created_date DATE
);

示例数据包含5条客户记录，用于演示结果集遍历、游标定位和行更新。示例程序会自动创建和删除测试表。

为减少重复代码，以下Java示例使用ResultSetTestSupport完成驱动加载、数据库连接、测试表初始化和清理。该类仅用于文档示例，不属于YashanDB JDBC API；实际项目中请替换为应用程序已有的连接和数据准备逻辑。

package quickstart;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Properties;

final class ResultSetTestSupport {
    static final String TABLE = "jdbc_resultset_customer";
    private static final String URL =
            "jdbc:yasdb://192.168.1.2:1688/yasdb";

    private ResultSetTestSupport() {
    }

    static Connection openConnection() throws Exception {
        Class.forName("com.yashandb.jdbc.Driver");
        Properties properties = new Properties();
        properties.setProperty("user", "username");
        properties.setProperty("password", "passwd");
        return DriverManager.getConnection(URL, properties);
    }

    static void prepareTable() throws Exception {
        try (Connection connection = openConnection();
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
            statement.execute(
                    "CREATE TABLE " + TABLE
                            + " (customer_id INTEGER PRIMARY KEY, "
                            + "customer_name VARCHAR(50), "
                            + "credit_limit DECIMAL(10, 2), "
                            + "created_date DATE)");
            for (int id = 1; id <= 5; id++) {
                statement.executeUpdate(
                        "INSERT INTO " + TABLE + " VALUES ("
                                + id + ", 'Customer " + id + "', "
                                + (id * 1000) + ".00, "
                                + "DATE '2026-01-0" + id + "')");
            }
        }
    }

    static void dropTable() throws Exception {
        try (Connection connection = openConnection();
             Statement statement = connection.createStatement()) {
            statement.execute("DROP TABLE IF EXISTS " + TABLE);
        }
    }
}

# 创建和读取ResultSet

# 创建ResultSet

调用Statement.executeQuery()执行查询即可创建ResultSet。ResultSet、Statement和Connection都实现了AutoCloseable，可以使用try-with-resources按资源依赖关系自动关闭。

以下示例查询指定客户，并通过next()将游标移动到第一行后读取列值。

package quickstart;

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

public class ResultSetCreationExample {
    public static void main(String[] args) throws Exception {
        ResultSetTestSupport.prepareTable();
        try (Connection connection = ResultSetTestSupport.openConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(
                     "SELECT customer_id, customer_name FROM "
                             + ResultSetTestSupport.TABLE
                             + " WHERE customer_id = 1")) {
            if (resultSet.next()) {
                System.out.println(
                        "Customer: " + resultSet.getInt("customer_id")
                                + ", "
                                + resultSet.getString("customer_name"));
            }
        } finally {
            ResultSetTestSupport.dropTable();
        }
    }
}

正常情况下，程序输出如下：

Customer: 1, Customer 1

# 遍历结果集

next()每调用一次，游标向前移动一行。当没有更多数据时返回false。列可以通过从1开始的列索引或列标签读取。

以下示例使用列索引读取客户编号，使用列标签读取客户名称，并遍历全部5条记录。

package quickstart;

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

public class ResultSetTraversalExample {
    public static void main(String[] args) throws Exception {
        ResultSetTestSupport.prepareTable();
        try (Connection connection = ResultSetTestSupport.openConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(
                     "SELECT customer_id, customer_name FROM "
                             + ResultSetTestSupport.TABLE
                             + " ORDER BY customer_id")) {
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString("customer_name");
                System.out.println("Customer: " + id + ", " + name);
            }
        } finally {
            ResultSetTestSupport.dropTable();
        }
    }
}

正常情况下，程序输出如下：

Customer: 1, Customer 1
Customer: 2, Customer 2
Customer: 3, Customer 3
Customer: 4, Customer 4
Customer: 5, Customer 5

# 检索不同类型的数据

ResultSet提供了与数据库类型对应的getXxx()方法。读取数据时，应选择与列类型匹配的方法；例如使用getInt()读取整数、getString()读取字符、getBigDecimal()读取精确数值、getDate()读取日期。

以下示例读取客户编号、名称、额度和创建日期四种类型的列。

package quickstart;

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

public class ResultSetDataTypesExample {
    public static void main(String[] args) throws Exception {
        ResultSetTestSupport.prepareTable();
        try (Connection connection = ResultSetTestSupport.openConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(
                     "SELECT customer_id, customer_name, credit_limit, "
                             + "created_date FROM "
                             + ResultSetTestSupport.TABLE
                             + " WHERE customer_id = 1")) {
            if (resultSet.next()) {
                int id = resultSet.getInt("customer_id");
                String name = resultSet.getString("customer_name");
                java.math.BigDecimal creditLimit =
                        resultSet.getBigDecimal("credit_limit");
                Date createdDate = resultSet.getDate("created_date");
                System.out.println(
                        id + ", " + name + ", " + creditLimit
                                + ", " + createdDate);
            }
        } finally {
            ResultSetTestSupport.dropTable();
        }
    }
}

正常情况下，程序输出如下：

1, Customer 1, 1000, 2026-01-01

# 可滚动结果集

创建Statement时指定ResultSet.TYPE_SCROLL_INSENSITIVE，可以使用first()、last()、absolute()、relative()和previous()等方法移动游标。ResultSet.CONCUR_READ_ONLY表示结果集只读。

以下示例演示从结果集末尾、开头和指定行之间移动游标。示例使用5条测试数据，因此absolute(5)定位到第五行。

package quickstart;

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

public class ScrollableResultSetExample {
    public static void main(String[] args) throws Exception {
        ResultSetTestSupport.prepareTable();
        try (Connection connection = ResultSetTestSupport.openConnection();
             Statement statement = connection.createStatement(
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
             ResultSet resultSet = statement.executeQuery(
                     "SELECT customer_id, customer_name FROM "
                             + ResultSetTestSupport.TABLE
                             + " ORDER BY customer_id")) {
            if (resultSet.last()) {
                System.out.println("Total rows: " + resultSet.getRow());
            }
            if (resultSet.first()) {
                System.out.println(
                        "First customer: "
                                + resultSet.getString("customer_name"));
            }
            if (resultSet.absolute(5)) {
                System.out.println(
                        "Fifth customer: "
                                + resultSet.getString("customer_name"));
            }
            if (resultSet.relative(-2)) {
                System.out.println(
                        "Third customer: "
                                + resultSet.getString("customer_name"));
            }
            if (resultSet.previous()) {
                System.out.println(
                        "Previous customer: "
                                + resultSet.getString("customer_name"));
            }
        } finally {
            ResultSetTestSupport.dropTable();
        }
    }
}

正常情况下，程序输出如下：

Total rows: 5
First customer: Customer 1
Fifth customer: Customer 5
Third customer: Customer 3
Previous customer: Customer 2

# 可更新结果集

创建Statement时指定ResultSet.CONCUR_UPDATABLE，可以通过updateXxx()写入当前行的临时值，再调用updateRow()将修改提交到数据库。应用程序仍需要根据事务配置调用commit()或rollback()。

以下示例将编号为1的客户名称更新为Updated customer。执行更新前关闭自动提交，确认结果后提交事务；发生异常时回滚。

package quickstart;

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

public class UpdatableResultSetExample {
    public static void main(String[] args) throws Exception {
        ResultSetTestSupport.prepareTable();
        try (Connection connection = ResultSetTestSupport.openConnection()) {
            connection.setAutoCommit(false);
            try (Statement statement = connection.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
                 ResultSet resultSet = statement.executeQuery(
                         "SELECT customer_id, customer_name FROM "
                                 + ResultSetTestSupport.TABLE)) {
                while (resultSet.next()) {
                    if (resultSet.getInt("customer_id") == 1) {
                        resultSet.updateString(
                                "customer_name", "Updated customer");
                        resultSet.updateRow();
                        break;
                    }
                }
                connection.commit();
            } catch (Exception exception) {
                connection.rollback();
                throw exception;
            }

            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(
                         "SELECT customer_name FROM "
                                 + ResultSetTestSupport.TABLE
                                 + " WHERE customer_id = 1")) {
                resultSet.next();
                System.out.println(
                        "Updated name: " + resultSet.getString(1));
            }
        } finally {
            ResultSetTestSupport.dropTable();
        }
    }
}

正常情况下，程序输出如下：

Updated name: Updated customer

# ResultSet元数据

ResultSetMetaData用于读取结果集的结构信息，适合列数量或列结构在运行时才确定的场景。常用方法包括getColumnCount()、getColumnLabel()和getColumnTypeName()。

以下示例读取查询结果的列数、列标签和数据库类型名称。

package quickstart;

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

public class ResultSetMetaDataExample {
    public static void main(String[] args) throws Exception {
        ResultSetTestSupport.prepareTable();
        try (Connection connection = ResultSetTestSupport.openConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(
                     "SELECT customer_id, customer_name, credit_limit FROM "
                             + ResultSetTestSupport.TABLE)) {
            ResultSetMetaData metadata = resultSet.getMetaData();
            System.out.println("Column count: " + metadata.getColumnCount());
            for (int index = 1;
                 index <= metadata.getColumnCount();
                 index++) {
                System.out.println(
                        metadata.getColumnLabel(index)
                                + ": "
                                + metadata.getColumnTypeName(index));
            }
        } finally {
            ResultSetTestSupport.dropTable();
        }
    }
}

正常情况下，程序输出如下：

Column count: 3
CUSTOMER_ID: INTEGER
CUSTOMER_NAME: VARCHAR
CREDIT_LIMIT: NUMBER

# 资源管理

ResultSet使用完毕后应及时关闭，同时关闭创建它的Statement和数据库连接。推荐使用try-with-resources，关闭顺序由Java运行时按声明顺序的逆序处理，避免结果集、语句或连接长期占用资源。

以下示例遍历结果集后退出try代码块，自动关闭ResultSet、Statement和Connection。

package quickstart;

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

public class ResourceManagementExample {
    public static void main(String[] args) throws Exception {
        ResultSetTestSupport.prepareTable();
        try {
            try (Connection connection =
                         ResultSetTestSupport.openConnection();
                 Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(
                         "SELECT customer_name FROM "
                                 + ResultSetTestSupport.TABLE
                                 + " ORDER BY customer_id")) {
                while (resultSet.next()) {
                    System.out.println(
                            "Customer: " + resultSet.getString(1));
                }
            }
        } finally {
            ResultSetTestSupport.dropTable();
        }
    }
}

正常情况下，程序输出如下：

Customer: Customer 1
Customer: Customer 2
Customer: Customer 3
Customer: Customer 4
Customer: Customer 5
pdf-btn 下载文档 copy-btn 复制链接
edit-icon
反馈
coperate-icon
coperate
合作
communication-icon
communicate
交流