Hello All,

I need help with writing a query in Hibernate.

The DDL for the table i am using is given below :-
CREATE TABLE FRENCH_BATCH_DETAILS
(
BATCH_ID NUMBER PK,
BATCH_STARTDATE DATE NOT NULL,
BATCH_ENDDATE DATE,
BATCH_NAME NVARCHAR2(150),
)

The above table stores Details of French Batches for a particular Institute.
Sample Data is given below :-
BATCH_ID BATCH_STARTDATE BATCH_ENDDATE BATCH_NAME
1 9/25/2008 10/2/2008 BATCH_1
2 9/29/2008 10/10/2008 BATCH_2
3 10/5/2008 10/15/2008 BATCH_3
4 10/10/2008 10/25/2008 BATCH_4
5 10/20/2008 10/30/2008 BATCH_5
6 10/25/2008 11/5/2008 BATCH_6
7 11/1/2008 11/10/2008 BATCH_7
8 11/11/2008 11/21/2008 BATCH_8

-> The Batch End Date will always be greater than Batch Start Date
-> Batches are not added into the table in any particular ascending order.
-> Two batches may or may not be overlapping.

The functional query to be written is as below :-
If I have student_start_date = 10/10/2008

I need to search for the first Batch_StartDate >= Student_START_DATE
For 10/10/2008, batch_4 is the current batch we need.
Now we need to fetch start date and end date of two batches above this and two batches below this.
I need to display on page in a JSF datatable the start date and end date of 5 batches in total - current batch, prev two batches and next two batches.


Hibernate version: 3.2.2

Name and version of the database you are using: Oracle 10g