SQL DSL#

You can write SQL as a string, but the DSL gives you types.

Selecting#

List<Data> posts = db.selectList(
	SQL.select()
		.from(Post.instance())
		.where(Post.published.eq(true))
		.orderBy(Post.created_at.desc())
		.limit(20)
);

There are limit and offset. Pass nothing to select() and you get every column. Pass columns to pick them.

SQL.select(Post.id, Post.title).from(Post.instance())

Conditions#

.where(Post.id.eq(1L))
.where(Post.title.like("%jimble%"))
.where(Post.created_at.ge(from).and(Post.created_at.lt(to)))
.where(Post.id.in(List.of(1L, 2L, 3L)))

Call where more than once and the conditions are joined with AND.

For OR and parentheses, group with Dsl.anyOf(...) / Dsl.allOf(...) (since 1.5.0). Chaining a.or(b).and(c) reads left to right as (a OR b) AND c (since 2.2.3; up to 2.2.2 it was written without parentheses as a OR b AND c, that is a OR (b AND c)).

.where(Post.shop_id.eq(shopId), Dsl.anyOf(Post.status.eq("draft"), Post.status.eq("review")))
// WHERE (shop_id = ?) AND ( status = ? OR status = ?)

Joins#

SQL.select()
	.from(Post.instance())
	.left(Comment.instance()).on(Comment.post_id.eq(Post.id))
	.where(Post.id.eq(id))

Inner joins are inner(...). An on() attaches to the join right before it. Calling it twice joins the conditions with AND. Calling on() where there is no join fails (up to 1.4, when from was still a plain table, the ON was silently dropped).

The result nests under the table name, so you get it with row.getData("comment").

Index hints (MySQL)#

Put forceIndex(...) / useIndex(...) / ignoreIndex(...) on a table and pass it to from or a join (since 2.5.0).

SQL.select()
	.from(Orders.instance().forceIndex("orders__created_at"))       // FROM `orders` FORCE INDEX (`orders__created_at`)
	.left(Customer.instance().useIndex("customer__code"))           // LEFT JOIN `customer` USE INDEX (`customer__code`)
	.on(Customer.id.eq(Orders.customer_id))
	.where(Orders.created_at.ge(from))

You can list as many names as you like. Anything that does not look like a name (spaces, quotes, parentheses) is a SqlBuildException before anything is built.

Inserting, updating, deleting#

long id = db.insertKey(
	SQL.insert(Post.instance())
		.value(Post.title, title)
		.value(Post.created_at, new Date())      // the application's clock
);

int updated = db.update(
	SQL.update(Post.instance())
		.set(Post.published, true)
		.where(Post.id.eq(id))
);

int deleted = db.delete(
	SQL.delete(Post.instance()).where(Post.id.eq(id))
);

.value(Post.created_at, Dsl.now())       // the database's clock

insertKey returns the generated key (it throws if no key was generated). When you do not need the ID, use insert (it returns nothing). For INSERT ... SELECT and anything else where you need the count, use execute(...).

update / delete return the number of rows hit. Every failure is a SqlExecuteException; a unique-constraint violation alone is a DuplicateKeyException (Using the DB).

Updating only the fields that were sent looks like this.

patch("/posts/{id}", context -> {

	long id = id(context);
	BlogApp.findPost(id);

	Data request = context.request().bodyAll();

	UpdateBuilder builder = SQL.update(Post.instance());
	boolean hasChange = false;

	if (request.containsKey("title")) {
		builder.set(Post.title, request.getString("title"));
		hasChange = true;
	}

	if (request.containsKey("body")) {
		builder.set(Post.body, request.getString("body"));
		hasChange = true;
	}

	if (request.containsKey("published")) {
		builder.set(Post.published, request.getBoolean("published"));
		hasChange = true;
	}

	if (!hasChange) {
		throw new HttpException(400, "変える項目がありません（title / body / published）");
	}

	int updated = BlogExample.db().update(builder.where(Post.id.eq(id)));

	context.response().json("updated", updated);

});

Using functions#

The functions live on Dsl. Every one of them works on both MySQL and PostgreSQL.

SQL.select(Dsl.count(), Dsl.max(Post.created_at)).from(Post.instance())

Things like CASE WHEN that can be either a value or a column cannot be passed to select directly. Wrap them in a SelectQuery.

SQL.select(new SelectQuery().dsl(Dsl.caseWhen()...)).from(...)

Pass one unwrapped and it is treated as a bound value, not a column.

