- JDBC Tutorial
- JDBC - Home
- JDBC - Introduction
- JDBC - SQL Syntax
- JDBC - Environment
- JDBC - Sample Code
- JDBC - Driver Types
- JDBC - Connections
- JDBC - Statements
- JDBC - Result Sets
- JDBC - Data Types
- JDBC - Transactions
- JDBC - Exceptions
- JDBC - Batch Processing
- JDBC - Stored Procedure
- JDBC - Streaming Data
- JDBC Examples
- JDBC - Create Database
- JDBC - Select Database
- JDBC - Drop Database
- JDBC - Create Tables
- JDBC - Drop Tables
- JDBC - Insert Records
- JDBC - Select Records
- JDBC - Update Records
- JDBC - Delete Records
- JDBC - WHERE Clause
- JDBC - Like Clause
- JDBC - Sorting Data
- JDBC Useful Resources
- JDBC - Questions and Answers
- JDBC - Quick Guide
- JDBC - Useful Resources
- JDBC - Discussion
- Useful - Java Tutorials
JDBC - Updating a Result Set Example
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.
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 JDBCExample.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 JDBCExample { 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 JDBCExample.java C:\>
When you run JDBCExample, it produces the following result −
C:\>java JDBCExample 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:\>
Advertisements