Update result set in java

How to Use Updatable Result Sets with JDBC

In this JDBC tutorial, you will learn how to use updatable result sets in JDBC. By default, result sets are not updatable if you create a Statement object with no-argument constructor like this:

Statement statement = connection.createStatement();

PreparedStatement statement = connection.prepareStatement(sql);

So to use updatable result sets, you must specify the appropriate result set type and result set concurrency values when create a Statement object:

Statement statement = connection.createStatement(int resultSetType, int resultSetConcurrency);

PreparedStatement statement = connection.prepareStatement( String sql, int resultSetType, int resultSetConcurrency);

Remember the possible values for result set type are defined by the following constants in the ResultSet interface:

— TYPE_FORWARD_ONLY : the result set is not scrollable (default).

— TYPE_SCROLL_INSENSITIVE : the result set is scrollable but not sensitive to database changes.

— TYPE_SCROLL_SENSITIVE : the result set is scrollable and sensitive to database changes.

and the possible values for the result set concurrency:

— CONCUR_READ_ONLY : the result set cannot be used to update the database (default).

— CONCUR_UPDATABLE : the result set can be used to update the database.

For example, the following code creates a Statement object that will produce updatable result sets which are scrollable and insensitive to database changes:

Statement statement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

PreparedStatement statement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet result = statement.executeQuery(sql);

It’s also recommended to check if the underlying database supports updatable result sets or not, for example:

DatabaseMetaData metadata = connection.getMetaData(); boolean isUpdatable = metadata.supportsResultSetConcurrency( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); if (!isUpdatable) < // exit >// continue

1. Updating the Current Row in the Result Set

You can update data of the current row in the result set by calling updateXXX() methods:

updateString(int columnIndex, String x) updateString(String columnLabel, String x) updateInt(int columnIndex, int x) updateInt(String columnLabel, int x) .

As you can see, you can specify the column in the current row by using either column index or column label. Note that columnIndex is the order of the column in the result set, which may be different than the order in the actual database.

It’s recommended to specify columns by their names (labels) instead of indexes for code readability and correctness.

After calling updateXXX() methods, call the updateRow() method to commit the changes to the database.

For example, the following code snippet executes a SQL SELECT query to get all rows from the student table, and then update the 3 rd row:

String sql = "SELECT * FROM student"; Statement statement = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet result = statement.executeQuery(sql); result.absolute(3); result.updateString("name", "New Name"); result.updateString("email", "newemail@gmail.com"); result.updateString("major", "New Major"); result.updateRow();

2. Inserting a New Row in the Result Set

result.moveToInsertRow(); result.updateString("name", "New Name"); result.updateString("email", "newemail@gmail.com"); result.updateString("major", "New Major"); result.insertRow(); result.moveToCurrentRow();

You see, first you need to move the cursor to the appropriate position by calling moveToInsertRow() . Use updateXXX() methods to specify values for columns in the row, and then call insertRow() to save changes to the database.

The method moveToCurrentRow() moves the cursor back to the remembered position before the new row was inserted.

3. Deleting the Current Row in the Result Set

NOTE: The updateRow() , insertRow() and deleteRow() methods throw SQLException if a database access error occurs, or the result set is in read-only mode, or they are called on a closed result set.

That’s how to perform changes on an updatable result set. Let’s see a complete example program below.

4. Updatable ResultSet Example Program

The following program retrieves all rows from the student table in a MySQL database schema named college . It allows the user to enter a row number to see the details of that row. Then it asks if the user wishes to update, delete or insert row (type ‘y’ to confirm).

Here’s the complete code of the program:

