今回はSQLite JDBC Driver 3.8.11.2のお話です。
SQLite3には日付型（datetime型）がありませんので、文字列型（text型）で代用するのが一般的です。例えば2010年1月1日12時34分56秒は 2010-01-01 12:34:56という文字列で表現するわけです。しかし、Javaプログラムから使う場合、String s = rs.getString("date");とかで文字列を取り出してそこからJava側でDateに変換処理するのは面倒ですし、他のDBとの互換性等に鑑みても、やっぱりrs.getTimestamp("date");と書きたいところですよね。しかし、それは可能なんでしょうか。
結論を言うと、中途半端に可能です。
SQLiteConfigクラスを利用する
SQLiteConfig.setDateStringFormat()というメソッドがあるのです。いい感じでしょ！こんなふうに使います。
SQLiteConfig config = new SQLiteConfig();
config.setDateStringFormat("yyyy-MM-dd HH:mm:ss.SSS");
Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:", config.toProperties());
秒の小数点以下部分が不要なら、.SSSを取り除いてconfig.setDateStringFormat("yyyy-MM-dd HH:mm:ss");としてもよいでしょう。
問題は、ここで指定したフォーマットがgetには適用されるがsetには適用されないということです。
getの場合
getの場合から見てみましょう。こんな感じです。
ResultSet rs = stmt.executeQuery("SELECT * FROM tablewithdate");
while (rs.next()) {
Date d = rs.getTimestamp("date");
System.out.println(d);
}
結果は。。。
2010-01-01 12:34:56.0
こんな感じに出てきます。おお、いいじゃないですか。
setの場合
問題はsetの場合です。
long d = new Date().getTime(); // 現在日時
PreparedStatement ps = conn.prepareStatement("INSERT INTO tablewithdate VALUES (?)");
ps.setTimestamp(1, new Timestamp(d));
ps.executeUpdate();
うまく行きそうな気がしますよね。ところが実際のdate列を見ると。。。
1464452912370
Javaの日付時刻表現「1970年1月1日00:00:00 UTC からのミリ秒数」がそのまま文字列と化してINSERTされてしまうようです。がっかりですね。
setの場合その２：これなら動く
エレガントに行くのはあきらめて、日付の文字列化にjava.text.SimpleDateFormatを使います。
long d = new Date().getTime(); // 現在日時
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
PreparedStatement ps = conn.prepareStatement("INSERT INTO tablewithdate values (?)");
ps.setString(1, df.format(new Date(d)));
ps.executeUpdate();
これなら通ります。なお、このサンプルはタイムゾーンを考慮しておりませんので、そのあたりは読者様にて別途格段のご配慮をお願いします（汗）。
考察
これはバグなんでしょうか。これが仕様なんでしょうか。
わかりませんが、注意して使うべき箇所であるようです。気に入らなければ、setDate, setTime, setTimestamp をオーバーライドするラッパーを作ってもいいかもしれません。
なお、繰り返になりますが、今回検証したバージョンはSQLite JDBC Driver 3.8.11.2 (Oct 03, 2015) です。
リファレンス
SQLite JDBC Driver
https://github.com/xerial/sqlite-jdbc