0

I have an entity class userdetails which has the username, userid (numeric) and password fields, with username and userid forming a composite primary key. This is negotiable, and possibly unimportant to the main problem.

I have another class, connectiontable, which has userid as the primary key. The sql code used to generate the relevant tables is as follows:

create table usertable
(
userid int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
username varchar(128) NOT NULL UNIQUE,
password varchar(128) NOT NULL, 
CONSTRAINT USER_PK PRIMARY KEY(username, userid)

);

That's the sql code for usertable. The following is for connectiontable

create table connectiontable
(
userid int not null,
username varchar(128) not null,
connections varchar(32670) not null,

CONSTRAINT CONNECTION_PK PRIMARY KEY(username, userid),
CONSTRAINT CONNECTION_FK FOREIGN KEY(username,userid) REFERENCES usertable(username,userid) 
);

There are a bunch of other things in connectiontable, but those are irrelevant. I use netbeans 7.2.1 and Jave EE6. I use the 'create entities from database entries' but for some reason, I don't have a getter and setter for either userid or username. They are in connectiontablePK, but I can't seem to make use of that. For example, when I generate the jsf pages, I want to be able to do something like:

Connectiontable con = new Connectiontable();
con.getUsername();

But it complains because it can't find that method in connectiontable.java.

Can anyone advise me why this is the case, and how I can solve it? Thank you.

AodhanOL
  • 630
  • 7
  • 26
  • Why is the primary key for `usertable` _both_ `userid` and `username`? Since both of them are unique, either one alone should have worked for the primary key (especially as using just `userid` would have allowed you to make `username` updateable). Please don't suffix everything with `...table`, as it ends up being noise. Also, I'm extremely suspicious about `connections` - I have a feeling that it's holding delimited data (comma-separated list), which is usually frowned upon. – Clockwork-Muse Jan 23 '13 at 17:04
  • Out of curiosity - what would you recommend I use instead? I'm using something different, but I've never heard that it's frowned upon (mind you, I'm learning this all as I go, it's not part of an official class, so that could be part of it). – AodhanOL Jan 25 '13 at 10:56
  • Recording multiple values in a single column is a violation of [First Normal Form](http://en.wikipedia.org/wiki/First_normal_form), one of the major design guidelines for creating good databases. It also makes dealing with the column **extremely** difficult, on the database side. And dealing with it on the application side isn't often performant, either. More information is needed for actual design recommendations - post a new question with sample contents (and use) of `ConnectionTable`. – Clockwork-Muse Jan 25 '13 at 17:06

1 Answers1

1

... You're not posting the Java code, which I suspect would help but:

Anytime in JPA when you have a composite primary key, you have to have an 'embedded' primary-key class. I suspect you have a class definition similar to the following:

@Embeddable
public class UserNameId {
    private int userid;
    private String username;
}

And then usertable and connectiontable both contain the following (or similar):

@Embedded
@Id
private UserNameId userNameId;

... So you should expect a getter/setter for userNameId, but not the embedded fields, like you expect.

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45
  • Sorry for that, I was in a hurry when I posted this and thought I had added that code at the end. I think your answer is correct, but I'm not sure why this would be. – AodhanOL Jan 25 '13 at 11:15
  • This is because JPA wants a single 'value' for the id (the embedded class should be overriding `.hashcode()` and `.equals(...)`. Having multiple columns makes it difficult. – Clockwork-Muse Jan 25 '13 at 17:07