Sequence in Mysql
546330Nov 15 2006 — edited Apr 14 2008As 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