0
# ResultSet Handling
1

2
This document covers ResultSet processing, navigation, type conversions, and PostgreSQL-specific ResultSet extensions.
3

4
## Capabilities
5

6
### ResultSet Navigation
7

8
Standard JDBC ResultSet interface for processing query results.
9

10
```java { .api }
11
import java.sql.ResultSet;
12
import java.sql.SQLException;
13

14
/**
15
 * ResultSet represents the result of a database query.
16
 * PostgreSQL driver provides full JDBC ResultSet implementation.
17
 */
18
public interface ResultSet extends AutoCloseable {
19
    /**
20
     * Moves cursor to next row.
21
     *
22
     * @return true if new row is valid, false if no more rows
23
     * @throws SQLException if navigation fails
24
     */
25
    boolean next() throws SQLException;
26

27
    /**
28
     * Closes the ResultSet and releases resources.
29
     *
30
     * @throws SQLException if close fails
31
     */
32
    void close() throws SQLException;
33

34
    /**
35
     * Reports whether last column read was SQL NULL.
36
     *
37
     * @return true if last column was NULL
38
     * @throws SQLException if check fails
39
     */
40
    boolean wasNull() throws SQLException;
41

42
    // Scrollable ResultSet methods (when supported)
43

44
    /**
45
     * Moves cursor to previous row.
46
     * Requires TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE.
47
     *
48
     * @return true if cursor is on valid row
49
     * @throws SQLException if navigation fails
50
     */
51
    boolean previous() throws SQLException;
52

53
    /**
54
     * Moves cursor before first row.
55
     *
56
     * @throws SQLException if navigation fails
57
     */
58
    void beforeFirst() throws SQLException;
59

60
    /**
61
     * Moves cursor after last row.
62
     *
63
     * @throws SQLException if navigation fails
64
     */
65
    void afterLast() throws SQLException;
66

67
    /**
68
     * Moves cursor to first row.
69
     *
70
     * @return true if cursor is on valid row
71
     * @throws SQLException if navigation fails
72
     */
73
    boolean first() throws SQLException;
74

75
    /**
76
     * Moves cursor to last row.
77
     *
78
     * @return true if cursor is on valid row
79
     * @throws SQLException if navigation fails
80
     */
81
    boolean last() throws SQLException;
82

83
    /**
84
     * Moves cursor to specified row number (1-based).
85
     *
86
     * @param row Row number
87
     * @return true if cursor is on valid row
88
     * @throws SQLException if navigation fails
89
     */
90
    boolean absolute(int row) throws SQLException;
91

92
    /**
93
     * Moves cursor relative to current position.
94
     *
95
     * @param rows Number of rows to move (negative for backwards)
96
     * @return true if cursor is on valid row
97
     * @throws SQLException if navigation fails
98
     */
99
    boolean relative(int rows) throws SQLException;
100

101
    /**
102
     * Returns current row number (1-based).
103
     *
104
     * @return Row number, or 0 if before first or after last
105
     * @throws SQLException if retrieval fails
106
     */
107
    int getRow() throws SQLException;
108

109
    /**
110
     * Checks if cursor is before first row.
111
     *
112
     * @return true if before first
113
     * @throws SQLException if check fails
114
     */
115
    boolean isBeforeFirst() throws SQLException;
116

117
    /**
118
     * Checks if cursor is after last row.
119
     *
120
     * @return true if after last
121
     * @throws SQLException if check fails
122
     */
123
    boolean isAfterLast() throws SQLException;
124

125
    /**
126
     * Checks if cursor is on first row.
127
     *
128
     * @return true if on first row
129
     * @throws SQLException if check fails
130
     */
131
    boolean isFirst() throws SQLException;
132

133
    /**
134
     * Checks if cursor is on last row.
135
     *
136
     * @return true if on last row
137
     * @throws SQLException if check fails
138
     */
139
    boolean isLast() throws SQLException;
140
}
141
```
142

143
### Data Retrieval Methods
144

