Using setDate in PreparedStatement
❐ 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 formatyyyy-[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 typejava.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 8ps.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 formatyyyy-[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 typejava.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 8ps.setTimestamp(2, java.sql.Timestamp.from(java.time.Instant.now()));ps.setTimestamp(2, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));
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.
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);