MySQL Bugs: #105211: class java.time.LocalDate cannot be cast to class java.sql.Date

Bug #105211	class java.time.LocalDate cannot be cast to class java.sql.Date
Submitted:	13 Oct 2021 16:01	Modified:	17 Dec 2021 1:11
Reporter:	Oussama Sakri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	LocalDate Date

Description:
Use the parameter useserverprepstmts = true.
The "How to repeat" code was inspired from Bug#101413 and rerun after noticing the bug in production code.

stack trace:

java.lang.ClassCastException: class java.time.LocalDate cannot be cast to class java.sql.Date (java.time.LocalDate is in module java.base of loader 'bootstrap'; java.sql.Date is in module java.sql of loader 'platform')
	at com.mysql.cj.jdbc.ServerPreparedStatement.setOneBatchedParameterSet(ServerPreparedStatement.java:763)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:712)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:426)
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)
	at com.m19.test.LocalDateTest.testLocalDate(LocalDateTest.java:16)

How to repeat:
public class LocalDateTest {
    String url = "jdbc:mysql://127.0.0.1/test&rewriteBatchedStatements=true&useServerPrepStmts=true";
    String qurey = "insert into dateTest(localDate) values(?)";

    @Test
    void testLocalDate() {
        try (Connection connection = DriverManager.getConnection(this.url, "root", "root");
             PreparedStatement statement = connection.prepareStatement(this.qurey);) {
       
            connection.setAutoCommit(false);

            for (int i = 0; i <= 1000; i++) {
                statement.setObject(1, LocalDate.now());
                statement.addBatch();
                if (i % 100 == 0) {
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }

            connection.commit();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

Hello Oussama Sakri,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Added the following entry to the Connector/J 8.0.28 changelog: 

"Storing a java.time.LocalDate object onto the server as a DATE value using a batched PreparedStatement failed with the complaint that java.time.LocalDate could not be cast to java.sql.Date. With this fix, the object is encoded correctly into a DATE value."