PostgreSQL-XA & DTP(分散式事務交易處理的概觀)

分享製作給 ...
對於想了解PostgreSQL Transaction 或是進階開發 Java EE 有關 JTA 的新人,
建議必要對該內容有個初步的認識.

(點圖大放後再保存)

撰寫 PL/pgSQL 函數快速指南: Part 1

PL/pgSQL 是 PostgreSQL 內置的可選預儲程序(Stored Procured)語言之一, 也是當中的效能與功能最強的。在這系列我們將學習如何撰寫 PL/pgSQL 的存儲函數/函式(Function)。

PLPGSQL FUNCTION 的構造

所有的 PL/pgSQL 函數遵循的結構, 看起來都如同下面的形式。

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
RETURNS text AS
$
DECLARE
strresult text;
BEGIN
strresult := '';
IF numtimes > 0 THEN
    FOR i IN 1 .. numtimes LOOP
        strresult := strresult || msg || E'\r\n';
    END LOOP;
END IF;
RETURN strresult;
END;
$
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER
COST 10;

-- 我們呼叫這個函數它將回傳10次'hello there's'且回傳處理於單一的文本(text)欄位中. 
SELECT fnsomefunc(10, 'Hello there');

這個 PL/pgSQL 函數的基本構成如下:

1. 在函數介面宣告定義了引數(args)與返回的型別(type)。
2. 本體(body)的封裝部份, 當前 PostgreSQL (8+) 版本首選是 $$(dollar quoting)與使用著'單引號'。
3. 在主體(body)範圍: 變數宣告 DECLARE(declaration) 的區段是可選的。
4. 再者 BEGIN / END; 結構是定義該函數的內容。不同於 SQL 函數的選擇要求你參考到它們的順序位置例如 $1, $2, $3 等等。在 PL/pgSQL 中您能參考到它們的變數名稱。
5. 在主體(body)之後, 諸如所有的 PostgreSQL 函數，顯著的指出預儲程序(Stored Procured)語言和標記，並且指示應該如何被快取。在這種案例中，我們已顯著的標示 IMMUTABLE 這意味著不可改變輸出的函數，如果輸入的是相同的，可以預期是相同的。其它的選擇值是 STABLE - 這意味著它將不能被變更於一個查詢式輸入範圍中，VOLATILE 如此標示的函數帶有random()，CURRENT_TIMESTAMP 可以被預計變化輸出在相同的查詢式呼叫。
   更多參考: 34.6. 函數易變性範疇(Function Volatility Categories)
6. PostgreSQL 8.3 已投入有能力設定成本(costs)與估計行(rows)回傳的函數。對於標量函數的行不適用，我們這個簡單的例子也是同樣地。這個 COST 是相對於其他函數及預設為 100 ，除非你改變它。
7. 另外注意到該範例的最後有個快取模式的短句 SECURITY DEFINER，這意味著該函數是運行於符合擁有人權限背景下的函數。這意味著該函數可以安全的如同擁有人所能做的任何事情，即使這樣做的人運行的函數沒有這些權限。這部分適用於不僅要 PL/pgSQL 的函數，但任何。如果這一短句排除在外，之後該函數運行是以個人為安全背景。
   對於將從 SQL Server 來的使用者 - 這是類似的概念到 SQL Server 2005 - EXECUTE AS OWNER（leaving Security definer out is equivalent to EXECUTE As CALLER in sql server）。注意：SQL Server 2005 中有一個額外的選項，稱為使用 EXECUTE As 'user_name' 但這是 PostgreSQL 所缺乏的，使您能夠執行在一個命名的使用者, 而不需要是函數的擁有者。
   對於 MySQL 使用者們，SECURITY DEFINER 存在以及與工作或多或少相同的在PostgreSQL。
8. 幾乎所有的函數你可以寫在 PostgreSQL 抑或 SQL 中, PL/pgSQL 或者某些其它的預儲程序語言能夠使用 遞迴(recursion). 我們將在這系列的的另一個主題中示範。

有條件限制的邏輯

