JPA map simple String object to Oracle Date type?

Question

I have a table in which date column is stores as varchar. while doing the Object relational mapping (i am using eclipselink) i want to map this string/varchar to Date object in java.

My table is :

T_BUSINESS
  TRAN_ID        Number, 
  BUSINESS_DATE  Varchar

and I want to select all the rows where business date is between two input dates, here problem is since BUSINESS_DATE is varchar i cannot carry out the comparison. I can't modify the table and i don't wanna use the Native sql.

Can anybody tell me how can I do this.

Thanks in advance!

I'm not familiar with eclipselink, but I'm quite suree you'll need to write a custom data type to transform the varchar in whatever format you store it, to a java.util.Date. — Augusto
– Augusto, Commented Sep 26, 2012 at 8:29
Do not store dates as VARCHAR. Just don't. You'll save yourself (and your co-workers) a lot of trouble in the long run. — user330315
– user330315, Commented Sep 26, 2012 at 13:11

James · Accepted Answer · 2012-09-26 13:06:53Z

1

You can use a TypeConverter to map this,

See,

http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_typeconverter.htm#CHDHCCJF

Doing comparisons in queries may be more difficult, I think the default date format is yyyy-mm-dd so a varchar comparison may work. Otherwise you really need a real DATE field on the database. You could use a database function to convert the varchar to a DATE for comparison, such as using CAST or FUNCTION which EclipseLink support is JPQL.

See, http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_cast.htm#cast

http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_func.htm#func

answered Sep 26, 2012 at 13:06

James

18.5k13 gold badges100 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JPA map simple String object to Oracle Date type?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related