Kumar Aditya Kumar Aditya - 11 days ago 10
Java Question

"show databases" in HQL Hibernate

I am trying to list all databases in MySQL using Hibernate. But I am getting below error.

Nov 24, 2016 11:50:27 AM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: line 1:1: unexpected token: show
Nov 24, 2016 11:50:27 AM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: line 1:1: unexpected token: show
line 1:1: unexpected token: show
at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:191)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:295)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1796)
at TestHibernate.main(TestHibernate.java:40)


Below is my code



Configuration cfgObj = new Configuration();
cfgObj.configure("MySQLhibernate.cfg.xml");
servcRegObj = (ServiceRegistry) new StandardServiceRegistryBuilder().applySettings(cfgObj.getProperties()).build();
SessionFactory sfObj = cfgObj.buildSessionFactory(servcRegObj);

Session session = sfObj.openSession();
BSCTable bscTblObj = new BSCTable();

Query qry = session.createQuery("show databases");
List<Object> databaseList = qry.list();
for(Object object : databaseList)
{
System.out.println(object);
}
session.close();
sfObj.close();

Answer

show databases is not a valid JPQL statement and dependent on the database you are using. You can use a native query session.createSQLQuery(...) to talk to the database directly, but in this case, you will lose the Hibernate advantage of database independent implementation.

Comments