-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: Criteria to check date is set, is not 0000-00-00 00:00:00
PostPosted: Wed Jun 23, 2010 2:46 am 
Pro
Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246
I have a Criteria checking that the value of a date column is less than a given date.

Here is the Dao method:
Code:
   public List<User> findNotValid(DateTime dateTime) {
      Criteria criteria = getSession().createCriteria(User.class);
      criteria
      .add(Restrictions.lt("validUntil", dateTime))
      .addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname"));
      return criteria.list();
   }


The problem is that, if the date column contains not date, but simply the value 0000-00-00 00:00:00 then the criteria "less than" returns a match.

So I need to have also another criteria to ensure the date is actually set, and is not a 0000-00-00 00:00:00

The isNotNull cannot solve this issue as the value 0000-00-00 00:00:00 is not a null value. So I cannot use the following criteria:
Code:
.add(Restrictions.isNotNull("validUntil"))

I also cannot use the following criteria:
Code:
.add(Restrictions.ne("validUntil", "0000-00-00 00:00:00"))
as it triggers an exception for attempting to cast a String to a DateTime
Code:
testException = java.lang.ClassCastException: java.lang.String cannot be cast to org.joda.time.DateTime


Is there any way to have a criteria that checks for not 0000-00-00 00:00:00 ?


Top
 Profile  
 
 Post subject: Re: Criteria to check date is set, is not 0000-00-00 00:00:00
PostPosted: Wed Jun 23, 2010 3:05 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Is that MySQL? Try setting zeroDateTimeBehavior=convertToNull on the connection url. Those invalid dates doesn't work well with Java as far as I know. If you can change it, I recommend that you store null instead of 0000-00-00 00:00:00.


Top
 Profile  
 
 Post subject: Re: Criteria to check date is set, is not 0000-00-00 00:00:00
PostPosted: Wed Jun 23, 2010 3:29 am 
Pro
Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246
Yes it is MySql. I will try that suggestion.

Must now rush !

Thanks


Top
 Profile  
 
 Post subject: Re: Criteria to check date is set, is not 0000-00-00 00:00:00
PostPosted: Fri Jun 25, 2010 4:58 pm 
Pro
Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246
Sorry, but I failed to try this zeroDateTimeBehavior=convertToNull setting.

I didn't know where to put it.

Here are my configuration files
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
   <bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName">
         <value>${dataSource.driverClassName}</value>
      </property>
      <property name="url">
         <value>${dataSource.url}</value>
      </property>
      <property name="username">
         <value>${dataSource.username}</value>
      </property>
      <property name="password">
         <value>${dataSource.password}</value>
      </property>
   </bean>
</beans>


Code:
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.hbm2ddl.auto=validate
dataSource.driverClassName=com.mysql.jdbc.Driver
dataSource.url=jdbc:mysql://localhost:3306/db_integration
dataSource.username=thalasoft
dataSource.password=mypassword


Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
   <bean id="sessionFactory"
      class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
      <property name="dataSource">
         <ref bean="dataSource" />
      </property>
      <property name="mappingDirectoryLocations">
         <list>
            <value>classpath:com/thalasoft/learnintouch/core/domain</value>
         </list>
      </property>
      <property name="hibernateProperties">
         <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.format_sql">true</prop>
            <prop key="hibernate.connection.pool_size">0</prop>
            <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
         </props>
      </property>
   </bean>

   <bean id="transactionManager"
      class="org.springframework.orm.hibernate3.HibernateTransactionManager">
      <property name="sessionFactory">
         <ref local="sessionFactory" />
      </property>
   </bean>

   <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
      <property name="sessionFactory">
         <ref bean="sessionFactory" />
      </property>
   </bean>

   <bean class="org.springframework.beans.factory.annotation.RequiredAnnotationBeanPostProcessor" />

</beans>


Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

   <bean id="addressDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.AddressHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="adminDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.AdminHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="adminModuleDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.AdminModuleHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="adminOptionDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.AdminOptionHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="clientDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.ClientHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="contactDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.ContactHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="contactStatusDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.ContactStatusHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="contactRefererDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.ContactRefererHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="preferenceDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.PreferenceHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

   <bean id="userDao"
      class="com.thalasoft.learnintouch.core.dao.hibernate.UserHibernateDao">
      <property name="sessionFactory" ref="sessionFactory" />
   </bean>

</beans>


Last edited by stephaneeybert on Sat Jun 26, 2010 9:27 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Criteria to check date is set, is not 0000-00-00 00:00:00
PostPosted: Fri Jun 25, 2010 5:04 pm 
Pro
Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246
Other than that, I also wonder (on another forum post at viewtopic.php?f=1&t=1004552) if I should use NOT NULL in the MySql columns...


Top
 Profile  
 
 Post subject: Re: Criteria to check date is set, is not 0000-00-00 00:00:00
PostPosted: Sat Jul 10, 2010 10:47 am 
Pro
Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246
So I tried the following Hibernate property:

<prop key="hibernate.connection.zero_date_time_behavior">${hibernate.connection.zero_date_time_behavior}</prop>

with

hibernate.connection.zero_date_time_behavior=convertToNull

But my 0000-00-00 00:00:00 date is still not seen as a NULL one when retrieving it with Hibernate.

By the way, where can I find the Hibernate log file ?


Top
 Profile  
 
 Post subject: Re: Criteria to check date is set, is not 0000-00-00 00:00:00
PostPosted: Thu Jul 22, 2010 3:59 am 
Pro
Pro

Joined: Mon Apr 16, 2007 8:10 am
Posts: 246
I solved the problem by updating the schema so as to have a nullable date property.

This way I can check for isNotNull.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.