Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchTo convert an arbitrary JDBC query result without creating a row class, read its column metadata, iterate through the ResultSet, and pass each value to a JSON library. The main decisions are the JSON shape, column naming, and how to handle SQL NULL and database-specific types.
Choose the JSON shape before writing the conversion
A common contract is an array of objects, with each object representing one row and each property named after a result column:
[{"id":1,"name":"Ada"},{"id":2,"name":"Lin"}]This is convenient for clients that want named values. Another option is a structure with a fields array describing the columns and a records array containing positional row values. That can avoid repeating column names on every row, but consumers must match each record position to its field. The jOOQ example in Baeldung’s JDBC-to-JSON tutorial uses this fields-and-records approach.
Agree on the contract with the JSON consumer before implementation. It determines how column names are represented and whether rows should be objects or arrays.
#1 Best Overall
Convert a ResultSet to an array of JSON objects
The following example uses JSON-Java (org.json). It reads the result metadata once, then reads each row from left to right. JSONObject.put stores Java null as JSON null.
import org.json.JSONArray;
import org.json.JSONObject;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public static JSONArray toJsonArray(ResultSet rs) throws SQLException {
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
JSONArray rows = new JSONArray();
while (rs.next()) {
JSONObject row = new JSONObject();
for (int column = 1; column <= columnCount; column++) {
String name = meta.getColumnLabel(column);
Object value = rs.getObject(column);
row.put(name, value);
}
rows.put(row);
}
return rows;
}Use this method while the result set is open; it does not close the result set or its statement. The caller should own and close those JDBC resources, normally with try-with-resources:
try (var statement = connection.createStatement();
var rs = statement.executeQuery("SELECT id, name FROM person")) {
JSONArray json = toJsonArray(rs);
writer.write(json.toString());
}Adapt the SQL and output destination to the application. If the result is large, this implementation retains every row in a JSONArray; use a streaming writer or an appropriate driver API when bounded memory matters.
Use metadata labels and make names unambiguous
ResultSetMetaData exposes information about result columns, including their count, types, and properties. getColumnLabel is useful when query aliases should become JSON keys. For example, SELECT first_name AS "firstName" can make the intended output name explicit. Check the chosen driver’s label behavior if casing or quoting matters.
Recommended Free Tools
Rank #3
Every key in a JSON object should identify one column. Joins and expressions can produce repeated labels; assign unique SQL aliases rather than relying on duplicate object keys. JDBC name-based access selects the first matching column when names repeat, and the Java SE API recommends explicit aliases when name lookup must be unique. See the Java SE 22 ResultSet API.
Handle nulls and JDBC value types deliberately
ResultSet.getObject returns Java null for SQL NULL and otherwise returns a Java object according to JDBC and driver mappings. A JSON library can serialize many ordinary Java values, but neither JDBC nor a generic conversion loop guarantees identical JSON representations for every SQL type or driver.
Rank #4
Test the types your queries actually return, especially decimals, date/time values, binary data, large objects, arrays, structured types, and database-native JSON. Decide whether dates should be strings in a specified format, whether binary values should be encoded, and how any unsupported vendor object should be represented. Avoid converting every value to a string without an explicit contract: doing so loses JSON numbers and booleans as native types.
Use a JSON library instead of concatenating strings by hand. A library handles JSON escaping for quotes, backslashes, control characters, and other string content; manual assembly must get those details and special values right.
Best Value
Choose an implementation that fits the application
| Approach | Useful when | Trade-off |
|---|---|---|
| Metadata-driven loop and JSON library | You need output for an arbitrary query without introducing a larger framework. | You define the output shape, alias policy, type handling, and memory strategy. |
| jOOQ result formatting | The application already uses jOOQ and its fields-and-records output suits the consumer. | It relies on a framework API and yields a different structure from an array of row objects. See Baeldung’s example. |
| Vendor-specific driver JSON API | The database or driver has a JSON type or conversion API suited to the result. | The implementation is tied to a database and driver version, reducing portability. |
| Streaming JSON writer or vendor Reader | Results may be large and retaining all rows in memory is undesirable. | You must manage JSON framing, output errors, and JDBC resource lifetimes as data is streamed. |
Check vendor-specific options when JSON is involved
Generic getObject conversion is not the only option. Database and driver APIs can provide special handling, particularly for native JSON columns. Confirm that an API applies to your database edition and installed driver version before depending on it.
- IBM Db2 for z/OS: IBM documents
DB2JSONResultSet, including incremental access through aReader. The documentation says the interface is available in IBM Data Server Driver for JDBC and SQLJ version 4.18 or later. See IBM’s DB2JSONResultSet documentation. - Microsoft SQL Server: Microsoft documents JSON data type handling in its JDBC driver. See Microsoft Learn’s JDBC JSON data type guidance.
- Oracle: Oracle JDBC documents JSON-aware
getObjectmethods. See Oracle’s JDBC JSON package documentation. - Neo4j: Neo4j JDBC describes optional Jackson mapping. See the Neo4j JDBC documentation.
Stream large results instead of retaining every row
The sample builds the entire JSON array in memory. For a response that could be large, write the opening bracket, serialize each row as it is read, separate rows with commas, and then write the closing bracket. A streaming JSON generator can handle the JSON syntax while the application emits each object. Ensure the response handles failures cleanly: an exception partway through output can leave incomplete JSON, and resources must still be closed.
There is no universal row-count cutoff for switching to streaming; the relevant limit depends on row size, available memory, and the application’s response path. For Db2-specific JSON workflows, IBM’s DB2JSONResultSet documentation describes incremental Reader access.
Keep the conversion portable and predictable
- Read columns from left to right and read each column only once per row; the Java SE API recommends this access pattern for portability.
- Use unique aliases for columns that would otherwise have duplicate labels.
- Keep the chosen JSON contract stable, especially for names, nulls, dates, decimals, and binary data.
- Test with the actual database, JDBC driver, and JSON library; mappings and vendor-object serialization are not universal.
- Close the statement and result set at the calling boundary, including when serialization fails.
The Java SE 22 ResultSet API documents metadata access and result retrieval behavior. For a worked generic conversion and the jOOQ alternative, see Baeldung’s tutorial, last updated January 8, 2024.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
