Jaws/MySQL date handling

[dsmith]

Jaws doesn't seem to be handling my dates well, I am using jboss 2.4.3 with the "out of the box" settings in standardjaws.xml with MySQL 3.23.36. java.util.Date is mapped to DATETIME but only the date part is captured, and time seems to be mapped to BIGINT for some reason. Anyone know a workable jaws mapping for mysql date/time values?

[mnewcomb]

Make java.util.Date map to a jdbc-type of TIMESTAMP:


<java-type>java.util.Date</java-type>
<jdbc-type>TIMESTAMP</jdbc-type>
<sql-type>DATETIME</sql-type>


I think this is a better mapping because java.util.Date is not just the Date, it is accurate to the millisecond. Whereas the jdbc-type of DATE is just accurate to the day-month-year.

Michael

[sealbb]

yeah i did that by actually using a "Long" to store the datetime, and mapped to MySQL with "BIGINT" and the Jdbc with "BIGINT" also. Btw, boolean with Jdbc's "TINYINT" and MySQL's "TINYINY" doesn't work either. change both to "BIT" and now it works perfect

[bayers]

I was having the same problem but we're using MS SQL 7.0. I tried having my CMP beans use java.util.Date, java.sql.Date, and java.sql.Timestamp map to a DATETIME column in the database and no matter what I used, when the data got put into the database, the time would be "normalized" to 12am. Finally here was my solution. In my xxxBean.java file, the field that would be a date, I declared to be a String. In the jaws.xml file it would look like this:
<cmp-field>
<field-name>dateAdded</field-name>
<column-name>DATE_ADDED</column-name>
<jdbc-type>TIMESTAMP</jdbc-type>
<sql-type>TIMESTAMP</sql-type>
</cmp-field>

In order for clients of this bean to still use a java.util.Date value, I put the responsibility of converting from String to Date (actually java.sql.Timestamp since that's what maps to MS SQL's datetime) on the bean. For example, here are a couple of member functions from my OrderHistoryBean.java:
public Date getDateAdded() {
return( Timestamp.valueOf( dateAdded ) );
}

public void setDateAdded( Date dateAdded ) {
this.dateAdded = (new Timestamp( dateAdded.getTime() )).toString();
}

Note that this is based on CMP1.1. I don't think in CMP2.0 that you can have member functions use a different data type than what the field truely is. Did that all make sense? I hope so.

Brad

[erik777]

I'm not having any trouble getting the Time along with the date into the mySQL database.
My problem is with a subsequent finder.
For some reason, the finder only includes the date in the WHERE clause,
which means that it never finds it.

I can see that it has the time in the Date variable it is passing to the finder,
which it actually got by doing a get on the bean that it created when it inserted it into the database:
[pre][Default] finding session where loginTime=Sun Nov 25 22:33:39 EST 2001[/pre]
Here is the insert it does before it attempts the find:
[pre]INSERT INTO Sessions (randomId,logoutType,loginTime,activityTime,logoutTime,userId,domainId,id)
VALUES (84352878,null,'2001-11-25 22:33:39',null,null,1,null,0)[/pre]
Yet the SELECT JAWS produces only includes the date:
[pre]SELECT Sessions.id FROM Sessions where randomId = 84352878 AND userId = 1 AND
domainId = null AND loginTime = '2001-11-25'[/pre]
Here is the JAWS finder:
[pre]
findByRandomUserDomainLogin
randomId = {0} AND userId = {1} AND
domainId = {2} AND loginTime = {3}

randomId
<read-ahead>true</read-ahead>
[/pre]
and the JAWS cmp-field entry:
[pre] <cmp-field>
<field-name>loginTime</field-name>
<column-name>loginTime</column-name>
<jdbc-type>TIMESTAMP</jdbc-type>
<sql-type>DATETIME</sql-type>
</cmp-field>[/pre]
Does anyone know how to fix this?

Erik

[bayers]

Erik,

That's a strange one. It seems like it should work. The only thing that came to mind is that perhaps there's a bug in JAWS which cuts the string at the first whitespace. Notice your date and time are seperated by a space.

Brad

[erik777]

I got it to work by declaring the parameter on the finder as a java.sql.Timestamp
[pre] public Session findByRandomUserDomainLogin(
Integer randomId, Integer userId, Integer domainId, java.sql.Timestamp loginTime )
throws FinderException, RemoteException;[/pre]
and then converting the Date value to a Timestamp value when passing the parameter to the finder
[pre] theSession = sessionHome.findByRandomUserDomainLogin(
randomId, userId, domainId, new Timestamp( loginTime.getTime() ) );[/pre]
It appears as though JAWS will recognize a Date type as including the time on a bean property for create/retrieve/update purposes when its jdbc-type is declared as TIMESTAMP, but does not automatically do the same on a finder parameter unless you also declare the parameter type to be java.sql.Timestamp.

It makes sense when you consider that your finder's FROM and WHERE clauses can include tables and columns that aren't even part of the bean. Additionally, while Date includes the time, I guess that JAWS needs some way to have a Date that doesn't include a time, particularly on a WHERE clause. If it used the time by default on a Date type, then how would you be able to accomplish this.

[guoqiao]

Is it possible to pass in argument to use in the order part, say:


findByRandomUserDomainLogin
randomId = {0} AND userId = {1}
randomId LIMIT {2}
<read-ahead>true</read-ahead>


where I use the third argument to control the selected record number.

thanks.