CODEWITHSUNDEEP
javahibernateoracle11g
Ginja Ninja
Ginja Ninja

Reputation: 415

Invalid Column Name error with Hibernate createSQLquery

This is a smaller version of a larger query I'm trying to get working. When I run the query I get the following error. I've tried removing as much of the query as I can to try to find the guilty column, but haven't had any success. I'm using Spring 3.2.1, Hibernate 3.6, and Oracle 11g

Caused by: java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)
    at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2763)
    at oracle.jdbc.driver.OracleResultSet.getBigDecimal(OracleResultSet.java:368)
    at org.hibernate.type.descriptor.sql.DecimalTypeDescriptor$2.doExtract(DecimalTypeDescriptor.java:62)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)

Query:

String query = "SELECT d.ID as {d.id}, d.DETAIL_ORDER as {d.detailOrder}, g.ID as {g.id}, g.NAME as {g.name} " +
    "FROM EVAL_MASTER_EVAL_DETAIL d " +
    "JOIN EVAL_QUESTION_GROUP g ON d.GROUP_ID = g.ID " +
    "WHERE d.ACTIVE = 'Y' " +
    "AND d.MASTER_EVAL_ID =  :evalId" + 
    " ORDER BY d.DETAIL_ORDER ASC"; 

    details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class)
    .addEntity("g", EvalQuestionGroup.class).setParameter("evalId", evalId).list();

Resulting Hibernate Query:

Hibernate: SELECT d.ID as ID29_0_, d.DETAIL_ORDER as DETAIL5_29_0_, g.ID as ID34_1_, g.NAME as NAME34_1_ FROM EVAL_MASTER_EVAL_DETAIL d JOIN EVAL_QUESTION_GROUP g ON d.GROUP_ID = g.ID WHERE d.ACTIVE = 'Y' AND d.MASTER_EVAL_ID =  ? ORDER BY d.DETAIL_ORDER ASC

Mappings (to verify names)

<hibernate-mapping>
        <class name="org.anes.surveys.domain.EvalMasterEvalDetail" table="EVAL_MASTER_EVAL_DETAIL">
            <id name="id" type="big_decimal">
                <column name="ID" precision="22" scale="0" />
                <generator class="sequence-identity" >
                    <param name="sequence">EVAL_MASTER_EVAL_DETAIL_SEQ</param>
                </generator>
            </id>
            <many-to-one name="evalQuestionGroup" class="org.anes.surveys.domain.EvalQuestionGroup" fetch="select">
                <column name="GROUP_ID" precision="22" scale="0" />
            </many-to-one>
        <many-to-one name="evalMasterEvaluation" class="org.anes.surveys.domain.EvalMasterEvaluation" fetch="select">
        <column name="MASTER_EVAL_ID" precision="22" scale="0" not-null="true" />
    </many-to-one>
            <property name="detailOrder" type="big_decimal">
                <column name="DETAIL_ORDER" precision="22" scale="0" not-null="true" />
            </property>
            <property name="active" type="string">
                <column name="ACTIVE" length="1" not-null="true" />
            </property>
        </class>
    </hibernate-mapping>

<hibernate-mapping>
    <class name="org.anes.surveys.domain.EvalQuestionGroup" table="EVAL_QUESTION_GROUP">
        <id name="id" type="big_decimal">
            <column name="ID" precision="22" scale="0" />
            <generator class="sequence-identity" >
                <param name="sequence">EVAL_QUESTION_GROUP_SEQ</param>
            </generator>
        </id>
        <property name="name" type="string">
            <column name="NAME" not-null="true" />
        </property>
        <set name="evalMasterEvalDetails" table="EVAL_MASTER_EVAL_DETAIL" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="GROUP_ID" precision="22" scale="0" />
            </key>
            <one-to-many class="org.anes.surveys.domain.EvalMasterEvalDetail" />
        </set>
    </class>
</hibernate-mapping>

EDIT: Tried the following and still get invalid column error. ACTIVE & d.active are strings.

String query = "SELECT ACTIVE as {d.active} " +
                "FROM EVAL_MASTER_EVAL_DETAIL";
details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class).list();

String query = "SELECT d.ID " +
        "FROM EVAL_MASTER_EVAL_DETAIL d ";
details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class).list();

String query = "SELECT ID " +
        "FROM EVAL_MASTER_EVAL_DETAIL";
details = session.createSQLQuery(query).addEntity("d", EvalMasterEvalDetail.class).list();

This works, but I only get scalar values:

String query = "SELECT ID " +
        "FROM EVAL_MASTER_EVAL_DETAIL";
details = session.createSQLQuery(query).list();

Upvotes: 1

Views: 14302

Answers (2)

Ginja Ninja
Ginja Ninja

Reputation: 415

Here's what I ended up doing.

-I used HQL instead of my native SQL (reference the object fields instead of db columns)

-Created a new domain class with getters and setters to define/hold the results of my query (class contains fields from 5 different objects/tables)

public class EvalForm {
    private BigDecimal detailId;
    private BigDecimal detailOrder;
    private BigDecimal groupId;
    private String groupName;
}

-Used my new class' field names as the column aliases 

String query = "SELECT d.id as detailId, d.detailOrder as detailOrder, g.id as groupId, g.name as groupName....

-Added setResultTransformer to point to my new class

details = session.createQuery(query).setParameter("evalId", evalId).setResultTransformer(Transformers.aliasToBean(EvalForm.class)).list();

The result? Perfect for JSON output.

[{"detailId":44,"detailOrder":0,"groupId":128,"groupName":"My dope name"},{"detailId":42,"detailOrder":1,"groupId":68,"groupName":"qGroup AJAX"},{"detailId":81,"detailOrder":2,"groupId":68,"groupName":"qGroup AJAX"}]

Upvotes: 4

Bohemian
Bohemian

Reputation: 425033

Remove all column aliases. Ie change:

d.ID as {d.id}

to just

d.ID

etc.

I've never seen such HQL syntax. It might be valid, but it's certainly unnecessary.

Upvotes: 0

Related Questions