145
Methods for retrieving column values from ResultSet.
146

147
```java { .api }
148
/**
149
 * ResultSet data retrieval methods.
150
 * Columns can be accessed by index (1-based) or name.
151
 */
152
public interface ResultSet {
153
    // Retrieve by column index (1-based)
154

155
    String getString(int columnIndex) throws SQLException;
156
    boolean getBoolean(int columnIndex) throws SQLException;
157
    byte getByte(int columnIndex) throws SQLException;
158
    short getShort(int columnIndex) throws SQLException;
159
    int getInt(int columnIndex) throws SQLException;
160
    long getLong(int columnIndex) throws SQLException;
161
    float getFloat(int columnIndex) throws SQLException;
162
    double getDouble(int columnIndex) throws SQLException;
163
    java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException;
164
    byte[] getBytes(int columnIndex) throws SQLException;
165
    java.sql.Date getDate(int columnIndex) throws SQLException;
166
    java.sql.Time getTime(int columnIndex) throws SQLException;
167
    java.sql.Timestamp getTimestamp(int columnIndex) throws SQLException;
168
    Object getObject(int columnIndex) throws SQLException;
169
    java.sql.Array getArray(int columnIndex) throws SQLException;
170
    java.sql.Blob getBlob(int columnIndex) throws SQLException;
171
    java.sql.Clob getClob(int columnIndex) throws SQLException;
172
    java.io.InputStream getBinaryStream(int columnIndex) throws SQLException;
173
    java.io.Reader getCharacterStream(int columnIndex) throws SQLException;
174

175
    // Retrieve by column name
176

177
    String getString(String columnLabel) throws SQLException;
178
    boolean getBoolean(String columnLabel) throws SQLException;
179
    byte getByte(String columnLabel) throws SQLException;
180
    short getShort(String columnLabel) throws SQLException;
181
    int getInt(String columnLabel) throws SQLException;
182
    long getLong(String columnLabel) throws SQLException;
183
    float getFloat(String columnLabel) throws SQLException;
184
    double getDouble(String columnLabel) throws SQLException;
185
    java.math.BigDecimal getBigDecimal(String columnLabel) throws SQLException;
186
    byte[] getBytes(String columnLabel) throws SQLException;
187
    java.sql.Date getDate(String columnLabel) throws SQLException;
188
    java.sql.Time getTime(String columnLabel) throws SQLException;
189
    java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;
190
    Object getObject(String columnLabel) throws SQLException;
191
    java.sql.Array getArray(String columnLabel) throws SQLException;
192

193
    // Type-specific methods
194

195
    /**
196
     * Gets a column value as specified Java type.
197
     *
198
     * @param columnIndex Column index
199
     * @param type Target Java class
200
     * @return Value converted to target type
201
     * @throws SQLException if conversion fails
202
     */
203
    <T> T getObject(int columnIndex, Class<T> type) throws SQLException;
204

205
    /**
206
     * Gets column value with custom type map.
207
     *
208
     * @param columnIndex Column index
209
     * @param map Type mappings
210
     * @return Mapped object
211
     * @throws SQLException if retrieval fails
212
     */
213
    Object getObject(int columnIndex, java.util.Map<String,Class<?>> map)
214
            throws SQLException;
215
}
216
```
217

218
**Usage Examples:**
219

