在游戏中!2.x应用程序,我正在尝试使用Ebean向MySQL Server发送简单的查询。
我的完整课程看起来如下:
public static List<Venue> search(String query) {
List<Venue> matches = new ArrayList<Venue>();
try {
String q = query.replace(" ", "");
String sql = "SELECT v.id, c.company, c.postcode n" +
"FROM venue v n" +
"JOIN contact c ON (c.id = v.id) n" +
"WHERE REPLACE(c.postcode, ' ', '') LIKE '%" + q + "%' n" +
" OR c.company LIKE '%" + q + "%'";
RawSql rawSql = RawSqlBuilder.unparsed(sql)
.columnMapping("v.id", "id")
.columnMapping("c.company", "contact.company")
.columnMapping("c.postcode", "contact.postcode")
.create();
Query<Venue> eQ = Ebean.find(Venue.class);
eQ.setRawSql(rawSql);
matches = eQ.findList();
}
catch (Exception e) {
Utils.eHandler("Venue.search(" + query + ")", e);
}
finally {
return matches;
}
}
但是,执行该行...
matches = eQ.findList();
..导致以下错误,这似乎是mySQL错误(?):
Query threw SQLException:Column Index out of range, 0 < 1.
Bind values:[]
Query was:
SELECT v.id, c.company, c.postcode
FROM venue v
JOIN contact c ON (c.id = v.id)
WHERE REPLACE(c.postcode, ' ', '') LIKE '%sw3%'
OR c.company LIKE '%sw3%'
查询本身很好,例如,我可以从错误消息中复制版本,将其粘贴到MySQL Workbench中,并且没有问题。
请注意,我正在使用RAWSQL,因为我需要有超过2"或"子句,据我所知,这是唯一的方法。
有人可以帮忙吗?
谢谢!
找到了问题(或至少一个解决方案)。由于"联系人"是"场地"的子对象,因此您无法在未指定联系人标识符的情况下映射到联系字段(例如" contact.company")。因此,以下代码,添加了适当的元素用于Contact.ID,工作:
String sql = "SELECT v.id, c.id, c.company, c.postcode " +
"FROM venue v " +
"JOIN contact c ON (c.id = v.id) " +
"WHERE REPLACE(c.postcode, ' ', '') LIKE '%" + q + "%' " +
" OR c.company LIKE '%" + q + "%'";
RawSql rawSql = RawSqlBuilder.unparsed(sql)
.columnMapping("v.id", "id")
.columnMapping("c.id", "contact.id")
.columnMapping("c.company", "contact.company")
.columnMapping("c.postcode", "contact.postcode")
.create();
甚至比这更好,因为例程只是返回查询中与之匹配的"场地"对象的列表,甚至不需要包含其他字段,所以我真正需要的就是以下内容(这也有效):
String sql = "SELECT v.id " +
"FROM venue v " +
"JOIN contact c ON (c.id = v.id) " +
"WHERE REPLACE(c.postcode, ' ', '') LIKE '%" + q + "%' " +
" OR c.company LIKE '%" + q + "%'";
RawSql rawSql = RawSqlBuilder.unparsed(sql)
.columnMapping("v.id", "id")
.create();