or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

advanced-patterns.mdcore-operations.mddatabase-init.mddatasource.mdembedded-database.mdexception-translation.mdindex.mdkey-generation.mdlob-support.mdresult-parameter-handling.mdsimplified-operations.mdsqlrowset.mdtransactions.md
tile.json

sqlrowset.mddocs/

SqlRowSet Support

SqlRowSet provides a disconnected, scrollable ResultSet abstraction that doesn't require an active database connection. It's useful for caching query results and navigating data without maintaining a connection.

Core Interfaces

SqlRowSet

Disconnected ResultSet mirror interface.

public interface SqlRowSet {
    // Metadata
    SqlRowSetMetaData getMetaData();
    int findColumn(String columnLabel) throws InvalidResultSetAccessException;

    // Navigation
    boolean next() throws InvalidResultSetAccessException;
    boolean previous() throws InvalidResultSetAccessException;
    boolean first() throws InvalidResultSetAccessException;
    boolean last() throws InvalidResultSetAccessException;
    boolean absolute(int row) throws InvalidResultSetAccessException;
    boolean relative(int rows) throws InvalidResultSetAccessException;
    void beforeFirst() throws InvalidResultSetAccessException;
    void afterLast() throws InvalidResultSetAccessException;
    boolean isFirst() throws InvalidResultSetAccessException;
    boolean isLast() throws InvalidResultSetAccessException;
    boolean isBeforeFirst() throws InvalidResultSetAccessException;
    boolean isAfterLast() throws InvalidResultSetAccessException;
    int getRow() throws InvalidResultSetAccessException;

    // Data retrieval by index
    Object getObject(int columnIndex) throws InvalidResultSetAccessException;
    String getString(int columnIndex) throws InvalidResultSetAccessException;
    boolean getBoolean(int columnIndex) throws InvalidResultSetAccessException;
    byte getByte(int columnIndex) throws InvalidResultSetAccessException;
    short getShort(int columnIndex) throws InvalidResultSetAccessException;
    int getInt(int columnIndex) throws InvalidResultSetAccessException;
    long getLong(int columnIndex) throws InvalidResultSetAccessException;
    float getFloat(int columnIndex) throws InvalidResultSetAccessException;
    double getDouble(int columnIndex) throws InvalidResultSetAccessException;
    BigDecimal getBigDecimal(int columnIndex) throws InvalidResultSetAccessException;
    Date getDate(int columnIndex) throws InvalidResultSetAccessException;
    Time getTime(int columnIndex) throws InvalidResultSetAccessException;
    Timestamp getTimestamp(int columnIndex) throws InvalidResultSetAccessException;

    // Data retrieval by column name
    Object getObject(String columnLabel) throws InvalidResultSetAccessException;
    String getString(String columnLabel) throws InvalidResultSetAccessException;
    boolean getBoolean(String columnLabel) throws InvalidResultSetAccessException;
    int getInt(String columnLabel) throws InvalidResultSetAccessException;
    long getLong(String columnLabel) throws InvalidResultSetAccessException;
    double getDouble(String columnLabel) throws InvalidResultSetAccessException;
    BigDecimal getBigDecimal(String columnLabel) throws InvalidResultSetAccessException;
    Date getDate(String columnLabel) throws InvalidResultSetAccessException;
    Timestamp getTimestamp(String columnLabel) throws InvalidResultSetAccessException;

    // NULL handling
    boolean wasNull() throws InvalidResultSetAccessException;
}

SqlRowSetMetaData

Metadata interface for SqlRowSet.

