| Dealing with separate DATE and TIME fields in database, need TIMESTAMP [message #379616] |
Tue, 19 August 2008 21:06  |
 Bill Blalock
Messages: 119
Registered: July 2009
Location: Alabama |
Senior Member |
|
|
Background:
I am working with a data base that has separate DATE and TIME fields, it
really should have been one TIMESTAMP field.
I am using Ecliseslink workbench and Europa (3.3) Eclipse. I am trying to
replace Ibatis with Eclipselink.
Problem:
When using SQL with JDBC and Ibatis I use something like this to give a
TIMESTAMP field:
select FSUSRL, FSOPID, FSOPIN,
TIMESTAMP (FSDATE, FSTIME) as FSTMSP from TCPEXT.fslog00
where FSOPID in (6, 7) and
TIMESTAMP (FSLU, FSTM) > #value#
order by TIMESTAMP (FSLU, FSTM)
The timestamp isn't a field in the data base, but using this sort of fudge
I am able to get it into the java data object, query with it, etc.
This works both way, I can read a Timestamp from the data base and I can
query against the database using a Timestamp (#value#).
For Eclipselink I put this transformer into the class which represents the
data base record:
public Date transformFsTmsp(Record rec) {
java.sql.Date fsDate = (java.sql.Date)rec.get("FSLOG00.FSDATE");
java.sql.Time fsTime = (java.sql.Time)rec.get("FSLOG00.FSTIME");
fsTmsp = new Date();
try {
fsTmsp.setTime(sdf.parse(fsDate + " " + fsTime).getTime());
} catch (ParseException pe) {
fsTmsp.setTime(fsDate.getTime());
}
return fsTmsp;
}
This lets me read a Date which represents a Timestamp from the data base.
This is a FTP server log so I won't be writing to it.
How should this problem be handled with Eclipselink?
How can I query against the data base using Date objects? I am having a
lot of trouble getting a named query to work. It won't let me use the
fsTmsp field created with the Transfomer method -- fsTmsp doesn't appear
in the list of query keys.
Can I use SQL selection in the name query? Say the query had an argument,
afterDate, a java.util.Date. Can something like the Ibatis example work?
select FSUSRL, FSOPID, FSOPIN,
TIMESTAMP (FSDATE, FSTIME) as FSTMSP from TCPEXT.fslog00
where FSOPID in (6, 7) and
TIMESTAMP (FSLU, FSTM) > #value#
order by TIMESTAMP (FSLU, FSTM)
the passed parameter (afterDate) is converted to a string and replaces
#value# by the API.
I could not find any examples showing how to use SQL in the selection part
of a named query with parameters.
Thanks all.
Bill Blalock
|
|
|
|
Powered by
FUDForum. Page generated in 0.03259 seconds
] 
Search
Help
Register
Login
Home
