mysql的sqlbean,使用RawSql通过ebean执行MySQL查询时出错

In a Play! 2.x application, I'm trying to send a simple query to a MySQL server using ebean.

My complete class looks as follows:

public static List search(String query) {

List matches = new ArrayList();

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 eQ = Ebean.find(Venue.class);

eQ.setRawSql(rawSql);

matches = eQ.findList();

}

catch (Exception e) {

Utils.eHandler("Venue.search(" + query + ")", e);

}

finally {

return matches;

}

}

However, executing the line...

matches = eQ.findList();

..results in the following error, which appears to be a MySQL error(?):

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%'

The query itself is fine, for example I can copy the version from the error message, paste it into MySQL Workbench, and it executes no problem.

Note that I'm using RawSql because I'll need to have more than 2 "OR" clauses, and as far as I know this is the only way to do it.

Can anyone help?

Thanks!

解决方案

Found the problem (or a solution, at least). As "Contact" is a child object of "Venue", you can't map to Contact fields (e.g. "contact.company") without specifying the Contact identifier. So, the following code, with appropriate elements added for contact.id, works:

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();

Even better than that, since the routine is just returning a list of "Venue" objects that are matched in the query, it's not even necessary to include the additional fields, so all I really needed was the following (which also works):

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();