Hi!

I'm using MySQL 4.0.xx and have been trying to query the database for a dateinterval using all sorts of code...
A search on this forum reveals that more people hva had problems with Date-querys and MySQL. Is this a known issue reported somewhere? Bug even?

Heres the mapping

<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping (View Source for full doctype...)>
- <hibernate-mapping default-cascade="none" auto-import="true">
- <class name="ks.rah.cainfo.db.model.InfoContact" table="CONTACT" dynamic-update="false" dynamic-insert="false" mutable="true" polymorphism="implicit" batch-size="1" select-before-update="false" optimistic-lock="version">
- <id name="id" column="id" type="java.lang.Long" unsaved-value="null">
<generator class="native" />
</id>
<property name="user" type="java.lang.Long" update="true" insert="true" column="USER_FK" not-null="false" unique="false" />
<property name="entryDate" type="date" update="true" insert="true" column="ENTRYDATE" not-null="false" unique="false" />
<property name="duration" type="long" update="true" insert="true" column="DURATION" not-null="false" unique="false" />
<property name="contactType" type="int" update="true" insert="true" column="CONTACTTYPE" not-null="false" unique="false" />
- <list name="data" lazy="false" inverse="false" cascade="save-update" batch-size="1" outer-join="auto">
<key column="CONTACT_FK" />
<index column="I" />
<one-to-many class="ks.rah.cainfo.db.model.ContactDataNode" />
</list>
- <!--
To add non XDoclet property mappings, create a file named
hibernate-properties-InfoContact.xml
containing the additional properties and place it in your merge dir.


-->
</class>
</hibernate-mapping>

Heres the code

public List retrieveAll(Date startDate, Date endDate) throws HibernateException
{
List contacts = null;
Session session = null;
Transaction tx = null;
Boolean active = new Boolean(true);
Query q = null;
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String start, end;
start = format.format(startDate);
end = format.format(endDate);

try
{
session = HibernateSession.currentSession();
tx = session.beginTransaction();
contacts = session.find("from InfoContact as contact where contact.entryDate between ? and ?",
new Object[] { startDate, endDate },
new Type[] { Hibernate.DATE, Hibernate.DATE } );
//q = session.createQuery("from InfoContact as contact where contact.entryDate between :startIt and :endIt");
//q.setParameter("startIt", startDate);
//q.setParameter("endIt", endDate);
//q.setDate("startIt", startDate);
//q.setDate("endIt", endDate);
//contacts = q.list();
tx.commit();
}
catch(HibernateException he)
{
if(tx!=null) tx.rollback();
throw he;
}
finally
{
HibernateSession.closeSession();
}
return contacts;
}

I've tried the Query way too, and also to transform the dates to Strings....
What am I doing wrong??

Sincerely,

/F

your code is wrong. you init start and end but use startDate, endDate.

Actually this is correct I think, the dates are used in the query. Please strip down the code before posting. And you never told us what your problem actually IS

You're right, read too fast

Thanks for answering!
Here's the stripped code, which should retrieve a list of some length, but length is always 0, and that is the error.

===========
public List retrieveAll(Date startDate, Date endDate) throws HibernateException
{
List contacts = null;
Session session = null;
Transaction tx = null;

try
{
session = HibernateSession.currentSession();
tx = session.beginTransaction();
contacts = session.find("from InfoContact as contact where contact.entryDate between ? and ?",
new Object[] { startDate, endDate },
new Type[] { Hibernate.DATE, Hibernate.DATE } );
tx.commit();
}
catch(HibernateException he)
{
if(tx!=null) tx.rollback();
throw he;
}
finally
{
HibernateSession.closeSession();
}
return contacts;
}
==================

Input to this method is startDate = today - 1 month, endDate = today + 1 month, and last input in database was made Thu 27:th, so something should come out...

The method is invoked from a JUnit 3.8.1 test, and the other tests performed passes smoothly. I'm using Hibernate 2.1. The fact that more people have had problems with dates in conjunction with MySQL makes me a bit suspicious...

Sincerely,

/F

Remember to reset the date's millisecond, second, minute and hour field before binding them. Use Calendar.clear(int FIELDNAME). Your code should look something like

