Spring Boot Database Connection Complete Guide: JPA, HikariCP, Transactions, QueryDSL, Multi-DB

Dependency Configuration
DataSource Configuration (HikariCP)
Entity Design Patterns
Repository Patterns
QueryDSL Setup and Usage
Transaction Management
Solving the N+1 Problem
Multi-DataSource Configuration
R2DBC Async Connection
Slow Query Logging and Monitoring
Quiz

1. Dependency Configuration

Maven Dependencies

<!-- JPA/Hibernate -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- PostgreSQL driver -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- MySQL driver (optional) -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- QueryDSL -->
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <classifier>jakarta</classifier>
</dependency>
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <classifier>jakarta</classifier>
    <scope>provided</scope>
</dependency>

<!-- Flyway migration -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

2. DataSource Configuration (HikariCP)

Spring Boot 2.x+ uses HikariCP as the default connection pool.

Complete Configuration Example

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: user
    password: secret
    driver-class-name: org.postgresql.Driver
    hikari:
      pool-name: HikariCP-Main
      maximum-pool-size: 10 # Maximum connections
      minimum-idle: 5 # Minimum idle connections
      connection-timeout: 30000 # Connection acquisition timeout (30 seconds)
      idle-timeout: 600000 # Idle connection retention time (10 minutes)
      max-lifetime: 1800000 # Maximum connection lifetime (30 minutes)
      connection-test-query: SELECT 1
      auto-commit: true
      leak-detection-threshold: 60000 # Connection leak detection (60 seconds)
  jpa:
    hibernate:
      ddl-auto: validate # prod: validate, dev: update/create-drop
    show-sql: false
    open-in-view: false # Disable OSIV (recommended)
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true
        default_batch_fetch_size: 100
        jdbc:
          batch_size: 50
        order_inserts: true
        order_updates: true
        dialect: org.hibernate.dialect.PostgreSQLDialect

HikariCP Pool Size Formula

Optimal pool size = Tn x (Cm - 1) + 1

Tn: Maximum number of concurrent threads
Cm: Maximum connections needed per single transaction

A commonly used practical formula:

Connections = (CPU cores x 2) + effective spindle count

The HikariCP documentation notes that a pool of 10 connections satisfies most workloads.

Disabling OSIV (Open Session In View)

spring:
  jpa:
    open-in-view: false

Enabling OSIV holds a DB connection open for the entire HTTP response lifecycle, which can exhaust the connection pool. Managing transactions explicitly in the service layer is the recommended pattern.

3. Entity Design Patterns

Basic Entity Structure

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_user_email", columnList = "email"),
    @Index(name = "idx_user_created_at", columnList = "created_at")
})
@EntityListeners(AuditingEntityListener.class)
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 100)
    private String email;

    @Column(nullable = false, length = 50)
    private String name;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 20)
    private UserStatus status = UserStatus.ACTIVE;

    @CreatedDate
    @Column(updatable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    private LocalDateTime updatedAt;

    @Version
    private Long version;  // Optimistic Locking

    @Builder
    public User(String email, String name) {
        this.email = email;
        this.name = name;
    }
}

Enabling JPA Auditing

@Configuration
@EnableJpaAuditing
public class JpaConfig {
    // Add AuditorAware implementation if needed
}

BaseEntity Abstract Class

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@Getter
public abstract class BaseEntity {

    @CreatedDate
    @Column(updatable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    private LocalDateTime updatedAt;
}

4. Repository Patterns

Using JpaRepository Built-in Methods

public interface UserRepository extends JpaRepository<User, Long> {

    // Method name-based query
    Optional<User> findByEmail(String email);
    List<User> findByStatusAndCreatedAtAfter(UserStatus status, LocalDateTime date);
    boolean existsByEmail(String email);
    long countByStatus(UserStatus status);

    // @Query with JPQL
    @Query("SELECT u FROM User u WHERE u.email = :email AND u.status = 'ACTIVE'")
    Optional<User> findActiveUserByEmail(@Param("email") String email);

    // Native Query
    @Query(value = "SELECT * FROM users WHERE created_at > :date LIMIT :limit",
           nativeQuery = true)
    List<User> findRecentUsersNative(@Param("date") LocalDateTime date,
                                     @Param("limit") int limit);

