英文:
Spring Data JPA: The data types varchar and varbinary are incompatible in the add operator exception occurs when using concat() method
问题
在我的Spring Data项目中，我正尝试在JPA查询注解上使用连接操作（如下所示）：
@Query("SELECT a from ApiEnrollmentsView a where (:mrn is null or a.mrn=:mrn) and " +
" (:firstName is null or a.firstName like concat(:firstName, '%')) and " +
" (:lastName is null or a.lastName like concat(:lastName, '%')) ")
List<ApiEnrollmentsView> findEnrollmentsByMrnAndFirstNameAndLastName(String mrn, String firstName, String lastName);
项目编译正常，但当我访问该方法时，我收到异常信息：“数据类型varchar和varbinary在加法运算符中不兼容”。
异常信息：
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types varchar and varbinary are incompatible in the add operator.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)
...
英文:
In my Spring Data project I am trying to use concat operation on JPA query annotation(shown below)
@Query("SELECT a from ApiEnrollmentsView a where (:mrn is null or a.mrn=:mrn) and " +
" (:firstName is null or a.firstName like concat(:firstName, '%')) and " +
" (:lastName is null or a.lastName like concat(:lastName, '%')) ")
List<ApiEnrollmentsView> findEnrollmentsByMrnAndFirstNameAndLastName(String mrn, String firstName, String lastName);
Project compiles fine but when I access the method, I get exception data types varchar and varbinary are incompatible in the add operator
Exception:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types varchar and varbinary are incompatible in the add operator.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
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:57)
... 174 more
答案1
得分: 2
我记得我曾经遇到过完全相同的奇怪错误。正如评论中提到的，问题是通过使用 coalesce 函数而不是 concat 得以解决。
英文:
I remember I had exactly the same weird error. As it is mentioned in the comments the problem solved by using coalesce function instead of concat.
答案2
得分: 0
使用 COALESCE(:lastName, '') 替代 CONCAT 函数内部。这解决了我的问题。
英文:
use COALESCE(:lastName, '') inside CONCAT. It resolves my issue
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论