PL/pgSQL 對條件限制的邏輯式是成對的結構. 在上面的範例中我們看到了簡單的 IF THEN。同樣也存在於 IF .. ELSIF ..ELSIF END IF, IF ..ELSE ..END IF。我們將在上面的示例上做一個改變。

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
RETURNS text AS
$
DECLARE
strresult text;
BEGIN
strresult := '';
IF numtimes = 42 THEN
    strresult := '你說得對！';
ELSIF numtimes > 0 AND numtimes < 100 THEN
    FOR i IN 1 .. numtimes LOOP
        strresult := strresult || msg || E'\r\n';
    END LOOP;
ELSE
    strresult := '你不能這樣做。麻煩不要濫用我們的慷慨。';
    IF numtimes <= 0 THEN
        strresult := strresult || '你是一個 bozo。';
    ELSIF numtimes > 1000 THEN
        strresult := strresult || ' 我不知道你以為你是誰。 
                    你的意向失去了控制。';
    END IF;
END IF;
RETURN strresult;
END;
$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT fnsomefunc(42, 'Hello there');
SELECT fnsomefunc(200, 'Hello there');
SELECT fnsomefunc(5000, 'Hello there'); 

控制流程

在上面的範例中，我們看到了不同的 FOR LOOP(迴圈) - 在以下是列出其他基本控制結構。第2部分中，我們將深入探討使用其中的一些。

在 PL/pgSQL 中提供的基本控制流結構是：

• FOR somevariable IN (1 ...someendnumber) 迴圈 .. END 迴圈;
• FOR somevariable IN REVERSE someendnumber .. 1 BY somestep 迴圈 .. END 迴圈;
• FOR somevariable IN (somesqlquery) 迴圈 ..RETURN NEXT; .. END 迴圈;
• 迴圈 ..logic statements EXIT .. EXIT WHEN .. CONTINUE WHEN .. END 迴圈;
• WHILE ... 迴圈 ... END 迴圈;
• EXCEPTION WHEN .... WHEN ..
• 介紹在 PostgreSQL 8.3 RETURN QUERY 迴圈能被置於任何的結構或是單獨裡。

本文譯自:

http://www.postgresonline.com/journal/index.php?/archives/58-guid.html

pagila sample用資料庫安裝 (windows 系統)

如何安裝Pagila範例資料庫呢?
以下程序為安裝過程:

1.首先解壓縮檔案後，會產生三個檔案，包含pagila-data.sql, pagila-insert-data.sql, pagila-schema.sql

2.然後執行PgAdmin->然後新增資料庫，設定內容如下圖:

3.資料庫創好後，點選SQL查詢視窗->讀取檔案，選取Pagila-schema.sql。

4.開啟後，點選執行，讓SQL語法自動寫入架構。

5.架構建好後，在資料庫上按右鍵，點選維護，在根據下圖指示選取。

6.維護完後，再根據類似步驟3的作法，以下圖的方式開啟Pagila-insert-data.sql。

7.再根據類似步驟4的方式，將pagila-insert-data.sql的資料寫入資料庫。

8.再根據類似步驟5的方式再做一次維護。

9.整理完後關閉PgAdmin->點選我的電腦按右鍵，點選內容。

10.開啟後，將PostgreSQL\版本\bin，路徑複製下來，貼到環境變數裡的path裡面。

11.環境設定完畢後，接著點選開始->執行->輸入cmd->在終端機上可以輸入psql --version 測試環境有沒有設置OK。

12.將目錄移致檔案目錄，然後打psql -U "使用者的名稱" -d "資料庫名稱" -f "要匯入資料的檔案"。

13.打該pgadmin，然後跟著先前步驟5，8一樣進行維護。

14.最後完整的資料，大功告成。

相關連結:

PostgreSQL 示範用(Sample)資料庫

日本電信龍頭-NTT導入PostgreSQL削減成本達30億元

(感謝日譯老師的校詞協助 - 2008/10/13)

NTT - Wikipedia
日本電信電話株式會社，簡稱NTT，為日本最大的電信服務公司，是目前日本通訊產業最重要的旗艦企業，也被併列為目前世界上首屈一指的通信公司之一。總集團員工數約20萬人。

NTT DATA Group OSS Square
集合 NTT 集團所有子公司研發的 OSS 資訊，成為目前日本最大的 OSS 入口網站。

