Java Type vs PostgreSQL Type (十一) 時間型別之對應 -java.util.Date

java.util.Date:

在測試Java Type:java.util.Date時，先在資料庫建置一張資料表。

資料表語法:

CREATE TABLE datetypes
(
date date,
"time" time without time zone,
"timestamp" timestamp without time zone,
id serial NOT NULL,
CONSTRAINT datetypes_pkey PRIMARY KEY (id)
)

原始程式碼:

JavaDateType.java:

package zasax.type.timetypes;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import zasax.conn.PostgreConnection;

/**
*
* @author YiCheng,Hsiao
*/
public class JavaDateType {

private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private String setSQL = "Insert into datetypes (date,time,timestamp) values (?,?,?)";
private String getSQL = "select * from datetypes";

public static void main(String[] args)
throws IOException, ClassNotFoundException, SQLException {
JavaDateType jdt = new JavaDateType();
jdt.setDateValue(new Date(), new Date(), new Date());
ResultSet rs = jdt.getDateValue();
while (rs.next()) {
System.out.print("Date:"+rs.getDate("date")+" ");
System.out.print("Time:"+rs.getTime("time")+" ");
System.out.println("Timestamp:"+rs.getTimestamp("timestamp"));
}
}

public void setDateValue(Date d1, Date d2, Date d3)
throws IOException, ClassNotFoundException, SQLException {
PostgreConnection pc = new PostgreConnection();
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(setSQL);
pstmt.setDate(1, new java.sql.Date(d1.getTime()));
pstmt.setTime(2, new java.sql.Time(d2.getTime()));
pstmt.setTimestamp(3, new java.sql.Timestamp(d3.getTime()));
pstmt.execute();
pstmt.close();
conn.close();
}

public ResultSet getDateValue()
throws IOException, ClassNotFoundException, SQLException {
PostgreConnection pc = new PostgreConnection();
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(getSQL);
this.rs = pstmt.executeQuery();
return rs;
}
}

實際測試程序:

儲存java.util.Date的方式:

先透過setDateValue(Date ,Date, Date)的方法，將時間引入，再透過java.sql.Date, java.sql.Time, java.sql.Timestamp 等三種容器的承接，才得以透過setDate(), setTime(), setTimestamp() ，存入資料庫。

讀取java.util.Date的方式:

由getDateValue()的方法，以SQL 查詢語法"select * from datetype "，經executeQuery()回傳至ResultSet，再經由ResultSet的方法getDate(), getTime(), getTimestamp() ，讀取內容。

印出結果如下:

整理表格如下:

Java Types	PostgreSQL Types	特性
java.util.Date	date	4 bytes，只用於日期
	time	8 bytes，只用於一日內時間
	timestamp	8 bytes，包括日期和時間