Spring Data JPA

@Entity
@Table(name = "posts")
@EntityListeners(AuditingEntityListener.class)
public class Post {

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

    @Column(nullable = false, length = 200)
    private String title;

    @Column(columnDefinition = "TEXT")
    private String content;

    @Enumerated(EnumType.STRING)
    private PostStatus status; // DRAFT, PUBLISHED, ARCHIVED

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

    @OneToMany(mappedBy = "post", cascade = CascadeType.ALL, orphanRemoval = true)
    @BatchSize(size = 25)
    private List<Comment> comments = new ArrayList<>();

    @ManyToMany
    @JoinTable(
        name = "post_tags",
        joinColumns = @JoinColumn(name = "post_id"),
        inverseJoinColumns = @JoinColumn(name = "tag_id")
    )
    private Set<Tag> tags = new HashSet<>();

    @Version
    private Integer version;

    @CreatedDate
    private LocalDateTime createdAt;

    @LastModifiedDate
    private LocalDateTime updatedAt;

    @CreatedBy
    private String createdBy;

    @LastModifiedBy
    private String modifiedBy;
}

public interface PostRepository extends JpaRepository<Post, Long> {

    // Derived queries
    List<Post> findByStatus(PostStatus status);
    List<Post> findByAuthorIdAndStatus(Long authorId, PostStatus status);
    Optional<Post> findByTitle(String title);
    List<Post> findByCreatedAtAfter(LocalDateTime date);
    List<Post> findByTitleContainingIgnoreCase(String keyword);
    long countByStatus(PostStatus status);
    boolean existsByTitleAndAuthorId(String title, Long authorId);
    List<Post> findTop5ByStatusOrderByCreatedAtDesc(PostStatus status);
    void deleteByStatusAndCreatedAtBefore(PostStatus status, LocalDateTime date);
}

public interface PostRepository extends JpaRepository<Post, Long>, JpaSpecificationExecutor<Post> {

    // Eagerly fetch 'author' to prevent N+1 when mapping posts to DTOs
    @Override
    @EntityGraph(attributePaths = {"author"})
    Page<Post> findAll(Specification<Post> spec, Pageable pageable);
}

public class PostSpecifications {

    public static Specification<Post> hasStatus(PostStatus status) {
        return (root, query, cb) -> 
            status == null ? null : cb.equal(root.get("status"), status);
    }

    public static Specification<Post> titleContains(String keyword) {
        return (root, query, cb) -> {
            if (keyword == null) return null;
            // Escape LIKE wildcards to prevent SQL injection via pattern characters
            String escaped = keyword.replace("%", "\\%").replace("_", "\\_");
            return cb.like(cb.lower(root.get("title")), "%" + escaped.toLowerCase() + "%");
        };
    }

    public static Specification<Post> createdAfter(LocalDateTime date) {
        return (root, query, cb) -> 
            date == null ? null : cb.greaterThan(root.get("createdAt"), date);
    }

    public static Specification<Post> hasTag(String tagName) {
        return (root, query, cb) -> {
            if (tagName == null) return null;
            Join<Post, Tag> tags = root.join("tags");
            return cb.equal(tags.get("name"), tagName);
        };
    }
}

// Service layer — combine dynamically based on user input
Specification<Post> spec = Specification
    .where(hasStatus(filter.getStatus()))
    .and(titleContains(filter.getKeyword()))
    .and(createdAfter(filter.getFromDate()))
    .and(hasTag(filter.getTag()));

Page<Post> results = postRepository.findAll(spec, pageable);

// Repository
Page<Post> findByStatus(PostStatus status, Pageable pageable);
Slice<Post> findByAuthorId(Long authorId, Pageable pageable);

// Service
Pageable pageable = PageRequest.of(0, 20, Sort.by(Sort.Direction.DESC, "createdAt"));
Page<Post> page = postRepository.findByStatus(PostStatus.PUBLISHED, pageable);

page.getContent();       // List<Post> for current page
page.getTotalElements(); // Total rows matching query
page.getTotalPages();    // ceil(totalElements / pageSize)
page.getNumber();        // Current page (0-indexed)
page.hasNext();          // More pages available?
page.isLast();           // Is this the last page?

// Controller
@GetMapping("/posts")
public Page<PostSummary> getPosts(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size,
        @RequestParam(defaultValue = "createdAt") String sortBy,
        @RequestParam(defaultValue = "desc") String direction) {

    Sort sort = direction.equalsIgnoreCase("asc")
        ? Sort.by(sortBy).ascending()
        : Sort.by(sortBy).descending();

    return postService.getPublishedPosts(PageRequest.of(page, size, sort));
}

