JPA getting repeated rows (results all have the same values)

Question

I have experience with JDBC and am attempting to move to JPA. I created some entity classes using the eclipse facet that read from a Netezza DB. I am using createNativeQuery with a TypedQuery return. I am doing a simple select * from table.

The problem is that it works great for some tables, but on others it returns the right number of rows, but they are all the same... It is the same code because I copied the working code to the other class and only did a change all for the table name. I also tried the namedQuery created by the facet and it gave the same results.

Here is the code that calls into Entity class:

@Test
public void test2() {
    assertTrue(emf != null);
    if (em == null) {
        em = (EntityManager) Persistence.
                  createEntityManagerFactory("web20POC").
                  createEntityManager();
    }
    TypedQuery<Dimtask> tq = (TypedQuery<Dimtask>) em.createNamedQuery("Dimtask.findAll", Dimtask.class);
    tq.setFirstResult(0);
    tq.setMaxResults(10);
    List<Dimtask> rlist = tq.getResultList();
    for (Dimtask line : rlist) {
        System.out.println(line.toString().trim());
    }
}

Any idea on why I would get all the same values with one table when the same code works with another table?

Thanks in advance.

Answer 1

I've had the same problem.

The answer is, that if there is a @Id annotation in your entity JPA will threat those rows with the given id as a unique element.

For example if you have in your DB 3 rows where this @Id annotated field is the same, JPA will use the first result elements and just duplicate them.

In the DB you can see:

1; ID: 111, Name : AAA

2; ID: 111, Name : BBB

3; ID: 111, Name : CCC

The result will be 3 rows retrieved by JPA:

1; ID: 111 Name: AAA

1; ID: 111 Name: AAA

1; ID: 111 Name: AAA

Check if the Entity has an @Id annotation, and check if that field value is really unique in the DB.

Answer 2

The problem is the ID. The @Id should not have the same value in database, because is created a primary cache for same ID value.