    // Interface-based Projection
    @Query("SELECT u.id AS id, u.email AS email FROM User u WHERE u.status = :status")
    List<UserSummary> findUserSummariesByStatus(@Param("status") UserStatus status);

    // Modifying Query
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query("UPDATE User u SET u.status = :status WHERE u.id IN :ids")
    int updateStatusByIds(@Param("ids") List<Long> ids, @Param("status") UserStatus status);
}

Projection Interface

public interface UserSummary {
    Long getId();
    String getEmail();
}

Specification API (Dynamic Queries)

public class UserSpecification {

    public static Specification<User> hasStatus(UserStatus status) {
        return (root, query, criteriaBuilder) ->
            criteriaBuilder.equal(root.get("status"), status);
    }

    public static Specification<User> emailContains(String keyword) {
        return (root, query, criteriaBuilder) ->
            criteriaBuilder.like(root.get("email"), "%" + keyword + "%");
    }

    public static Specification<User> createdAfter(LocalDateTime date) {
        return (root, query, criteriaBuilder) ->
            criteriaBuilder.greaterThan(root.get("createdAt"), date);
    }
}

// Usage
List<User> users = userRepository.findAll(
    UserSpecification.hasStatus(UserStatus.ACTIVE)
        .and(UserSpecification.emailContains("example.com"))
);

5. QueryDSL Setup and Usage

QueryDSL Maven Plugin Configuration

<plugin>
    <groupId>com.mysema.maven</groupId>
    <artifactId>apt-maven-plugin</artifactId>
    <version>1.1.3</version>
    <executions>
        <execution>
            <goals>
                <goal>process</goal>
            </goals>
            <configuration>
                <outputDirectory>target/generated-sources/java</outputDirectory>
                <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
        </execution>
    </executions>
</plugin>

Registering JPAQueryFactory Bean

@Configuration
public class QueryDslConfig {

    @PersistenceContext
    private EntityManager entityManager;

    @Bean
    public JPAQueryFactory jpaQueryFactory() {
        return new JPAQueryFactory(entityManager);
    }
}

QueryDSL Repository Implementation

@Repository
@RequiredArgsConstructor
public class UserQueryRepository {

    private final JPAQueryFactory queryFactory;

    public Page<User> findActiveUsers(UserSearchCondition condition, Pageable pageable) {
        List<User> content = queryFactory
            .selectFrom(user)
            .where(
                statusEq(condition.getStatus()),
                emailContains(condition.getEmail()),
                createdAtAfter(condition.getFromDate())
            )
            .orderBy(getOrderSpecifier(pageable))
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

        JPAQuery<Long> countQuery = queryFactory
            .select(user.count())
            .from(user)
            .where(
                statusEq(condition.getStatus()),
                emailContains(condition.getEmail())
            );

        return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
    }

    private BooleanExpression statusEq(UserStatus status) {
        return status != null ? user.status.eq(status) : null;
    }

    private BooleanExpression emailContains(String email) {
        return StringUtils.hasText(email) ? user.email.containsIgnoreCase(email) : null;
    }

    private BooleanExpression createdAtAfter(LocalDateTime date) {
        return date != null ? user.createdAt.after(date) : null;
    }
}

6. Transaction Management

Key @Transactional Options

@Service
@Transactional(readOnly = true)  // Class-level default: read-only
@RequiredArgsConstructor
public class UserService {

    private final UserRepository userRepository;

    // Read-only (inherits class-level readOnly=true)
    public UserResponse getUser(Long id) {
        return userRepository.findById(id)
            .map(UserResponse::from)
            .orElseThrow(() -> new EntityNotFoundException("User not found: " + id));
    }

    // Write operation: override with readOnly=false
    @Transactional
    public UserResponse createUser(CreateUserRequest request) {
        if (userRepository.existsByEmail(request.getEmail())) {
            throw new DuplicateEmailException(request.getEmail());
        }
        User user = User.builder()
            .email(request.getEmail())
            .name(request.getName())
            .build();
        return UserResponse.from(userRepository.save(user));
    }

    // Roll back on all exceptions
    @Transactional(rollbackFor = Exception.class)
    public void processPayment(PaymentRequest request) {
        // Payment processing logic
    }

