LocalDate::format
As others pointed out, your line System.out.println("Date: " + ld); implicitly calls LocalDate::toString without making use of your DateTimeFormatter object. So your custom formatting pattern is never applied.
Apply your formatter while generating text. Instead of calling LocalDate::toString, call LocalDate::format.
String output = ld.format( formatter ) ;
System.out.println( "output: " + output ) ;
20/06/2020
You said:
the query get visualized in the JDBC default format (yyyy-mm-dd).
No, there is no such thing as a “JDBC default format”. The text produced by LocalDate::toString is in standard ISO 8601 format, YYYY-MM-DD. This has nothing to do with JDBC.
Your code has other issues. So read on.
Use smart objects, not dumb strings
Assuming your database column is of a type akin to the SQL standard type DATE, retrieve the value as a LocalDate object using a JDBC driver compliant with JDBC 4.2 or later. Stop thinking in terms of text. Think in terms of smart objects, not dumb strings.
LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;
Likewise, send appropriate objects to the database rather than mere text. Use a prepared statement with ? placeholders.
myPreparedStatement.setObject( … , localDate ) ; // Send object to database, to be written into row in table.
Separate retrieval from formatting/presentation
Only later should you think about formatting the data for presentation to the user. Generally best to separate the retrieval of data from the formatting/presentation of data. Collect your data into objects first, then later generate text for presentation.
Collecting data into objects is much easier and simpler with the upcoming Records feature previewed in Java 14 and arriving in Java 15. Constructor, getter methods, toString, equals & hashCode, and so on are all synthesized automatically behind the scenes.
record Order ( UUID id , String description , LocalDate whenPlaced ) {} // That is all the code you need. Constructor, getter methods, `toString`, `equals` & `hashCode`, and so on are all synthesized automatically behind the scenes.
Generate text in standard ISO 8601 format that represents the value within the object. Be aware that a LocalDate object has no text, has no “format”. Such an object knows how to parse text, and knows how to generate text, is not itself text.
String output = localDate.toString() ;
Let java.time automatically localize while generating text.
Locale locale = Locale.CANADA_FRENCH ; // or Locale.US, Locale.ITALY, etc.
DateTimeFormatter f =
DateTimeFormatter
.ofLocalizedDate( FormatStyle.MEDIUM )
.withLocale( locale ) ;
String output = localDate.format( f ) ; // Generate text representing the value of this object, while automatically localizing.
Or, if you insist, specify your own custom formatting pattern.
DateTimeFormatter f = DateTimeFormatter.ofPattern( "MM/dd/uuuu" ) ;
String output = localDate.format( f ) ;
Example
Here is source-code for a complete example.
This example uses the H2 Database Engine. We take the option of an in-memory database that disappears when the JVM shutdown.
First, establish a DataSource object.
// Establish `DataSource`.
org.h2.jdbcx.JdbcDataSource dataSource = new JdbcDataSource();
// Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
dataSource.setURL( "jdbc:h2:mem:pstmt_localdate_example_db;DB_CLOSE_DELAY=-1" );
dataSource.setUser( "scott" );
dataSource.setPassword( "tiger" );
On first connection, the database is implicitly created. The name comes from the URL field of our DataSource above. Then we execute SQL to create the table order_. We use a trailing underscore to avoid naming conflicts, per the SQL standard’s promise to never name keyword in that manner.
By the way, embedding SQL code in Java will be much easier when the text blocks feature arrives in Java 15 (previewed in Java 14).
// Create database implicitly upon connection, and create first table.
try (
Connection conn = dataSource.getConnection() ;
Statement stmt = conn.createStatement() ;
)
{
String sql =
"DROP TABLE IF EXISTS order_ ; \n "
+
"CREATE TABLE IF NOT EXISTS \n" +
" order_ \n" +
" ( \n" +
" pkey_ UUID NOT NULL DEFAULT RANDOM_UUID() PRIMARY KEY , \n" +
" description_ VARCHAR NOT NULL , \n" +
" when_placed_ DATE NOT NULL \n" +
" ) \n" +
";";
System.out.println( "sql = \n" + sql );
stmt.execute( sql );
}
catch ( SQLException e )
{
e.printStackTrace();
}
We insert a row.
// Insert row.
try (
Connection conn = dataSource.getConnection() ;
Statement stmt = conn.createStatement() ;
)
{
String sql = "INSERT INTO order_ ( description_ , when_placed_ ) \n";
sql += "VALUES ( ? , ? ) \n";
sql += ";";
System.out.println( "sql = " + sql );
try (
PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;
)
{
pstmt.setString( 1 , "blah" );
pstmt.setObject( 2 , LocalDate.now( ZoneId.of( "America/Montreal" ) ) );
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
System.out.println( "INFO - Reporting generated keys." );
while ( rs.next() )
{
UUID uuid = rs.getObject( 1 , UUID.class );
System.out.println( "generated keys: " + uuid );
}
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
Dump the row(s) to console. If you look further down this bit of code, you will see how we generate text using automatic localization. And we generate text using a custom formatter. I suggest going with localization generally over a hard-coded custom format.
// Dump all rows.
try (
Connection conn = dataSource.getConnection() ;
Statement stmt = conn.createStatement() ;
)
{
System.out.println( "INFO - Reporting all rows in table `order_`." );
String sql = "SELECT * FROM order_ ; ";
System.out.println( "sql = " + sql );
try ( ResultSet rs = stmt.executeQuery( sql ) ; )
{
while ( rs.next() )
{
UUID pkey = rs.getObject( "pkey_" , UUID.class );
String description = rs.getString( "description_" );
LocalDate whenPlaced = rs.getObject( "when_placed_" , LocalDate.class );
// Dump to console.
System.out.println( "-----------------" );
System.out.println( "pkey = " + pkey );
System.out.println( "description = " + description );
System.out.println( "whenPlaced = " + whenPlaced ); // Standard ISO 8601 format.
// Localized.
Locale locale = new Locale( "fr" , "DZ" ); // French language, Algeria culture.
DateTimeFormatter formatterLocalized = DateTimeFormatter.ofLocalizedDate( FormatStyle.SHORT ).withLocale( locale );
String outputLocalized = whenPlaced.format( formatterLocalized );
System.out.println( "whenPlaced (localized): " + outputLocalized );
// Custom format.
DateTimeFormatter formatterCustom = DateTimeFormatter.ofPattern( "dd/MM/uuuu" );
String outputCustom = whenPlaced.format( formatterCustom );
System.out.println( "whenPlaced (custom-format): " + outputCustom );
System.out.println( "-----------------" );
}
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
When run.
-----------------
pkey = a4388a40-738b-44bd-b01a-2c487c7e08bf
description = blah
whenPlaced = 2020-06-20
whenPlaced (localized): 20/06/2020
whenPlaced (custom-format): 20/06/2020
-----------------
By the way, make a habit of including the semicolon to terminate your SQL statement. You can get away with its omission in some cases, but will cause problems in others.
