Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1.2
Mapping documents:
<?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">

<hibernate-mapping
>
<class
name="com.siemens.swa.domain.Activity"
table="ACTIVITY"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
lazy="true"
batch-size="6"
>

<id
name="id"
column="ACTIVITY_ID"
type="java.lang.Long"
>
<generator class="native"/>
</id>

<many-to-one
name="equipment"
class="com.siemens.swa.domain.Equipment"
cascade="save-update"
outer-join="auto"
update="true"
insert="true"
access="property"
column="EQUIPMENT_ID"
/>

<many-to-one
name="veloxInfo"
class="com.siemens.swa.domain.ActivityVeloxInfo"
cascade="all"
outer-join="auto"
unique="true"
column="ACT_VELOX_INFO_ID"
/>

<many-to-one
name="activityExecution"
class="com.siemens.swa.domain.ActivityExecution"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="ACT_EXEC_ID"
/>

<property
name="promiseDate"
column="PROMISE_DATE"
/>

<property
name="promiseTime"
column="PROMISE_TIME"
/>

<property
name="promiseEndDate"
column="PROMISE_END_DATE"
/>

<property
name="promiseEndTime"
column="PROMISE_END_TIME"
/>

<property
name="openChecklist"
column="OPEN_CHECKLIST"
/>

<many-to-one
name="scheduleInfo"
class="com.siemens.swa.domain.ActivitySchedule"
cascade="all"
outer-join="auto"
access="property"
column="ACT_SCH_ID"
unique="true"
/>

<property
name="activityNumber"
column="ACTIVITY_NUM"
not-null="true"
unique="true"
/>

<property
name="state"
type="com.siemens.swa.dao.ActivityStateUserType"
update="true"
insert="true"
access="property"
column="STATE"
length="10"
/>

<property
name="type"
type="com.siemens.swa.dao.ActivityTypeUserType"
update="true"
insert="true"
access="property"
column="TYPE"
length="12"
/>

<many-to-one
name="priority"
cascade="none"
class="com.siemens.swa.domain.ActivityPriority"
outer-join="auto"
column="ACT_PRIORITY_ID"
/>

<set
name="preventiveChecklist"
lazy="true"
cascade="all"
sort="unsorted"
batch-size="6"
outer-join="auto"
>

<key
column="ACTIVITY_ID"
>
</key>

<one-to-many
class="com.siemens.swa.domain.PreventiveChecklist"
/>

</set>

<many-to-one
name="techniqueArea"
class="com.siemens.swa.domain.TechniqueArea"
cascade="save-update"
outer-join="auto"
update="true"
insert="true"
access="property"
column="TECHN_AREA_ID"
/>

<many-to-one
name="informer"
class="com.siemens.swa.domain.Informer"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="INFORMER_ID"
/>

<property
name="targetType"
type="com.siemens.swa.dao.ActivityTargetTypeUserType"
update="true"
insert="true"
access="property"
column="TARGET_ID"
/>

<many-to-one
name="activityLocalInfo"
class="com.siemens.swa.domain.ActivityLocalInfo"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="LOCAL_INFO_ID"
/>

<many-to-one
name="activityEquipmentInfo"
class="com.siemens.swa.domain.ActivityEquipmentInfo"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="ACT_EQUIP_INFO_ID"
/>

<property
name="associatedDocument"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="ASSOC_DOC"
/>

<property
name="freeDescription"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="FREE_DESC"
/>

<many-to-one
name="techniqueAreaRamification"
class="com.siemens.swa.domain.TechniqueAreaRamification"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="TECHN_AREA_RAMIF_ID"
/>

<many-to-one
name="clientContract"
class="com.siemens.swa.domain.ClientContract"
outer-join="auto"
column="CLIENT_CONTRACT_ID"
/>

<property
name="initialInterruptOfServiceDate"
column="INITIAL_INTERRUPT_DATE"
/>

<property
name="finalInterruptOfServiceDate"
column="FINAL_INTERRUPT_DATE"
/>

<property
name="initialInterruptOfServiceTime"
column="INITIAL_INTERRUPT_TIME"
/>

<property
name="finalInterruptOfServiceTime"
column="FINAL_INTERRUPT_TIME"
/>

<many-to-one
name="externalInfo"
class="com.siemens.swa.domain.ExternalInfo"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="EXTERNAL_INFO_ID"
/>