220
```java
221
import java.sql.*;
222

223
// Example 1: Basic ResultSet processing
224
public class BasicResultSetExample {
225
    public static void processResults(Connection conn) throws SQLException {
226
        String sql = "SELECT id, name, email, created_at FROM users";
227

228
        try (Statement stmt = conn.createStatement();
229
             ResultSet rs = stmt.executeQuery(sql)) {
230

231
            while (rs.next()) {
232
                int id = rs.getInt("id");
233
                String name = rs.getString("name");
234
                String email = rs.getString("email");
235
                Timestamp createdAt = rs.getTimestamp("created_at");
236

237
                System.out.printf("%d: %s <%s> - %s%n",
238
                    id, name, email, createdAt);
239
            }
240
        }
241
    }
242
}
243

244
// Example 2: Handle NULL values
245
public class NullHandlingExample {
246
    public static void handleNulls(ResultSet rs) throws SQLException {
247
        String name = rs.getString("name");
248
        if (rs.wasNull()) {
249
            name = "Unknown";
250
        }
251

252
        Integer age = rs.getInt("age");
253
        if (rs.wasNull()) {
254
            age = null; // Or handle appropriately
255
        }
256
    }
257
}
258

259
// Example 3: Scrollable ResultSet
260
public class ScrollableResultSetExample {
261
    public static void scrollResults(Connection conn) throws SQLException {
262
        String sql = "SELECT * FROM products";
263

264
        // Create scrollable ResultSet
265
        try (Statement stmt = conn.createStatement(
266
                 ResultSet.TYPE_SCROLL_INSENSITIVE,
267
                 ResultSet.CONCUR_READ_ONLY);
268
             ResultSet rs = stmt.executeQuery(sql)) {
269

270
            // Jump to last row
271
            if (rs.last()) {
272
                System.out.println("Last row: " + rs.getInt("id"));
273
            }
274

275
            // Go back to first
276
            if (rs.first()) {
277
                System.out.println("First row: " + rs.getInt("id"));
278
            }
279

280
            // Move to specific row
281
            if (rs.absolute(5)) {
282
                System.out.println("Row 5: " + rs.getInt("id"));
283
            }
284

285
            // Iterate backwards
286
            while (rs.previous()) {
287
                System.out.println("Row: " + rs.getInt("id"));
288
            }
289
        }
290
    }
291
}
292

293
// Example 4: Binary data retrieval
294
public class BinaryDataExample {
295
    public static byte[] getImageData(Connection conn, int imageId)
296
            throws SQLException {
297
        String sql = "SELECT data FROM images WHERE id = ?";
298

299
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
300
            pstmt.setInt(1, imageId);
301

302
            try (ResultSet rs = pstmt.executeQuery()) {
303
                if (rs.next()) {
304
                    return rs.getBytes("data");
305
                }
306
            }
307
        }
308
        return null;
309
    }
310
}
311
```
312

313
### PGResultSetMetaData
314

315
PostgreSQL-specific ResultSet metadata extensions.
316

317
```java { .api }
318
package org.postgresql;
319

320
import java.sql.ResultSetMetaData;
321
import java.sql.SQLException;
322

323
/**
324
 * PostgreSQL extensions to ResultSetMetaData.
325
 * Provides additional metadata about result columns.
326
 */
327
public interface PGResultSetMetaData extends ResultSetMetaData {
328
    /**
329
     * Returns the underlying column name before any aliasing.
330
     *
331
     * @param column Column number (1-based)
332
     * @return Base column name
333
     * @throws SQLException if column is invalid
334
     */
335
    String getBaseColumnName(int column) throws SQLException;
336

337
    /**
338
     * Returns the underlying table name before any aliasing.
339
     *
340
     * @param column Column number (1-based)
341
     * @return Base table name
342
     * @throws SQLException if column is invalid
343
     */
344
    String getBaseTableName(int column) throws SQLException;
345

346
    /**
347
     * Returns the underlying schema name.
348
     *
349
     * @param column Column number (1-based)
350
     * @return Base schema name
351
     * @throws SQLException if column is invalid
352
     */
353
    String getBaseSchemaName(int column) throws SQLException;
354

355
    /**
356
     * Returns the format of the column data.
357
     *
358
     * @param column Column number (1-based)
359
     * @return 0 for text format, 1 for binary format
360
     * @throws SQLException if column is invalid
361
     */
362
    int getFormat(int column) throws SQLException;
363
}
364
```
365

366
**Standard ResultSetMetaData Methods:**
367

