Using setDate in PreparedStatement

java

date-formatting

jdbc-date-escape-format

java-time-api

byAlex Kataev·Oct 8, 2024

To inscribe a date into a SQL repository, harness setDate on your PreparedStatement with a java.sql.Date. Here's the quick way:

PreparedStatement pstmt = connection.prepareStatement("INSERT INTO table_name (date_column) VALUES (?)");
pstmt.setDate(1, new java.sql.Date(new java.util.Date().getTime())); // Who said time travel isn't possible?
pstmt.executeUpdate();

Note: Equip setDate with matching index to align with the ? placeholder in your SQL query.

How to transform util.Date into SQL types

Your java.util.Date needs some shape-shifting magic in order to work with SQL:

Get date only, no timestamp: new java.sql.Date(utilDate.getTime()).
Need timestamp as well? Use new java.sql.Timestamp(utilDate.getTime()).

Quickly insert today's date or timestamp like so:

pstmt.setDate(1, new java.sql.Date(System.currentTimeMillis()));
pstmt.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis())); // SQL meets Back to the Future!

Modern Java: Using LocalDateTime and LocalDate

From JDBC 4.2 and above, LocalDate and LocalDateTime objects can be set straight using setObject:

pstmt.setObject(1, LocalDate.now());
pstmt.setObject(1, LocalDateTime.now());

This implementation is free of conversion chaos and leverages the updated java.time API.

Correct formatting and compatibility

Keep compatibility in check and remember the JDBC date escape format (yyyy-mm-dd) when setting string dates:

pstmt.setDate(1, java.sql.Date.valueOf("2023-04-14")); // Taking a safe trip to 2023!

To avoid rocky roads, don't directly set date strings. Parse and convert them properly using SimpleDateFormat or the equivalent java.time framework tools.

Going beyond: Advanced tips

Time zones: Keeping up with the world

To get a date as per your specific timezone:

LocalDate localDate = LocalDate.now(ZoneId.of("America/New_York")); // I <3 NY!
pstmt.setObject(1, localDate);

Server-side date handling: One for the team

Consider server-side integrated solutions like SYSDATE or insert triggers that autonomously manage date values. It's like having your SQL be your trustworthy assistant!

Time travel with java.time

Transitioning to the java.time framework lets you step away from the problematic java.util and java.sql date-time categories. You'd be stepping into a more resilient and reliable era of handling dates and times.

Back to the future with ThreeTen

For the folks using Java 6 & 7, TheeTen-Backport is at your service for adopting the new java.time functionality and simplifying your temporal manipulation journey.

explain-codes / Java / Using setDate in PreparedStatement

Linked

A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)



How to convert java.util.Date to java.sql.Date?



How to convert java.sql.timestamp to LocalDate (java8) java.time?



How do I get a Date without time in Java?



Gson - Date format

