// File: ScrollableAndUpdatableResultSetExample.java
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdateScrollResultset {
    public static void main(String[] args) throws ClassNotFoundException {
       
        //Class.forName("oracle.jdbc.driver.OracleDriver");
       //String jdbcURL = "jdbc:oracle:thin:@localhost:1521/XE";
        String jdbcURL = "jdbc:mysql://localhost:3306/jdbcdb"; 
        String username = ""; 
        String password = ""; 

    
        String query = "SELECT emp_id, empname, dob,salary,dept_id FROM employee";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {

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

            
            ResultSet resultSet = statement.executeQuery(query);

            
            if (resultSet.last()) {
                System.out.println("Last Employee:");
                printEmployee(resultSet);
            }

            
            if (resultSet.first()) {
                System.out.println("First Employee:");
                printEmployee(resultSet);
            }

            
            if (resultSet.absolute(3)) {
                System.out.println("Third Employee:");
                printEmployee(resultSet);
            }

   
            resultSet.absolute(2); /
            double oldSalary = resultSet.getDouble("salary");
            double newSalary = oldSalary * 1.05; 
            resultSet.updateDouble("salary", newSalary);
            

            resultSet.updateRow();

            System.out.println("Updated Employee (2nd row) Salary: ");
            printEmployee(resultSet);

          
            resultSet.moveToInsertRow(); 
            resultSet.updateInt("emp_id",9);
            resultSet.updateString("empname", "Karthik");
            resultSet.updateDate("dob", Date.valueOf("1990-01-01"));
            resultSet.updateDouble("salary", 500000);
            resultSet.updateInt("dept_id", 15);
            resultSet.insertRow(); // Insert the row in the database
            System.out.println("Inserted a new employee.");
    
            // Delete the newly inserted row
            if (resultSet.last()) {
                resultSet.deleteRow(); // Delete the last row (which was just inserted)
                System.out.println("Deleted the newly inserted employee.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // Utility method to print employee details
    private static void printEmployee(ResultSet resultSet) throws SQLException {
        int empId = resultSet.getInt("emp_id");
        String empName = resultSet.getString("empname");
        Date dob = resultSet.getDate("dob");
        double salary = resultSet.getDouble("salary");
        int deptId = resultSet.getInt("dept_id");

        System.out.println("Employee ID: " + empId);
        System.out.println("Employee Name: " + empName);
       // System.out.println("Date of Birth: " + dob);
        System.out.println("Salary: " + salary);
        System.out.println("Department ID: " + deptId);
        System.out.println("------------------------");
    }
}