Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Dealing with separate DATE and TIME fields in database, need TIMESTAMP
Dealing with separate DATE and TIME fields in database, need TIMESTAMP [message #379616] Tue, 19 August 2008 17:06 Go to next message
Bill Blalock is currently offline Bill Blalock
Messages: 118
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
Re: Dealing with separate DATE and TIME fields in database, need TIMESTAMP [message #379881 is a reply to message #379616] Wed, 20 August 2008 09:46 Go to previous message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
You could add query keys in EclipseLink for the DATE and TIME fields, but
you would have to query on them separately, not as a timestamp. JPQL does
not have a TIMESTAMP function, so you would have to query the date and
time as separate fields instead.

You could use EclipseLink Expressions or SQL for your query. Expressions
have a getFunction() API to access any database function, you can also
define your own ExpressionOperators.


-- James
Previous Topic:Cache not refreshing?
Next Topic:Everything works with Toplink essential but..
Goto Forum:
  


Current Time: Mon Jul 28 20:40:44 EDT 2014

Powered by FUDForum. Page generated in 0.02211 seconds