Description:
When insert into a table contains Date column, using
rpst.setInt(1, 20080509);
works perfect.
when fetch that column use
rs.getInt(1);
2008 is returned.
How to repeat:
a java class connecting to jdbc:mysql://localhost/test
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MysqlGetIntOnDate {
/**
* @param args
*/
public static void main(String[] args) {
Connection conn = getMySQLconn();
int day = 20080509;
testInsert(conn, day);
if (day == testFetch(conn))
System.out.println("getInt on Date field matched");
else
System.out.println("getInt on Date field failed");
}
static Connection getMySQLconn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager
.getConnection(
"jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF-8",
"root", "");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void testInsert(Connection conn, int day) {
PreparedStatement rpst = null;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
stmt.execute("drop table if exists tmptest");
stmt
.execute("create table tmptest(id int not null primary key, day date not null) DEFAULT CHARSET=utf8");
stmt.close();
String thesql = "replace into tmptest(id, day) values(1, ?)";
rpst = conn.prepareStatement(thesql);
rpst.setInt(1, day);
rpst.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (rpst != null)
rpst.close();
} catch (SQLException e) {
}
}
}
public static int testFetch(Connection conn) {
PreparedStatement rpst = null;
ResultSet rs = null;
try {
String thesql = "select day from tmptest where id = 1";
rpst = conn.prepareStatement(thesql);
rs = rpst.executeQuery();
if (rs.next()) {
int day = rs.getInt(1);
System.out.println("got day: " + day);
return day;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
rpst.close();
} catch (SQLException e) {
}
}
return 0;
}
}
Suggested fix:
for Connector/J 5.0.8, a quick and dirty patch
diff -ur src.orig/com/mysql/jdbc/ResultSet.java src/com/mysql/jdbc/ResultSet.java
--- src.orig/com/mysql/jdbc/ResultSet.java 2007-10-05 05:11:24.000000000 +0800
+++ src/com/mysql/jdbc/ResultSet.java 2008-05-08 18:51:42.000000000 +0800
@@ -2649,7 +2649,12 @@
if (!needsFullParse) {
try {
- return parseIntWithOverflowCheck(columnIndex,
+ int field_mysqltype = this.fields[columnIndex - 1].getMysqlType();
+ if (field_mysqltype == MysqlDefs.FIELD_TYPE_DATE || field_mysqltype == MysqlDefs.FIELD_TYPE_NEWDATE)
+ return parseDateIntWithOverflowCheck(columnIndex,
+ intAsBytes, null);
+ else
+ return parseIntWithOverflowCheck(columnIndex,
intAsBytes, null);
} catch (NumberFormatException nfe) {
try {
@@ -7326,6 +7331,96 @@
return (int) valueAsDouble;
}
+ public static int getDateInt(byte[] buf, int offset, int endPos) throws NumberFormatException {
+ int base = 10;
+
+ int s = offset;
+
+ /* Skip white space. */
+ while (Character.isWhitespace((char) buf[s]) && (s < endPos)) {
+ ++s;
+ }
+
+ if (s == endPos) {
+ throw new NumberFormatException(new String(buf));
+ }
+
+ /* Check for a sign. */
+ boolean negative = false;
+
+ if ((char) buf[s] == '-') {
+ negative = true;
+ ++s;
+ } else if ((char) buf[s] == '+') {
+ ++s;
+ }
+
+ /* Save the pointer so we can check later if anything happened. */
+ int save = s;
+
+ int cutoff = Integer.MAX_VALUE / base;
+ int cutlim = (Integer.MAX_VALUE % base);
+
+ if (negative) {
+ cutlim++;
+ }
+
+ boolean overflow = false;
+
+ int i = 0;
+
+ for (; s < endPos; s++) {
+ char c = (char) buf[s];
+
+ if (Character.isDigit(c)) {
+ c -= '0';
+ } else if (Character.isLetter(c)) {
+ c = (char) (Character.toUpperCase(c) - 'A' + 10);
+ } else {
+ continue;
+ }
+
+ if (c >= base) {
+ break;
+ }
+
+ /* Check for overflow. */
+ if ((i > cutoff) || ((i == cutoff) && (c > cutlim))) {
+ overflow = true;
+ } else {
+ i *= base;
+ i += c;
+ }
+ }
+
+ if (s == save) {
+ throw new NumberFormatException(new String(buf));
+ }
+
+ if (overflow) {
+ throw new NumberFormatException(new String(buf));
+ }
+
+ /* Return the result of the appropriate sign. */
+ return (negative ? (-i) : i);
+ }
+
+ private int parseDateIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes,
+ String valueAsString) throws NumberFormatException, SQLException {
+
+ if (valueAsBytes == null && valueAsString == null) {
+ return 0;
+ }
+
+ if (valueAsBytes != null) {
+ return getDateInt(valueAsBytes, 0 , valueAsBytes.length);
+ } else {
+ valueAsString = valueAsString.trim();
+ valueAsString.replace('-', ',');
+ return Integer.parseInt(valueAsString);
+ }
+ }
+
private int parseIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes,
String valueAsString) throws NumberFormatException, SQLException {