Skip to content

Tentackle SQL — The Database Backend Layer

Overview and Motivation

The tentackle-sql module is Tentackle's database abstraction layer. It sits directly above JDBC and below the persistence layer, isolating the rest of the framework from the dialect, quirks, and capabilities of the concrete relational database in use.

Its central abstraction is the Backend — a single object that knows everything dialect-specific about one kind of database: how to build a SELECT with a LIMIT/OFFSET, what an empty string looks like, how to create a sequence, whether UUID is supported natively, how to render a CREATE TABLE column, how to rename an index, which words are reserved, how to recognize a deadlock, and much more. Everything that differs between PostgreSQL, Oracle, DB2, SQL Server, MySQL/MariaDB, Informix, and H2 is funneled through this one interface, so the layers above it can stay completely database-agnostic.

The module serves two distinct audiences:

  1. The runtime persistence layer (tentackle-persistence) uses a Backend to build and execute the SQL behind every PDO operation — selects, inserts, updates, deletes, sequences, write locks, paged queries, and array parameters.
  2. The build-time tooling — the SQL Maven plugin and the Wurbelizer wurblets — use the same Backend and the DataType system to generate DDL scripts, schema migrations, and type-specific persistence code from the application model.

Because the build tools and the runtime share the same backend code, the SQL you generate at build time and the SQL you execute at runtime are guaranteed to agree.

Design principles

  • One backend instance per database type. A Backend is a stateless singleton (loaded as a service). It must not hold per-connection state — the only state it conceptually represents is "the whole of all connections to this kind of database". This makes backends trivially shareable across threads and JVMs.
  • No ORM, no query DSL. This module does not try to be JPA. It produces and consumes plain SQL strings and JDBC PreparedStatement/ResultSet objects. The intelligence lives in how those strings are assembled for each dialect, not in hiding SQL from the developer.
  • Pluggable via SPI. Backends and data types are discovered through Tentackle's ServiceFinder mechanism, so applications can add their own database support or their own column types without modifying the framework.
  • Backend-agnostic upward, dialect-aware downward. Code above this layer never branches on the database product; code inside a Backend never needs to know about domain objects or persistence semantics.

Key Concepts

Backend

Backend is the heart of the module. It is a large interface — by design — because it enumerates every point at which relational databases disagree. Its methods fall into a handful of groups:

Group Examples Purpose
Identification & connection isMatchingUrl, isMatchingName, getDriverClassName, createConnection, validateVersion Recognize the database from a JDBC URL or name and open connections to it.
Query construction buildSelectSql, setLeadingSelectParameters, setTrailingSelectParameters, sqlJoin, sqlJoinSelects, sqlFunction, optimizeSql Assemble SELECTs with dialect-correct paging, write-locking, joins and function calls.
DDL generation sqlCreateTableIntro, sqlCreateColumn, sqlCreateIndex, sqlCreateForeignKey, sqlCreateSequence, sqlCreateSchema, sqlDropTable Emit CREATE/DROP statements for the schema generator.
Schema migration getMigrationStrategy, sqlRenameColumn, sqlAlterColumnType, sqlAlterColumnNullConstraint, sqlAlterColumnDefault, isDefaultEqual Compute and emit the ALTER statements that morph an existing schema into the model's.
Type mapping sqlTypeToString, columnTypeToString, columnTypeNullDefaultToString, jdbcTypeToSqlType, getMaxSize, getMaxScale, valueToLiteral Translate Tentackle SqlTypes to the database's own type declarations and back.
Capability flags isSequenceSupported, isSchemaSupported, isClobSupported, isUUIDSupported, isFunctionBasedIndexSupported, isFilteredIndexSupported, isReleaseSavepointSupported Let callers ask what the database can do instead of hard-coding product checks.
Naming & reserved words assertValidName, getMaxNameLength, getReservedWords, isReservedTableName, isTemporaryName Validate identifiers against the dialect's rules.
Error classification isConstraintException, isCommunicationLinkException, isTransientTransactionException Interpret vendor-specific SQLExceptions so the upper layers can decide whether to retry a transaction or reconnect.
Metadata getMetaData, getModelMetaData, getTableMetaData, createColumnMetaData, createIndexMetaData Read the live database schema for migration and verification.

A long list of SQL_* string constants (SQL_WHERE, SQL_SELECT, SQL_AND, SQL_EQUAL_PAR, …) is also defined on the interface. These are the building blocks the persistence layer concatenates into statements; sharing them avoids scattering string literals throughout the framework and keeps generated and runtime SQL consistent.

Stateless contract: because there is exactly one backend per database type, implementations must not cache per-connection or per-transaction data. The one mutable exception is setDropIfExistsEnabled / isDropIfExistsEnabled, a global toggle that controls whether generated DROP statements include IF EXISTS.

BackendInfo

While a Backend describes a kind of database, a BackendInfo describes a concrete configured connection target: the JDBC URL (or JNDI DataSource), username, password and optional schemas. It is the object you actually use to open a Connection.

