Help needed for JPA/hibernate - problems with creating entity classes

Question

I started creating JPA/hibernate mappings for a legacy database based on Oracle. At one (early...) point I have a many-to-many relation between to tables (FOO, BAR, join table with extra fields: FOO_BAR). So I defined three entities, created an embeddable Id class for the join table, strictly following some examples from a good (?!) book.

I can select a Foo but whenever I try to read the related Bars from the result set, I get wither an "SQLException: No data found" or a "SQLException: General error (S1000)". I can switch between the two by just changing some java types of the entity beans...

The log contains the following line n case of the "No data found" error:

INFO org.hibernate.type.LongType - could not read column value from result set: Foo1_2_; No data found

The columns FOO_ID and BAR_ID are defined as NUMBER(22). I tried Long types first, this resulted in "No data found", Double led to "General error". Then I read somewhere that the standard mapping for NUMBER is BigDecimal (-> "General Error"), I tried BigInteger instead (-> "No data found").

I'm lost.

When I take the sql statement from the logs and use it with "native" jdbc, ... it works fine.

PreparedStatement prep = con.prepareStatement(sqlQueryFromHibernateLogs);
prep.setBigDecimal(1, new  BigDecimal(1));
ResultSet rs = prep.executeeQuery();  // result set has the correct values...

Any help, suggestions, pointers to helpful resources are highly appreciated. Oh, one final thing to mention: I'm 'forced' to use the JdbcOdbc bridge. It's really a legacy system...

---

My select statement goes like this:

List<Foo> foos = em.createQuery("select f from Foo f order by f.name").getResultList();

---

Edit

Versions - I'm bound to the hibernate libraries that are shipped with the play framework (1.0.3.2). The hibernate3.jar has no useful version information (nothing in Manifest, Version#getVersionString() says [WORKING]), the other hibernatexxx jars report as 3.1.0.GA (validator) or 3.4.0.GA (entitymanager).

---

Edit 2

I've reduced the classes to the absolute minimum with the errors still present. This is what I did:

Foo.java

@Entitiy @Table(name="FOO")
public class Foo {
  @Id @Column(name="FOO_ID")
  private BigInteger fooId;

  Foo(){}

  @OneToMany(mappedBy="foo")
  private Set<FooBar> fooBars = new HashSet<FooBar>();
}

Bar.java

@Entitiy @Table(name="BAR")
public class Bar {
  @Id @Column(name="BAR_ID")
  private BigInteger fooId;

  Bar(){}

  @OneToMany(mappedBy="bar")
  private Set<FooBar> fooBars = new HashSet<FooBar>();
}

FooBar.java

@Entitiy @Table(name="FOOBAR")
public class FooBar {

  @Embeddable
  public static class Id implements Serializable {
    @Column(name="FOO_ID")
    private BigInteger fooId;
    @Column(name="BAR_ID")
    private BigInteger barId;

    Id() {}

    // implementations of hashcode and equals

  }

  @Embedded
  private Id id = new Id();

  @ManytoOne @JoinColumn(name = "FOO_ID", insertable=false, updatable=false)
  private Foo foo;

  @ManytoOne @JoinColumn(name = "BAR_ID", insertable=false, updatable=false)
  private Bar bar;

  FooBar(){}
}

FOO_ID and BAR_ID are defined as NUMBER(22) on the the Oracle database. The above example leads to the "No data found" error, replacing BigInteger with Long results in the "General Error". And sending the very same SQL expression over the bridge gives a correct result set...

Answer 1

Found the solution or - at least - found out, that the approach can't work with the given tools (hibernate, JdbcOdbc bridge):

Some more googeling finally sent me to this (german) page, where someone had a similiar problem: a No data found SQLException while working with the bridge. Someone else answered, that the code is correct but it is a problem with the JdbcOdbc bridge which does not support reading a result set more than once. I can't clarify, if I have the same problem here, but I'm pretty sure that it is this or a similiar case and the problem is related to using the bridge with hibernate/JPA.

For my very special case: I solved the problem by ignoring it after ... finally finding out that I can use the oracle oci driver. (sigh)

Thanks to everyone who tried to follow my posting and thought about a solution!