Data Manipulation

Author: Eike Stepper

Net4j DB's structured DML API represents statements as schema-aware definitions, compiles them for an adapter, and binds application values to prepared JDBC statements. A template is independent of a connection. Its compiled statement records rendered SQL and ordered parameter occurrences, while a managed connection prepares and caches JDBC statements. The caller owns each prepared statement checkout and its result sets.

The fluent interfaces constrain clause order and keep identifiers connected to schema metadata. Parameter objects keep values separate from SQL text. Structured fragments can combine raw SQL with structured expressions, but raw fragment text remains trusted input and is not made portable or safe automatically.

Most examples use a SALES schema containing CUSTOMER(ID, NAME, EMAIL) and ORDERS(ID, CUSTOMER_ID, TOTAL, STATUS). Snippets accept schema objects and managed resources as method arguments; they do not create a database or define an application's transaction policy.

1  Introduction

Structured DML separates statement shape from values and connection state. This makes definitions composable and reusable while leaving connection ownership and transaction boundaries with the application.

1.1  Concepts and Architecture

SQL creates expressions, conditions, fragments, parameters, and statement builders. An SQLTemplate is the immutable adapter-neutral definition captured by build(). Compiling it with an IDBAdapter produces an SQLStatement containing rendered SQL and metadata for each JDBC parameter occurrence. An IDBConnection prepares that SQL and returns a managed IDBPreparedStatement; execution and result reading use JDBC through the Net4j DB wrappers.

An SQLExpression represents a value and an SQLCondition represents a predicate. Staged interfaces in org.eclipse.net4j.db.dml.fluent expose the next legal operation. Builders are mutable while assembled, but an earlier stage becomes stale after advancing. Each call to build() captures its own snapshot, so later valid changes do not rewrite an earlier template.

1.2  The DML Lifecycle

A typical operation creates a template from schema objects, compiles it once for the selected adapter, prepares it on a managed connection, binds all required values, executes it, and closes the result set and statement. Closing a managed prepared statement returns it to that connection's cache. A template or compiled statement can be reused independently of a checked-out JDBC statement.

Binding calls can change some JDBC positions before a later converter or setter fails. Net4j DB validates structural ranges before starting a sequence, but it cannot undo JDBC side effects already performed by an earlier binding operation. Transaction rollback remains the application's responsibility.

1.3  Definitions, Templates, and Compilation

Build a SQLTemplate without selecting a database connection. Compilation uses the adapter for SQL rendering and identifier quoting, and records an inferred DBType for each parameter occurrence when its schema context provides one. An explicit JDBC target type remains part of the parameter definition and is honored during binding. Default NULL binding is a later phase: when an inferred type is known, the adapter selects the JDBC NULL code through getJDBCTypeForNull(). Compilation cannot translate vendor-specific raw fragment text into another dialect. The same template can be compiled for another adapter.

2  Basic Statements

The statement builders cover common SELECT and data-change forms. Keep values as parameters and represent tables and columns with schema metadata so the compiler can render identifiers for the selected adapter.

2.1  SELECT

A SELECT projects fields or expressions. The example compiles a key lookup, binds the key, executes it, and closes both managed resources. The returned statement can be prepared again for another lookup.

	FindCustomers.java
// Inputs: registered adapter, open managed connection, CUSTOMER table, and CUSTOMER.ID field. SQLParameter requestedID = SQL.param(); SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(customerID).eq(requestedID) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.HIGH)) {   statement.bind(prepared, 42);   try (IDBResultSet rows = prepared.executeQuery())   {     while (rows.next())     {       System.out.println("Customer ID: " + rows.getInt("ID"));     }   } }

2.2  INSERT and INSERT SELECT

An explicit column list pairs each value with its named destination column. INSERT SELECT instead copies a query result into the target table; parameters in the SELECT remain ordinary compiled occurrences.

The examples insert a customer row and copy selected orders for one customer:

	InsertCustomer.java
// Inputs: adapter, connection, CUSTOMER table and its ID/NAME fields. SQLTemplate template = SQL.insertInto(customer) //     .columns(customerID, name) //     .values(SQL.param(), SQL.param()) //     .build(); SQLStatement statement = template.compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindAll(prepared, 501, "Ada Lovelace");   prepared.executeUpdate();   connection.commit(); } catch (SQLException | RuntimeException ex) {   connection.rollback();   throw ex; }

	CopyOrders.java
