[Solved] Hibernate : why createQuery() appends package name to the Entity Name?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Hibernate : why createQuery() appends package name to the Entity Name?

Database type:

I am trying to query database from two Different Tables i.e.

query = "select rbt.rbtCode, rbt.maskedName, cmas.catId, cmas.maskedName " +
    "from CrbtRbt rbt, CrbtCategoryMaster cmas " +
    "where rbt.playable='Y' and rbt.showOnWeb='Y' and rbt.rbtCode!=0 " +
    "and rbt.catId=cmas.catId";

but when I pass this query through session.createQuery(query);

It appends package name to the next in coming Entity Name i.e.

select rbt.rbtCode, rbt.maskedName, cmas.catId, cmas.maskedName
from CrbtRbt rbt, com.telemune.toolGeneratedPojos.CrbtCategoryMaster cmas
where rbt.playable='Y' and rbt.showOnWeb='Y' and rbt.rbtCode!=0 and rbt.catId=cmas.catId

and gives the following Exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: CrbtRbt is not mapped [select rbt.rbtCode, rbt.maskedName, cmas.catId, cmas.maskedName from CrbtRbt rbt, com.telemune.toolGeneratedPojos.CrbtCategoryMaster cmas where rbt.playable='Y' and rbt.showOnWeb='Y' and rbt.rbtCode!=0 and rbt.catId=cmas.catId]
at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:180)
at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:110)
at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:93)
at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:325)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3252)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3141)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:694)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:550)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:287)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:235)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:248)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:119)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:214)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:192)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1537)
at com.telemune.generator.TestQuery.select(TestQuery.java:114)
at com.telemune.generator.PojoGenerator.main(PojoGenerator.java:191)

But if I query the Entities One By One it shows the desired result successfully.

i.e.

select rbt.rbtCode, rbt.maskedName
from CrbtRbt rbt
where rbt.playable='Y' and rbt.showOnWeb='Y' and rbt.rbtCode!=0;

gives desired result.

Can anyone Explain what I can do?

here is some example code: Query Code

 hql = session.createQuery(query);
    hql.setMaxResults( querySchema.getMaxRes() );
    list=hql.list();

For information I have done All the Mappings and included all the libraries very carefully no chance of any mistake.

UPDATE

As I said I have done the mappings carefully but someone may have doubts so here is the mapping:

<hibernate-mapping>
    <class name="com.telemune.toolGeneratedPojos.CrbtCategoryMaster" schema="SDP" table="CRBT_CATEGORY_MASTER">
            <id name="catId" type="java.lang.Integer">
                <column name="CAT_ID" precision="4" scale="0" />
                <generator class="assigned" />
            </id>
            <property column="SHOW_IN_SMS" name="showInSms" type="java.lang.String" />
            <property column="SHOW_ON_WEB" name="showOnWeb" type="java.lang.String" />
            <property column="PLAYABLE" name="playable" type="java.lang.String" />
            <property column="STATUS" name="status" type="java.lang.String" />
            <property column="IMAGE_PATH" name="imagePath" type="java.lang.String" />
            <property column="DESCRIPTION" name="description" type="java.lang.String" />
            <property column="MASKED_NAME" name="maskedName" type="java.lang.String" />
            <property column="IVR_FILEPATH_1" name="ivrFilepath1" type="java.lang.String" />
            <property column="IVR_FILEPATH" name="ivrFilepath" type="java.lang.String" />
            <property column="MASKED_NAME_1" name="maskedName1" type="java.lang.String" />
    </class></hibernate-mapping>

and for CRBT_RBT:

<hibernate-mapping>
    <class name="com.telemune.toolGeneratedPojos.CrbtRbt" schema="SDP" table="CRBT_RBT">
            <id name="rbtCode" type="java.lang.Integer">
                <column name="RBT_CODE" precision="10" scale="0" />
                <generator class="assigned" />
            </id>
            <property column="PLAYABLE" name="playable" type="java.lang.String" />
            <property column="OTHER" name="other" type="java.lang.Integer" />
            <property column="IMAGE_PATH" name="imagePath" type="java.lang.String" />
            <property column="RBT_ORDER" name="rbtOrder" type="java.lang.Integer" />
            <property column="VALIDITY_PERIOD" name="validityPeriod" type="java.lang.Integer" />
            <property column="LYRICIST" name="lyricist" type="java.lang.String" />
            <property column="APPROVED_BY" name="approvedBy" type="java.lang.String" />
            <property column="PREV_CAT_ID" name="prevCatId" type="java.lang.Integer" />
            <property column="RELEASE_YEAR" name="releaseYear" type="java.lang.Integer" />
            <property column="COMPOSER" name="composer" type="java.lang.String" />
            <property column="SHOW_ON_WEB" name="showOnWeb" type="java.lang.String" />
            <property column="MASKED_NAME" name="maskedName" type="java.lang.String" />
            <property column="CONTENT_PROVIDER_CODE" name="contentProviderCode" type="java.lang.Integer" />
            <property column="CREATE_DATE" name="createDate" type="java.sql.Date" />
            <property column="IVR_FILEPATH" name="ivrFilepath" type="java.lang.String" />
            <property column="CORP_ID" name="corpId" type="java.lang.Integer" />
            <property column="SHOW_IN_SMS" name="showInSms" type="java.lang.String" />
            <property column="ALBUM_NAME" name="albumName" type="java.lang.String" />
            <property column="NOKIA" name="nokia" type="java.lang.Integer" />
            <property column="STATUS" name="status" type="java.lang.String" />
            <property column="FILE_PATH" name="filePath" type="java.lang.String" />
            <property column="REFERENCE_ID" name="referenceId" type="java.lang.Integer" />
            <property column="CHARGING_CODE" name="chargingCode" type="java.lang.Integer" />
            <property column="RBT_NICK" name="rbtNick" type="java.lang.String" />
            <property column="APPROVAL_DATE" name="approvalDate" type="java.sql.Date" />
            <property column="CAT_ID" name="catId" type="java.lang.Integer" />
            <property column="RBT_SCORE" name="rbtScore" type="java.lang.Integer" />
            <property column="ARTIST_NAME" name="artistName" type="java.lang.String" />
    </class>
</hibernate-mapping>

I don't think there is any mistake in the mapping.

Hibernate.cfg.xml :

<hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
    <property name="hibernate.connection.password">sdp</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@10.168.2.127:1521:mastera</property>
    <property name="hibernate.connection.username">sdp</property>
    <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
    <mapping resource="com/telemune/toolGeneratedPojos/CrbtRbt.hbm.xml"/>
    <mapping resource="com/telemune/toolGeneratedPojos/CrbtCategoryMaster.hbm.xml"/>
</session-factory>
</hibernate-configuration>

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX crbtcategorymaster_idx_catid ON CrbtCategoryMaster (catId);
CREATE INDEX crbtrbt_idx_playabl_showonw_catid_rbtcode ON CrbtRbt (playable,showOnWeb,catId,rbtCode);
The optimized query:
SELECT
        rbt.rbtCode,
        rbt.maskedName,
        cmas.catId,
        cmas.maskedName 
    FROM
        CrbtRbt rbt,
        com.telemune.toolGeneratedPojos.CrbtCategoryMaster cmas 
    WHERE
        rbt.playable = 'Y' 
        AND rbt.showOnWeb = 'Y' 
        AND rbt.rbtCode != 0 
        AND rbt.catId = cmas.catId

Related Articles



* original question posted on StackOverflow here.