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