'Hibernate createNativeQuery returns duplicate rows

I have 2 database tables Customer and Items with 1 -> many relation. To fetch data from database i am using the following query.

select customer.id, customer.name, items.itemName, items.itemPrice from testdb.customer INNER JOIN items ON items.customer_Id = customer.id

I have an entity class Customers

@Entity    
public class Customer{

@Id
private int id;

@Column
private String name;

@Column
private String itemName;

@Column
private int itemPrice;

public Customer() {}
 //Getter and setter are here
.......
}

in Service class i have the following code.

@GET @Path("/getCustomerInfo")
@Produces(MediaType.APPLICATION_JSON)
public List getCustomerInfo() {
    CustomerDao dao = new CustomerDao();
    return dao.getBuildingsCustomerInfo();
}

in my DAO class i have the following code

public List<Customer> getCustomerInfo(){
    Session session = SessionUtil.getSession();
    String queryString = "the above mentioned query";
    List<Customer> customerInfo = session.createNativeQuery(queryString, Customer.class) ;
    session.close();
    return customerInfo;
}

I am getting the following JSON response from the service

[id:1, name:"Alfred", itemName:"jeans", itemprice:10],[id:1, name:"Alfred", itemName:"jeans", itemprice:10],[id:2, name:"James", itemName:"watch", itemPrice:20 ],[id:2, name:"James", itemName:"watch", itemPrice:20 ], [id:2, name:"James", itemName:"watch", itemPrice:20 ]

The number of results are 5 which is correct But 2nd result is a copy of 1st, 4th and 5th are copies of 3rd. In 2nd, 4th and 5th results the itemName and the itemPrice should be different.

if I use createSQLQuery(queryString); instead of createNativeQuery(queryString, Customer.class); I am getting the correct result but without entity attribut names. 

[1, "Alfred", "jeans", 10],[1, "Alfred", "shirt", 15],[2, "James", "watch", 20], [2, "James", "coffee", 25], [2, "James", "drinks", 30]

I have seen number of articles but could not find the solution. I have to use createNativeQuery() not createSQLQuery() because I need to map the entity class attributes. Please let me know if i am doing something wrong.

sqljsonhibernateweb-serviceshibernate-mapping


Solution 1:[1]

Your data structure is wrong on the Java side and not corresponding to the database relation. In the relation you describe you need to have a list of items:

@Entity    
public class Customer implements Serializable {
    // ... the fields you have so far

    // assuming the parent field on the other side is called customer
    // you may also want to set the cascade and orphanRemoval properties of the annotation
    @OneToMany(mappedBy = "customer")
    @JsonManagedReference // assuming you're using Jackson databind JSON
    private List<Item> items;

}

And on the Item side:

@Entity
public class Item implements Serializable {
    @Id
    private int id;

    @JsonBackReference
    @ManyToOne
    @JoinColumn(name = "customer_Id")
    private Customer customer;

}

Then if you really the JSON data strucutred that way, you need a third Entity class to use as a ResultSetMapping.

@Entity
@SqlResultSetMapping(
    name = "CustomerItem",
    entities = @EntityResult(entityClass = CustomerItem.class)
)
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "CustomerItem.getAll",
        resultSetMapping = "CustomerItem"
        query = "select customer.id as cid, items.id as iid, customer.name,"
            + " items.itemName, items.itemPrice from testdb.customer INNER JOIN"
            + " items ON items.customer_Id = customer.id"
    )
})
public class CustomerItem implements Serializable {
    @Id
    private int cid;

    @Id
    private int iid;

    @Column
    private String name;

    @Column
    private String itemName;

    @Column
    private int itemPrice;

    ... getters and setters
}

Then you can use the native query in named variant, which should offer some slight optimizations.

List<CustomerItem> lst = em.createNamedQuery("CustomerItem.getAll", CustomerItem.class)
                               .getResultList();

The use of @SqlResultSetMapping is so that the returned entities are not monitored for changes, but you can still use the defined entity for the result. I believe that by JPA specification it should also work without it, but in Hibernate it doesn't. Could be a bug, or a planned, but not implemented feature, or I could just be misinterpreting the JPA usage, but this workaround does work with Hibernate 5+.

Solution 2:[2]

Not sure about the exact reason behind duplicates but SELECT DISTINCT will solve your issue as it will take only distinct records.

Refer using-distinct-in-jpa

Solution 3:[3]

I solve this issue by using @SqlResultSetMapping

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Wilson

Related Questions