Overview
SQL to JSON mapping involves converting relational database results into JSON format. This is essential for REST APIs, data serialization, and integrating SQL data with web services.
Core Approaches
- Manual Mapping: Custom code for result set processing
- ORM Frameworks: Hibernate, JPA with JSON serialization
- Libraries: jOOQ, Jackson, Gson with custom mappers
- Database Native: PostgreSQL JSON functions, MySQL JSON functions
Basic Manual Mapping
1. Simple ResultSet to JSON
import java.sql.*;
import org.json.JSONArray;
import org.json.JSONObject;
public class SimpleSqlToJson {
public static JSONArray resultSetToJson(ResultSet rs) throws SQLException {
JSONArray jsonArray = new JSONArray();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
JSONObject jsonObject = new JSONObject();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
Object value = rs.getObject(i);
// Handle null values and specific types
if (value == null) {
jsonObject.put(columnName, JSONObject.NULL);
} else if (value instanceof java.sql.Date) {
jsonObject.put(columnName, value.toString());
} else if (value instanceof java.sql.Timestamp) {
jsonObject.put(columnName, value.toString());
} else {
jsonObject.put(columnName, value);
}
}
jsonArray.put(jsonObject);
}
return jsonArray;
}
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydb";
String user = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id, name, email FROM users")) {
JSONArray jsonResult = resultSetToJson(rs);
System.out.println(jsonResult.toString(2)); // Pretty print
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2. Advanced ResultSet Processor
import java.sql.*;
import java.util.*;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
public class AdvancedSqlToJson {
private static final ObjectMapper mapper = new ObjectMapper()
.enable(SerializationFeature.INDENT_OUTPUT)
.disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS);
public static String resultSetToJsonString(ResultSet rs) throws Exception {
List<Map<String, Object>> resultList = new ArrayList<>();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new LinkedHashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
Object value = getColumnValue(rs, metaData, i);
row.put(convertToCamelCase(columnName), value);
}
resultList.add(row);
}
return mapper.writeValueAsString(resultList);
}
private static Object getColumnValue(ResultSet rs, ResultSetMetaData metaData, int index)
throws SQLException {
String columnType = metaData.getColumnTypeName(index);
Object value = rs.getObject(index);
if (value == null) {
return null;
}
// Handle specific SQL types
switch (columnType.toUpperCase()) {
case "DATE":
return rs.getDate(index).toLocalDate().toString();
case "TIMESTAMP":
case "DATETIME":
return rs.getTimestamp(index).toInstant().toString();
case "TIME":
return rs.getTime(index).toLocalTime().toString();
case "DECIMAL":
case "NUMERIC":
return rs.getBigDecimal(index);
case "BLOB":
return Base64.getEncoder().encodeToString(rs.getBytes(index));
case "CLOB":
return rs.getString(index); // Already handled as string
default:
return value;
}
}
private static String convertToCamelCase(String columnName) {
if (columnName == null || columnName.isEmpty()) {
return columnName;
}
String[] parts = columnName.toLowerCase().split("_");
StringBuilder camelCase = new StringBuilder(parts[0]);
for (int i = 1; i < parts.length; i++) {
camelCase.append(Character.toUpperCase(parts[i].charAt(0)))
.append(parts[i].substring(1));
}
return camelCase.toString();
}
// Batch processing for large result sets
public static void processLargeResultSet(ResultSet rs, int batchSize,
Consumer<String> jsonConsumer) throws Exception {
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
List<Map<String, Object>> batch = new ArrayList<>();
int count = 0;
while (rs.next()) {
Map<String, Object> row = new LinkedHashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
row.put(convertToCamelCase(columnName), getColumnValue(rs, metaData, i));
}
batch.add(row);
count++;
if (count % batchSize == 0) {
String jsonBatch = mapper.writeValueAsString(batch);
jsonConsumer.accept(jsonBatch);
batch.clear();
}
}
// Process remaining records
if (!batch.isEmpty()) {
String jsonBatch = mapper.writeValueAsString(batch);
jsonConsumer.accept(jsonBatch);
}
}
@FunctionalInterface
public interface Consumer<T> {
void accept(T t) throws Exception;
}
}
Using ORM Frameworks
1. Hibernate with Jackson
import com.fasterxml.jackson.databind.ObjectMapper;
import org.hibernate.Session;
import org.hibernate.query.Query;
import javax.persistence.EntityManager;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
public class HibernateJsonMapper {
private static final ObjectMapper mapper = new ObjectMapper();
// Entity to JSON
public static <T> String entityToJson(T entity) {
try {
return mapper.writeValueAsString(entity);
} catch (Exception e) {
throw new RuntimeException("Failed to serialize entity to JSON", e);
}
}
// List of entities to JSON array
public static <T> String entitiesToJson(List<T> entities) {
try {
return mapper.writeValueAsString(entities);
} catch (Exception e) {
throw new RuntimeException("Failed to serialize entities to JSON", e);
}
}
// Native SQL query to JSON
public static String nativeQueryToJson(EntityManager em, String sql,
Map<String, Object> parameters) {
try {
Query query = em.unwrap(Session.class).createNativeQuery(sql);
// Set parameters
if (parameters != null) {
parameters.forEach(query::setParameter);
}
@SuppressWarnings("unchecked")
List<Object[]> results = query.getResultList();
// Convert to list of maps
List<Map<String, Object>> jsonList = results.stream()
.map(row -> {
Map<String, Object> map = new LinkedHashMap<>();
// This assumes you know the column names or can get them from metadata
for (int i = 0; i < row.length; i++) {
map.put("column" + (i + 1), row[i]);
}
return map;
})
.collect(Collectors.toList());
return mapper.writeValueAsString(jsonList);
} catch (Exception e) {
throw new RuntimeException("Failed to convert native query to JSON", e);
}
}
}
// Example Entity
@Entity
@Table(name = "users")
class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@Column(unique = true)
private String email;
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd")
@Column(name = "created_at")
private LocalDate createdAt;
// Constructors, getters, setters
public User() {}
public User(String firstName, String lastName, String email) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.createdAt = LocalDate.now();
}
// Getters and setters...
}
2. JPA Repository with JSON Support
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.fasterxml.jackson.databind.ObjectMapper;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;
import java.util.stream.Collectors;
@Repository
public class UserRepository {
@PersistenceContext
private EntityManager entityManager;
private final ObjectMapper mapper = new ObjectMapper();
// Custom query returning JSON string
public String findUsersAsJson() {
List<User> users = entityManager.createQuery("SELECT u FROM User u", User.class)
.getResultList();
try {
return mapper.writeValueAsString(users);
} catch (Exception e) {
throw new RuntimeException("Failed to convert users to JSON", e);
}
}
// Native SQL to JSON with custom mapping
public String findUserStatsAsJson() {
String sql = """
SELECT
u.first_name as firstName,
u.last_name as lastName,
COUNT(o.id) as orderCount,
SUM(o.amount) as totalAmount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.first_name, u.last_name
""";
@SuppressWarnings("unchecked")
List<Object[]> results = entityManager.createNativeQuery(sql).getResultList();
List<UserStats> stats = results.stream()
.map(row -> new UserStats(
(String) row[0],
(String) row[1],
((Number) row[2]).longValue(),
row[3] != null ? ((Number) row[3]).doubleValue() : 0.0
))
.collect(Collectors.toList());
try {
return mapper.writeValueAsString(stats);
} catch (Exception e) {
throw new RuntimeException("Failed to convert stats to JSON", e);
}
}
// DTO for JSON serialization
public static class UserStats {
private final String firstName;
private final String lastName;
private final long orderCount;
private final double totalAmount;
public UserStats(String firstName, String lastName, long orderCount, double totalAmount) {
this.firstName = firstName;
this.lastName = lastName;
this.orderCount = orderCount;
this.totalAmount = totalAmount;
}
// Getters
public String getFirstName() { return firstName; }
public String getLastName() { return lastName; }
public long getOrderCount() { return orderCount; }
public double getTotalAmount() { return totalAmount; }
}
}
Using jOOQ for SQL to JSON
1. jOOQ Native JSON Support
import org.jooq.*;
import org.jooq.impl.DSL;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.Connection;
import java.util.List;
import static org.jooq.impl.DSL.*;
public class JooqJsonMapper {
private final DSLContext dsl;
private final ObjectMapper mapper = new ObjectMapper();
public JooqJsonMapper(Connection connection) {
this.dsl = DSL.using(connection, SQLDialect.POSTGRES);
}
// Simple table to JSON
public String tableToJson(String tableName) {
try {
Result<Record> result = dsl.select().from(table(tableName)).fetch();
return result.formatJSON();
} catch (Exception e) {
throw new RuntimeException("Failed to convert table to JSON", e);
}
}
// Custom query to JSON with field selection
public String usersToJson() {
try {
return dsl
.select(
field("id"),
field("first_name").as("firstName"),
field("last_name").as("lastName"),
field("email")
)
.from(table("users"))
.where(field("active").eq(true))
.fetch()
.formatJSON();
} catch (Exception e) {
throw new RuntimeException("Failed to convert users to JSON", e);
}
}
// Complex query with joins to JSON
public String userOrdersToJson() {
try {
return dsl
.select(
field("u.first_name").as("userFirstName"),
field("u.last_name").as("userLastName"),
field("o.id").as("orderId"),
field("o.order_date").as("orderDate"),
field("o.total_amount").as("totalAmount"),
field("oi.product_name").as("productName"),
field("oi.quantity").as("quantity"),
field("oi.price").as("price")
)
.from(table("users").as("u"))
.join(table("orders").as("o")).on(field("u.id").eq(field("o.user_id")))
.join(table("order_items").as("oi")).on(field("o.id").eq(field("oi.order_id")))
.where(field("o.order_date").ge(currentDate().minus(30)))
.orderBy(field("u.last_name"), field("o.order_date").desc())
.fetch()
.formatJSON();
} catch (Exception e) {
throw new RuntimeException("Failed to convert user orders to JSON", e);
}
}
// Using jOOQ code generation for type-safe queries
public String typedUsersToJson() {
try {
// Assuming generated tables
Users u = USERS.as("u");
Orders o = ORDERS.as("o");
return dsl
.select(
u.FIRST_NAME.as("firstName"),
u.LAST_NAME.as("lastName"),
o.ID.as("orderId"),
o.ORDER_DATE.as("orderDate")
)
.from(u)
.join(o).on(u.ID.eq(o.USER_ID))
.where(u.ACTIVE.eq(true))
.fetch()
.formatJSON();
} catch (Exception e) {
throw new RuntimeException("Failed to convert typed users to JSON", e);
}
}
}
Database Native JSON Functions
1. PostgreSQL JSON Functions
import java.sql.*;
import org.json.JSONArray;
import org.json.JSONObject;
public class PostgresJsonMapper {
public static String queryToJsonUsingPostgres(Connection conn, String baseQuery)
throws SQLException {
// Use PostgreSQL's row_to_json and array_to_json functions
String jsonQuery = "SELECT json_agg(row_to_json(t)) FROM (" + baseQuery + ") t";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(jsonQuery)) {
if (rs.next()) {
return rs.getString(1);
}
return "[]";
}
}
public static String complexQueryToJson(Connection conn) throws SQLException {
String query = """
SELECT
json_build_object(
'userId', u.id,
'userName', u.first_name || ' ' || u.last_name,
'email', u.email,
'orders', (
SELECT json_agg(
json_build_object(
'orderId', o.id,
'orderDate', o.order_date,
'totalAmount', o.total_amount,
'items', (
SELECT json_agg(
json_build_object(
'productName', oi.product_name,
'quantity', oi.quantity,
'price', oi.price
)
)
FROM order_items oi
WHERE oi.order_id = o.id
)
)
)
FROM orders o
WHERE o.user_id = u.id
AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
)
) as user_data
FROM users u
WHERE u.active = true
ORDER BY u.last_name, u.first_name
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
JSONArray resultArray = new JSONArray();
while (rs.next()) {
String jsonData = rs.getString("user_data");
resultArray.put(new JSONObject(jsonData));
}
return resultArray.toString();
}
}
}
2. MySQL JSON Functions
public class MySqlJsonMapper {
public static String queryToJsonUsingMySql(Connection conn, String baseQuery)
throws SQLException {
// Use MySQL's JSON_OBJECT and JSON_ARRAYAGG functions
String jsonQuery = """
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id', id,
'firstName', first_name,
'lastName', last_name,
'email', email
)
) as json_result
FROM (""" + baseQuery + ") t";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(jsonQuery)) {
if (rs.next()) {
return rs.getString("json_result");
}
return "[]";
}
}
}
Spring Boot Integration
1. REST Controller with SQL to JSON
import org.springframework.web.bind.annotation.*;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/api")
public class SqlToJsonController {
private final JdbcTemplate jdbcTemplate;
private final ObjectMapper mapper;
public SqlToJsonController(JdbcTemplate jdbcTemplate, ObjectMapper mapper) {
this.jdbcTemplate = jdbcTemplate;
this.mapper = mapper;
}
@GetMapping("/users")
public ResponseEntity<String> getUsersAsJson() {
try {
String sql = "SELECT id, first_name, last_name, email FROM users WHERE active = true";
List<Map<String, Object>> users = jdbcTemplate.queryForList(sql);
String json = mapper.writeValueAsString(users);
return ResponseEntity.ok()
.contentType(org.springframework.http.MediaType.APPLICATION_JSON)
.body(json);
} catch (Exception e) {
return ResponseEntity.internalServerError()
.body("{\"error\": \"Failed to retrieve users\"}");
}
}
@GetMapping("/users/{id}/orders")
public ResponseEntity<String> getUserOrdersAsJson(@PathVariable Long id) {
try {
String sql = """
SELECT
o.id as orderId,
o.order_date as orderDate,
o.total_amount as totalAmount,
oi.product_name as productName,
oi.quantity,
oi.price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = ?
ORDER BY o.order_date DESC
""";
List<Map<String, Object>> orders = jdbcTemplate.queryForList(sql, id);
String json = mapper.writeValueAsString(orders);
return ResponseEntity.ok().body(json);
} catch (Exception e) {
return ResponseEntity.internalServerError()
.body("{\"error\": \"Failed to retrieve user orders\"}");
}
}
@GetMapping("/reports/sales")
public ResponseEntity<String> getSalesReportAsJson(
@RequestParam(defaultValue = "30") int days) {
try {
String sql = """
SELECT
DATE(o.order_date) as orderDate,
COUNT(o.id) as orderCount,
SUM(o.total_amount) as totalSales,
AVG(o.total_amount) as averageOrderValue
FROM orders o
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL ? DAY)
GROUP BY DATE(o.order_date)
ORDER BY orderDate DESC
""";
List<Map<String, Object>> report = jdbcTemplate.queryForList(sql, days);
String json = mapper.writeValueAsString(report);
return ResponseEntity.ok().body(json);
} catch (Exception e) {
return ResponseEntity.internalServerError()
.body("{\"error\": \"Failed to generate sales report\"}");
}
}
}
2. Custom JsonResultSetExtractor
import org.springframework.jdbc.core.ResultSetExtractor;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
@Component
public class JsonResultSetExtractor implements ResultSetExtractor<String> {
private final ObjectMapper mapper;
public JsonResultSetExtractor(ObjectMapper mapper) {
this.mapper = mapper;
}
@Override
public String extractData(ResultSet rs) throws SQLException {
List<Map<String, Object>> resultList = new ArrayList<>();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new LinkedHashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
Object value = rs.getObject(i);
row.put(columnName, value);
}
resultList.add(row);
}
try {
return mapper.writeValueAsString(resultList);
} catch (Exception e) {
throw new SQLException("Failed to convert result set to JSON", e);
}
}
// Version with custom field name mapping
public ResultSetExtractor<String> withFieldNameMapper(
java.util.function.Function<String, String> fieldNameMapper) {
return rs -> {
List<Map<String, Object>> resultList = new ArrayList<>();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new LinkedHashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
String fieldName = fieldNameMapper.apply(columnName);
Object value = rs.getObject(i);
row.put(fieldName, value);
}
resultList.add(row);
}
try {
return mapper.writeValueAsString(resultList);
} catch (Exception e) {
throw new SQLException("Failed to convert result set to JSON", e);
}
};
}
}
// Usage in Service class
@Service
public class UserService {
private final JdbcTemplate jdbcTemplate;
private final JsonResultSetExtractor jsonExtractor;
public UserService(JdbcTemplate jdbcTemplate, JsonResultSetExtractor jsonExtractor) {
this.jdbcTemplate = jdbcTemplate;
this.jsonExtractor = jsonExtractor;
}
public String getUsersAsJson() {
String sql = "SELECT user_id, first_name, last_name, email_address FROM users";
return jdbcTemplate.query(sql, jsonExtractor.withFieldNameMapper(
columnName -> {
// Convert snake_case to camelCase
return Arrays.stream(columnName.split("_"))
.map(part -> part.substring(0, 1).toUpperCase() + part.substring(1).toLowerCase())
.reduce("", String::concat);
}
));
}
}
Performance Optimization
1. Streaming Large Result Sets
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.*;
import java.io.OutputStream;
public class StreamingSqlToJson {
private final ObjectMapper mapper;
public StreamingSqlToJson(ObjectMapper mapper) {
this.mapper = mapper;
}
public void streamResultSetToJson(ResultSet rs, OutputStream outputStream)
throws Exception {
try (JsonGenerator generator = mapper.getFactory().createGenerator(outputStream)) {
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
generator.writeStartArray();
while (rs.next()) {
generator.writeStartObject();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
Object value = rs.getObject(i);
generator.writeFieldName(columnName);
if (value == null) {
generator.writeNull();
} else {
generator.writeObject(value);
}
}
generator.writeEndObject();
}
generator.writeEndArray();
}
}
// Batch streaming for very large datasets
public void streamLargeResultSetInBatches(Connection conn, String sql,
int batchSize, OutputStream outputStream)
throws Exception {
// Use server-side cursor for large result sets
conn.setAutoCommit(false);
Statement stmt = conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY
);
stmt.setFetchSize(batchSize);
try (ResultSet rs = stmt.executeQuery(sql);
JsonGenerator generator = mapper.getFactory().createGenerator(outputStream)) {
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
int recordCount = 0;
generator.writeStartArray();
while (rs.next()) {
generator.writeStartObject();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
Object value = rs.getObject(i);
generator.writeFieldName(columnName);
if (value == null) {
generator.writeNull();
} else {
generator.writeObject(value);
}
}
generator.writeEndObject();
recordCount++;
// Periodically flush to manage memory
if (recordCount % batchSize == 0) {
generator.flush();
}
}
generator.writeEndArray();
} finally {
conn.setAutoCommit(true);
}
}
}
Best Practices
- Use connection pooling for better performance
- Handle SQL types properly (dates, blobs, decimals)
- Use streaming for large datasets to avoid memory issues
- Implement proper error handling and logging
- Consider using database-native JSON functions when available
- Use connection timeouts for long-running queries
- Implement pagination for large result sets
- Cache frequently accessed data when appropriate
Common Issues and Solutions
- Memory Issues: Use streaming for large result sets
- Date Formatting: Use consistent date formats across the application
- Character Encoding: Ensure proper UTF-8 handling
- Null Values: Handle null values consistently in JSON
- SQL Injection: Use prepared statements for dynamic queries
- Connection Management: Use connection pools and proper resource cleanup
SQL to JSON mapping is essential for modern web applications, and choosing the right approach depends on your specific requirements, database technology, and performance needs.