The limitations of RawSqlBuild.parse

[wenzhihong2003]

The RawSqlBuild.parse() method of Ebean can only handle very simple SQL structures. The reason for this is that during the parsing of the SQL, it only parses based on the keywords in the string. For example, the following SQL will cause an error when parsed by RawSqlBuild.parse().

String sql = """
            WITH AggregatedData AS (
              select
                  company_id,
                  company_name,
                  spool_type,
                  sum(lend_num) as lend_num,
                  sum(return_num) as return_num,
                  sum(remain_num) as remain_num,
                  sum(sum(remain_num)) over (partition by company_id) as company_total_remain
              from
                  reelnoreturn_view
              ${where}
              group by  company_id, company_name, spool_type
          )
          select
              company_id,
              company_name,
              spool_type,
              lend_num,
              return_num,
              remain_num
          from
              AggregatedData
          order by
              company_total_remain desc,  company_id,  spool_type
          """;
        RawSql rawSql = RawSqlBuilder.parse(sql).create();
        Query<EReelNoReturnAgg> query = db.createQuery(EReelNoReturnAgg.class).setRawSql(rawSql);
        if (isNotEmpty(companyName)) {
            query.where().contains("company_name", companyName);
       
        }

Is there any plan to enhance this part? For example, introducing a library like jsqlparse to parse the SQL into an abstract syntax tree(ASM), and then modifying the syntax nodes based on the query?

[rbygrave]

Is there any plan to enhance this part?

Well there wasn't any plans ... but I do see the issue here.

There is RawSqlBuilder.unparsed(sql) ... which doesn't support/allow the ${where} but that is pretty much what I think would be good / useful here.

That is, get the placeholder positions [${where} etc] and NOT try to parse the select clause ... which means we can dynamically add to the where clause but we need to manually specify the column mapping like unparsed.

Hmm.

introducing a library like jsqlparse to parse the SQL

I haven't looked into that. I'd be wary of the cost benefit of doing that so I'd be looking for a simple solution first.