WS WhereIsStuff
Tutorial Guides
Log In
Sign Up

Advertisement

Google Ad Slot: content-top

JDBC RowSet

~1 min read · Java
On this page
❮ Previous Next ❯

A RowSet in JDBC is an enhanced version of ResultSet that provides additional features such as scrollability, update capabilities, and offline processing.

Why Use RowSet?

✔ Works offline (disconnected from the database).

✔ Provides scrolling and updatable capabilities.

✔ Can be used with JavaBeans (Serializable).

✔ More flexible than ResultSet.

Types of RowSet in JDBC

There are five types of RowSet in Java:

RowSet Type

Description

JdbcRowSet

A connected RowSet, works like ResultSet.

CachedRowSet

A disconnected RowSet, can be serialized and modified offline.

WebRowSet

A CachedRowSet with XML support (read/write XML).

FilteredRowSet

A CachedRowSet that allows filtering rows.

JoinRowSet

Allows joining multiple RowSets like SQL JOIN.

JdbcRowSet:

JdbcRowSet is a connected RowSet that works like a ResultSet but with additional features.

✔ Uses RowSetProvider.newFactory().createJdbcRowSet() to create a JdbcRowSet.

✔ Executes the query using execute().

✔ Iterates through the result using next().

✔ Unlike ResultSet, it is scrollable and updatable.

import javax.sql.rowset.JdbcRowSet; import javax.sql.rowset.RowSetProvider; import java.sql.SQLException; public class JdbcRowSetExample { public static void main(String[] args) { try { // Creating a JdbcRowSet object JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet(); // Setting Database details rowSet.setUrl("jdbc:mysql://localhost:3306/mydb"); rowSet.setUsername("root"); rowSet.setPassword("password"); // Setting SQL Query rowSet.setCommand("SELECT * FROM employees"); rowSet.execute(); // Iterating through RowSet while (rowSet.next()) { System.out.println(rowSet.getInt("id") + " - " + rowSet.getString("name")); } rowSet.close(); } catch (SQLException e) { e.printStackTrace(); } } }

CachedRowSet:

CachedRowSet allows working offline, meaning once the data is fetched, the database connection is closed, and changes can be made in memory.

✔ Fetches data and disconnects from DB using execute().

✔ Modifies data offline using updateString() and updateRow().

✔ Commits changes back to DB using acceptChanges().

import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.RowSetProvider; import java.sql.SQLException; public class CachedRowSetExample { public static void main(String[] args) { try { // Creating a CachedRowSet object CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet(); // Setting Database details rowSet.setUrl("jdbc:mysql://localhost:3306/mydb"); rowSet.setUsername("root"); rowSet.setPassword("password"); // Setting SQL Query rowSet.setCommand("SELECT * FROM employees"); rowSet.execute(); // Fetches and disconnects from DB // Modifying data offline while (rowSet.next()) { int id = rowSet.getInt("id"); if (id == 102) { rowSet.updateString("name", "Updated Name"); rowSet.updateRow(); // Updates row in memory } } // Reconnecting and committing changes to DB rowSet.acceptChanges(); rowSet.close(); } catch (SQLException e) { e.printStackTrace(); } } }

WebRowSet (XML Support):

WebRowSet extends CachedRowSet and allows storing and reading XML format data.

✔ Fetches data and stores it in XML format using writeXml().

✔ Useful for web applications and configuration storage.

import javax.sql.rowset.WebRowSet; import javax.sql.rowset.RowSetProvider; import java.io.FileWriter; import java.sql.SQLException; public class WebRowSetExample { public static void main(String[] args) { try { WebRowSet rowSet = RowSetProvider.newFactory().createWebRowSet(); // Database connection rowSet.setUrl("jdbc:mysql://localhost:3306/mydb"); rowSet.setUsername("root"); rowSet.setPassword("password"); // Fetch data rowSet.setCommand("SELECT * FROM employees"); rowSet.execute(); // Write data to XML FileWriter writer = new FileWriter("employees.xml"); rowSet.writeXml(writer); writer.close(); System.out.println("Data written to employees.xml"); } catch (Exception e) { e.printStackTrace(); } } }

FilteredRowSet (Filter Data in Memory):

FilteredRowSet is a CachedRowSet that allows filtering rows based on conditions.

✔ Uses setFilter() to filter data in memory without modifying SQL query.

✔ Only shows employees with id > 100.

import javax.sql.rowset.FilteredRowSet; import javax.sql.rowset.RowSetProvider; import java.sql.SQLException; public class FilteredRowSetExample { public static void main(String[] args) { try { FilteredRowSet rowSet = RowSetProvider.newFactory().createFilteredRowSet(); // Database connection rowSet.setUrl("jdbc:mysql://localhost:3306/mydb"); rowSet.setUsername("root"); rowSet.setPassword("password"); // Fetch data rowSet.setCommand("SELECT * FROM employees"); rowSet.execute(); // Applying filter (Example: Show only employees with ID > 100) rowSet.setFilter(row -> { try { return row.getInt("id") > 100; } catch (SQLException e) { e.printStackTrace(); return false; } }); // Display filtered data while (rowSet.next()) { System.out.println(rowSet.getInt("id") + " - " + rowSet.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } } }

JoinRowSet (Joining Multiple RowSets):

JoinRowSet allows joining multiple RowSets like SQL JOIN.

✔ Joins two CachedRowSets on id and emp_id.

import javax.sql.rowset.JoinRowSet; import javax.sql.rowset.RowSetProvider; import javax.sql.rowset.CachedRowSet; import java.sql.SQLException; public class JoinRowSetExample { public static void main(String[] args) { try { JoinRowSet joinRowSet = RowSetProvider.newFactory().createJoinRowSet(); CachedRowSet employeeSet = RowSetProvider.newFactory().createCachedRowSet(); employeeSet.setUrl("jdbc:mysql://localhost:3306/mydb"); employeeSet.setCommand("SELECT id, name FROM employees"); employeeSet.execute(); CachedRowSet departmentSet = RowSetProvider.newFactory().createCachedRowSet(); departmentSet.setUrl("jdbc:mysql://localhost:3306/mydb"); departmentSet.setCommand("SELECT emp_id, department FROM departments"); departmentSet.execute(); // Joining on Employee ID joinRowSet.addRowSet(employeeSet, "id"); joinRowSet.addRowSet(departmentSet, "emp_id"); while (joinRowSet.next()) { System.out.println(joinRowSet.getInt("id") + " - " + joinRowSet.getString("name") + " - " + joinRowSet.getString("department")); } } catch (SQLException e) { e.printStackTrace(); } } }

❮ Previous Next ❯