BackendInfo understands several ways to point at a database:

  • A JDBC URL (jdbc:...) — the matching backend is found automatically via Backend.isMatchingUrl.
  • A JNDI name (jndi:...) — the DataSource is looked up and the backend derived from its metadata.
  • A TRIP / remote URL — there is no local backend at all (isRemote() returns true); the persistence layer routes calls to a remote server. This is what lets the very same code run on a directly-connected client and on a remoting client without change.

A backend type can be pinned explicitly by appending |<BackendName> to the URL. This is the escape hatch for running an older dialect against a newer server, e.g.:

jdbc:oracle:thin:@//localhost:1521/xe|Oracle8

BackendInfo can be built directly from arguments, from an EncryptedProperties configuration (honouring Cryptor-encrypted URLs and passwords), or copied with a different user/password. It also carries the optional keep-alive settings (backendTimeout, backendKeepAliveEnabled) used to survive cloud databases that silently drop idle connections — either by sending a dummy SELECT periodically, or by testing the connection just before it is reused.

Passwords are held in char[] and can be wiped from memory with clearPassword().

SqlType

SqlType is a small, type-safe enum that is Tentackle's curated subset of java.sql.Types: VARCHAR, CHAR, CLOB, BLOB, DECIMAL, BIT, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DATE, TIME, TIMESTAMP, UUID and JAVA_OBJECT. Each value knows its Java default value and how to parse a literal coming back from JDBC metadata.

SqlType is the physical view — one SqlType corresponds to one database column. It is what the backend turns into an actual type declaration via columnTypeToString and friends.

DataType

DataType<T> is the logical, model-level view of a type. Where SqlType describes a column, a DataType describes a Java type as used in the model and maps it to one or more columns. For example BMoneyType maps a single BMoney value to two columns (amount + scale).

DataType is deliberately an interface, not an enum, so that applications can define their own column types. The module ships ~36 predefined implementations under org.tentackle.sql.datatypes covering the primitive wrappers and their primitives, String/Character, the java.time types (LocalDate, LocalDateTime, Instant, ZonedDateTime, OffsetDateTime, …), the legacy java.sql date/time types, BigDecimal, UUID, binary/large types, and the Tentackle money types BMoney/DMoney.

Each DataType is a stateless singleton annotated with @Service(DataType.class) and located through the DataTypeFactory. It is keyed by the plain Java class name plus an optional variant (e.g., the "Large" variant distinguishes a CLOB-backed String from a VARCHAR one).

The interface serves three roles at once, which is why it is so broad:

  1. Model description — getColumnCount, getSize, getScale, createColumnNames, getSortableColumns, isNumeric, isDateOrTime, isMutable, … describe how the type appears in the schema.
  2. Code generation — getColumnGetter, valueOfLiteralToCode, getDataTypeConstant, isPredefined, … feed the wurblets that generate persistence code. Predefined types have handwritten persistence methods; application-defined types must return isPredefined() == false so generic accessor code is generated instead.
  3. Runtime persistence — set(...), get(...), getColumnValue, getMappedNullValue, toLiteral read and write actual values to/from PreparedStatements and ResultSets (mainly for non-predefined types).

Where to put custom data types: because a DataType references its Java type, define it in a separate module that the Java type's own module does not depend on — otherwise every application module would transitively depend on tentackle-sql, which is undesirable for the PDO modules. The custom type's module must then be registered as a wurbletDependency for the Wurbelizer plugin and as a plugin dependency of the SQL Maven plugin.

ScriptRunner

ScriptRunner executes multi-statement SQL scripts (e.g., generated DDL and migration scripts) against a Connection. It splits a script into statements using the backend's statement separators and comment syntax, then runs each one. It offers:

  • control over escape processing, max rows, per-statement timeout, read-only mode, and whether to wrap the whole script in a transaction;
  • cancel() / isCanceled() to abort a long-running script;
  • listeners for each statement submitted (addExecutionListener), each result (addResultListener) and each result set (addResultSetListener).

A Backend creates an appropriate runner via createScriptRunner(connection). The default is DefaultScriptRunner; PostgreSQL supplies a specialized PostgresScriptRunner. Failures surface as ScriptRunnerException (a statement failed) or BackendException (anything else).

DynamicDriver

DynamicDriver loads a JDBC driver from an arbitrary URL at runtime. DriverManager refuses to use drivers not loaded by the system classloader, so DynamicDriver wraps the URL-loaded driver in a system-classloader-visible delegate and registers it. The driver is specified as <className>:<url>, e.g.:

org.postgresql.Driver:jar:file:/usr/share/java/postgresql.jar!/

This is triggered by the backend.driver configuration property and lets an application ship without bundling every possible JDBC driver.

Package Layout

