错误描述
最近在使用spring-data-jpa时,自定义了一个update方法,如下:
public interface TestRepository extends JpaRepository<Test,Long> {
/**
* 根据主键ID更新
* @param paymentOrderId
* @param id
* @return
*/
@Query(value = "update transfer_orders as t set t.order = :order where t.id = :id",nativeQuery = true)
int updatePaymentOrderIdById(@Param("order") Long paymentOrderId, @Param("id") Long id);
}
这个方法在使用时,报错如下(只截取部分报错信息):
java.sql.SQLException: Can not issue data manipulation statements with executeQuery().
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:469)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1968)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2168)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
at org.hibernate.loader.Loader.doQuery(Loader.java:938)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doList(Loader.java:2692)
at org.hibernate.loader.Loader.doList(Loader.java:2675)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
at org.hibernate.loader.Loader.list(Loader.java:2502)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2200)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1463)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:214)
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)
原因分析
原因就是JPA默认使用executeQuery()去执行了上面自定义的SQL,而自定义的是一个update语句,所以应该使用execute()去执行,自此造成数据库报错。
修改方法
修改方法,在update方法的声明生使用注解**@Modifying**,告诉JPA这是一个UPDATE或DELETE语句,这样就可以正常执行,最终代码如下:
public interface TestRepository extends JpaRepository<Test,Long> {
/**
* 根据主键ID更新
* @param paymentOrderId
* @param id
* @return
*/
@Modifying
@Query(value = "update transfer_orders as t set t.order = :order where t.id = :id",nativeQuery = true)
int updatePaymentOrderIdById(@Param("order") Long paymentOrderId, @Param("id") Long id);
}
备注:
数据库使用MySQL,但此问题与数据库类型无关。