Using the DB#

Configure it#

Write your data source in application.conf.

db {
	blog_example {
		main     = true
		driver   = "org.mariadb.jdbc.Driver"
		url      = "jdbc:mariadb://127.0.0.1:3306/blog_example?useUnicode=true&characterEncoding=UTF-8"
		url      = ${?DB_URL}
		username = "blog"
		username = ${?DB_USER}
		password = ""
		password = ${?DB_PASSWORD}

		maximum_pool_size      = 10
		connection_pool_type = "hikari"
	}
}

codegen {
	package = "db"
}

Do not put secrets in the file. Write them as ${?ENV_NAME} and the environment variable wins when it is set. When it is not, the value from the line above stays.

jimble-db ships drivers for both MySQL / MariaDB and PostgreSQL, so you normally do not have to add one.

Encrypting the connection (TLS)#

Since 2.6.0, the MySQL / MariaDB driver (MariaDB Connector/J 4.0) uses TLS by default and verifies the server's certificate (sslMode=verify-full). The driver in 2.5 (3.5) connected without encryption when nothing was set.

If the certificate cannot be verified, the connection fails at startup. With a self-signed MySQL such as Docker's mysql:8.4, you get:

Cannot use authentication plugin caching_sha2_password with a Self signed certificates.
Either set sslMode=trust, use password with a MitM-Proof authentication plugin or provide server certificate to client

MariaDB 11.4 and later servers can be verified even with a self-signed certificate, with nothing added (a local mariadb:11.4 connected without any setting).

Choose with sslMode in the url:

To skip encryption only on your machine, put it in the local configuration:

# application.local.conf
include "application.conf"

db {
	blog_example {
		url = "jdbc:mariadb://127.0.0.1:3306/blog_example?sslMode=disable"
	}
}

Choose a DB product#

Set product and the SQL builder emits SQL for that product.

db {
	blog_example {
		main     = true
		product  = "postgresql"
		driver   = "org.postgresql.Driver"
		url      = "jdbc:postgresql://127.0.0.1:5432/blog_example"
		username = "blog"
		password = "blog"
		password = ${?DB_PASSWORD}
	}
}

When the database does not exist yet#

Write create_database_sql and it is run once, after a failed connection.

db {
	blog_example {
		...
		create_database_sql = "CREATE DATABASE blog_example"
	}
}

Leave it out and nothing happens, so you do not need it if you created the database by hand. In production you normally connect as a user without that privilege, so this is for development and CI.

Leave it out and you get mysql. Write a value it does not know and startup fails.

Not one line of your application code changes.

// This one line becomes
//   MySQL      SELECT `post`.`id` AS `post__id` ... LIMIT ? OFFSET ?
//   PostgreSQL SELECT "post"."id" AS "post__id" ... LIMIT ? OFFSET ?
// depending on product
SQL.select().from(Post.instance()).where(Post.id.eq(1L));

Each data source can name a different product (MySQL for the main one, PostgreSQL for reporting, and so on).

What you cannot write for a given product#

What you cannot write raises DialectException at the point where the SQL is built. You never get as far as running it and failing on the product's syntax error.