368
```java { .api }
369
import java.sql.ResultSetMetaData;
370
import java.sql.SQLException;
371

372
/**
373
 * Standard ResultSetMetaData interface.
374
 * Provides information about ResultSet columns.
375
 */
376
public interface ResultSetMetaData {
377
    /**
378
     * Returns the number of columns in the ResultSet.
379
     */
380
    int getColumnCount() throws SQLException;
381

382
    /**
383
     * Returns the column name or alias.
384
     */
385
    String getColumnName(int column) throws SQLException;
386

387
    /**
388
     * Returns the column label (alias if specified).
389
     */
390
    String getColumnLabel(int column) throws SQLException;
391

392
    /**
393
     * Returns the SQL type code for the column.
394
     */
395
    int getColumnType(int column) throws SQLException;
396

397
    /**
398
     * Returns the database-specific type name.
399
     */
400
    String getColumnTypeName(int column) throws SQLException;
401

402
    /**
403
     * Returns the table name for the column.
404
     */
405
    String getTableName(int column) throws SQLException;
406

407
    /**
408
     * Returns the schema name for the column.
409
     */
410
    String getSchemaName(int column) throws SQLException;
411

412
    /**
413
     * Returns the catalog name for the column.
414
     */
415
    String getCatalogName(int column) throws SQLException;
416

417
    /**
418
     * Returns the column display size.
419
     */
420
    int getColumnDisplaySize(int column) throws SQLException;
421

422
    /**
423
     * Returns the precision for numeric columns.
424
     */
425
    int getPrecision(int column) throws SQLException;
426

427
    /**
428
     * Returns the scale for numeric columns.
429
     */
430
    int getScale(int column) throws SQLException;
431

432
    /**
433
     * Indicates whether column is automatically numbered.
434
     */
435
    boolean isAutoIncrement(int column) throws SQLException;
436

437
    /**
438
     * Indicates whether column is case sensitive.
439
     */
440
    boolean isCaseSensitive(int column) throws SQLException;
441

442
    /**
443
     * Indicates whether column can be used in WHERE clause.
444
     */
445
    boolean isSearchable(int column) throws SQLException;
446

447
    /**
448
     * Indicates whether column is a currency value.
449
     */
450
    boolean isCurrency(int column) throws SQLException;
451

452
    /**
453
     * Indicates nullability of column values.
454
     * Returns columnNoNulls, columnNullable, or columnNullableUnknown.
455
     */
456
    int isNullable(int column) throws SQLException;
457

458
    /**
459
     * Indicates whether column is signed numeric.
460
     */
461
    boolean isSigned(int column) throws SQLException;
462

463
    /**
464
     * Indicates whether column is read-only.
465
     */
466
    boolean isReadOnly(int column) throws SQLException;
467

468
    /**
469
     * Indicates whether column is writable.
470
     */
471
    boolean isWritable(int column) throws SQLException;
472

473
    /**
474
     * Indicates whether write on column will definitely succeed.
475
     */
476
    boolean isDefinitelyWritable(int column) throws SQLException;
477

478
    /**
479
     * Returns the fully-qualified Java class name for column values.
480
     */
481
    String getColumnClassName(int column) throws SQLException;
482
}
483
```
484

485
**Usage Examples:**
486

