need an example which support mysql and oracle both

Question

Possible Duplicate:
multiple database support for same JPA classs

I have a table

Users( id , name);

Now i want to create the JPA class for the table so that it can support both database.

the id should be auto increment.

Kindly help me out in achieving same by providing some example.

Thanks in advance.

What have *you* tried? Where are you getting stuck? What issues have you run into? — T.J. Crowder, Jul 23 '12 at 11:55
This is possibly one of the reasons why I personally don't like to use annotations with Hibernate... Finally you end up modifying source code instead of config files, when it comes to changing the DB. — Less, Jul 23 '12 at 11:57
You just write one simple hbm file and Pojo to your table.just change dialect ralated to your DB it will works fine for both databases. — NPKR, Jul 23 '12 at 11:58
@T.J.Crowder: we were having application which was supporting MYSQL and the primary key in db is auto_increment. Now oracle supports sequences and now we unable to figure out what could be the way that with same pojo we support both DB(ORACLE and MYSQL). — Zuned Ahmed, Jul 23 '12 at 12:00
Since I use Hibernate with Oracle, I think the hbm configuration cannot stay the same. Oracle sequence is not the same as Mysql indentity, or autoincrement whatever, as far as I know — Less, Jul 23 '12 at 12:01
@Pradeep: i know how to configure mybatis , the issue is brother how to run primary key auto increment on both db. — Zuned Ahmed, Jul 23 '12 at 12:03
Please refer this question for more detail : http://stackoverflow.com/questions/11611046/multiple-database-support-for-same-jpa-classs/11611269#11611269 — Zuned Ahmed, Jul 23 '12 at 12:03
@Zuned Ahmed so, you didn't find the answer in the link useful ? Have you tried the AUTO strategy? — Less, Jul 23 '12 at 12:09
Didn't you asked the same question 5 minutes prior to this one? I'm referring to http://stackoverflow.com/questions/11611046/multiple-database-support-for-same-jpa-classs — Olaf, Jul 23 '12 at 12:50

Mikko Maunu · Answer 1 · 2012-07-23T13:26:13.653

Just go directly to the GenerationType.TABLE, which is most portable. It does not depend that much about database specifics, because incrementing value is done via SQL. Also I find it more suitable than AUTO, because same generation type will be used independently from database provider. You can also use it without TableGenerator, but because our goal is to have it to function exactly same way with all the databases, we are explicitly giving needed values.

In your case mappings are:

@Entity
@TableGenerator(
    name="usersGenerator",
    table="ID_GENERATOR",
    pkColumnName="GENERATOR_KEY",
    valueColumnName="GENERATOR_VALUE",
    pkColumnValue="USERS_ID",
    initialValue = 1,
    allocationSize=1)
public class Users {
    @Id
    @GeneratedValue(strategy= GenerationType.TABLE, 
                    generator = "usersGenerator")
    private Integer value;

    private String name;

    protected Integer getValue() {
        return value;
    }

    protected void setValue(Integer value) {
        this.value = value;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Same database table (ID_GENERATOR in this case) can be used by multiple table generators. If needed, for example because of type of id, same table can store multiple pk and value columns.

Name of TableGenerator is global for persistence unit, as generator names in general are. If wished, annotation can be also located to id attribute.

Possible caveat: If I remember right, some hibernate version combinations do not support initial value. That is in general case not limiting portability. It is problem only if we have to autogenerate tables and reproduce exactly same set of id values. Workaround is to manually insert initial value after table is constructed.

NPKR · Answer 2 · 2012-07-23T12:33:25.513

Generator Type

Increment

This generator supports in all the databases, database independent
This generator is used for generating the id value for the new record by using the

sequence

Not has the support with MySql
This generator class is database dependent it means, we cannot use this generator class for all the database, we should know whether the database supports sequence or not before we are working with it

Reffer this link, it might helpful to you.

http://www.java4s.com/hibernate/generators-in-hibernate/

score 0 · Accepted Answer · answered Jul 23 '12 at 12:25

You should pick generation type AUTO. I only used .hbm mappings, but my understanding that with annotations it should look something like that:

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private long id;

need an example which support mysql and oracle both

3 Answers3