B4J Library ResultSetConvertor

Since B4J came out over 10 years ago, I have amassed a load of B4J helper functions to convert SQL query ResultSets into Maps, Lists, JSON for various scenarios - e.g. return results as JSON via jServer. I decided to standardise and put them into a Java library.

Features:

1. Converts a ResultSet to a B4X List or Map
2. Value types are correctly identified
3. ResultSet automatically closed
4. Helper methods to get field names and field types

Code Example

Dim rsconv As ResultSetConverter

'Get list of ids
Dim ids_rs As ResultSet
ids_rs = Main.DB_RW.ExecQuery("select id from People")   
Dim ids As List = rsconv.ToList(ids_rs)

'ids will be a list of ids eg (1,2,3,4,5)

'Get list of maps
Dim people_rs As ResultSet
people_rs = Main.DB_RW.ExecQuery("select * from People")   
Dim people As List = rsconv.ToListOfMaps(people_rs)

'people will be a list of maps eg (Map,Map,Map)

The code and library is available on GitHub. It would pay to read the comments as it shows examples.

https://github.com/ope-nz/ResultSetConverter

NOTE ABOUT RESULTSET:
When a ResultSet is converted, it will be closed after reading. I implemented this as the ResultSet can usually only be read once, so there is no point in keeping it open after reading. I looked into being able to rewind the ResultSet but this didn't seem possible with the default SQL library.

NOTE ABOUT EXAMPLE BELOW:
The examples below show JSON for readability, but the return types are always B4J Map or List. These can easily be converted to JSON by using ".As(JSON)"

ResultSetConverter (B4J / JDBC Utility) * this is an AI Summary with some touch ups​

ResultSetConverter is a small Java utility library for B4J that converts JDBC ResultSet objects into B4X-friendly List and Map structures.

It removes repetitive JDBC iteration and type-handling code and is particularly useful for APIs, UI data binding, exports, and reporting.

Supported Conversions & Output Examples​

Assume the SQL query:

SELECT Name, Age FROM People;

with data:

Name	Age
John	30
Jane	25

---

Metadata Helpers​

GetFieldTypes(ResultSet) returns Map
Returns column names mapped to JDBC types. This does not affect the ResultSet.

{
  "Name": "VARCHAR",
  "Age": "INTEGER"
}

GetFieldList(ResultSet) returns List
Returns column names in order. This does not affect the ResultSet.

["Name", "Age"]

Row-Based Conversions​

ToListOfMaps(ResultSet) returns List
Each row becomes a Map keyed by column name.

[
  { "Name": "John", "Age": 30 },
  { "Name": "Jane", "Age": 25 }
]

ToListOfLists(ResultSet, includeHeader) returns List

With includeHeader = true:

[
  ["Name", "Age"],
  ["John", 30],
  ["Jane", 25]
]

With includeHeader = false:

[
  ["John", 30],
  ["Jane", 25]
]

Column-Based Conversions​

ToList(ResultSet) returns List
Returns first column only as a list.

["John", "Jane"]

Map-Based Conversions​

ToMap(ResultSet) returns Map
First column as key, full row as value.

{
  "John": { "Name": "John", "Age": 30 },
  "Jane": { "Name": "Jane", "Age": 25 }
}

ToMap_KVP(ResultSet) returns Map
Two-column key/value mapping.

{
  "John": 30,
  "Jane": 25
}

ToMap_IVP(ResultSet) returns Map
Row number (1-based) as key, first column as value.

{
  1: "John",
  2: "Jane"
}

Chart / Series Conversion​

ToDataSeries(ResultSet) returns Map
Two-column result split into series and data arrays. This aligns with many JavaScript charting libraries.

{
  "series": ["John", "Jane"],
  "data": [30, 25]
}

Type Handling​

The library inspects ResultSetMetaData and converts values appropriately:

• INTEGER, SMALLINT → int
• BIGINT → long
• FLOAT / DOUBLE → numeric
• DECIMAL / NUMERIC → BigDecimal
• BOOLEAN / BIT → boolean
• DATE → "yyyy-MM-dd"
• TIME → "HH:mm:ss"
• TIMESTAMP → ISO-8601 UTC string
  (yyyy-MM-dd'T'HH:mm:ss.SSSZ)
• Unknown types → String

Resource Management & Design Notes​

• All conversion methods consume and close the ResultSet
• Errors are logged via Common.Log (no exceptions thrown)
• No external dependencies beyond JDBC and B4X runtime
• Designed for read-only, single-pass ResultSets

Typical Use Cases​

• REST / API responses
• UI data binding
• CSV / Excel exports
• Charting and analytics
• Rapid JDBC prototyping in B4J