[ionEyes]

Hi there,

I have created a database with 2 tables, such that one links within a composite key to the other as follow:

Business (BusinessID<primary key auto-inc>) -> one-to-many -> jobs (BusinessID<foreign key>, JobsID<primary key auto-inc)

A Business can have many jobs so i have a Business table with a primary key of BusinessID and a jobs table with a composite primary key of BusinessID and JobID, with BusinessID as a foreign key created with the tooling (which is fantastic). I also used cascade on update and delete when I was generating the entities.
Here is the DDL for the jobs table so you can see the detail:
“”” (tooling created)

create table `test`.`job`(
`jobId` INT not null auto_increment,
`businessId` INT not null,
`description` VARCHAR(128) default ‘none’ not null,
primary key (`jobId`,`businessId`)
);

alter table `test`.`job`
add index `fromBusiness`(`businessId`),
add constraint `fromBusiness`
foreign key (`businessId`)
references `test`.`business`(`businessId`)
on delete cascade
on update cascade ;
create unique index `PRIMARY` on `test`.`job`(`jobId`,`businessId`);
create index `fromBusiness` on `test`.`job`(`businessId`);
“””

I can create a Business. I then find the Business and try to create a new job for it:
“”” (custom code)
// if business has required name
if (oBusiness.getName().equalsIgnoreCase(“myNewBusiness”)) {
System.out.println(“creating a job for business : ”
+ oBusiness.getName());

JobDAO oJobDAO = new JobDAO();
Job oJob = new Job();
oJob.setBusiness(oBusiness);
oJob.setDescription(“a new job description”);
oJobDAO.save(oJob);
}
“””

The code, inside a JUnit causes the following error:
Caused by: <openjpa-1.2.0-r422266:683325 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Field “Job.id” of “Job@18adae2” can not be set to “null” value.

As you can see from the Job DDL, JobId is an auto incrementing field. I get errors of a different kind if I try and set it manually, which I dont think should be done for auto-increment fields. This was the case when creating a new Business row in the Business table.

My question is, with creating a new object with a compound primary key, how should I do it ? I thought above would work ?

Should I not express the foreign key and manage the integrity myself ?

[Brian Fernandes]

ionEyes,
Before going into detail, for auto increment columns a @GeneratedValue annotation on the key is important. However, this is not always generated, depending on your database and driver – which database are you connected to? I’m assuming MySQL from your SQL – which version?

If you modify the generated Java code and add the @GeneratedValue(strategy=GenerationType.AUTO) it may work. For example

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="CUST_ID")
public Long getId() { 
        return id; 
    }

However, since you have a composite primary key (your code probably has @EmbeddedId, not @Id) where one column is auto increment, I’m not really this will work or if JPA will even support such a case; I could not find a way to specify additional detail to the @GeneratedValue annotation. Hope this helps.

[ionEyes]

Thanks very much for giving me hope Brian.

For now I have decided to manage the composite key manually by additional code, which is not that much. If this is DB dependant I could run into problems if I change DB’s for any reason, based on your reply. Its mySQL v5.1.33 at present.

Thank you again.

[Author]

Posts