Hibernate version: 3.2.0 CR2
Hello everyone,
I know that there were a lot of discussions about comparing date and time using HQL within this forum and on other places in the web and I think I have read most of them and tried several things including but not limited to the single scenario listed below in the detailed information.
The main problem I encountered is that Hibernate does not seem to know exactly which function is available to what database. E.g. the error that comes up here is the function
DAY() that can be used in HQL directly. I thought that these functions are Hibernates own function names and are interpreted / resolved to real function names for the appropriate database. But I now see that they are directly passed through to the SQL. Instead that behavior I expected to get something like
TO_NUMBER(TO_CHAR(log_timestamp, 'DD')) for an interpretation of
DAY(logTimestamp) in HQL.
This odd behavior (at least to me) makes it impossible to compare date / time / time stamp values using DBMS-independent HQL. I therefore have to check for the dialect which is VERY, VERY DIRTY when using Springs IOP abstractions (which is the case for our web-app). Additionally we actually rely on the independence of the type of DBMS that Hibernate tries to offer.
So and at this point my precise question now is:
Is there any way (using HQL) to compare date or time (actually date would be sufficient for now) that runs fine for at least Oracle9i and MySQL5?
Thanks in advance for any hint or help!
Greetz,
C]-[aoZ
P.S.: Assume that the data model is fixed and cannot be changed for this!
Mapping documents:
Code:
<class table="module_log" name="de.[...].component.log.ModuleLog">
<id column="log_id" unsaved-value="null" name="id">
<generator class="native">
<param name="sequence">auto_increment_for_module_log</param>
</generator>
</id>
<property name="logPriority" length="15" column="log_priority"/>
<property name="logCategory" length="255" column="log_category"/>
<property name="logModuleName" length="200" column="log_modulename"/>
<property name="logThread" length="100" column="log_thread"/>
<property name="logMessage" length="2000" column="log_message"/>
<property name="logMessageId" column="log_message_id"/>
<property name="logTimestamp" column="log_timestamp"/>
</class>
Code:
public void cleanupLogEntries(Date beforeThisTime) {
int removed = 0;
if(beforeThisTime != null && beforeThisTime.getTime() > 0) {
StringBuilder deletionHql = new StringBuilder("DELETE ModuleLog WHERE YEAR(logTimestamp) < YEAR(:SPECIFIC_TIME)");
deletionHql.append(" OR (YEAR(logTimestamp) = YEAR(:SPECIFIC_TIME) AND MONTH(logTimestamp) < MONTH(:SPECIFIC_TIME))");
deletionHql.append(" OR (YEAR(logTimestamp) = YEAR(:SPECIFIC_TIME) AND MONTH(logTimestamp) = MONTH(:SPECIFIC_TIME) AND DAY(logTimestamp) < DAY(:SPECIFIC_TIME))");
Session session = getSessionFactory().openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
removed = session.createQuery(deletionHql.toString())
.setDate("SPECIFIC_TIME", beforeThisTime)
.executeUpdate();
tx.commit();
} catch(RuntimeException x) {
if(tx != null && !tx.wasCommitted() && !tx.wasRolledBack()) {
tx.rollback();
}
fallbackLog.warn("Error during deletion of log entries. Details to follow...", x);
} finally {
session.close();
}
}
if(fallbackLog.isDebugEnabled()) {
fallbackLog.debug("CLEANUP: "+removed+" log entries have been deleted.");
}
}
Full stack trace of any exception that occurs:Code:
org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:391)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1134)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at de.[...].component.log.JMSLogHandler.cleanupLogEntries(JMSLogHandler.java:233)
at de.[...].ps.business.service.PSScheduleServiceImpl.executeJobCleanupModuleLog(PSScheduleServiceImpl.java:39)
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:266)
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:100)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)
at $Proxy152.executeJobCleanupModuleLog(Unknown Source)
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.util.MethodInvoker.invoke(MethodInvoker.java:270)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:199)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:90)
at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: java.sql.SQLException: ORA-00904: "DAY": invalid identifier
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:227)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
... 26 more
Name and version of the database you are using: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production The generated SQL (show_sql=true):Code:
delete from module_log where year(log_timestamp)<year(?)
or year(log_timestamp)=year(?) and month(log_timestamp)<month(?)
or year(log_timestamp)=year(?) and month(log_timestamp)=month(?) and day(log_timestamp)<day(?)
Login
FAQ
Search