or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

advanced-patterns.md core-operations.md database-init.md datasource.md embedded-database.md exception-translation.md index.md key-generation.md lob-support.md result-parameter-handling.md simplified-operations.md sqlrowset.md transactions.md

tile.json

DataSource Management

DataSource implementations, routing, and configuration.

DataSource Implementations

DriverManagerDataSource

Simple, non-pooled DataSource for testing.

import org.springframework.jdbc.datasource.DriverManagerDataSource;

DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setUrl("jdbc:postgresql://localhost:5432/mydb");
ds.setUsername("user");
ds.setPassword("password");

// Constructor shorthand
DriverManagerDataSource ds = new DriverManagerDataSource(
    "jdbc:postgresql://localhost:5432/mydb",
    "user",
    "password"
);

Use case: Testing only. Does NOT pool connections.

SingleConnectionDataSource

Single shared Connection for testing.

import org.springframework.jdbc.datasource.SingleConnectionDataSource;

SingleConnectionDataSource ds = new SingleConnectionDataSource(
    "jdbc:h2:mem:testdb",
    "sa",
    "",
    true  // suppress close() calls
);

Use case: Unit tests with in-memory databases.

Production DataSources

Use dedicated connection pools:

HikariCP (recommended):

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);

HikariDataSource ds = new HikariDataSource(config);

Spring Boot:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: user
    password: password
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 30000

DataSource Routing

AbstractRoutingDataSource

Route to different DataSources based on lookup key.

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

public class DataSourceContextHolder {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setDataSourceType(String type) {
        contextHolder.set(type);
    }

    public static String getDataSourceType() {
        return contextHolder.get();
    }

    public static void clearDataSourceType() {
        contextHolder.remove();
    }
}

// Configuration
@Bean
public DataSource routingDataSource(
    @Qualifier("primaryDataSource") DataSource primary,
    @Qualifier("replicaDataSource") DataSource replica
) {
    Map<Object, Object> targetDataSources = new HashMap<>();
    targetDataSources.put("primary", primary);
    targetDataSources.put("replica", replica);

    RoutingDataSource routingDS = new RoutingDataSource();
    routingDS.setTargetDataSources(targetDataSources);
    routingDS.setDefaultTargetDataSource(primary);
    routingDS.afterPropertiesSet();

    return routingDS;
}

// Usage
DataSourceContextHolder.setDataSourceType("replica");
try {
    // Queries go to replica
    List<User> users = jdbc.query("SELECT * FROM users", new UserRowMapper());
} finally {
    DataSourceContextHolder.clearDataSourceType();
}

Read-write splitting:

@Aspect
@Component
public class DataSourceAspect {
    @Around("@annotation(readOnly)")
    public Object routeDataSource(ProceedingJoinPoint pjp, ReadOnly readOnly) throws Throwable {
        DataSourceContextHolder.setDataSourceType("replica");
        try {
            return pjp.proceed();
        } finally {
            DataSourceContextHolder.clearDataSourceType();
        }
    }
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface ReadOnly {}

// Usage
@ReadOnly
public List<User> findAllUsers() {
    return jdbc.query("SELECT * FROM users", new UserRowMapper());
}

LazyConnectionDataSourceProxy

Delays Connection acquisition until first use.

import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;

@Bean
public DataSource dataSource() {
    HikariDataSource target = new HikariDataSource(config);
    return new LazyConnectionDataSourceProxy(target);
}

Use case: Read-only transactions that may not actually query database.

TransactionAwareDataSourceProxy

Makes DataSource participate in Spring transactions.

import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;

@Bean
public DataSource dataSource() {
    HikariDataSource target = new HikariDataSource(config);
    return new TransactionAwareDataSourceProxy(target);
}

Use case: When using DataSource directly (not via JdbcTemplate).

JNDI DataSource

import org.springframework.jndi.JndiObjectFactoryBean;

@Bean
public JndiObjectFactoryBean dataSource() {
    JndiObjectFactoryBean jndi = new JndiObjectFactoryBean();
    jndi.setJndiName("java:comp/env/jdbc/MyDB");
    jndi.setResourceRef(true);
    jndi.setProxyInterface(DataSource.class);
    return jndi;
}

// Spring Boot
spring.datasource.jndi-name=java:comp/env/jdbc/MyDB

Multi-Tenant DataSource

public class TenantRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TenantContext.getCurrentTenant();
    }
}

public class TenantContext {
    private static final ThreadLocal<String> currentTenant = new ThreadLocal<>();

    public static void setCurrentTenant(String tenant) {
        currentTenant.set(tenant);
    }

    public static String getCurrentTenant() {
        return currentTenant.get();
    }

    public static void clear() {
        currentTenant.remove();
    }
}

@Configuration
public class MultiTenantConfig {
    @Bean
    public DataSource dataSource() {
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("tenant1", createDataSource("jdbc:postgresql://localhost/tenant1_db"));
        dataSourceMap.put("tenant2", createDataSource("jdbc:postgresql://localhost/tenant2_db"));

        TenantRoutingDataSource routing = new TenantRoutingDataSource();
        routing.setTargetDataSources(dataSourceMap);
        routing.setDefaultTargetDataSource(dataSourceMap.get("tenant1"));
        routing.afterPropertiesSet();

        return routing;
    }

    private DataSource createDataSource(String url) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername("user");
        config.setPassword("password");
        return new HikariDataSource(config);
    }
}

// Interceptor to set tenant
@Component
public class TenantInterceptor implements HandlerInterceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
        String tenantId = request.getHeader("X-Tenant-ID");
        TenantContext.setCurrentTenant(tenantId);
        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) {
        TenantContext.clear();
    }
}

Connection Pooling Best Practices

HikariCP configuration:

# Core settings
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

# Performance
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.connection-test-query=SELECT 1

# Leak detection
spring.datasource.hikari.leak-detection-threshold=60000

Pool size formula:

connections = ((core_count * 2) + effective_spindle_count)

For typical web app on 4-core machine: 10-20 connections.

DataSource Utils

import org.springframework.jdbc.datasource.DataSourceUtils;

// Get connection (participates in transaction)
Connection conn = DataSourceUtils.getConnection(dataSource);
try {
    // Use connection
} finally {
    DataSourceUtils.releaseConnection(conn, dataSource);
}

// Check if connection is transactional
boolean isTransactional = DataSourceUtils.isConnectionTransactional(conn, dataSource);

Spring Boot Configuration

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: user
    password: password
    driver-class-name: org.postgresql.Driver

    hikari:
      maximum-pool-size: 10
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: SELECT 1
      pool-name: MyHikariPool

Multiple DataSources:

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.primary")
    public DataSourceProperties primaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource primaryDataSource() {
        return primaryDataSourceProperties()
            .initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.secondary")
    public DataSourceProperties secondaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    public DataSource secondaryDataSource() {
        return secondaryDataSourceProperties()
            .initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }

    @Bean
    public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

    @Bean
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }
}

spring:
  datasource:
    primary:
      url: jdbc:postgresql://localhost:5432/primary_db
      username: user
      password: password
    secondary:
      url: jdbc:postgresql://localhost:5432/secondary_db
      username: user
      password: password