Preface
The goal of the Spring Data Jpa framework is to significantly reduce the amount of boilerplate code required to implement the data access layers of various persistent stores. The central interface in the Spring Data Jpa Repository abstraction is Repository. It needs domain entity class and domain entity ID type as type parameters for management. This interface is primarily used as a tag interface to capture the types to be used and to help you discover the interfaces that extend that interface. CrudRepository and JpaRepository are more specific data operation abstractions. Generally, when we use them in projects, we can define our domain interface and then inherit CrudRepository or JpaRepository to implement the basic CURD method. However, this usage has limitations, and it can't handle super complex queries, and the slightly complex query code is not very elegant Let's see how to solve this problem in the most elegant way.
Extended interface usage
/** * @author: kl @kailing.pub * @date: 2019/11/11 */ @Repository public interface SendLogRepository extends JpaRepository<SendLog,Integer> { /** * Derived query by resolving method name * @param templateName * @return */ List<SendLog> findSendLogByTemplateName(String templateName); /** * HQL * @param templateName * @return */ @Query(value ="select SendLog from SendLog s where s.templateName = :templateName") List<SendLog> findByTempLateName(String templateName); /** * Native sql * @param templateName * @return */ @Query(value ="select s.* from sms_sendlog s where s.templateName = :templateName",nativeQuery = true) List<SendLog> findByTempLateNameNative(String templateName); }
Advantage:
- 1. This is the most common way to extend the interface. After inheriting the JpaRepository interface, it immediately has the basic CURD function
- 2. You can also use specific method names for parsing queries, which is the most special feature of spring Data Jpa. Moreover, the mainstream IDE has a good automation support for this way of use, and will give a prompt when entering the method name to be resolved.
- 3. It is very convenient to support HQL and native SQL in the form of annotation
Defects:
- 1. Complex paging query is not well supported
One defect is that there are two ways to realize complex paging query in this way of extending interface, and the codes of these two ways are not elegant, and a lot of conditional splicing logic will be written in the service layer of calling query.
The first method is example query
public void testExampleQuery() { SendLog log = new SendLog(); log.setTemplateName("kl"); /* * Note: the propertyPath parameter value of the withMatcher method fills in the field value of the domain object instead of the actual table field */ ExampleMatcher matcher = ExampleMatcher.matching() .withMatcher("templateName", match -> match.contains()); Example example = Example.of(log, matcher); Pageable pageable = PageRequest.of(0, 10); Page<SendLog> logPage = repository.findAll(example, pageable); }
The semantics of the above code implementation is to fuzzy query the records with templateName equal to "kl" and paginate them. At first glance, this code is passable. In fact, when there are more query conditions, this code will become smelly and long, and only support the basic string type field query. If the query conditions have time to filter, it will not be supported, especially when there are more complex points and multiple tables associated GG, so this way unqualified directly on the blacklist.
The second method inherits the jpaspecification executor method:
JPA 2 introduces a standard API that you can use to build queries programmatically. Spring Data JPA provides an API for defining such specifications using the JPA standard API. In this way, we first need to inherit the jpaspecification executor interface. Next, we use this way to implement queries with the same semantics as above:
public void testJpaSpecificationQuery() { String templateName = "kk"; Specification specification = (Specification) (root, query, criteriaBuilder) -> { Predicate predicate = criteriaBuilder.like(root.get("templateName"),templateName); query.where(predicate); return predicate; }; Pageable pageable = PageRequest.of(0, 2); Page<SendLog> logPage = sendLogRepository.findAll(specification, pageable); }
Obviously, this method is more appropriate, and it also supports complex query condition splicing, such as date and so on. The only drawback is that the property string of domain object needs to be handwritten, and the interface only provides findall (@ nullable specification < T > spec, pageable pageable) method, and all kinds of complex query logic splicing should be written in the service layer. It's really intolerable for people who have been popular for so many years with obsessive-compulsive disorder. It's a bit redundant and bloated to write complex queries by encapsulating a Dao class alone
Spring Data Jpa best practices
Before introducing the best practices in detail, we should first think about and understand one thing. How does Spring Data Jpa inherit one interface and realize various complex queries? In fact, this is a typical proxy application. As long as you inherit the lowest Repository interface, a proxy instance will be generated for you when the application starts, and the real target class is the class that finally performs the query. This class is simplejparpository, which implements all interfaces of JpaRepository and jpaspecification executor, so as long as it is based on simplejparepos By customizing the Repository base class, the Repository can have the same query function as the inheritance interface, and can write complex query methods in the implementation class.
1. Inherit simplejprarepository implementation class
/** * @author: kl @kailing.pub * @date: 2019/11/8 */ public abstract class BaseJpaRepository<T, ID> extends SimpleJpaRepository<T, ID> { public EntityManager em; BaseJpaRepository(Class<T> domainClass, EntityManager em) { super(domainClass, em); this.em = em; } }
To construct a simplejpalepository instance, only one domain object type and EntityManager instance are needed. EntityManager already exists in the Spring context and will be injected automatically. Domain object types can be injected into specific implementation classes. Such as:
/** * @author: kl @kailing.pub * @date: 2019/11/11 */ @Repository public class SendLogJpaRepository extends BaseJpaRepository<SendLog,Integer> { public SendLogJpaRepository(EntityManager em) { super(SendLog.class, em); } /** * Native Query * @param templateName * @return */ public SendLog findByTemplateName(String templateName){ String sql = "select * from send_log where templateName = :templateName"; Query query =em.createNativeQuery(sql); query.setParameter("templateName",templateName); return (SendLog) query.getSingleResult(); } /** * hql query * @param templateName * @return */ public SendLog findByTemplateNameNative(String templateName){ String hql = "from SendLog where templateName = :templateName"; TypedQuery<SendLog> query =em.createQuery(hql,SendLog.class); query.setParameter("templateName",templateName); return query.getSingleResult(); } /** * JPASpecification Implement complex paging query * @param logDto * @param pageable * @return */ public Page<SendLog> findAll(SendLogDto logDto,Pageable pageable) { Specification specification = (Specification) (root, query, criteriaBuilder) -> { Predicate predicate = criteriaBuilder.conjunction(); if(!StringUtils.isEmpty(logDto.getTemplateName())){ predicate.getExpressions().add( criteriaBuilder.like(root.get("templateName"),logDto.getTemplateName())); } if(logDto.getStartTime() !=null){ predicate.getExpressions().add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime").as(Timestamp.class),logDto.getStartTime())); } query.where(predicate); return predicate; }; return findAll(specification, pageable); } }
By inheriting BaseJpaRepository, SendLogJpaRepository has all the method functions defined in the interface of JpaRepository and JpaSpecification executor. Based on the EntityManager instance in the abstract base class, it is also very convenient to write HQL and native SQL queries. The most enjoyable purpose is not only to have the most basic CURD and other functions, but also to separate the ultra complex paging queries. It's just that the query method of JpaSpecification is not particularly brilliant. Let's continue with the best practice
2. Integrated QueryDsl structured query
Querydsl is a framework that can construct static type SQL like queries through its fluent API. This is the description of querydsl in the Spring Data Jpa document. Spring Data Jpa has good support for querydsl extension, which can be used seamlessly. Querydsl defines a set of interfaces similar to JpaSpecification, and uses the same way. Because querydsl has a maven plug-in, it can generate domain object operation entities during compilation, so it is more flexible and easy to use when splicing complex query conditions than JpaSpecification, especially when it is associated with multi table query. Here's how to integrate:
1. Rapid integration
Because the simplest QueryDsl integration method has been written before, I won't go over it here. For details, see Query DSL structured query jpa,
2. Rich BaseJpaRepository base classes
/** * @author: kl @kailing.pub * @date: 2019/11/8 */ public abstract class BaseJpaRepository<T, ID> extends SimpleJpaRepository<T, ID> { public EntityManager em; protected final QuerydslJpaPredicateExecutor<T> jpaPredicateExecutor; BaseJpaRepository(Class<T> domainClass, EntityManager em) { super(domainClass, em); this.em = em; this.jpaPredicateExecutor = new QuerydslJpaPredicateExecutor<>(JpaEntityInformationSupport.getEntityInformation(domainClass, em), em, SimpleEntityPathResolver.INSTANCE, getRepositoryMethodMetadata()); } }
The QuerydslJpaPredicateExecutor instance is added in the BaseJpaRepository base class, which is an implementation of Spring Data Jpa based on QueryDsl. The Predicate related query used to execute QueryDsl. After the integration of QueryDsl, the painting style of complex paging query becomes more relaxed, such as:
/** * QSendLog The Entity is automatically generated by QueryDsl plug-in. The plug-in will automatically scan the Entity with @ Entity and generate an EntityPath class for query */ private final static QSendLog sendLog = QSendLog.sendLog; public Page<SendLog> findAll(SendLogDto logDto, Pageable pageable) { BooleanExpression expression = sendLog.isNotNull(); if (logDto.getStartTime() != null) { expression = expression.and(sendLog.createTime.gt(logDto.getStartTime())); } if (!StringUtils.isEmpty(logDto.getTemplateName())) { expression = expression.and(sendLog.templateName.like("%"+logDto.getTemplateName()+"%")); } return jpaPredicateExecutor.findAll(expression, pageable); }
So far, to implement the same complex paging query, the code has been very fresh and elegant, and in this mode, the complex query has become very clear. But it's not perfect. There are two other issues that need to be addressed:
- The method implemented by QuerydslJpaPredicateExecutor does not support paging queries with field sorting. The following is its interface definition. You can see that either the paging query is in place in one step, but there is no sorting, or the sorting query returns the List list to encapsulate the paging itself.
public interface QuerydslPredicateExecutor<T> { Optional<T> findOne(Predicate predicate); Iterable<T> findAll(Predicate predicate); Iterable<T> findAll(Predicate predicate, Sort sort); Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders); Iterable<T> findAll(OrderSpecifier<?>... orders); Page<T> findAll(Predicate predicate, Pageable pageable); long count(Predicate predicate); boolean exists(Predicate predicate); }
- Complex multi table Association query QuerydslJpaPredicateExecutor is not supported
3. Final BaseJpaRepository form
Spring Data Jpa has limited support for QuerDsl, but QueryDsl has this function. For example, the above scenario needs special handling. The final modified BaseJpaRepository is as follows:
/** * @author: kl @kailing.pub * @date: 2019/11/8 */ public abstract class BaseJpaRepository<T, ID> extends SimpleJpaRepository<T, ID> { protected final JPAQueryFactory jpaQueryFactory; protected final QuerydslJpaPredicateExecutor<T> jpaPredicateExecutor; protected final EntityManager em; private final EntityPath<T> path; protected final Querydsl querydsl; BaseJpaRepository(Class<T> domainClass, EntityManager em) { super(domainClass, em); this.em = em; this.jpaPredicateExecutor = new QuerydslJpaPredicateExecutor<>(JpaEntityInformationSupport.getEntityInformation(domainClass, em), em, SimpleEntityPathResolver.INSTANCE, getRepositoryMethodMetadata()); this.jpaQueryFactory = new JPAQueryFactory(em); this.path = SimpleEntityPathResolver.INSTANCE.createPath(domainClass); this.querydsl = new Querydsl(em, new PathBuilder<T>(path.getType(), path.getMetadata())); } protected Page<T> findAll(Predicate predicate, Pageable pageable, OrderSpecifier<?>... orders) { final JPAQuery countQuery = jpaQueryFactory.selectFrom(path); countQuery.where(predicate); JPQLQuery<T> query = querydsl.applyPagination(pageable, countQuery); query.orderBy(orders); return PageableExecutionUtils.getPage(query.fetch(), pageable, countQuery::fetchCount); } }
A new findall (predicate predicate, pageable pageable, orderspecifier <? >... Orders) method is added to support complex paging queries and query scenarios with field sorting. The second change is the introduction of a JPAQueryFactory instance for complex queries associated with multiple tables. Use as follows:
/** * QSendLog The Entity is automatically generated by QueryDsl plug-in. The plug-in will automatically scan the Entity with @ Entity and generate an EntityPath class for query */ private final static QSendLog qSendLog = QSendLog.sendLog; private final static QTemplate qTemplate = QTemplate.template; public Page<SendLog> findAll(SendLogDto logDto, Template template, Pageable pageable) { JPAQuery countQuery = jpaQueryFactory.selectFrom(qSendLog).leftJoin(qTemplate); countQuery.where(qSendLog.templateCode.eq(qTemplate.code)); if(!StringUtils.isEmpty(template.getName())){ countQuery.where(qTemplate.name.eq(template.getName())); } JPQLQuery query = querydsl.applyPagination(pageable, countQuery); return PageableExecutionUtils.getPage(query.fetch(), pageable, countQuery::fetchCount); }
3. Integrate the sql executed by p6spy printing
The above functions are perfect, but when it comes to best practices, there seems to be less of a print SQL function. When using the structured semantics of Jpa to build a complex query, the result set of the query is often not what you want for various reasons, but it can't be checked because you don't know what the final SQL execution is. Spring Data Jpa also has the function of printing SQL, but it prints SQL without replacing query parameters, so it can't directly copy and execute. Therefore, a tool p6spy is recommended here. P6spy is a tool for printing the final execution of SQL, and it can record the execution time of SQL. It is also convenient to use, simple three-step integration:
1. Introduce dependency
<dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>${p6spy.version}</version> </dependency>
2. Modify the data source link string
jdbc:mysql://127.0.0.1:3306 to jdbc:p6spy:mysql://127.0.0.1:3306
3. Add configuration of spy.propertis
appender=com.p6spy.engine.spy.appender.Slf4JLogger logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat customLogMessageFormat = executionTime:%(executionTime)| sql:%(sqlSingleLine)
This is the most simplified configuration of custom printing. For more configurations, please refer to: https://p6spy.readthedocs.io/...
epilogue
The final BaseJpaRepository function basically meets all the query requirements, and does not separate the basic query from the complex query, so as not to write a large number of complex query splicing logic to the service layer, or to create a new complex query class. The problems put forward at the beginning of this paper have been solved thoroughly. The complex query code based on QueryDsl has clear logic and elegant structure, which is highly recommended. Finally, under Amway p6spy , a very practical tool for printing sql, which can help to troubleshoot and analyze the sql statements generated and executed by JPA. The printed sql statements can be directly copied to the mysql management tool for execution.