using MySQL by Hibernate : org.hibernate.exception.SQLGrammarException

Question

I am using Hibernate to access MySQL database for my java application. I configured Hibernate using this instructions .Now I want to have query to my database; using this code:

java.util.List categList = null;
SessionFactory sessionFacoryt ;
sessionFacoryt = NewHibernateUtil.getSessionFactory();
org.hibernate.classic.Session hiberanateSession = sessionFacoryt.getCurrentSession();
String HQL = "select r from RssCategory r";
Transaction transaction = hiberanateSession.beginTransaction();                
org.hibernate.Query q = hiberanateSession.createQuery(HQL);

// here is the Exception cause line  
categList = q.list();

as you see because of the last line; I see this Exception:

Oct 23, 2011 3:20:25 PM org.hibernate.util.JDBCExceptionReporter
logExceptions WARNING: SQL Error: 1064, SQLState: 42000 Oct 23, 2011
3:20:25 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '-IPTV-EPG.rss_category rsscategor0_' at line 1
org.hibernate.exception.SQLGrammarException: could not execute query at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)     at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)  at
org.hibernate.loader.Loader.list(Loader.java:2099)  at
org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:912) at
org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)  at
org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)    at
RSS.connectHtmlandleRSS.addTODB(connectHtmlandleRSS.java:206)   at
RSS.connectHtmlandleRSS.access$000(connectHtmlandleRSS.java:37)     at
RSS.connectHtmlandleRSS$1.run(connectHtmlandleRSS.java:163) Caused by:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
'-IPTV-EPG.rss_category rsscategor0_' at line 1     at
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at
java.lang.reflect.Constructor.newInstance(Constructor.java:513) at
com.mysql.jdbc.Util.handleNewInstance(Util.java:409)    at
com.mysql.jdbc.Util.getInstance(Util.java:384)  at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)  at
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)  at
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)  at
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)   at
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)    at
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)     at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113) at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275) at
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at 
org.hibernate.loader.Loader.getResultSet(Loader.java:1787)  at
org.hibernate.loader.Loader.doQuery(Loader.java:674)    at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at
org.hibernate.loader.Loader.doList(Loader.java:2220)

can anyone guide me for the cause of this exception ? thanks.

here is mapping generated for RssCategory; This class was generated to this package sakila.entity.

<hibernate-mapping>
    <class name="sakila.entity.RssCategory" table="rss_category" catalog="SAMIM-IPTV-EPG">
        <id name="catId" type="int">
            <column name="cat_id" />
            <generator class="assigned" />
        </id>
        <property name="catName" type="string">
            <column name="cat_name" length="45" not-null="true" unique="true" />
        </property>
        <set name="rssNewses" inverse="true" table="rss_to_cat">
            <key>
                <column name="cat_id" not-null="true" />
            </key>
            <many-to-many entity-name="sakila.entity.RssNews">
                <column name="rss_id" not-null="true" />
            </many-to-many>
        </set>
    </class> </hibernate-mapping>

and here is the SQL code generated for rss_category:

CREATE TABLE `rss_category` (
  `cat_id` int(11) NOT NULL,
  `cat_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`cat_id`),
  UNIQUE KEY `cat_name_UNIQUE` (`cat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and the HQL query generated by Hibernate is:

Hibernate: select rsscategor0_.cat_id as cat1_4_, rsscategor0_.cat_name as cat2_4_ from SAMIM-IPTV-EPG.rss_category rsscategor0_

Please show the generated SQL query, and the definition of the RssCategory table. Are you sure all the columns are there? — JB Nizet
– JB Nizet, Commented Oct 23, 2011 at 13:46
this is the SQL code generated for rss_category table. please see my edit in the ending of question. — sajad
– sajad, Commented Oct 23, 2011 at 13:53
yes, but what is the SQL query that Hibernate generates for your HQL query? — JB Nizet
– JB Nizet, Commented Oct 23, 2011 at 13:57
I added SQL query that Hibernate generates in the ending of my question; hoping that clarifies the problem! — sajad
– sajad, Commented Oct 23, 2011 at 14:09
I suspect the error is due to the catalog. Having dashes inside the catalog name is probably causing the problem. Try renaming the catalog, or avoiding it completely (if possible). — JB Nizet
– JB Nizet, Commented Oct 23, 2011 at 14:13

JB Nizet · Accepted Answer · 2011-10-23 14:34:00Z

1

The problem is certainly caused by the dashes in the catalog name. Try to change the name of the catalog to something else, without dashes.

answered Oct 23, 2011 at 14:34

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francisco Spaeth · Accepted Answer · 2011-10-23 12:51:02Z

0

I had some similar problems because I was not using the correct dialect (MySQL Diallect). Please try to configure the hibernate.dialect to org.hibernate.dialect.MySQL5Dialect.

answered Oct 23, 2011 at 12:51

Francisco Spaeth

24k8 gold badges72 silver badges107 bronze badges

1 Comment

sajad Over a year ago

Thank you but not effective! I had seen this instruction before but the output not differs...

ManuPK · Accepted Answer · 2011-10-23 13:50:58Z

0

Use the the below HQL,

String HQL = "from RssCategory"

If you want all the columns as a list of Object[] (or partial list of items) use the below,

String HQL = "select r.prop1, r.prop2 ... from RssCategory r"

edited Oct 23, 2011 at 13:50

answered Oct 23, 2011 at 13:35

ManuPK

11.9k10 gold badges60 silver badges77 bronze badges

5 Comments

sajad Over a year ago

Thank you, I tested your commands but the result was the same!

JB Nizet Over a year ago

@ManuPK : The original HQL request is perfectly fine. It's even a correct JPQL query, whereas your first one is not. Your second one is invalid HQL.

ManuPK Over a year ago

@JBNizet thanks. you were right and I have modified the answer.

ManuPK Over a year ago

@sajad Try connecting using the SQL eqivalent like select * from table(RssCategory) to see if the results are coming.

sajad Over a year ago

I tried executing 'select * from rss_category' and it was successful.

Collectives™ on Stack Overflow

using MySQL by Hibernate : org.hibernate.exception.SQLGrammarException

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related