欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-24 16:55:38
目录spring data jpa介绍自定义sql查询jpa两种自定义sql的方式1. 原生sql2. 实体类sqlspring data jpa介绍pa(java persistence api)是...

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

执行后如图所示:

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

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

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