1

I have following web application: Users can enter java simple date format patterns and a date (of course matching to the java simple date format pattern) and I want to store these date in an oracle database.

Therefore I need to translate the java simple date format pattern into the oracle pattern. E.g:

  • "dd-MM-yyyy HH:mm:ss" into "DD-MM-YYYY HH24:MI:SS"
  • "dd-MM-yy HH:mm:ss" into "DD-MM-YY HH24:MI:SS"
  • "dd-MM-yy HH:mm" into "DD-MM-YY HH24:MI"

and so on.

Instead of the following code just having one SimpleDateFormat I would like to have all or at least a big bunch of SimpleDateFormatPatterns translated into Oracle pattern:

SimpleDateFormat sFormat = new SimpleDateFormat( "dd-MM-yyyy HH:mm:ss");
String sqlSnippet = "TO_DATE('" + sFormat.format(date) + "','DD-MM-YYYY HH24:MI:SS')";

Is there a library or maybe just a mapping list to do this?

Thanks.

Edit: I need to build the SQL by hand as the user defines the criteria, compare operators and joins in the user interface. In the end I have something like this

AND col2 > TO_DATE('26-09-2012','DD-MM-YYYY')
timguy
  • 2,063
  • 2
  • 21
  • 40

2 Answers2

3

Therefore I need to translate the java simple date format pattern into the oracle pattern

No, you don't. You should instead use a PreparedStatement, and call setDate or setTimestamp on it to specify the value you're interested in.

Avoid string conversions unless they're fundamentally part of what you're trying to do (e.g. displaying a date/time in a UI). For simply transferring information from your app to your database or vice versa, you should reduce the number of conversions required as far as possible.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Thanks for that fast reply but I need to build up this sql statement by hand. The User can define criteria for the WHERE clause in the user interface (joins and further stuff as well) as well as the compare operators. Maybe I can use the inner API of the setDate. – timguy Aug 14 '14 at 08:49
  • 1
    Just because you need to build *some* of the SQL dynamically doesn't mean you have to do it *all* dynamically. I *strongly, strongly, strongly* urge you not to do so. Specifying values directly in SQL is *fraught* with danger. You can still create the SQL dynamically, but include parameter specifiers in there and use `PreparedStatement.setDate` etc. – Jon Skeet Aug 14 '14 at 08:55
  • Ok, I will try to solve it this way. Anyway currently I can not imagine if I just give a `Date` how the method will know if I want to end up with SQL like this `DD-MM-YYYY` or this `DD-MM-YYYY HH24:MI:SS` which makes a big difference in the WHERE clause. – timguy Aug 14 '14 at 09:08
  • @user239293: Why would it make a big difference in the where clause? What do you expect the difference to be after you've called `TO_DATE`? You really need to think about what fundamental data you're using. Maybe you need to differentiate between `DATE` and `TIMESTAMP` values instead, and make sure your fields are of the appropriate type. – Jon Skeet Aug 14 '14 at 09:59
  • You are right. It doesn't make a difference in WHERE clause. I mixed it up with trunc and to_char. – timguy Aug 15 '14 at 14:00
0

The Answer by Jon Skeet is correct but is now outdated in referring to some legacy classes. The java.sql.Date and java.sql.Timestamp and related classes are now supplanted by the java.time classes, LocalDate and Instant respectively. But your Question demands the LocalDateTime class.

Smart objects, not dumb strings

You objects to represent your date-time values. As of JDBC 4.2 and later, you can directly exchange java.time objects with your database.

Your inputs lack any indicator of time zone or offset-from-UTC. So parse in Java as a LocalDateTime for storage in a column of a type similar to SQL-standard TIMESTAMP WITHOUT TIME ZONE. 

DateTimeFormatter f1 = DateTimeFormatter.ofPattern( "dd-MM-uuuu HH:mm:ss" ) ;
DateTimeFormatter f2 = DateTimeFormatter.ofPattern( "dd-MM-uu HH:mm:ss" ) ;
DateTimeFormatter f3 = DateTimeFormatter.ofPattern( "dd-MM-uu HH:mm" ) ;

Choose a formatter by length of the input string.

LocalDateTime ldt = LocalDateTime.parse( myInputString , f2 ) ;

I need to build the SQL by hand as the user defines the criteria, compare operators and joins in the user interface. In the end I have something like this

String sqlSnippet = "TO_DATE('" + sFormat.format(date) + "','DD-MM-YYYY HH24:MI:SS')";

No, do not embed your date-time value as text in a String of SQL. Instead, use a PreparedStatement with ? placeholders replaced with your LocalDateTime object.

String sql = "SELECT when FROM tbl WHERE when > ? ;" ;

Pass the object to be slipped into that placeholder at runtime.

myPreparedStatement.setObject( … , ldt ) ;

Retrieval:

LocalDateTime ldt = myResultSet.getObject( … ; LocalDateTime.class ) ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154