The other way round, things that differ only in name we do line up silently (RAND / RANDOM, IFNULL / COALESCE, TRUNCATE / TRUNC, and so on). Dsl.concat(...) becomes || on PostgreSQL (PostgreSQL's concat() swallows NULL as an empty string, so swapping the name alone would change the result compared with MySQL).

When you really do want the product's own syntax, Dsl.freeSql(...) is the way out.

Things to watch#

• Spatial functions take their coordinates in a different order. SRID 4326 on MySQL 8 is latitude then longitude; PostGIS is longitude then latitude. jimble does not absorb this, so line it up yourself if you use both
• Migration SQL (conf/migration/...) runs exactly as you wrote it. To run on both products, split it by filename suffix — 001_xxx.mysql.sql / 001_xxx.postgresql.sql (no suffix is applied on both). See Splitting SQL per product

Connecting at startup#

Writing the configuration is not enough. Call it explicitly from your main.

public static void main (String[] args) {

	Migration.install();                                // startup migrations (if you want them; before DBUtil.load)

	DBUtil.load(Conf.conf().config(), App.class);       // throws if it cannot connect. Startup stops here

	JimbleServer.start(new App());

}

The second argument to DBUtil.load is the classpath anchor — migration SQL is looked up from there. Pass a class of your own application.

When it cannot connect it throws SqlExecuteException (DB_007), and startup stops there. There is no return value (void). Do not catch it; let it propagate out of main.

To shut down, call DBUtil.stop() (the web server does it as part of graceful shutdown; in batches and tests you call it yourself).

When there is more than one data source#

db {
	main_db { main = true, ... }
	log_db  { ... }
}

DBUtil.getDB("log_db") gets you a different data source. The one with main = true is DBUtil.getMainDB() (there is no no-argument getDB()).

Everything listed at the top level is treated as its own thing:

• migrations under conf/migration/<data source name>/ are applied to it
• codegen generates db/<data source name>/ for it
• migration / db_lock / db_value / db_cache are created there too

If all you want is one more handle without copying the settings, use subs.

db {
	main_db {
		main = true
		...
		subs {
			archive_db { }
		}
	}
}

Get it with db.newSubDB("archive_db").

examples/approval-data has both a second top-level entry and a subs.

Generate table definitions#

./gradlew codegen

This generates one class per table from the DB schema.

Post.id       // Column
Post.title
Post.instance()   // Table

Add the io.jimble.db plugin and migrate → codegen → compileJava are wired together. Write DDL, start up, and the code catches up on its own.

plugins {
	id("io.jimble.db")
}

Commit the generated classes to the repository. If you do not commit them, you cannot build in an environment with no DB.

Selecting#

Data row = db.select(
	SQL.select()
		.from(Post.instance())
		.where(Post.id.eq(1L))
).orElseThrow();

// SELECT の結果はテーブル名でネストする（要件 F-D-02）
String title = row.getData("post").getString("title");

// Column で引けば、途中の文字列が出てこない
String same = row.getString(Post.title);

select returns Optional<Data>. It is empty when there are no rows (the .orElseThrow() above is how you take it out when the row must be there). When the row cannot be read you get an exception, not an empty result (see "Reading errors" below).

A SELECT result nests under the table name. Join post and comment and the id on each side does not collide.

Query with Column and no string keys show up in your code. row.getData(Post.instance()) pulls out one table's worth, flattened.

JSON columns#

MySQL JSON and PostgreSQL json / jsonb columns come back already decoded, as Data (objects) or List (arrays).

row.getStringList("tags");      // ["a", "b"]  an array column
row.getData("options");         // an object column
row.getString("tags");          // "a"  <- only the first element, not JSON text

getString on an array column returns only the first element (a WARN is logged the first time it happens). To get the JSON text itself, convert it in SQL: CAST(col AS CHAR) on MySQL, col::text on PostgreSQL.

Reading errors#

try (DB db = BlogExample.db()) {

	/*
	 * 0件は空（空リスト・空の Optional・件数 0）、失敗は SqlExecuteException（2.0）。
	 * 書かなければ上まで飛んで 500。トランザクションの中なら巻き戻る。
	 */
	List<Data> rows = db.selectList(SQL.select().from(Post.instance()));

	// 分岐したい失敗は一意制約くらい。それだけを受け止める
	try {
		db.insert(SQL.insert(Post.instance()).value(Post.title, "hello"));
	} catch (DuplicateKeyException ex) {
		Log.info("もうあります");
	}

}

DB failures come back as exceptions. When a statement fails or the connection cannot be made, you get SqlExecuteException (unchecked). Leave it uncaught and it travels up to a 500; inside a transaction, the transaction rolls back. The code is in getCode() (DB_999 etc.), and the original JDBC exception is at getCause().getCause().

No rows is not a failure. It comes back as an empty Optional, an empty list, or a count of 0.

executeBatch / insertBatch require every statement to be the same SQL. A mix throws with DB_998.

"No rows" and "could not read"#

They come back as different things. An empty Optional means "no rows" and nothing else; a failure to read throws.

Optional<Data> user = db.select(sql, id);      // throws SqlExecuteException here if it cannot be read
if (user.isEmpty()) { return nobody; }         // empty means "no rows", nothing else

Data post = db.select(sql, id)
	.orElseThrow(() -> new HttpException(404, "no such post"));

The day the DB goes down, your app does not answer "no such user".

Catching only unique-constraint violations#

The only failure you usually want to branch on is a unique-constraint violation. That one has its own type. Hitting a unique constraint throws DuplicateKeyException (a subclass of SqlExecuteException) — from insert, insertKey, update or any other statement.

try {
	long id = db.insertKey(SQL.insert(User.instance()).value(User.email, email));
} catch (DuplicateKeyException e) {
	return "that email address is taken";
}

Where to catch#

DB exceptions are unchecked, so the compiler never asks you to catch them. Decide where to catch with the table below. Anything not in it is left alone: the framework replies 500 and rolls back.

• Checking with select first does not let you skip the catch. If two requests arrive together, both pass the check and one hits the constraint
• Catch outside the transaction (see the TRAP above)
• Write one test that inserts the same value twice; a forgotten catch shows up there as a 500

Generated keys and counts#

insert returns nothing. Use insertKey for the generated key, and execute when you need a count, as with INSERT ... SELECT.

db.insert(SQL.insert(Tag.instance()).value(Tag.name, name));             // just insert
long id = db.insertKey(SQL.insert(Post.instance()).value(...));          // the generated key
int count = db.execute("INSERT INTO archive SELECT * FROM post WHERE ...");  // the count

Migrations#

Put them in conf/migration/<schema name>/001_xxx.sql.

# --- !Ups

CREATE TABLE `note` (
	`id`         BIGINT UNSIGNED AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
	`title`      VARCHAR(200)  NOT NULL COMMENT 'Title',
	`created_at` DATETIME      NOT NULL COMMENT 'Created at'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Note';

# --- !Downs

DROP TABLE `note`;

./gradlew migrate

What has already been applied is recorded, so it never runs twice. When several servers start at once, a lock is taken and only one of them runs.

When each thing runs, what happens when one fails, and the type mapping that gets generated are all in Migrations and code generation.