Skip to Main Content

Integration

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Sequence in Mysql

546330Nov 15 2006 — edited Apr 14 2008
As we know, Mysql has no sequence object.
I chose the Native Sequence in my whole top link project.

As below, if we create a Dept object and commit.
Dept dept = new Dept();
uow.registerObject(dept);
uow.commit();

It would generate the sql statment like this.
insert into dept values (null);
or
insert into dept values (0);

That's no problem, cause I have set the primary key as auto_increment in the table at mysql database, so the mysql would generate a new id for the new records.

In 1:M relationship, e.g
Dept dept = new Dept();
Emp emp = new Emp();
dept.getEmps().add(emp);
uow.commit();
uow.registerObject(dept);

It would generate 2 sql statment
insert into dept values (null); //Mysql return new id as "3"
insert into emp (empid,deptid) values(null,null); //Here would not use id 3 as the deptid at the foreign key in the database.

So to fix this problem, I try to preassign the id to my objects, however I believe it's the bad way.
Dept dept = new Dept();
dept.setId(3);
Emp emp = new Emp();
dept.getEmps().add(emp);
uow.commit();
uow.registerObject(dept);

It would generate 2 sql
insert into dept values (3); //Not depends on mysql autoincrement
insert into emp (empid,deptid) values(null,3); //Here would use id 3 as the deptid

Any way to fix this problem?

Software:
TopLink 10.1.3
Mysql 5.0
JDK 1.5 Path 7
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 12 2008
Added on Nov 15 2006
4 comments
2,356 views