新聞來自: 日本 IT-Pro - 2008-10-07
NTTがPostgreSQLベースのEnterpriseDBと提携，社内利用で30億円コスト削減見込む

★ NTT 與以 PostgreSQL 為基礎的 EnterpriseDB 公司合作, 估計將削減該集團 30億日元的成本支出

日本 NTT 於 2008年10月7日 發表，將出資和美國 EnterpriseDB 公司(以開發、販賣PostgreSQL 資料庫處理為主)合作。共同合作發展 PostgreSQL 適用於大規模系統的技術開發，以及促進 NTT 與一般企業用戶的 PostgreSQL 導入使用率。

EnterpriseDB 公司擁有 "佔 PostgreSQL 註冊開發成員人數達 30% !" - ( EnterpriseDB 公司共同創辦人兼高級副總裁 - Andy Astor)。
在發展 PostgreSQL 資料庫系統的同時，也推出功能擴充的商業付費版本。該公司的產品 PostgreSQL Plus Advanced Server 有著和 Oracle 很高的相容性工具，以及監測的功能。
根據 EnterpriseDB公司表示 "與甲骨文(Oracle)授權費用相比，成本相差高達百分之八十 !" (EnterpriseDB 公司的共同創始人兼首席設計師 - Denis Lussier )

日本 NTT 在過往(関連記事)提供中說明 NTT 如何應用標準且開源的 PostgreSQL 資料庫管理系統在 NTT 企業中，在 NTT，PostgreSQL 在標準的資料庫處理系統佔有一定的地位，目前 NTT 已有幾十個系統投入使用中。"目前在NTT企業團隊中擁有數百個處理系統，引進 PostgreSQL 資料庫系統已達百分之十" (NTT OSS 中心主任-木ノ原誠司氏)。

此外 NTT 也投入參加 PostgreSQL 資料庫系統的研發。"發布於 2008年2月 的最新版本的 PostgreSQL 8.3 中有三個最大的改良設計專案, 當中的二項就是 NTT 所做的貢獻"(NTT OSS 中心主任-木ノ原誠司氏)。
具體來說，是 PostgreSQL 內部自動最佳化 Data 的配置和改良的功能，以及 Data 寫入到實體儲存體和負載均衡(LB)。此外，NTT 共有 45項提出改進程序碼，以及日文全文檢索的工具 - textsearch-ja，能大幅削減交易日誌歸檔容量能力的 pglesslog ，高速裝載大量 Data 的工具 - pg_bulkload。

日本 NTT 估算該公司在5年內至少可達到削減累計約 20-30億日元(折合新台幣約 5億元)的成效，降低 NTT TCO(資訊化整體擁有成本)。

"在當前的 PostgreSQL 8.3 中 1TB(terabyte) datas，可達99.99％的可用性/可靠性的高要求(故障時系統的更替時間可在5分鐘內)"-（日本 NTT OSS事業化推進PT 担当部長 館剛司氏)。
預計將在 2011年財政年度，10TB，能提供高達 99.999％ (故障時系統的更替時間可在1分鐘內)，可用於顧客付費相關的主系統。透過這些過程，"在未來5年內， PosgtreSQL 可預期的導入程度可有 25% 的增長速度。在這樣成長、擴展的過程中，可削減約 20-30億日元"- (NTT 木ノ原氏)。

另外，在 2008年宣布在9月2日為 SaaS 的商業服務也將適用於該基金會。EnterpriseDB 公司將公開其研究開發的開放源碼一個平行處理技術 - GridSQL， NTT 與 PostgtreSQL 共同發展社群和提供改良建議，結合同步複寫技術，開發 PostgreSQL 的大規模分佈式數據庫技術。

NTT對EnterpriseDB 公司的出資金額與比例是屬於非公開的事項。在日本，Cntents、Lgistique、Linux是EnterpriseDB 公司的代理商，而 EnterpriseDB 公司也以「想要在日本增加所屬的代理商」為由，已經向多數廠商進行交涉合作中。而NTT也正極力研討加入所屬代理商的可能性。

相關連結:
PostgreSQL 日本NTT協助取得 ISO/IEC15408 安全認證
日本-IPA OSS: PostgreSQL 的性能不再是議題

Java Type vs PostgreSQL Type 參考圖

