Database & JPA/Hibernate - Câu hỏi phỏng vấn Senior Engineer

1. N+1 Query Problem và Solutions

Câu hỏi:

"Hãy giải thích N+1 query problem và các cách để giải quyết nó trong JPA/Hibernate."

Câu trả lời:

N+1 Problem xảy ra khi: 1. Execute 1 query để lấy N entities 2. Sau đó execute N queries để lấy related entities

Ví dụ N+1 Problem:

// Entities
@Entity
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
    private List<Book> books = new ArrayList<>();

    // constructors, getters, setters
}

@Entity
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;

    // constructors, getters, setters
}

// BAD: Causes N+1 problem
public class BadService {

    @Autowired
    private AuthorRepository authorRepository;

    public List<AuthorDto> getAllAuthorsWithBooks() {
        List<Author> authors = authorRepository.findAll(); // 1 query

        return authors.stream()
                .map(author -> new AuthorDto(
                        author.getName(),
                        author.getBooks().size() // N queries (one for each author)
                ))
                .collect(Collectors.toList());
    }
}

Solutions cho N+1 Problem:

// Solution 1: JOIN FETCH
@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("SELECT DISTINCT a FROM Author a LEFT JOIN FETCH a.books")
    List<Author> findAllWithBooks();

    // For pagination with fetch join
    @Query(value = "SELECT DISTINCT a FROM Author a LEFT JOIN FETCH a.books",
           countQuery = "SELECT COUNT(DISTINCT a) FROM Author a")
    Page<Author> findAllWithBooks(Pageable pageable);
}

// Solution 2: Entity Graph
@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {

    @EntityGraph(attributePaths = {"books"})
    List<Author> findAll();

    @EntityGraph(attributePaths = {"books", "books.publisher"})
    Optional<Author> findById(Long id);

    // Named Entity Graph
    @NamedEntityGraph(
        name = "Author.withBooksAndPublisher",
        attributeNodes = {
            @NamedAttributeNode(value = "books", subgraph = "books-subgraph")
        },
        subgraphs = {
            @NamedSubgraph(
                name = "books-subgraph",
                attributeNodes = @NamedAttributeNode("publisher")
            )
        }
    )
    @EntityGraph("Author.withBooksAndPublisher")
    List<Author> findAllWithCompleteData();
}

// Solution 3: Batch Fetching
@Entity
public class Author {
    @Id
    private Long id;

    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
    @BatchSize(size = 25) // Load 25 authors' books at once
    private List<Book> books = new ArrayList<>();
}

// Solution 4: Projection với DTO
public interface AuthorBookCountProjection {
    String getName();
    Long getBookCount();
}

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("SELECT a.name as name, COUNT(b) as bookCount " +
           "FROM Author a LEFT JOIN a.books b " +
           "GROUP BY a.id, a.name")
    List<AuthorBookCountProjection> findAuthorsWithBookCount();

    // DTO Projection
    @Query("SELECT new com.company.dto.AuthorSummaryDto(a.name, COUNT(b)) " +
           "FROM Author a LEFT JOIN a.books b " +
           "GROUP BY a.id, a.name")
    List<AuthorSummaryDto> findAuthorSummaries();
}

// Solution 5: Two separate queries
@Service
@Transactional(readOnly = true)
public class OptimizedAuthorService {

    @Autowired
    private AuthorRepository authorRepository;

    @Autowired
    private BookRepository bookRepository;

    public List<AuthorWithBooksDto> getAllAuthorsWithBooks() {
        // 1. Get all authors
        List<Author> authors = authorRepository.findAll();
        List<Long> authorIds = authors.stream()
                .map(Author::getId)
                .collect(Collectors.toList());

        // 2. Get all books for these authors in one query
        List<Book> books = bookRepository.findByAuthorIdIn(authorIds);

        // 3. Group books by author
        Map<Long, List<Book>> booksByAuthor = books.stream()
                .collect(Collectors.groupingBy(book -> book.getAuthor().getId()));

        // 4. Create DTOs
        return authors.stream()
                .map(author -> new AuthorWithBooksDto(
                        author.getName(),
                        booksByAuthor.getOrDefault(author.getId(), Collections.emptyList())
                ))
                .collect(Collectors.toList());
    }
}

2. Custom Repository Implementation với Specifications

Câu hỏi:

