JDBC code for Prepared Statements, Scrollable and Updatable Result Sets, Row sets by G Krishna

Objective:     Write a JDBC code for Prepared Statements, Scrollable and Updatable Result Sets, and Row sets.

Code :

package com.lcgk.jdbc;

import java.sql.*;

import javax.sql.rowset.*;

public class ScrollableResultSetSensitiveExample {

public static void main(String[] args) {

try {

// Load the MySQL JDBC driver

Class.forName("com.mysql.jdbc.Driver");

// Establish the connection

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/gk", "root", "");

// Create a PreparedStatement for updating data

String updateQuery = "UPDATE login SET un = ? WHERE SN = ?";

PreparedStatement pstmt = con.prepareStatement(updateQuery);

// Set new values for the "un" column and specify the row to update based on the "SN" column

pstmt.setString(1, "new_username"); // New value for the "un" column

pstmt.setInt(2, 1); // Row where "SN" = 1

// Execute the update statement

int rowsUpdated = pstmt.executeUpdate();

if (rowsUpdated > 0) {

System.out.println("Data updated successfully.");

} else {

System.out.println("No rows were updated.");

}

// Close the PreparedStatement

pstmt.close();

// Create a Statement with scrollable and updatable result set

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

// Execute query to retrieve data

ResultSet rs = stmt.executeQuery("SELECT * FROM login");

// Move cursor to the first row

rs.first();

// Update the first row

rs.updateString("un", "updated_username");

rs.updateRow();

System.out.println("First row updated successfully.");

// Print updated record

System.out.println("Updated record:");

System.out.println("SN: " + rs.getInt("SN") + ", un: " + rs.getString("un"));

// Create a RowSet and populate it with data from the ResultSet

CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();

rowSet.populate(rs);

System.out.println("Record After updated row :");

// Iterate over the RowSet and print data

while (rowSet.next()) {

System.out.println("SN: " + rowSet.getInt("SN") + ", un: " + rowSet.getString("un"));

}

// Close resources

rs.close();

stmt.close();

con.close();

} catch(Exception e) {

System.out.println(e);

}

}

}

Output: