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}

		maximumPoolSize      = 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.

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)

	// false when it could not connect. Stop here
	if (!DBUtil.load(Conf.conf().config(), App.class)) {
		throw new IllegalStateException("could not load the DB (the reason is in the log just above)");
	}

	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.

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))
);

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

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

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.extractTableData(Post.instance()) pulls out one table's worth.

Reading errors#

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

	List<Data> rows = db.selectList(SQL.select().from(Post.instance()));

	/*
	 * DB のエラーは例外ではなく戻り値で返る（要件 F-D-11）。
	 * select 系は null、更新系は -1。
	 */
	if (rows == null) {
		Log.error("引けませんでした: " + db.getError());
		return;
	}

}

DB errors come back as return values, not exceptions. select returns null, insert returns -1, update / delete return -1. The reason is in db.getError().

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.