"Implement một custom repository sử dụng Criteria API và Specifications để tạo dynamic queries."

Câu trả lời:

Ví dụ Dynamic Query Implementation:

// 1. Specification Classes
public class BookSpecifications {

    public static Specification<Book> hasTitle(String title) {
        return (root, query, criteriaBuilder) -> {
            if (title == null || title.trim().isEmpty()) {
                return criteriaBuilder.conjunction();
            }
            return criteriaBuilder.like(
                    criteriaBuilder.lower(root.get("title")), 
                    "%" + title.toLowerCase() + "%"
            );
        };
    }

    public static Specification<Book> hasAuthor(String authorName) {
        return (root, query, criteriaBuilder) -> {
            if (authorName == null || authorName.trim().isEmpty()) {
                return criteriaBuilder.conjunction();
            }
            Join<Book, Author> authorJoin = root.join("author");
            return criteriaBuilder.like(
                    criteriaBuilder.lower(authorJoin.get("name")), 
                    "%" + authorName.toLowerCase() + "%"
            );
        };
    }

    public static Specification<Book> hasCategory(String category) {
        return (root, query, criteriaBuilder) -> {
            if (category == null || category.trim().isEmpty()) {
                return criteriaBuilder.conjunction();
            }
            return criteriaBuilder.equal(root.get("category"), category);
        };
    }

    public static Specification<Book> publishedBetween(LocalDate startDate, LocalDate endDate) {
        return (root, query, criteriaBuilder) -> {
            if (startDate == null && endDate == null) {
                return criteriaBuilder.conjunction();
            }

            if (startDate != null && endDate != null) {
                return criteriaBuilder.between(root.get("publishedDate"), startDate, endDate);
            } else if (startDate != null) {
                return criteriaBuilder.greaterThanOrEqualTo(root.get("publishedDate"), startDate);
            } else {
                return criteriaBuilder.lessThanOrEqualTo(root.get("publishedDate"), endDate);
            }
        };
    }

    public static Specification<Book> hasMinRating(Double minRating) {
        return (root, query, criteriaBuilder) -> {
            if (minRating == null) {
                return criteriaBuilder.conjunction();
            }
            return criteriaBuilder.greaterThanOrEqualTo(root.get("averageRating"), minRating);
        };
    }

    // Complex specification with subquery
    public static Specification<Book> hasReviewsCount(Long minReviews) {
        return (root, query, criteriaBuilder) -> {
            if (minReviews == null) {
                return criteriaBuilder.conjunction();
            }

            Subquery<Long> subquery = query.subquery(Long.class);
            Root<Review> reviewRoot = subquery.from(Review.class);
            subquery.select(criteriaBuilder.count(reviewRoot))
                    .where(criteriaBuilder.equal(reviewRoot.get("book"), root));

            return criteriaBuilder.greaterThanOrEqualTo(subquery, minReviews);
        };
    }
}

// 2. Custom Repository Interface
public interface BookRepositoryCustom {
    Page<BookProjection> findBooksWithDynamicFilters(BookSearchCriteria criteria, Pageable pageable);
    List<BookStatistics> getBookStatisticsByCategory();
    List<Book> findTopRatedBooks(int limit);
}