主要用來便於對應Java Type 與 PostgreSQL Type 的參考圖

Java Type - 時間型別之間之差異 以PostgreSQL對應

測試平台:JDK SE 6

測試目標:
java.util.Date
java.util.Calendar
java.sql.Date
java.sql.Time
java.sql.Timestamp

測試目的:
對於五種都是所謂的時間類別，有什麼區別。


1.是否能自己產生時間:
java.util.Date:
在java.util.Date建構子上，預設的情況，public Date() { this(System.currentTimeMillis()); } ，預設會調用System.currentTimeMillis()的方法。

System.currentTimeMillis()在API的內容:

currentTimeMillis

public static long currentTimeMillis()

返回以毫秒為單位的當前時間。注意，當返回值的時間單位是毫秒時，值的粒度取決於底層作業系統，並且粒度可能更大。例如，許多作業系統以幾十毫秒為單位測量時間。

請參閱 Date 類別的描述，瞭解可能發生在“計算機時間”和協調世界時（UTC）之間的細微差異的討論。

返回：

當前時間與協調世界時 1970 年 1 月 1 日午夜之間的時間差（以毫秒為單位測量）。

另請參見：

Date

表示說在java.util.Date建構時，也會順便初始本地時間。也可以明確了解，java.util.Date能夠自己產生本地時間。


java.util.Calendar:
java.util.Calendar為抽象類別，建構方式透過Calendar.getInstance()初始， public static Calendar getInstance() { Calendar cal = createCalendar(TimeZone.getDefaultRef(), Locale.getDefault()); cal.sharedZone = true; return cal; } ，在預設的情況下會順便invoked TimeZone.getDefault()，以及Loale.getDefault()這兩個方法。

TimeZone.getDefault()在API的內容:

getDefault

public static TimeZone getDefault()

獲取此主機的預設 TimeZone。預設 TimeZone 的來源可能隨實作的變化而變化。

返回：

預設的 TimeZone。

另請參見：

setDefault(java.util.TimeZone)

Locale.getDefault()在API的內容:

getDefault

public static Locale getDefault()

獲得此 Java 虛擬機器實例的當前預設語言環境值。

Java 虛擬機器根據主機的環境在啟動期間設置預設語言環境。如果沒有明確地指定語言環境，則很多語言環境敏感的方法都使用該方法。可使用 setDefault 方法更改該值。

返回：

此 Java 虛擬機器實例的預設語言環境。

因為這些方法，java.util.Calendar在透過getInstance()後，會順便初始時間及時區，也就可以得知，java.util.Calendar能夠自己產生時間。


java.sql.Date:
在API的內容:

public class Date
extends Date

一個套件裝了毫秒值的瘦外覆類別 (thin wrapper)，它允許 JDBC 將毫秒值標識為 SQL DATE 值。毫秒值表示自 1970 年 1 月 1 日 00:00:00 GMT 以來經過的毫秒數。

為了與 SQL DATE 的定義一致，由 java.sql.Date 實例包裹的毫秒值必須通過將小時、分鐘、秒和毫秒設置為與該實例相關的特定時區中的零來“規範化”。

可以得知，java.sql.Date屬於一個wrapper class，專門用來承接時間數據，轉為與資料庫中的date型態相符合的型態。
所以java.sql.Date，並無法自己產生出時間。


java.sql.Time:
在API的內容:

public class Time
extends Date

一個與 java.util.Date 類別有關的瘦外覆類別 (thin wrapper)，它允許 JDBC 將該類別標識為 SQL TIME 值。Time 類別添加格式化和解析操作以支持時間值的 JDBC 轉義語法。

應該將日期元件設置為 1970 年 1 月 1 日的 "zero epoch" 值並且不應存取該值。

可以得知，java.sql.Time屬於一個wrapper class，專門用來承接時間數據，轉為與資料庫中的time型態相符合的型態。
所以java.sql.Time，並無法自己產生出時間。


java.sql.Timestamp:
在API的內容:

public class Timestamp
extends Date

一個與 java.util.Date 類別有關的瘦外覆類別 (thin wrapper)，它允許 JDBC API 將該類別標識為 SQL TIMESTAMP 值。它通過允許小數秒到納秒級精度的規範來添加保存 SQL TIMESTAMP 小數秒值的能力。Timestamp 也提供支持時間戳值的 JDBC 轉義語法的格式化和解析操作的能力。

