SQL Generators

The jdbc.sql packages build SQL statements in Java with the builder pattern. Every builder is a SqlPart: getSqlFragment() renders the statement and getParameterValues() returns the values for its ? placeholders, in order. Values are never written into the SQL text, so the output goes straight into a PreparedStatement, a JdbcReader, or a script file without quoting or escaping concerns. The builders cover SELECT (with joins, nested queries, and unions), INSERT, UPDATE, DELETE, and MERGE, and they are part of the core DataPipeline jar. Database-specific DDL and upsert generators ship in the MySQL, PostgreSQL, and H2 add-ons described below.

How the Builders Work

Build a statement with the fluent methods, then call getSqlFragment() for the SQL and getParameterValues() for the parameters. Each builder's setPretty(true) switches the output from one line to indented lines, toString() returns the same text as getSqlFragment(), and both strip trailing whitespace (since DataPipeline 11.0). Conditions take their values as extra arguments, one per ?, which keeps user input out of the SQL string.

SELECT Queries

Select is created with one or more table names, or with other query sources (see nested queries and unions). Its clauses map to methods:

select(String... fields) adds columns or expressions; with none, * is selected. getSelection().setDistinct(true) generates SELECT DISTINCT.
where(String condition, Object... values) ANDs a condition with the existing ones; where(false, condition, values) ORs it instead.
groupBy(String... fields) and having(String condition, Object... values) add the GROUP BY and HAVING clauses.
orderBy(String... fields) sorts ascending, orderBy(field, false) descending, and orderBy(field, ascending, nullsFirst) adds NULLS FIRST or NULLS LAST. orderBy(FieldList fields), new in 11.0, sorts by the field names of a record or a FieldList; the same methods exist on QueryOrder, the clause object returned by getOrder().
limit(int count), limit(Integer offset, Integer count), and page(int page, int pageSize) (0-based) generate LIMIT and OFFSET.

Joins

innerJoin, leftJoin, rightJoin, and fullJoin each take the table to join and the ON condition, and render in the order they are added. Qualify column names with their table when more than one table is involved.

WHERE and HAVING Criteria

Behind where() and having() sits a QueryCriteria holding a tree of parts: a ConditionPart is one condition with its values, AndPart and OrPart combine their children, and NotPart negates one. Mix the shortcut methods with the tree when a query needs grouped conditions: getWhere().and(part) and getWhere().or(part) attach a part to the existing conditions, and where(QueryCriteriaPart) replaces them. Each part that renders SQL is parenthesized when it has siblings, and empty parts are skipped, so conditions can be added conditionally without producing a dangling AND.

Nested Queries and Unions

A Select is itself a QuerySource, as is a Union of several selects, so either can be passed to the Select constructor in place of a table name. Nested sources are parenthesized and should be given an alias with setNestedAlias(), which most databases require for a derived table. Union.setAll(true) generates UNION ALL. Parameter values are collected in the order the parts render, so the outer query's values follow the nested ones.

INSERT UPDATE and DELETE Statements

Insert takes column and value pairs with add(column, value), or bare column names with add(String... columns) for null values you will set later on the statement. nextRow() starts another row for a multi-row insert (nextRow(true) copies the previous row's columns and values) and addRows(count, copyLastRowValues) appends several at once; the column list comes from the first row. Update collects set(column, value) assignments and Delete only needs conditions; both share the where() behavior of Select.

MERGE and ON CONFLICT

Merge builds the standard MERGE statement that updates a matching row and inserts a missing one, as used by SQL Server, Oracle, and Sybase. The target table is aliased target, the source row is supplied as parameters through a MergeUsing clause (VALUES or SELECT) whose columns are named with addValue(), setCondition() sets the ON condition, addInsertValues() lists the columns of the INSERT branch, and addUpdateValue() adds a column=? assignment to the UPDATE branch. This is the template JdbcUpsertWriter's MergeUpsert strategy builds for each record, binding every ? from the record's fields, so the builder's own parameter list is not used there.

OnConflict renders only the UPDATE SET column=?, ... action that follows ON CONFLICT (...) DO in a PostgreSQL-style upsert, for appending to an insert statement of your own.

Running the Generated SQL

Bind the parameters in order on a PreparedStatement, or hand the SQL and the values to a JdbcReader, which accepts them as its query string and parameters.

In DataPipeline Foundations, every PipelineAction implements SqlSelectGenerator: generateSqlSelect(QuerySource...) returns a Select equivalent to the add, aggregate, copy, rename, select, sort, and remove-duplicates actions, so the database can do that work instead of the JVM. See Generate SQL SELECT from Pipeline Actions.

Database-Specific Generators

The core builders stick to portable SQL. Statements that differ by database come from the add-ons, which share one design: DDL builders for tables, columns, indexes, and foreign keys; CreateXxxDdlFromSchemaDef to generate a whole schema's DDL from a Foundations SchemaDef; and writers that turn records into insert or upsert scripts. Add the artifact for your database as shown on the Integrations page.

Database	Add-on and package	Generators
MySQL	`northconcepts-datapipeline-integrations-sql-mysql` com.northconcepts.datapipeline.sql.mysql	`CreateTable`, `CreateTableColumn`, `TableColumnType`, `CreateIndex`, `CreateForeignKey`, `DropTable`, `DropTableConstraint`, `CreateMySqlDdlFromSchemaDef`, `MySqlInsertWriter`, and `MySqlUpsertWriter` (`ON DUPLICATE KEY UPDATE`)
PostgreSQL	`northconcepts-datapipeline-integrations-sql-postgresql` com.northconcepts.datapipeline.sql.postgresql	The same DDL builders, `CreatePostgreSqlDdlFromSchemaDef`, `PostgreSqlInsertWriter`, and `PostgreSqlUpsertWriter` (`ON CONFLICT` on the key fields with a `ConflictAction`)
H2	`northconcepts-datapipeline-integrations-sql-h2` com.northconcepts.datapipeline.sql.h2	The same DDL builders, `CreateH2DdlFromSchemaDef`, and `H2InsertWriter`

The DDL builders quote identifiers the way their database expects and have no parameters, so getSqlFragment() is ready to execute:

The add-on Insert and Upsert builders write the values you add directly into the statement instead of using ? placeholders, because they produce scripts rather than prepared statements. Use them through the writers, which format each field as a SQL literal (quoted strings, numbers, TRUE and FALSE, JDBC date and time escapes, JSON for nested records, and X'...' for binary values) and batch rows: MySqlUpsertWriter and PostgreSqlUpsertWriter accept a file or any Writer.