java hibernate query in xml

hi guys

I am calling query using hibernate named query.
In my Postgres database i have schema epro and created table PROJECT under that schema.
Now In my xml file i have the query


like this
<hibernate-mapping>
<query name ="PROJECT_DETAILS">
select distinct project from epro.PROJECT project where project.projectId=:projectId
 </query>
but when server starts the query is parsed and i get this error
org.hibernate.hql.ast.QuerySyntaxException: epro.PROJECT is not mapped [
            select distinct project from epro.PROJECT project where project.projectId=:projectId
      ]
      at org.hibernate.hql.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:181)
      at org.hibernate.hql.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:110)

any idea whats going wrong?
thanks
royjaydAsked:
Who is Participating?
 
Gurvinder Pal SinghCommented:
0
 
Gurvinder Pal SinghCommented:
Do you have a mapping file PROJECT.hbm.xml corresponding to this erpo.PROJECT class?

have you added

<mapping resource="erpo/PROJECT.hbm.xml"/>

to hibernate.cfg.xml?

0
 
Sathish David Kumar NArchitectCommented:
>>>>> is this epro.PROJECT  ur pojo class mapping with ur HBM and table ?

PROJECT   try to cahnge the name of the and check !!
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
royjaydAuthor Commented:
I am new to hinernate and still learning.
Here is what i have so far.

The above issue seems to be resolved. I made sure i have PROJECT.hbm.xml file  like gurvinder mentioned and it worked.

Now in my xml file i have two queries
1. within <sql-query.. tags
2. within <query name...tags



<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Oct 7, 2010 1:54:20 AM by Hibernate Tools 3.3.0.GA -->
<hibernate-mapping>
<sql-query name="USER_PROJECTS"><![CDATA[            
select distinct project.* from PROJECT left outer join PROJECT_USERS puser  -- this throws erroron project.project_id=puser.project_id where puser.user_id=:userId
]]>
<return alias="projects" class="com.hibernate.entity.Project"/>
</sql-query>

<query name ="PROJECT_DETAILS">  -- This works fine now
select distinct project from epro.PROJECT project where project.projectId=:projectId
</query>
</hibernate-mapping>

2. PROJECT.hbm.xml
?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Mar 1, 2011 5:29:16 PM by Hibernate Tools 3.3.0.GA -->
<hibernate-mapping>
    <class name="com.hibernate.entity.Project" table="PROJECT" schema="ePro" >
        <id name="projectId" type="int">
            <column name="PROJECT_ID" />
            <generator class="assigned" />
        </id>

I have postgres database in the backend. The schema name is ePro (case-sensitive), that is why i included schema attribute as ePro in the hbm file.
And here is the error i get

[3/31/11 17:39:55:140 EDT] 00000011 SystemOut     O 17:39:55,140 [WebContainer : 0] DEBUG SQL - 
    /* named native SQL query GET_USER_PROJECTS */ select
        distinct project.* 
    from
        PROJECT 
    left outer join
        PROJECT_USERS puser    
            on project.project_id=puser.project_id 
    where
        puser.user_id=?
[3/31/11 17:39:55:140 EDT] 00000011 SystemOut     O Hibernate: 
    /* named native SQL query GET_USER_PROJECTS */ select
        distinct project.* 
    from
        PROJECT 
    left outer join
        PROJECT_USERS puser    
            on project.project_id=puser.project_id 
    where
        puser.user_id=?
[3/31/11 17:39:55:156 EDT] 00000011 SystemOut     O 17:39:55,156 [WebContainer : 0] WARN  JDBCExceptionReporter - SQL Error: 0, SQLState: 42P01
[3/31/11 17:39:55:156 EDT] 00000011 SystemOut     O 17:39:55,156 [WebContainer : 0] ERROR JDBCExceptionReporter - ERROR: relation "project" does not exist
  Position: 79
[3/31/11 17:39:55:156 EDT] 00000011 SystemOut     O 17:39:55,156 [WebContainer : 0] ERROR ProjectDao - could not execute query
[3/31/11 17:39:55:171 EDT] 00000011 SystemErr     R org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)

Open in new window


It says "project" does not exist. Is it not able to find PROJECT table ?

Banging my head for the last 24 hours. Any help will be appreciated

thanks
0
 
royjaydAuthor Commented:
looks like i messed up the query , corrected the query to

<sql-query name="USER_PROJECTS"><![CDATA[            
select distinct project.* from PROJECT project left outer join PROJECT_USERS puser on project.project_id=puser.project_id where puser.user_id=:userId
]]>
<return alias="projects" class="com.hibernate.entity.Project"/>
</sql-query>


still get same error:  ERROR: relation "project" does not exist
Is it not finding the schema ?


thanks
0
 
royjaydAuthor Commented:
ok, it worked finally, it wasnt picking up the schema, when i give like this it works

<sql-query name="USER_PROJECTS">        
select distinct project.* from "ePro"."PROJECT" project left outer join  
"ePro"."PROJECT_USERS"  puser on project.project_id=puser.project_id where puser.user_id=:userId

<return alias="projects" class="com.hibernate.entity.Project"/>
</sql-query>  

"ePro"."PROJECT"   --> ePro is schema, PROJECT is the table. but i dont want to hard code the schema here. Can anyone tell me how i can include the schema in properties file or config file?

thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.