public interface SqlRowSetMetaData {
    String getCatalogName(int columnIndex) throws InvalidResultSetAccessException;
    String getColumnClassName(int columnIndex) throws InvalidResultSetAccessException;
    int getColumnCount() throws InvalidResultSetAccessException;
    String[] getColumnNames() throws InvalidResultSetAccessException;
    int getColumnDisplaySize(int columnIndex) throws InvalidResultSetAccessException;
    String getColumnLabel(int columnIndex) throws InvalidResultSetAccessException;
    String getColumnName(int columnIndex) throws InvalidResultSetAccessException;
    int getColumnType(int columnIndex) throws InvalidResultSetAccessException;
    String getColumnTypeName(int columnIndex) throws InvalidResultSetAccessException;
    int getPrecision(int columnIndex) throws InvalidResultSetAccessException;
    int getScale(int columnIndex) throws InvalidResultSetAccessException;
    String getSchemaName(int columnIndex) throws InvalidResultSetAccessException;
    String getTableName(int columnIndex) throws InvalidResultSetAccessException;
    boolean isCaseSensitive(int columnIndex) throws InvalidResultSetAccessException;
    boolean isCurrency(int columnIndex) throws InvalidResultSetAccessException;
    boolean isSigned(int columnIndex) throws InvalidResultSetAccessException;
}

Usage Examples

Basic SqlRowSet Usage

// Query for SqlRowSet
SqlRowSet rowSet = jdbcTemplate.queryForRowSet(
    "SELECT id, name, email, salary FROM employees WHERE department = ?",
    "Engineering"
);

// Iterate through rows
while (rowSet.next()) {
    long id = rowSet.getLong("id");
    String name = rowSet.getString("name");
    String email = rowSet.getString("email");
    double salary = rowSet.getDouble("salary");

    System.out.println(id + ": " + name + " - " + email + " ($" + salary + ")");
}

Navigating SqlRowSet

SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT * FROM users ORDER BY id");

// Go to first row
if (rowSet.first()) {
    System.out.println("First user: " + rowSet.getString("name"));
}

// Go to last row
if (rowSet.last()) {
    System.out.println("Last user: " + rowSet.getString("name"));
    System.out.println("Total rows: " + rowSet.getRow());
}

// Go to specific row (1-based)
if (rowSet.absolute(5)) {
    System.out.println("5th user: " + rowSet.getString("name"));
}

// Move relative to current position
rowSet.first();
if (rowSet.relative(3)) {  // Move 3 rows forward
    System.out.println("4th user: " + rowSet.getString("name"));
}

// Navigate backwards
while (rowSet.previous()) {
    System.out.println(rowSet.getString("name"));
}

Accessing Metadata

SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT * FROM users");

SqlRowSetMetaData metaData = rowSet.getMetaData();

// Get column information
int columnCount = metaData.getColumnCount();
System.out.println("Column count: " + columnCount);

String[] columnNames = metaData.getColumnNames();
for (String columnName : columnNames) {
    int index = rowSet.findColumn(columnName);
    String typeName = metaData.getColumnTypeName(index);
    int displaySize = metaData.getColumnDisplaySize(index);

    System.out.println(columnName + " (" + typeName + ", " + displaySize + ")");
}

Named Parameters with SqlRowSet

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

SqlRowSet rowSet = namedTemplate.queryForRowSet(
    "SELECT * FROM orders WHERE user_id = :userId AND status = :status",
    Map.of("userId", 123, "status", "completed")
);

while (rowSet.next()) {
    long orderId = rowSet.getLong("id");
    BigDecimal total = rowSet.getBigDecimal("total");
    Timestamp createdAt = rowSet.getTimestamp("created_at");

    System.out.println("Order " + orderId + ": $" + total);
}

Handling NULL Values

SqlRowSet rowSet = jdbcTemplate.queryForRowSet(
    "SELECT id, name, middle_name, email FROM users"
);

while (rowSet.next()) {
    long id = rowSet.getLong("id");
    String name = rowSet.getString("name");

    // Check for NULL
    String middleName = rowSet.getString("middle_name");
    if (rowSet.wasNull()) {
        middleName = "(none)";
    }

    System.out.println(id + ": " + name + " " + middleName);
}

Converting SqlRowSet to List

