[Java]MariaDB JDBC Connection Support milliseconds

MySQL在5.1版時曾支援timestamp有milliseconds (毫秒)，不過之後的版本又把它移除支援了，而在5.6.4版後又在支援了。當然在不支援milliseconds (毫秒)時還是可以把欄位開成Long來存放時間格式。

MariaDB 10版本身已經支援timestamp的milliseconds (毫秒)，只需再配合MariaDB提供的Type 4 JDBC Driver，另在Connection Url裡加上參數useFractionalSeconds=true。

首先需要建立一個Table，格式如下：

create table mytest (id decimal(10), create_time timestamp(6) default 0);

程式碼如下：

package com.yslifes;

//table 
//create table mytest (id decimal(10), create_time timestamp(6) default 0);
import java.sql.*;
import java.text.SimpleDateFormat;

public class TestCon {
	private static SimpleDateFormat datetimeformat = new SimpleDateFormat(
			"yyyy/MM/dd HH:mm:ss.SSS");

	public static void main(String args[]) throws Exception {
		Class.forName("org.mariadb.jdbc.Driver");
		Connection conn = DriverManager.getConnection(
				"jdbc:mariadb://127.0.0.1:3306/test?useFractionalSeconds=true",
				"root", "12345");
		Statement insert1 = conn.createStatement();
		insert1.executeUpdate("insert into mytest (id, create_time) values (1,'2013-07-18 13:44:22.123456')");
		insert1.close();
		PreparedStatement insert2 = conn
				.prepareStatement("insert into mytest (id, create_time) values (?,?)");
		insert2.setInt(1, 2);
		insert2.setTimestamp(2, new Timestamp(1273017612999L));
		insert2.executeUpdate();
		insert2.close();

		Statement query = conn.createStatement();
		ResultSet rs = query.executeQuery("select * from mytest ");
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "\t"
					+ datetimeformat.format(rs.getTimestamp(2)));
		}
		rs.close();
		query.close();

		conn.createStatement().executeUpdate("delete from mytest");

	}
}

結果：

1	2014/07/18 13:44:22.123
2	2010/05/05 08:00:12.999

原始碼下載