Mapping RawSql Result to Object in Java Ebean

Question

Let's say I have a class Article which is automatically mapped by Java Ebean as a database table.

For this table I wanted to retrieve entries via a RawSql query, because I find SQL more simple when it gets to complex queries with many joins and such. By far I have managed to give my SQL statement to the Parser. The query is correct, I already checked that.

The only problem is, that I don't know, how to map the database results to my Article class. I know, that there is a columnMapping(...) method but honestly, I am to lazy to map every single column manually...

Isn't there another way to just like myResults.mapToClass(Article.class) to retrieve something like a List<Article>?

This is the code I already have:

Finder<String, Article> find = new Finder<String, Article>(
        String.class, Article.class);
String sql = "SELECT * FROM article [...]";
RawSql rawSql = RawSqlBuilder.parse(sql).create();
List<Article> returnList = find.setRawSql(rawSql).findList();

Alternatively:

Finder<String, Article> find = new Finder<String, Article>(
                String.class, Article.class);
String sql = "SELECT id, title, sub_title FROM article [...]";
RawSql rawSql = RawSqlBuilder.parse(sql)
            .columnMapping("id", "id")
            .columnMapping("title", "title")
            .columnMapping("sub_title", "subTitle")
            .create();
List<Article> resultList = find.setRawSql(rawSql).findList();

I have the same problem. Anybody found a solution for this? – sliwinski.lukas Dec 04 '13 at 08:38 — sliwinski.lukas, Dec 04 '13 at 08:38
pinging this issue. I have the same problem! – skariel Dec 29 '15 at 18:28 — skariel, Dec 29 '15 at 18:28

score 1 · Answer 1 · answered Jun 13 '16 at 08:53

A lot of happened in Ebean since the question was asked, but I think the problem is still valid. the new RawSqlBuilder.tableMapping() makes things easier as can be seen in the code below, but afaik it still needs manual mapping of all attributes (no SELECT table.* FROM table)

I have this exact problem, and worked around this by creating a helper object (@Entity/@Sql) that I map to. E.g. CustomerWithPurchaseStats.

Extract:

@Entity
@Sql
public class CustomerWithPurchaseStats {

    @OneToOne
    private Customer customer;
...

And in the DAO:

public List<CustomerWithPurchaseStats> getAllCustomersWithPurchaseStats() {
    StringBuilder sql = new StringBuilder("SELECT cu.id, <manually add all fields you need mapped ").append(" FROM customer cu ");
    RawSqlBuilder rawSqlBuilder = RawSqlBuilder.parse(sql.toString());
    rawSqlBuilder.tableAliasMapping("cu", "customer").create();
    return Ebean.find(CustomerWithPurchaseStats.class)
                .setRawSql(rawSqlBuilder.create())
                .findList();
}

Mapping RawSql Result to Object in Java Ebean

1 Answers1