<many-to-one
name="category"
class="com.siemens.swa.domain.ActivityCategory"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="CATEGORY_ID"
/>

<many-to-one
name="corretiveInfo"
class="com.siemens.swa.domain.CorretiveInfo"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="CORRETIVE_INFO_ID"
/>

<many-to-one
name="reserver"
class="com.siemens.swa.domain.User"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="RESERVER_ID"
/>

<property
name="reserved"
type="java.lang.Boolean"
update="true"
insert="true"
not-null="true"
access="property"
column="RESERVED"
/>

<set
name="technicians"
table="TECHNICIAN_ACTIVITY"
cascade="save-update"
sort="unsorted"
lazy="true"
batch-size="6"
outer-join="auto"
>

<key
column="ACTIVITY_ID"
>
</key>

<many-to-many
class="com.siemens.swa.domain.Technician"
column="TCN_ID"
outer-join="auto"
/>

</set>

<many-to-one
name="responsibleGroup"
class="com.siemens.swa.domain.ResponsibleGroup"
column="RESP_GROUP_ID"
/>

<many-to-one
name="criticity"
class="com.siemens.swa.domain.ActivityCriticity"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="ACT_CRITICITY_ID"
/>

<property
name="loadStatus"
column="LOAD_STATUS"
/>

<property
name="shadow"
column="SHADOW"
/>

<many-to-one
name="activityShadowGenerator"
class="com.siemens.swa.domain.Activity"
column="ACT_SHADOW_GENERATOR_ID"
/>

<many-to-one
name="activityServiceType"
class="com.siemens.swa.domain.ActivityServiceType"
column="ACT_SERVICE_TYPE_ID"
/>

<set
name="malfunctionTransmissionDataTypes"
table="ACTIVITY_TRANSMISSION_DATA_TYPE"
cascade="save-update"
sort="unsorted"
lazy="true"
batch-size="6"
outer-join="auto"
>

<key
column="ACTIVITY_ID"
>
</key>

<many-to-many
class="com.siemens.swa.domain.TransmissionDataType"
column="TRANSMISSION_DATA_TYPE_ID"
outer-join="auto"
/>
</set>

<property
name="beginningTransitionDate"
column="BEGINNING_TRANSITION_DATE"
/>

<property
name="beginningTreatmentDate"
column="BEGINNING_TREATMENT_DATE"
/>

<property
name="observation"
column="OBSERVATION"
/>

<property
name="movimentStatus"
column="OS_MOVIMENT_STATUS"
/>

<property
name="infoStatus"
column="OS_INFO_STATUS"
/>

<many-to-one
name="operator"
class="com.siemens.swa.domain.Dispatcher"
column="OPERATOR_ID"
/>

<list
name="logList"
lazy="true"
cascade="all"
inverse="false"
batch-size="6"
outer-join="auto"
>

<key
column="ACTIVITY_ID" not-null="true"
>
</key>

<index column="ACTIVITY_ORDER"/>

<one-to-many
class="com.siemens.swa.domain.ActivityLog"
/>

</list>
</class>

</hibernate-mapping>

<?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">

<hibernate-mapping
>
<class
name="com.siemens.swa.domain.ActivityVeloxInfo"
table="ACTIVITY_VELOX_INFO"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
lazy="true"
batch-size="6"
>

<id
name="id"
column="ACT_VELOX_INFO_ID"
type="java.lang.Long"
>
<generator class="native"/>

</id>

<property
name="circuit"
column="circuit"
/>

<property
name="circuitType"
type="com.siemens.swa.dao.CircuitTypeUserType"
update="true"
insert="true"
access="property"
column="CIRCUIT_TYPE"
/>

<property
name="tiedNum"
column="tied_num"
/>

<property
name="clientName"
column="client_name"
/>

<component name="address">
<property name="street"/>
<property name="quarter"/>
<property name="number"/>
<property name="complementAddress"/>
<property name="cep"/>
<property name="city"/>
<property name="state"/>
<property name="country"/>
</component>

<many-to-one
name="microArea"
class="com.siemens.swa.domain.MicroArea"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="MICRO_AREA_ID"
/>

<property
name="modemType"
type="com.siemens.swa.dao.ModemTypeUserType"
update="true"
insert="true"
access="property"
column="MODEM_TYPE"
/>

