Database Optimization

1. Connection Pool

HikariCP Configuration

@Configuration
public class DatabaseConfig {

    @Bean
    public HikariConfig hikariConfig() {
        HikariConfig config = new HikariConfig();
        config.setDriverClassName("org.postgresql.Driver");
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("user");
        config.setPassword("password");

        // Pool Configuration
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setIdleTimeout(300000); // 5 minutes
        config.setConnectionTimeout(20000); // 20 seconds
        config.setMaxLifetime(1200000); // 20 minutes

        // Performance Optimization
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        return config;
    }

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

Connection Pool Monitoring

@Component
public class ConnectionPoolMetrics {

    private final HikariDataSource dataSource;
    private final MeterRegistry registry;

    public ConnectionPoolMetrics(HikariDataSource dataSource, MeterRegistry registry) {
        this.dataSource = dataSource;
        this.registry = registry;
        recordMetrics();
    }

    private void recordMetrics() {
        Gauge.builder("hikari.connections.active", dataSource,
            this::getActiveConnections)
            .description("Active connections")
            .register(registry);

        Gauge.builder("hikari.connections.idle", dataSource,
            this::getIdleConnections)
            .description("Idle connections")
            .register(registry);

        Gauge.builder("hikari.connections.total", dataSource,
            this::getTotalConnections)
            .description("Total connections")
            .register(registry);
    }

    private double getActiveConnections() {
        return dataSource.getHikariPoolMXBean().getActiveConnections();
    }

    private double getIdleConnections() {
        return dataSource.getHikariPoolMXBean().getIdleConnections();
    }

    private double getTotalConnections() {
        return dataSource.getHikariPoolMXBean().getTotalConnections();
    }
}

2. Indexing Strategies

Index Configuration

@Entity
@Table(name = "users",
    indexes = {
        @Index(name = "idx_user_email", columnList = "email"),
        @Index(name = "idx_user_status", columnList = "status"),
        @Index(name = "idx_user_created_at", columnList = "created_at")
    })
public class User {

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

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

    @Enumerated(EnumType.STRING)
    private UserStatus status;

    @CreationTimestamp
    @Column(name = "created_at")
    private LocalDateTime createdAt;

    // Other fields and methods
}

Composite Index

@Entity
@Table(name = "orders",
    indexes = {
        @Index(name = "idx_order_user_status",
            columnList = "user_id, status"),
        @Index(name = "idx_order_date_status",
            columnList = "order_date, status")
    })
public class Order {

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

    @Column(name = "user_id")
    private Long userId;

    @Enumerated(EnumType.STRING)
    private OrderStatus status;

    @Column(name = "order_date")
    private LocalDateTime orderDate;

    // Other fields and methods
}

3. Query Optimization

JPQL Optimization

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Bad: Fetches all orders then filters in memory
    @Query("SELECT o FROM Order o WHERE o.status = :status")
    List<Order> findByStatus(OrderStatus status);

    // Good: Uses database indexing and filtering
    @Query("SELECT o FROM Order o WHERE o.status = :status " +
           "AND o.orderDate >= :startDate")
    List<Order> findByStatusAndDateAfter(
        OrderStatus status,
        LocalDateTime startDate);

    // Better: Pagination and sorting
    @Query("SELECT o FROM Order o WHERE o.status = :status " +
           "AND o.orderDate >= :startDate")
    Page<Order> findByStatusAndDateAfter(
        OrderStatus status,
        LocalDateTime startDate,
        Pageable pageable);
}

