 |
Using Composite Keys with JPA
Posted by bleonard on November 29, 2006 at 09:14 AM | Comments (14)
The Java Persistence API is beautiful. I create a class, annotate it with @Entity and @Id and I'm on my way. That is, however, assuming the Id is of a simple type (I'm a firm believer that primary keys should be of a simple type, unrelated to the data they index.). However, if you find yourself in a situation where you must use a composite key (hopefully this is only because you're connecting to some existing data set), JPA will still work for you, although it gets a bit more complex. First of all, you can declare a composite primary key using one of two annotations: @IdClass or @EmbeddedId. In this blog, I'm going to focus on the latter.
Using @EmbeddedId
A composite primary key must be represented by a primary key class. And when using the @EmbeddedId approach, it must be annotated as @Embeddable.
@Embeddable
public class ContactPK implements Serializable {
@Column(name = "FIRSTNAME", nullable = false)
private String firstname;
@Column(name = "LASTNAME", nullable = false)
private String lastname;
...
The primary key in the entity class is annoated with @EmbeddedId annotation:
@Entity
public class Contact implements Serializable {
@EmbeddedId
protected ContactPK contactPK;
...
However, it doesn't stop there. The entity class must also implement the hashcode() and equals() methods. As I mentioned in my introduction, hopefully you're only using composite keys if you already have existing tables defined that way. In the spirit of that assumption, we're going to start with a table and just use NetBeans to create our entity class.
Setting Things Up
To continue we need NetBeans 5.5 and the Java EE Application Server 9.0. If you need both, just download and install the bundle.
Creating the Table
CompositeCustomer.sql will create a table with a composite primary key populated with some sample data.
- Save the CompositeCustomer.sql file somewhere on your disk.
- Open the file in NetBeans (Ctrl+O).
- On the SQL editor's toolbar, you'll see a Connection drop-down box. Select the jdbc:derby://localhost:1527/sample [app on APP] entry.
- Click the Run SQL icon
 on the right of the Connection drop-down box. This will open the Connect dialog. The password is app. Click OK to connect and run the SQL script.
- You can now view the table in the Runtime window:
Generating Entities With Composite Keys
The setup was the hard part. To create the entity class:
- Create a new Web Application project named CompositeKey.
- Add a new file (Ctrl+N) and select Persistence > Entity Classes from Database
- Select jdbc/sample as the Data Source and COMPOSITE_CUSTOMER as the table. Click Next.
- Set the Package to entity and click Create Persistence Unit.
- On the Create Persistence Unit dialog, just click Create.
- Click Finish
And you're done. The wizard created the primary key class, CompositeCustomerPK as well as the entity CompositeCustomer.
Testing the Entity
Here we'll delete our table and let JPA re-create it for us.
- Switch to the Runtime window, select the COMPOSITE_CUSTOMER table and press Delete.
- Switch to the Projects window and open up persistence.xml (under the Configuration Files folder). Change the Table Generation Strategy to Create
- Right-click the CompositeKey project and select Deploy Project. This will start the Sun Java System Application Server, if not already running, and deploy the project.
- Switch back to the Runtime window, right-click the Tables folder and select Refresh. JPA has re-created the table for us (sans the sample data, of course).
Bookmark blog post:  del.icio.us  Digg  DZone  Furl  Reddit
Comments
Comments are listed in date ascending order (oldest first) | Post Comment
-
Nice sample, Brian, thanks.
Can't you do this just as well using just the JPA jar files without the full app server download? Just checking...
David
Posted by: davidvc on November 29, 2006 at 11:15 AM
-
Hey David,
Yes, good point, the app server is not required. I use it because it's an easy way to get Derby integrated into NetBeans. It also allows me to easily do the last step, Testing the Entity. I could also do that without the app server, but it's a bit more work with Java SE (not just a simple application deployment).
Cheers,
Brian
Posted by: bleonard on November 29, 2006 at 04:02 PM
-
Composite PKs that are created from foreign keys seem like a little more trouble. Can you blog about them?
JoSE
Posted by: xmaniac on November 30, 2006 at 12:44 AM
-
More trouble than I want to deal with :-). Can you give me an example?
Posted by: bleonard on November 30, 2006 at 06:19 AM
-
Coincidentally, I just started a new project last week that deals w/ legacy Progress 10.1A tables where almost all of them have composite keys.
Could you blog on the details of doing @OneToMany/@ManyToOne and @ManyToMany relationships where composite PKs and FKs are involved?
I've found this to be particularly troublesome with both Toplink and Hibernate as Glassfish's persistence framework.
In particular, I've only gotten a @OneToOne to work correctly...but in a situation where I have to do the @JoinTables *exactly* the same way for a @ManyToMany or @OneToMany (bi-directional) - I simply cannot deploy the application without GF telling me the relationship is "incomplete" - it then goes on to report that I should built the @JoinTables *exactly* as I've done them.
See here for details
Posted by: zambizzi on November 30, 2006 at 07:32 AM
-
Is there any standred way for calling Stored procedure and functions using JPA ???
Posted by: javaniraj on November 30, 2006 at 07:41 AM
-
This CustomersCompositeRel.sql creates a ManyToOne releationship from the Customers table to the Discount table, where the foreign key is composite. If you use the steps above to create these tables and then the enity classes, it will deploy to GlassFish.
Regards,
Brian
Posted by: bleonard on November 30, 2006 at 10:37 AM
-
Stored procedures were not addressed in the EJB 3.0 JPA spec. Oracle appears to have addressed this with their JPA Extensions , but I have not tried them myself (and it's not standard). /Brian
Posted by: bleonard on November 30, 2006 at 12:30 PM
-
You're example is good but easy ;-)
Try something like this(pseudo-code)
create table A (ID integer, pk ID)
create table B (ID integer, pk ID)
create table C (ID integer, pk ID)
create table ABC (a integer, B integer, C integer, D date, pk (a,b,c))
alter table ABC add constraint fk (a) references A(ID)
alter table ABC add constraint fk (b) references B(ID)
alter table ABC add constraint fk (b) references C(ID)
Creating the relationships is quite a work here.
Greetings,
JoSE
Posted by: xmaniac on November 30, 2006 at 12:59 PM
-
JoSE,
If you could produce the SQL for those tables, that would help. Otherwise just give me a bit of time (I was in a class all day today).
Regards,
Brian
Posted by: bleonard on December 01, 2006 at 02:41 PM
-
Do you have any code sample that persists a class with composite id? My code doesn't work.
@Entity
@Table(name = "COMPOSITE_ACEGI")
public class Composite {
@Embeddable
public static class Id implements Serializable {
@Column(name = "USER_ID", nullable = false)
private Integer userId;
@Column(name = "ROLE_ID", nullable = false)
private String roleId;
public Id() {
}
public Id(Integer userId, String roleId) {
this.userId = userId;
this.roleId = roleId;
}
public boolean equals(Object obj) {
if (obj instanceof Id) {
Id that = (Id) obj;
return this.userId.equals(that.userId) &&
this.roleId.equals(that.roleId);
}
else {
return false;
}
}
public int hashCode() {
int result;
result = userId.hashCode();
result = 31 * result + roleId.hashCode();
return result;
}
public String toString() {
return new ToStringBuilder(this)
.append("userId", userId)
.append("roleId", roleId)
.toString();
}
}
@EmbeddedId
private Id id;
@Column(name = "COMMENTS")
private String comments;
public int hashCode() {
return id.hashCode();
}
public boolean equals(Object o) {
if (o instanceof Composite) {
Composite that = (Composite) o;
return this.id.equals(that.id);
}
else {
return false;
}
}
public String toString() {
return new ToStringBuilder(this)
.append("id", id)
.append("comments", comments)
.toString();
}
public Composite() {
}
public Composite(Integer userId, String roleId, String comments) {
this.id = new Id(userId, roleId);
this.comments = comments;
}
public Id getId() {
return id;
}
public void setId(Id id) {
this.id = id;
}
public String getComments() {
return comments;
}
public void setComments(String comments) {
this.comments = comments;
}
}
public class GenericDaoJpa implements GenericDao {
protected final Log log = LogFactory.getLog(getClass());
private Class persistentClass;
public GenericDaoJpa(Class persistentClass) {
this.persistentClass = persistentClass;
}
protected EntityManager entityManager;
@PersistenceContext(unitName = "AcegiEx_EntityManager")
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
}
public List getAll() {
return this.entityManager.createQuery(
"select obj from " + this.persistentClass.getName() + " obj")
.getResultList();
}
public T get(PK id) {
T entity = (T) this.entityManager.find(this.persistentClass, id);
if (entity == null) {
StringBuilder msg = new StringBuilder()
.append("Uh oh, '")
.append(this.persistentClass)
.append("' object with id '")
.append(id)
.append("' not found...");
log.warn(msg);
throw new EntityNotFoundException(msg.toString());
}
return entity;
}
public T save(T object) {
return entityManager.merge(object);
}
public void persist(T object) {
entityManager.persist(object);
}
}
public class CompositeDaoJpa extends GenericDaoJpa
implements CompositeDao {
public CompositeDaoJpa() {
super(Composite.class);
}
}
public void test5() {
Composite c = new Composite(5, "five", null);
// c = cDao.save(c);
cDao.persist(c);
}
If I use cDao.save (which uses entityManager.merge), then Hibernate does a select statement!
select composite0_.USER_ID as USER1_0_0_, composite0_.ROLE_ID as ROLE2_0_0_, composite0_.COMMENTS as COMMENTS0_0_ from COMPOSITE_ACEGI composite0_ where composite0_.USER_ID=? and composite0_.ROLE_ID=?
If I use cDao.persist (which uses entityManger.persist), then Hibernate almost does nothing!
So ... anybody can help me please?
Posted by: dxxvi on March 14, 2007 at 08:16 AM
-
Looking through your code it appears as though you are just forgetting to commit the transaction. Here's a test project I created using your CompositeAcegi entity. Open it up in NetBeans and press Alt+6 to run the test.
public void testPersist() {
System.out.println("persist");
trans.begin();
CompositeAcegi c = new CompositeAcegi("Five", 5);
em.persist(c);
trans.commit();
Query query = em.createNamedQuery("CompositeAcegi.findByUserId");
query.setParameter("userId", 5);
CompositeAcegi result = (CompositeAcegi) query.getSingleResult();
assertEquals(c, result);
//Clean up for repeat tests
trans.begin();
em.remove(c);
trans.commit();
}
Posted by: bleonard on March 20, 2007 at 12:57 PM
-
Hello I have been playing with two entity beans forning tables very like the above but when I try to populate the second of them (the one with a composite key), I get NullPointer exceptions. Is it possible to show how you would persist an instance of CompositeCustomer from a session bean or servlet? My question comes down to the fact that I don't see how you can populate each part of the key. Thanks Martin O'Shea.
Posted by: martin_oshea on June 16, 2007 at 08:21 AM
-
Martin,
Did you take a look at the test project I posted on March 20th in the comment just above yours? It persists an entity with a composite key.
Regards,
Brian
Posted by: bleonard on June 17, 2007 at 06:15 PM
|
java.net RSS Feeds