// Inputs: adapter, connection, source ORDERS.ID/CUSTOMER_ID, and matching target fields. SQLParameter customerID = SQL.param(); SQLStatement statement = SQL.insertInto(targetOrders) //     .columns(targetID, targetCustomerID) //     .select(SQL.select(sourceID, sourceCustomerID) //         .from(sourceOrders) //         .where(sourceCustomerID).eq(customerID) //         .subSelect()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, 501);   prepared.executeUpdate(); }

2.3  UPDATE

An UPDATE names assigned fields in set() and limits affected rows with where(). The values are bound in rendered occurrence order: the new status first, then the order key.

	UpdateOrderStatus.java
// Inputs: adapter, connection, ORDERS table and its ID/STATUS fields. SQLStatement statement = SQL.update(orders) //     .set(status).to(SQL.param()) //     .where(orderID).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.HIGH)) {   statement.bind(prepared, "SHIPPED", 9001L);   prepared.executeUpdate();   connection.commit(); } catch (SQLException | RuntimeException ex) {   connection.rollback();   throw ex; }

2.4  DELETE

A DELETE may omit its WHERE clause and affect every row. Prefer an explicit restrictive condition when removing a single record, as this key-based example does.

	DeleteOrder.java
// Inputs: adapter, connection, ORDERS table, and ORDERS.ID. This delete is restricted to one key. SQLStatement statement = SQL.deleteFrom(orders) //     .where(orderID).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, 9001L);   prepared.executeUpdate();   connection.commit(); } catch (SQLException | RuntimeException ex) {   connection.rollback();   throw ex; }

2.5  Structured Expressions, IN Lists, and Table Providers

Use SQL.abs(SQLExpression) for a structured absolute-value expression, and use the IN factories or fluent value-list stage for ordered membership values. An IDBTable.Provider lets a domain wrapper supply its primary schema table directly to DML construction; the table is resolved when the API call is made.

These examples cover a structured function, fixed and iterable IN lists, incremental list construction, and a table provider:

	SelectByAbsoluteVersion.java
return SQL.selectAll() //     .from(table) //     .where(SQL.abs(version)).eq(SQL.param()) //     .compile(adapter);

	SelectForFixedRevisions.java
return SQL.selectAll() //     .from(table) //     .where(revision).in(SQL.literal(1), SQL.param()) //     .compile(adapter);

	SelectForRevisions.java
return SQL.selectAll() //     .from(table) //     .where(revision).in(values) //     .compile(adapter);

	SelectForRevisionsIncrementally.java
SQLInStep<SQLSelectWhereStep> pending = SQL.selectAll() //     .from(table) //     .where(revision).in(); pending.addAll(values); return pending //     .end() //     .compile(adapter);

	SelectFromProvider.java
return SQL.select(id) //     .from(lockAreas) //     .where(id).eq(SQL.param()) //     .compile(adapter);

3  Queries and Composition

Conditions, table references, subqueries, and fragments allow a query to be assembled from reusable parts. Structured elements retain metadata for rendering and binding; raw fragment text remains opaque to the compiler.

3.1  Conditions and Optional Filters

Conditions are immutable values that can be named and combined before attaching them to a WHERE stage. The final condition tree determines SQL order and therefore positional binding order. Build only complete conditions, and add optional predicates only when their values are part of the request.

	FindOrders.java
// Inputs: adapter, connection, ORDERS table, STATUS/TOTAL fields, and the filter choice. SQLParameter statusValue = SQL.param(); SQLCondition filter = SQL.eq(status, statusValue); SQLParameter minimumValue = null; if (filterByMinimum) {   minimumValue = SQL.param();   filter = filter.and(SQL.ge(total, minimumValue)); } SQLStatement statement = SQL.selectAll() //     .from(orders) //     .where(filter) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.HIGH)) {   if (filterByMinimum)   {     statement.bind(prepared, "OPEN", new BigDecimal("100.00"));   }   else   {     statement.bind(prepared, "OPEN");   }   try (IDBResultSet rows = prepared.executeQuery())   {     while (rows.next())     {       System.out.println("Order total: " + rows.getBigDecimal("TOTAL"));     }   } }

