I'm doing integration testing in a Hsqldb. My production database is an Oracle Database.
Hibernate : 4.1.3.final.
Hsqldb : 2.3.3. ( I can't use the 2.3.4 because it can't run all my junit tests in one click.)
I met two different exceptions when I had to test methods that contains queries where the WHERE condition asks a date :
org.hsqldb.HsqlException: incompatible data types in combination
or
org.hsqldb.HsqlException: data exception: invalid datetime format
Codes that caused the exception
The code below caused the first exception (incompatible data types in combination)
select = "from Player player where player.creationDate = to_char(sysdate,'dd/MM/yy')"
The code below caused the second exception (invalid datetime format)
select = "from Player player where player.creationDate > '01/01/2016'"
1) So obviously, the problem stems from the date format. Hsqldb supports the 'yyyy-MM-dd' date format so when I changed to_char(sysdate,'dd/MM/yy') and 01/01/2016 into 1995-01-01, it worked fine.
However, I can't change the date format of these methods.
2) On second thoughts, I considered changing the hsqldb source code, but it seemed a little bit radical.
Are there any options than the latter?
Thank you for your time.
Dont try to format the date. Use query with named parameters instead. Then you can use setters for all data types. See example below:
String hql = "from Player player where player.creationDate = :date";
List result = session.createQuery(hql)
.setDate("date", new Date()).list();