英文:
Ebean RawSql does not allow named parameters in "case when" clause
问题
我在使用Ebean RawSql查询时遇到了一个问题。以下是一个简单的示例：
Finder<Long, Test> finder = new Finder<>(Test.class);
String query = "SELECT t.ID AS t_id, t.NAME as t_name, (SELECT CASE WHEN t.NAME = :name THEN 1 ELSE 0 END) AS sort_priority FROM test ORDER BY sort_priority";
RawSql raw = RawSqlBuilder.parse(query)
.columnMapping("t_id", "id")
.columnMapping("t_name", "name")
.columnMappingIgnore("sort_priority")
.create();
Query<Test> testQuery = finder.query().setRawSql(raw).setParameter("name", "Test1");
但是，当我执行这个查询时，出现了一个错误：
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':name THEN 1 ELSE 0 END) AS sort_priority FROM test ORDER BY sort_priorit' at line 1
如果我在查询中使用问号（?）代替命名参数，一切都正常。但我是否可以在保持命名参数的同时修复这个问题，并且同时使用 "case when" 子句呢？
英文:
I've encountered a problem while working with Ebean RawSql query. Here's a simple example.
Finder<Long, Test> finder= new Finder<>(Test.class);
String query = "SELECT t.ID AS t_id, t.NAME as t_name, (SELECT CASE WHEN t.NAME = :name THEN 1 ELSE 0 END) AS sort_priority FROM test ORDER BY sort_priority";
RawSql raw = RawSqlBuilder.parse(query)
.columnMapping("t_id", "id")
.columnMapping("t_name", "name")
.columnMappingIgnore("sort_priority")
.create();
Query<Test> testQuery = finder.query().setRawSql(raw).setParameter("name", "Test1");
But when I execute this query I get an error:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':name THEN 1 ELSE 0 END) AS sort_priority FROM test ORDER BY sort_priorit' at line 1
If I use ? instead of named parameters everything is okay. But can I somehow fix it with keeping named parameter and "case when" clause at the same time?
专注分享java语言的经验与见解，让所有开发者获益！
评论