如何使用Ebean查询原始SQL时映射结果 [英] How to map Results when Querying raw SQL using Ebean

问题描述

使用由Ebean创建的Postgres表，我想用手写的语句查询这些表：

Using Postgres Tables created by Ebean, I would like to query these tables with a hand-written statement:

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 query executes fine, but I don't know how I would proceed:

如何使用Play 2.2执行查询。和Ebean？
如何将此查询映射到可迭代对象？我是否需要创建一个包含查询中所有字段的Model类，还是可以使用某种HashMap？
如何以安全的方式参数化查询？

How do I even execute the query using Play 2.2. and Ebean? How can I map this query to an iterable object? Do I need to create a Model class which contains all the fields from the query, or can I use some sort of HashMap? How can I parameterize the query in a safe way?

推荐答案

要执行此查询，您需要使用RawSql类。您还必须创建要将结果投射到的类。

To execute this query you need to use RawSql class. You will also have to create class to which results will be casted.

以下是示例结果类的代码：

Here is a code of exemplary 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;
} 

执行此查询的示例：

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

这篇关于如何使用Ebean查询原始SQL时映射结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！