SpringBoot JPA 专题
Error:
Error starting ApplicationContext. To display the auto-configuration report re-run your application with 'debug' enabled.
2017-10-30 11:18:52.721 ERROR 16868 --- [main] o.s.boot.SpringApplication : Application startup failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'jobDetailServiceImpl': Unsatisfied dependency expressed through field 'jobDetailRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jobDetailRepository': Invocation of init method failed; nested exception is java.lang.IllegalStateException: Method must not have Pageable *and* Sort parameter. Use sorting capabilities on Pageable instead! Offending method: public abstract org.springframework.data.domain.Page com.hujiang.career.bank.data.analysis.repository.JobDetailRepository.findAll(org.springframework.data.domain.Pageable,org.springframework.data.domain.Sort)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:588) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:366) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) ~[spring-context-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543) ~[spring-context-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) ~[spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:303) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107) [spring-boot-1.5.8.RELEASE.jar:1.5.8.RELEASE]
at com.hujiang.career.bank.data.analysis.DataAnalysisApplication.main(DataAnalysisApplication.java:13) [classes/:na]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jobDetailRepository': Invocation of init method failed; nested exception is java.lang.IllegalStateException: Method must not have Pageable *and* Sort parameter. Use sorting capabilities on Pageable instead! Offending method: public abstract org.springframework.data.domain.Page com.hujiang.career.bank.data.analysis.repository.JobDetailRepository.findAll(org.springframework.data.domain.Pageable,org.springframework.data.domain.Sort)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:585) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
... 19 common frames omitted
Caused by: java.lang.IllegalStateException: Method must not have Pageable *and* Sort parameter. Use sorting capabilities on Pageable instead! Offending method: public abstract org.springframework.data.domain.Page com.hujiang.career.bank.data.analysis.repository.JobDetailRepository.findAll(org.springframework.data.domain.Pageable,org.springframework.data.domain.Sort)
at org.springframework.data.repository.query.QueryMethod.<init>(QueryMethod.java:87) ~[spring-data-commons-1.13.8.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryMethod.<init>(JpaQueryMethod.java:89) ~[spring-data-jpa-1.11.8.RELEASE.jar:na]
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:77) ~[spring-data-jpa-1.11.8.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:436) ~[spring-data-commons-1.13.8.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:221) ~[spring-data-commons-1.13.8.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:277) ~[spring-data-commons-1.13.8.RELEASE.jar:na]
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:263) ~[spring-data-commons-1.13.8.RELEASE.jar:na]
at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:101) ~[spring-data-jpa-1.11.8.RELEASE.jar:na]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
... 29 common frames omitted
解决办法：
将
@GetMapping("/list")
public Page<JobDetail> list(@PageableDefault(size = 5) Pageable pageable) {
return jobDetailService.list(pageable);
}
@Autowired private JobDetailRepository jobDetailRepository; @Override public Page<JobDetail> list(Pageable pageable) { return jobDetailRepository.findAll(pageable,new Sort(Sort.Direction.DESC,"id")); }
改成：
@GetMapping("/list")
public Page<JobDetail> list(@PageableDefault(size = 5, sort = "id", direction = Sort.Direction.DESC) Pageable pageable) {
return jobDetailService.list(pageable);
}
@Autowired private JobDetailRepository jobDetailRepository; @Override public Page<JobDetail> list(Pageable pageable) { return jobDetailRepository.findAll(pageable); }
原因：
A Pageable already carries a Sort. Why would you want to hand in an additional one? The other point is, that the method can't have the same return type.
A Pageable usually results in a Page returned, which doesn't work if only a Sort is given. Yes, there's a case for returning a List or the like for both but I'd argue that giving up the constraints opens up a source for undetected misdeclarations.
So I'd recommend to simply declare two query methods, one taking a Pageeable, one taking a Sort.
https://jira.spring.io/browse/DATAJPA-1138
Pageable 是Spring Data库中定义的一个接口，该接口是所有分页相关信息的一个抽象，通过该接口，我们可以得到和分页相关所有信息（例如pageNumber、pageSize等）。
Pageable定义了很多方法，但其核心的信息只有两个：一是分页的信息（page、size），二是排序的信息。
在springmvc的请求中只需要在方法的参数中直接定义一个pageable类型的参数，当Spring发现这个参数时，Spring会自动的根据request的参数来组装该pageable对象，
Spring支持的request参数如下：
page，第几页，从0开始，默认为第0页 size，每一页的大小，默认为20 sort，排序相关的信息，以property,property(,ASC|DESC)的方式组织，例如sort=firstname&sort=lastname,desc表示在按firstname正序排列基础上按lastname倒序排列。
这样，我们就可以通过url的参数来进行多样化、个性化的查询。
Spring data提供了@PageableDefault帮助我们个性化的设置pageable的默认配置。
例如@PageableDefault(value = 15, sort = { "id" }, direction = Sort.Direction.DESC)表示默认情况下我们按照id倒序排列，每一页的大小为15。
@ResponseBody @RequestMapping(value = "list", method=RequestMethod.GET) public Page<blog> listByPageable(@PageableDefault(value = 15, sort = { "id" }, direction = Sort.Direction.DESC) Pageable pageable) { return blogRepository.findAll(pageable); }
http://blog.csdn.net/zsg88/article/details/66025560
今天给大家介绍一下SpringBoot中JPA的一些常用操作，例如：增删改查、分页、排序、事务操作等功能。
下面先来介绍一下JPA中一些常用的查询操作:
//And --- 等价于 SQL 中的 and 关键字，比如 findByHeightAndSex(int height,char sex)； public List<User> findByHeightAndSex(int height,char sex); // Or --- 等价于 SQL 中的 or 关键字，比如 findByHeightOrSex(int height,char sex)； public List<User> findByHeightOrSex(int height,char sex); //Between --- 等价于 SQL 中的 between 关键字，比如 findByHeightBetween(int min, int max)； public List<User> findByHeightBetween(int min,int max); //LessThan --- 等价于 SQL 中的 "<"，比如 findByHeightLessThan(int max)； public List<User> findByHeightLessThan(int max); //GreaterThan --- 等价于 SQL 中的">"，比如 findByHeightGreaterThan(int min)； public List<User> findByHeightGreaterThan(int min); //IsNull --- 等价于 SQL 中的 "is null"，比如 findByNameIsNull()； public List<User> findByNameIsNull(); //IsNotNull --- 等价于 SQL 中的 "is not null"，比如 findByNameIsNotNull()； public List<User> findByNameIsNotNull(); //NotNull --- 与 IsNotNull 等价； public List<User> findByNameNotNull(); //Like --- 等价于 SQL 中的 "like"，比如 findByNameLike(String name); public List<User> findByNameLike(String name); //NotLike --- 等价于 SQL 中的 "not like"，比如 findByNameNotLike(String name)； public List<User> findByNameNotLike(String name); //OrderBy --- 等价于 SQL 中的 "order by"，比如 findByNameNotNullOrderByHeightAsc()； public List<User>findByNameNotNullOrderByHeightAsc(); //Not --- 等价于 SQL 中的 "！ ="，比如 findByNameNot(String name)； public List<User> findByNameNot(String name); //In --- 等价于 SQL 中的 "in"，比如 findByNameIN(String name); public List<User> findByNameIn(String name); //NotIn --- 等价于 SQL 中的 "not in"，比如 findByNameNotIN(String name); public List<User> findByNameNotIn(String name);
JPA中的风格就是这样，每个方法其实都是一条SQl命令，通过一些关键字就可以实现SQL中类似于like in等等之类的命令了。
最重要的是我们再开发的过程中，只需要编写dao中一个个方法，不需要我们编写dao的实现类，这样就可以大大的挺高代码的复用率、提高我们的开发效率。
说道这里不免会有人会问，那一些比较复杂的关联查询要怎么实现呢，JPA的处理方法是：利用原生的SQl命令来实现那些复杂的关联查询，下面就来看下案例。
//利用原生的SQL进行查询操作 @Query(value = "select o.* from orders o ,user u where o.uid=u.id and u.name=?1", nativeQuery = true) @Modifying public List<Order> findOrderByName(String name); //利用原生的SQL进行删除操作 @Query(value = "delete from orders where id=?1 ", nativeQuery = true) @Modifying public void deleteOrderById(int id); //利用原生的SQL进行删除操作 @Query(value = "delete from orders where uid=?1 ", nativeQuery = true) @Modifying public void deleteOrderByUId(int uid); //利用原生的SQL进行修改操作 @Query(value = "update orders set name=?1 where id=?2 ", nativeQuery = true) @Modifying public void updateOrderName(String name,int id); //利用原生的SQL进行插入操作 @Query(value = "insert into orders(name,uid) value(?1,?2)", nativeQuery = true) @Modifying public void insertOrder(String name,int uid);
上面的案例中给出了，利用JPA实现原生的SQL操作，可以很方便的进行数据库表的操作。
所以如果是那种查询语句不是非常复杂，对查询时间要求不是特别苛刻的项目，完全可以采用JPA来进行项目的开发。
下面接着来介绍JPA是怎么实现分页的效果，其实JPA脱胎于hibernate，所以本身就对分页功能有很好的支持。下面给出具体例子：
//实现分页功能 Page<User> findByNameNot(String name,Pageable pageable);
@RequestMapping(value = "/params", method= RequestMethod.GET) @ResponseBody public String getEntryByParams(@RequestParam(value = "name", defaultValue = "林志强") String name, @RequestParam(value = "page", defaultValue = "0") Integer page, @RequestParam(value = "size", defaultValue = "15") Integer size) { Sort sort = new Sort(Sort.Direction.DESC, "id"); Pageable pageable = new PageRequest(page, size, sort); Page<User> pages=userDao.findByNameNot(name,pageable); Iterator<User> it=pages.iterator(); while(it.hasNext()){ System.out.println("value:"+((User)it.next()).getId()); } return "success...login...."; }
上面的代码一个是在dao层中的，一个是在controller中的。
dao层中添加一个返回值为Page，参数值为Pageable。controller层中通过实例化Pageable这个类，然后调用dao层这个分页方法。
通过这些步骤就可以轻轻松松的实现分页的效果啦，看起来是不是特别方便。
最后在给大家介绍一下JPA是如何实现事务操作的。其实因为SpringBoot中已经对事务做了很好的封装了，使用起来特别方便。下面看一下案例：
@RequestMapping("/saveOrder")
@ResponseBody
@Transactional()
public String saveOrder(){
Order o1=new Order("11",2);
Order o2=new Order("22",2);
Order o3=new Order("33",2);
Order o4=new Order("44",2);
orderDao.save(o1);
orderDao.save(o2);
orderDao.save(o3);
orderDao.save(o4);
return "successfull....saveOrder......";
}
只要在方法的上面加上@Transaction 这个注解就可以轻轻松松的实现事务的操作了，是不是特别方便啊。
不过这里有几点需要注意的是：
1.这个注解实现的事务管理器是默认的，如果不想要默认是事务管理器，可以自己进行添加，我这里就不多介绍了。
2.事务的隔离级别也是可以自己设置的，具体请看这篇博客 点击打开链接
3.事务的传播行为也是可以自己设置的，具体请看这篇博客点击打开链接
到此到此关于JPA的一些常规方法就介绍完了，谢谢大家的阅读。
http://blog.csdn.net/linzhiqiang0316/article/details/52639265
Error
... 55 common frames omitted Caused by: java.lang.IllegalArgumentException: Did not find a query class com.product.domain.QProductPoolShelfStatusStatement for domain class com.product.domain.ProductPoolShelfStatusStatement! at org.springframework.data.querydsl.SimpleEntityPathResolver.createPath(SimpleEntityPathResolver.java:62) ~[spring-data-commons-2.0.6.RELEASE.jar:2.0.6.RELEASE] at org.springframework.data.jpa.repository.support.QuerydslJpaRepository.<init>(QuerydslJpaRepository.java:90) ~[spring-data-jpa-2.0.6.RELEASE.jar:2.0.6.RELEASE] at org.springframework.data.jpa.repository.support.QuerydslJpaRepository.<init>(QuerydslJpaRepository.java:74) ~[spring-data-jpa-2.0.6.RELEASE.jar:2.0.6.RELEASE] at sun.reflect.GeneratedConstructorAccessor125.newInstance(Unknown Source) ~[na:na] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_321] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_321] at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:170) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE] ... 69 common frames omitted Caused by: java.lang.ClassNotFoundException: com.product.domain.QProductPoolShelfStatusStatement at java.net.URLClassLoader.findClass(URLClassLoader.java:387) ~[na:1.8.0_321] at java.lang.ClassLoader.loadClass(ClassLoader.java:418) ~[na:1.8.0_321] at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355) ~[na:1.8.0_321] at java.lang.ClassLoader.loadClass(ClassLoader.java:351) ~[na:1.8.0_321] at org.springframework.util.ClassUtils.forName(ClassUtils.java:274) ~[spring-core-5.0.5.RELEASE.jar:5.0.5.RELEASE] at org.springframework.data.querydsl.SimpleEntityPathResolver.createPath(SimpleEntityPathResolver.java:55) ~[spring-data-commons-2.0.6.RELEASE.jar:2.0.6.RELEASE] ... 75 common frames omitted
解决：
执行下面操作：
maven-项目-clean-install
https://blog.csdn.net/Jackey1314007/article/details/116258805
Using @Query
Using named queries to declare queries for entities is a valid approach and works fine for a small number of queries. As the queries themselves are tied to the Java method that runs them, you can actually bind them directly by using the Spring Data JPA @Query annotation rather than annotating them to the domain class. This frees the domain class from persistence specific information and co-locates the query to the repository interface.
public interface UserRepository extends JpaRepository<User, Long> { @Query("select u from User u where u.emailAddress = ?1") User findByEmailAddress(String emailAddress); }
Applying a QueryRewriter
Sometimes, no matter how many features you try to apply, it seems impossible to get Spring Data JPA to apply every thing you’d like to a query before it is sent to the EntityManager.
You have the ability to get your hands on the query, right before it’s sent to the EntityManager and "rewrite" it. That is, you can make any alterations at the last moment.
@Query
public interface MyRepository extends JpaRepository<User, Long> { @Query(value = "select original_user_alias.* from SD_USER original_user_alias", nativeQuery = true, queryRewriter = MyQueryRewriter.class) List<User> findByNativeQuery(String param); @Query(value = "select original_user_alias from User original_user_alias", queryRewriter = MyQueryRewriter.class) List<User> findByNonNativeQuery(String param); }
This example shows both a native (pure SQL) rewriter as well as a JPQL query, both leveraging the same QueryRewriter. In this scenario, Spring Data JPA will look for a bean registered in the application context of the corresponding type.
You can write a query rewriter like this:
QueryRewriter
public class MyQueryRewriter implements QueryRewriter { @Override public String rewrite(String query, Sort sort) { return query.replaceAll("original_user_alias", "rewritten_user_alias"); } }
Using Advanced LIKE Expressions
The query running mechanism for manually defined queries created with @Query allows the definition of advanced LIKE expressions inside the query definition, as shown in the following example:
like expressions in @Querypublic interface UserRepository extends JpaRepository<User, Long> { @Query("select u from User u where u.firstname like %?1") List<User> findByFirstnameEndsWith(String firstname); }
In the preceding example, the LIKE delimiter character (%) is recognized, and the query is transformed into a valid JPQL query (removing the %). Upon running the query, the parameter passed to the method call gets augmented with the previously recognized LIKE pattern.
Native Queries
The @Query annotation allows for running native queries by setting the nativeQuery flag to true, as shown in the following example:
public interface UserRepository extends JpaRepository<User, Long> { @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true) User findByEmailAddress(String emailAddress); }
Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL. You can, however, use native queries for pagination by specifying the count query yourself, as shown in the following example:
@Querypublic interface UserRepository extends JpaRepository<User, Long> { @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1", countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1", nativeQuery = true) Page<User> findByLastname(String lastname, Pageable pageable); }
A similar approach also works with named native queries, by adding the .count suffix to a copy of your query. You probably need to register a result set mapping for your count query, though.
However, using Sort together with @Query lets you sneak in non-path-checked Order instances containing functions within the ORDER BY clause. This is possible because the Order is appended to the given query string. By default, Spring Data JPA rejects any Order instance containing function calls, but you can use JpaSort.unsafe to add potentially unsafe ordering.
The following example uses Sort and JpaSort, including an unsafe option on JpaSort:
Sort and JpaSort
public interface UserRepository extends JpaRepository<User, Long> { @Query("select u from User u where u.lastname like ?1%") List<User> findByAndSort(String lastname, Sort sort); @Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%") List<Object[]> findByAsArrayAndSort(String lastname, Sort sort); } repo.findByAndSort("lannister", Sort.by("firstname")); (1) repo.findByAndSort("stark", Sort.by("LENGTH(firstname)")); (2) repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)")); (3) repo.findByAsArrayAndSort("bolton", Sort.by("fn_len")); (4)
Using Named Parameters
By default, Spring Data JPA uses position-based parameter binding, as described in all the preceding examples. This makes query methods a little error-prone when refactoring regarding the parameter position. To solve this issue, you can use @Param annotation to give a method parameter a concrete name and bind the name in the query, as shown in the following example:
public interface UserRepository extends JpaRepository<User, Long> { @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname") User findByLastnameOrFirstname(@Param("lastname") String lastname, @Param("firstname") String firstname); }
Modifying Queries
All the previous sections describe how to declare queries to access a given entity or collection of entities. You can add custom modifying behavior by using the custom method facilities described in “Custom Implementations for Spring Data Repositories”. As this approach is feasible for comprehensive custom functionality, you can modify queries that only need parameter binding by annotating the query method with @Modifying, as shown in the following example:
@Modifying @Query("update User u set u.firstname = ?1 where u.lastname = ?2") int setFixedFirstnameFor(String firstname, String lastname);
Doing so triggers the query annotated to the method as an updating query instead of a selecting one. As the EntityManager might contain outdated entities after the execution of the modifying query, we do not automatically clear it (see the JavaDoc of EntityManager.clear() for details), since this effectively drops all non-flushed changes still pending in the EntityManager. If you wish the EntityManager to be cleared automatically, you can set the @Modifying annotation’s clearAutomatically attribute to true.
The @Modifying annotation is only relevant in combination with the @Query annotation. Derived query methods or custom methods do not require this annotation.
Derived Delete Queries
Spring Data JPA also supports derived delete queries that let you avoid having to declare the JPQL query explicitly, as shown in the following example:
interface UserRepository extends Repository<User, Long> { void deleteByRoleId(long roleId); @Modifying @Query("delete from User u where u.role.id = ?1") void deleteInBulkByRoleId(long roleId); }
Although the deleteByRoleId(…) method looks like it basically produces the same result as the deleteInBulkByRoleId(…), there is an important difference between the two method declarations in terms of the way they are run. As the name suggests, the latter method issues a single JPQL query (the one defined in the annotation) against the database. This means even currently loaded instances of User do not see lifecycle callbacks invoked.
To make sure lifecycle queries are actually invoked, an invocation of deleteByRoleId(…) runs a query and then deletes the returned instances one by one, so that the persistence provider can actually invoke @PreRemove callbacks on those entities.
In fact, a derived delete query is a shortcut for running the query and then calling CrudRepository.delete(Iterable<User> users) on the result and keeping behavior in sync with the implementations of other delete(…) methods in CrudRepository.
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
