CODEWITHSUNDEEP
hibernate
Prasanth
Prasanth

Reputation: 1014

Using place holders in Hibernate Native SQL query

I have an entity Book which holds a many-to-one relation with a Publisher. Here is the hibernate mapping file for Book

<hibernate-mapping package="com.pramati.model">
    <class name="Book" table="BOOK">
        <id name="id" column="ID" type="long">
            <generator class="native"/>
        </id>
        <property name="name" column="NAME" type="string"/>
        <property name="isbn" type="int">
            <column name="ISBN" unique="true" not-null="true"/>
        </property>
        <property name="price" column="PRICE" type="double"/>
        <property name="bookIndex" column="BOOK_INDEX" type="int"/>
        <many-to-one name="publisher" class="Publisher" column="PUBLISHER_ID" cascade="save-update,delete"/>
    </class>
</hibernate-mapping>

Here is the native query I am using:

String sql = "SELECT {b.*}  FROM BOOK book LEFT OUTER JOIN PUBLISHER pub ON book.PUBLISHER_ID = pub.ID  WHERE book.price > 100 ";
Query query = session.createSQLQuery(sql).addEntity("b", Book.class);
List list = query.list();

On executing this, I am getting the following exception:

20:39:49,438 DEBUG SQL:401 - SELECT b.ID as ID0_0_, b.NAME as NAME0_0_, b.ISBN as ISBN0_0_, b.PRICE as PRICE0_0_, b.BOOK_INDEX as BOOK5_0_0_, b.PUBLISHER_ID as PUBLISHER6_0_0_ FROM BOOK book LEFT OUTER JOIN PUBLISHER pub ON book.PUBLISHER_ID = pub.ID WHERE book.price > 100 
20:39:49,466  WARN JDBCExceptionReporter:77 - SQL Error: 1054, SQLState: 42S22
20:39:49,467 ERROR JDBCExceptionReporter:78 - Unknown column 'b.ID' in 'field list'
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:2223)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
    at com.pramati.model.Publisher.main(Publisher.java:95)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'b.ID' in 'field list'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Why is this happening? If i just replace the alias as 'book' in addEntity and try fetching it using 'select {book.*} ..' (i.e. using the same alias name in SQL and in the addEntity method), it is working fine.

Upvotes: 1

Views: 2083

Answers (1)

sblundy
sblundy

Reputation: 61434

You should be using {book.*}. Otherwise SQL driver don't know what in the query it refers to.

If you look at the caused by, you see that it's the mysql driver that's throwing the exception. Hibernate is duly replacing {b.*} with b.ID, ..., but the driver doesn't know what in the FROM clause the fields map to.

Upvotes: 2

Related Questions