WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

JDBC RowSet

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