Spring Data JPA repositories eliminate boilerplate for CRUD while offering multiple query styles: method-name derivation, JPQL/HQL via @Query, native SQL, Example-based matching, and the Criteria-based Specification API. These cover many scenarios but often become unwieldy for complex, paginated, and join-heavy queries. A practical approach is to keep the convenience of JpaRepository while introducing a custom base repository and Querydsl for expressive, type-safe predicates, sorted pagination, and multi-table joins.

Extended repository usage

• Derived queries by method name, JPQL, and native SQL coexist cleanly on a Spring Data repository interface.

@Repository
public interface SendLogRepository extends JpaRepository<SendLog, Integer> {

    // Method-name derived query
    List<SendLog> findByTemplateNameContaining(String keyword);

    // JPQL/ HQL
    @Query("select s from SendLog s where s.templateName = :tn")
    List<SendLog> findByTemplate(@Param("tn") String name);

    // Native SQL
    @Query(value = "select s.* from sms_sendlog s where s.template_name = :tn", nativeQuery = true)
    List<SendLog> findByTemplateNative(@Param("tn") String name);
}

Advantages

• Inherits CRUD immediately from JpaRepository
• Supports query derivation from method names with IDE assistance
• Inline JPQL and native SQL with @Query

Limitation

• Complex, paginated queries often require verbose, error-prone condition building sctatered in service layers

Example-based matching

public void exampleSearch(SendLogRepository repo) {
    SendLog probe = new SendLog();
    probe.setTemplateName("kl");

    // Note: property paths refer to entity fields, not database columns
    ExampleMatcher matcher = ExampleMatcher.matching()
        .withMatcher("templateName", ExampleMatcher.GenericPropertyMatchers.contains())
        .withIgnoreNullValues();

    Example<SendLog> example = Example.of(probe, matcher);
    Pageable page = PageRequest.of(0, 10);

    Page<SendLog> result = repo.findAll(example, page);
}

Effective for simple string fields, but quickly becomes insufficient for ranges, dates, or joins.

Criteria/Specification API

public void criteriaSearch(SendLogRepository repo) {
    String tn = "kk";

    Specification<SendLog> spec = (root, query, cb) -> cb.like(root.get("templateName"), tn);

    Pageable page = PageRequest.of(0, 2);
    Page<SendLog> result = repo.findAll(spec, page);
}

The Criteria API supports rich predicates but tends to push complex predicate assembly into services and relies on fragile string-based property paths.

Custom base repository on top of SimpleJpaRepository

A custom base repository provides a central place to host complex queries while retaining Spring Data behavior via SimpleJpaRepository.

public abstract class BaseRepository<T, ID> extends SimpleJpaRepository<T, ID> {

    protected final EntityManager em;

    protected BaseRepository(Class<T> domainClass, EntityManager em) {
        super(domainClass, em);
        this.em = em;
    }
}

Concrete repository with EntityManager, JPQL/native, and Specification

@Repository
public class SendLogQueries extends BaseRepository<SendLog, Integer> {

    public SendLogQueries(EntityManager em) {
        super(SendLog.class, em);
    }

    // Native SQL
    public SendLog findOneByTemplateNative(String template) {
        String sql = "select * from send_log where template_name = :tn";
        return (SendLog) em.createNativeQuery(sql, SendLog.class)
            .setParameter("tn", template)
            .getSingleResult();
    }

    // JPQL
    public SendLog findOneByTemplate(String template) {
        String jpql = "from SendLog s where s.templateName = :tn";
        return em.createQuery(jpql, SendLog.class)
            .setParameter("tn", template)
            .getSingleResult();
    }

    // Specification-based paged filtering
    public Page<SendLog> findAll(SendLogFilter filter, Pageable pageable) {
        Specification<SendLog> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();

            if (filter.getTemplateName() != null && !filter.getTemplateName().isEmpty()) {
                predicates.add(cb.like(root.get("templateName"), "%" + filter.getTemplateName() + "%"));
            }
            if (filter.getFrom() != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("createdAt"), filter.getFrom()));
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        };
        return super.findAll(spec, pageable);
    }
}

Integrating Querydsl for fluent, type-safe predicates

Querydsl adds a static, type-safe DSL for predicates and joins. Spring Data provides QuerydslJpaPredicateExecutor, which can be wired into the base repository.

public abstract class BaseRepository<T, ID> extends SimpleJpaRepository<T, ID> {

    protected final EntityManager em;
    protected final QuerydslJpaPredicateExecutor<T> predicateExecutor;

    protected BaseRepository(Class<T> domainClass, EntityManager em) {
        super(domainClass, em);
        this.em = em;
        JpaEntityInformation<T, ?> info = JpaEntityInformationSupport.getEntityInformation(domainClass, em);
        this.predicateExecutor = new QuerydslJpaPredicateExecutor<>(
            info, em, SimpleEntityPathResolver.INSTANCE, getRepositoryMethodMetadata());
    }
}

