Description:
When default locale of my computer is Thai and I uses PreparedStatement to save date value to date column, the JDBC Driver saves date using buddhist era instead of chirstian era. (2005 AD = 2548 BE)
However, when reading this value back, the driver returns correct value.
But when I use DATE() function to save the value and read it back, the driver return incorrect value.
Currently, I need to change my default locale to US to avoid this problem.
How to repeat:
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Locale;
public class MySqlBug {
public static void main(String[] args) throws Exception {
// set default locale to Thai, which use buddhist era
Locale.setDefault(new Locale("th", "TH"));
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=");
// use 30 june 2005 as test date (which is equal to 30 june AD.2548)
// Please note that java.sql.Date.valueOf() function use date in ISO format, so this line is absolutely correct
Date testDate = Date.valueOf("2005-06-30");
// prepare blank test table
{
Statement stmt = con.createStatement();
// create test table
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS buddhist_test (name VARCHAR(50), mydate DATE)");
// delete all data
stmt.executeUpdate("DELETE FROM buddhist_test");
stmt.close();
}
// test save data using server function
{
PreparedStatement stmt = con.prepareStatement("INSERT INTO buddhist_test(name, mydate) VALUES(?,DATE('2005-06-30'))");
stmt.setString(1, "Using DATE() function");
stmt.executeUpdate();
stmt.close();
}
// test save data using prepared statement
{
PreparedStatement stmt = con.prepareStatement("INSERT INTO buddhist_test(name, mydate) VALUES(?,?)");
stmt.setString(1, "Using PreparedStatement");
stmt.setDate(2, testDate);
stmt.executeUpdate();
stmt.close();
}
// read data
{
PreparedStatement stmt = con.prepareStatement("select name, mydate, concat(mydate,'') from buddhist_test");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + ": Date value = " + rs.getDate(2) + " (Real value = " + rs.getString(3) + ")");
}
stmt.close();
}
}
}
The output will be: -
Using DATE() function: Date value = 1462-06-30 (Real value = 2005-06-30)
Using PreparedStatement: Date value = 2005-06-30 (Real value = 2548-06-30)
If the driver worked correctly, output should be: -
Using DATE() function: Date value = 2005-06-30 (Real value = 2005-06-30)
Using PreparedStatement: Date value = 2005-06-30 (Real value = 2005-06-30)
Suggested fix:
*** com.mysql.jdbc.PreparedStatement ***
1. In setDate() method, you need to specify US locale (or other locale that is use christian era) when instantiate java.util.DateFormat.
SimpleDateFormat dateFormatter = new SimpleDateFormat("''yyyy-MM-dd''");
should be changed to
SimpleDateFormat dateFormatter = new SimpleDateFormat("''yyyy-MM-dd''", Locale.US);
2. In setTimestampInternal() method, the same thing should be applied.
this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''"); //$NON-NLS-1$
should be changed to
this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$
*** com.mysql.jdbc.ResultSet ***
3. All appearances of "new GregorianCalendar()" should be changed to "new GregorianCalendar(Locale.US)".
Description: When default locale of my computer is Thai and I uses PreparedStatement to save date value to date column, the JDBC Driver saves date using buddhist era instead of chirstian era. (2005 AD = 2548 BE) However, when reading this value back, the driver returns correct value. But when I use DATE() function to save the value and read it back, the driver return incorrect value. Currently, I need to change my default locale to US to avoid this problem. How to repeat: import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.Locale; public class MySqlBug { public static void main(String[] args) throws Exception { // set default locale to Thai, which use buddhist era Locale.setDefault(new Locale("th", "TH")); Class.forName("com.mysql.jdbc.Driver").newInstance(); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password="); // use 30 june 2005 as test date (which is equal to 30 june AD.2548) // Please note that java.sql.Date.valueOf() function use date in ISO format, so this line is absolutely correct Date testDate = Date.valueOf("2005-06-30"); // prepare blank test table { Statement stmt = con.createStatement(); // create test table stmt.executeUpdate("CREATE TABLE IF NOT EXISTS buddhist_test (name VARCHAR(50), mydate DATE)"); // delete all data stmt.executeUpdate("DELETE FROM buddhist_test"); stmt.close(); } // test save data using server function { PreparedStatement stmt = con.prepareStatement("INSERT INTO buddhist_test(name, mydate) VALUES(?,DATE('2005-06-30'))"); stmt.setString(1, "Using DATE() function"); stmt.executeUpdate(); stmt.close(); } // test save data using prepared statement { PreparedStatement stmt = con.prepareStatement("INSERT INTO buddhist_test(name, mydate) VALUES(?,?)"); stmt.setString(1, "Using PreparedStatement"); stmt.setDate(2, testDate); stmt.executeUpdate(); stmt.close(); } // read data { PreparedStatement stmt = con.prepareStatement("select name, mydate, concat(mydate,'') from buddhist_test"); ResultSet rs = stmt.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1) + ": Date value = " + rs.getDate(2) + " (Real value = " + rs.getString(3) + ")"); } stmt.close(); } } } The output will be: - Using DATE() function: Date value = 1462-06-30 (Real value = 2005-06-30) Using PreparedStatement: Date value = 2005-06-30 (Real value = 2548-06-30) If the driver worked correctly, output should be: - Using DATE() function: Date value = 2005-06-30 (Real value = 2005-06-30) Using PreparedStatement: Date value = 2005-06-30 (Real value = 2005-06-30) Suggested fix: *** com.mysql.jdbc.PreparedStatement *** 1. In setDate() method, you need to specify US locale (or other locale that is use christian era) when instantiate java.util.DateFormat. SimpleDateFormat dateFormatter = new SimpleDateFormat("''yyyy-MM-dd''"); should be changed to SimpleDateFormat dateFormatter = new SimpleDateFormat("''yyyy-MM-dd''", Locale.US); 2. In setTimestampInternal() method, the same thing should be applied. this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''"); //$NON-NLS-1$ should be changed to this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$ *** com.mysql.jdbc.ResultSet *** 3. All appearances of "new GregorianCalendar()" should be changed to "new GregorianCalendar(Locale.US)".