2008-10-03

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

沒有留言:

網誌存檔

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)