dev ツ dev ツ - 3 months ago 17
MySQL Question

Issue in fetching Time data type of MySQL using JDBC client

MySQL version : 5.5.41-0ubuntu0.14.04.1




As per the docs, max value of Time is "838:59:59"

I inserted this value in table named
table01
having 1 Time column
col_time
.

I tried to fetch this using Java JDBC code:

public class JDBCUtil {

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://xx.xx.xx.xx:3306/testdb";

static final String USER = "root";
static final String PASS = "root";

public static void main(String[] args) {

try {
Class.forName(JDBC_DRIVER);

} catch (ClassNotFoundException ce) {
ce.printStackTrace();
}

try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();) {

String sql = "select * from table01;";
ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {
Time time = rs.getTime("col_time");
System.out.print("col_time : " + time);

}
rs.close();

} catch (SQLException se) {
se.printStackTrace();
}

}
}


I got exception:


java.sql.SQLException: Bad format for Time '838:59:59' in column 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetRow.getTimeFast(ResultSetRow.java:949)
at com.mysql.jdbc.ByteArrayRow.getTimeFast(ByteArrayRow.java:226)
at com.mysql.jdbc.ResultSetImpl.getTimeInternal(ResultSetImpl.java:6050)
at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5784)
at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5819)
at com.impetus.idw.blend.utils.JDBCUtil.main(JDBCUtil.java:34)


This is expected as exceeding limit of
java.sql.Time


Then I fetched it as String using

while (rs.next()) {
String time = rs.getString("col_time");
System.out.print("col_time : " + time);
}


I got exception:


java.sql.SQLException: Bad format for Time '838:59:59' in column 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:5975)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5723)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5570)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5610)
at com.impetus.idw.blend.utils.JDBCUtil.main(JDBCUtil.java:37)


How can I fetch Time field? What am I missing here?

Answer

The documentation for the MySQL JDBC driver indicates that TIME fields are always converted to java.sql.Time. A look at the source code shows that this is indeed true - even when you call getString() it first retrieves the data as Time, and then gives you its string representation.

Your solution should be to let the database convert the time value to string before you fetch it. That is, instead of just using SELECT *, use

SELECT CAST( col_time AS char ) AS col_time_str
FROM table01

And then use rs.getString("col_time_str").

Mysql will convert the time field to a string for you, and you'll be able to retrieve it like you do any CHAR or VARCHAR column.