    // Independent transaction (commits/rolls back independently of parent)
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void saveAuditLog(String message) {
        // Audit log must always be saved
    }

    // Run without a transaction
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public void callExternalApi() {
        // External API call with no DB operations
    }
}

Transaction Propagation Levels

Propagation	Description
REQUIRED	Use existing transaction; create new one if none exists (default)
REQUIRES_NEW	Always create a new transaction; suspend the existing one
SUPPORTS	Use existing transaction if available; proceed without one otherwise
NOT_SUPPORTED	Execute without a transaction; suspend any existing one
MANDATORY	Requires existing transaction; throw exception if none exists
NEVER	Execute without a transaction; throw exception if one exists
NESTED	Execute within a nested transaction using a Savepoint

Isolation Levels

// Prevent phantom reads
@Transactional(isolation = Isolation.SERIALIZABLE)

// Guarantee repeatable reads
@Transactional(isolation = Isolation.REPEATABLE_READ)

// Prevent dirty reads (most DB default)
@Transactional(isolation = Isolation.READ_COMMITTED)

7. Solving the N+1 Problem

The N+1 problem occurs when one query retrieves N records, then N additional queries fire to load related data for each record.

Problem Scenario

// N+1 example
List<Order> orders = orderRepository.findAll();
orders.forEach(order -> {
    System.out.println(order.getUser().getName()); // One User query per Order
});

Solution 1: @EntityGraph

@EntityGraph(attributePaths = {"user", "orderItems"})
@Query("SELECT o FROM Order o WHERE o.status = :status")
List<Order> findByStatusWithUserAndItems(@Param("status") OrderStatus status);

Solution 2: Fetch Join

@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.user JOIN FETCH o.orderItems WHERE o.status = :status")
List<Order> findByStatusWithFetch(@Param("status") OrderStatus status);

Warning: Combining collection Fetch Join with pagination causes Hibernate to load all data into memory before applying the page (HHH90003004 warning), risking OutOfMemoryError. Use @BatchSize instead when pagination is required.

Solution 3: BatchSize

@Entity
public class User {
    @OneToMany(mappedBy = "user")
    @BatchSize(size = 100)   // Load 100 at a time using IN queries
    private List<Order> orders;
}

Or configure globally:

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 100

8. Multi-DataSource Configuration

Configuring Two DataSources

@Configuration
@EnableTransactionManagement
public class DataSourceConfig {

    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties("spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties("spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDataSource") DataSource dataSource) {
        return builder
            .dataSource(dataSource)
            .packages("com.example.domain.primary")
            .persistenceUnit("primary")
            .build();
    }

    @Primary
    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") EntityManagerFactory emf) {
        return new JpaTransactionManager(emf);
    }
}

application.yml Multi-DataSource Configuration

spring:
  datasource:
    primary:
      url: jdbc:postgresql://primary-db:5432/maindb
      username: ${DB_PRIMARY_USERNAME}
      password: ${DB_PRIMARY_PASSWORD}
      hikari:
        maximum-pool-size: 10
    secondary:
      url: jdbc:postgresql://secondary-db:5432/analyticsdb
      username: ${DB_SECONDARY_USERNAME}
      password: ${DB_SECONDARY_PASSWORD}
      hikari:
        maximum-pool-size: 5

Read/Write Separation (AbstractRoutingDataSource)

public class RoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
            ? DataSourceType.READ
            : DataSourceType.WRITE;
    }
}

public enum DataSourceType {
    READ, WRITE
}

9. R2DBC Async Connection

When using the reactive stack (Spring WebFlux), configure asynchronous DB connections with R2DBC.

Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-r2dbc</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>r2dbc-postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Configuration

spring:
  r2dbc:
    url: r2dbc:postgresql://localhost:5432/mydb
    username: user
    password: secret
    pool:
      initial-size: 5
      max-size: 10
      max-idle-time: 30m
      validation-query: SELECT 1

R2DBC Repository

public interface UserReactiveRepository extends ReactiveCrudRepository<User, Long> {
    Mono<User> findByEmail(String email);
    Flux<User> findByStatus(UserStatus status);
}

@Service
@RequiredArgsConstructor
public class UserReactiveService {