3.2  Aliases and Joins

A SQLTableRef represents one occurrence of a table and can qualify its columns. Table aliases distinguish source columns in joins; aliases on projected expressions label output values instead.

	CustomerOrderJoin.java
// Inputs: adapter, connection, CUSTOMER.ID, and ORDERS.CUSTOMER_ID. SQLTableRef c = customer.as("c"); SQLTableRef o = orders.as("o"); SQLStatement statement = SQL.select(c.column(customerID), o.column(orderCustomerID)) //     .from(c) //     .join(o) //     .on(c.column(customerID)).eq(o.column(orderCustomerID)) //     .orderBy(SQL.asc(c.column(customerID))) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW); //     IDBResultSet rows = prepared.executeQuery()) {   while (rows.next())   {     System.out.println("Customer/order key: " + rows.getInt(1) + "/" + rows.getInt(2));   } }

3.3  Aggregates, Grouping, and HAVING

Aggregate expressions can be projected and used in HAVING. This query groups orders by status and applies a parameterized minimum count after grouping. Aggregate and grouping support still depends on the target SQL dialect and adapter.

	SummarizeOrders.java
// Inputs: adapter, connection, ORDERS table, STATUS and TOTAL fields. SQLStatement statement = SQL.select(status, SQL.count().as("ORDER_COUNT"), SQL.sum(total).as("GROSS_TOTAL")) //     .from(orders) //     .where(status).eq(SQL.param()) //     .groupBy(status) //     .having(SQL.count()).gt(SQL.param()) //     .orderBy(SQL.asc(status)) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, "CLOSED", 0);   try (IDBResultSet rows = prepared.executeQuery())   {     while (rows.next())     {       System.out.println(rows.getString("STATUS") + ": " + rows.getLong("ORDER_COUNT"));     }   } }

3.4  Subqueries

A complete SELECT can be converted to SQLSubSelect and used in supported IN, EXISTS, or scalar contexts. Its parameters join the outer statement's rendered occurrence order. A subquery is preferable to raw SQL when the structured API can express the required shape.

	CustomersWithOpenOrders.java
// Inputs: adapter, connection, CUSTOMER.ID, ORDERS.CUSTOMER_ID, and ORDERS.STATUS. SQLSubSelect openCustomers = SQL.select(orderCustomerID) //     .from(orders) //     .where(status).eq(SQL.param()) //     .subSelect(); SQLStatement statement = SQL.select(customerID) //     .from(customer) //     .where(customerID).in(openCustomers) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, "OPEN");   try (IDBResultSet rows = prepared.executeQuery())   {     while (rows.next())     {       System.out.println("Customer with open order: " + rows.getInt(1));     }   } }

3.5  SQL Fragments

Fragments fill a gap when a query shape is not represented by the structured builders. Keep tables, columns, and parameter tokens structured inside a fragment. Raw text is neither parsed nor escaped, and must not contain question marks; represent every placeholder with a parameter token so occurrence metadata remains aligned with JDBC indexes.

	SearchByNameFragment.java
// Inputs: adapter, connection, CUSTOMER table and NAME field. // The raw SQL tokens are trusted syntax. SQLParameter nameValue = SQL.param(); SQLFragment fragment = SQL.fragment() //     .sql("SELECT ").column(name) //     .sql(" FROM ").table(customer) //     .sql(" WHERE ").column(name).sql(" = ").param(nameValue) //     .build(); SQLStatement statement = SQL.build(fragment).compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindParameter(prepared, nameValue, "Ada Lovelace");   try (IDBResultSet rows = prepared.executeQuery())   {     while (rows.next())     {       System.out.println("Customer name: " + rows.getString(1));     }   } }

4  Parameter Binding

A logical parameter is a reusable identity in a definition; compilation creates one occurrence record for each place it appears in rendered SQL. The occurrence supplies the physical JDBC index and any type inferred from its local context. Binding APIs address values by JDBC order, JDBC index, or logical identity.

4.1  Fundamentals

Sequential calls bind occurrence order. Indexed calls address one physical placeholder. Identity calls find all occurrences of the same SQLParameter object. These choices matter when a logical parameter is reused in different field contexts, because each physical occurrence can infer a different DBType.

