in our current project we need a query of the long-matching-prefix type but the column was oddly enough defined as bigint. We came up with the solution in the snippet below, where we want to find the longest number in column dc that matches the parameter ?2 and ?3, and ?1 date is between the 2 date columns.
Here are the relevant dependencies:
- Postresql 8.4
- Hibernate 3.6.10
- Hibernate JPA 1.0.1
- Spring 3.2.1
Here is the source:
... String query_string = "SELECT * FROM numberlist WHERE " + "?1 BETWEEN init_date AND COALESCE(end_date,'2999-12-31') " + "AND dc IN " + "(select ?2/(10^i)::int8 from generate_series(0,floor(log(?3))::int) i) " + "ORDER BY dc DESC LIMIT 1"; Query query = emf.createEntityManager().createNativeQuery(query_string, MyNumber.class); query.setParameter(1, valid_date); query.setParameter(2, num); query.setParameter(3, num); MyNumber result = (MyNumber)query.getSingleResult(); ...
We are getting the following exception:
Caused by: org.hibernate.QueryException: Not all named parameters have been set: [:int8, :int]
We think the problem is Hibernate taking :int and :int8 as parameters but cannot find the solution.
Thank you for your help.