計算 Timestamp 物件的精度為二者之一：

• 19 ，其為 yyyy-mm-dd hh:mm:ss 格式下的字元數
• 20 + s ，其為 yyyy-mm-dd hh:mm:ss.[fff...] 格式下的字元數，s 表示給定 Timestamp 的標度（其小數秒精度）。

註：此型別由 java.util.Date 和單獨的毫微秒值組成。只有整數秒才會存儲在 java.util.Date 元件中。小數秒（毫微秒）是獨立存在的。傳遞不是 java.sql.Timestamp 實例的物件時，Timestamp.equals(Object) 方法永遠不會返回 true，因為日期的毫微秒元件是未知的。因此，相對於 java.util.Date.equals(Object) 方法而言，Timestamp.equals(Object) 方法是不對稱的。此外，hashcode 方法使用底層 java.util.Date 實作並因此在其計算中不包括毫微秒。

鑒於 Timestamp 類別和上述 java.util.Date 類別之間的不同，建議程式碼一般不要將 Timestamp 值視為 java.util.Date 的實例。Timestamp 和 java.util.Date 之間的繼承關係實際上指的是實作繼承，而不是型別繼承。

可以得知，java.sql.Timestamp屬於一個wrapper class，專門用來承接時間數據，轉為與資料庫中的timestamp型態相符合的型態。所以java.sql.Timestamp，並無法自己產生出時間。


2.對應資料庫存取方法區別:
以下為API內容，選擇java.sql.PreparedStatement內的方法:

setDate

void setDate(int parameterIndex,
           Date x)
           throws SQLException

使用運行應用程序的虛擬機器的預設時區將指定參數設置為給定 java.sql.Date 值。在將此值發送到資料庫時，驅動程序將它轉換成一個 SQL DATE 值。

參數：

parameterIndex - 第一個參數是 1，第二個參數是 2，……

x - 參數值

拋出：

SQLException - 如果 parameterIndex 不對應於 SQL 語句中的參數標記；如果發生資料庫存取錯誤，或者在關閉的 PreparedStatement 上調用此方法

setTimestamp

void setTimestamp(int parameterIndex,
                Timestamp x)
                throws SQLException

將指定參數設置為給定 java.sql.Timestamp 值。在將此值發送到資料庫時，驅動程序將它轉換成一個 SQL TIMESTAMP 值。

參數：

parameterIndex - 第一個參數是 1，第二個參數是 2，……

x - 參數值

拋出：

SQLException - 如果 parameterIndex 不對應於 SQL 語句中的參數標記；如果發生資料庫存取錯誤，或者在關閉的 PreparedStatement 上調用此方法

setTime

void setTime(int parameterIndex,
           Time x)
           throws SQLException

將指定參數設置為給定 java.sql.Time 值。在將此值發送到資料庫時，驅動程序將它轉換成一個 SQL TIME 值。

參數：

parameterIndex - 第一個參數是 1，第二個參數是 2，……

x - 參數值

拋出：

SQLException - 如果 parameterIndex 不對應於 SQL 語句中的參數標記；如果發生資料庫存取錯誤，或者在關閉的 PreparedStatement 上調用此方法

由上面三個主要存取給資料庫型別date, time, timestamp 的方法，可以明確了解，在JDBC的要求上，透過java.sql.Date, java.sql.Time, java.sql.Timestamp，最為符合跟資料庫存取的規格，所以java.util.Date, java.util.Calendar，要存進資料庫時，得透過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

java.sql.PreparedStatement的方法測試-對應PostgreSQL JDBC4

測試類別: java.sql.PreparedStatement
測試目標: setBinaryStream()
測試jdk: jdk1.6.0_10
測試選擇資料庫:PostgreSQL 8.3.3
測試資料庫對應之JDBC: postgresql-8.3-603.jdbc4


測試原始碼:
package zasax.test;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import zasax.conn.PostgreConnection;

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

private Connection conn;
private PreparedStatement pstmt;
private String setSQL = "Insert into checktest (\"binary\") values (?)";

