4

I am pretty new to hibernate and I am stuck at this exception. As far as my understanding is, this should not happen if my dialect is correct and I am quiet sure I am using the correct dialect i.e org.hibernate.dialect.MySQLDialect, still I tried with org.hibernate.dialect.MySQLInnoDBDialect too but it still gives the same exception. I am using spring 3.1.0 with hibernate 3 and mysql 5. The dao function that is causing this error is:

@SuppressWarnings("unchecked")
protected List<T> findByCriteria(Criterion... criterion) {

    Criteria crit = getSession().createCriteria(getPersistentClass())
            .addOrder(Order.asc(getDefaultProperty()));
    for (Criterion c : criterion) {
        crit.add(c);
    }
    List<T> critList=crit.list();
    return critList;
}

I have searched a lot, and have pretty much hit a wall as to possible problems. So any help would be greatly appreciated.

EDIT Full stacktrace

I have enabled logging and posting the part that I think has the problem

14:52:26,891 DEBUG HibernateTransactionManager:504 - Preparing JDBC Connection of Hibernate Session [org.hibernate.impl.SessionImpl@7889fe65]
14:52:26,901 DEBUG HibernateTransactionManager:569 - Exposing Hibernate transaction as JDBC transaction [jdbc:mysql://localhost:3306/cricket_calendar, UserName=root@localhost, MySQL-AB JDBC Driver]
14:52:26,901 DEBUG TransactionSynchronizationManager:193 - Bound value [org.springframework.jdbc.datasource.ConnectionHolder@7378aae2] for key [org.apache.commons.dbcp.BasicDataSource@753ef605] to thread [http-bio-8080-exec-4]
14:52:26,901 DEBUG TransactionSynchronizationManager:193 - Bound value [org.springframework.orm.hibernate3.SessionHolder@771b16a7] for key [org.hibernate.impl.SessionFactoryImpl@426abd0b] to thread [http-bio-8080-exec-4]
14:52:26,901 DEBUG TransactionSynchronizationManager:272 - Initializing transaction synchronization
14:52:26,902 DEBUG TransactionInterceptor:362 - Getting transaction for [com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findAll]
14:52:26,902 DEBUG TransactionSynchronizationManager:140 - Retrieved value [org.springframework.orm.hibernate3.SessionHolder@771b16a7] for key [org.hibernate.impl.SessionFactoryImpl@426abd0b] bound to thread [http-bio-8080-exec-4]
Hibernate: select this_.id as id14_10_, this_.country1_id as country4_14_10_, this_.country2_id as country5_14_10_, this_.end_date as end2_14_10_, this_.ground_id as ground6_14_10_, this_.series_id as series7_14_10_, this_.start_date as start3_14_10_, this_.umpire1_id as umpire8_14_10_, this_.umpire2_id as umpire9_14_10_, this_.umpire3rd_id as umpire10_14_10_, this_.umpireReserve_id as umpireR11_14_10_, country2_.id as id17_0_, country2_.abbreviation as abbrevia2_17_0_, country2_.name as name17_0_, country2_.status_id as status4_17_0_, cricketsta3_.id as id18_1_, cricketsta3_.description as descript2_18_1_, cricketsta3_.display_name as display3_18_1_, country4_.id as id17_2_, country4_.abbreviation as abbrevia2_17_2_, country4_.name as name17_2_, country4_.status_id as status4_17_2_, ground5_.id as id15_3_, ground5_.country_id as country4_15_3_, ground5_.description as descript2_15_3_, ground5_.name as name15_3_, country6_.id as id17_4_, country6_.abbreviation as abbrevia2_17_4_, country6_.name as name17_4_, country6_.status_id as status4_17_4_, series7_.id as id19_5_, series7_.description as descript2_19_5_, series7_.end_date as end3_19_5_, series7_.name as name19_5_, series7_.start_date as start5_19_5_, umpire8_.id as id16_6_, umpire8_.name as name16_6_, umpire9_.id as id16_7_, umpire9_.name as name16_7_, umpire10_.id as id16_8_, umpire10_.name as name16_8_, umpire11_.id as id16_9_, umpire11_.name as name16_9_ from match this_ left outer join ref_country country2_ on this_.country1_id=country2_.id left outer join ref_cricket_status cricketsta3_ on country2_.status_id=cricketsta3_.id left outer join ref_country country4_ on this_.country2_id=country4_.id left outer join ref_ground ground5_ on this_.ground_id=ground5_.id left outer join ref_country country6_ on ground5_.country_id=country6_.id left outer join series series7_ on this_.series_id=series7_.id left outer join ref_umpire umpire8_ on this_.umpire1_id=umpire8_.id left outer join ref_umpire umpire9_ on this_.umpire2_id=umpire9_.id left outer join ref_umpire umpire10_ on this_.umpire3rd_id=umpire10_.id left outer join ref_umpire umpire11_ on this_.umpireReserve_id=umpire11_.id order by this_.id asc
14:52:26,927 DEBUG TransactionInterceptor:406 - Completing transaction for [com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findAll] after exception: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,928 DEBUG RuleBasedTransactionAttribute:130 - Applying rules to determine whether transaction should rollback on org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,928 DEBUG RuleBasedTransactionAttribute:147 - Winning rollback rule is: null
14:52:26,928 DEBUG RuleBasedTransactionAttribute:152 - No relevant rollback rule found: applying default rules
14:52:26,928 DEBUG HibernateTransactionManager:935 - Triggering beforeCompletion synchronization
14:52:26,928 DEBUG HibernateTransactionManager:843 - Initiating transaction rollback
14:52:26,928 DEBUG HibernateTransactionManager:672 - Rolling back Hibernate transaction on Session [org.hibernate.impl.SessionImpl@7889fe65]
14:52:26,929 DEBUG HibernateTransactionManager:964 - Triggering afterCompletion synchronization
14:52:26,930 DEBUG TransactionSynchronizationManager:331 - Clearing transaction synchronization
14:52:26,930 DEBUG TransactionSynchronizationManager:243 - Removed value [org.springframework.orm.hibernate3.SessionHolder@771b16a7] for key [org.hibernate.impl.SessionFactoryImpl@426abd0b] from thread [http-bio-8080-exec-4]
14:52:26,930 DEBUG TransactionSynchronizationManager:243 - Removed value [org.springframework.jdbc.datasource.ConnectionHolder@7378aae2] for key [org.apache.commons.dbcp.BasicDataSource@753ef605] from thread [http-bio-8080-exec-4]
14:52:26,931 DEBUG HibernateTransactionManager:734 - Closing Hibernate Session [org.hibernate.impl.SessionImpl@7889fe65] after transaction
14:52:26,931 DEBUG SessionFactoryUtils:793 - Closing Hibernate Session
14:52:26,934 DEBUG ExceptionHandlerExceptionResolver:132 - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.emumba.cricketcalendar.web.CalendarController.home()]: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,935 DEBUG ResponseStatusExceptionResolver:132 - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.emumba.cricketcalendar.web.CalendarController.home()]: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,935 DEBUG DefaultHandlerExceptionResolver:132 - Resolving exception from handler [public org.springframework.web.servlet.ModelAndView com.emumba.cricketcalendar.web.CalendarController.home()]: org.hibernate.exception.SQLGrammarException: could not execute query
14:52:26,936 DEBUG DispatcherServlet:905 - Cleared thread-bound request context: org.apache.catalina.connector.RequestFacade@3569be56
14:52:26,937 DEBUG DispatcherServlet:910 - Could not complete request
org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2545)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    at com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findByCriteria(GenericDAOHibernate.java:114)
    at com.emumba.cricketcalendar.dao.hibernate.GenericDAOHibernate.findAll(GenericDAOHibernate.java:75)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy26.findAll(Unknown Source)
    at com.emumba.cricketcalendar.service.CalendarManagerImpl.getAllMatches(CalendarManagerImpl.java:25)
    at com.emumba.cricketcalendar.web.CalendarController.home(CalendarController.java:25)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
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 'match this_ left outer join ref_country country2_ on this_.country1_id=country2_' at line 1
    at sun.reflect.GeneratedConstructorAccessor12.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2542)
    ... 56 more
