The Answer by Elevate is correct as to the cause of your exception being unrelated to date-time handling.
But you do have problems with your date-time handling.
- Ignoring crucial issue of time zone.
- Using dumb strings instead of smart objects.
Here is a full example use the H2 Database.
A ZonedDateTime object represents a moment, a point on the timeline, as seen through the wall-clock time used by the people of a certain region (a time zone).
We can view that same moment, that same point on the timeline, through the wall-clock time of UTC simply by extracting a Instant from our ZonedDateTime. The JDBC driver for H2 Database requires an Instant, as its PreparedStatement::setObject method seems unable to deal with a ZonedDateTime directly. Most databases, including H2, store a TIMESTAMP WITH TIME ZONE in UTC. Some will adjust incoming values from another time zone to UTC, but apparently H2 requires us to do the adjustment.
package com.basilbourque.example;
import org.h2.jdbcx.JdbcDataSource;
import java.sql.*;
import java.time.*;
public class App {
public static void main ( String[] args ) {
App app = new App();
app.doIt();
}
private void doIt () {
JdbcDataSource ds = new JdbcDataSource();
ds.setURL( "jdbc:h2:mem:bogus;DATABASE_TO_UPPER=FALSE;DB_CLOSE_DELAY=-1" ); // Create in-memory database that disappears when this JVM exits.
ds.setUser( "sa" ); // Arbitrary.
ds.setPassword( "sa" );
ZoneId z = ZoneId.of( "America/Montreal" ); // Arbitrarily chosen time zone.
// Define "event" table with 3 columns: id number auto-generated, beginning of event, ending of event.
String sql = "CREATE TABLE event_ ( id_ INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY , start_ TIMESTAMP WITH TIME ZONE NOT NULL, stop_ TIMESTAMP WITH TIME ZONE NOT NULL ) ;";
try (
Connection conn = ds.getConnection() ;
Statement stmt = conn.createStatement() ;
) {
stmt.executeUpdate( sql );
} catch ( SQLException e ) {
e.printStackTrace();
}
// Insert rows for years 1972, 1977, & 1973.
sql = "INSERT INTO event_ ( start_ , stop_ ) VALUES ( ? , ? ) ; ";
try (
Connection conn = ds.getConnection() ;
PreparedStatement ps = conn.prepareStatement( sql ) ;
) {
ps.setObject( 1 , LocalDate.of( 1972 , Month.MAY , 1 ).atStartOfDay( z ).toInstant() );
ps.setObject( 2 , LocalDate.of( 1972 , Month.MAY , 2 ).atStartOfDay( z ).toInstant() );
ps.executeUpdate();
ps.setObject( 1 , LocalDate.of( 1977 , Month.JUNE , 1 ).atStartOfDay( z ).toInstant() );
ps.setObject( 2 , LocalDate.of( 1977 , Month.JULY , 2 ).atStartOfDay( z ).toInstant() );
ps.executeUpdate();
ps.setObject( 1 , LocalDate.of( 1973 , Month.AUGUST , 1 ).atStartOfDay( z ).toInstant() );
ps.setObject( 2 , LocalDate.of( 1973 , Month.AUGUST , 2 ).atStartOfDay( z ).toInstant() );
ps.executeUpdate();
} catch ( SQLException e ) {
e.printStackTrace();
}
// Dump table.
System.out.println( "Dumping event_ table. " );
sql = "SELECT * FROM event_ ; ";
try (
Connection conn = ds.getConnection() ;
PreparedStatement ps = conn.prepareStatement( sql ) ;
ResultSet rs = ps.executeQuery() ;
) {
while ( rs.next() ) {
System.out.println( "id_: " + rs.getInt( 1 ) + " | " + rs.getObject( 2 , Instant.class ).atZone( z ) + " to " + rs.getObject( 3 , Instant.class ).atZone( z ) );
}
} catch ( SQLException e ) {
e.printStackTrace();
}
// Deleting rows for events starting before beginning of 1976. Should delete 2 of our 3 rows, leaving 1 row.
System.out.println( "Deleting rows. " );
sql = "DELETE FROM event_ WHERE start_ < ? ; ";
try (
Connection conn = ds.getConnection() ;
PreparedStatement ps = conn.prepareStatement( sql ) ;
) {
ps.setObject( 1 , Year.of( 1976 ).atDay( 1 ).atStartOfDay( z ).toInstant() ); // First moment of 1976 in our time zone.
ps.executeUpdate();
} catch ( SQLException e ) {
e.printStackTrace();
}
// Dump table.
System.out.println( "Dumping event_ table. " );
sql = "SELECT * FROM event_ ; ";
try (
Connection conn = ds.getConnection() ;
PreparedStatement ps = conn.prepareStatement( sql ) ;
ResultSet rs = ps.executeQuery() ;
) {
while ( rs.next() ) {
System.out.println( "id_: " + rs.getInt( 1 ) + " | " + rs.getObject( 2 , Instant.class ).atZone( z ) + " to " + rs.getObject( 3 , Instant.class ).atZone( z ) );
}
} catch ( SQLException e ) {
e.printStackTrace();
}
}
}
When run.
Dumping event_ table.
id_: 1 | 1972-05-01T00:00-04:00[America/Montreal] to 1972-05-02T00:00-04:00[America/Montreal]
id_: 2 | 1977-06-01T00:00-04:00[America/Montreal] to 1977-07-02T00:00-04:00[America/Montreal]
id_: 3 | 1973-08-01T00:00-04:00[America/Montreal] to 1973-08-02T00:00-04:00[America/Montreal]
Deleting rows.
Dumping event_ table.
id_: 2 | 1977-06-01T00:00-04:00[America/Montreal] to 1977-07-02T00:00-04:00[America/Montreal]
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.
