Select one row only from HSQLDB with Oracle dialect

Question

As the title suggests I need to select one row (no matter what is) from my query. ROWNUM doesn't work in HSQLDB as there is ROWNUM() function. What's more, setting ORA dialect both in connection url settings and in schema-hsqldb.sql doesn't work (it should but it doesn't, I don't know why). Query is fully working in Oracle.

Any suggestions? Thank you very much for any!

Answer 1

After about three days of searching, crying and thinking of committing suicide I've found that in my case (I have fairly complex SQL) Hibernate prefixes ROWNUM with THIS_ thinking it is a column. I've added the following custom dialect resolver and now it works:

public class RownumStandardDialectResolver extends StandardDialectResolver {

    @Override
    public Dialect resolveDialect(DialectResolutionInfo info) {
        Dialect dialect = super.resolveDialect(info);
        dialect.getKeywords().add("rownum");

        return dialect;
    }

}