SQLite JDBC rs.getDate（）getTimestamp（）等都返回错误的值 [英] SQLite JDBC rs.getDate() getTimestamp() etc. all return wrong values

问题描述

由于某些原因使用JDBC for SQLite Date和Timestamp值正确存储在DB中，使用命令行sqlite3工具时会正确显示，但是当使用ResultSet函数检索这些值时，它不起作用。下面是一个小测试类，它演示了我的意思。

When using the JDBC for SQLite for some reason Date and Timestamp values are stored correctly in the DB, are displayed correctly when using the command line sqlite3 tool, but when using the ResultSet functions to retrieve these values it doesn't work. Below is a small test class that demonstrates what I mean.

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

name = Turing
job = Computers
date = 1970-01-01
dateAsString = 2011-03-24

推荐答案

像Scott所说：SQLite没有Date类型。

Like Scott says: SQLite does not have a Date type.

您可以让SQLite使用函数为您进行转换： strftime（'％s'，date）。然后，您可以在Java端使用 rs.getDate 。

You could have SQLite do the conversion for you with the strftime function: strftime('%s', date). Then you can use rs.getDate on the Java side.

您还可以将SQLite日期检索为字符串，并解析为日期：

You can also retrieve the SQLite date as string, and parse that into a date:

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();
}

这篇关于SQLite JDBC rs.getDate（）getTimestamp（）等都返回错误的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！