org.hibernate.hql.ast.QuerySyntaxException: INFORMATION_SCHEMA.SYSTEM_ALIASES is not mapped.

[Simpson Kumar]

when I executed the a hibernate application to test, i got the above exception.

the code is:

   
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		     session.beginTransaction();
		         System.out.println("Obtaining Record");
		        // String SQL_QUERY ="select a.object_type from INFORMATION_SCHEMA.SYSTEM_ALIASES as a";
		         List result = session.createQuery("from INFORMATION_SCHEMA.SYSTEM_ALIASES").list();
		         for (int i = 0; i < result.size(); i++) {
		        	 SystemAliases theEvent = (SystemAliases) result.get(i);
		                System.out.println("SystemAliases: "+theEvent.getAlias() );
		            }

		         session.getTransaction().commit();

 

cfg filr

  
<hibernate-configuration>
	<session-factory>
		 <!-- Database connection settings -->
        <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
        <property name="connection.url">jdbc:hsqldb:.</property>
        <property name="connection.username">sa</property>
        <property name="connection.password"></property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">2</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.HSQLDialect</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        
        <property name="hibernate.current_session_context_class">thread</property>
        
        <mapping resource="SystemAliases.hbm.xml"/>
	</session-factory>
</hibernate-configuration>

  

pojo:

public class SystemAliases {
	private Integer id;
	private String alias;
	private String alias_cat;
	private String alias_schem;
	private String object_cat;
	private String object_name;
	private String object_schem;
	private String object_type;
//setters and getteres
    

 
<hibernate-mapping>
  
  <class name="SystemAliases" table="SYSTEM_ALIASES">
        
    <id name="id">
          <generator class="native"/>
     </id>

    <property name="alias" column="ALIAS" />
    <property name="alias_cat" column="ALIAS_CAT" />
    <property name="alias_schem" column="ALIAS_SCHEM" />
    <property name="object_cat" column="OBJECT_CAT" />
    <property name="object_name" column="OBJECT_NAME" />
    <property name="object_schem" column="OBJECT_SCHEM" />
    <property name="object_type" column="OBJECT_TYPE" />
  </class>
</hibernate-mapping> 
   

here in my DB, we don.t have the id column, that's why we are getting the problem? in hibernate we must needed the id attribute, but in our table it doesn't, The error is

 



org.hibernate.hql.ast.QuerySyntaxException: INFORMATION_SCHEMA.SYSTEM_ALIASES is not mapped. [from INFORMATION_SCHEMA.SYSTEM_ALIASES]
	at org.hibernate.hql.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:157)
	at org.hibernate.hql.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:86)
	at org.hibernate.hql.ast.tree.FromClause.addFromElement(FromClause.java:70)
	at org.hibernate.hql.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:263)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3049)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2938)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
	at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:134)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:113)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1602)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
	at $Proxy0.createQuery(Unknown Source)
	at HibernateTest.main(HibernateTest.java:24)
Caused by:  INFORMATION_SCHEMA.SYSTEM_ALIASES is not mapped.
	... 26 more
   

How to avoid this problem?

[Reehan Lalkhanwar]

H.E.M. (Hibernate Entity Manager) prefixes the schema name of accessed database tables to that of the connecting user. For example, if my database account username is MICKEYMOUSE, and I connect a database and then all tables that I try to access will be accessed as MICKEYMOUSE.*.
So try removing the "INFORMATION_SCHEMA" schema name from the query.

[Simpson Kumar]

I tried in that by removing schema too, but I get the same exception

  


org.hibernate.hql.ast.QuerySyntaxException: SYSTEM_ALIASES is not mapped. [from SYSTEM_ALIASES]
	at org.hibernate.hql.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:157)
	at org.hibernate.hql.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:86)
	at org.hibernate.hql.ast.tree.FromClause.addFromElement(FromClause.java:70)
	at org.hibernate.hql.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:263)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3049)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2938)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
	at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:134)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:113)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1602)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
	at $Proxy0.createQuery(Unknown Source)
	at HibernateTest.main(HibernateTest.java:23)
Caused by:  SYSTEM_ALIASES is not mapped.
	... 26 more

  

[Paul Sturrock]

Are you connecting using a user that can see this table? Are they in the same schema, or has the table been made available via another mechanism (e.g. synonyms)?

[Simpson Kumar]

Paul Sturrock wrote:Are you connecting using a user that can see this table? Are they in the same schema, or has the table been made available via another mechanism (e.g. synonyms)?

I'm using HSQL Database Engine and these tables are already predefined with JBOSS Dev studio. They are in the same schema too.

[Paul Sturrock]

They are in the same schema too.

So when you log in to the database using a normal SQL client and the user details hibenrate is using to connect you can see this object?

[Simpson Kumar]

Paul Sturrock wrote:

They are in the same schema too.

So when you log in to the database using a normal SQL client and the user details hibenrate is using to connect you can see this object?

Yes, Before I start using hibernate, I used JDBC code to test and I got the results too

[Paul Sturrock]

Sorry - I'm a bit slow today.

Objects in HQL are case sensitive. You've mapped SystemAliases, not SYSTEM_ALIASES.

[Simpson Kumar]

Paul Sturrock wrote:Sorry - I'm a bit slow today.

Objects in HQL are case sensitive. You've mapped SystemAliases, not SYSTEM_ALIASES.

Hey Paul, I got idea and changed to "from SystemAliases"

  
List result = session.createQuery("from SystemAliases").list();
  

I didn't find exception but I didn't find the results, in DB there are.
and the query is look like I printed from console.

  

Hibernate: select systemalia0_.id as id0_, systemalia0_.ALIAS as ALIAS0_, systemalia0_.ALIAS_CAT as ALIAS3_0_, systemalia0_.ALIAS_SCHEM as ALIAS4_0_, systemalia0_.OBJECT_CAT as OBJECT5_0_, systemalia0_.OBJECT_NAME as OBJECT6_0_, systemalia0_.OBJECT_SCHEM as OBJECT7_0_, systemalia0_.OBJECT_TYPE as OBJECT8_0_ from SYSTEM_ALIASES systemalia0_  

Here I got a doubt, actually there is no id in the table (SYSTEM_ALIASES), but I put the id tag in the hbm file as it is recommended. Then generally it supposed to throw an SQL error as Im trying to fetch id form the table. why it is not throwing error?

[Paul Sturrock]

Here I got a doubt, actually there is no id in the table (SYSTEM_ALIASES), but I put the id tag in the hbm file as it is recommended.

Not just recommended, its mandatory (unless you are using a composite-id).