Using setDate in PreparedStatement

Question

In order to make our code more standard, we were asked to change all the places where we hardcoded our SQL variables to prepared statements and bind the variables instead.

I am however facing a problem with the setDate().

Here is the code:

DateFormat dateFormatYMD = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
DateFormat dateFormatMDY = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
Date now = new Date();
String vDateYMD = dateFormatYMD.format(now);
String vDateMDY = dateFormatMDY.format(now);
String vDateMDYSQL =  vDateMDY ;
java.sql.Date date = new java.sql.Date(0000-00-00);

requestSQL = "INSERT INTO CREDIT_REQ_TITLE_ORDER (REQUEST_ID," + 
             " ORDER_DT, FOLLOWUP_DT) " +  "values(?,?,?,)";

prs = conn.prepareStatement(requestSQL);
prs.setInt(1,new Integer(requestID));
prs.setDate(2,date.valueOf(vDateMDYSQL));
prs.setDate(3,date.valueOf(sqlFollowupDT));

I get this error when the SQL gets executed:

java.lang.IllegalArgumentException
    at java.sql.Date.valueOf(Date.java:138)
    at com.cmsi.eValuate.TAF.TAFModuleMain.CallTAF(TAFModuleMain.java:1211)

Should I use setString() instead with a to_date()?

See http://stackoverflow.com/questions/6874146/jdbc-prepared-statement-setdate-doesnt-save-the-time-just-the-date-h — Vadzim, Apr 13 '15 at 10:05

score 173 · Accepted Answer · edited May 02 '17 at 12:40

❐ Using `java.sql.Date`

If your table has a column of type DATE:

java.lang.String

The method java.sql.Date.valueOf(java.lang.String) received a string representing a date in the format yyyy-[m]m-[d]d. e.g.:
```
ps.setDate(2, java.sql.Date.valueOf("2013-09-04"));
```
java.util.Date

Suppose you have a variable endDate of type java.util.Date, you make the conversion thus:
```
ps.setDate(2, new java.sql.Date(endDate.getTime());
```

Current

If you want to insert the current date:

ps.setDate(2, new java.sql.Date(System.currentTimeMillis()));

// Since Java 8
ps.setDate(2, java.sql.Date.valueOf(java.time.LocalDate.now()));

❐ Using `java.sql.Timestamp`

If your table has a column of type TIMESTAMP or DATETIME:

java.lang.String

The method java.sql.Timestamp.valueOf(java.lang.String) received a string representing a date in the format yyyy-[m]m-[d]d hh:mm:ss[.f...]. e.g.:
```
ps.setTimestamp(2, java.sql.Timestamp.valueOf("2013-09-04 13:30:00");
```
java.util.Date

Suppose you have a variable endDate of type java.util.Date, you make the conversion thus:
```
ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));
```

Current

If you require the current timestamp:

ps.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));

// Since Java 8
ps.setTimestamp(2, java.sql.Timestamp.from(java.time.Instant.now()));
ps.setTimestamp(2, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));

+1 also I think its worth noting that if the date is not the current date but a java Date object, you can use getTime() method to get it to work in a similar manner. — , Sep 04 '13 at 13:34
I see an answer with the similar info was added while I was writing my comment :) — , Sep 04 '13 at 13:36
FYI, this Answer is correct but now out-of-date. These troublesome badly-designed old date-time classes are now legacy, supplanted by the java.time classes. — Basil Bourque, Aug 25 '17 at 16:39
I got an error saying `TypeError: Cannot read property "sql" from undefined.` when running `ps.setTimestamp(2, java.sql.Timestamp.valueOf("2013-09-04 13:30:00");`. — Brian, Jun 21 '18 at 05:50

Basil Bourque · Answer 2 · 2018-06-06T21:19:32.997

tl;dr

With JDBC 4.2 or later and java 8 or later:

myPreparedStatement.setObject( … , myLocalDate  )

…and…

myResultSet.getObject( … , LocalDate.class )

Details

The Answer by Vargas is good about mentioning java.time types but refers only to converting to java.sql.Date. No need to convert if your driver is updated.

java.time

The java.time framework is built into Java 8 and later. These classes supplant the old troublesome date-time classes such as java.util.Date, .Calendar, & java.text.SimpleDateFormat. The Joda-Time team also advises migration to java.time.

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

Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport and further adapted to Android in ThreeTenABP.

`LocalDate`

In java.time, the java.time.LocalDate class represents a date-only value without time-of-day and without time zone.

If using a JDBC driver compliant with JDBC 4.2 or later spec, no need to use the old java.sql.Date class. You can pass/fetch LocalDate objects directly to/from your database via PreparedStatement::setObject and ResultSet::getObject.

LocalDate localDate = LocalDate.now( ZoneId.of( "America/Montreal" ) );
myPreparedStatement.setObject( 1 , localDate  );

…and…

LocalDate localDate = myResultSet.getObject( 1 , LocalDate.class );

Before JDBC 4.2, convert

If your driver cannot handle the java.time types directly, fall back to converting to java.sql types. But minimize their use, with your business logic using only java.time types.