Package Contents
org.tentackle.sql The core API: Backend, BackendInfo, BackendFactory, DataType, DataTypeFactory, SqlType, SqlNameType, JoinType, MigrationStrategy, ScriptRunner, DynamicDriver, the Default* implementations and the exceptions.
org.tentackle.sql.backends The concrete Backend implementations and their abstract base classes (see below).
org.tentackle.sql.datatypes The ~36 predefined DataType implementations and the abstract bases AbstractDataType, AbstractNumberType, AbstractDateTimeType.
org.tentackle.sql.metadata Live-schema metadata model (ModelMetaData, TableMetaData, ColumnMetaData, IndexMetaData, ForeignKeyMetaData and backend-specific subclasses) used by the migrator and schema verifier.
org.tentackle.sql.service The ModuleHook for resource-bundle/i18n integration.

Supported Backends

All backend implementations extend AbstractBackend and are organized by SQL-standard level so that newer dialects inherit standard behavior and only override their peculiarities:

AbstractBackend
└── AbstractSql92Backend
    ├── H2
    ├── Informix
    ├── Postgres
    ├── MySql
    │   └── MariaDb
    └── AbstractSql2003Backend
        ├── Db2
        ├── MsSql
        └── Oracle8
            └── Oracle
        (AbstractSql2008Backend extends AbstractSql2003Backend)
Backend Name Matches JDBC URL containing
Postgres PostgreSQL :postgresql
H2 H2 :h2: (in-memory when :mem:)
MySql MySQL :mysql
MariaDb MariaDB :mariadb
Oracle Oracle :oracle:
Oracle8 Oracle8 :oracle: (legacy, selected by name)
Db2 DB2 :db2
MsSql MsSQL :sqlserver
Informix Informix :informix

When several backends could match the same URL (e.g. Oracle and the deprecated Oracle8), only one matches by URL and the others are selected explicitly by name via the |<BackendName> URL suffix. Backends marked isDeprecated() are excluded from BackendFactory.getAllBackends().

How It Fits Together

Locating a backend at runtime

Backends are discovered as services and located through the BackendFactory singleton:

// by JDBC URL — the matching backend is found automatically
Backend backend = BackendFactory.getInstance()
                                .getBackendByUrl("jdbc:postgresql://localhost/mydb");

// by name — e.g., to pin a specific dialect
Backend oracle8 = BackendFactory.getInstance().getBackendByName("Oracle8");

// all non-deprecated backends — used by the SQL Maven plugin to generate DDL for every dialect
Collection<Backend> all = BackendFactory.getInstance().getAllBackends();

In practice, application code rarely calls BackendFactory directly. It configures a BackendInfo (usually from properties) and obtains a connection:

BackendInfo info = new BackendInfo(backendProperties);   // url, user, password, schemas, driver, timeout...
try (Connection con = info.connect()) {
    Backend backend = info.getBackend();
    String sql = backend.buildSelectSql("FROM customer WHERE name LIKE ?",
                                        false /*writeLock*/, 50 /*limit*/, 0 /*offset*/);
    // ... prepare, set parameters via backend.setLeadingSelectParameters(...), execute
}

Generating and migrating schemas at build time

The SQL Maven plugin walks the application model, and for each configured backend uses the DataType/SqlType mapping plus the backend's sqlCreate* methods to emit a complete DDL script. For an existing database it reads the live schema into a ModelMetaData, diffs it against the model, and asks the backend for a MigrationStrategy[] per changed column, emitting the corresponding ALTER statements. The generated scripts are then either applied using a ScriptRunner (local development only) or checked into source control to be applied later by migration tools such as Flyway or Liquibase.

Because the same Backend objects power both the build-time generation and the runtime execution, the schema the tools create always matches the SQL the persistence layer issues.

Extending the Module

Adding a custom column type

  1. Implement DataType<T> (usually by extending AbstractDataType), annotate it with @Service(DataType.class), and make isPredefined() return false.
  2. Place it in a dedicated module that does not create a dependency from your PDO modules onto tentackle-sql (see the note in DataType).
  3. Register that module as a wurbletDependency of the Wurbelizer plugin and as a dependency of the SQL Maven plugin so both build-time and runtime can find the type.

Adding support for another database

Subclass the closest AbstractSqlNNNNBackend matching the database's SQL-standard level, override the methods where the dialect deviates (type strings, paging syntax, sequence support, reserved words, error classification, …), and annotate the class with @Service(Backend.class). The new backend is then picked up automatically by the BackendFactory.

Module Dependencies

tentackle-sql deliberately sits low in the stack:

  • It requires transitive tentackle-common (service discovery, EncryptedProperties/Cryptor, StringHelper, money types, …) and java.naming (for JNDI DataSource lookups).
  • It bundles the PostgreSQL JDBC driver as its reference/test database; other drivers are loaded by the application (optionally via DynamicDriver).
  • It opens org.tentackle.sql to org.tentackle.core for TRIP serialization, since BackendInfo and related types may travel between JVMs.
  • It knows nothing about the domain or persistence layers — those depend on it, never the reverse.