Java ResultSet教程

2 年 ago
新, 韵
8 minutes

Java ResultSet接口是java.sql包的一部分。它是JDBC框架的核心组件之一。ResultSet对象用于访问从关系数据库检索的查询结果。

ResultSet维护着一个指向查询结果中单个行的光标/指针。通过ResultSet提供的导航和获取方法，我们可以逐个迭代和访问数据库记录。ResultSet还可以用于更新数据。

Java 数据集层次结构

Java ResultSet Class Hierarchy

上图展示了JDBC框架中ResultSet的位置。通过使用Statement、PreparedStatement或CallableStatement执行SQL查询可以获得ResultSet。

AutoCloseable 和 Wrapper 是 ResultSet 的超级接口。现在我们将看到如何在我们的 Java 程序中使用 ResultSet。

结果集示例

我们将使用MySQL进行示例。使用下面的数据库脚本创建一个数据库和表，以及一些记录。

create database empdb;

use empdb;

create table tblemployee (empid integer primary key, firstname varchar(32), lastname varchar(32), dob date);

insert into tblemployee values  (1, 'Mike', 'Davis',' 1998-11-11');
insert into tblemployee values  (2, 'Josh', 'Martin', '1988-10-22');
insert into tblemployee values  (3, 'Ricky', 'Smith', '1999-05-11');

让我们来看一下下面的示例程序，从表中获取记录并在控制台上打印出来。请确保在项目类路径中有MySQL JDBC驱动程序。

package com.scdev.examples;

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

/**
 * Java Resultset Example of Retrieving records.
 * 
 * @author scdev
 *
 */

public class ResultSetDemo {

	public static void main(String[] args) {
		String query = "select empid, firstname, lastname, dob from tblemployee";
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb", "root", "root");
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				Integer empId = rs.getInt(1);
				String firstName = rs.getString(2);
				String lastName = rs.getString(3);
				Date dob = rs.getDate(4);
				System.out.println("empId:" + empId);
				System.out.println("firstName:" + firstName);
				System.out.println("lastName:" + lastName);
				System.out.println("dob:" + dob);
				System.out.println("");
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (Exception e) {}
		}
	}
}

产出。

empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11

empId:2
firstName:Josh
lastName:Martin
dob:1988-10-22

empId:3
firstName:Ricky
lastName:Smith
dob:1999-05-11

解释：

  • ResultSet is obtained by calling the executeQuery method on Statement instance. Initially, the cursor of ResultSet points to the position before the first row.
  • The method next of ResultSet moves the cursor to the next row. It returns true if there is further row otherwise it returns false.
  • We can obtain data from ResultSet using getter methods provided by it. e.g.  getInt(),  getString(),  getDate()
  • All the getter methods have two variants. 1st variant takes column index as Parameter and 2nd variant accepts column name as Parameter.
  • Finally, we need to call close method on ResultSet instance so that all resources are cleaned up properly.

结果集类型和并发性

在创建 Statement、PreparedStatement 或 CallableStatement 实例时，我们可以指定 ResultSet 的类型和并发方式。

创建语句对象时，指定结果集类型和结果集并发性的方法是 statement.createStatement(int resultSetType, int resultSetConcurrency)。

结果集类型

只能向前（ResultSet.TYPE_FORWARD_ONLY）

创建Statement、PreparedStatement或CallableStatement实例时，我们可以获取这种类型的ResultSet。这种ResultSet实例只能从第一行向最后一行方向移动。通过调用next()方法，可以将ResultSet向前移动一行。

Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

2）不敏感滚动（ResultSet.TYPE_SCROLL_INSENSITIVE）

不受滚动ResultSet在前向和后向方向上都可以滚动。还可以通过调用absolute()方法滚动到绝对位置。但它对数据更改不敏感。它只在查询执行并获得ResultSet时具有数据。在获取数据后对数据所做的更改不会反映在其中。

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  		ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

3) 滚动敏感（ResultSet.TYPE_SCROLL_SENSITIVE）

滚动敏感的结果集可以在向前和向后的方向上滚动。还可以通过调用absolute()方法将其滚动到绝对位置。但它对数据变化敏感。在打开时，它将反映对数据所做的更改。

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,  		ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

结果集并发性

只读（ResultSet.CONCUR_READ_ONLY）

它是默认的并发模型。我们只能对ResultSet实例执行只读操作，不允许进行更新操作。

可更新的 (ResultSet.CONCUR_UPDATABLE)

在這個情況下，我們可以對ResultSet實例進行更新操作。

结果集方法

我们可以将ResultSet的方法分为以下几个类别。

  • Navigational Methods
  • Getter/Reader Methods
  • Setter/Updater Methods
  • Miscellaneous Methods – close() and getMetaData()

1. 结果集导航方法

  • boolean absolute(int row) throws SQLException**:** This method moves ResultSet cursor to the specified row and returns true if the operation is successful.
  • void afterLast() throws SQLException**:** This method moves ResultSet cursor to the position after the last row.
  • void beforeFirst() throws SQLException**:** This method moves ResultSet cursor to the position before the first row.
  • boolean first() throws SQLException: This method moves ResultSet cursor to the first row.
  • boolean last() throws SQLException: This method moves ResultSet cursor to the last row.
  • boolean next() throws SQLException: This method moves ResultSet cursor to the next row.
  • boolean previous() throws SQLException: This method moves ResultSet cursor to the previous row.
