Database independent beans.

[chatbuilder]

Hi,

I'm creating an application with CMP beans, that runs on JBoss. I want to make my beans database independent. So that our users can easly change from database version. (Oracle, MySQL, MS server, ...). But in fact I'm wondering if it is possible. Because ever table has a primary key, and to optimize my finders I want to auto create my primary key. In database terms I want to use auto_increments / sequences.

When I want to create an auto_increment in MySQL I only need to fill my id with an integer(0).

ebjCreate()..{
setId(new Integer(0));
}

When I want to create an auto_increment / sequence in Oracle in need to ask oracle first for the new sequence of a table.

ejbCreate()..{
Connection connection = // get connection //
this.setId(new Integer(JDBC.nextValueOf("database.table_SEQ1",connection)));
}

As you can see, there is a difference between an auto_increment in MySQL and a sequence in Oracle. My question is now if there is a uniform procedure to create my primary key. So that my beans are database independent, and that I only need to change my datasource xml file to change from database.

Thanks in advance,
Pieter

[triathlon98]

Combining database independence and auto_increment is very difficult.

Your best bet is not to use auto increment, but instead either calculate the keys yourself, or use one of the JBoss generators (like to UUID one). Alternatively, you could write your own genertor for this.

Joachim