Play Mapping ignore RawSQL Ebean

Question

I'm using Play Framework 2.3 and I always have trouble with mapping ignore when I try to create my own sql query.

I don't know if I should use Ebean to write this complexe sql query.

This is the SQL query works well :

 SELECT g.id, 
  concat(g.client_id,'-',trim(to_char(n.number,'099999'))) as reference,
 (
  SELECT sum(total_ttc)
    from global_commission_invoice_line 
  where global_commission_invoice_id= g.id 
 ) as totalTTC  
 from global_commission_invoice g 
 LEFT JOIN invoice_number n on g.invoice_number_id = n.id 
 LEFT JOIN client c2 on g.client_id = c2.id

I try to parse it with ebean because I have a WHEN clause with parameters.

My java code briefly looks like.

String sql = " SELECT g.id, " +
        " concat(g.client_id,'-',trim(to_char(n.number,'099999'))) as reference," +
        " (" +
        "  SELECT sum(total_ttc)" +
        "    from global_commission_invoice_line" +
        "  where global_commission_invoice_id= g.id" +
        " ) as totalTTC " +
        " from global_commission_invoice g" +
        " LEFT JOIN invoice_number n on g.invoice_number_id = n.id" +
        " LEFT JOIN client c2 on g.client_id = c2.id";

// Create sql
RawSql rawSql = RawSqlBuilder.parse(sql)
        .columnMapping("g.id","id")
        .columnMappingIgnore("concat(g.client_id,'-',trim(to_char(n.number,'099999')))")
        .columnMappingIgnore("( SELECT sum(total_ttc) from global_commission_invoice_line where global_commission_invoice_id= g.id )")
        .create();

return Ebean.find(models.GlobalCommissionInvoice.class).setRawSql(rawSql)
                .findList()

This mapping ignore succeeded and with no error :

.columnMappingIgnore("( SELECT sum(total_ttc) from global_commission_invoice_line where global_commission_invoice_id= g.id )")

But this one :

.columnMappingIgnore("concat(g.client_id,'-',trim(to_char(n.number,'099999')))")

Return this error :

Execution exception[[IllegalArgumentException: DB Column [ concat(g.client_id, '-', trim(to_char(n.number, '099999')))] not found in mapping. Expecting one of [[g.id, concat(g.client_id, '-', trim(to_char(n.number, '099999'))), ( SELECT sum(total_ttc) from global_commission_invoice_line where global_commission_invoice_id= g.id )]]]]

Some time I find the error, is about one space or one line break misplaced but I never know really how to use it with complex query without searching why I have an error for hours.

Should I use ebean for this type of sql query ? Am I using mapping ignore badly ?

P.S. : Sorry for my english, I'm french

Answer 1

I've found a solution. I debug ebean RawSql class and I made my mapping match with ebean mapping.

Ebean slice SELECT statement at each comma, so my concat function is sliced in 3 parts.

Solution :

        // Create sql
    RawSql rawSql = RawSqlBuilder.parse(sqlStringBuilder.getStrStatement())
            .columnMapping("g.id","id")
            .columnMappingIgnore("concat(g.client_id")
            .columnMappingIgnore("'-'")
            .columnMappingIgnore("trim(to_char(n.number")
            .columnMappingIgnore("'099999')))")
            .columnMappingIgnore("( SELECT sum(total_ttc) from global_commission_invoice_line where global_commission_invoice_id= g.id )")
            .create();