When will we be able to make a primary key comprised of fore

[dornus]

I am trying to represent a database object similar to the tomcat role authentication.

Where

CREATE TABLE `myTest` (
 `user` varchar(128) NOT NULL,
 `role` varchar(15) NOT NULL,
 PRIMARY KEY (`user`,`role`),
 CONSTRAINT `FK_user` FOREIGN KEY (`user`) REFERENCES `tableOne` (`user`) ,
 CONSTRAINT `FK_role` FOREIGN KEY (`role`) REFERENCES `tableTwo` (`role`)
)

Each column is a foreign key to another table, and the primary key is both columns.

How do I create a par representation of this?

[dornus]

Here is what I have so far. Any help would be greatly appreciated.

package par;

import javax.persistence.*;

@Entity
@Table(name = "myTest")
public final class MyTestPar {

 private int userPK;
 private User user;

 private String rolePK;
 private Role role;

 public MyTestPar() {

 }

 @Id
 public int getUserPK() {
 return userPK;
 }

 public void setUserPK(int userPK) {
 this.userPK = userPK;
 }

 @ManyToOne
 @JoinColumn(insertable = false, updatable = false, name = "userPK")
 public User getUser() {
 return user;
 }

 public void setUser(User user) {
 this.user = user;
 }

 //@Id --Cannot have two @ID tags in one class
 public String getRolePK() {
 return rolePK;
 }

 public void setRolePK(String rolePK) {
 this.rolePK = rolePK;
 }

 @ManyToOne
 @JoinColumn(insertable = false, updatable = false, name = "rolePK")
 public RoleConfig getRole() {
 return role;
 }

 public void setRole(final Role role) {
 this.role = role;
 }

}

[niesar]

Better consider something like this

@Entity
@Table(name = "myTest")
public final class MyTestPar {

 private int id; // PK for this entity bean
 private User user;
 private Role role;

 public MyTestPar() {
 }

 @Id (generate = GeneratorType.AUTO)
 public int getId() {
 return id;
 }

 @ManyToOne ...
 public User getUser() {
 return user;
 }

 public void setUser(User user) {
 this.user = user;
 }


 @ManyToOne ...
 public Role getRole() {
 return role;
 }

 public void setRole(final Role role) {
 this.role = role;
 }
}

However, all this looks like a standard Many to Many Relationship to me:
http://docs.jboss.org/ejb3/app-server/tutorial/relationships/relationships.html[/url]

[niesar]

[edit]
http://docs.jboss.org/ejb3/app-server/tutorial/relationships/relationships.html

[dornus]

The problem is that role is also a primary key. So it needs an ID tag as well. You can make a class that allows this, but only for primitive things (ints, Strings, etc..) However if both primary keys are custom objects it never works out correctly.

[dornus]

Are you suggesting that I add another column to my table which will be the primary key?

I thought about that, but it wouldn't be appropriate for a few of my tables that need this type of functionality.

[niesar]

"Dornus" wrote:
Are you suggesting that I add another column to my table which will be the primary key?

Yes, unless query performance isn't relevant.

[niesar]

"Dornus" wrote:
The problem is that role is also a primary key. So it needs an ID tag as well.

Well, there needs to be a unique primary key for every TABLE. Both, userPK and rolePK are primary keys in other tables. You need another PK for the "myTest" table. This can be done with a Primary Key class combining whatever data is neccessary. But in my experience you are likely to run into significant performance problems.

[dornus]

Is the performance problems 'hibernate specific', or are you saying just in general?

I was looking for an alternative way, since I have quite a few tables I'd have to modify.

Thanks for responding and providing input.

[dornus]

I changed the code as you suggested but now am running into a slightly different problem.

The column I am referencing in User is not the primary key for the User.
Ex.

CREATE TABLE `user` (
 `id` integer unsigned NOT NULL auto_increment,
 `username` varchar(128) NOT NULL,
 PRIMARY KEY (`id`)
 )

In my code I am doing this

 private User user;

 @ManyToOne
 @JoinColumn(name="username", nullable=false )
 public User getUsername() {
 return user;
 }

 public void setUsername(User user) {
 this.user = user;
 }

Is this legal or can you only do joins on primary keys? For some reason it keeps trying to insert the primary key of user into the 'myTest' table instead of the username column.

[dornus]

Nevermind, I got it working by adding the following:

 @ManyToOne
 @JoinColumn(name="blah", nullable=false, referencedColumnName="userName")

[gavin.king]

Just map an @Id property and an @ManyToOne association to the same column, and mark the many-to-one as insertable="false" updatabale="false".

There are examples of this in the Hibernate documentation and test suite.