Java Type vs PostgreSQL Type (四) 布林型別之對應

以數值作為Boolean儲存值
資料表語法:
CREATE TABLE booleantypes
(
"boolean" smallint,
boolean1 smallint,
id serial NOT NULL,
CONSTRAINT booleantypes_pkey PRIMARY KEY (id)
)

程式原始碼:
JavaBooleanTypeSetForNumber.java:
package zasax.type.booleantypes;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import zasax.conn.PostgreConnection;

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

private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private String setSQL = "Insert into booleantypes (boolean,boolean1) values (?,?)";
private String getSQL = "select * from booleantypes";

public static void main(String[] args) {
JavaBooleanTypeSetForNumber jbtsfn = new JavaBooleanTypeSetForNumber();
boolean b1 = false;
Boolean b2 = true;
jbtsfn.setBooleanValue(true, false);
ResultSet rs = jbtsfn.getBooleanValue();
try {
while (rs.next()) {
b1 = jbtsfn.isBoolean(rs.getShort("boolean"));
b2 = jbtsfn.isBoolean(rs.getShort("boolean1"));
System.out.println(b1 + " " + b2);
}
} catch (SQLException ex) {
Logger.getLogger(JavaBooleanType.class.getName()).log(Level.SEVERE, null, ex);
}
}

public void setBooleanValue(boolean b1, Boolean b2) {
PostgreConnection pc = new PostgreConnection();
try {
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(setSQL);
pstmt.setShort(1, this.isBooleanValue(b1).shortValue());
pstmt.setShort(2, this.isBooleanValue(b2).shortValue());
pstmt.execute();
pstmt.close();
conn.close();
} catch (IOException ex) {
Logger.getLogger(JavaBooleanType.class.getName()).log(Level.SEVERE, null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(JavaBooleanType.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(JavaBooleanType.class.getName()).log(Level.SEVERE, null, ex);
}
}

public boolean isBoolean(int i) {
return i == 1;
}

public Integer isBooleanValue(Boolean b) {
return b.booleanValue() ? 1 : 0;
}

public ResultSet getBooleanValue() {
PostgreConnection pc = new PostgreConnection();
try {
this.conn = pc.getConnection();
this.pstmt = conn.prepareStatement(getSQL);
this.rs = pstmt.executeQuery();
} catch (IOException ex) {
Logger.getLogger(JavaBooleanType.class.getName()).log(Level.SEVERE, null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(JavaBooleanType.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(JavaBooleanType.class.getName()).log(Level.SEVERE, null, ex);
}
return rs;
}
}

實際測試流程:

儲存Boolean值方式:
透過setBooleanValue(boolean , Boolean ) 這個方法，將傳入值true,false分別指派給boolean(基本型別)，Boolean(外覆類別)。再經過isBooleanValue(Boolean b) 轉成Integer值，再透過Integer.shortValue()的方式，將這兩個變數分別存入PostgreSQL Type : smallint 的兩個欄位(boolean , boolean1)。傳入值在資料表上有呈現資料，表示傳入成功。

讀取Boolean值方式:
經getBooleanValue()的方法，先透過executeQuery()，將SQL語法"select * from booleantypes"傳入資料庫查詢，回傳ResultSet指派給rs，再將rs回傳指派給main()區塊的ResultSet，而ResultSet保存著SQL查詢完後的資料表內容，再分別透過ResultSet將回傳Short值至isBoolean(int i)轉譯為boolean值再分別將回傳給boolean b1，以及Boolean b2，最後在印出結果true,false，為了確定印出值為資料庫所傳回的，在初始值上，將boolean b1 = false ， Boolean b2 = true，以作為驗證其結果。