JPA Composite-ID+ForeignKey =>SQLException: Invalid column index

From:

grz01 <grz01@spray.se>

Newsgroups:

comp.lang.java.programmer,comp.lang.java.databases

Date:

Mon, 16 Nov 2009 16:34:36 -0800 (PST)

Message-ID:

<0063b9b1-38bc-4d52-a9fd-35d04ecf2265@j4g2000yqe.googlegroups.com>

Hi,

I have a problem running JPA + Hibernate + Oracle, that I am not able
to figure out.

Here is a minimal test-case to demonstrate the error I get:

I have a class, say B, with a composite-id of two columns. One of the
id-columns is also a foreign-key to another class, say C.

In Oracle:

    CREATE TABLE C
    ( C_ID INTEGER,
      CONSTRAINT PK_C PRIMARY KEY(C_ID)
    );

    CREATE TABLE B
    ( B_PK1 INTEGER,
      B_PK2 INTEGER,
      CONSTRAINT PK_B PRIMARY KEY (B_PK1, B_PK2),
      CONSTRAINT FK_B_C FOREIGN KEY (B_PK1) REFERENCES C (C_ID)
    );

In Java, I define the classes B, BPK and C.
I need a ID-class BPK for the primary key of B,
and I also want be able to access the class C directly from the class
B:

BPK.java:

    @Embeddable
    public class BPK implements Serializable {

        @Column(name = "B_PK1")
        private Long pk1;

        @Column(name = "B_PK2")
        private Long pk2;

        // ...setters, getters, equals and hashCode() go here...
    }

B.java:

    @Entity
    @Table(name = "B")
    @IdClass(value = BPK.class)
    public class B {
        @Id
        @Column(name = "B_PK1")
        Long pk1;

        @Id
        @Column(name = "B_PK2")
        Long pk2;

        @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name = "B_PK1", referencedColumnName = "C_ID")
        private C c;

        // ...setters, getters, equals and hashCode() go here...
    }

C.java:

    @Entity
    @Table(name = "C")
    public class C {
        @Id
        @Column(name = "C_ID")
        private Long cId;
            // ...setters, getters, equals and hashCode() go here...
    }

Now I preload table C with the value 1:

    insert into c values (1);
    commit;

Next, run the test-case:

    B b = new B();
    b.setPk1(1L);
    b.setPk2(new Random().nextLong() % 1000000L);
    entityManager.persist(b);
    entityManager.flush();

This gives me the exception:

....
Caused by: java.sql.SQLException: Invalid column index
   at oracle.jdbc.driver.DatabaseError.throwSqlException
(DatabaseError.java:112)
   at oracle.jdbc.driver.DatabaseError.throwSqlException
(DatabaseError.java:146)
   at oracle.jdbc.driver.DatabaseError.throwSqlException
(DatabaseError.java:208)
   at oracle.jdbc.driver.OraclePreparedStatement.setLongInternal
(OraclePreparedStatement.java:4639)
   at oracle.jdbc.driver.OraclePreparedStatement.setLong
(OraclePreparedStatement.java:4631)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.setLong
(DelegatingPreparedStatement.java:120)
   at org.hibernate.type.LongType.set(LongType.java:65)
   at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:
154)
   at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:
136)
   at org.hibernate.type.ComponentType.nullSafeSet(ComponentType.java:
307)
   at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate
(AbstractEntityPersister.java:2036)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert
(AbstractEntityPersister.java:2271)
   ... 50 more

In the log I can see that the error is caused by
     insert into b (b_pk1, b_pk2) values (?, ?)

I think this Exception means that Hibernate somehow gets confused and
tries to submit more parameter-values to the INSERT statement than
there are ?-place-holders in the SQL-statement, but I cannot figure
out what the reason is.

Anyone can explain exactly what causes this error?

( I am aware that the class B kind of references class C "twice" -
both through the BPK.pk1 foreign-key, and the property B.c, but that
is how I gleaned from other examples, that you are supposed to do in a
case like this? )

/ grz01