14:52:26,941 DEBUG XmlWebApplicationContext:322 - Publishing event in WebApplicationContext for namespace 'appServlet-servlet': ServletRequestHandledEvent: url=[/cricket_calendar/calendar.do]; client=[0:0:0:0:0:0:0:1]; method=[GET]; servlet=[appServlet]; session=[null]; user=[null]; time=[60ms]; status=[failed: org.hibernate.exception.SQLGrammarException: could not execute query]
14:52:26,941 DEBUG XmlWebApplicationContext:322 - Publishing event in Root WebApplicationContext: ServletRequestHandledEvent: url=[/cricket_calendar/calendar.do]; client=[0:0:0:0:0:0:0:1]; method=[GET]; servlet=[appServlet]; session=[null]; user=[null]; time=[60ms]; status=[failed: org.hibernate.exception.SQLGrammarException: could not execute query]
14:52:26,942 DEBUG TransactionSynchronizationManager:243 - Removed value [org.springframework.orm.hibernate3.SessionHolder@611cfa21] for key [org.hibernate.impl.SessionFactoryImpl@5df86e79] from thread [http-bio-8080-exec-4]
14:52:26,942 DEBUG OpenSessionInViewFilter:207 - Closing single Hibernate Session in OpenSessionInViewFilter
14:52:26,942 DEBUG SessionFactoryUtils:793 - Closing Hibernate Session
Improve this question
1
  • Which version of MySQL are you using? Could you also post the full stacktrace please? Commented Jan 18, 2012 at 9:43

