Split View: Spring Boot DB 연결 완전 가이드: JPA, HikariCP, 트랜잭션, QueryDSL, 멀티 DB

<!-- 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>

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

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

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

spring:
  jpa:
    open-in-view: false

@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;
    }
}

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

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

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

    @LastModifiedDate
    private LocalDateTime updatedAt;
}

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

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

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

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

@Configuration
public class QueryDslConfig {

    @PersistenceContext
    private EntityManager entityManager;

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

@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;
    }
}

@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
    }
}

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

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

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

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

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

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 100

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

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

public class RoutingDataSource extends AbstractRoutingDataSource {

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

public enum DataSourceType {
    READ, WRITE
}

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

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

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

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

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

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

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

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