spring boot之使用spring data jpa的自定義sql方式

spring data jpa介紹

PA(Java Persistence API)是Sun官方提出的Java持久化規范。它為Java開發人員提供瞭一種對象/關聯映射工具來管理Java應用中的關系數據。他的出現主要是為瞭簡化現有的持久化開發工作和整合ORM技術，結束現在Hibernate，TopLink，JDO等ORM框架各自為營的局面。值得註意的是，JPA是在充分吸收瞭現有Hibernate，TopLink，JDO等ORM框架的基礎上發展而來的，具有易於使用，伸縮性強等優點。

自定義SQL查詢

Spring data 覺大部分的SQL都可以根據方法名定義的方式來實現，但是由於某些原因我們想使用自定義的SQL來查詢，spring data也是完美支持的；在SQL的查詢方法上面使用@Query註解，如涉及到刪除和修改在需要加上@Modifying.

    public interface CustomerRepository extends JpaRepository<Customer, Integer>{
    @Modifying
    @Query("update Customer c set c.customerName=?1 where c.id=?2")
    Integer modifyByIdAndUserId(String customerName,Integer id);
    @Modifying
    @Query("delete from Customer where id = ?1")
    void deleteByCustomerId(Integer id);
}

註意當調用自定義更新和刪除sql操作時，會出現下面的異常

org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:396) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:227) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy88.modifyByIdAndUserId(Unknown Source) at com.baidu.HelloPeopleApplicationTests.contextLoads(HelloPeopleApplicationTests.java:42) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:73) at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:83) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:538) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:760) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:206) Caused by: javax.persistence.TransactionRequiredException: Executing an update/delete query at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1496) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:380) at com.sun.proxy.$Proxy95.executeUpdate(Unknown Source) at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:256) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) … 41 more

對於該異常的原因是更新和刪除操作沒有進行事務處理，解決辦法是在service層調用CustomerRepository接口的方法的方法上添加註解@Transactional進行事務的處理,然後在調用service層的方法

@Service
public class CustomerService {
    @Autowired
    private CustomerRepository customerRepository;
    @Transactional
    public void deleteByCustomerId(Integer id) {
        customerRepository.deleteByCustomerId(id);
    }
    @Transactional
    public Integer modifyByIdAndUserId(String customerName,Integer id) {
        return customerRepository.modifyByIdAndUserId(customerName, id);
    }

Integer line = customerService.modifyByIdAndUserId("張三", 4);
        System.out.println(line);
        customerService.deleteByCustomerId(3);

執行後如圖所示：

jpa兩種自定義SQL的方式

當你的抽象類繼承瞭JpaRepository類時，就會擁有一些基本的增刪改查操作。但是，很多時候隻有這些簡單的功能是不夠的的，jpa也支持原生SQL和實體類SQL進行自定義查詢。

1. 原生SQL

@Query(value = "SELECT t2.userId, t1.title, t1.content, t1.completeTime, t2.scheduleState" +
            " FROM schedule t1 LEFT JOIN schedule_user t2 ON t1.id = t2.schedule_id " +
            " WHERE t2.user_id = ?1 AND t2.schedule_state = ?2", nativeQuery=true)
    List<ScheduleUserView> findScheduleListByState(Long userId, int scheduleState);

2. 實體類SQL

@Query(value = "SELECT new com.x3.schedule.saas.table.ScheduleUserView(" +
            " t2.userId, t1.title, t1.content, t1.completeTime, t2.scheduleState)" +
            " FROM ScheduleTable t1 LEFT JOIN ScheduleUserTable t2 ON t1.scheduleId = t2.scheduleId " +
            " WHERE t2.userId = ?1 AND t2.scheduleState = ?2")
    List<ScheduleUserView> findScheduleListByState(Long userId, int scheduleState);

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：

• Assert.assertEquals的使用方法及註意事項說明
• 解決json串和實體類字段不一致的問題
• 基於Spring Boot使用JpaRepository刪除數據時的註意事項
• 解決使用stream將list轉map時,key重復導致報錯的問題
• SpringBoot 如何實現自定義Redis序列化