# Advanced Spring Data JPA: Queries, Transactions, and Performance Optimization Spring Data JPA simplifies database access in Java applications, but mastering its advanced features is crucial for building high-performance, maintainable applications. This comprehensive guide covers advanced querying, transaction management, and optimization techniques. ## Advanced Query Techniques ### Custom Query Methods Spring Data JPA provides powerful query derivation from method names: ```java @Repository public interface UserRepository extends JpaRepository<User, Long> { // Basic query methods List<User> findByLastName(String lastName); List<User> findByFirstNameAndLastName(String firstName, String lastName); List<User> findByAgeGreaterThan(Integer age); List<User> findByEmailContaining(String emailPart); // Sorting and pagination List<User> findByLastNameOrderByFirstNameAsc(String lastName); Page<User> findByDepartment(String department, Pageable pageable); // Limiting results List<User> findTop10ByOrderByCreatedDateDesc(); User findFirstByEmailOrderByCreatedDateDesc(String email); // Boolean queries List<User> findByActiveTrue(); List<User> findByActiveFalse(); // Date queries List<User> findByCreatedDateBetween(LocalDateTime start, LocalDateTime end); List<User> findByCreatedDateAfter(LocalDateTime date); // Collection queries List<User> findByRolesIn(Collection<Role> roles); List<User> findBySkillsContaining(String skill); // Null checks List<User> findByManagerIsNull(); List<User> findByManagerIsNotNull(); // Case insensitive List<User> findByFirstNameIgnoreCase(String firstName); List<User> findByEmailContainingIgnoreCase(String email); } ``` ### JPQL Queries ```java @Repository public interface UserRepository extends JpaRepository<User, Long> { // Basic JPQL @Query("SELECT u FROM User u WHERE u.email = ?1") Optional<User> findByEmail(String email); // Named parameters @Query("SELECT u FROM User u WHERE u.firstName = :firstName AND u.lastName = :lastName") List<User> findByFullName(@Param("firstName") String firstName, @Param("lastName") String lastName); // Join queries @Query("SELECT u FROM User u JOIN u.roles r WHERE r.name = :roleName") List<User> findByRoleName(@Param("roleName") String roleName); // Fetch joins to avoid N+1 problem @Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.roles WHERE u.active = true") List<User> findActiveUsersWithRoles(); // Projection queries @Query("SELECT new com.example.dto.UserSummaryDto(u.id, u.firstName, u.lastName, u.email) " + "FROM User u WHERE u.department = :department") List<UserSummaryDto> findUserSummariesByDepartment(@Param("department") String department); // Aggregate functions @Query("SELECT COUNT(u) FROM User u WHERE u.active = true") long countActiveUsers(); @Query("SELECT u.department, COUNT(u) FROM User u GROUP BY u.department") List<Object[]> countUsersByDepartment(); // Subqueries @Query("SELECT u FROM User u WHERE u.salary > " + "(SELECT AVG(u2.salary) FROM User u2 WHERE u2.department = u.department)") List<User> findUsersWithAboveAverageSalary(); // Update queries @Modifying @Query("UPDATE User u SET u.active = false WHERE u.lastLoginDate < :cutoffDate") int deactivateInactiveUsers(@Param("cutoffDate") LocalDateTime cutoffDate); // Delete queries @Modifying @Query("DELETE FROM User u WHERE u.active = false AND u.createdDate < :cutoffDate") int deleteInactiveUsers(@Param("cutoffDate") LocalDateTime cutoffDate); } ``` ### Native SQL Queries ```java @Repository public interface UserRepository extends JpaRepository<User, Long> { // Native SQL query @Query(value = "SELECT * FROM users u WHERE u.email = ?1", nativeQuery = true) Optional<User> findByEmailNative(String email); // Complex native query with joins @Query(value = """ SELECT u.*, d.name as department_name FROM users u LEFT JOIN departments d ON u.department_id = d.id WHERE u.salary BETWEEN :minSalary AND :maxSalary ORDER BY u.salary DESC """, nativeQuery = true) List<Object[]> findUsersBySalaryRange(@Param("minSalary") BigDecimal minSalary, @Param("maxSalary") BigDecimal maxSalary); // Native query with pagination @Query(value = "SELECT * FROM users WHERE department = :dept", countQuery = "SELECT count(*) FROM users WHERE department = :dept", nativeQuery = true) Page<User> findByDepartmentNative(@Param("dept") String department, Pageable pageable); // Database-specific functions @Query(value = "SELECT * FROM users WHERE LOWER(first_name) LIKE LOWER(CONCAT('%', :name, '%'))", nativeQuery = true) List<User> findByNameContainingIgnoreCaseNative(@Param("name") String name); } ``` ## Custom Repository Implementation ### Custom Repository Interface ```java public interface CustomUserRepository { List<User> findUsersWithComplexCriteria(UserSearchCriteria criteria); Page<User> findUsersWithDynamicQuery(Map<String, Object> filters, Pageable pageable); List<UserStatistics> getUserStatistics(); void bulkUpdateUserStatus(List<Long> userIds, UserStatus status); } @Repository public class CustomUserRepositoryImpl implements CustomUserRepository { @PersistenceContext private EntityManager entityManager; @Override public List<User> findUsersWithComplexCriteria(UserSearchCriteria criteria) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<User> query = cb.createQuery(User.class); Root<User> user = query.from(User.class); List<Predicate> predicates = new ArrayList<>(); if (criteria.getFirstName() != null) { predicates.add(cb.like(cb.lower(user.get("firstName")), "%" + criteria.getFirstName().toLowerCase() + "%")); } if (criteria.getMinAge() != null) { predicates.add(cb.greaterThanOrEqualTo(user.get("age"), criteria.getMinAge())); } if (criteria.getMaxAge() != null) { predicates.add(cb.lessThanOrEqualTo(user.get("age"), criteria.getMaxAge())); } if (criteria.getDepartments() != null && !criteria.getDepartments().isEmpty()) { predicates.add(user.get("department").in(criteria.getDepartments())); } if (criteria.getHasManager() != null) { if (criteria.getHasManager()) { predicates.add(cb.isNotNull(user.get("manager"))); } else { predicates.add(cb.isNull(user.get("manager"))); } } query.where(predicates.toArray(new Predicate[0])); // Add sorting if (criteria.getSortBy() != null) { if (criteria.getSortDirection() == SortDirection.DESC) { query.orderBy(cb.desc(user.get(criteria.getSortBy()))); } else { query.orderBy(cb.asc(user.get(criteria.getSortBy()))); } } TypedQuery<User> typedQuery = entityManager.createQuery(query); if (criteria.getMaxResults() != null) { typedQuery.setMaxResults(criteria.getMaxResults()); } return typedQuery.getResultList(); } @Override public Page<User> findUsersWithDynamicQuery(Map<String, Object> filters, Pageable pageable) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); // Count query CriteriaQuery<Long> countQuery = cb.createQuery(Long.class); Root<User> countRoot = countQuery.from(User.class); countQuery.select(cb.count(countRoot)); List<Predicate> countPredicates = buildPredicates(cb, countRoot, filters); if (!countPredicates.isEmpty()) { countQuery.where(countPredicates.toArray(new Predicate[0])); } Long total = entityManager.createQuery(countQuery).getSingleResult(); // Data query CriteriaQuery<User> dataQuery = cb.createQuery(User.class); Root<User> dataRoot = dataQuery.from(User.class); List<Predicate> dataPredicates = buildPredicates(cb, dataRoot, filters); if (!dataPredicates.isEmpty()) { dataQuery.where(dataPredicates.toArray(new Predicate[0])); } // Add sorting if (pageable.getSort().isSorted()) { List<Order> orders = new ArrayList<>(); for (Sort.Order order : pageable.getSort()) { if (order.isAscending()) { orders.add(cb.asc(dataRoot.get(order.getProperty()))); } else { orders.add(cb.desc(dataRoot.get(order.getProperty()))); } } dataQuery.orderBy(orders); } TypedQuery<User> typedQuery = entityManager.createQuery(dataQuery); typedQuery.setFirstResult((int) pageable.getOffset()); typedQuery.setMaxResults(pageable.getPageSize()); List<User> content = typedQuery.getResultList(); return new PageImpl<>(content, pageable, total); } private List<Predicate> buildPredicates(CriteriaBuilder cb, Root<User> root, Map<String, Object> filters) { List<Predicate> predicates = new ArrayList<>(); filters.forEach((key, value) -> { if (value != null) { switch (key) { case "firstName": predicates.add(cb.like(cb.lower(root.get("firstName")), "%" + value.toString().toLowerCase() + "%")); break; case "email": predicates.add(cb.equal(root.get("email"), value)); break; case "minAge": predicates.add(cb.greaterThanOrEqualTo(root.get("age"), (Integer) value)); break; case "maxAge": predicates.add(cb.lessThanOrEqualTo(root.get("age"), (Integer) value)); break; case "active": predicates.add(cb.equal(root.get("active"), value)); break; } } }); return predicates; } @Override @Transactional public void bulkUpdateUserStatus(List<Long> userIds, UserStatus status) { String jpql = "UPDATE User u SET u.status = :status, u.lastModifiedDate = :now " + "WHERE u.id IN :userIds"; entityManager.createQuery(jpql) .setParameter("status", status) .setParameter("now", LocalDateTime.now()) .setParameter("userIds", userIds) .executeUpdate(); } } // Extend both JpaRepository and custom repository public interface UserRepository extends JpaRepository<User, Long>, CustomUserRepository { // Standard repository methods are available here } ``` ## Transaction Management ### Declarative Transactions ```java @Service @Transactional public class UserService { @Autowired private UserRepository userRepository; @Autowired private AuditService auditService; @Autowired private EmailService emailService; // Read-only transaction for better performance @Transactional(readOnly = true) public List<User> getAllActiveUsers() { return userRepository.findByActiveTrue(); } // Default transaction (read-write) public User createUser(CreateUserRequest request) { User user = User.builder() .firstName(request.getFirstName()) .lastName(request.getLastName()) .email(request.getEmail()) .active(true) .createdDate(LocalDateTime.now()) .build(); User savedUser = userRepository.save(user); // This will be part of the same transaction auditService.logUserCreation(savedUser.getId()); return savedUser; } // Custom transaction configuration @Transactional( propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_COMMITTED, timeout = 30, rollbackFor = {Exception.class}, noRollbackFor = {ValidationException.class} ) public void processUserBatch(List<CreateUserRequest> requests) { for (CreateUserRequest request : requests) { try { createUser(request); } catch (ValidationException e) { // Log but don't rollback transaction log.warn("Validation failed for user: {}", request.getEmail(), e); } } } // Programmatic transaction control @Transactional(propagation = Propagation.NEVER) public void processUsersWithManualTransactions(List<CreateUserRequest> requests) { TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager); transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED); for (CreateUserRequest request : requests) { transactionTemplate.executeWithoutResult(status -> { try { createUserInternal(request); } catch (Exception e) { status.setRollbackOnly(); log.error("Failed to create user: {}", request.getEmail(), e); } }); } } // Nested transaction example @Transactional public void updateUserWithNotification(Long userId, UpdateUserRequest request) { User user = userRepository.findById(userId) .orElseThrow(() -> new UserNotFoundException("User not found")); // Update user data user.setFirstName(request.getFirstName()); user.setLastName(request.getLastName()); user.setLastModifiedDate(LocalDateTime.now()); userRepository.save(user); // Send notification in separate transaction sendNotificationInNewTransaction(user); } @Transactional(propagation = Propagation.REQUIRES_NEW) public void sendNotificationInNewTransaction(User user) { try { emailService.sendUserUpdateNotification(user); auditService.logNotificationSent(user.getId()); } catch (Exception e) { // This transaction will rollback independently log.error("Failed to send notification for user: {}", user.getId(), e); throw e; } } } ``` ### Transaction Event Listeners ```java @Component public class UserTransactionEventListener { @Autowired private CacheManager cacheManager; @Autowired private SearchIndexService searchIndexService; // Execute after successful transaction commit @TransactionalEventListener(phase = TransactionPhase.AFTER_COMMIT) public void handleUserCreated(UserCreatedEvent event) { // Clear relevant caches cacheManager.getCache("users").evict(event.getUserId()); cacheManager.getCache("usersByDepartment").clear(); // Update search index searchIndexService.indexUser(event.getUserId()); // Send welcome email (non-transactional) emailService.sendWelcomeEmail(event.getUserId()); } // Execute after transaction rollback @TransactionalEventListener(phase = TransactionPhase.AFTER_ROLLBACK) public void handleUserCreationFailed(UserCreatedEvent event) { log.error("User creation failed and was rolled back: {}", event.getUserId()); // Cleanup any external resources if needed } // Execute before transaction commit @TransactionalEventListener(phase = TransactionPhase.BEFORE_COMMIT) public void validateUserBeforeCommit(UserCreatedEvent event) { // Perform final validation before commit User user = userRepository.findById(event.getUserId()) .orElseThrow(() -> new IllegalStateException("User not found")); if (!isValidUser(user)) { throw new ValidationException("User validation failed before commit"); } } // Execute after transaction completion (commit or rollback) @TransactionalEventListener(phase = TransactionPhase.AFTER_COMPLETION) public void handleUserTransactionCompleted(UserCreatedEvent event) { // Cleanup resources regardless of transaction outcome cleanupTempResources(event.getUserId()); } } ``` ## Performance Optimization ### Entity Relationships and Fetching ```java @Entity @Table(name = "users") @NamedEntityGraph( name = "User.withRolesAndDepartment", attributeNodes = { @NamedAttributeNode("roles"), @NamedAttributeNode("department") } ) public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; // Lazy loading by default for collections @ManyToMany(fetch = FetchType.LAZY) @JoinTable( name = "user_roles", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id") ) private Set<Role> roles = new HashSet<>(); // Eager loading for single-valued associations when needed @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "department_id") private Department department; // Use @BatchSize to optimize N+1 queries @OneToMany(mappedBy = "user", fetch = FetchType.LAZY) @BatchSize(size = 20) private List<Order> orders = new ArrayList<>(); } @Repository public interface UserRepository extends JpaRepository<User, Long> { // Use EntityGraph to control fetching @EntityGraph("User.withRolesAndDepartment") @Query("SELECT u FROM User u WHERE u.active = true") List<User> findActiveUsersWithRolesAndDepartment(); // Dynamic EntityGraph @EntityGraph(attributePaths = {"roles", "department", "manager"}) Optional<User> findById(Long id); // Projection to fetch only needed fields @Query("SELECT new com.example.dto.UserSummary(u.id, u.firstName, u.lastName, u.email) " + "FROM User u WHERE u.department.name = :departmentName") List<UserSummary> findUserSummariesByDepartment(@Param("departmentName") String departmentName); } ``` ### Caching Strategies ```java @Service @CacheConfig(cacheNames = "users") public class UserService { @Autowired private UserRepository userRepository; // Cache the result @Cacheable(key = "#id") public Optional<User> findById(Long id) { return userRepository.findById(id); } // Cache with condition @Cacheable(key = "#email", condition = "#email.length() > 5") public Optional<User> findByEmail(String email) { return userRepository.findByEmail(email); } // Cache with custom key generator @Cacheable(keyGenerator = "customKeyGenerator") public List<User> findByDepartmentAndActive(String department, boolean active) { return userRepository.findByDepartmentAndActive(department, active); } // Update cache after modification @CachePut(key = "#result.id") public User updateUser(User user) { return userRepository.save(user); } // Evict cache entry @CacheEvict(key = "#id") public void deleteUser(Long id) { userRepository.deleteById(id); } // Evict all entries in cache @CacheEvict(allEntries = true) public void clearUserCache() { // Method implementation } // Multiple cache operations @Caching( cacheable = @Cacheable(value = "users", key = "#id"), evict = @CacheEvict(value = "userStats", allEntries = true) ) public User getUserWithStatsUpdate(Long id) { return userRepository.findById(id).orElse(null); } } @Configuration @EnableCaching public class CacheConfig { @Bean public CacheManager cacheManager() { CaffeineCacheManager cacheManager = new CaffeineCacheManager(); cacheManager.setCaffeine(Caffeine.newBuilder() .maximumSize(1000) .expireAfterWrite(10, TimeUnit.MINUTES) .recordStats()); return cacheManager; } @Bean("customKeyGenerator") public KeyGenerator customKeyGenerator() { return (target, method, params) -> { StringBuilder sb = new StringBuilder(); sb.append(target.getClass().getSimpleName()); sb.append("."); sb.append(method.getName()); for (Object param : params) { sb.append("_").append(param.toString()); } return sb.toString(); }; } } ``` ### Database Connection Optimization ```yaml # application.yml spring: datasource: url: jdbc:postgresql://localhost:5432/mydb username: ${DB_USERNAME} password: ${DB_PASSWORD} hikari: maximum-pool-size: 20 minimum-idle: 5 idle-timeout: 300000 max-lifetime: 1200000 connection-timeout: 20000 leak-detection-threshold: 60000 jpa: hibernate: ddl-auto: validate properties: hibernate: # Enable SQL logging in development show_sql: false format_sql: true use_sql_comments: true # Performance optimizations jdbc: batch_size: 25 order_inserts: true order_updates: true batch_versioned_data: true # Second-level cache cache: use_second_level_cache: true use_query_cache: true region: factory_class: org.hibernate.cache.jcache.JCacheRegionFactory # Statistics for monitoring generate_statistics: true # Connection handling connection: provider_disables_autocommit: true ``` ### Query Performance Monitoring ```java @Component public class QueryPerformanceInterceptor implements Interceptor { private static final Logger logger = LoggerFactory.getLogger(QueryPerformanceInterceptor.class); private static final long SLOW_QUERY_THRESHOLD = 1000; // 1 second @Override public boolean onLoad(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) { return false; } @Override public boolean onSave(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) { return false; } @Override public void onDelete(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) { // Implementation } @Override public boolean onFlushDirty(Object entity, Serializable id, Object[] currentState, Object[] previousState, String[] propertyNames, Type[] types) { return false; } @Override public Boolean isTransient(Object entity) { return null; } @Override public int[] findDirty(Object entity, Serializable id, Object[] currentState, Object[] previousState, String[] propertyNames, Type[] types) { return null; } @Override public Object instantiate(String entityName, EntityMode entityMode, Serializable id) { return null; } @Override public String getEntityName(Object object) { return null; } @Override public Object getEntity(String entityName, Serializable id) { return null; } @Override public void afterTransactionBegin(Transaction tx) { // Implementation } @Override public void afterTransactionCompletion(Transaction tx) { // Implementation } @Override public void beforeTransactionCompletion(Transaction tx) { // Implementation } @Override public String onPrepareStatement(String sql) { long startTime = System.currentTimeMillis(); return new String(sql) { @Override public String toString() { long endTime = System.currentTimeMillis(); long executionTime = endTime - startTime; if (executionTime > SLOW_QUERY_THRESHOLD) { logger.warn("Slow query detected ({}ms): {}", executionTime, sql); } return sql; } }; } } @Configuration public class HibernateConfig { @Bean public QueryPerformanceInterceptor queryPerformanceInterceptor() { return new QueryPerformanceInterceptor(); } @Bean public HibernatePropertiesCustomizer hibernatePropertiesCustomizer() { return (properties) -> { properties.put("hibernate.session_factory.interceptor", queryPerformanceInterceptor()); }; } } ``` Mastering Spring Data JPA requires understanding its advanced features and optimization techniques. By implementing proper querying strategies, transaction management, and performance optimizations, you can build efficient, scalable data access layers for your applications.