Hibernate native sql join query

Question

I have a problem with hibernate native sql join query. My query is below and works on Mysql db.

SELECT c.cart_id, u.name, u.surname, c.totalPrice
FROM sandbox.cart c JOIN
     sandbox.user u
     ON u.id = c.placedBy

I am using hibernate in code and encountered an exception

java.sql.SQLException: Column 'id' not found.
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1093)

Query in code here

Session session = hibernateUtil.getSessionFactory().getCurrentSession();
        SQLQuery query = session.createSQLQuery(ORDER_PER_USER_QUERY);
        query.addEntity(OrderPerUser.class);
        return query.list();

Table column name

Cart

| cart_id | placedBy | totalPrice

User

| id | email | name | surname

My mapped class is

   @Entity
public class OrderPerUser {
@Id
private long id;
private String name;
private String surName;
private long cartId;
private double totalPrice; }

I already added a mapped class. I added in question

Ugur Artun
– Ugur Artun

2016-06-10 13:11:14 +00:00
Commented Jun 10, 2016 at 13:11 — Ugur Artun
– Ugur Artun, Commented Jun 10, 2016 at 13:11

Kalam · Accepted Answer · 2016-06-10 13:51:19Z

1

You need to remove the line:
query.addEntity(OrderPerUser.class);
After that, you need to rewrite the code and map your object manually, because your OrderPerUser is not an entity:

Session session = hibernateUtil.getSessionFactory().getCurrentSession();
    SQLQuery query = session.createSQLQuery(ORDER_PER_USER_QUERY);
    List<OrderPerUser> returnList new ArrayList<>();
    for(Object[] row : query.list()){
        OrderPerUser orderPerUserObj = new OrderPerUser();
        oderPerUserObj.setCartId(Long.parseLong(row[0].toString()));
        //put other properties here
        returnList.add(orderPerUserObj);
    }
    return returnList;

Edit1: Now I see that you added the mapped class, but OrderPerUser should not be an entity in your case, but a regular DTO. An entity requires an ID, but you can't select the ID in this case, because OrderPerUser is not part of a table, it is just some selected data that you want in your memory and not in the database. So you should make your OrderPerUser a regular data transfer object.
Please read about entities, data transfer objects, data access objects to see what each object should do.

edited Jun 10, 2016 at 13:51

answered Jun 10, 2016 at 13:11

Kalam

464 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ugur Artun Over a year ago

I use OrderPerUser class only mapping the list result. I want to return list only in memory. I dont want to persist db.

Kalam Over a year ago

I don't persist anything in DB. What I do here, is return a list of the selected information. Nothing is persisted in DB and the returnList will hold the information that you selected and you can then put it in a cache if you want or do other things with it. I basically do exactly what you want to do.

Kalam Over a year ago

And what error do you have now? The SQLException should not appear anymore because the root of that is that you are using query.addEntity(OrderPerUser.class). Have you removed that line? Have you made the OrderPerUser not an entity, but a normal object?

Ugur Artun Over a year ago

No I didnt remove. Btw if I change column value in query like this --> SELECT u.id, u.name, u.surname, c.cart_id, c.totalPrice. Exception change to Column 'cartId' not found. Is it about mapping problem ?

Kalam Over a year ago

You have to understand what ORM does and what ORM does not, before doing happy mappings all the way. In your case, entities are not a solution. You don't have a table represents a OrderPerUser. OrderPerUser is just in your application, a data transfer object, but not an entity. You need to stop mapping to ORM entities when it is not the case.

Per Huss · Accepted Answer · 2016-06-10 13:21:31Z

0

My guess is that your OrderPerUser class which you try to use for collecting the result is expecting a column with name id, and you have no such column in your query...

Try using the query:

SELECT u.id, c.cart_id, u.name, u.surname, c.totalPrice
FROM sandbox.cart c 
     JOIN sandbox.user u ON u.id = c.placedBy

edited Jun 10, 2016 at 13:21

answered Jun 10, 2016 at 13:05

Per Huss

5,12516 silver badges34 bronze badges

Collectives™ on Stack Overflow

Hibernate native sql join query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related