We are using Oracle 8i database with Oracle JDBC Driver.

1.We are converting ERP project (which is in D2K and Oracle ) in Web based technology using Struts and JSP.
In ERP,database logic( like Execute MRP , Bill processing , Voucher creation , Financial Month closure , Stock closure.)
is much more in Oracle Package , Triggers and Views.
In ERP,we have different types of forms like
1.Master form - In this,we want the functionality like we can show only 10 rows at a time on a form and
we can move these records at first , forward , backward and last.Also we can search into the ResultSet.
We have already achieved this in Struts and Java JDBC Driver.
2.Master Detail form - In this,one master record have multiple child records.
3.Master Detail-Detail - In this,one master record have multiple child records and again for each of these child record have multiple child records.
E.g. Purchase Order - PO have multiple items and items have multiple charges.
We have done this functionality using XML document with replacing Item node with it's child nodes.
Is there any better solution to achieve this functionality?

Can we use Hibernate technology to achieve the above functionality?

2. We are trying to use Hibernate technology but we are facing some issues regarding hibernate Mapping of file.

I am using one table departments :-

company_code not null varchar2(6)
division_code not null varchar2(6)
department_code not null varchar2(6)
insert_user_id not null varchar2(30)
insert_date not null date
description varchar2(60)
update_user_id varchar2(30)
update_date date
department_type not null char(1)
replenishment_ind char(1)
parent_department_code varchar2(6)
actual_department_flag not null char(1)

I have defined the bean with following properties :-

private String rowId ;
private String rowStatus ;
private String companyCode ;
private String divisionCode ;
private String departmentCode ;
private String insertUserId ;
private String insertDate ;
private String description ;
private String updateUserId ;
private String updateDate ;
private String departmentType ;
private String replenishmentInd ;
private String parentDepartmentCode ;
private String actualDepartmentFlag ;


Following is a Hibernate mapping file :-

<class
name="erp.forms.global.bean.DepartmentBean"
table="departments">

<id
name="rowId"
type="java.lang.String"/>

<property
name="companyCode"
type="java.lang.String"
column="COMPANY_CODE"
not-null="true"
length="6"/>

<property
name="divisionCode"
type="java.lang.String"
column="DIVISION_CODE"
not-null="true"
length="6"/>

<property
name="departmentCode"
type="java.lang.String"
column="DEPARTMENT_CODE"
not-null="true"
length="6"/>

<property
name="insertUserId"
type="java.lang.String"
column="INSERT_USER_ID"
not-null="true"
length="30"/>

<property
name="insertDate"
type="java.lang.String"
column="INSERT_DATE"
not-null="true"
length="60"/>

<property
name="description"
type="java.lang.String"
column="DESCRIPTION"
not-null="false"
length="60"/>

<property
name="updateUserId"
type="java.lang.String"
column="UPDATE_USER_ID"
not-null="false"
length="30"/>

<property
name="updateDate"
type="java.lang.String"
column="UPDATE_DATE"
not-null="false"
length="60"/>

<property
name="departmentType"
type="java.lang.String"
column="DEPARTMENT_TYPE"
not-null="true"
length="1"/>

<property
name="replenishmentInd"
type="java.lang.String"
column="REPLENISHMENT_IND"
not-null="false"
length="1"/>

<property
name="parentDepartmentCode"
type="java.lang.String"
column="PARENT_DEPARTMENT_CODE"
not-null="false"
length="6"/>

<property
name="actualDepartmentFlag"
type="java.lang.String"
column="ACTUAL_DEPARTMENT_FLAG"
not-null="true"
length="1"/>

</class>

1. Whenever I am trying to insert the object using session.save( department_bean );
rowid also get inserted in the table.But I dont want that to be happened.

Following is the Hibernate log genrated at the time of insertion :-

insert into departments (COMPANY_CODE, DIVISION_CODE, DEPARTMENT_CODE, INSERT_USER_ID, INSERT_DATE, DESCRIPTION, UPDATE_USER_ID, UPDATE_DATE, DEPARTMENT_TYPE, REPLENISHMENT_IND, PARENT_DEPARTMENT_CODE, ACTUAL_DEPARTMENT_FLAG, rowId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
15381 [http-8080-Processor4] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1747, SQLState: 42000
15382 [http-8080-Processor4] ERROR org.hibernate.util.JDBCExceptionReporter - ORA-01747: invalid user.table.column, table.column, or column specification

Here, in above log rowId is also get inserted in the table bcoz we are passing the complete bean object for insertion.
But we dont want rowId to be inserted in the table.
The command must be generate like this :-

insert into departments (COMPANY_CODE, DIVISION_CODE, DEPARTMENT_CODE, INSERT_USER_ID, INSERT_DATE, DESCRIPTION, UPDATE_USER_ID, UPDATE_DATE, DEPARTMENT_TYPE, REPLENISHMENT_IND, PARENT_DEPARTMENT_CODE, ACTUAL_DEPARTMENT_FLAG ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

2. When I am trying to update the data then it gets updated properly with the data
like

Hibernate: update departments set COMPANY_CODE=?, DIVISION_CODE=?, DEPARTMENT_CODE=?, INSERT_USER_ID=?, INSERT_DATE=?, DESCRIPTION=?, UPDATE_USER_ID=?, UPDATE_DATE=?, DEPARTMENT_TYPE=?, REPLENISHMENT_IND=?, PARENT_DEPARTMENT_CODE=?, ACTUAL_DEPARTMENT_FLAG=? where rowId=?

3. Also I have a problem with Mapping XML file.
a) When I am trying to add any new property like "rowStatus" that belongs to DepartmentBean in mapping file,
then Hibernate gets initialised and "rowstatus" column automatically get inserted in the Table.
I have defined property in XML file like
<property name="rowStatus" />
Is there any other way to define the property which may or may not related to the Database.

4. When I am selecting data from database with following lines of code,
List department_list = session.createQuery(" from DepartmentBean department").list();
then I also want to set rowStatus property of bean to "E" (i.e. status is Existing ).
In this case, property "rowStatus" is not belongs to Database table.

Also, any one column having null values then it will return "null" string in bean property. I dont want that.
I have achievee this null string problem using Oracle "nvl" function but then Java SQL and Hibernate SQL has no difference.

How can I achieve this functionality?

List departments = session.createQuery(" from DepartmentBean department ").list();

for( Iterator itr = departments.iterator(); itr.hasNext(); )
{
DepartmentBean department_bean = (DepartmentBean)itr.next() ;
department_bean.setRowStatus("E");
}

This code directly changes in the List of departments.

This is right or not or any other solution exists.
5. How to create property in which we want to define one or more primary key values?