import java.sql.*; import java.io.*; /** * This program demonstrates how to use updatable result sets with JDBC. * @author www.codejava.net */ public class UpdatableResultSetExample < public static void main(String[] args) < String url = "jdbc:mysql://localhost:3306/college"; String username = "root"; String password = "password"; Console console = System.console(); try (Connection conn = DriverManager.getConnection(url, username, password)) < DatabaseMetaData metadata = conn.getMetaData(); boolean isUpdatable = metadata.supportsResultSetConcurrency( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); if (!isUpdatable) < System.out.println("The database does not support updatable result sets."); return; >String sql = "SELECT * FROM student"; Statement statement = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet result = statement.executeQuery(sql); int row = -1; while (row != 0) < row = Integer.parseInt(console.readLine("Enter row number: ")); if (result.absolute(row)) < readStudentInfo("Student at row " + row + ": ", result); String answer = console.readLine("Do you want to update this row (Y/N)?: "); if (answer.equalsIgnoreCase("Y")) < String name = console.readLine("\tUpdate name: "); String email = console.readLine("\tUpdate email: "); String major = console.readLine("\tUpdate major: "); result.updateString("name", name); result.updateString("email", email); result.updateString("major", major); result.updateRow(); System.out.println("The student at row " + row + " has been updated."); >answer = console.readLine("Do you want to delete this row (Y/N)?: "); if (answer.equalsIgnoreCase("Y")) < result.deleteRow(); System.out.println("The student at row " + row + " has been deleted."); >answer = console.readLine("Do you want to insert new row (Y/N)?: "); if (answer.equalsIgnoreCase("Y")) < result.moveToInsertRow(); String name = console.readLine("\tUpdate name: "); String email = console.readLine("\tUpdate email: "); String major = console.readLine("\tUpdate major: "); result.updateString("name", name); result.updateString("email", email); result.updateString("major", major); result.insertRow(); result.moveToCurrentRow(); System.out.println("The new student has been inserted."); >> else < System.out.println("There's no student at row " + row); >> > catch (SQLException ex) < ex.printStackTrace(); >> private static void readStudentInfo(String position, ResultSet result) throws SQLException < String name = result.getString("name"); String email = result.getString("email"); String major = result.getString("major"); String studentInfo = "%s: %s - %s - %s\n"; System.out.format(studentInfo, position, name, email, major); >>

So far you have understood how to use updatable result sets in JDBC and the benefits: You can perform CRUD operations on the result set without writing any SQL statements — it’s a very convenient and time-saving features, isn’t it?

API References:

Related JDBC Tutorials:

Other JDBC Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Add comment

Comments

CodeJava.net shares Java tutorials, code examples and sample projects for programmers at all levels.
CodeJava.net is created and managed by Nam Ha Minh — a passionate programmer.

Copyright © 2012 — 2023 CodeJava.net, all rights reserved.

Источник

Interface ResultSet

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2"); // rs will be scrollable, will not show changes made by others, // and will be updatable

The ResultSet interface provides getter methods ( getBoolean , getLong , and so on) for retrieving column values from the current row. Values can be retrieved using either the index number of the column or the name of the column. In general, using the column index will be more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

For the getter methods, a JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value. The JDBC specification has a table showing the allowable mappings from SQL types to Java types that can be used by the ResultSet getter methods.

Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

A set of updater methods were added to this interface in the JDBC 2.0 API (Java 2 SDK, Standard Edition, version 1.2). The comments regarding parameters to the getter methods also apply to parameters to the updater methods.

to update a column value in the current row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.

rs.absolute(5); // moves the cursor to the fifth row of rs rs.updateString("NAME", "AINSWORTH"); // updates the // NAME column of row 5 to be AINSWORTH rs.updateRow(); // updates the row in the data source

rs.moveToInsertRow(); // moves cursor to the insert row rs.updateString(1, "AINSWORTH"); // updates the // first column of the insert row to be AINSWORTH rs.updateInt(2,35); // updates the second column to be 35 rs.updateBoolean(3, true); // updates the third column to true rs.insertRow(); rs.moveToCurrentRow();

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

The number, types and properties of a ResultSet object’s columns are provided by the ResultSetMetaData object returned by the ResultSet.getMetaData method.

Источник