New methods have been added to the old classes for conversion to/from java.time types. For java.sql.Date see the valueOf and toLocalDate methods.

java.sql.Date sqlDate = java.sql.Date.valueOf( localDate );

…and…

LocalDate localDate = sqlDate.toLocalDate();

Placeholder value

Be wary of using 0000-00-00 as a placeholder value as shown in your Question’s code. Not all databases and other software can handle going back that far in time. I suggest using something like the commonly-used Unix/Posix epoch reference date of 1970, 1970-01-01.

LocalDate EPOCH_DATE = LocalDate.ofEpochDay( 0 ); // 1970-01-01 is day 0 in Epoch counting.

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?

Java SE 8, Java SE 9, Java SE 10, and later
- Built-in.
- Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
- Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

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.

score 4 · Answer 3 · answered Sep 04 '13 at 13:31

The docs explicitly says that java.sql.Date will throw:

IllegalArgumentException - if the date given is not in the JDBC date escape format (yyyy-[m]m-[d]d)

Also you shouldn't need to convert a date to a String then to a sql.date, this seems superfluous (and bug-prone!). Instead you could:

java.sql.Date sqlDate := new java.sql.Date(now.getTime());
prs.setDate(2, sqlDate);
prs.setDate(3, sqlDate);

score 3 · Answer 4 · answered Sep 04 '13 at 13:32

The problem you're having is that you're passing incompatible formats from a formatted java.util.Date to construct an instance of java.sql.Date, which don't behave in the same way when using valueOf() since they use different formats.

I also can see that you're aiming to persist hours and minutes, and I think that you'd better change the data type to java.sql.Timestamp, which supports hours and minutes, along with changing your database field to DATETIME or similar (depending on your database vendor).

Anyways, if you want to change from java.util.Date to java.sql.Date, I suggest to use

java.util.Date date = Calendar.getInstance().getTime();
java.sql.Date sqlDate = new java.sql.Date(date.getTime()); 
// ... more code here
prs.setDate(sqlDate);

score 1 · Answer 5 · answered Sep 06 '13 at 01:31

1

If you want to add the current date into the database, I would avoid calculating the date in Java to begin with. Determining "now" on the Java (client) side leads to possible inconsistencies in the database if the client side is mis-configured, has the wrong time, wrong timezone, etc. Instead, the date can be set on the server side in a manner such as the following:

requestSQL = "INSERT INTO CREDIT_REQ_TITLE_ORDER ("   +
                "REQUEST_ID, ORDER_DT, FOLLOWUP_DT) " +
                "VALUES(?, SYSDATE, SYSDATE + 30)";

...

prs.setInt(1, new Integer(requestID));

This way, only one bind parameter is required and the dates are calculated on the server side will be consistent. Even better would be to add an insert trigger to CREDIT_REQ_TITLE_ORDER and have the trigger insert the dates. That can help enforce consistency between different client apps (for example, someone trying to do a fix via sqlplus.

answered Sep 06 '13 at 01:31

Glenn

8,932
2
41
54

Will this still allow Oracle to re-use the same execution plan. According to this [link](http://use-the-index-luke.com/sql/where-clause/bind-parameters) :- re-use of execution plan - which saves time in case or preparedStatements or bind variables works only if the SQL statement is exactly the same. If you put different values into the SQL statement, the database handles it like a different statement and recreates the execution plan. Will SYSDATE be treated as different value? Thanks! – JavaTec Dec 05 '16 at 20:43
1

@JavaTec The definitive answer would be to look in the sql cache and run a few tests. I no longer have an Oracle server available to do some testing, however, I do recall that the versions of Oracle I was using (<= 11g), a hash of the first n characters was used to check the sql cache to find the matching statement. Since the sql string submitted doesn't actually change, my guess would be that, yes, the same execution plan would be used. – Glenn Dec 06 '16 at 03:06

score -1 · Answer 6 · edited May 09 '14 at 06:06

Not sure, but what I think you're looking for is to create a java.util.Date from a String, then convert that java.util.Date to a java.sql.Date.

try this:

private static java.sql.Date getCurrentDate(String date) {

    java.util.Date today;
    java.sql.Date rv = null;
    try {

        SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy");
        today = format.parse(date);
        rv = new java.sql.Date(today.getTime());
        System.out.println(rv.getTime());

    } catch (Exception e) {
        System.out.println("Exception: " + e.getMessage());
    } finally {
        return rv;
    }

}

Will return a java.sql.Date object for setDate();

The function above will print out a long value:

1375934400000

Using setDate in PreparedStatement

6 Answers6

❐ Using `java.sql.Date`

❐ Using `java.sql.Timestamp`

tl;dr

Details

java.time

`LocalDate`

Before JDBC 4.2, convert

Placeholder value

About java.time

Linked

Related

Using setDate in PreparedStatement

6 Answers6

❐ Using java.sql.Date

❐ Using java.sql.Timestamp

tl;dr

Details

java.time

LocalDate

Before JDBC 4.2, convert

Placeholder value

About java.time

Linked

Related

❐ Using `java.sql.Date`

❐ Using `java.sql.Timestamp`

`LocalDate`