487
```java
488
import org.postgresql.PGResultSetMetaData;
489
import java.sql.*;
490

491
// Example 1: Examine ResultSet metadata
492
public class MetadataExample {
493
    public static void printMetadata(ResultSet rs) throws SQLException {
494
        ResultSetMetaData meta = rs.getMetaData();
495
        int columnCount = meta.getColumnCount();
496

497
        System.out.println("Column count: " + columnCount);
498

499
        for (int i = 1; i <= columnCount; i++) {
500
            String name = meta.getColumnName(i);
501
            String label = meta.getColumnLabel(i);
502
            String type = meta.getColumnTypeName(i);
503
            int sqlType = meta.getColumnType(i);
504
            boolean nullable = (meta.isNullable(i) == ResultSetMetaData.columnNullable);
505

506
            System.out.printf("Column %d: %s (%s) [%s] - nullable: %b%n",
507
                i, label, name, type, nullable);
508
        }
509
    }
510
}
511

512
// Example 2: PostgreSQL-specific metadata
513
public class PGMetadataExample {
514
    public static void printPGMetadata(ResultSet rs) throws SQLException {
515
        PGResultSetMetaData pgMeta = rs.getMetaData().unwrap(PGResultSetMetaData.class);
516
        int columnCount = pgMeta.getColumnCount();
517

518
        for (int i = 1; i <= columnCount; i++) {
519
            String baseColumn = pgMeta.getBaseColumnName(i);
520
            String baseTable = pgMeta.getBaseTableName(i);
521
            String baseSchema = pgMeta.getBaseSchemaName(i);
522
            int format = pgMeta.getFormat(i);
523

524
            System.out.printf("Column %d: %s.%s.%s (format: %s)%n",
525
                i, baseSchema, baseTable, baseColumn,
526
                format == 0 ? "text" : "binary");
527
        }
528
    }
529
}
530

531
// Example 3: Dynamic column processing
532
public class DynamicProcessingExample {
533
    public static void processDynamically(ResultSet rs) throws SQLException {
534
        ResultSetMetaData meta = rs.getMetaData();
535
        int columnCount = meta.getColumnCount();
536

537
        while (rs.next()) {
538
            for (int i = 1; i <= columnCount; i++) {
539
                String columnName = meta.getColumnLabel(i);
540
                Object value = rs.getObject(i);
541

542
                if (rs.wasNull()) {
543
                    System.out.println(columnName + ": NULL");
544
                } else {
545
                    System.out.println(columnName + ": " + value);
546
                }
547
            }
548
            System.out.println("---");
549
        }
550
    }
551
}
552
```
553

554
### Type Mappings
555

556
PostgreSQL to Java type conversions.
557

558
| PostgreSQL Type | Java Type | ResultSet Method | Notes |
559
|----------------|-----------|------------------|-------|
560
| boolean | boolean | getBoolean() | |
561
| smallint | short | getShort() | Can also use getInt() |
562
| integer | int | getInt() | |
563
| bigint | long | getLong() | |
564
| real | float | getFloat() | |
565
| double precision | double | getDouble() | |
566
| numeric, decimal | BigDecimal | getBigDecimal() | Preserves precision |
567
| char, varchar, text | String | getString() | |
568
| bytea | byte[] | getBytes() | Binary data |
569
| date | java.sql.Date | getDate() | |
570
| time | java.sql.Time | getTime() | |
571
| timestamp | java.sql.Timestamp | getTimestamp() | |
572
| timestamptz | java.sql.Timestamp | getTimestamp() | With timezone |
573
| interval | PGInterval | getObject() | Cast to PGInterval |
574
| uuid | java.util.UUID | getObject() | getObject(col, UUID.class) |
575
| json, jsonb | String or PGobject | getString() | Parse as JSON |
576
| xml | java.sql.SQLXML | getSQLXML() | |
577
| array types | java.sql.Array | getArray() | PostgreSQL arrays |
578
| composite types | Object | getObject() | Custom mapping |
579
| hstore | Map | getObject() | Use HStoreConverter |
580
| point, box, etc. | PGpoint, PGbox | getObject() | Geometric types |
581
582
**Usage Examples:**
583

584
```java
585
// Example: Type conversions
586
public class TypeConversionExample {
587
    public static void convertTypes(ResultSet rs) throws SQLException {
588
        // Numeric types
589
        int intVal = rs.getInt("int_col");
590
        long longVal = rs.getLong("bigint_col");
591
        BigDecimal decVal = rs.getBigDecimal("numeric_col");
592

593
        // Date/time types
594
        Date date = rs.getDate("date_col");
595
        Timestamp timestamp = rs.getTimestamp("timestamp_col");
596

597
        // Binary data
598
        byte[] bytes = rs.getBytes("bytea_col");
599

600
        // UUID (PostgreSQL 9.4+)
601
        UUID uuid = rs.getObject("uuid_col", UUID.class);
602

603
        // Array
604
        Array array = rs.getArray("array_col");
605
        String[] strArray = (String[]) array.getArray();
606

607
        // JSON (as String)
608
        String json = rs.getString("json_col");
609

610
        // PostgreSQL types
611
        PGobject pgObj = (PGobject) rs.getObject("custom_col");
612
        String typeName = pgObj.getType();
613
        String value = pgObj.getValue();
614
    }
615
}
616
```
617

