Hibernate: BigInteger vs Long from native query to be used in JPQL query

Question

In our Java EE EJB application, we have the following JPA/Hibernate mapping of a class:

@Entity
@Table(name="T")
@TableGenerator( /* all annotation attributes */)
public class T {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name="SEQ_T", nullable = false)
    private long seqT;

    @OneToMany(
        cascade = CascadeType.ALL, 
        orphanRemoval = true,
        mappedBy = "t",
        fetch = FetchType.LAZY
    )
    private List<W> wu;

}

and these are the classes which are in relation with it:

@Entity
@Table(name="W")
@TableGenerator( /* all annotation attributes */)
public class W {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name="SEQ_W", nullable = false)
    private long seqW;

    @Column(name="SEQ_T", nullable = false)
    private long seqT;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "SEQ_T", insertable = false, updatable = false)
    private T t;

    @OneToMany(
        cascade = CascadeType.ALL, 
        orphanRemoval = true,
        mappedBy = "w",
        fetch = FetchType.LAZY
    )
    private List<WA> wua;
 }

@Entity
@Table(name="WA")
@TableGenerator( /* all annotation attributes */)
public class WA {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name="SEQ_W_A", nullable = false)
    private long seqWA;

    @Column(name="SEQ_W", nullable = false)
    private long seqW;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "SEQ_W", insertable = false, updatable = false)
    private W w;

}

Moreover, we have a scheduled job which is executed periodically by TimerService EJB. First of all, this job must understand if there is something to execute so it performs a native sql query like the following to recover a list of pk from T table according several conditions:

List<Long> seqTs = (List<Long>)em.createNativeQuery("select SEQ_T from T").getResultList();

where em is an instance of EntityManager. The query is not obviously that simple but very complex as it derives from some JOIN and subqueries with other tables. If the returned list is not empty, then the job can do its work and this JPQL is performed to load the entities it manipulates:

String queryJPQL = "select wu from W wu JOIN FECTCH wu.wua where wu.seqT in :seqTs";
List<Workup> wus = em.createQuery(queryJPQL, W.class)
                     .setParameter("seqTs", seqTs)
                     .getResultList();

This query is performed because even if we always need the data in @OneToMany relation, if we set that relation as EAGER then N+1 queries is performed. Instead, with JOIN FETCH a unique query is performed recovering a sort of view and then entities and relations are associated by Hibernate.

Well, the problem is that this exception is raised when .setParameter() is called:

Exception in thread "main" java.lang.IllegalArgumentException: Parameter value element [1] did not match expected type [java.lang.Long (n/a)]

Reading many posts here, and set a breakpoint in Eclipse, I discovered that not a List<Long> is returned from a native query but a List<BigInteger> (according to native type of PK in database), without any ClassCastException or similar. Why this? So, I guess I should perform something like this before:

List<Long> seqTLong = new ArrayList<Long>();
for(BigInteger seqNative : seqTs) 
        seqTLong.add(seqNative.longValue());

and pass it to the query. Anyway, is this the right solution? Is it safe? This because our application supports 3 DB and it is built accordingly in 3 JARs by ANT: Oracle, PostgreSQL and SQL Server. Can I assume the value of PK is always BigInteger for each DB? In Oracle we use Number(19), in PostgreSQL we use BigInt...I don't remember about SQL Server. This entities are then passed to DRools and when rules have been applied, this job uses EntityManager to persist data. That's why I need JPA entities to be loaded, otherwise I would get a

Caused by: org.hibernate.PersistentObjectException: detached entity passed to persist

or I would have to call .find() again for each fact modified by DRools and set its attributes by calling getters from the others. Which still causes N+1 queries.

Answer 1

A safer method would be to use Number instead of BigInteger

List<Long> seqTLong = new ArrayList<Long>();
for(Number seqNative : seqTs) {
        seqTLong.add(seqNative.longValue());
}