Java reads the datetime type of Mysql

1. in the MySQL database, the datetime type is used to store the time. When reading this field using JDBC, The resultset should be used. gettimestamp () to get a java. SQL. timestamp type data. 2. In this case, neither resultset. getdate () nor resultset. gettime () can be used, because the former does not include time data, and the latter does not include date data. 3. however, you are using resultset. gettimestamp () is not completely secure. For example, if the value of the timestamp type field in the database is '2017-00-00 00:00:00 ', use this method to read data, an exception is thrown: cannot convert value '2017-00-00 00:00:00 'from Column 1 to timestamp, this is because JDBC cannot convert '2017-00-00 00:00:00 'into a java. SQL. timestamp. in Java, you want to create a java. util. it is also impossible to set the value to '2014-00-00 '. The oldest date should be '2014-01-01 00:00:00 '.



4. Therefore, adding zerodatetimebehavior information to the jdbc url can solve the following problem:

String url = "JDBC: mysql: /// 10.149.51.80: 3306/test? Relaxautocommit = true & zerodatetimebehavior = converttonull ";

View code

Try {ps = DB. prepare (conn, SQL _list); rs = ps.exe cuteQuery (); DateFormat df = new SimpleDateFormat ("yyyy-MM-dd HH: mm: ss"); while (rs. next () {User u = new User (); u. setId (rs. getInt ("id"); u. setUserName (rs. getString ("userName"); u. setPassword (rs. getString ("password"); // rs. getTimestamp ("regTime") returns "11:30:33. 0 "; mysql is accurate to milliseconds // df. format () ---> remove ". 0 "u. setRegTime (df. format (rs. getTimestamp ("regTime"); u. setRealName (rs. getString ("realName"); u. setRoleId (rs. getInt ("roleId"); roleName = rs. getString ("roleName"); list. add (u);} DB. close (rs);} catch (SQLException e) {e. printStackTrace ();}