618
### Updateable ResultSets
619

620
Support for updating database through ResultSet.
621

622
```java { .api }
623
/**
624
 * Updateable ResultSet methods.
625
 * Available when ResultSet is created with CONCUR_UPDATABLE.
626
 */
627
public interface ResultSet {
628
    /**
629
     * Updates a column value in current row.
630
     */
631
    void updateString(int columnIndex, String x) throws SQLException;
632
    void updateInt(int columnIndex, int x) throws SQLException;
633
    void updateLong(int columnIndex, long x) throws SQLException;
634
    void updateDouble(int columnIndex, double x) throws SQLException;
635
    void updateTimestamp(int columnIndex, Timestamp x) throws SQLException;
636
    void updateNull(int columnIndex) throws SQLException;
637

638
    /**
639
     * Updates the underlying database with changes to current row.
640
     */
641
    void updateRow() throws SQLException;
642

643
    /**
644
     * Deletes the current row from underlying database.
645
     */
646
    void deleteRow() throws SQLException;
647

648
    /**
649
     * Inserts the contents of insert row into database.
650
     */
651
    void insertRow() throws SQLException;
652

653
    /**
654
     * Moves cursor to insert row (special row for building new rows).
655
     */
656
    void moveToInsertRow() throws SQLException;
657

658
    /**
659
     * Moves cursor back from insert row to previous position.
660
     */
661
    void moveToCurrentRow() throws SQLException;
662

663
    /**
664
     * Cancels updates made to current row.
665
     */
666
    void cancelRowUpdates() throws SQLException;
667

668
    /**
669
     * Refreshes current row with latest database values.
670
     */
671
    void refreshRow() throws SQLException;
672
}
673
```
674

675
**Usage Example:**
676

677
```java
678
// Updateable ResultSet example
679
public class UpdateableResultSetExample {
680
    public static void updateResults(Connection conn) throws SQLException {
681
        String sql = "SELECT id, name, email FROM users WHERE active = true";
682

683
        // Create updateable ResultSet
684
        try (Statement stmt = conn.createStatement(
685
                 ResultSet.TYPE_SCROLL_SENSITIVE,
686
                 ResultSet.CONCUR_UPDATABLE);
687
             ResultSet rs = stmt.executeQuery(sql)) {
688

689
            while (rs.next()) {
690
                String email = rs.getString("email");
691

692
                // Update email if needed
693
                if (email.endsWith("@old.com")) {
694
                    String newEmail = email.replace("@old.com", "@new.com");
695
                    rs.updateString("email", newEmail);
696
                    rs.updateRow(); // Commit changes to database
697
                }
698
            }
699
        }
700
    }
701

702
    public static void insertViaResultSet(Connection conn) throws SQLException {
703
        String sql = "SELECT id, name, email FROM users";
704

705
        try (Statement stmt = conn.createStatement(
706
                 ResultSet.TYPE_SCROLL_SENSITIVE,
707
                 ResultSet.CONCUR_UPDATABLE);
708
             ResultSet rs = stmt.executeQuery(sql)) {
709

710
            // Move to insert row
711
            rs.moveToInsertRow();
712

713
            // Set values for new row
714
            rs.updateString("name", "New User");
715
            rs.updateString("email", "new@example.com");
716

717
            // Insert into database
718
            rs.insertRow();
719

720
            // Move back to normal cursor
721
            rs.moveToCurrentRow();
722
        }
723
    }
724
}
725
```
726