Java: Long SQL Can Cause Problems

StackOverflowError Cause & Solution

1. Summary

The StackOverflowError in your EnergyHourDetailJobConfiguration was not caused by recursive method calls or circular references, but rather by extremely long SQL strings being built and processed in memory.

This happened because

• The SQL query contains hundreds of fields (INSERT ... ON CONFLICT ... DO UPDATE).

• The query is built entirely in Java using StringBuilder.append() calls.

• The query is logged in full using log.info(), which forces the JVM to create a huge string for logging.

2. Why Long SQL Can Cause Problems

1. Logging overhead

   • Printing a very large SQL string in logs can consume a lot of memory and CPU, especially if the logging framework tries to format or process the string.

2. Driver limitations

   • Some JDBC drivers or databases have limits on SQL statement length.
     PostgreSQL can handle large queries, but certain environments or configurations may impose limits.

3. Memory usage

   • Building a huge string in memory can cause excessive garbage collection or even OutOfMemoryError.
     In rare cases, if the logging framework recursively processes the string, it can trigger StackOverflowError.

3. Recommended Fixes

A. Avoid logging the full SQL

Instead of:

log.info(">>>>> query : " + sql);

Use:

if (sql.length() > 2000) {
    log.info("SQL length: {}", sql.length());
    log.debug("SQL preview: {}...", sql.substring(0, 500));
} else {
    log.info("SQL: {}", sql);
}

This logs only the length and a short preview.

B. Move SQL to an external file

Store the SQL in src/main/resources/sql/energy_hour_detail_insert.sql and load it:

String sql = Files.readString(Paths.get("classpath:sql/energy_hour_detail_insert.sql"));

Advantages:

• Cleaner Java code.

• Easier SQL maintenance.

• No huge inline strings in Java.

C. Use SQL templates

Instead of hardcoding hundreds of .append() calls, store field names in arrays and use:

String insertFields = String.join(",", fieldList);
String updateFields = fieldList.stream()
    .map(f -> f + " = EXCLUDED." + f)
    .collect(Collectors.joining(", "));

This reduces code size and makes changes easier.

D. Reduce fields in ON CONFLICT DO UPDATE

If some fields rarely change, exclude them from the update clause to shorten the SQL.

4. Conclusion

The error was caused by overly long SQL strings being built and logged.

To fix:

• Limit SQL logging.

• Move SQL to external files.

• Use dynamic generation for repetitive field lists.

• Consider reducing the number of fields in the query.