Native Query Optimization

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = """
        SELECT p.*
        FROM products p
        JOIN order_items oi ON p.id = oi.product_id
        WHERE oi.order_id = :orderId
        AND p.status = 'ACTIVE'
        GROUP BY p.id
        HAVING COUNT(oi.id) > 0
        """, nativeQuery = true)
    List<Product> findActiveProductsByOrderId(Long orderId);

    @Query(value = """
        SELECT p.*, COUNT(oi.id) as order_count
        FROM products p
        LEFT JOIN order_items oi ON p.id = oi.product_id
        WHERE p.category = :category
        AND p.created_at >= :startDate
        GROUP BY p.id
        HAVING COUNT(oi.id) >= :minOrders
        ORDER BY order_count DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<Product> findTopSellingProducts(
        String category,
        LocalDateTime startDate,
        int minOrders,
        int limit);
}

4. Caching Strategies

Second Level Cache

@Configuration
@EnableCaching
public class CacheConfig {

    @Bean
    public CacheManager cacheManager() {
        return new EhCacheCacheManager(ehCacheManager());
    }

    @Bean
    public net.sf.ehcache.CacheManager ehCacheManager() {
        CacheConfiguration config = new CacheConfiguration()
            .eternal(false)
            .timeToIdleSeconds(300)
            .timeToLiveSeconds(600)
            .memoryStoreEvictionPolicy("LRU")
            .maxEntriesLocalHeap(1000);

        net.sf.ehcache.config.Configuration configuration = 
            new net.sf.ehcache.config.Configuration();
        configuration.addCache(config);

        return net.sf.ehcache.CacheManager.newInstance(configuration);
    }
}

@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Product {

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

    @Column(nullable = false)
    private String name;

    @Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
    @OneToMany(mappedBy = "product")
    private Set<Review> reviews;

    // Other fields and methods
}

Query Cache

@Repository
public interface CategoryRepository extends JpaRepository<Category, Long> {

    @QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
    @Query("SELECT c FROM Category c WHERE c.active = true")
    List<Category> findAllActiveCategories();

    @Cacheable("category-products")
    @Query("SELECT p FROM Product p WHERE p.category.id = :categoryId")
    List<Product> findProductsByCategory(Long categoryId);
}

5. Best Practices

1. Batch Processing

@Service
@Transactional
public class OrderProcessingService {

    private final EntityManager entityManager;
    private final int batchSize = 100;

    public void processOrders(List<Order> orders) {
        for (int i = 0; i < orders.size(); i++) {
            Order order = orders.get(i);
            entityManager.persist(order);

            if (i % batchSize == 0) {
                entityManager.flush();
                entityManager.clear();
            }
        }
    }

    public void updateOrderStatuses(Map<Long, OrderStatus> orderStatuses) {
        StringBuilder sql = new StringBuilder(
            "UPDATE orders SET status = CASE id ");

        for (Map.Entry<Long, OrderStatus> entry : orderStatuses.entrySet()) {
            sql.append("WHEN ").append(entry.getKey())
               .append(" THEN '").append(entry.getValue()).append("' ");
        }

        sql.append("END WHERE id IN (")
           .append(String.join(",", orderStatuses.keySet().stream()
               .map(String::valueOf)
               .collect(Collectors.toList())))
           .append(")");

        entityManager.createNativeQuery(sql.toString()).executeUpdate();
    }
}

2. Lazy Loading

@Entity
public class Order {

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

    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private Set<OrderItem> items;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;

    public BigDecimal calculateTotal() {
        // Items are loaded only when needed
        return items.stream()
            .map(OrderItem::getSubtotal)
            .reduce(BigDecimal.ZERO, BigDecimal::add);
    }
}

@Service
public class OrderService {

    @Transactional(readOnly = true)
    public OrderDTO getOrderSummary(Long orderId) {
        Order order = orderRepository.findById(orderId)
            .orElseThrow(() -> new OrderNotFoundException(orderId));

        // Only basic order information is loaded
        return new OrderDTO(
            order.getId(),
            order.getStatus(),
            order.getOrderDate()
        );
    }

    @Transactional(readOnly = true)
    public OrderDetailDTO getOrderDetails(Long orderId) {
        Order order = orderRepository.findById(orderId)
            .orElseThrow(() -> new OrderNotFoundException(orderId));

        // Items are loaded when accessing them
        Set<OrderItemDTO> items = order.getItems().stream()
            .map(item -> new OrderItemDTO(
                item.getProduct().getName(),
                item.getQuantity(),
                item.getPrice()
            ))
            .collect(Collectors.toSet());

        return new OrderDetailDTO(
            order.getId(),
            order.getStatus(),
            order.getOrderDate(),
            items
        );
    }
}

3. Pagination

@RestController
@RequestMapping("/api/products")
public class ProductController {

    @GetMapping
    public Page<ProductDTO> getProducts(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size,
            @RequestParam(defaultValue = "id") String sortBy,
            @RequestParam(defaultValue = "ASC") String direction) {

        Sort.Direction sortDirection = Sort.Direction.fromString(direction.toUpperCase());
        Pageable pageable = PageRequest.of(page, size, Sort.by(sortDirection, sortBy));

        return productService.findAll(pageable)
            .map(product -> new ProductDTO(
                product.getId(),
                product.getName(),
                product.getPrice()
            ));
    }

    @GetMapping("/search")
    public Page<ProductDTO> searchProducts(
            @RequestParam String query,
            @RequestParam(required = false) String category,
            @RequestParam(required = false) BigDecimal minPrice,
            @RequestParam(required = false) BigDecimal maxPrice,
            Pageable pageable) {

        ProductSearchCriteria criteria = ProductSearchCriteria.builder()
            .query(query)
            .category(category)
            .minPrice(minPrice)
            .maxPrice(maxPrice)
            .build();

        return productService.search(criteria, pageable)
            .map(product -> new ProductDTO(
                product.getId(),
                product.getName(),
                product.getPrice()
            ));
    }
}

4. Query Optimization

@Repository
public class CustomOrderRepository {

    @PersistenceContext
    private EntityManager em;

    public List<Order> findOrdersWithDetails(OrderSearchCriteria criteria) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Order> query = cb.createQuery(Order.class);
        Root<Order> order = query.from(Order.class);

        // Optimize joins
        order.fetch("items", JoinType.LEFT);
        order.fetch("user", JoinType.LEFT);

        // Build predicates
        List<Predicate> predicates = new ArrayList<>();

        if (criteria.getStatus() != null) {
            predicates.add(cb.equal(order.get("status"), criteria.getStatus()));
        }

        if (criteria.getStartDate() != null) {
            predicates.add(cb.greaterThanOrEqualTo(
                order.get("orderDate"),
                criteria.getStartDate()
            ));
        }

        if (criteria.getEndDate() != null) {
            predicates.add(cb.lessThanOrEqualTo(
                order.get("orderDate"),
                criteria.getEndDate()
            ));
        }

        query.where(predicates.toArray(new Predicate[0]));
        query.orderBy(cb.desc(order.get("orderDate")));

        // Set query hints for optimization
        TypedQuery<Order> typedQuery = em.createQuery(query);
        typedQuery.setHint(QueryHints.HINT_FETCH_SIZE, 100);
        typedQuery.setHint(QueryHints.HINT_CACHEABLE, true);

        return typedQuery.getResultList();
    }
}

5. Performance Monitoring

@Aspect
@Component
public class QueryPerformanceMonitor {

    private final MeterRegistry registry;
    private final Logger logger = LoggerFactory.getLogger(QueryPerformanceMonitor.class);

    public QueryPerformanceMonitor(MeterRegistry registry) {
        this.registry = registry;
    }

    @Around("@annotation(org.springframework.transaction.annotation.Transactional)")
    public Object monitorQueryPerformance(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();
        String methodName = joinPoint.getSignature().getName();

        try {
            Object result = joinPoint.proceed();

            long executionTime = System.currentTimeMillis() - startTime;
            recordMetrics(methodName, executionTime);

            if (executionTime > 1000) { // 1 second threshold
                logger.warn("Slow query detected in method {}: {} ms",
                    methodName, executionTime);
            }

            return result;
        } catch (Exception e) {
            recordError(methodName);
            throw e;
        }
    }

    private void recordMetrics(String methodName, long executionTime) {
        Timer.builder("database.query.time")
            .tag("method", methodName)
            .register(registry)
            .record(executionTime, TimeUnit.MILLISECONDS);
    }

    private void recordError(String methodName) {
        Counter.builder("database.query.errors")
            .tag("method", methodName)
            .register(registry)
            .increment();
    }
}