public static void main(String[] args)
throws IOException, ClassNotFoundException, SQLException {
PreparedStament_SetBinaryStreamTest psst =
new PreparedStament_SetBinaryStreamTest();
psst.setBinaryStreamValue();
}

public void setBinaryStreamValue()
throws IOException, ClassNotFoundException, SQLException {
PostgreConnection pc = new PostgreConnection();
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(setSQL);
//pstmt.setBinaryStream(1, new ByteArrayInputStream(new byte[10]));
//pstmt.setBinaryStream(1, new ByteArrayInputStream(new byte[10]), 10);
//pstmt.setBinaryStream(1, new ByteArrayInputStream(new byte[10]), 10l);
pstmt.execute();
pstmt.close();
conn.close();

}
}

setBinaryStream() 在Java SE API 的內容:

setBinaryStream

void setBinaryStream(int parameterIndex,
                    InputStream x)
                    throws SQLException

將指定參數設置為給定輸入串流。在將一個非常大的二進制值輸入到 LONGVARBINARY 參數時，通過 java.io.InputStream 物件發送它可能更為實際。將根據需要從串流中讀取資料，一直讀取到檔案末尾。

註：此串流物件既可以是一個標準 Java 串流物件，也可以是實作標準介面的使用者自己的子類別。

註：查詢 JDBC 驅動程序文檔，以確定使用帶 length 參數的 setBinaryStream 是否更有效。

參數：

parameterIndex - 第一個參數是 1，第二個參數是 2，……

x - 套件含二進制參數值的 java 輸入串流

拋出：

SQLException - 如果 parameterIndex 不對應於 SQL 語句中的參數標記；

如果發生資料庫存取錯誤，或者在關閉的 PreparedStatement 上調用此方法

SQLFeatureNotSupportedException - 如果 JDBC 驅動程序不支持此方法

從以下版本開始：

1.6

setBinaryStream

void setBinaryStream(int parameterIndex,
                    InputStream x,
                    int length)
                    throws SQLException

將指定參數設置為給定輸入串流，該輸入串流將具有給定位元組數。

在將一個非常大的二進制值輸入到 LONGVARBINARY 參數時，

通過 java.io.InputStream 物件發送它可能更為實際。

將根據需要從串流中讀取資料，一直讀取到檔案末尾。

註：此串流物件既可以是一個標準 Java 串流物件，

也可以是實作標準介面的使用者自己的子類別。

參數：

parameterIndex - 第一個參數是 1，第二個參數是 2，……

x - 套件含二進制參數值的 Java 輸入串流

length - 串流中的位元組數

拋出：

SQLException - 如果 parameterIndex 不對應於 SQL 語句中的參數標記；

如果發生資料庫存取錯誤，或者在關閉的 PreparedStatement 上調用此方法

setBinaryStream

void setBinaryStream(int parameterIndex,
                    InputStream x,
                    long length)
                    throws SQLException

將指定參數設置為給定輸入串流，該輸入串流將具有指定位元組數。

在將一個非常大的二進制值輸入到 LONGVARBINARY 參數時，

通過 java.io.InputStream 物件發送它可能更為實際。

將根據需要從串流中讀取資料，一直讀取到檔案末尾。

註：此串流物件既可以是一個標準 Java 串流物件，

也可以是實作標準介面的使用者自己的子類別。

參數：

parameterIndex - 第一個參數是 1，第二個參數是 2，……

x - 套件含二進制參數值的 java 輸入串流

length - 串流中的位元組數

拋出：

SQLException - 如果 parameterIndex 不對應於 SQL 語句中的參數標記；

如果發生資料庫存取錯誤，或者在關閉的 PreparedStatement 上調用此方法

從以下版本開始：

1.6

setBinaryStream()方法所印出結果:
setBinaryStream(int parameterIndex, InputStream x ) throws SQLException

setBinaryStream(int parameterIndex, InputStream x, int length ) throws SQLException

setBinaryStream(int parameterIndex, InputStream x, long length ) throws SQLException

結論:
確認環境為postgresql-8.3-603.jdbc4及jdk1.6.0_10，而setBinaryStream(int, InputStream) 及 setBinaryStream(int, InputStream, long)所拋出的未實作的完成的異常，而拋出異常的位置為org.postgresql.util.PSQLException，表示setBinaryStream(int, InputStream) 及 setBinaryStream(int, InputStream, long)在PostgreSQL JDBC中還未實作出來，所以要使用setBinaryStream()方法時，還是得透過setBinaryStream(int, InputStream, int)。