4.1.1  Parameters and Occurrences

Calling SQL.param() creates a logical parameter object. If that object is used twice, compilation creates two occurrences with separate JDBC indexes. A field comparison or field assignment may let the compiler infer a type for an occurrence. That type belongs to the occurrence, not to the logical parameter; the same identity can therefore have a VARCHAR occurrence and an INTEGER occurrence.

4.1.2  Positional and Identity-Based Binding

Positional values are assigned in rendered JDBC order. Binding by JDBC index changes one occurrence, while binding by parameter identity changes every occurrence of that exact object in rendered order. The examples preserve the earlier customer lookup and repeated-identity patterns.

	BindPositionalParameters.java
// Inputs: adapter, connection, CUSTOMER.ID and CUSTOMER.NAME. SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(customerID).eq(SQL.param()) //     .and(name).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   int bound = statement.bind(prepared, 501, "Ada Lovelace");   System.out.println("Bound occurrences: " + bound); }

	BindRepeatedParameter.java
// Inputs: adapter, connection, CUSTOMER table, and its NAME/EMAIL fields. SQLParameter sharedValue = SQL.param(); SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(name).eq(sharedValue) //     .or(email).eq(sharedValue) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindParameter(prepared, sharedValue, "ada@example.test"); }

4.1.3  Sequential and Cursor-Based Binding

bind() accepts a partial prefix and returns the number of values it bound. bindAll() requires the complete placeholder count. bindFrom() starts a partial sequence at a specified one-based index. A SQLParameterBinder owns an independent cursor: bind() advances after success, getNextIndex() reports the next JDBC position, skip() moves without binding, and from(index) repositions absolutely. The final legal position is parameter count plus one and accepts only an empty sequence.

The examples bind complete and partial sequences, start a partial sequence at a chosen position, and navigate the independent binder cursor:

	BindCompleteAndPartialSequences.java
// Inputs: adapter, connection, CUSTOMER table and ID/NAME fields. SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(customerID).eq(SQL.param()) //     .and(name).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   int partialCount = statement.bind(prepared, 501);   int completeCount = statement.bindAll(prepared, 502, "Ada");   System.out.println("Partial count: " + partialCount + "; complete count: " + completeCount); }

	BindFromPosition.java
// Inputs: adapter, connection, CUSTOMER table, and ID/NAME/EMAIL fields. SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(customerID).eq(SQL.param()) //     .and(name).eq(SQL.param()) //     .and(email).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, 501);   int boundFromSecond = statement.bindFrom(prepared, 2, "Ada", "ada@example.test");   System.out.println("Bound from position 2: " + boundFromSecond); }

	BindWithCursor.java
// Inputs: adapter, connection, CUSTOMER table and ID/NAME fields. SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(customerID).eq(SQL.param()) //     .and(name).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   SQLParameterBinder binder = statement.binder(prepared);   binder.bind(501);   System.out.println("Next position: " + binder.getNextIndex());   binder.bind("Ada"); }

	NavigateBindingCursor.java
// Inputs: adapter, connection, ORDERS table and its ID, CUSTOMER_ID, TOTAL, and STATUS fields. SQLStatement statement = SQL.selectAll() //     .from(orders) //     .where(orderID).eq(SQL.param()) //     .and(customerID).eq(SQL.param()) //     .and(total).eq(SQL.param()) //     .and(status).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   SQLParameterBinder binder = statement.binder(prepared);   binder //       .bind(9001L) //       .skip() //       .skip(1) //       .bind("OPEN");   System.out.println("Next position: " + binder.getNextIndex());   binder //       .from(2) //       .bind(501) //       .bind(new BigDecimal("100.00")); }

4.2  Types and Default Binding

The compiler infers a logical database type only when the occurrence is connected to a known field context, such as a direct field comparison, BETWEEN bound, or INSERT/UPDATE assignment. Arbitrary expressions, projections, and raw fragment tokens may not provide enough context. Explicit JDBC types can handle NULL where inference is unavailable.

4.2.1  DBType Inference

Type inference is local to the compiled occurrence. A parameter assigned to a field or compared directly with a field inherits that field's DBType. BETWEEN uses the known field for both bounds. A parameter in an arbitrary projection or a fragment does not necessarily have an inferable type. For an untyped NULL, structured binding rejects the value if no occurrence type is available.

