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);

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