Java Type vs PostgreSQL Type (零) 總整理

撰寫動機:
面對PostgreSQL 龐大的型別組織，對於JAVA的型別對應上，難免會有所選擇，
若有詳細的比較JAVA型別對應PostgreSQL型別的資料，可以便於撰寫程式。

測試目的:
選擇使用頻率較高的型別做測試，包含基本型別，外覆類別，日期，二進制檔案(img and mp3)
還有序列化檔案等，作為測試要素，以尋求出PostgreSQL所對應型別。

型別表格:

Java Types	PostgreSQL Types
布林型別
boolean	boolean
Boolean
字元型別
String(char , Character)	character
	character varying
	text
整數型別
byte	integer(smallint)
Byte
short
Short
int
Integer
long	bigint
Long
浮點數型別
float	double precision (real)
Float
double
Double
時間型別
java.util.Date	date,time,timestamp
java.util.Calendar
二進制檔案
.gif	bytea
.jpg
.png
.mp3
序列化資料
SerializableData (SampleData(Object))	bytea

Java Type vs PostgreSQL Type (十四) 序列化資料之對應

序列化資料:

在測試序列化資料時，先在資料庫建置一張資料表。

資料表語法:

CREATE TABLE serialtypes
(
serial bytea,
id serial NOT NULL,
CONSTRAINT serialtypes_pkey PRIMARY KEY (id)
)

原始程式碼:

JavaSerializableType.java:
package zasax.type.binarytypes;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import zasax.conn.PostgreConnection;
import zasax.sample.SampleData;
import zasax.serial.SerializableFlow;

/**
*
* @author YiCheng,Hsiao
*/

