1

I am dealing with a legacy code that is 'not changeable' (no way to move to criteria api) and I have a little trouble with proper parameters binding. The query looks like this (MS SQL):

SELECT BRAND AS b FROM CAR WHERE (NAME LIKE :phrase OR MODEL LIKE :phrase ) AND AGE NOT IN(1997, 1998) AND (:mileage IS NULL OR MILEAGE LIKE :mileage) ORDER BY BRAND
(...)
    query.setParameter("phrase", "%" + phrase + "%");
    query.setParameter("mileage", mileage);

phrase is actually required, but because the mileageparameter is optional, it's done in wierd way presented above. The problem is that with both phrase and mileage provided, It keeps giving me following error : java.sql.SQLException: ResultSet may only be accessed in a forward direction.. It works wihtout mileage parameter provided. Why I am getting this error ?

EDITED:

  • Running this query on db gives me no results.
  • I am using query.setResultTransformer(Transformers.aliasToBean(type)) as well as setting first and max result on my SQLQuery query object.
  • An error is when calling query.list() (used intellij evaluate expression)

shouldn't query.list() return an empty result ?

Probable answer (in that case):

It looks like setting FirstResult on the query cause that problem because - by mistake - it's a negative number.

user3529850
  • 1,632
  • 5
  • 32
  • 51
  • Your SQL has `LIKE` keyword with `mileage` here `MILEAGE LIKE :mileage`, but you are not specifying `%` like you do with `phrase`. Is it done intentionally? – tsolakp Feb 02 '18 at 20:42
  • Yes, It is - 'cause I wanted it to be either a perfect match or not. – user3529850 Feb 02 '18 at 21:24

1 Answers1

0

How are you executing the SQL and then accessing the results?

It sounds like there are no results when executing the SQL with the mileage parameter and you are somehow trying to read the results anyway.

Try running the generated SQL immediately on the database and see if it returns any rows.

user39950
  • 83
  • 6
  • You are right. No rows returned when executed that query on db. The error is when I call `list()` : `query.list()`. I set debugger on that line and used intellij `Evaluate expression` and it gives me that exception. Shouldn't it return empty collection ? – user3529850 Feb 02 '18 at 21:23