4.2.2  Default JDBC Setters

Ordinary inferred values pass through CompiledStatement to IDBAdapter.bindParameter(). DBAdapter chooses a typed setter only for a supported DBType and compatible Java value. A non-NULL incompatible combination falls back to generic setObject(); it does not silently convert the Java value. Known-type NULL uses bindParameterNull() and getJDBCTypeForNull(). For unknown-type non-NULL values, Java-type dispatch is controlled by useJavaTypeForUnknownParameterType(), which defaults to false.

BLOB, CLOB, and temporal DBTypes have no automatic specialized mapping in this dispatch. In particular, BLOB does not select setBytes() merely because a byte array is supplied.

The example binds NULL using the field-derived type:

	BindInferredNull.java
// Inputs: adapter, connection, CUSTOMER table, and its EMAIL and ID fields. EMAIL supplies the NULL's // inferred DBType. SQLStatement statement = SQL.update(customer) //     .set(email).to(SQL.param()) //     .where(customerID).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindAll(prepared, (Object)null, 501); }

4.2.3  NULL and Explicit JDBC Types

SQL.param(sqlType) stores a JDBC Types code. For a non-NULL value, the compiled statement calls setObject(index, value, sqlType); for NULL it calls setNull(index, sqlType). This is different from a custom SQLValueSetter: SQL.param(Types.BLOB) does not request PreparedStatement.setBytes(). Use a configured setter such as SQL.param(PreparedStatement::setBytes) when the complete binding operation must use that JDBC method.

	BindExplicitlyTypedNull.java
// Inputs: adapter, connection, and CUSTOMER. // A projection parameter has no field from which to infer DBType. SQLExpression projectedValue = SQL.param(Types.VARCHAR); SQLStatement statement = SQL.select(projectedValue) //     .from(customer) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindAll(prepared, (Object)null); }

4.3  Custom Binding Strategies

A converter transforms an application value and leaves JDBC binding to the adapter. A setter receives the original value and performs the complete JDBC operation. Call-specific strategies override configured ones for one operation. The diagram shows how CompiledStatement resolves those strategies before default binding.

4.3.1  Value Converters

A converter maps a non-NULL application value to the value supplied to default binding. NULL input bypasses conversion and follows the occurrence's explicit or inferred JDBC NULL typing. Converter exceptions propagate to the caller.

The first example configures a converter; the second overrides it for one binding call:

	BindConvertedValue.java
// Inputs: adapter, connection, CUSTOMER table and NAME field. SQLParameter trimmedName = SQL.param((String value) -> value.trim()); SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(name).eq(trimmedName) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, " Ada Lovelace "); }

	OverrideValueConverter.java
// Inputs: adapter, connection, CUSTOMER table and NAME field. SQLParameter configuredTrim = SQL.param((String value) -> value.trim()); SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(name).eq(configuredTrim) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindParameter(prepared, 1, " ada ", String::toUpperCase); }

See Also:

• SQL.param(Function)
• SQL.param(int, Function)
• SQLStatement.bindParameter(PreparedStatement, int, Object, Function)
• SQLStatement.bindParameter(PreparedStatement, SQLParameter, Object, Function)
• SQLParameterBinder.bindParameter(Object, Function)

4.3.2  SQLValueSetter

A configured or explicit SQLValueSetter owns the full JDBC operation and receives the original application value, including NULL. It bypasses converter and adapter binding for that occurrence. The default byte-array mapping covers BINARY, VARBINARY, and LONGVARBINARY, not BLOB. SQL.param(Types.BLOB) selects explicit target typing through setObject(..., Types.BLOB) or setNull(..., Types.BLOB); it does not select setBytes(). An explicitly configured SQLValueSetter guarantees the chosen JDBC operation and can be useful for driver-specific BLOB compatibility, although drivers do not universally require setBytes() for BLOB.

One example configures setBytes() for a BLOB field; the other supplies a setter for a single call:

	BindWithConfiguredSetter.java
// Inputs: adapter, connection, ATTACHMENT table whose PAYLOAD is modeled as DBType.BLOB, and bytes to store. SQLParameter payloadValue = SQL.param(PreparedStatement::setBytes); SQLStatement statement = SQL.insertInto(attachment) //     .set(payload).to(payloadValue) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindParameter(prepared, payloadValue, bytes);   prepared.executeUpdate(); }

	OverrideValueSetter.java
