Your cart is currently empty!
Author: alien
-
Khóa học miễn phí Java & MySQL – Update Result Set nhận dự án làm có lương
Java & MySQL – Updating a ResultSet
The ResultSet interface contains a collection of update methods for updating the data of a result set.
As with the get methods, there are two update methods for each data type −
-
One that takes in a column name.
-
One that takes in a column index.
For example, to update a String column of the current row of a result set, you would use one of the following updateString() methods −
S.N. Methods & Description 1 public void updateString(int columnIndex, String s) throws SQLException Changes the String in the specified column to the value of s.
2 public void updateString(String columnName, String s) throws SQLException Similar to the previous method, except that the column is specified by its name instead of its index.
There are update methods for the eight primitive data types, as well as String, Object, URL, and the SQL data types in the java.sql package.
Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the underlying database. To update your changes to the row in the database, you need to invoke one of the following methods.
S.N. Methods & Description 1 public void updateRow() Updates the current row by updating the corresponding row in the database.
2 public void deleteRow() Deletes the current row from the database
3 public void refreshRow() Refreshes the data in the result set to reflect any recent changes in the database.
4 public void cancelRowUpdates() Cancels any updates made on the current row.
5 public void insertRow() Inserts a row into the database. This method can only be invoked when the cursor is pointing to the insert row.
Following is the example, which makes use of the ResultSet.CONCUR_UPDATABLE and ResultSet.TYPE_SCROLL_INSENSITIVE described in the Result Set tutorial. This example would explain INSERT, UPDATE and DELETE operation on a table.
It should be noted that tables you are working on should have Primary Key set properly. Let”s update our referece table first.
Drop the table and Create the table Employees again as follows −
mysql> use TUTORIALSPOINT; mysql> drop table Employees; Query OK, 0 rows affected (0.08 sec) mysql> create table Employees -> ( -> id int primary key auto_increment, -> age int not null, -> first varchar (255), -> last varchar (255) -> ); Query OK, 0 rows affected (0.08 sec) mysql>Create Data Records
Finally you create few records in Employee table as follows −
mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (18, ''Zara'', ''Ali''); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (25, ''Mahnaz'', ''Fatma''); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (30, ''Zaid'', ''Khan''); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (28, ''Sumit'', ''Mittal''); Query OK, 1 row affected (0.00 sec) mysql>
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestApplication { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT id, first, last, age FROM Employees"; public static void printResultSet(ResultSet rs) throws SQLException{ // Ensure we start with first row rs.beforeFirst(); while(rs.next()){ // Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); } System.out.println(); } public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(QUERY); ) { System.out.println("List result set for reference...."); printResultSet(rs); // Loop through result set and add 5 in age // Move to Before first position so while-loop works properly rs.beforeFirst(); //STEP 7: Extract data from result set while(rs.next()){ // Retrieve by column name int newAge = rs.getInt("age") + 5; rs.updateDouble( "age", newAge ); rs.updateRow(); } System.out.println("List result set showing new ages..."); printResultSet(rs); // Insert a record into the table. // Move to insert row and add column data with updateXXX() System.out.println("Inserting a new record..."); rs.moveToInsertRow(); rs.updateString("first","John"); rs.updateString("last","Paul"); rs.updateInt("age",40); // Commit row rs.insertRow(); System.out.println("List result set showing new set..."); printResultSet(rs); } catch (SQLException e) { e.printStackTrace(); } } }Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication List result set for reference.... ID: 1, Age: 18, First: Zara, Last: Ali ID: 2, Age: 25, First: Mahnaz, Last: Fatma ID: 3, Age: 30, First: Zaid, Last: Khan ID: 4, Age: 28, First: Sumit, Last: Mittal List result set showing new ages... ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal Inserting a new record... List result set showing new set... ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul C:>
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
-
Khóa học miễn phí Java & MySQL – Navigate Result Set nhận dự án làm có lương
Java & MySQL – Navigating a ResultSet
There are several methods in the ResultSet interface that involve moving the cursor, including −
| S.N. | Methods & Description |
|---|---|
| 1 | public void beforeFirst() throws SQLException
Moves the cursor just before the first row. |
| 2 | public void afterLast() throws SQLException
Moves the cursor just after the last row. |
| 3 | public boolean first() throws SQLException
Moves the cursor to the first row. |
| 4 | public void last() throws SQLException
Moves the cursor to the last row. |
| 5 | public boolean absolute(int row) throws SQLException
Moves the cursor to the specified row. |
| 6 | public boolean relative(int row) throws SQLException
Moves the cursor the given number of rows forward or backward, from where it is currently pointing. |
| 7 | public boolean previous() throws SQLException
Moves the cursor to the previous row. This method returns false if the previous row is off the result set. |
| 8 | public boolean next() throws SQLException
Moves the cursor to the next row. This method returns false if there are no more rows in the result set. |
| 9 | public int getRow() throws SQLException
Returns the row number that the cursor is pointing to. |
| 10 | public void moveToInsertRow() throws SQLException
Moves the cursor to a special row in the result set that can be used to insert a new row into the database. The current cursor location is remembered. |
| 11 | public void moveToCurrentRow() throws SQLException
Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing |
Following is the example which makes use of few navigation methods described.
This sample code has been written based on the environment and database setup done in the previous chapters.
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestApplication {
static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "guest";
static final String PASS = "guest123";
static final String QUERY = "SELECT id, first, last, age FROM Employees";
public static void main(String[] args) {
// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(QUERY);
) {
// Move cursor to the last row.
System.out.println("Moving cursor to the last...");
rs.last();
// Extract data from result set
System.out.println("Displaying record...");
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
// Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
// Move cursor to the first row.
System.out.println("Moving cursor to the first row...");
rs.first();
// Extract data from result set
System.out.println("Displaying record...");
// Retrieve by column name
id = rs.getInt("id");
age = rs.getInt("age");
first = rs.getString("first");
last = rs.getString("last");
// Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
// Move cursor to the first row.
System.out.println("Moving cursor to the next row...");
rs.next();
// Extract data from result set
System.out.println("Displaying record...");
id = rs.getInt("id");
age = rs.getInt("age");
first = rs.getString("first");
last = rs.getString("last");
// Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication Moving cursor to the last... Displaying record... ID: 103, Age: 30, First: Sumit, Last: Mittal Moving cursor to the first row... Displaying record... ID: 100, Age: 18, First: Zara, Last: Ali Moving cursor to the next row... Displaying record... ID: 101, Age: 25, First: Mehnaz, Last: Fatma C:>
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