public List<User> sqlRowSetToList(SqlRowSet rowSet) {
    List<User> users = new ArrayList<>();

    while (rowSet.next()) {
        User user = new User();
        user.setId(rowSet.getLong("id"));
        user.setName(rowSet.getString("name"));
        user.setEmail(rowSet.getString("email"));
        users.add(user);
    }

    return users;
}

// Usage
SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT * FROM users");
List<User> users = sqlRowSetToList(rowSet);

Caching Query Results

@Service
public class ReportService {
    private final JdbcTemplate jdbcTemplate;
    private SqlRowSet cachedResults;
    private LocalDateTime cacheTime;

    public ReportService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public SqlRowSet getReport() {
        // Cache for 5 minutes
        if (cachedResults == null ||
            cacheTime.isBefore(LocalDateTime.now().minusMinutes(5))) {

            cachedResults = jdbcTemplate.queryForRowSet(
                "SELECT * FROM sales_report"
            );
            cacheTime = LocalDateTime.now();
        }

        // Reset to start
        cachedResults.beforeFirst();
        return cachedResults;
    }
}

Paginating Results

public SqlRowSet getPagedResults(int page, int pageSize) {
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(
        "SELECT * FROM products ORDER BY id"
    );

    // Navigate to page start
    int startRow = (page - 1) * pageSize + 1;
    if (rowSet.absolute(startRow)) {
        return rowSet;
    }

    return null;
}

// Usage
SqlRowSet page1 = getPagedResults(1, 10);  // First 10 rows
int count = 0;
while (page1.next() && count < 10) {
    System.out.println(page1.getString("name"));
    count++;
}

Searching Within SqlRowSet

public boolean findInRowSet(SqlRowSet rowSet, String searchName) {
    rowSet.beforeFirst();

    while (rowSet.next()) {
        String name = rowSet.getString("name");
        if (name.equalsIgnoreCase(searchName)) {
            // Found it - rowSet positioned at matching row
            return true;
        }
    }

    return false;
}

Converting to Different Formats

// Convert SqlRowSet to JSON
public String sqlRowSetToJson(SqlRowSet rowSet) {
    StringBuilder json = new StringBuilder("[");
    SqlRowSetMetaData metaData = rowSet.getMetaData();
    String[] columnNames = metaData.getColumnNames();

    boolean first = true;
    while (rowSet.next()) {
        if (!first) json.append(",");
        json.append("{");

        for (int i = 0; i < columnNames.length; i++) {
            if (i > 0) json.append(",");
            json.append("\"").append(columnNames[i]).append("\":");

            Object value = rowSet.getObject(i + 1);
            if (value == null) {
                json.append("null");
            } else if (value instanceof Number) {
                json.append(value);
            } else {
                json.append("\"").append(value).append("\"");
            }
        }

        json.append("}");
        first = false;
    }

    json.append("]");
    return json.toString();
}

Row Count

public int getRowCount(SqlRowSet rowSet) {
    // Go to last row and get row number
    if (rowSet.last()) {
        int count = rowSet.getRow();
        rowSet.beforeFirst();  // Reset to beginning
        return count;
    }
    return 0;
}

When to Use SqlRowSet

SqlRowSet is useful when:

  • You need to cache query results
  • You require scrollable navigation (forward/backward)
  • Connection needs to be closed but data retained
  • You want to pass results across layers
  • Multiple passes over data are needed

Limitations

  • Read-only (cannot update database)
  • All data loaded into memory
  • No streaming for large datasets
  • Performance overhead compared to direct ResultSet

Best Practices

  1. Use for small to medium result sets - All data loaded in memory
  2. Close connection after query - SqlRowSet is disconnected
  3. Check wasNull() for NULL values - Handle nulls properly
  4. Reset position before reuse - Call beforeFirst()
  5. Use appropriate getter methods - Match SQL types
  6. Consider alternatives for large data - Use streaming or pagination
  7. Cache when beneficial - Reuse expensive query results
  8. Handle InvalidResultSetAccessException - Check navigation results