ORM và Database Optimization

1. Hibernate Performance Tuning

1.1 Caching Strategies

// Entity Level Cache
@Entity
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String username;

    // Other fields
}

// Query Cache
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @QueryHints(value = { @QueryHint(name = "org.hibernate.cacheable", value = "true") })
    List<User> findByStatus(UserStatus status);
}

// Cache Configuration
@Configuration
@EnableCaching
public class CacheConfig {
    @Bean
    public CacheManager cacheManager() {
        return new EhCacheCacheManager(ehCacheManagerFactoryBean().getObject());
    }

    @Bean
    public EhCacheManagerFactoryBean ehCacheManagerFactoryBean() {
        EhCacheManagerFactoryBean factory = new EhCacheManagerFactoryBean();
        factory.setConfigLocation(new ClassPathResource("ehcache.xml"));
        factory.setShared(true);
        return factory;
    }
}

1.2 Lazy Loading và Fetch Strategies

@Entity
public class User {
    @OneToMany(
        mappedBy = "user",
        fetch = FetchType.LAZY,
        cascade = CascadeType.ALL
    )
    private List<Order> orders;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "user_roles")
    private Set<Role> roles;
}

// Using EntityGraph
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @EntityGraph(attributePaths = {"orders", "roles"})
    Optional<User> findWithOrdersAndRolesById(Long id);

    @EntityGraph(value = "User.orders")
    List<User> findByStatus(UserStatus status);
}

@NamedEntityGraph(
    name = "User.orders",
    attributeNodes = {
        @NamedAttributeNode("orders")
    }
)
@Entity
public class User {
    // Entity definition
}

2. Query Optimization

2.1 JPQL Optimization

// Avoiding N+1 Problem
@Query("SELECT DISTINCT u FROM User u " +
       "LEFT JOIN FETCH u.orders " +
       "LEFT JOIN FETCH u.roles " +
       "WHERE u.status = :status")
List<User> findByStatusWithOrdersAndRoles(
    @Param("status") UserStatus status);

// Pagination with Join Fetch
@Query(value = "SELECT DISTINCT u FROM User u " +
               "LEFT JOIN FETCH u.roles " +
               "WHERE u.status = :status",
       countQuery = "SELECT COUNT(DISTINCT u) FROM User u " +
                    "WHERE u.status = :status")
Page<User> findByStatusWithRoles(
    @Param("status") UserStatus status,
    Pageable pageable);

2.2 Native Query Optimization

@Query(
    value = "SELECT u.*, " +
            "COUNT(o.id) as order_count, " +
            "SUM(o.total_amount) as total_spent " +
            "FROM users u " +
            "LEFT JOIN orders o ON u.id = o.user_id " +
            "WHERE u.status = :status " +
            "GROUP BY u.id",
    nativeQuery = true
)
List<UserStatistics> getUserStatistics(
    @Param("status") String status);

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query(
        value = "SELECT * FROM users u " +
                "WHERE u.status = :status " +
                "AND EXISTS (" +
                "    SELECT 1 FROM orders o " +
                "    WHERE o.user_id = u.id " +
                "    AND o.created_at >= :startDate" +
                ")",
        nativeQuery = true
    )
    List<User> findActiveUsersWithRecentOrders(
        @Param("status") String status,
        @Param("startDate") Date startDate);
}

3. Batch Processing

3.1 Batch Insert/Update

@Service
@Transactional
public class UserService {
    @PersistenceContext
    private EntityManager em;

    public void saveUsers(List<User> users) {
        int batchSize = 50;
        for (int i = 0; i < users.size(); i++) {
            em.persist(users.get(i));
            if (i % batchSize == 0) {
                em.flush();
                em.clear();
            }
        }
    }

    @Modifying
    @Query(
        value = "UPDATE users SET status = :newStatus " +
                "WHERE id IN :userIds",
        nativeQuery = true
    )
    void updateUserStatusInBatch(
        @Param("newStatus") String newStatus,
        @Param("userIds") List<Long> userIds);
}

3.2 Batch Processing với Spring Batch

@Configuration
@EnableBatchProcessing
public class BatchConfig {
    @Bean
    public Job importUsersJob(JobBuilderFactory jobs, Step step1) {
        return jobs.get("importUsersJob")
            .incrementer(new RunIdIncrementer())
            .flow(step1)
            .end()
            .build();
    }

    @Bean
    public Step step1(StepBuilderFactory stepBuilderFactory,
                     ItemReader<User> reader,
                     ItemProcessor<User, User> processor,
                     ItemWriter<User> writer) {
        return stepBuilderFactory.get("step1")
            .<User, User>chunk(10)
            .reader(reader)
            .processor(processor)
            .writer(writer)
            .build();
    }

    @Bean
    public FlatFileItemReader<User> reader() {
        return new FlatFileItemReaderBuilder<User>()
            .name("userItemReader")
            .resource(new ClassPathResource("users.csv"))
            .delimited()
            .names("firstName", "lastName", "email")
            .targetType(User.class)
            .build();
    }
}

4. Database Indexing

4.1 Index Configuration

@Entity
@Table(
    name = "users",
    indexes = {
        @Index(
            name = "idx_user_email",
            columnList = "email",
            unique = true
        ),
        @Index(
            name = "idx_user_status_created_at",
            columnList = "status,created_at"
        )
    }
)
public class User {
    // Entity definition
}

4.2 Composite Keys

@Entity
public class OrderItem {
    @EmbeddedId
    private OrderItemId id;

    private int quantity;
    private BigDecimal price;
}

@Embeddable
public class OrderItemId implements Serializable {
    @Column(name = "order_id")
    private Long orderId;

    @Column(name = "product_id")
    private Long productId;

    // Constructors, equals, hashCode
}

5. Connection Pool Configuration

5.1 HikariCP Configuration

@Configuration
public class DatabaseConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    public HikariConfig hikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource dataSource() {
        return new HikariDataSource(hikariConfig());
    }
}

// application.properties
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.max-lifetime=1200000

5.2 Multiple DataSource Configuration

@Configuration
public class MultipleDataSourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.main")
    public DataSourceProperties mainDataSourceProperties() {
        return new DataSourceProperties();
    }

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

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

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

6. Monitoring và Profiling

6.1 SQL Logging

# application.properties
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

6.2 Performance Monitoring

@Aspect
@Component
public class QueryPerformanceMonitor {
    private static final Logger log = 
        LoggerFactory.getLogger(QueryPerformanceMonitor.class);

    @Around("execution(* org.springframework.data.jpa.repository.JpaRepository+.*(..))")
    public Object monitorQueryPerformance(ProceedingJoinPoint pjp) 
        throws Throwable {

        long start = System.currentTimeMillis();
        Object result = pjp.proceed();
        long executionTime = System.currentTimeMillis() - start;

        log.info("{}.{} took {}ms",
            pjp.getSignature().getDeclaringTypeName(),
            pjp.getSignature().getName(),
            executionTime);

        return result;
    }
}