CODEWITHSUNDEEP
javamysqlsqlhibernate
Ugur Artun
Ugur Artun

Reputation: 1804

Hibernate native sql join query

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; }

Upvotes: 1

Views: 7498

Answers (2)

Kalam
Kalam

Reputation: 46

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.

Upvotes: 1

Per Huss
Per Huss

Reputation: 5105

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

Upvotes: 0

Related Questions