// 3. Custom Repository Implementation
@Repository
public class BookRepositoryImpl implements BookRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<BookProjection> findBooksWithDynamicFilters(BookSearchCriteria criteria, 
                                                            Pageable pageable) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();

        // Count query
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<Book> countRoot = countQuery.from(Book.class);
        countQuery.select(cb.count(countRoot));
        countQuery.where(buildPredicates(criteria, countRoot, cb));

        Long total = entityManager.createQuery(countQuery).getSingleResult();

        // Data query
        CriteriaQuery<BookProjection> dataQuery = cb.createQuery(BookProjection.class);
        Root<Book> dataRoot = dataQuery.from(Book.class);
        Join<Book, Author> authorJoin = dataRoot.join("author", JoinType.LEFT);

        dataQuery.select(cb.construct(BookProjection.class,
                dataRoot.get("id"),
                dataRoot.get("title"),
                authorJoin.get("name"),
                dataRoot.get("category"),
                dataRoot.get("publishedDate"),
                dataRoot.get("averageRating")
        ));

        dataQuery.where(buildPredicates(criteria, dataRoot, cb));

        // Apply sorting
        if (pageable.getSort().isSorted()) {
            List<Order> orders = pageable.getSort().stream()
                    .map(sort -> sort.isAscending() 
                            ? cb.asc(dataRoot.get(sort.getProperty()))
                            : cb.desc(dataRoot.get(sort.getProperty())))
                    .collect(Collectors.toList());
            dataQuery.orderBy(orders);
        }

        TypedQuery<BookProjection> typedQuery = entityManager.createQuery(dataQuery);
        typedQuery.setFirstResult((int) pageable.getOffset());
        typedQuery.setMaxResults(pageable.getPageSize());

        List<BookProjection> content = typedQuery.getResultList();

        return new PageImpl<>(content, pageable, total);
    }

    private Predicate[] buildPredicates(BookSearchCriteria criteria, Root<Book> root, 
                                       CriteriaBuilder cb) {
        List<Predicate> predicates = new ArrayList<>();

        if (criteria.getTitle() != null && !criteria.getTitle().trim().isEmpty()) {
            predicates.add(cb.like(cb.lower(root.get("title")), 
                    "%" + criteria.getTitle().toLowerCase() + "%"));
        }

        if (criteria.getAuthorName() != null && !criteria.getAuthorName().trim().isEmpty()) {
            Join<Book, Author> authorJoin = root.join("author");
            predicates.add(cb.like(cb.lower(authorJoin.get("name")), 
                    "%" + criteria.getAuthorName().toLowerCase() + "%"));
        }

        if (criteria.getCategory() != null) {
            predicates.add(cb.equal(root.get("category"), criteria.getCategory()));
        }

        if (criteria.getMinRating() != null) {
            predicates.add(cb.greaterThanOrEqualTo(root.get("averageRating"), 
                    criteria.getMinRating()));
        }

        if (criteria.getPublishedAfter() != null) {
            predicates.add(cb.greaterThanOrEqualTo(root.get("publishedDate"), 
                    criteria.getPublishedAfter()));
        }

        if (criteria.getPublishedBefore() != null) {
            predicates.add(cb.lessThanOrEqualTo(root.get("publishedDate"), 
                    criteria.getPublishedBefore()));
        }

        return predicates.toArray(new Predicate[0]);
    }

    @Override
    public List<BookStatistics> getBookStatisticsByCategory() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<BookStatistics> query = cb.createQuery(BookStatistics.class);
        Root<Book> root = query.from(Book.class);

        query.select(cb.construct(BookStatistics.class,
                root.get("category"),
                cb.count(root),
                cb.avg(root.get("averageRating")),
                cb.max(root.get("averageRating")),
                cb.min(root.get("averageRating"))
        ));

        query.groupBy(root.get("category"));
        query.orderBy(cb.desc(cb.count(root)));

        return entityManager.createQuery(query).getResultList();
    }

    @Override
    public List<Book> findTopRatedBooks(int limit) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Book> query = cb.createQuery(Book.class);
        Root<Book> root = query.from(Book.class);

        // Join với reviews để lấy books có ít nhất 1 review
        Join<Book, Review> reviewJoin = root.join("reviews", JoinType.INNER);

        query.select(root);
        query.where(cb.isNotNull(root.get("averageRating")));
        query.groupBy(root.get("id"));
        query.having(cb.greaterThan(cb.count(reviewJoin), 10L)); // At least 10 reviews
        query.orderBy(cb.desc(root.get("averageRating")));

        return entityManager.createQuery(query)
                .setMaxResults(limit)
                .getResultList();
    }
}

// 4. Main Repository Interface
public interface BookRepository extends JpaRepository<Book, Long>, 
                                      JpaSpecificationExecutor<Book>, 
                                      BookRepositoryCustom {

    // Simple queries
    List<Book> findByTitleContainingIgnoreCase(String title);

    @Query("SELECT b FROM Book b WHERE b.author.name = :authorName")
    List<Book> findByAuthorName(@Param("authorName") String authorName);

    // Using Specifications in standard repository
    default Page<Book> findBooksWithCriteria(BookSearchCriteria criteria, Pageable pageable) {
        Specification<Book> spec = Specification.where(null);

        if (criteria.getTitle() != null) {
            spec = spec.and(BookSpecifications.hasTitle(criteria.getTitle()));
        }

        if (criteria.getAuthorName() != null) {
            spec = spec.and(BookSpecifications.hasAuthor(criteria.getAuthorName()));
        }

        if (criteria.getCategory() != null) {
            spec = spec.and(BookSpecifications.hasCategory(criteria.getCategory()));
        }

        if (criteria.getMinRating() != null) {
            spec = spec.and(BookSpecifications.hasMinRating(criteria.getMinRating()));
        }

        if (criteria.getPublishedAfter() != null || criteria.getPublishedBefore() != null) {
            spec = spec.and(BookSpecifications.publishedBetween(
                    criteria.getPublishedAfter(), criteria.getPublishedBefore()));
        }

        return findAll(spec, pageable);
    }
}