<property
name="modemOwner"
type="com.siemens.swa.dao.ModemOwnerUserType"
update="true"
insert="true"
access="property"
column="MODEM_OWNER"
/>

<property
name="serviceTypeResponsible"
type="com.siemens.swa.dao.ScheduleServiceTypeResponsibleUserType"
update="true"
insert="true"
access="property"
column="SCHEDULE_RESPONSIBLE"
/>

<property
name="retirementPassword"
column="RETIREMENT_PASSWORD"
/>

<many-to-one
name="enterprise"
class="com.siemens.swa.domain.Enterprise"
cascade="save-update"
outer-join="auto"
update="true"
insert="true"
access="property"
column="ENTERPRISE_ID"
/>

<property
name="serviceType"
type="com.siemens.swa.dao.ScheduleServiceTypeUserType"
update="true"
insert="true"
access="property"
column="SERVICE_TYPE"
/>

<many-to-one
name="periodOfDay"
class="com.siemens.swa.domain.PeriodsOfDay"
cascade="save-update"
column="PERIOD_OF_DAY_ID"
outer-join="auto"
/>

<many-to-one
name="clientType"
class="com.siemens.swa.domain.ClientType"
cascade="save-update"
outer-join="auto"
update="true"
insert="true"
access="property"
column="CLIENT_TYPE_ID"
/>

<property
name="clientContact"
column="CLIENT_CONTACT"
/>

<property
name="facilities"
column="FACILITIES"
/>

<property
name="vpiVcl"
column="VPI_VCL"
/>

<property
name="channel"
column="CHANNEL"
/>

<property
name="ipType"
column="IP_TYPE"
/>

<property
name="veloxSpeed"
column="VELOX_SPEED"
/>

<property
name="operationalSystem"
column="OP_SYSTEM"
/>

<property
name="eqn"
column="EQN"
/>

<property
name="posto"
column="POSTO"
/>

<property
name="desig"
column="DESIG"
/>

<property
name="msg"
column="MSG"
/>

<property
name="regDesp"
column="REG_DESP"
/>

<property
name="apz"
column="APZ"
/>

<property
name="uf"
column="UF"
/>

<property
name="estacao"
column="ESTACAO"
/>

<property
name="time"
column="TIME"
/>

<property
name="repetida"
column="REPETIDA"
/>

<property
name="cb"
column="CB"
/>

<property
name="lat"
column="LAT"
/>

<property
name="latEnd"
column="lat_end"
/>

<property
name="cx"
column="CX"
/>

<property
name="cxEnd"
column="CX_END"
/>

<property
name="ppar"
column="PPAR"
/>

<property
name="spar"
column="SPAR"
/>

<property
name="eqnStatus"
column="EQN_STATUS"
/>


<many-to-one
name="boardInfo"
class="com.siemens.swa.domain.NetBoardInfo"
cascade="all"
outer-join="auto"
update="true"
insert="true"
access="property"
column="BOARD_INFO_ID"
/>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
String strQuery = "SELECT count(activity) "
+ " FROM Activity activity, AppointmentScheduler scheduler"
+ " JOIN activity.veloxInfo veloxInfo"
+ " JOIN activity.scheduleInfo scheduleInfo"
+ " JOIN veloxInfo.microArea microArea"
+ " JOIN scheduler.microAreas microAreas"
+ " WHERE scheduler.id = :userId"
+ " AND activity.state <> :triage"
+ " AND (activity.type = :install OR activity.type = :service)"
+ " AND activity.promiseDate >= :initialDate"
+ " AND activity.promiseDate < :finalDate"
+ " AND veloxInfo.periodOfDay.code = :period"
+ " AND microArea IN (microAreas)"
+ " AND scheduleInfo.scheduler.id = scheduler.id";

