由于某种原因使用JDBC for sqlite时,Date和Timestamp值正确存储在DB中,在使用命令行sqlite3工具时正确显示,但使用ResultSet函数检索这些值时,它不起作用.下面是一个小型测试类,演示了我的意思.
import java.sql.*; public class Test { public static void main(String[] args) throws Exception { Class.forName("org.sqlite.JDBC"); Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db"); Statement stat = conn.createStatement(); stat.executeUpdate("drop table if exists people;"); stat.executeUpdate("create table people (name,occupation,date date);"); stat.executeUpdate("insert into people values ('Turing','Computers',date('now'));"); ResultSet rs = stat.executeQuery("select * from people;"); while (rs.next()) { System.out.println("name = " + rs.getString("name")); System.out.println("job = " + rs.getString("occupation")); System.out.println("date = " + rs.getDate("date")); System.out.println("dateAsString = " + rs.getString("date")); } rs.close(); conn.close(); } }
我得到的输出是：
name =图灵
job =电脑
date = 1970-01-01
dateAsString = 2011-03-24
解决方法
像Scott说的那样：sqlite没有Date类型.
您可以让sqlite使用strftime函数为您进行转换：strftime(‘％s’,date).然后你可以在Java端使用rs.getDate.
您还可以将sqlite日期检索为字符串,并将其解析为日期：
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
Date today = df.parse(rs.getString("date"));
System.out.println("Today = " + df.format(today));
} catch (ParseException e) {
e.printStackTrace();
}
