Hi to all

I have to migrate a project to Hibernate 3. This project has a very particular legacy table. This table stores ranges. As you know, a range is represented as two numeric values, so a number X belongs to a range [A-B] if A <=X and X <= B.
The primary key of this table is, therefore, the values [A, B] of the range (two fields): the minimum range field and the maximum range field.

My problem begins when I want to check if a certain number belongs to a range. I really don't know how to make this in Hibernate. In SQL I make the following sentence:

SELECT * FROM RANGE_TABLE
WHERE MINIMUM_RANGE_FIELD <= <value>
AND MAXIMUM_RANGE_FIELD >= <value>
AND.....

I don't want to make a HQL query, because I want to use the second-level hibernate caching (the range table is very often used).

Is this possible???.

Lot of thanks in advance.

The second-level cache can be used to cache the result of queries (Query.setCacheable(true)), so if you don't have many different <value> values this could work. The query cache only caches the primary keys of the entities. If you want to cache the full Range objects you need to configure that as well.

This table stores information about credit-card ranges. So there are a lot of possible <value> values.
I think caching the full Range objects is very hard for the performance, because this table has a considerable number of rows. So I'll try using Query.setCacheable(true) and check the performance.