// Inputs: adapter, connection, CUSTOMER table and NAME field. SQLParameter parameter = SQL.param((String value) -> value.trim()); SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(name).eq(parameter) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindParameter(prepared, parameter, "Ada", //       (stmt, index, value) -> stmt.setString(index, "[" + value + "]")); }

4.3.3  Overrides and Precedence

A call-specific setter runs first and receives the original value. Otherwise a call-specific converter, when supplied, transforms the value and takes precedence over a parameter-configured setter. Without a call-level strategy, the configured setter runs before a configured converter. If no setter handles the value, the explicit JDBC type controls typed setObject/setNull; otherwise default adapter binding handles it. Converters are applied only to non-NULL values. A setter and converter cannot both be configured on one SQLParameter.

The order in this table is precedence for one binding operation. A selected setter performs the complete operation and exits the default path.

An explicit converter therefore overrides a configured setter for that call, while a call-specific setter overrides both. Explicit JDBC types apply only when a setter has not already handled the value.

4.3.4  SQLDefaultSetter

An SQLDefaultSetter receives a statement and JDBC index, but no application value. It performs the complete binding operation for an implementation-defined default. That default is not necessarily NULL or the SQL column's DEFAULT expression. It may be a domain value such as an empty byte array. This strategy is separate from binding a supplied value through SQLValueSetter or the default adapter path.

The examples use a binder cursor and parameter identity to apply implementation-defined defaults:

	BindImplementationDefault.java
// Inputs: adapter, connection, CUSTOMER table, and NAME/EMAIL fields. SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(name).eq(SQL.param()) //     .and(email).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   SQLValueSetter<String> valueSetter = PreparedStatement::setString;   SQLDefaultSetter defaultSetter = (stmt, index) -> stmt.setString(index, "unassigned");   SQLParameterBinder binder = statement.binder(prepared);   binder //       .bindParameter("Ada", valueSetter) //       .bindParameter(defaultSetter); }

	BindDefaultByIdentity.java
// Inputs: adapter, connection, CUSTOMER table, and NAME/EMAIL fields. SQLParameter shared = SQL.param(); SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(name).eq(shared) //     .or(email).eq(shared) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bindDefault(prepared, shared, (stmt, index) -> stmt.setString(index, "unassigned")); }

5  DBAdapter Customization

IDBAdapter.bindParameter() is the public binding hook used for ordinary bindings. Its interface default is deliberately generic for independent implementations. DBAdapter supplies a typed implementation and protected per-setter extension points for adapters that need to change one JDBC operation without replacing the type-dispatch algorithm.

5.1  Default Binding Architecture

The SQL definition assigns values to logical parameters. Compilation records each physical occurrence and its inferred DBType. CompiledStatement applies selected converters, handles explicit JDBC target types, and delegates every remaining value, including typed NULL, to IDBAdapter.bindParameter(). The generic interface default uses setObject except for known-type NULL, which uses getJDBCTypeForNull(). DBAdapter dispatches supported compatible pairs to typed setters. This keeps occurrence logic in the compiler and JDBC customization in the adapter.

	BindDefaultScalarParameters.java
// Inputs: adapter, connection, CUSTOMER table, and ID/NAME fields. // DBAdapter selects typed setters for these pairs. SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(customerID).eq(SQL.param()) //     .and(name).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, Integer.valueOf(501), "Ada"); }

The diagram shows the adapter's independent default-binding decisions, including its direct-call handling for unknown-type NULL and the opt-in Java-type fallback for unknown non-NULL values.

5.2  Unknown Parameter Types

DBAdapter.useJavaTypeForUnknownParameterType() defaults to false. Under that conservative policy, an unknown non-NULL value uses setObject. If enabled, supported Java scalar classes select the same typed hooks used for known compatible DBType/value pairs; unsupported classes still use setObject. The policy does not change known-type dispatch, and unknown NULL remains generic object binding at the adapter level. Structured DML separately rejects unknown untyped NULL before delegation.

	BindUnknownParameterType.java