.. then bind (you need to do this for both fields, but can factor out the logic into say CalendarUtils.clearTimeFields(date)


Justin

Thanks, but it still doesn't give any more than a list of zero length. But I learned the method to clear specific field. You live, you learn! ;-)

Here's my JUnit test that fails..

========
public void testDateInterval() throws HibernateException
{
InfoContactDAO mgr = new InfoContactDAO();
Calendar last, today;
last = Calendar.getInstance();
today = Calendar.getInstance();
last.roll(Calendar.MONTH, -1);
today.roll(Calendar.MONTH, 1);
last.clear(Calendar.HOUR);
last.clear(Calendar.MINUTE);
last.clear(Calendar.SECOND);
last.clear(Calendar.MILLISECOND);
today.clear(Calendar.HOUR);
today.clear(Calendar.MINUTE);
today.clear(Calendar.SECOND);
today.clear(Calendar.MILLISECOND);
System.out.println("last: " + last.getTime() + ", today: " + today.getTime());
List list = mgr.retrieveAll(last.getTime(), today.getTime());
this.assertNotNull("list is NULL!", list);
this.assertEquals("should be more than 0....", 6, list.size()); //FAIL!!!!
}

========

Sincerely,

/F

Put the following in your log4j.properties file:

Execute the test, and post the trace

Also remember to use Calendar.add() and not Calendar.roll() as roll() does not increment the higher order fields. Check that javadocs for more info.
I don't suspect that this is causing your problem though.

..and please post the data that you have in your db

Trace....

======
junit2:
[junit] Running ks.rah.cainfo.db.dao.TestInfoContactDAO
[junit] last: Tue Dec 28 11:00:00 CET 2004, today: Sat Feb 28 11:00:00 CET 2004
[junit] 11:10:44,062 INFO Environment:432 - Hibernate 2.1 final
[junit] 11:10:44,062 INFO Environment:461 - hibernate.properties not found
[junit] 11:10:44,078 INFO Environment:481 - using CGLIB reflection optimizer
[junit] 11:10:44,078 INFO Configuration:830 - configuring from resource: /hibernate.cfg.xml
[junit] 11:10:44,078 INFO Configuration:802 - Configuration resource: /hibernate.cfg.xml
[junit] 11:10:44,203 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibernate-config
uration-2.0.dtd in classpath under net/sf/hibernate/
[junit] 11:10:44,203 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ration-2.0
.dtd in classpath
[junit] 11:10:44,281 DEBUG Configuration:788 - dialect=net.sf.hibernate.dialect.MySQLDialect
[junit] 11:10:44,281 DEBUG Configuration:788 - connection.driver_class=com.mysql.jdbc.Driver
[junit] 11:10:44,281 DEBUG Configuration:788 - connection.url=jdbc:mysql://localhost/cainfo_test
[junit] 11:10:44,281 DEBUG Configuration:788 - connection.username=
[junit] 11:10:44,281 DEBUG Configuration:788 - connection.password=
[junit] 11:10:44,281 DEBUG Configuration:788 - show_sql=true
[junit] 11:10:44,281 DEBUG Configuration:788 - c3p0.max_size=2
[junit] 11:10:44,296 DEBUG Configuration:788 - c3p0.min_size=2
[junit] 11:10:44,296 DEBUG Configuration:788 - c3p0.timeout=5000
[junit] 11:10:44,296 DEBUG Configuration:788 - c3p0.max_statements=100
[junit] 11:10:44,296 DEBUG Configuration:788 - c3p0.validate=false
[junit] 11:10:44,328 DEBUG Configuration:939 - null<-org.dom4j.tree.DefaultAttribute@de1b8a [Attribute: name resourc
e value "ks/rah/cainfo/db/model/ContactMeta.hbm.xml"]
[junit] 11:10:44,328 INFO Configuration:287 - Mapping resource: ks/rah/cainfo/db/model/ContactMeta.hbm.xml
[junit] 11:10:44,343 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibernate-mappin
g-2.0.dtd in classpath under net/sf/hibernate/
[junit] 11:10:44,343 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd i
n classpath
[junit] 11:10:44,484 INFO Binder:220 - Mapping class: ks.rah.cainfo.db.model.ContactMeta -> CONTACT_META
[junit] 11:10:44,593 DEBUG Binder:435 - Mapped property: id -> id, type: long
[junit] 11:10:44,625 DEBUG Binder:435 - Mapped property: fieldReference -> FIELD_FK, type: ks.rah.cainfo.db.model.Co
ntactField
[junit] 11:10:44,625 DEBUG Binder:435 - Mapped property: sortOrder -> SORT_ORDER, type: integer
[junit] 11:10:44,625 DEBUG Binder:435 - Mapped property: codeValue -> CODE, type: integer
[junit] 11:10:44,640 DEBUG Binder:435 - Mapped property: displayValue -> displayValue, type: string
[junit] 11:10:44,640 DEBUG Binder:435 - Mapped property: visible -> VISIBLE, type: boolean
[junit] 11:10:44,656 DEBUG Binder:435 - Mapped property: answers, type: java.util.Set
[junit] 11:10:44,656 DEBUG Configuration:939 - null<-org.dom4j.tree.DefaultAttribute@f18e8e [Attribute: name resourc
e value "ks/rah/cainfo/db/model/ContactField.hbm.xml"]
[junit] 11:10:44,671 INFO Configuration:287 - Mapping resource: ks/rah/cainfo/db/model/ContactField.hbm.xml
[junit] 11:10:44,703 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibernate-mappin
g-2.0.dtd in classpath under net/sf/hibernate/
[junit] 11:10:44,703 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd i
n classpath
[junit] 11:10:44,734 INFO Binder:220 - Mapping class: ks.rah.cainfo.db.model.ContactField -> CONTACT_FIELD
[junit] 11:10:44,734 DEBUG Binder:435 - Mapped property: id -> id, type: long
[junit] 11:10:44,750 DEBUG Binder:435 - Mapped property: fieldName -> FIELDNAME, type: string
[junit] 11:10:44,750 DEBUG Binder:435 - Mapped property: fieldHtmlName -> FIELDHTMLNAME, type: string
[junit] 11:10:44,750 DEBUG Binder:435 - Mapped property: htmlInputType -> INPUTTYPE, type: string
[junit] 11:10:44,750 DEBUG Binder:435 - Mapped property: sortOrder -> SORTORDER, type: integer
[junit] 11:10:44,765 DEBUG Binder:435 - Mapped property: meta, type: java.util.Set
[junit] 11:10:44,843 DEBUG Binder:435 - Mapped property: visible -> VISIBLE, type: boolean
[junit] 11:10:44,843 DEBUG Binder:435 - Mapped property: answers, type: java.util.Set
[junit] 11:10:44,843 DEBUG Configuration:939 - null<-org.dom4j.tree.DefaultAttribute@9d6065 [Attribute: name resourc
e value "ks/rah/cainfo/db/model/InfoContact.hbm.xml"]
[junit] 11:10:44,843 INFO Configuration:287 - Mapping resource: ks/rah/cainfo/db/model/InfoContact.hbm.xml
[junit] 11:10:44,859 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibernate-mappin
g-2.0.dtd in classpath under net/sf/hibernate/
[junit] 11:10:44,859 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd i
n classpath
[junit] 11:10:44,890 INFO Binder:220 - Mapping class: ks.rah.cainfo.db.model.InfoContact -> CONTACT
[junit] 11:10:44,906 DEBUG Binder:435 - Mapped property: id -> id, type: long
[junit] 11:10:44,906 DEBUG Binder:435 - Mapped property: user -> USER_FK, type: long
[junit] 11:10:44,906 DEBUG Binder:435 - Mapped property: entryDate -> ENTRYDATE, type: date
[junit] 11:10:44,906 DEBUG Binder:435 - Mapped property: duration -> DURATION, type: long
[junit] 11:10:44,906 DEBUG Binder:435 - Mapped property: contactType -> CONTACTTYPE, type: integer
[junit] 11:10:44,921 DEBUG Binder:435 - Mapped property: data, type: java.util.List
[junit] 11:10:44,921 DEBUG Configuration:939 - null<-org.dom4j.tree.DefaultAttribute@da18ac [Attribute: name resourc
e value "ks/rah/cainfo/db/model/ContactDataNode.hbm.xml"]
[junit] 11:10:44,921 INFO Configuration:287 - Mapping resource: ks/rah/cainfo/db/model/ContactDataNode.hbm.xml
[junit] 11:10:44,953 DEBUG DTDEntityResolver:20 - trying to locate http://hibernate.sourceforge.net/hibernate-mappin
g-2.0.dtd in classpath under net/sf/hibernate/
[junit] 11:10:44,953 DEBUG DTDEntityResolver:29 - found http://hibernate.sourceforge.net/hibern ... ng-2.0.dtd i
n classpath
[junit] 11:10:44,984 INFO Binder:220 - Mapping class: ks.rah.cainfo.db.model.ContactDataNode -> DATANODE
[junit] 11:10:44,984 DEBUG Binder:435 - Mapped property: id -> id, type: long
[junit] 11:10:44,984 DEBUG Binder:435 - Mapped property: contact -> CONTACT_FK, type: ks.rah.cainfo.db.model.InfoCon
tact
[junit] 11:10:44,984 DEBUG Binder:435 - Mapped property: field -> ANSWERFIELD_FK, type: ks.rah.cainfo.db.model.Conta
ctField
[junit] 11:10:45,000 DEBUG Binder:435 - Mapped property: value -> META_FK, type: ks.rah.cainfo.db.model.ContactMeta
[junit] 11:10:45,015 INFO Configuration:985 - Configured SessionFactory: null
[junit] 11:10:45,015 DEBUG Configuration:986 - properties: {java.vendor=Sun Microsystems Inc., show_sql=true, hibern
ate.connection.url=jdbc:mysql://localhost/cainfo_test, c3p0.min_size=2, os.name=Windows 2000, sun.boot.class.path=C:\jdk
\j2sdk1.4.2\jre\lib\rt.jar;C:\jdk\j2sdk1.4.2\jre\lib\i18n.jar;C:\jdk\j2sdk1.4.2\jre\lib\sunrsasign.jar;C:\jdk\j2sdk1.4.2
\jre\lib\jsse.jar;C:\jdk\j2sdk1.4.2\jre\lib\jce.jar;C:\jdk\j2sdk1.4.2\jre\lib\charsets.jar;C:\jdk\j2sdk1.4.2\jre\classes
, hibernate.c3p0.max_size=2, sun.java2d.fontpath=, java.vm.specification.vendor=Sun Microsystems Inc., ant.home=C:\jdk\a
pache-ant-1.6, c3p0.max_size=2, java.runtime.version=1.4.2-b28, hibernate.c3p0.min_size=2, user.name=Administrat

Hmmm. Maybe you should use Calendar.add()....

My gosh!!
It works!
Very red face here, purple even.

Thank you, thank you, thank you!

/F

pleasure