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 t c.customername=?1 where c.id=?2")    integer modifybyidandurid(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.convertjpaaccesxceptionifpossible(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.expoinvocationinterceptor.invoke(expoinvocationinterceptor.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.modifybyidandurid(unknown source) at com.baidu.hellopeopleapplicationtests.contextloads(hellop励志文字壁纸eopleapplicationtests.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.j直接抒情ava:363) at org.springframework.test.context.junit4.springjunit4classrunner.run(springjunit4classrunner.java:190) at org.eclip.jdt.internal.junit4.runner.junit4testreference.run(junit4testreference.java:86) at org.eclip.jdt.internal.junit.runner.testexecution.run(testexecution.java:38) at org.eclip.jdt.internal.junit.runner.remotetestrunner.runtests(remotetestrunner.java:538) at org.eclip.jdt.internal.junit.runner.remotetestrunner.runtests(remotetestrunner.java:760) at org.eclip.jdt.internal.junit.runner.remotetestrunner.run(remotetestrunner.java:460) at org.eclip.jdt.internal.junit.runner.remotetestrunner.main(remotetestrunner.java:206) caud by: javax.persistence.transactionrequir散光和近视的区别edexception: 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

对于该异常的原因是更新和删除操作没有进行事务处理，解决办法是在rvice层调用customerrepository接口的方法的方法上添加注解@transactional进行事务的处理,然后在调用rvice层的方法

@rvicepublic class customerrvice {    @autowired    private customerrepository customerrepository;    @transactional    public void deletebycustomerid(integer id) {        customerrepository.deletebycustomerid(id);    }    @transactional    public integer modifybyidandurid(string customername,integer id) {        return customerrepository.modifybyidandurid(customername, id);    }

integer line = customerrvice.modifybyidandurid("张三", 4);        system.out.println(line);        customerrvice.deletebycustomerid(3);

执行后如图所示：

jpa两种自定义sql的方式

当你的抽象类继承了jparepository类时，就会拥有一些基本的增删改查操作。但是，很多时候只有这些简单的功能是不够的的，jpa也支持原生sql和实体类sql进行自定义查询。

1. 原生sql

@query(value = "lect t2.urid, t1.title, t1.content, t1.completetime, t2.schedulestate" +            " from schedule t1 left join schedule_ur t2 on t1.id = t2.schedule_id " +            " where t2.ur_id = ?1 and t2.schedule_state = ?2", nativequery=true)    list<scheduleurview&g安徽巢湖t; findschedulelistbystate(long urid, int schedulestate);

2. 实体类sql

@query(value = "lect new com.x3.schedule.saas.table.scheduleurview(" +            " t2.urid, t1.title, t1.content, t1.completetime, t2.schedulestate)" +            " from scheduletable t1 left join scheduleurtable t2 on t1.scheduleid = t2.scheduleid " +            " where t2.urid = ?1 and t2.schedulestate = ?2")    list<scheduleurview> findschedulelistbystate(long urid, int schedulestate);

以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。