sugaiketadao

Posted on Mar 7

SQL Directly in Java — I built a lightweight Java framework for Japan's "SI" projects (third attempt in 10 years) #10

#java #sql #framework #ai

Introduction

Over the years I've written SQL in XML files, embedded it in entity classes, and built it inline in Java. Each approach has trade-offs, but two pain points kept recurring:

Dynamic search conditions — the more conditions you add, the harder the XML <if> tags become to read
Batch processing performance — when INSERT/UPDATE runs inside a loop, you start worrying about PreparedStatement overhead

SIcore's answer is two SQL-building classes tailored to each use case:

SqlBuilder — assembles SQL dynamically (primarily for web service search queries)
SqlConst — defines SQL statically (primarily for batch processing)

This article introduces both and explains when to use each.

What This Article Covers

The friction of XML-based SQL management
SqlBuilder — how dynamic SQL assembly works
SqlConst — static SQL with type-safe binding
PreparedStatement caching with SqlConst
Combining with Java Text Blocks (""")

The Problem with XML-Based SQL

O/R mappers like MyBatis write SQL in XML files:

<select id="searchUsers" resultType="map">
  SELECT u.user_id, u.user_nm, u.email
  FROM t_user u
  WHERE 1=1
  <if test="userId != null and userId != ''">
    AND u.user_id = #{userId}
  </if>
  <if test="userNm != null and userNm != ''">
    AND u.user_nm LIKE '%' || #{userNm} || '%'
  </if>
  ORDER BY u.user_id
</select>

The content is readable enough, but several friction points appear before you can use it effectively:

SQL is separated into a different file (XML), making it hard to trace where it's used. On the Java side, mapping uses an ID string like searchUsers — renaming or deleting won't produce a compile error
You need to learn the XML tag syntax for <if test="..."> and friends
You constantly switch between the XML file and the Java class
The difference between #{} and ${} is a SQL injection trap — ${} embeds values directly

Dynamic SQL is where the friction really hits. The more conditions you add, the more tags pile up, and the harder the file becomes to read. And there's one more subtle annoyance: comparison operators like < and > must be written as XML entities (<, >) — easy to forget, and mildly infuriating every time.

SIcore takes a different approach: build SQL using a Java library with SQL injection protection built in.

SqlBuilder — Dynamic SQL Assembly

SqlBuilder assembles SQL and bind parameters together. It's designed for search queries in web services, where WHERE clause conditions are added only when the corresponding input has a value.

Basic Usage

final SqlBuilder sb = new SqlBuilder();
sb.addQuery("SELECT ");
sb.addQuery("  u.user_id ");
sb.addQuery(", u.user_nm ");
sb.addQuery(", u.email ");
sb.addQuery(" FROM t_user u ").addQuery(" WHERE 1=1 ");  // method chaining also works

// Add WHERE condition only if value is not blank
sb.addQnotB("   AND u.user_id = ? ", io.getString("user_id"));
sb.addQnotB("   AND u.user_nm LIKE '%' || ? || '%' ", io.getString("user_nm"));
sb.addQnotB("   AND u.email LIKE ? || '%' ", io.getString("email"));
sb.addQnotB("   AND u.income_am >= ? ", io.getBigDecimalNullable("income_am"));
sb.addQnotB("   AND u.birth_dt = ? ", io.getDateNullable("birth_dt"));

sb.addQuery(" ORDER BY u.user_id ");

// Execute the query
final IoRows rows = SqlUtil.selectBulk(getDbConn(), sb, 5);

addQnotB (Query if not Blank) skips the SQL fragment when the parameter is null or blank. That's the core of dynamic SQL assembly here.

Unlike XML's <if test="...">, the code looks the same regardless of which conditions are active. addQuery and addQnotB just line up uniformly — the SQL structure is readable directly from the code.

When complex branching is needed, you're not locked into addQnotB. You can mix in regular Java if statements with addQuery. Because it's standard Java code, it's easy to debug and easy for AI to parse.

Bind variables (?) are always used, so SQL injection is not a concern.

Variable-Length IN Clauses

Writing an IN clause in MyBatis requires the <foreach> tag, which surprises many developers. With addListInBind, you just pass a list and the placeholders expand automatically:

final List<Object> statusList = new ArrayList<>();
statusList.add("A");
statusList.add("B");
statusList.add("C");

sb.addQuery(" AND u.status_cs IN (");
sb.addListInBind(statusList);
sb.addQuery(")");
// Generated SQL: AND u.status_cs IN (?,?,?)
// ? expands to match list size; each value is passed as a bind parameter

Key Methods

Method	Description
`addQuery(sql)`	Append SQL only
`addQuery(sql, params...)`	Append SQL and parameters (multiple params allowed)
`addQnotB(sql, param)`	Append SQL and parameter only if param is not null/blank
`addParams(params...)`	Append parameters only
`addListInBind(list)`	Append variable IN clause placeholders and parameters
`addSqlBuilder(other)`	Merge another SqlBuilder into this one

SqlConst — Static SQL Definition

SqlConst defines fixed, immutable SQL. It shines in batch processing where the same SQL runs repeatedly.

Basic Usage (Declare as a Class Field)

private static final SqlConst SQL_INS_USER = SqlConst.begin()
    .addQuery("INSERT INTO t_user ( ")
    .addQuery("  user_id ")
    .addQuery(", user_nm ")
    .addQuery(", email ")
    .addQuery(", income_am ")
    .addQuery(", birth_dt ")
    .addQuery(" ) VALUES ( ")
    .addQuery("  ? ", "user_id",   BindType.STRING)
    .addQuery(", ? ", "user_nm",   BindType.STRING)
    .addQuery(", ? ", "email",     BindType.STRING)
    .addQuery(", ? ", "income_am", BindType.BIGDECIMAL)
    .addQuery(", ? ", "birth_dt",  BindType.DATE)
    .addQuery(" ) ")
    .end();

You chain calls from begin(), building SQL and bind definitions together, then call end() to produce an immutable SqlConst object. Declared as static final, it's built once at application startup.

Bind Types (BindType)

The mapping between SQL columns and bind types is declared explicitly:

BindType	SQL Type	Java Type
`STRING`	VARCHAR and other strings	`String`
`BIGDECIMAL`	INTEGER, NUMERIC, DECIMAL and other numerics	`BigDecimal`
`DATE`	DATE (yyyyMMdd format)	`java.sql.Date`
`TIMESTAMP`	TIMESTAMP (yyyyMMddHHmmssSSS format)	`java.sql.Timestamp`

When bind(io) is called, values are retrieved and converted using the corresponding io method for each BindType: getString, getBigDecimal, getDate, and getTimestamp.

Binding at Runtime

At execution time, bind(io) extracts values from a parameter map and produces a bound SqlBean:

// Extract values from io (map), bind them, and execute
SqlUtil.executeOne(getDbConn(), SQL_INS_USER.bind(io));

Internally, bind() pulls values from the map in the order declared at definition time, converting each to the declared type. The order and types guaranteed at declaration time are enforced at runtime — type mismatches and ordering errors are managed in the code itself.

PreparedStatement Caching with SqlConst

When INSERT/UPDATE runs inside a batch loop, the normal executeOne creates and destroys a PreparedStatement on every call. executeOneCache (or executeCache) caches and reuses the PreparedStatement, improving performance:

@Override
protected void doExecute(final IoItems io) throws Exception {
  // Read CSV row by row and UPSERT
  try (final CsvReader cr = new CsvReader(inputPath, CharSet.UTF8, CsvType.DQ_ALL_LF)) {
    for (final IoItems row : cr) {
      // Try UPDATE; if 0 rows affected, INSERT (UPSERT)
      if (!SqlUtil.executeOneCache(getDbConn(), SQL_UPD_USER.bind(row))) {
        SqlUtil.executeOneCache(getDbConn(), SQL_INS_USER.bind(row));
      }
    }
  }
}

The cache is held per DbConn instance. When the connection closes, the cache is released.

Method	Return	Description
`executeOne(conn, sb)`	`boolean`	Normal execution. `true` for 1 row, `false` for 0, exception for multiple
`executeOneCache(conn, sb)`	`boolean`	Cached execution. `SqlConst` only
`execute(conn, sb)`	`int`	Normal execution. Returns row count
`executeCache(conn, sb)`	`int`	Cached execution. Returns row count

SqlBuilder (dynamic SQL) is not eligible for caching because the SQL string changes on every call. Caching only makes sense for fixed SQL like SqlConst.

Can't MyBatis Cache Too?

MyBatis-style frameworks do support PreparedStatement caching. However, the mechanism is different.

Caching depends on the JDBC driver and its defaults vary by environment — MySQL Connector/J defaults to off (cachePrepStmts=true required), while PostgreSQL defaults to on per connection. Even when enabled, there's no way to see from code which SQL statements are cached, and dynamic SQL may end up cached unnecessarily.

SIcore's executeOneCache lets you explicitly nominate which SQL to cache at the application level. It works consistently across any JDBC driver, and reading the code alone tells you "this SQL is cached and reused."

Combining with Java Text Blocks

Java 15+ Text Blocks (""") let you write multi-line SQL strings naturally.

