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:
The code and library is available on GitHub. It would pay to read the comments as it shows examples.
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 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.
Assume the SQL query:
SELECT Name, Age FROM People;
with data:
GetFieldTypes(ResultSet) returns Map
Returns column names mapped to JDBC types. This does not affect the ResultSet.
GetFieldList(ResultSet) returns List
Returns column names in order. This does not affect the ResultSet.
Each row becomes a Map keyed by column name.
ToListOfLists(ResultSet, includeHeader) returns List
With includeHeader = true:
With includeHeader = false:
ToList(ResultSet) returns List
Returns first column only as a list.
ToMap(ResultSet) returns Map
First column as key, full row as value.
ToMap_KVP(ResultSet) returns Map
Two-column key/value mapping.
ToMap_IVP(ResultSet) returns Map
Row number (1-based) as key, first column as value.
ToDataSeries(ResultSet) returns Map
Two-column result split into series and data arrays. This aligns with many JavaScript charting libraries.
Features:
- Converts a ResultSet to a B4X List or Map
- Value types are correctly identified
- ResultSet automatically closed
- Helper methods to get field names and field types
B4X:
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.
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.
JSON:
{
"Name": "VARCHAR",
"Age": "INTEGER"
}GetFieldList(ResultSet) returns List
Returns column names in order. This does not affect the ResultSet.
JSON:
["Name", "Age"]Row-Based Conversions
ToListOfMaps(ResultSet) returns ListEach row becomes a Map keyed by column name.
JSON:
[
{ "Name": "John", "Age": 30 },
{ "Name": "Jane", "Age": 25 }
]ToListOfLists(ResultSet, includeHeader) returns List
With includeHeader = true:
JSON:
[
["Name", "Age"],
["John", 30],
["Jane", 25]
]With includeHeader = false:
JSON:
[
["John", 30],
["Jane", 25]
]Column-Based Conversions
ToList(ResultSet) returns List
Returns first column only as a list.
JSON:
["John", "Jane"]Map-Based Conversions
ToMap(ResultSet) returns Map
First column as key, full row as value.
JSON:
{
"John": { "Name": "John", "Age": 30 },
"Jane": { "Name": "Jane", "Age": 25 }
}ToMap_KVP(ResultSet) returns Map
Two-column key/value mapping.
JSON:
{
"John": 30,
"Jane": 25
}ToMap_IVP(ResultSet) returns Map
Row number (1-based) as key, first column as value.
JSON:
{
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.
JSON:
{
"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
Last edited: