Spring Roo
  1. Spring Roo
  2. ROO-187

Inconsistency in external JPA implementation causes duplicate entry for key

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0.RC2
    • Fix Version/s: 1.0.0.RC2
    • Component/s: PERSISTENCE
    • Labels:
      None
    • Environment:
      windows, mysql 5.0.27 community

      Description

      See forum topic.

        Activity

        Hide
        Stefan Schmidt added a comment -

        I have performed some tests to see the differences in how both Hibernate and OpenJPA build the database schema based on the same JPA-configured domain model:

        Hibernate schema:

        create table postfix (id bigint not null auto_increment, version integer, code varchar(255), primary key (id))
        create table prefix (id bigint not null auto_increment, version integer, code varchar(255), primary key (id))
        create table product (id bigint not null auto_increment, version integer, name varchar(255), productgroup bigint, primary key (id))
        create table productgroup (id bigint not null auto_increment, version integer, name varchar(255), primary key (id))
        create table productgroup_postfixes (productgroup bigint not null, postfixes bigint not null, primary key (productgroup, postfixes), unique (postfixes))
        create table productgroup_prefixes (productgroup bigint not null, prefixes bigint not null, primary key (productgroup, prefixes), unique (prefixes))
        alter table product add index FKED8DCCEF383043A4 (productgroup), add constraint FKED8DCCEF383043A4 foreign key (productgroup) references productgroup (id)
        alter table productgroup_postfixes add index FK68EAA854383043A4 (productgroup), add constraint FK68EAA854383043A4 foreign key (productgroup) references productgroup (id)
        alter table productgroup_postfixes add index FK68EAA8544D076214 (postfixes), add constraint FK68EAA8544D076214 foreign key (postfixes) references postfix (id)
        alter table productgroup_prefixes add index FK812B7B2F383043A4 (productgroup), add constraint FK812B7B2F383043A4 foreign key (productgroup) references productgroup (id)
        alter table productgroup_prefixes add index FK812B7B2F25922E16 (prefixes), add constraint FK812B7B2F25922E16 foreign key (prefixes) references prefix (id)

        OpenJPA schema:

        CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID TINYINT NOT NULL, SEQUENCE_VALUE BIGINT, PRIMARY KEY (ID)) TYPE = innodb
        CREATE TABLE Postfix (id BIGINT NOT NULL, code VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb
        CREATE TABLE Prefix (id BIGINT NOT NULL, code VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb
        CREATE TABLE Product (id BIGINT NOT NULL, name VARCHAR(255), version INTEGER, PRODUCTGROUP_ID BIGINT, PRIMARY KEY (id)) TYPE = innodb
        CREATE TABLE Productgroup (id BIGINT NOT NULL, name VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb
        CREATE TABLE Productgroup_Postfix (PRODUCTGROUP_ID BIGINT, POSTFIXES_ID BIGINT) TYPE = innodb
        CREATE TABLE Productgroup_Prefix (PRODUCTGROUP_ID BIGINT, PREFIXES_ID BIGINT) TYPE = innodb
        CREATE INDEX I_PRODUCT_PRODUCTGROUP ON Product (PRODUCTGROUP_ID)
        CREATE INDEX I_PRDCTFX_ELEMENT ON Productgroup_Postfix (POSTFIXES_ID)
        CREATE INDEX I_PRDCTFX_PRODUCTGROUP_ID ON Productgroup_Postfix (PRODUCTGROUP_ID)
        CREATE INDEX I_PRDCRFX_ELEMENT ON Productgroup_Prefix (PREFIXES_ID)
        CREATE INDEX I_PRDCRFX_PRODUCTGROUP_ID ON Productgroup_Prefix (PRODUCTGROUP_ID)

        You can see that Hibernate drops in a Unique constraint for the productgroup_prefixes and productgroup_postfixes tables which causes the issue:

        create table productgroup_postfixes (productgroup bigint not null, postfixes bigint not null, primary key (productgroup, postfixes), unique (postfixes))
        create table productgroup_prefixes (productgroup bigint not null, prefixes bigint not null, primary key (productgroup, prefixes), unique (prefixes)) (productgroup bigint not null, postfixes bigint not null, primary key (productgroup, postfixes), unique (postfixes))
        create table productgroup_prefixes (productgroup bigint not null, prefixes bigint not null, primary key (productgroup, prefixes), unique (prefixes))

        Will have to review the spec a little closer to see if this is correct.

        -Stefan

        Show
        Stefan Schmidt added a comment - I have performed some tests to see the differences in how both Hibernate and OpenJPA build the database schema based on the same JPA-configured domain model: Hibernate schema: create table postfix (id bigint not null auto_increment, version integer, code varchar(255), primary key (id)) create table prefix (id bigint not null auto_increment, version integer, code varchar(255), primary key (id)) create table product (id bigint not null auto_increment, version integer, name varchar(255), productgroup bigint, primary key (id)) create table productgroup (id bigint not null auto_increment, version integer, name varchar(255), primary key (id)) create table productgroup_postfixes (productgroup bigint not null, postfixes bigint not null, primary key (productgroup, postfixes), unique (postfixes)) create table productgroup_prefixes (productgroup bigint not null, prefixes bigint not null, primary key (productgroup, prefixes), unique (prefixes)) alter table product add index FKED8DCCEF383043A4 (productgroup), add constraint FKED8DCCEF383043A4 foreign key (productgroup) references productgroup (id) alter table productgroup_postfixes add index FK68EAA854383043A4 (productgroup), add constraint FK68EAA854383043A4 foreign key (productgroup) references productgroup (id) alter table productgroup_postfixes add index FK68EAA8544D076214 (postfixes), add constraint FK68EAA8544D076214 foreign key (postfixes) references postfix (id) alter table productgroup_prefixes add index FK812B7B2F383043A4 (productgroup), add constraint FK812B7B2F383043A4 foreign key (productgroup) references productgroup (id) alter table productgroup_prefixes add index FK812B7B2F25922E16 (prefixes), add constraint FK812B7B2F25922E16 foreign key (prefixes) references prefix (id) OpenJPA schema: CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID TINYINT NOT NULL, SEQUENCE_VALUE BIGINT, PRIMARY KEY (ID)) TYPE = innodb CREATE TABLE Postfix (id BIGINT NOT NULL, code VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb CREATE TABLE Prefix (id BIGINT NOT NULL, code VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb CREATE TABLE Product (id BIGINT NOT NULL, name VARCHAR(255), version INTEGER, PRODUCTGROUP_ID BIGINT, PRIMARY KEY (id)) TYPE = innodb CREATE TABLE Productgroup (id BIGINT NOT NULL, name VARCHAR(255), version INTEGER, PRIMARY KEY (id)) TYPE = innodb CREATE TABLE Productgroup_Postfix (PRODUCTGROUP_ID BIGINT, POSTFIXES_ID BIGINT) TYPE = innodb CREATE TABLE Productgroup_Prefix (PRODUCTGROUP_ID BIGINT, PREFIXES_ID BIGINT) TYPE = innodb CREATE INDEX I_PRODUCT_PRODUCTGROUP ON Product (PRODUCTGROUP_ID) CREATE INDEX I_PRDCTFX_ELEMENT ON Productgroup_Postfix (POSTFIXES_ID) CREATE INDEX I_PRDCTFX_PRODUCTGROUP_ID ON Productgroup_Postfix (PRODUCTGROUP_ID) CREATE INDEX I_PRDCRFX_ELEMENT ON Productgroup_Prefix (PREFIXES_ID) CREATE INDEX I_PRDCRFX_PRODUCTGROUP_ID ON Productgroup_Prefix (PRODUCTGROUP_ID) You can see that Hibernate drops in a Unique constraint for the productgroup_prefixes and productgroup_postfixes tables which causes the issue: create table productgroup_postfixes (productgroup bigint not null, postfixes bigint not null, primary key (productgroup, postfixes), unique (postfixes)) create table productgroup_prefixes (productgroup bigint not null, prefixes bigint not null, primary key (productgroup, prefixes), unique (prefixes)) (productgroup bigint not null, postfixes bigint not null, primary key (productgroup, postfixes), unique (postfixes)) create table productgroup_prefixes (productgroup bigint not null, prefixes bigint not null, primary key (productgroup, prefixes), unique (prefixes)) Will have to review the spec a little closer to see if this is correct. -Stefan
        Hide
        Stefan Schmidt added a comment -

        Script to replicate

        After executing the script, run mvn tomcat:run, and create a prefix a and a postfix, then create two productgroups.

        Show
        Stefan Schmidt added a comment - Script to replicate After executing the script, run mvn tomcat:run, and create a prefix a and a postfix, then create two productgroups.
        Show
        Stefan Schmidt added a comment - http://opensource.atlassian.com/projects/hibernate/browse/HHH-3410
        Hide
        Ben Alex added a comment -

        SVN revision 294 contains the required fix.

        Essentially @ManyToMany needs to be used instead of @ManyToOne. The "field set" command now accepts a cardinality option to allow this to be specified. The clinic.roo script demonstrates this:

        field set --class ~.domain.Vet --fieldName specialties --element ~.reference.Specialty --cardinality MANY_TO_MANY --notNull false

        Automatic JUnit tests pass. Also, a specific hand-coded test also passes:

        package com.springsource.petclinic.domain;
        
        import java.util.Date;
        import java.util.HashSet;
        import java.util.Set;
        
        import junit.framework.Assert;
        
        import org.springframework.roo.addon.test.RooIntegrationTest;
        import org.springframework.transaction.annotation.Transactional;
        
        import com.springsource.petclinic.domain.Vet;
        import com.springsource.petclinic.reference.Specialty;
        
        import org.junit.Test;
        
        @RooIntegrationTest(entity = Vet.class)
        public class VetIntegrationTest {
        
            @Test
            @Transactional
            public void test() {
            	Vet v1 = makeVet();
            	Vet v2 = makeVet();
        
            	Specialty s1 = new Specialty();
            	s1.setName("Radiology");
            	s1.persist();
            	Specialty s2 = new Specialty();
            	s2.setName("Dentistry");
            	s2.persist();
            	Specialty s3 = new Specialty();
            	s3.setName("Crygogenics");
            	s3.persist();
            	
            	Set<Specialty> group1 = new HashSet<Specialty>();
            	group1.add(s1);
            	group1.add(s2);
            	
            	Set<Specialty> group2 = new HashSet<Specialty>();
            	group2.add(s1);
            	group2.add(s2);
            	group2.add(s3);
            	
            	v1.setSpecialties(group1);
            	v2.setSpecialties(group2);
        
            	v1.persist();
            	v2.persist();
            	
            	v1 = Vet.findVet(v1.getId());
            	v2 = Vet.findVet(v2.getId());
            	
            	Assert.assertEquals(2, v1.getSpecialties().size());
            	Assert.assertEquals(3, v2.getSpecialties().size());
            }
            
            private Vet makeVet() {
            	Vet v = new Vet();
            	v.setAddress("123 Smith St");
            	v.setBirthDay(new Date());
            	v.setCity("Somewhere");
            	v.setEmail("dcd@dc.com");
            	v.setEmployedSince(new Date());
            	v.setFirstName("Bob");
            	v.setHomePage("www.avet.com");
            	v.setLastName("Lastie");
            	v.setTelephone(12345);
            	return v;
            }
        }
        

        The web tier appears to have an issue, though, when listing the specialties on the Vet page. I'll bring this up with Stefan now.

        Show
        Ben Alex added a comment - SVN revision 294 contains the required fix. Essentially @ManyToMany needs to be used instead of @ManyToOne. The "field set" command now accepts a cardinality option to allow this to be specified. The clinic.roo script demonstrates this: field set --class ~.domain.Vet --fieldName specialties --element ~.reference.Specialty --cardinality MANY_TO_MANY --notNull false Automatic JUnit tests pass. Also, a specific hand-coded test also passes: package com.springsource.petclinic.domain; import java.util.Date; import java.util.HashSet; import java.util.Set; import junit.framework.Assert; import org.springframework.roo.addon.test.RooIntegrationTest; import org.springframework.transaction.annotation.Transactional; import com.springsource.petclinic.domain.Vet; import com.springsource.petclinic.reference.Specialty; import org.junit.Test; @RooIntegrationTest(entity = Vet.class) public class VetIntegrationTest { @Test @Transactional public void test() { Vet v1 = makeVet(); Vet v2 = makeVet(); Specialty s1 = new Specialty(); s1.setName( "Radiology" ); s1.persist(); Specialty s2 = new Specialty(); s2.setName( "Dentistry" ); s2.persist(); Specialty s3 = new Specialty(); s3.setName( "Crygogenics" ); s3.persist(); Set<Specialty> group1 = new HashSet<Specialty>(); group1.add(s1); group1.add(s2); Set<Specialty> group2 = new HashSet<Specialty>(); group2.add(s1); group2.add(s2); group2.add(s3); v1.setSpecialties(group1); v2.setSpecialties(group2); v1.persist(); v2.persist(); v1 = Vet.findVet(v1.getId()); v2 = Vet.findVet(v2.getId()); Assert.assertEquals(2, v1.getSpecialties().size()); Assert.assertEquals(3, v2.getSpecialties().size()); } private Vet makeVet() { Vet v = new Vet(); v.setAddress( "123 Smith St" ); v.setBirthDay( new Date()); v.setCity( "Somewhere" ); v.setEmail( "dcd@dc.com" ); v.setEmployedSince( new Date()); v.setFirstName( "Bob" ); v.setHomePage( "www.avet.com" ); v.setLastName( "Lastie" ); v.setTelephone(12345); return v; } } The web tier appears to have an issue, though, when listing the specialties on the Vet page. I'll bring this up with Stefan now.
        Hide
        Ben Alex added a comment -

        SVN revision 298 now has specialties included in the web stack for clinic.roo.

        The web tier has been confirmed to now work with many-to-many for clinic.roo's specialties.

        Show
        Ben Alex added a comment - SVN revision 298 now has specialties included in the web stack for clinic.roo. The web tier has been confirmed to now work with many-to-many for clinic.roo's specialties.
        Hide
        Ben Alex added a comment -

        Another test, this time showing pet storage against owners work:

            @Test
            @Transactional
            public void testPetStorageAgainstOwner() {
            	Owner o = new Owner();
            	o.setAddress("22");
            	o.setBirthDay(new Date());
            	o.setCity("dcdc");
            	o.setEmail("vfvf@dc.com");
            	o.setFirstName("fvdv");
            	o.setHomePage("dcdc.com");
            	o.setLastName("sxscxs");
            	o.setTelephone(1212);
            	o.persist();
            	
            	PetType rat = new PetType();
            	rat.setType("Rat");
            	rat.persist();
            	
            	PetType dog = new PetType();
            	dog.setType("Dog");
            	dog.persist();
            	
            	Pet p1 = new Pet();
            	p1.setName("sniffy");
            	p1.setOwner(o);
            	p1.setSendReminders(false);
            	p1.setType(rat);
            	p1.setWeight(1.1f);
            	p1.persist();
            	
            	Pet p2 = new Pet();
            	p2.setName("tara");
            	p2.setOwner(o);
            	p2.setSendReminders(false);
            	p2.setType(dog);
            	p2.setWeight(40.3f);
            	p2.persist();
            	
            	Set<Pet> pets = new HashSet<Pet>();
            	pets.add(p1);
            	pets.add(p2);
            	
            	o.setPets(pets);
            	o.flush();
            	
            	Owner read = Owner.findOwner(o.getId());
            	Assert.assertEquals(2, read.getPets().size());
            }
        
        Show
        Ben Alex added a comment - Another test, this time showing pet storage against owners work: @Test @Transactional public void testPetStorageAgainstOwner() { Owner o = new Owner(); o.setAddress( "22" ); o.setBirthDay( new Date()); o.setCity( "dcdc" ); o.setEmail( "vfvf@dc.com" ); o.setFirstName( "fvdv" ); o.setHomePage( "dcdc.com" ); o.setLastName( "sxscxs" ); o.setTelephone(1212); o.persist(); PetType rat = new PetType(); rat.setType( "Rat" ); rat.persist(); PetType dog = new PetType(); dog.setType( "Dog" ); dog.persist(); Pet p1 = new Pet(); p1.setName( "sniffy" ); p1.setOwner(o); p1.setSendReminders( false ); p1.setType(rat); p1.setWeight(1.1f); p1.persist(); Pet p2 = new Pet(); p2.setName( "tara" ); p2.setOwner(o); p2.setSendReminders( false ); p2.setType(dog); p2.setWeight(40.3f); p2.persist(); Set<Pet> pets = new HashSet<Pet>(); pets.add(p1); pets.add(p2); o.setPets(pets); o.flush(); Owner read = Owner.findOwner(o.getId()); Assert.assertEquals(2, read.getPets().size()); }

          People

          • Assignee:
            Ben Alex
            Reporter:
            Ronald Vermeire
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: