ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Cruz, Edward J." <e.c...@radium.ncsc.mil>
Subject Querying Based on Date.
Date Fri, 27 May 2005 21:24:52 GMT
I am having trouble with a query based on a date property.  Basically I need
to return data where a Transaction_Date in the database is equal to a date I
pass in through an object to the Ibatis framework.  The database holds
Transaction_Date as a Date type and my parameter object also has a
java.util.Date type.  I created a Date using the java SimpleDateFormat as
follows. 

SimpleDateFormat date = new SimpleDateFormat(yyyyMMdd); 
Date transactionDate = date.parse("20040205");

I used DbUnit to insert the transactionDate into the database.  Then I
called the iBatis framework passing in an Transfer Object that holds
transactionDate and has the appropriate getters and setters.  

My xml is as follows 
    searchCriteria has the appropriate type alias.  
	
    <select id="queryForTotalRecords" parameterClass="searchCriteria"
resultsClass="java.lang.Integer"> 
	Select count(*) from orders 
	<dynamic prepend="where"> 
		<isNotNull prepend="AND" property="transactionDate"> 
			TRANSACTION_NUM = #transactionDate:date#
                        </isNotNull>
	</dynamic>
    </select>
....  I have other properties that are all strings and work well in the same
manner using isNotNull that is why I have the prepend="AND" above.

When looking at the log file when this runs it shows the following
PreparedStatement: select count(*) from orders where TRANSACTION_NUM = ?
Parameters: [2004-02-05 00:00:00.0]
Types: [java.sql.Timestamp]

The results of invoking this query when the same Date is entered into the
database is 0.  When it should return 1.  Is there something special that
needs to be done to compare Dates?  I even tried using both greater than and
less than operators and I always receive 0 as my count.  Any Suggestions
would be appreciated. 


Jamie Cruz
Software Engineering 
e.cruz@radium.ncsc.mil 





Mime
View raw message