0

I have a method in my DAO which is querying a Oracle database and it is working fine in the application. However for testing we are using an Hsqldb and using Spring-junit for the tests. The same method returns an error during the tests because as far I have seen HSQL does not support subqueries so I'm getting:

Caused by: org.hsqldb.HsqlException: unexpected token: START required: )

Could you help me about how to proceed in this case? Could be possible to mock this method using Spring to not really do the call but give me some predefined result by configuration?

Any advice will be very welcome!

Here is the method:

private Long getRootParent(Long id) {
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("SELECT ID FROM ");
    sqlQuery.append(" ( SELECT MAX(level) , ff.ID FROM FOREF_FUND ff ");
    sqlQuery.append(" START WITH ff.ID = ? ");
    sqlQuery.append(" CONNECT BY PRIOR ff.PARENT_FK = ff.ID ");
    sqlQuery.append(" GROUP BY ff.ID  ORDER BY MAX (level) DESC ) ");
    sqlQuery.append(" where rownum = 1 ");

    SQLQuery query = this.createSQLQuery(sqlQuery.toString());
    query.setParameter(0, id);

    List result = query.list();
    if (result != null && !result.isEmpty()) {
        return ((BigDecimal) result.get(0)).longValue();
    }
    return null;
}
fredt
  • 24,044
  • 3
  • 40
  • 61
fgonzalez
  • 3,787
  • 7
  • 45
  • 79
  • HSQLDB does not support `CONNECT BY`. Another example on why it is a bad idea to use a different DBMS for unit testing... –  Jul 08 '15 at 15:15
  • I agree but I found it like this. Any idea? (without changing the testing db of course) – fgonzalez Jul 08 '15 at 15:18

0 Answers0