Full stack trace of any exception that occurs:
2006-02-09 14:48:02,984 [org.hibernate.util.JDBCExceptionReporter] - could not execute query [select count(activity0_.ACTIVITY_ID) as col_0_0_ from ACTIVITY activity0_, APPOINTMENT_SCHEDULER appointmen1_ inner join ACTIVITY_VELOX_INFO activityve2_ on activity0_.ACT_VELOX_INFO_ID=activityve2_.ACT_VELOX_INFO_ID inner join ACTIVITY_SCHEDULE activitysc3_ on activity0_.ACT_SCH_ID=activitysc3_.ACT_SCH_ID inner join MICRO_AREA microarea4_ on activityve2_.MICRO_AREA_ID=microarea4_.micro_area_id inner join MICRO_AREA_SCHEDULER microareas5_ on appointmen1_.appsch_id=microareas5_.appsch_id inner join MICRO_AREA microarea6_ on microareas5_.micro_area_id=microarea6_.micro_area_id, PERIODS_OF_DAY periodsofd7_ where (appointmen1_.appsch_id=? )AND(activity0_.STATE<>? )AND((activity0_.TYPE=? )OR(activity0_.TYPE=? ))AND(activity0_.PROMISE_DATE>=? )AND(activity0_.PROMISE_DATE<? )AND(periodsofd7_.CODE=? and activityve2_.PERIOD_OF_DAY_ID=periodsofd7_.PERIOD_OF_DAY_ID)AND(microarea4_.micro_area_id IN(microarea6_.micro_area_id))AND(activitysc3_.SCHEDULER_ID=appointmen1_.appsch_id )]
java.sql.SQLException: Unknown column 'activity0_.ACT_VELOX_INFO_ID' in 'on clause'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:887)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:749)
at com.siemens.swa.dao.AppointmentSchedulerVeloxDAOImpl.countScheduledActivitiesBySchedulerAndPeriodAndDate(AppointmentSchedulerVeloxDAOImpl.java:366)
at com.siemens.swa.service.AppointmentSchedulerVeloxServiceImpl.createTOResumeInfoScheduled(AppointmentSchedulerVeloxServiceImpl.java:336)
at com.siemens.swa.service.AppointmentSchedulerVeloxServiceImpl.countScheduledActivitiesByscheduler(AppointmentSchedulerVeloxServiceImpl.java:313)
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:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
at $Proxy36.countScheduledActivitiesByscheduler(Unknown Source)
at com.siemens.swa.action.ActivitySchedulerAction.loadSchedulesResume(ActivitySchedulerAction.java:199)
at com.siemens.swa.action.ActivitySchedulerAction.openHome(ActivitySchedulerAction.java:117)
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:585)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:276)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:196)
at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:106)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at net.sf.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:167)
at net.sf.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
2006-02-09 14:48:02,988 [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1054, SQLState: 42S22
2006-02-09 14:48:02,988 [org.hibernate.util.JDBCExceptionReporter] - Unknown column 'activity0_.ACT_VELOX_INFO_ID' in 'on clause'
2006-02-09 14:48:03,032 [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator] - Translating SQLException with SQLState '42S22' and errorCode '1054' and message [Unknown column 'activity0_.ACT_VELOX_INFO_ID' in 'on clause']; SQL was [select count(activity0_.ACTIVITY_ID) as col_0_0_ from ACTIVITY activity0_, APPOINTMENT_SCHEDULER appointmen1_ inner join ACTIVITY_VELOX_INFO activityve2_ on activity0_.ACT_VELOX_INFO_ID=activityve2_.ACT_VELOX_INFO_ID inner join ACTIVITY_SCHEDULE activitysc3_ on activity0_.ACT_SCH_ID=activitysc3_.ACT_SCH_ID inner join MICRO_AREA microarea4_ on activityve2_.MICRO_AREA_ID=microarea4_.micro_area_id inner join MICRO_AREA_SCHEDULER microareas5_ on appointmen1_.appsch_id=microareas5_.appsch_id inner join MICRO_AREA microarea6_ on microareas5_.micro_area_id=microarea6_.micro_area_id, PERIODS_OF_DAY periodsofd7_ where (appointmen1_.appsch_id=? )AND(activity0_.STATE<>? )AND((activity0_.TYPE=? )OR(activity0_.TYPE=? ))AND(activity0_.PROMISE_DATE>=? )AND(activity0_.PROMISE_DATE<? )AND(periodsofd7_.CODE=? and activityve2_.PERIOD_OF_DAY_ID=periodsofd7_.PERIOD_OF_DAY_ID)AND(microarea4_.micro_area_id IN(microarea6_.micro_area_id))AND(activitysc3_.SCHEDULER_ID=appointmen1_.appsch_id )] for task [Hibernate operation: could not execute query]
2006-02-09 14:48:03,033 [org.springframework.transaction.interceptor.RuleBasedTransactionAttribute] - Applying rules to determine whether transaction should rollback on org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [select count(activity0_.ACTIVITY_ID) as col_0_0_ from ACTIVITY activity0_, APPOINTMENT_SCHEDULER appointmen1_ inner join ACTIVITY_VELOX_INFO activityve2_ on activity0_.ACT_VELOX_INFO_ID=activityve2_.ACT_VELOX_INFO_ID inner join ACTIVITY_SCHEDULE activitysc3_ on activity0_.ACT_SCH_ID=activitysc3_.ACT_SCH_ID inner join MICRO_AREA microarea4_ on activityve2_.MICRO_AREA_ID=microarea4_.micro_area_id inner join MICRO_AREA_SCHEDULER microareas5_ on appointmen1_.appsch_id=microareas5_.appsch_id inner join MICRO_AREA microarea6_ on microareas5_.micro_area_id=microarea6_.micro_area_id, PERIODS_OF_DAY periodsofd7_ where (appointmen1_.appsch_id=? )AND(activity0_.STATE<>? )AND((activity0_.TYPE=? )OR(activity0_.TYPE=? ))AND(activity0_.PROMISE_DATE>=? )AND(activity0_.PROMISE_DATE<? )AND(periodsofd7_.CODE=? and activityve2_.PERIOD_OF_DAY_ID=periodsofd7_.PERIOD_OF_DAY_ID)AND(microarea4_.micro_area_id IN(microarea6_.micro_area_id))AND(activitysc3_.SCHEDULER_ID=appointmen1_.appsch_id )]; nested exception is java.sql.SQLException: Unknown column 'activity0_.ACT_VELOX_INFO_ID' in 'on clause'

Name and version of the database you are using:
mysql innodb 5.0.18

The generated SQL (show_sql=true):

2006-02-09 14:48:02,924 [org.hibernate.SQL] - select count(activity0_.ACTIVITY_ID) as col_0_0_ from ACTIVITY activity0_, APPOINTMENT_SCHEDULER appointmen1_ inner join ACTIVITY_VELOX_INFO activityve2_ on activity0_.ACT_VELOX_INFO_ID=activityve2_.ACT_VELOX_INFO_ID inner join ACTIVITY_SCHEDULE activitysc3_ on activity0_.ACT_SCH_ID=activitysc3_.ACT_SCH_ID inner join MICRO_AREA microarea4_ on activityve2_.MICRO_AREA_ID=microarea4_.micro_area_id inner join MICRO_AREA_SCHEDULER microareas5_ on appointmen1_.appsch_id=microareas5_.appsch_id inner join MICRO_AREA microarea6_ on microareas5_.micro_area_id=microarea6_.micro_area_id, PERIODS_OF_DAY periodsofd7_ where (appointmen1_.appsch_id=? )AND(activity0_.STATE<>? )AND((activity0_.TYPE=? )OR(activity0_.TYPE=? ))AND(activity0_.PROMISE_DATE>=? )AND(activity0_.PROMISE_DATE<? )AND(periodsofd7_.CODE=? and activityve2_.PERIOD_OF_DAY_ID=periodsofd7_.PERIOD_OF_DAY_ID)AND(microarea4_.micro_area_id IN(microarea6_.micro_area_id))AND(activitysc3_.SCHEDULER_ID=appointmen1_.appsch_id )
2006-02-09 14:48:02,924 [org.hibernate.jdbc.AbstractBatcher] - preparing statement
2006-02-09 14:48:02,976 [org.hibernate.loader.Loader] - bindNamedParameters() FIRST_HOUR -> period [7]
2006-02-09 14:48:02,976 [org.hibernate.type.StringType] - binding 'FIRST_HOUR' to parameter: 7
2006-02-09 14:48:02,976 [org.hibernate.loader.Loader] - bindNamedParameters() SERVICE -> service [4]
2006-02-09 14:48:02,977 [org.hibernate.type.StringType] - binding 'SERVICE' to parameter: 4
2006-02-09 14:48:02,977 [org.hibernate.loader.Loader] - bindNamedParameters() TRIAGE -> triage [2]
2006-02-09 14:48:02,977 [org.hibernate.type.StringType] - binding 'TRIAGE' to parameter: 2
2006-02-09 14:48:02,977 [org.hibernate.loader.Loader] - bindNamedParameters() 10 -> userId [1]
2006-02-09 14:48:02,977 [org.hibernate.type.LongType] - binding '10' to parameter: 1
2006-02-09 14:48:02,977 [org.hibernate.loader.Loader] - bindNamedParameters() 2006-2-9 -> initialDate [5]
2006-02-09 14:48:02,977 [org.hibernate.type.StringType] - binding '2006-2-9' to parameter: 5
2006-02-09 14:48:02,977 [org.hibernate.loader.Loader] - bindNamedParameters() INSTALL -> install [3]
2006-02-09 14:48:02,977 [org.hibernate.type.StringType] - binding 'INSTALL' to parameter: 3
2006-02-09 14:48:02,977 [org.hibernate.loader.Loader] - bindNamedParameters() 2006-2-10 -> finalDate [6]
2006-02-09 14:48:02,977 [org.hibernate.type.StringType] - binding '2006-2-10' to parameter: 6

Debug level Hibernate log excerpt:

2006-02-09 14:48:02,924 [org.hibernate.engine.query.HQLQueryPlan] - find: SELECT count(activity) FROM Activity activity, AppointmentScheduler scheduler JOIN activity.veloxInfo veloxInfo JOIN activity.scheduleInfo scheduleInfo JOIN veloxInfo.microArea microArea JOIN scheduler.microAreas microAreas WHERE scheduler.id = :userId AND activity.state <> :triage AND (activity.type = :install OR activity.type = :service) AND activity.promiseDate >= :initialDate AND activity.promiseDate < :finalDate AND veloxInfo.periodOfDay.code = :period AND microArea IN (microAreas) AND scheduleInfo.scheduler.id = scheduler.id
2006-02-09 14:48:02,924 [org.hibernate.engine.QueryParameters] - named parameters: {service=SERVICE, period=FIRST_HOUR, triage=TRIAGE, install=INSTALL, initialDate=2006-2-9, userId=10, finalDate=2006-2-10}
2006-02-09 14:48:02,924 [org.hibernate.hql.classic.QueryTranslatorImpl] - HQL: SELECT count(activity) FROM com.siemens.swa.domain.Activity activity, com.siemens.swa.domain.AppointmentScheduler scheduler JOIN activity.veloxInfo veloxInfo JOIN activity.scheduleInfo scheduleInfo JOIN veloxInfo.microArea microArea JOIN scheduler.microAreas microAreas WHERE scheduler.id = :userId AND activity.state <> :triage AND (activity.type = :install OR activity.type = :service) AND activity.promiseDate >= :initialDate AND activity.promiseDate < :finalDate AND veloxInfo.periodOfDay.code = :period AND microArea IN (microAreas) AND scheduleInfo.scheduler.id = scheduler.id

Hi all,

I've spent several hours searching google and these forums - I'm stuck:

1) We have a production app that was using mysql 4.1.15 non-innodb with 3.0.5 - worked great.

