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

java.util.Calendar:

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

資料表語法:

CREATE TABLE calendartypes
(
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.Calendar;
import zasax.conn.PostgreConnection;

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

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

public static void main(String[] args)
throws IOException, ClassNotFoundException, SQLException {
JavaCalendarType jct = new JavaCalendarType();
jct.setCalendarValue(Calendar.getInstance(), Calendar.getInstance(), Calendar.getInstance());
ResultSet rs = jct.getCalendarValue();
while (rs.next()) {
System.out.println("Date:" + rs.getDate("date"));
System.out.println("Time:" + rs.getTime("time"));
System.out.println("Timestamp:" + rs.getTimestamp("timestamp"));
}
}

public void setCalendarValue(Calendar c1, Calendar c2, Calendar c3)
throws IOException, ClassNotFoundException, SQLException {
PostgreConnection pc = new PostgreConnection();
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(setSQL);
pstmt.setDate(1, new java.sql.Date(c1.getTimeInMillis()));
pstmt.setTime(2, new java.sql.Time(c2.getTimeInMillis()));
pstmt.setTimestamp(3, new java.sql.Timestamp(c3.getTimeInMillis()));
pstmt.execute();
pstmt.close();
conn.close();
}

public ResultSet getCalendarValue()
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.Calendar的方式:

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

讀取java.util.Calendar的方式:

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

印出結果如下:

整理表格如下:

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

備註:

java.sql.Date, java.sql.Time, java.sql.Timestamp, 雖然也都是跟時間有關的型別，而這三個型別較屬於為容器系統，為了便於跟資料庫的型別存取讀取。

Java Types	自己產生時間	存入PostgreSQL資料庫的對應型別
java.util.Date	T	需要轉譯
java.util.Calendar	T	需要轉譯
java.sql.Date	F	date
java.sql.Time	F	time
java.sql.Timestamp	F	timestamp