SQLTemplate

SQLTemplate is a SQL template engine like MyBatis, supporting JDBC (blocking) and R2DBC (reactive) connections, with two generation modes: compile-time (annotation processing) and runtime (bytecode generation).

Template Engines

SQLTemplate supports two template formats:

1. Pebble Templates (.peb)

A modern Jinja2-style template engine with built-in caching. Use {% if %}, {% for %}, {{ var | bind }} syntax:

-- stg/user/getUser.peb
SELECT id, name, login, password, age
FROM user
WHERE id = {{ id | bind }}

2. MyBatis XML Mappers (.xml)

Full MyBatis dynamic SQL compatibility — <if>, <where>, <set>, <foreach>, <choose>, <trim>, #{} and ${}:

<mapper namespace="io.sqltemplate.UserMapper">
    <select id="findUsers">
        SELECT id, name, login, password, age FROM user
        <where>
            <if test="name != null">AND name LIKE #{name}</if>
            <if test="age != null">AND age >= #{age}</if>
        </where>
    </select>
</mapper>

Key Features

• Dual connection mode: JDBC (blocking) and R2DBC (reactive via Project Reactor)
• Two code generation modes: annotation processor (compile-time) or ByteBuddy proxy (runtime)
• ActiveRecord ORM: Record<T> / ReactiveRecord<T> with generated POJOs
• Transaction management: adapter-level @Transactional + Java Agent interception
• Extensible SPI: TemplateEngine, connection providers, transaction managers — all via ServiceLoader

Modules

Module	Description
sqltemplate-spi	Public API: annotations, service interfaces
sqltemplate-core	Pebble engine, JDBC/R2DBC adapters, template router
sqltemplate-template-mybatis	MyBatis XML engine
sqltemplate-annotation-processor	Compile-time code generation (JavaPoet)
sqltemplate-runtime	Runtime proxy generation (ByteBuddy)
sqltemplate-active-record	ActiveRecord ORM
sqltemplate-gradle-plugin	DB schema → POJO generator
sqltemplate-spring-boot-starter	Spring Boot 3.x auto-configuration
sqltemplate-showcase	Integration tests and examples

Quick Start

Standalone Mode

@Template("stg/user")
public interface UserTemplate {
    User getUser(String id);

    @Instance(type = InstanceType.UPDATE)
    long insertUser(int id, String name, String login, String password, int age);
}

// Compile-time mode (annotation processor generates UserTemplateImpl)
UserTemplate tmpl = new UserTemplateImpl();
User user = tmpl.getUser("1");

// Runtime mode (ByteBuddy creates proxy)
UserTemplate tmpl = RuntimeTemplateProvider.getInstance().getTemplate(UserTemplate.class);
Mono<User> user = tmpl.getUserMono("1");

Spring Boot 3.x

Add the starter dependency:

// build.gradle
dependencies {
    implementation 'io.sqltemplate:sqltemplate-spring-boot-starter:0.0.1-SNAPSHOT'
}

Configure in application.yml:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb
    username: root
    password: secret

sqltemplate:
  dialect: mysql       # mysql (default), postgresql, or ansi
  cache: true          # enable Pebble template caching (default: true)

SQLTemplate auto-configures ConnectionProvider, Dialect, and TemplateEngine automatically. Use @Template interfaces or ActiveRecord models as usual — they work out of the box with Spring-managed connections.

ActiveRecord with Preload

// Without preload — N+1 queries
User user = User.get(1);
List<Order> orders = user.getMany("orders");       // 1 extra query
Profile profile = user.getOne("profile");           // 1 extra query

// With preload — 3 queries total (not 1+N)
User user = User.where(User.class).eq("id", 1)
    .include("orders", "profile")
    .first();
List<Order> orders = user.getMany("orders");       // from cache, 0 queries
Profile profile = user.getOne("profile");           // from cache, 0 queries

Configuration

Standalone (sqltemplate.properties)

Place sqltemplate.properties on the classpath:

sqltemplate.dialect=postgresql
sqltemplate.template.path=stg/
sqltemplate.cache.templates=true

Or programmatically:

SqlTemplateConfig.set(SqlTemplateConfig.builder()
    .dialectName("postgresql")
    .build());

Spring Boot (application.yml)

sqltemplate:
  dialect: postgresql
  template-path: stg/
  cache: true
  jdbc:
    enabled: true     # default: true
  r2dbc:
    enabled: true     # default: true

Transaction Management

Standalone Mode

Use @Transactional on template interface methods or ActiveRecord models. The Java Agent (sqltemplate-transaction-agent) intercepts @Transactional methods at class-load time. Supports 7 propagation types: REQUIRED, REQUIRES_NEW, MANDATORY, SUPPORTS, NOT_SUPPORTED, NEVER.

Spring Boot Mode

When using the Spring Boot starter with a DataSource or ConnectionFactory, Spring's PlatformTransactionManager / ReactiveTransactionManager manages transaction boundaries. Use Spring's @Transactional on your service layer:

@Service
public class UserService {
    @Transactional
    public void transferMoney(long fromId, long toId, int amount) {
        // SQLTemplate operations participate in Spring's transaction
    }
}

The starter automatically detects Spring's transaction manager and configures SQLTemplate to delegate connection lifecycle management to Spring.