// Inputs: connection and CUSTOMER table. // This projection comparison has no field-derived parameter DBType. SQLTemplate template = SQL.selectAll() //     .from(customer) //     .where(SQL.eq(SQL.param(), SQL.ONE)) //     .build(); IDBAdapter conservative = new H2Adapter(); IDBAdapter javaTypeFallback = new H2Adapter() {   @Override   public boolean useJavaTypeForUnknownParameterType()   {     return true;   } }; SQLStatement conservativeStatement = template.compile(conservative); try (IDBPreparedStatement prepared = conservativeStatement.prepare(connection, ReuseProbability.LOW)) {   // DBAdapter uses setObject.   conservativeStatement.bind(prepared, Integer.valueOf(42)); } SQLStatement fallbackStatement = template.compile(javaTypeFallback); try (IDBPreparedStatement prepared = fallbackStatement.prepare(connection, ReuseProbability.LOW)) {   // DBAdapter uses setInt for this supported Java type.   fallbackStatement.bind(prepared, Integer.valueOf(42)); }

5.3  Protected Binding Extension Points

DBAdapter exposes exactly twelve protected overridable methods. The default implementation makes the matching JDBC call. Most adapters need no override; a database-specific adapter can change one operation while retaining the common mapping and fallback behavior. The Boolean example is hypothetical: it models a backend whose Boolean values are represented as integer 0/1 parameters. The H2Adapter subclass is a concrete, compilable illustration of the extension mechanism; it does not imply that H2 generally requires integer Boolean binding. Adapters should normally retain setBoolean() unless their backend or driver gives a concrete reason to change it.

	CustomizeBooleanBinding.java
// Inputs: connection, CUSTOMER table, and a BOOLEAN ACTIVE field. // This hypothetical backend expects integer Boolean values. IDBAdapter adapter = new H2Adapter() {   @Override   protected void bindParameterBoolean(PreparedStatement stmt, int index, boolean value) throws SQLException   {     stmt.setInt(index, value ? 1 : 0);   } }; SQLStatement statement = SQL.selectAll() //     .from(customer) //     .where(active).eq(SQL.param()) //     .compile(adapter); try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.LOW)) {   statement.bind(prepared, Boolean.TRUE); }

6  Statement Lifecycle and Reuse

Reuse the adapter-neutral template or compiled statement across operations. Prepare and close a managed JDBC statement for each checkout so its connection can return the statement to its cache and release associated resources.

6.1  Templates and Compiled Statements

A template captures the builder's immutable definition. The adapter-specific compiled statement retains its SQL and occurrence metadata, but no connection or prepared statement. Reuse either definition for repeated executions.

6.2  Preparing, Executing, and Closing

Preparing checks out a connection-bound managed statement. Use try-with-resources for that statement and any result set. The connection's cache may reuse the JDBC object after close; ReuseProbability is a cache hint and does not guarantee object identity.

6.3  Partial Binding and Statement Reuse

Partial binding changes only the positions supplied in that operation. Untouched positions may still contain values from an earlier use of a reused JDBC statement. Rebind every position required by the next execution. Range and value-count checks happen before a sequence starts, but a later setter failure does not roll back earlier JDBC side effects.

	RepeatCustomerLookup.java
// Inputs: adapter, connection, CUSTOMER table and ID field. SQLParameter idValue = SQL.param(); SQLTemplate template = SQL.selectAll() //     .from(customer) //     .where(customerID).eq(idValue) //     .build(); SQLStatement statement = template.compile(adapter); for (int id : new int[] { 501, 502, 503 }) {   try (IDBPreparedStatement prepared = statement.prepare(connection, ReuseProbability.HIGH))   {     statement.bindAll(prepared, id);     try (IDBResultSet rows = prepared.executeQuery())     {       while (rows.next())       {         System.out.println("Customer " + rows.getInt("ID") + ": " + rows.getString("NAME"));       }     }   } }

7  Portability and Limitations

The compiler renders operations represented by the structured API and delegates identifier quoting and typed binding behavior to the adapter. It does not rewrite arbitrary fragment text, validate business rules, map result rows to objects, infer every parameter type through arbitrary expression trees, or generate DDL. SQL syntax and join support remain bounded by the adapter and target database. Keep portable portions structured and isolate vendor-specific syntax where it is needed.