Hibernate version: 3.1

Name and version of the database you are using: Oracle 9

Hi all,
My web application uses Mysql and runs fine.. but now i have to migrate it to oracle for some reason. for every table i have used <generator class="native"/> for Primary Key generation..
in mysql i have used auto-increment feature so i dont hv to worry about PK generation.. but for oracle , what should be my approach so that there are minimum changes done to the code ?

My goal is to make this application portable for any DB.. Specially, MySQL, Oracle and SQL server..

Please help me..

Thanks in advance,

Venkatesh

use this way

<hibernate-mapping>
<class name="....." table="TABLEXXX" schema="TEST_SCHEMA">
<id name="id" type="int">
<column name="ID" precision="22" scale="0" />
<generator class="sequence">
<param name="sequence">TABLEXXX_SEQ</param>
</generator>
</id>
..............
.....

greetings willi

Hi icoserve,
Thanks for reply..
but MySQL does not support sequence.. is there no other way, so that application will support Oracle, MYSQL or SQL server at the same time?

Hi,
May be you can try this.

<generator class="increment" />

OR

you can also try this

<generator class="hilo">
<param name="table">hibernate_unique_key</param>
<param name="column"> next_value</param>
<param name="max_lo">100</param>
</generator>

and create a table

CREATE TABLE HIBERNATE_UNIQUE_KEY
(
NEXT_VALUE INTEGER
)


Hope this helps!!!

PS: Please rate the posting if it proves to be useful

Thanx a lot falgunipm,
Using "hilo" solved my problem..

Hi,

If I have more than one table that need to use the hilo generation, I have to create a new 'hibernate_unique_key' table for each one of them?

Can I have an extra column with an identifier for each table?

Is that possible?

Thanks,
Cristiane

Hi,
Something like this is valid

CREATE TABLE HIBERNATE_UNIQUE_KEY
(
NEXT_VALUE INTEGER,
COL2 INTEGER
)

File1.hbm.xml
<id name="pk" type="int" unsaved-value="0" column="pk">
<generator class="hilo">
<param name="table">hibernate_unique_key</param>
<param name="column"> next_value</param>
<param name="max_lo">100</param>
</generator>
</id>

File2.hbm.xml
<id name="pk" type="int" unsaved-value="0" column="pk">
<generator class="hilo">
<param name="table">hibernate_unique_key</param>
<param name="column"> COL2</param>
<param name="max_lo">100</param>
</generator>
</id>

In short, you do not have to create separate tables. You can just create a single table having multiple columns which would be primary keys for different tables.

Regards,
falgunipm

Hi,

Thanks for the answer, but I'm afraid I cannot use it. Actually I have an existing table for id generation with the following columns:

IdTyp
NextNum
EndNum

In that way each table has it's own record. In your suggestion I will have only 1 record with multiple columns.

Can I extend the hilo generator class to adapt to my table structure?


Thanks,
Cristiane