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);
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
上一篇: 重塑「互联网」,产业互联网成败的关键