Hibernate createNativeQuery returns duplicate rows

Question

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.

@K.Nicholas the problem is not in the select query. If I run the same query in MySQL or with createSQLQuery(queryString) function, I get the correct result. The problem comes in when I run the query with createNativeQuery(queryString, Customer.class) function. — Daim, Nov 05 '18 at 07:07

Alien · Answer 1 · 2018-11-05T05:40:52.490

0

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

edited Nov 05 '18 at 05:40

answered Nov 05 '18 at 05:35

Alien

15,141
6
37
57

the problem is not in the select query. If I run the same query in MySQL or with createSQLQuery(queryString) function, I get the correct result. The problem comes in when I run the query with createNativeQuery(queryString, Customer.class) function – Daim Nov 05 '18 at 07:09

coladict · Accepted Answer · 2018-11-05T08:12:53.390

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+.

thanks alot for your reply. I am trying to adapt your solution but when I deploy the code I get an error "org.hibernate.AnnotationException: Use of OneToMany or ManyToMany targeting an unmapped class". In my Items class I have ManyToOne JoinColumn(name = "customer_Id") private Customer customer; and in my Customer class I have OneToMany(mappedBy = "customer") private List items; Moreover do i need to have getters and setters in both customer and item classes ? I think we dont need it as the mapping class is CustomerItems. — Daim, Nov 05 '18 at 09:43
You do need the getters and setters, yes. With an error like that, it probably means you have to list the entity classes somewhere (in the persistence.xml or orm.xml perhaps), because it's not detecting them automatically. Also I forgot to add the `@Table` annotations, but I assume you have those. — coladict, Nov 05 '18 at 09:47
I have changed everything as per your suggestion but still the same error. The number of fetched results must be 5. I am getting back the correct number of results but 2nd result is copy of 1st where as 4th and 5th are copies of 3rd. It seems that if the customer ID is same for 2 or more records JPA is coping the first record over all others. — Daim, Nov 05 '18 at 10:13
its working now. I forgot to add items id in the query. Now i added items id in the query and everything is working fine. Thanks alot for your help. — Daim, Nov 05 '18 at 10:39

score 0 · Answer 3 · answered Apr 19 '22 at 21:06

0

I solve this issue by using @SqlResultSetMapping

answered Apr 19 '22 at 21:06

Wilson

67
3

Hibernate createNativeQuery returns duplicate rows

3 Answers3