// 5. Service Usage
@Service
@Transactional(readOnly = true)
public class BookSearchService {

    private final BookRepository bookRepository;

    public BookSearchService(BookRepository bookRepository) {
        this.bookRepository = bookRepository;
    }

    public Page<Book> searchBooks(BookSearchCriteria criteria, Pageable pageable) {
        return bookRepository.findBooksWithCriteria(criteria, pageable);
    }

    public Page<BookProjection> searchBooksProjection(BookSearchCriteria criteria, 
                                                     Pageable pageable) {
        return bookRepository.findBooksWithDynamicFilters(criteria, pageable);
    }

    public List<BookStatistics> getStatistics() {
        return bookRepository.getBookStatisticsByCategory();
    }
}

3. Transaction Management và Isolation Levels

Câu hỏi:

"Hãy giải thích các isolation levels trong database transactions và khi nào sử dụng @Transactional với different propagation behaviors."

Câu trả lời:

Ví dụ Transaction Management:

// 1. Transaction Configuration
@Configuration
@EnableTransactionManagement
public class TransactionConfig {

    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
        transactionManager.setDataSource(dataSource);
        return transactionManager;
    }

    // Custom transaction manager for specific use cases
    @Bean("customTransactionManager")
    public PlatformTransactionManager customTransactionManager(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

// 2. Service với different propagation behaviors
@Service
public class OrderService {

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private PaymentService paymentService;

    @Autowired
    private AuditService auditService;

    @Autowired
    private NotificationService notificationService;

    // REQUIRED: Default behavior, join existing transaction or create new
    @Transactional
    public Order createOrder(CreateOrderRequest request) {
        Order order = new Order();
        order.setUserId(request.getUserId());
        order.setAmount(request.getAmount());
        order.setStatus(OrderStatus.PENDING);

        order = orderRepository.save(order);

        // This joins the same transaction
        processPayment(order);

        return order;
    }

    // REQUIRES_NEW: Always create new transaction, suspend current if exists
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void processPayment(Order order) {
        try {
            Payment payment = paymentService.charge(order.getAmount());
            order.setPaymentId(payment.getId());
            order.setStatus(OrderStatus.PAID);
            orderRepository.save(order);

        } catch (PaymentException ex) {
            order.setStatus(OrderStatus.FAILED);
            orderRepository.save(order);
            throw ex;
        }
    }

    // NEVER: Throw exception if transaction exists
    @Transactional(propagation = Propagation.NEVER)
    public void validateOrderData(CreateOrderRequest request) {
        // Pure validation logic without transaction
        if (request.getAmount().compareTo(BigDecimal.ZERO) <= 0) {
            throw new IllegalArgumentException("Amount must be positive");
        }
    }

    // NOT_SUPPORTED: Suspend current transaction if exists
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public void sendConfirmationEmail(Order order) {
        // External service call, no need for transaction
        notificationService.sendOrderConfirmation(order);
    }

    // NESTED: Create nested transaction (savepoint)
    @Transactional(propagation = Propagation.NESTED)
    public void updateOrderMetrics(Order order) {
        try {
            // Update metrics in nested transaction
            orderRepository.updateOrderMetrics(order.getId());
        } catch (Exception ex) {
            // Nested transaction can rollback without affecting parent
            log.warn("Failed to update metrics for order: {}", order.getId());
        }
    }

    // Isolation levels example
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public List<Order> getOrdersWithReadCommitted(Long userId) {
        // Prevents dirty reads, allows non-repeatable reads
        return orderRepository.findByUserId(userId);
    }

    @Transactional(isolation = Isolation.REPEATABLE_READ)
    public OrderSummary generateOrderSummary(Long userId) {
        // Prevents dirty reads and non-repeatable reads
        List<Order> orders = orderRepository.findByUserId(userId);

        // Same query will return same results within this transaction
        BigDecimal totalAmount = orders.stream()
                .map(Order::getAmount)
                .reduce(BigDecimal.ZERO, BigDecimal::add);

        return new OrderSummary(orders.size(), totalAmount);
    }

    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void processHighValueOrder(CreateOrderRequest request) {
        // Highest isolation level, prevents all phenomena
        // Use for critical operations requiring absolute consistency
        if (request.getAmount().compareTo(new BigDecimal("10000")) > 0) {
            // Serialize access to high-value order processing
            Order order = createOrder(request);
            auditService.logHighValueTransaction(order);
        }
    }

    // Read-only transaction optimization
    @Transactional(readOnly = true)
    public Page<Order> searchOrders(OrderSearchCriteria criteria, Pageable pageable) {
        // Optimizes for read operations, no write locks
        return orderRepository.findByCriteria(criteria, pageable);
    }

    // Custom timeout
    @Transactional(timeout = 30) // 30 seconds
    public void processLargeOrderBatch(List<CreateOrderRequest> requests) {
        for (CreateOrderRequest request : requests) {
            createOrder(request);
        }
    }

    // Rollback for specific exceptions
    @Transactional(rollbackFor = {BusinessException.class, PaymentException.class},
                   noRollbackFor = {ValidationException.class})
    public Order createOrderWithCustomRollback(CreateOrderRequest request) {
        validateOrderData(request); // ValidationException won't rollback

        Order order = createOrder(request);

        if (someBusinessRule(order)) {
            throw new BusinessException("Business rule violation"); // Will rollback
        }

        return order;
    }
}

// 3. Audit Service với separate transaction
@Service
public class AuditService {

    @Autowired
    private AuditLogRepository auditLogRepository;

    // REQUIRES_NEW ensures audit log is saved even if main transaction fails
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logTransaction(String entityType, Long entityId, String action) {
        AuditLog log = new AuditLog();
        log.setEntityType(entityType);
        log.setEntityId(entityId);
        log.setAction(action);
        log.setTimestamp(LocalDateTime.now());
        log.setUserId(getCurrentUserId());

        auditLogRepository.save(log);
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logHighValueTransaction(Order order) {
        HighValueAudit audit = new HighValueAudit();
        audit.setOrderId(order.getId());
        audit.setAmount(order.getAmount());
        audit.setTimestamp(LocalDateTime.now());

        auditLogRepository.saveHighValueAudit(audit);
    }

    private Long getCurrentUserId() {
        // Get current user from security context
        return 1L;
    }
}

// 4. Programmatic Transaction Management
@Service
public class ManualTransactionService {

    @Autowired
    private PlatformTransactionManager transactionManager;

    @Autowired
    private OrderRepository orderRepository;

    public void processOrdersWithManualTransaction(List<CreateOrderRequest> requests) {
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        def.setTimeout(60);

        TransactionStatus status = transactionManager.getTransaction(def);

        try {
            for (CreateOrderRequest request : requests) {
                Order order = new Order();
                order.setUserId(request.getUserId());
                order.setAmount(request.getAmount());
                orderRepository.save(order);
            }

            transactionManager.commit(status);

        } catch (Exception ex) {
            transactionManager.rollback(status);
            throw ex;
        }
    }

    // TransactionTemplate approach
    @Autowired
    private TransactionTemplate transactionTemplate;

    public Order createOrderWithTemplate(CreateOrderRequest request) {
        return transactionTemplate.execute(status -> {
            Order order = new Order();
            order.setUserId(request.getUserId());
            order.setAmount(request.getAmount());
            return orderRepository.save(order);
        });
    }
}

Isolation Levels: - READ_UNCOMMITTED: Fastest, allows dirty reads - READ_COMMITTED: Prevents dirty reads (default cho nhiều DB) - REPEATABLE_READ: Prevents dirty và non-repeatable reads
- SERIALIZABLE: Slowest, prevents all concurrency issues

Propagation Behaviors: - REQUIRED: Join existing hoặc create new (default) - REQUIRES_NEW: Always create new transaction - SUPPORTS: Use existing, no transaction if none - NOT_SUPPORTED: No transaction, suspend if exists - NEVER: No transaction, exception if exists - MANDATORY: Must have existing transaction - NESTED: Nested transaction với savepoints