Querying a view with JPA returns weird results

Question

this is my view data:

30.0 120.0 1500.0 16
30.0 120.0 4000.0 16
30.0 140.0 1500.0 16
30.0 140.0 4000.0 16
35.0 130.0 2750.0 18
40.0 120.0 1500.0 16
40.0 120.0 4000.0 16
40.0 140.0 1500.0 16
40.0 140.0 4000.0 16

this is my code:

public List<Duplicate> getData() {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("DXSorterPU");
    EntityManager entityManager = emf.createEntityManager();
    entityManager.getTransaction().begin();
    List<Duplicate> result = entityManager.createQuery("SELECT d FROM Duplicate d").getResultList();
    for (Duplicate d : result) {
        System.out.println(d.getF1()+"  " +d.getF3()+"  "+ d.getF4()+"  "+ d.getResult()) ;
    }
    entityManager.getTransaction().commit();
    entityManager.close();
    return result;
}

this is my data that i get with above code:

30.0 120.0 1500.0 16
30.0 120.0 1500.0 16
30.0 120.0 1500.0 16
30.0 120.0 1500.0 16
35.0 130.0 2750.0 18
40.0 120.0 1500.0 16
40.0 120.0 1500.0 16
40.0 120.0 1500.0 16
40.0 120.0 1500.0 16

Why the result is different? Please help me.

this is my Duplicate class:

package dxsorter;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlRootElement;

@Entity
@Table(name = "DUPLICATE")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Duplicate.findAll", query = "SELECT d FROM Duplicate d")
    , @NamedQuery(name = "Duplicate.findByF1", query = "SELECT d FROM Duplicate d WHERE d.f1 = :f1")
    , @NamedQuery(name = "Duplicate.findByF3", query = "SELECT d FROM Duplicate d WHERE d.f3 = :f3")
    , @NamedQuery(name = "Duplicate.findByF4", query = "SELECT d FROM Duplicate d WHERE d.f4 = :f4")
    , @NamedQuery(name = "Duplicate.findByResult", query = "SELECT d FROM Duplicate d WHERE d.result = :result")})
public class Duplicate implements Serializable {

    private static final long serialVersionUID = 1L;
    // @Max(value=?)  @Min(value=?)//if you know range of your decimal fields consider using these annotations to enforce field validation
    @Basic(optional = false)
    @Column(name = "F1")
    @Id
    private BigDecimal f1;
    @Basic(optional = false)
    @Column(name = "F3")
    private BigDecimal f3;
    @Basic(optional = false)
    @Column(name = "F4")
    private BigDecimal f4;
    @Basic(optional = false)
    @Column(name = "RESULT")
    private int result;

    public Duplicate() {
    }

    public BigDecimal getF1() {
        return f1;
    }

    public void setF1(BigDecimal f1) {
        this.f1 = f1;
    }

    public BigDecimal getF3() {
        return f3;
    }

    public void setF3(BigDecimal f3) {
        this.f3 = f3;
    }

    public BigDecimal getF4() {
        return f4;
    }

    public void setF4(BigDecimal f4) {
        this.f4 = f4;
    }

    public int getResult() {
        return result;
    }

    public void setResult(int result) {
        this.result = result;
    }

    public List<Duplicate> getData() {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("DXSorterPU");
        EntityManager entityManager = emf.createEntityManager();
        entityManager.getTransaction().begin();
        List<Duplicate> result = entityManager.createQuery("SELECT d FROM Duplicate d").getResultList();
        for (Duplicate d : result) {
            System.out.println(d.getF1()+"  " +d.getF3()+"  "+ d.getF4()+"  "+ d.getResult()) ;
        }
        entityManager.getTransaction().commit();
        entityManager.close();
        return result;
    }

}

Answer 1

Your problem is that the column that you annotated with @Id (F1) is not unique in your view. Your JPA provider (for example hibernate) assumes that the value of F1 is unique in the view.

So this is what happens when you execute the query:

Lets say that the first row that is fetched is

30.0 120.0 1500.0 16.

Your JPA provider associates this row with the id 30.0 (the value of the F1 column).

Lets say that the next row that is fetched is 30.0 120.0 4000.0 16. Now, we can see that this row is different from the first one. But your JPA provider does not know that. What the provider sees is that the id of this row has value of 30. Because he already fetched a row with that id (and has it in the session/cache) he believes that it is the same row. So instead of creating a new object for the second row, he puts the same object from the first row.

In order to solve this problem, you should either create a real id column (that will have unique values in the view) or use raw SQL queries.

Adding an ID column to the view

MySQL:
You can add a unique identifier to the view by using the UUID() function in mysql. That is explained here. With the UUID, your select should look like this:

SELECT UUID() as ID, FACTORS.F1, FACTORS.F3, FACTORS.F4, COUNT() AS RESULT FROM APP.FACTORS GROUP BY FACTORS.F1, FACTORS.F3, FACTORS.F4 HAVING COUNT() > 1

I created a simple example on rextester. You can check it out here.

Derby:
In derby you can use the ROW_NUMBER() function. Your select should look like this:

SELECT ROW_NUMBER() OVER () as ID, FACTORS.F1, FACTORS.F3, FACTORS.F4, COUNT() AS RESULT FROM APP.FACTORS GROUP BY FACTORS.F1, FACTORS.F3, FACTORS.F4 HAVING COUNT() > 1