2) We are now upgrading to 3.1.2 and mysql 5.0.18 with innodb. Some queries are failing.

3) I've read that there is now 'Stricter checking of invalid queries' and you can't use 'as' in your queries. I've looked hard at the above query and I see no problem with the join sematics or that it is in any way invalid. Please excuse if it is indeed bad!

4) I've switched to the latest mysql-connector-java-3.1.12-bin.jar and my dialect is set to org.hibernate.dialect.MySQLInnoDBDialect .

All ideas welcome - including RTFM if I missed something - I honestly did try.
iksrazal

Have you tried running the generated SQL (with ?s replaced by useful dummy data) directly on your SQL console? If your new DB server has changed its syntax, you may need to change your hibernate SQL dialect.

Also, please consider using code tags. Your post is very dificult to read.

Thanks for the reply - sorry I forget to use the code tags. The query mysql-query-browser also fails:



The jist of the problem is here:



So its complaining on ACT_VELOX_INFO_ID, yet I can do a simple query:



Since this query worked before with 3.0.5 and mysql 4.1 non-innodb, either I'm configuring mysql5 wrong or the tables are being generated wrong - probably the former, but I'd appreciate any tips while I'm trying to sort it all out.

iksrazal

Oh yeah - forgot to mention I did change the hibernate SQL dialect - we were using MySQLDialect - now we are using MySQLInnoDBDialect.

iksrazal