Java SQL ResultSet

In the world of programming, especially when working with databases like a librarian handles books, we often need a way to retrieve and manipulate data. This is where Java SQL ResultSet step in. They're like your trusty assistant, helping you fetch data from your database and present it in a format that your Java code can easily understand.

Just like how you might scan through shelves to find specific books, a ResultSet lets your Java code navigate through rows of data fetched from a database. Want to find a particular book? ResultSet can help you pinpoint it by moving through rows and columns until you've found what you're looking for.

But it's not just about finding data; ResultSet also allows you to update, insert, and delete records in your database. Think of it as having the power to add new books to your library, reshuffle existing ones, or even remove outdated editions.

Types of ResultSets

Certainly! Let's explore the different types of ResultSets in Java along with examples:

1. Forward-only ResultSet

• This type of ResultSet allows you to move only forward through the data.

• Once a row is fetched, you cannot go back to the previous one.

• It's generally the most efficient type for reading data.

  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery("SELECT * FROM books");
  while (rs.next()) {
    // Process each row
  }

2. Scrollable ResultSet

• Unlike the forward-only ResultSet, this type allows you to move both forward and backward through the data.

• It provides more flexibility but may be less efficient than forward-only ResultSets.

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  ResultSet rs = stmt.executeQuery("SELECT * FROM books");
  rs.afterLast(); // Move to the end
  while (rs.previous()) {
      // Process each row in reverse order
  }

3. Updatable ResultSet

• This type allows you to not only read but also update the data in the ResultSet and underlying database.

• You can insert, update, or delete rows directly through the ResultSet.

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  ResultSet rs = stmt.executeQuery("SELECT * FROM books");
  while (rs.next()) {
    if (rs.getString("title").equals("Java Programming")) {
        rs.updateString("title", "Advanced Java Programming");
        rs.updateRow(); // Update the row in the database
    }
  }

4. Insensitive ResultSet

• This type is insensitive to changes made by other users in the database.

• It returns a snapshot of the data as it was when the ResultSet was created.

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  ResultSet rs = stmt.executeQuery("SELECT * FROM books");

5. Sensitive ResultSet

• This type is sensitive to changes made by other users in the database.

• It reflects the changes made to the data even after the ResultSet is created.

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
  ResultSet rs = stmt.executeQuery("SELECT * FROM books");

Interacting with result set

Sure, let's delve deeper into "seeing" ResultSet, explaining how you can view and interact with the data it contains:

1. Viewing Data

• When you execute a query against a database using a Statement or PreparedStatement in Java, the result is typically stored in a ResultSet.
• To view the data in the ResultSet, you iterate through its rows using methods like next(), which moves the cursor to the next row, and retrieve column values using getter methods like getString(), getInt(), etc.

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM books");
while (rs.next()) {
    String title = rs.getString("title");
    int pageCount = rs.getInt("page_count");
    System.out.println("Title: " + title + ", Page Count: " + pageCount);
}

2. Navigating

• Depending on the type of ResultSet, you can navigate through the data differently.
• Forward-only ResultSets allow only forward movement with methods like next().
• Scrollable ResultSets offer more flexibility, allowing you to move both forward and backward using methods like next(), previous(), first(), last(), etc.

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM books");
rs.last(); // Move to the last row
while (rs.previous()) {
    // Process each row in reverse order
}

3. Updating Data

• Updatable ResultSets allow you to not only read but also modify data.
• You can use methods like updateString(), updateInt(), insertRow(), deleteRow(), etc., to modify the data in the ResultSet and the underlying database.

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM books");
while (rs.next()) {
    if (rs.getString("title").equals("Java Programming")) {
        rs.updateString("title", "Advanced Java Programming");
        rs.updateRow(); // Update the row in the database
    }
}

4. Observing Changes

• Depending on whether the ResultSet is insensitive or sensitive, it may or may not reflect changes made by other users in the database after it was created.
• Insensitive ResultSets return a snapshot of the data as it was when the ResultSet was created, while sensitive ResultSets reflect changes made by other users.

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM books");
// ResultSet will not reflect changes made by other users