The list#

// Tidy up the name, and fall back to "(no name)" when it is empty
Dsl.coalesce(Dsl.trim(Post.title), "(no name)").as("title")

// Count per month
SQL.select(Dsl.year(Post.created_at).as("y"), Dsl.month(Post.created_at).as("m"), Dsl.count())
    .from(Post.instance())
    .groupBy(Dsl.year(Post.created_at), Dsl.month(Post.created_at))

// 30 days from now
Dsl.dateAdd(Post.created_at, 30, DateUnit.DAY)

// Roll the tags up into one string
Dsl.groupConcat(Tag.name, "/").as("tags")

Units (DateUnit) and types (CastType) are enums. You cannot pass strings. They go straight into the SQL, so the API is built so that a string from outside cannot reach them.

Filtering on an aggregate (having)#

An aggregate can be a condition directly — the same way a column can.

SQL.select(
        Department.id
        , Dsl.sum(Request.amount).as("total"))
    .from(Request.instance())
    .groupBy(Department.id)
    // only departments over 500,000
    .having(Dsl.sum(Request.amount).ge(500_000L));

It works inside CASE too.

new SelectQuery().dsl(Dsl.caseWhen()
        .when(Dsl.sum(Request.amount).ge(500_000L)).then("large")
        .elseCase("small")).as("size")

Only the comparisons are there (eq / not / gt / lt / ge / le / between / is_null / is_not_null). like and contains are not, because they mean nothing against an aggregate.

Window functions#

They attach an aggregate to each row without collapsing rows. Unlike GROUP BY, you do not lose rows.

SQL.select(
        Sale.shop_id
        , Sale.amount
        // Sales rank within each shop
        , Dsl.rank().partitionBy(Sale.shop_id).orderBy(Sale.amount.desc()).as("rank")
        // Running total
        , Dsl.over(Dsl.sum(Sale.amount))
            .orderBy(Sale.sold_at.asc())
            .rowsBetween(WindowFrame.unboundedPreceding(), WindowFrame.currentRow())
            .as("total"))
    .from(Sale.instance());

What you pass to Dsl.over(...) is an aggregate (sum / count / avg and so on). Put an alias or a calculation inside it and you get an exception (attach those after the OVER).

[!WARNING] A window function cannot go in where or in having. SQL forbids it, because a window function is evaluated after both of them. Write Dsl.rowNumber().over(...).eq(1) and you get an exception the moment the SQL is built. To filter on a rank, select it in a subquery first and filter on the outside.

in will not take an empty list#

where(Site.id.in(List.of()))   // ← throws the moment the SQL is built

IN () is a syntax error. Send it to the database quietly and the error message tells you nothing about where the empty list came from (F-D-07).

We do not "drop the condition when the list is empty." in(empty) means "matches nothing"; dropping the condition means everything. Only the caller knows which one was meant, so the caller decides.

if (ids.isEmpty()) {
	return List.of();          // "matches nothing"
}
where(Site.id.in(ids));

Product differences to watch#

Where the name is the same but the meaning is not, the DSL lines them up.

Some things we do not line up.

What the product does not have raises an exception (DialectException) at the point where the SQL is built. The list is in Using the DB.

When the DB product differs#

The same code emits SQL that follows db.xxx.product. Identifier quoting (` and "), ON DUPLICATE KEY UPDATE versus ON CONFLICT, INSERT IGNORE versus ON CONFLICT DO NOTHING, RAND() versus RANDOM() — the builder absorbs all of it.

What you cannot write for that product raises an exception (DialectException) at the point where the SQL is built. Which ones those are is listed in Using the DB.

// DialectException on PostgreSQL (to_char uses a different format language)
SQL.select(Dsl.dateFormat(Post.created_at, "%Y-%m-%d")).from(Post.instance());

Running a batch#

List<Integer> counts = db.executeBatch(builderList);   // row counts
List<Long>    ids    = db.insertBatch(builderList);    // generated keys

The two return different things. executeBatch gives you row counts (List<Integer>); insertBatch gives you the generated keys (List<Long>). Failures are exceptions. Pass an empty list and you get an empty list back.

Large results#

When you do not want the whole thing in a list, take it one row at a time with a cursor.

try (DB db = BlogExample.db();
	 ResultSetFetcher fetcher = new ResultSetFetcher()) {

	db.selectListWithFetcher(fetcher, SQL.select().from(Post.instance()));

	for (Data row : fetcher) {
		// arrives one row at a time
	}

}