Handling Oracle TimeStamp from JPA

[karthik sai]

HI,

I am using oracle 11g, JPA -Eclipse link implementation. The oracle has a table called currency

TABLE currency
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE NOT NULL CHAR(3)
DESCRIPTION VARCHAR2(120)
CRT_ID VARCHAR2(10)
CRT_TIMESTAMP TIMESTAMP(6)

code + crt_timestamp is a composite primary key

Data in oracle
===========

COD DESCRIPTION CRT_ID CRT_TIMESTAMP
ind india system 05-AUG-12 01.47.02.250000 PM

Using JPA Named query, i am trying to retrieve the currency code with crt_timestamp in the query where clause.

The PO field for crt_timestamp is java.util.Date, i am unable to retrieve the result set since the the crt_timestamp format is not known.


The below code snippet i tried, but unable to retrieve the data

String dateFormat = "dd-MMM-yy HH.mm.ss.SSS aa"; //i see date format in oracle 11g
String date = "05-AUG-12 01.47.02.250000 PM";

DateFormat s = new SimpleDateFormat(dateFormat);
Date d = null;
try {
d = (Date)s.parse(date);
} catch (ParseException e) {
}

Timestamp ts = new Timestamp(d.getTime());

Currency resultBook = (Currency)em
.createQuery("select b from Currency b where crt_Timestamp="+ts)
.setMaxResults(1)
.getSingleResult();


Please could you shed some light on this and tell me if it will be done.

[Bill Gorder]

In the future please UseCodeTags.

You have not showed us your currency object containing your mappings. But it should look something like this:

@Temporal(TemporalType.TIMESTAMP)
@Column(name="CRT_TIMESTAMP")
private Date someTimeStamp;

[James Sutherland]

The timestamp format in JPQL is, {ts'yyyy-mm-dd hh:mm:ss.nnnnnnnnn'}

But in general you should use a parameter,

See,
http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Parameters