public class JavaSerializableType {

private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private String setSQL = "Insert into serialtypes (serial) values (?)";
private String getSQL = "select * from serialtypes";

public static void main(String[] args)
throws IOException, ClassNotFoundException, SQLException {
SerializableFlow sf = new SerializableFlow();
JavaSerializableType jst = new JavaSerializableType();
SampleData sd = new SampleData();
sd.setStr("這是個序列化檔案的測試。");
SampleData sd1;
ByteArrayOutputStream baos =
(ByteArrayOutputStream) sf.serializeToByte(sd);
jst.setSerializableValue(
new ByteArrayInputStream(baos.toByteArray()), baos.toByteArray().length);
ResultSet rs = jst.getSerializableValue();
while (rs.next()) {
sd1 = (SampleData) sf.decodeSerializableValue(rs.getBytes("serial"));
System.out.println(sd1.getStr());
}
}

public void setSerializableValue(InputStream is, int length)
throws IOException, ClassNotFoundException, SQLException {
PostgreConnection pc = new PostgreConnection();
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(setSQL);
pstmt.setBinaryStream(1, is, length);
pstmt.execute();
pstmt.close();
conn.close();
}

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

SerializableFlow.java:

package zasax.serial;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;

/**
*
* @author YiCheng,Hsiao
*/

public class SerializableFlow {

public OutputStream serializeToByte(T toSerializableValue)
throws IOException {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(baos);
oos.writeObject(toSerializableValue);
return baos;
}

public T decodeSerializableValue(byte[] getSerializbleValue)
throws IOException, ClassNotFoundException {
ObjectInputStream ois = new ObjectInputStream(
new ByteArrayInputStream(getSerializbleValue));
return (T) ois.readObject();
}
}

SampleData.java:

package zasax.sample;

import java.io.Serializable;

/**
*
* @author YiCheng,Hsiao
*/

public class SampleData implements Serializable {

private String str;

public String getStr() {
return str;
}

public void setStr(String str) {
this.str = str;
}
}

實際測試程序:

儲存序列化資料的方式:

先建置一個範例資料物件:SampleData，並set一個字串作為存取資料，再透過Serializable中的serializeToByte方法，將SampleData物件傳入序列化轉成二進制資料(baos(ByteArrayOutputStream))，再將這筆二進制資料回傳給bais(ByteArrayInputStream)承接，不過承接前要將baos透過toByteArray()的方法轉成byte[]，再放置到bais，再經由setSerializableValue()這個方法，將bais傳入，再由setBinaryStream()存到資料庫。

讀取序列化資料的方式:

在getSerializableValue()這個方法，透過excuteQuery()這個方法，將SQL語法:"select * from serialtypes "，將資料表資料傳回給ResultSet，再回傳至main()區塊的ResultSet rs 承接，再由rs.getByte()將欄位的資料讀出，作為sf.decodeSerialzableValue()的傳入參數，經過解序列化的方式，將二進制資料轉回成原物件(SampleData)，再將SampleData回傳至給SampleData sd1此物件承接。要驗證是否為SampleData的物件，先前再存入資料庫前，有set一串字串，所以透過getStr()的方式，驗證是否跟存入的字串相同。

印出的結果:

整理表格如下:

Java Types	PostgreSQL Types	特性
SerializableData (SampleData(Object))	bytea	4 bytes加上實際的二進制字串，變長的二進制字串

Java Type vs PostgreSQL Type (十三) 二進制檔案之對應

二進制檔案:
再進行二進制檔案測試時，先在資料庫創建一張資料表。
資料表語法:
CREATE TABLE binarytypes
(
gif bytea,
jpg bytea,
png bytea,
mp3 bytea,
id serial NOT NULL,
CONSTRAINT binarytypes_pkey PRIMARY KEY (id)
)

原始程式碼:
JavaBinaryFileType:
package zasax.type.binarytypes;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import zasax.conn.PostgreConnection;

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

private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private String setSQL = "Insert into binarytypes (gif,jpg,png,mp3) values (?,?,?,?)";
private String getSQL = "select * from binarytypes";

public static void main(String[] args)
throws IOException, SQLException, ClassNotFoundException {
JavaBinaryFileType jbft = new JavaBinaryFileType();
String fileroad = "binaryfiles/";
jbft.setBinaryValue(
new File(fileroad + "man.gif"), new File(fileroad + "po.jpg"),
new File(fileroad + "allpass.png"), new File(fileroad + "song.mp3"));
ResultSet rs = jbft.getBinaryValue();
for (int i = 1; rs.next(); i++) {
new FileOutputStream
(fileroad + "man" + i + ".gif").write(rs.getBytes("gif"));
new FileOutputStream
(fileroad + "po" + i + ".jpg").write(rs.getBytes("jpg"));
new FileOutputStream
(fileroad + "allpass" + i + ".png").write(rs.getBytes("png"));
new FileOutputStream
(fileroad + "song" + i + ".mp3").write(rs.getBytes("mp3"));
}
}

public void setBinaryValue(File gif, File jpg, File png, File mp3)
throws IOException, SQLException, ClassNotFoundException {
PostgreConnection pc = new PostgreConnection();
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(setSQL);
pstmt.setBinaryStream(1, new FileInputStream(gif), (int) gif.length());
pstmt.setBinaryStream(2, new FileInputStream(jpg), (int) jpg.length());
pstmt.setBinaryStream(3, new FileInputStream(png), (int) png.length());
pstmt.setBinaryStream(4, new FileInputStream(mp3), (int) mp3.length());
pstmt.execute();
pstmt.close();
conn.close();

}

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

實際測試程序:

儲存二進制檔案的方式:
透過setBinaryValue(File, File, File, File) 分別傳入.gif, .jpg, .png. .mp3 等各式檔案。再透過setBinaryStream(int, InputStream, int)的方法，將資料儲存至資料庫。

讀取二進制檔案的方式:
經getBinaryValue()的方法，先透過executeQuery()，將SQL語法"select * from binarytypes"傳入資料庫查詢，回傳ResultSet指派給rs，再將rs回傳指派給main()區塊的ResultSet，而ResultSet保存著SQL查詢完後的資料表內容，再分別透過ResultSet資料寫入FileOutputStream，再透過FileOutputStream的方法write()，將資料寫出成檔案。

印出的結果:

整理表格如下:

Java Types	PostgreSQL Types	特性
.gif	bytea	4 bytes加上實際的二進制字串，變長的二進制字串
.jpg
.png
.mp3

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