1 Answer 1

Reset to default
9

First of all, your Dialect is wrong. For MySQL 5, you need to use org.hibernate.dialect.MySQL5Dialect. MySQL 5 InnoDB dialect is org.hibernate.dialect.MySQL5InnoDBDialect.

This exception clearly tells you the cause of your error:

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 'match this_ left outer join ref_country country2_ on this_.country1_id=country2_' at line 1

Your Hibernate SQL:

select this_.id as id14_10_, this_.country1_id as country4_14_10_, this_.country2_id as country5_14_10_, this_.end_date as end2_14_10_, this_.ground_id as ground6_14_10_, this_.series_id as series7_14_10_, this_.start_date as start3_14_10_, this_.umpire1_id as umpire8_14_10_, this_.umpire2_id as umpire9_14_10_, this_.umpire3rd_id as umpire10_14_10_, this_.umpireReserve_id as umpireR11_14_10_, country2_.id as id17_0_, country2_.abbreviation as abbrevia2_17_0_, country2_.name as name17_0_, country2_.status_id as status4_17_0_, cricketsta3_.id as id18_1_, cricketsta3_.description as descript2_18_1_, cricketsta3_.display_name as display3_18_1_, country4_.id as id17_2_, country4_.abbreviation as abbrevia2_17_2_, country4_.name as name17_2_, country4_.status_id as status4_17_2_, ground5_.id as id15_3_, ground5_.country_id as country4_15_3_, ground5_.description as descript2_15_3_, ground5_.name as name15_3_, country6_.id as id17_4_, country6_.abbreviation as abbrevia2_17_4_, country6_.name as name17_4_, country6_.status_id as status4_17_4_, series7_.id as id19_5_, series7_.description as descript2_19_5_, series7_.end_date as end3_19_5_, series7_.name as name19_5_, series7_.start_date as start5_19_5_, umpire8_.id as id16_6_, umpire8_.name as name16_6_, umpire9_.id as id16_7_, umpire9_.name as name16_7_, umpire10_.id as id16_8_, umpire10_.name as name16_8_, umpire11_.id as id16_9_, umpire11_.name as name16_9_ from match this_ left outer join ref_country country2_ on this_.country1_id=country2_.id left outer join ref_cricket_status cricketsta3_ on country2_.status_id=cricketsta3_.id left outer join ref_country country4_ on this_.country2_id=country4_.id left outer join ref_ground ground5_ on this_.ground_id=ground5_.id left outer join ref_country country6_ on ground5_.country_id=country6_.id left outer join series series7_ on this_.series_id=series7_.id left outer join ref_umpire umpire8_ on this_.umpire1_id=umpire8_.id left outer join ref_umpire umpire9_ on this_.umpire2_id=umpire9_.id left outer join ref_umpire umpire10_ on this_.umpire3rd_id=umpire10_.id left outer join ref_umpire umpire11_ on this_.umpireReserve_id=umpire11_.id order by this_.id asc

Match is a reserved word in MySQL 5. My suggestion is to rename your entity to another name.

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

3 Comments

thanx for the response, i tried with both but still no success. i am posting the stacktrace in the question, sorry about the formatting , i cant seem to get it right. Please see if it helps.
thanx a lot for the response, i thought that might be it, but i was hoping its something else, coz that means some changes i wanted to avoid, so i'll test this and than post the result ASAP
thanx a lot mate, that was the problem and it worked as soon as i changed the table name ... i was in a i-will-solve-my-problems-myself mode and i wasted a whole day on it, should have asked for help sooner :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.