// 1. Define custom interface
public interface PostRepositoryCustom {
    List<Post> fullTextSearch(String query);
    void bulkInsert(List<Post> posts);
}

// 2. Implement it (naming convention: {RepositoryName}Impl)
@Repository
public class PostRepositoryImpl implements PostRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Post> fullTextSearch(String query) {
        return em.createNativeQuery(
            "SELECT * FROM posts WHERE to_tsvector('english', title || ' ' || content) @@ plainto_tsquery(:q)",
            Post.class)
            .setParameter("q", query)
            .getResultList();
    }

    @Override
    public void bulkInsert(List<Post> posts) {
        int batchSize = 50;
        for (int i = 0; i < posts.size(); i++) {
            em.persist(posts.get(i));
            if (i % batchSize == 0 && i > 0) {
                em.flush();
                em.clear();
            }
        }
    }
}

// 3. Extend in main repository
public interface PostRepository extends JpaRepository<Post, Long>, PostRepositoryCustom {
    // all derived + custom methods available
}

@Entity
public class Post {
    @Version
    private Integer version; // auto-incremented on each UPDATE
}

@Service
public class PostService {

    @Retryable(value = OptimisticLockException.class, maxAttempts = 3)
    @Transactional
    public Post updateTitle(Long id, String newTitle) {
        Post post = postRepository.findById(id).orElseThrow();
        post.setTitle(newTitle);
        return postRepository.save(post);
    }
}

public interface PostRepository extends JpaRepository<Post, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT p FROM Post p WHERE p.id = :id")
    Optional<Post> findByIdForUpdate(@Param("id") Long id);

    @Lock(LockModeType.PESSIMISTIC_READ)
    Optional<Post> findWithSharedLockById(Long id);
}

@Configuration
@EnableJpaAuditing
public class JpaConfig {

    @Bean
    public AuditorAware<String> auditorProvider() {
        return () -> Optional.ofNullable(
            SecurityContextHolder.getContext().getAuthentication())
            .map(Authentication::getName);
    }
}

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class Auditable {

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

    @LastModifiedDate
    private LocalDateTime updatedAt;

    @CreatedBy
    @Column(updatable = false)
    private String createdBy;

    @LastModifiedBy
    private String modifiedBy;
}

@Entity
public class Post extends Auditable {
    // inherits all audit fields
}

@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class PostService {

    private final PostRepository postRepository;

    // N+1 FIX: Without @EntityGraph, calling post.getAuthor().getName() triggers
    // a separate SELECT for each post's author (N+1 problem). The @EntityGraph on
    // the repository method eagerly fetches the author in the same query.
    public Page<PostSummaryView> searchPosts(PostSearchFilter filter, Pageable pageable) {
        Specification<Post> spec = Specification
            .where(PostSpecifications.hasStatus(PostStatus.PUBLISHED))
            .and(PostSpecifications.titleContains(filter.getKeyword()))
            .and(PostSpecifications.createdAfter(filter.getFromDate()))
            .and(PostSpecifications.hasTag(filter.getTag()));

        return postRepository.findAll(spec, pageable) // uses @EntityGraph below
            .map(post -> new PostSummaryView(post.getId(), post.getTitle(), 
                post.getCreatedAt(), post.getAuthor().getName()));
    }

    public PostDetailDto getPostDetail(Long id) {
        Post post = postRepository.findDetailById(id) // @EntityGraph loads author + comments + tags
            .orElseThrow(() -> new PostNotFoundException(id));
        return PostDetailDto.from(post);
    }

    @Transactional
    public Post createPost(CreatePostRequest request) {
        Author author = authorRepository.getReferenceById(request.getAuthorId()); // no SELECT, just proxy
        Post post = new Post();
        post.setTitle(request.getTitle());
        post.setContent(request.getContent());
        post.setStatus(PostStatus.DRAFT);
        post.setAuthor(author);
        request.getTagIds().stream()
            .map(tagRepository::getReferenceById)
            .forEach(post.getTags()::add);
        return postRepository.save(post); // persist — entity is new
    }

    @Transactional
    @Retryable(value = OptimisticLockException.class, maxAttempts = 3)
    public Post publishPost(Long id) {
        Post post = postRepository.findById(id).orElseThrow();
        post.setStatus(PostStatus.PUBLISHED); // dirty checking handles UPDATE
        return post; // no explicit save needed — entity is managed
    }
}