Hibernate version: 3 with JPA Annotations

Hi,

I'd appreciate some advice about how to map the following example, is a legacy database and some existing classes that I'm trying to annotate. The bean structure can be simplified as:

Group:

(PK) int groupId; (oracle sequence)
Set<Request> getRequests();

Request:

PK (group)
PK int requestID (oracle sequence);
getter for group;

So essentially group has a list of requests, and a request's PK is its group in addition to a requestID (oracle sequence number). I know this is not ideal, and some tables have 4PK columns two of which will be foreign keys, and one will be a sequence. But please take it as read that we can't change this! (I've tried). There's three ways of representing this from the JPwith Hibernate book. But can't seem to get any to work. Error is 'A Foreign key refering xxx.Group from xxx.Request has wrong number of Column . 1.' Is the normal error.

I've tried just using an int in Request to point to its group and also an object. I've tried embeddedId etc as well. Any help much appreciate as am really struggling.

The alternative which I think would work, is to tell hibernate that the Group is not part of the PK for request, which would make it more normal. But this wouldn't then correctly match the DB and I'd assume it would be bad practice.

Any help appreciated, and posts will be rated.

Cheers,

Chris.

Can u post the XML files. And create table scripts.

I'm using annotations so here is what I have at the moment, this works without the composite key, but not with it:

----------------------------------
Group

@Id @GeneratedValue(generator = "seq1")
@Column(name = "GP_NO")
@SequenceGenerator(name="seq1" sequenceName="seq1sequence")
public voic getGroupId() etc


@OneToMany (mappedBy = "group")
public List<Request> getRequests() etc

----------------------------------
Request

@ManyToOne(cascade = CascadeType.ALL, fetch=FetchType.LAZY, targetEntity = "Group.class")
@JoinColumn(name="GP_NO", nullable=false)
public Group getGroup() etc

@Id
@Column (name="RQ_NO");
public int getOnlineRequestId;
----------------------------------

This is copied from a computer not internet accesssible so please excuse any small typos.

What I'd like to do, is to make the getGroup be part of the primary key. I've create a separate PK class but any attempts to map getGroup as part of a composite PK fail. I've also tried @PrimaryKeyJoinColumn instead of JoinColumn

Table Structure is basic:

Group:
int GP_NO
Request:
int GP_NO
int RQ_NO

Many thanks,

Chris.

Have realised what I was doing wrong, I was using an object and not a string to represent the group in the PK class.

Now all that's left is to figure out how to make the PK class generate an oracle sequence number, as well as link to the foreign key... this database is such a mess!

Hi Chris,

I'm having a similar problem, trying to use composite keys and sequences in Oracle DB. Did you solve your problem? Please share the solution!

Thanks in advance,

Alexandre

Hi Alexandre,

All I think I did in the end was use the oracle sequence as the primary key and ignore the compound nature as found in the db, the sequence is guaranteed to be unique so that seemed fine. I don't think there is a great need to match exactly the databse, but if you've some other problem then let me know ;)

Cheers,

Chris.

Hi Chris,

Unfortunatelly to me, I need follow the already defined database structure, and it has a large number of composite keys like this:

@Entity
@IdClass(AtAtendimentosPK.class)
@SequenceGenerator(name = "AtendimentoSeq", sequenceName = "ATATE_SEQ",
allocationSize = 1)
public class AtAtendimentos implements Serializable {

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "AtendimentoSeq")
//@GeneratedValue(strategy=GenerationType.SEQUENCE, sequence = "ATATE_SEQ")
@Column(name = "NUM_ATENDIMENTO", nullable = false)
private Long numAtendimento;
@Id
@Column(name = "COD_MAQ_ATE", nullable = false)
private Long codMaqAte;

....

}

Althought I've defined a PK key class exactly how is showned in web's available samples, Hibernate don't query the ATATE_SEQ sequence object to obtain a new value, what leads to an SQLExcecption "Attempt to insert null value at column NUM_ATENDIMENTO"

Any suggests?

Alexandre

yep I see, I've had to call the sequence a few times from the constructor of the object, probably not the most efficient but it works :)

Thanks Chris, but invoke a sequence in entity's contructor will generate a new problem to me. My application is deployed on JBoss an OC4J, and when in OC4J, the Toplink Essentials persistence provider correctly gets the next sequence value. If I put a query in the constructor, the sequences will be called two times on OC4J.

It seems a Hibernate bug to me. And only happens with compound keys. I know is better user unique object ids as primary keys, but it applies only to new applications. I can't believe Hibernate team doesn't think about legacy databases.

But thanks again for your reply. I still have my problem :)

Alexandre

ah ok, we're also using oc4j, but not toplink.

Do you have triggers on the inserts to get the key? if so there should be no need to specify a sequence at all.

If you don't have the triggers then it should be possible to just use a compound key with an int, and manually retrieve the seq no first, that's what we do :)

the legacy database situation does make hibernate less useful :(

Chris,

I agree. Besides legacy databases, Hibernate stills a great software to me. Just think about write some 6 hundreds of DAO's... ahhhhhh

I'm sad to see the desired behaviour in Toplink but not in Hibernate. Is not JPA a specification? Why the different behaviour?

Anything we do, it should be done in Java, and not in DB. We're not allowed to alter database structure in any way, Because there are a lot of Oracle Form apps accessing the same tables. It is because of it that triggers are not an option.

To get sequence number in Java apps, and set it into the entities is an alternative, but not an elegant one! In this case the sequence annotations in the entities have no reason to be there.

Regards,

Alexandre