How to map results when querying raw SQL using Ebean
Using Postgres tables created by Ebean, I would like to query these tables with this handwritten one:
SELECT r.name,
r.value,
p.name as param1,
a.name as att1,
p2.name as param2,
a2.name as att2
FROM compatibility c
JOIN attribute a ON c.att1_id = a.id
JOIN attribute a2 ON c.att2_id = a2.id
JOIN PARAMETER p ON a.parameter_id = p.id
JOIN PARAMETER p2 ON a2.parameter_id = p2.id
JOIN rating r ON c.rating_id = r.id
WHERE p.problem_id = %d
OR p2.problem_id = %d
Each of the joined tables represent one of my model classes. The request runs fine, but I don't know how I will proceed:
How to make a request using Play 2.2. and Ebean? How can I match this query to an iterable object? I need to create a Model class that contains all the fields from the request, or can I use some kind of HashMap? How can I parameterize a query in a safe way?
+3
source to share
1 answer
You need to use the RawSql class to execute this query. You also need to create a class for which the results will run.
Here's an example of an example result class:
import javax.persistence.Entity;
import com.avaje.ebean.annotation.Sql;
@Entity
@Sql
public class Result {
String name;
Integer value;
String param1;
String param2;
String att1;
String att2;
}
And an example of this query:
String sql
= " SELECT r.name,"
+ " r.value,"
+ " p.name as param1,"
+ " a.name as att1,"
+ " p2.name as param2,"
+ " a2.name as att2"
+ " FROM compatibility c"
+ " JOIN attribute a ON c.att1_id = a.id"
+ " JOIN attribute a2 ON c.att2_id = a2.id"
+ " JOIN PARAMETER p ON a.parameter_id = p.id"
+ " JOIN PARAMETER p2 ON a2.parameter_id = p2.id"
+ " JOIN rating r ON c.rating_id = r.id"
+ " WHERE p.problem_id = %d"
+ " OR p2.problem_id = %d"
RawSql rawSql =
RawSqlBuilder
.parse(sql)
.columnMapping("r.name", "name")
.columnMapping("r.value", "value")
.create();
Query<Result> query = Ebean.find(Result.class);
query.setRawSql(rawSql)
.where().gt("amount", 10);
List<Result> list = query.findList();
+6
source to share