Java ResultSet教程
Java ResultSet接口是java.sql包的一部分。它是JDBC框架的核心组件之一。ResultSet对象用于访问从关系数据库检索的查询结果。
ResultSet维护着一个指向查询结果中单个行的光标/指针。通过ResultSet提供的导航和获取方法,我们可以逐个迭代和访问数据库记录。ResultSet还可以用于更新数据。
Java 数据集层次结构
![Java ResultSet Class Hierarchy](https://cdn.silicloud.com/blog-img/blog/img/65647bd1a4b2f92e6c72c5c6/3-0.png)
上图展示了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文档