With SqlBuilder:

sb.addQuery("""
    SELECT
      u.user_id
    , u.user_nm
    , u.email
    FROM t_user u
    WHERE 1=1
    """);
sb.addQnotB("   AND u.user_id = ? ", io.getString("user_id"));

With SqlConst:

private static final SqlConst SQL_INS_USER = SqlConst.begin()
    .addQuery("""
        INSERT INTO t_user (
          user_id
        , user_nm
        , email
        , income_am
        , birth_dt
        ) VALUES (
        """)
    .addQuery("  ? ", "user_id",   BindType.STRING)
    .addQuery(", ? ", "user_nm",   BindType.STRING)
    .addQuery(", ? ", "email",     BindType.STRING)
    .addQuery(", ? ", "income_am", BindType.BIGDECIMAL)
    .addQuery(", ? ", "birth_dt",  BindType.DATE)
    .addQuery(" ) ")
    .end();

Separating the SQL block from the bind-definition lines keeps readability high while maintaining type-safe binding.

Note: the framework requires Java 11+, so confirm your project's Java version before adopting Text Blocks.

SqlBuilder vs SqlConst — When to Use Which

	SqlBuilder	SqlConst
SQL nature	Dynamic (changes by condition)	Static (fixed)
Primary use	Web service search queries	Batch INSERT/UPDATE
Bind type declaration	Not required (pass as Object)	Required (explicit BindType)
PS caching	Not supported (SQL changes per call)	Supported (`executeOneCache`)
Declaration location	Inside methods	`static final` class field

The rule is simple: dynamic conditions → SqlBuilder, fixed SQL → SqlConst.

SqlUtil also provides methods like insertOne, updateOne, and deleteOne that generate INSERT/UPDATE/DELETE SQL automatically from database metadata — just pass the table name and IoItems. These cover simple single-record operations without writing SQL at all.

Closing Thoughts

Both SqlBuilder and SqlConst ultimately produce a SqlBean (SQL string + parameter list) and hand it to SqlUtil — the same interface. The calling code doesn't change. This consistency is what makes the dynamic/static split feel natural rather than awkward.

Writing SQL in Java also means XML syntax to learn disappears, and SQL, conditions, and bind values all live in the same file. The code flow is straightforward to follow.

That advantage extends to AI-assisted development. In XML-based approaches, AI reading your Java code has to also load the XML file and interpret its tag syntax before understanding the query. With Java-inline SQL, SQL, branching, and bind values all live in the same file — one file read is enough to understand the entire operation, which leads to more accurate completions and refactoring suggestions.