package com.scdev.examples;
import java.sql.*;

/**
 * Java Resultset Example using navigational methods.
 * 
 * @author scdev
 *
 */
public class ResultSetDemo {

	public static void main(String[] args) {
		String query = "select empid, firstname, lastname, dob from tblemployee";
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb", "root", "root");
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stmt.executeQuery(query);
			System.out.println("All the rows of table=>");
			while (rs.next()) { 
				// Go to next row by calling next() method
				displayData(rs);
			}
			System.out.println("Now go directly to 2nd row=>");
			rs.absolute(2); // Go directly to 2nd row
			displayData(rs);
			System.out.println("Now go to Previous row=>");
			rs.previous(); 
			// Go to 1st row which is previous of 2nd row
			displayData(rs);
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public static void displayData(ResultSet rs) throws SQLException {
		System.out.println("empId:" + rs.getInt(1));
		System.out.println("firstName:" + rs.getString(2));
		System.out.println("lastName:" + rs.getString(3));
		System.out.println("dob:" + rs.getDate(4));
		System.out.println("");
	}
}

输出结果：

All the rows of table=>
empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11

empId:2
firstName:Josh
lastName:Martin
dob:1988-10-22

empId:3
firstName:Ricky
lastName:Smith
dob:1999-05-11

Now go directly to 2nd row=>
empId:2
firstName:Josh
lastName:Martin
dob:1988-10-22

Now go to Previous row=>
empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11

2. 结果集获取/读取方法

  • int getInt(int columnIndex) throws SQLException: This method returns value of specified columnIndex as int.
  • long getLong(int columnIndex) throws SQLException: This method returns value of specified columnIndex as long
  • String getString(int columnIndex) throws SQLException: This method returns value of specified columnIndex as String
  • java.sql.Date getDate(int columnIndex) throws SQLException: This method returns value of specified columnIndex as java.sql.Date
  • int getInt(String columnLabel) throws SQLException: This method returns value of specified column name as int.
  • long getLong(String columnLabel) throws SQLException: This method returns value of specified column name as long.
  • String getString(String columnLabel) throws SQLException: This method returns the value of the specified column name as String.
  • java.sql.Date getDate(String columnLabel) throws SQLException: This method returns the value of the specified column name as java.sql.Date.
  • ResultSet contains getter methods that return other primitive datatypes like boolean, float and double. It also has methods to obtain array and binary data from the database.

3. 结果集设置/更新方法

  • void updateInt(int columnIndex, int x) throws SQLException: This method updates the value of specified column of current row with int value.
  • void updateLong(int columnIndex, long x) throws SQLException: This method updates the value of the specified column of the current row with long value.
  • void updateString(int columnIndex, String x) throws SQLException: This method updates the value of the specified column of the current row with a String value.
  • void updateDate(int columnIndex, java.sql.Date x) throws SQLException: This method updates the value of specified column of current row with java.sql.Date value.
  • void updateInt(String columnLabel, int x) throws SQLException: This method updates the value of the specified column label of the current row with int value.
  • void updateLong(String columnLabel, long x) throws SQLException: This method updates the value of the specified column label of the current row with long value.
  • void updateString(String columnLabel, String x) throws SQLException: This method updates the value of the specified column label of the current row with a String value.
  • void updateDate(String columnLabel, java.sql.Date x) throws SQLException: This method updates the value of specified columnLabel of current row with java.sql.Date value.

注意：Setter / Updater 方法不直接更新数据库值。只有在调用 insertRow 或 updateRow 方法后，数据库的值才会被插入/更新。

package com.scdev.examples;
import java.sql.*;

/**
 * Java Resultset Example using updater methods.
 * 
 * @author scdev
 *
 */

public class ResultSetUpdateDemo {

	public static void main(String[] args) {
		String query = "select empid, firstname, lastname, dob from tblemployee";
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb", "root", "root");
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stmt.executeQuery(query);
			System.out.println("Now go directly to 2nd row for Update");
			if (rs.absolute(2)) { 
				// Go directly to 2nd row
				System.out.println("Existing Name:" + rs.getString("firstName"));
				rs.updateString("firstname", "Tyson");
				rs.updateRow();
			}
			rs.beforeFirst(); // go to start
			System.out.println("All the rows of table=>");
			while (rs.next()) { 
			// Go to next row by calling next() method
				displayData(rs);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public static void displayData(ResultSet rs) throws SQLException {
		System.out.println("empId:" + rs.getInt(1));
		System.out.println("firstName:" + rs.getString(2));
		System.out.println("lastName:" + rs.getString(3));
		System.out.println("dob:" + rs.getDate(4));
		System.out.println("");
	}
}

输出：请以母语中文进行改写，只需要一种选项：
结果：

Now go directly to 2nd row for Update
Existing Name:Josh
All the rows of table=>
empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11

empId:2
firstName:Tyson
lastName:Martin
dob:1988-10-22

empId:3
firstName:Ricky
lastName:Smith
dob:1999-05-11

4. 杂项结果集方法

  • void close() throws SQLException**:** This method frees up resources associated with ResultSet Instance. It must be called otherwise it will result in resource leakage.
  • ResultSetMetaData getMetaData() throws SQLException: This method returns ResultSetMetaData instance. It gives information about the type and property of columns of the query output.

参考资料：Java文档

发表回复 0

Your email address will not be published. Required fields are marked *