英文:
JpaRepository does not read string parameter in a native query
问题
我在一个Spring Boot应用程序中遇到了JpaRepository的问题。
我想在我的数据库上执行一个简单的更新查询，但事实证明原生查询非常令人讨厌，请帮忙。
public interface ImageRepository extends JpaRepository<Image, Integer> {
@Modifying
@Transactional
@Query(value = "UPDATE image SET path = (?0), status = (?1) WHERE Id = (?2)", nativeQuery = true)
void update(String path, String status, int Id);
}
上述代码返回以下错误信息：
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
我尝试将SQL方言更改为：
org.hibernate.dialect.SQLServer2008Dialect
org.hibernate.dialect.SQLServer2012Dialect
分别尝试了这两种方言，但都没有成功。
我还尝试以不同的方式编写查询，这样不会给我报错，但它不会更新字段。它可以从方法中检测到整数值，但会将字符串值设置为空值：
@Query(value = "UPDATE image SET physical_path = (?0), status = (?1) WHERE Id = (?2)", nativeQuery = true)
如果有人遇到了相同的问题，请提供支持。
英文:
I am facing a problem with my JpaRepository in a spring boot application
I want to perform a simple update query on my database, but it turns out that the native query is quite annoying, please help
public interface ImageRepository extends JpaRepository<Image, Integer> {
@Modifying
@Transactional
@Query(value = "UPDATE image SET path =(0?), status = (1?) WHERE Id = (2?)", nativeQuery = true)
void update(String path ,String status,int Id);
}
the code above returns the following error message
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
I have tried to change SQL dialect to
org.hibernate.dialect.SQLServer2008Dialect
org.hibernate.dialect.SQLServer2012Dialect
respectively and non of them worked.
I tried also to write the query in a different way which does not give me an error but it does not update the fields. it can detect the integer value from the method but it will set the string values to an emply value:
@Query(value = "UPDATE image SET physical_path =(0), status = (1) WHERE Id = (2)", nativeQuery = true)
If anyone has faced the same issue please support
答案1
得分: 1
从Spring Data JPA - 参考文档中可以看出，参数（以您想要使用的方式）的定义方式如下 -> ?1, ?2 等。
另外，请记住，JPQL 语法与普通 SQL 有些不同。
@Modifying
@Query("update Image i set i.path = ?1 where i.status = ?2 and i.id = ?3")
void update(String path, String status, int id);
英文:
From the Spring Data JPA - Reference you can see that the parameters (in the way you want to use them) are defined like -> ?1, ?2 etc..
Also, please keep in mind that the JPQL syntax is slightly different than plain sql.
@Modifying
@Query("update Image i set i.path = ?1 where i.status = ?2 where i.id = ?3")
void update(String path, String status, int id);
答案2
得分: 1
以下是您提供的代码部分的翻译：
// 使用命名参数的方式构建查询：
@Query(value = "UPDATE image i SET path =:path, status = :status WHERE i.Id = :Id", nativeQuery = true)
void update(@Param("path") String path , @Param("status") String status, @Param("Id") int Id);
// 对于位置参数的方式：
@Query(value = "UPDATE image i SET path = ?1, status = ?2 WHERE i.Id = ?3", nativeQuery = true)
void update(String path , String status, int Id);
英文:
Frame the query like this :
@Query(value = "UPDATE image i SET path =:path, status = :status WHERE i.Id = :Id", nativeQuery = true)
void update(@Param("path") String path , @Param("status") String status, @Param("Id") int Id);
For positional parameters :
@Query(value = "UPDATE image i SET path = ?1, status = ?2 WHERE i.Id = ?3", nativeQuery = true)
void update(String path , String status, int Id);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论