ManyToOne relation with composite keys

[Kulbhushan Sharma]

Hello,

I am having problems with JPA using Toplink. I have used the default generated code for entities with NetBeans 6.1.

There are three tables defined in MySQL as

CREATE TABLE `files_on_server` (
  `FILE_ID` bigint(20) NOT NULL,
  `AUTO_UID` int(11) NOT NULL,
  `FILENAME` varchar(50) character set utf8 NOT NULL,
  `FILESIZE` decimal(20,0) NOT NULL,
  `PARENT_ID` int(11) NULL,
  PRIMARY KEY  (`FILE_ID`,`AUTO_UID`),
  KEY `FKP_files_on_server` (`AUTO_UID`,`PARENT_ID`),
  CONSTRAINT `FK_files_on_server` FOREIGN KEY (`AUTO_UID`, `PARENT_ID`) REFERENCES `folder_details` (`AUTO_UID`, `FOLDER_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*Table structure for table `folder_details` */
 
DROP TABLE IF EXISTS `folder_details`;
 
CREATE TABLE `folder_details` (
  `AUTO_UID` int(11) NOT NULL,
  `FOLDER_ID` int(11) NOT NULL,
  `FOLDER_NAME` varchar(50) character set utf8 NOT NULL,
  `PARENT_ID` int(11) default NULL,
  PRIMARY KEY  (`AUTO_UID`,`FOLDER_ID`),
  CONSTRAINT `FK_folder_details` FOREIGN KEY (`AUTO_UID`) REFERENCES `users` (`AUTO_UID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*Table structure for table `users` */
 
DROP TABLE IF EXISTS `users`;
 
CREATE TABLE `users` (
  `AUTO_UID` int(11) NOT NULL auto_increment,
  `EMAIL_ID` varchar(50) character set utf8 NOT NULL,
  `FIRSTNAME` varchar(25) character set utf8 NOT NULL,
  `LASTNAME` varchar(25) character set utf8 NOT NULL,
  PRIMARY KEY  (`AUTO_UID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

The entities generated lead rightly to Composite Keys with @Embedded and EmbeddedID annotations for the tables FilesOnServer and FolderDetails with additional entities FilesOnServerPK FolderDetailsPK .

I persist the relationship (between the FilesOnServer and FolderDetails entities with the code below

{ /********   Find the object ***************/
 FolderDetailsPK obj=new FolderDetailsPK();
        obj.setAutoUid(auto_uid);
        obj.setFolderId(parentId);
        FolderDetails obj2=em.find(FolderDetails.class,obj);
 
  /**************
         NOTE: The object is found correctly ***********/
 
/*************** Now create a new FilesOnServer object *******/
       FilesOnServerPK filesOnServerPK=new FilesOnServerPK();
        filesOnServerPK.setAutoUid(auto_uid);
        filesOnServerPK.setFileId(file_id);
        filesOnServer.setFilesOnServerPK(filesOnServerPK);
        filesOnServer.setFilename(fileName);
       
        filesOnServer.setFolderDetails(obj2);
        
 
        em.persist(filesOnServer);
        
       /****** I have also tried the below statements in case the relationship is not set up correctly *****/
        //obj2.getFilesOnServerCollection().add(filesOnServer);
        
        //obj2 = em.merge(obj2);
        em.flush();

The FileOnServer entity is persisted without error but the PARENT_ID field remains null when I was expecting it to be updated with the FOLDER_ID field from the FolderDetails Table/entity.


In the log the following sql command appears
INSERT INTO files_on_server (FILENAME, FILESIZE, FILE_ID, AUTO_UID) VALUES (?, ?, ?, ?)
bind => [n0n0n.txt, 51, 1001, 1]

The thing I noted it that there is no refernce to the PARENT_ID column here...


Any ideas what I may be doing wrong?

Thanks

[Kulbhushan Sharma]

No response.

The workaround we use is to expand the primary key for FilesOnServer to also include the ParentID which works but is a hack.