spring data jpa @Query注解中delete语句报错的解决

spring data jpa @query注解中delete语句报错

项目中需要删除掉表中的一些数据

@query("delete from engineerservices es where es.engineerid = ?1")
int deletebyegid(string engineerid);

但是提示了错误

org.hibernate.hql.queryexecutionrequestexception: not supported for dml operations

通过查阅相关的资料发现，对于执行update和delete语句需要添加@modifying注解

@modifying
@query("delete from engineerservices es where es.engineerid = ?1")
int deletebyegid(string engineerid);

不过，添加之后运行又出现了另一个错误

nested exception is javax.persistence.transactionrequiredexception: executing an update/delete query

发现缺少transaction，于是添加@transactional

@modifying
@transactional
@query("delete from engineerservices es where es.engineerid = ?1")
int deletebyegid(string engineerid);

到此，这条delete语句终于可以成功的执行了。

代码示例：

package com.easy.kotlin.chapter11_kotlin_springboot.dao 
import com.easy.kotlin.chapter11_kotlin_springboot.entity.image
import org.springframework.data.domain.page
import org.springframework.data.domain.pageable
import org.springframework.data.jpa.repository.modifying
import org.springframework.data.jpa.repository.query
import org.springframework.data.repository.pagingandsortingrepository
import org.springframework.data.repository.query.param
import org.springframework.transaction.annotation.transactional
 
/** created by jack on 2017/7/17.
@query注解里面的value和nativequery=true,意思是使用原生的sql查询语句.
sql模糊查询like语法,我们在写sql的时候是这样写的
like '%?%'
但是在@query的value字符串中, 这样写
like %?1%
另外，要注意的是： 对于执行update和delete语句需要添加@modifying注解
 */
 
interface imagerepository : pagingandsortingrepository<image, long> {
    @query("select a from #{#entityname} a where a.isdeleted=0 and a.category like %?1%")
    fun findbycategory(category: string): mutablelist<image>
 
    @query("select count(*) from #{#entityname} a where a.isdeleted=0 and a.url = ?1")
    fun countbyurl(url: string): int
 
    @query("select a from #{#entityname} a where a.isdeleted=0 and a.category like %:searchtext%")
    fun search(@param("searchtext") searchtext: string, pageable: pageable): page<image>
 
    @query("select a from #{#entityname} a where a.isdeleted=0 and a.isfavorite=1")
    fun findallfavorite(pageable: pageable): page<image>
 
    @query("select a from #{#entityname} a where a.isdeleted=0 and a.isfavorite=1 and a.category like %:searchtext%")
    fun searchfavorite(@param("searchtext") searchtext: string, pageable: pageable): page<image>
 
    @modifying
    @transactional
    @query("update #{#entityname} a set a.isfavorite=1 where a.id=?1")
    fun addfavorite(id: long)
 
    @modifying
    @transactional
    @query("delete from #{#entityname} a where a.id=?1")
    fun delete(id: long) 
} 
 

jpa使用@query注解实例

1. 一个使用@query注解的简单例子

@query(value = "select name,author,price from book b where b.price>?1 and b.price<?2")
list<book> findbypricerange(long price1, long price2);

2. like表达式

@query(value = "select name,author,price from book b where b.name like %:name%")
list<book> findbynamematch(@param("name") string name);

3. 使用native sql query

所谓本地查询，就是使用原生的sql语句（根据数据库的不同，在sql的语法或结构方面可能有所区别）进行查询数据库的操作。

@query(value = "select * from book b where b.name=?1", nativequery = true)
list<book> findbyname(string name);

4. 使用@param注解注入参数

@query(value = "select name,author,price from book b where b.name = :name and b.author=:author and b.price=:price")
list<book> findbynamedparam(@param("name") string name, @param("author") string author,
        @param("price") long price);

5. spel表达式(使用时请参考最后的补充说明)

'#{#entityname}'值为'book'对象对应的数据表名称(book)。

public interface bookqueryrepositoryexample extends repository<book, long>{
    @query(value = "select * from #{#entityname} b where b.name=?1", nativequery = true)
    list<book> findbyname(string name);
}

6. 一个较完整的例子

public interface bookqueryrepositoryexample extends repository<book, long> {
    @query(value = "select * from book b where b.name=?1", nativequery = true) 
    list<book> findbyname(string name);// 此方法sql将会报错(java.lang.illegalargumentexception)，看出原因了吗,若没看出来，请看下一个例子
 
    @query(value = "select name,author,price from book b where b.price>?1 and b.price<?2")
    list<book> findbypricerange(long price1, long price2);
 
    @query(value = "select name,author,price from book b where b.name like %:name%")
    list<book> findbynamematch(@param("name") string name);
 
    @query(value = "select name,author,price from book b where b.name = :name and b.author=:author and b.price=:price")
    list<book> findbynamedparam(@param("name") string name, @param("author") string author,
            @param("price") long price); 
}

7. s模糊查询注意问题

模糊查询时在原生sql中like后跟随的值需要单独传，%不支持单独出现；在使用length查询数据库时需要（）单独括起。

@repository
public interface saleschargerespolity extends jparepository<salescharge,integer> { 
    @query(value = "select p from salescharge p where ordertime>:starttime and ordertime<:endtime and staffid like :staffidlike and length(staffid)=(length(:staffid)+2)")    
    list<salescharge> finallsubchargeinfo(@param("starttime") date starttime, @param("endtime") date endtime,@param("staffid") string staffid, @param("staffidlike") string staffidlike); 
}

8. 解释例6中错误的原因

因为指定了nativequery = true，即使用原生的sql语句查询。使用java对象'book'作为表名来查自然是不对的。只需将book替换为表名book。

@query(value = "select * from book b where b.name=?1", nativequery = true)
list<book> findbyname(string name);

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