Using Querydsl in a concrete repository for complex pagination

public class SendLogQueries extends BaseRepository<SendLog, Integer> {

    private static final QSendLog s = QSendLog.sendLog;

    public SendLogQueries(EntityManager em) {
        super(SendLog.class, em);
    }

    public Page<SendLog> findAll(SendLogFilter filter, Pageable pageable) {
        BooleanExpression where = s.isNotNull();
        if (filter.getFrom() != null) {
            where = where.and(s.createdAt.goe(filter.getFrom()));
        }
        if (filter.getTemplateName() != null && !filter.getTemplateName().isEmpty()) {
            where = where.and(s.templateName.contains(filter.getTemplateName()));
        }
        return predicateExecutor.findAll(where, pageable);
    }
}

Notes

• Querydsl simplifies composing optional filters and joins
• For specialized ordering and multi-join queries, extending beyond QuerydslJpaPredicateExecutor provides more control

Final base repository with Querydsl JPAQueryFactory

Introduce JPAQueryFactory and Querydsl to support sorted pagination and joins within the base.

public abstract class BaseRepository<T, ID> extends SimpleJpaRepository<T, ID> {

    protected final EntityManager em;
    protected final QuerydslJpaPredicateExecutor<T> predicateExecutor;
    protected final JPAQueryFactory queryFactory;
    protected final Querydsl querydsl;
    private final EntityPath<T> rootPath;

    protected BaseRepository(Class<T> domainClass, EntityManager em) {
        super(domainClass, em);
        this.em = em;
        JpaEntityInformation<T, ?> info = JpaEntityInformationSupport.getEntityInformation(domainClass, em);
        this.predicateExecutor = new QuerydslJpaPredicateExecutor<>(
            info, em, SimpleEntityPathResolver.INSTANCE, getRepositoryMethodMetadata());
        this.queryFactory = new JPAQueryFactory(em);
        this.rootPath = SimpleEntityPathResolver.INSTANCE.createPath(domainClass);
        this.querydsl = new Querydsl(em, new PathBuilder<>(rootPath.getType(), rootPath.getMetadata()));
    }

    protected Page<T> findAll(Predicate predicate, Pageable pageable, OrderSpecifier<?>... orders) {
        // Content query with pagination and ordering
        JPQLQuery<T> contentQuery = queryFactory.selectFrom(rootPath).where(predicate);
        querydsl.applySorting(pageable.getSort(), contentQuery);
        contentQuery.orderBy(orders);
        querydsl.applyPagination(pageable, contentQuery);
        List<T> content = contentQuery.fetch();

        // Count query
        JPQLQuery<Long> countQuery = queryFactory.select(Wildcard.count).from(rootPath).where(predicate);
        return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
    }
}

Multi-table Qureydsl join with paged results

public class SendLogQueries extends BaseRepository<SendLog, Integer> {

    private static final QSendLog sl = QSendLog.sendLog;
    private static final QTemplate tpl = QTemplate.template;

    public SendLogQueries(EntityManager em) {
        super(SendLog.class, em);
    }

    public Page<SendLog> findAll(SendLogFilter filter, Template t, Pageable pageable) {
        BooleanExpression where = sl.templateCode.eq(tpl.code);
        if (t != null && t.getName() != null && !t.getName().isEmpty()) {
            where = where.and(tpl.name.eq(t.getName()));
        }
        if (filter.getFrom() != null) {
            where = where.and(sl.createdAt.goe(filter.getFrom()));
        }

        // Base select with join
        JPQLQuery<SendLog> base = queryFactory
            .selectFrom(sl)
            .leftJoin(tpl).on(sl.templateCode.eq(tpl.code))
            .where(where);

        querydsl.applyPagination(pageable, base);
        List<SendLog> rows = base.fetch();

        JPQLQuery<Long> count = queryFactory
            .select(Wildcard.count)
            .from(sl)
            .leftJoin(tpl).on(sl.templateCode.eq(tpl.code))
            .where(where);

        return PageableExecutionUtils.getPage(rows, pageable, count::fetchOne);
    }
}

Printing executed SQL with P6Spy

Capturing final SQL with bound values is invaluable for verifying Querydsl/Criteria queries.

1. Maven dependency

<dependency>
  <groupId>p6spy</groupId>
  <artifactId>p6spy</artifactId>
  <version>${p6spy.version}</version>
</dependency>

2. Datasource URL prefix

• Change: jdbc:mysql://host:3306/db
• To: jdbc:p6spy:mysql://host:3306/db

3. spy.properties (on classpath)

appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=executionTime:%(executionTime)| sql:%(sqlSingleLine)

P6Spy logs final SQL with parameters inlined and execution time, enabling direct copy/paste into SQL clients for troubleshooting.