    private final UserReactiveRepository userRepository;

    public Mono<UserResponse> getUser(Long id) {
        return userRepository.findById(id)
            .map(UserResponse::from)
            .switchIfEmpty(Mono.error(new EntityNotFoundException("User not found: " + id)));
    }

    public Flux<UserResponse> getAllActiveUsers() {
        return userRepository.findByStatus(UserStatus.ACTIVE)
            .map(UserResponse::from);
    }
}

10. Slow Query Logging and Monitoring

p6spy Configuration

<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.9.0</version>
</dependency>

src/main/resources/spy.properties:

appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=%(currentTime)|%(executionTime)ms|%(category)|connection%(connectionId)|%(sqlSingleLine)
filter=true
execution=true

Enabling Hibernate Statistics

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        session:
          events:
            log:
              LOG_QUERIES_SLOWER_THAN_MS: 100 # Log queries taking over 100ms

Micrometer + Prometheus Monitoring

management:
  endpoints:
    web:
      exposure:
        include: metrics,health,prometheus
  metrics:
    export:
      prometheus:
        enabled: true

Key monitoring metrics:

jdbc.connections.active: Currently active connection count
jdbc.connections.max: Maximum connection pool size
hibernate.query.executions.total: Total query execution count
hibernate.second.level.cache.hits: Second-level cache hit rate

Actuator Endpoints

GET /actuator/metrics/jdbc.connections.active
GET /actuator/metrics/jdbc.connections.pending
GET /actuator/metrics/hikaricp.connections.timeout

11. Quiz

Q1. What problems arise from setting HikariCP's maximum-pool-size too high?

Answer: Exceeding the DB server's maximum connection limit, or performance degradation due to excessive context switching.

Explanation: More connections do not automatically mean better performance. Each connection consumes memory and CPU resources on the DB server side. The HikariCP documentation states that a pool of fewer than 10 connections satisfies most workloads. Even if you open 200 connections on an 8-core server, the CPU can only process 8 tasks simultaneously, leaving the remaining connections idle. The optimal pool size should be determined through load testing.

Q2. Why does @Transactional(readOnly=true) improve performance?

Answer: It sends a read-only hint to the database and disables unnecessary dirty checking, reducing memory usage and CPU overhead.

Explanation: With readOnly=true, Hibernate skips dirty checking in the first-level cache (Persistence Context), reducing memory consumption and CPU overhead. Some DB drivers use this hint to route queries to read replicas or to use optimized execution plans. PostgreSQL can also skip snapshot creation at transaction start as an additional optimization.

Q3. Why should you avoid using Fetch Join on collections together with pagination?

Answer: Combining collection (one-to-many) Fetch Join with pagination causes Hibernate to load all data into memory before applying the page limit (HHH90003004 warning), risking OutOfMemoryError.

Explanation: Collection Fetch Joins produce a Cartesian product, increasing the number of result rows. Applying LIMIT/OFFSET at the DB level would produce unexpected results, so Hibernate loads all data into memory and performs pagination in Java. Use @BatchSize or the default_batch_fetch_size setting instead — they resolve N+1 problems using IN queries while allowing pagination to work correctly.

Q4. What is a representative use case for REQUIRES_NEW transaction propagation?

Answer: Operations like audit logging or email notification that must be saved/executed regardless of the parent transaction's success or failure.

Explanation: A method with REQUIRES_NEW suspends the parent transaction and starts an independent new one. Even if the parent transaction rolls back, the REQUIRES_NEW transaction commits independently. For example, even when order processing fails, the "order attempt failed" audit log must still be persisted to the database.

Q5. Can you use standard JPA @Transactional when using R2DBC?

Answer: Not directly. R2DBC is a reactive stack, so it requires ReactiveTransactionManager and TransactionalOperator instead of traditional Thread-Local-based transaction management.

Explanation: Unlike blocking JDBC, R2DBC operates asynchronously and non-blockingly. The standard @Transactional relies on Thread-Local storage and does not work correctly in reactive pipelines. Spring Boot automatically registers an R2dbcTransactionManager in R2DBC environments. @Transactional is also supported in reactive contexts, but only works correctly on methods that return Mono or Flux.

Table of Contents