A BLOB is a reference to data in a database. This example demonstrates how to retrieves bytes from a BLOB. try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT col_blob FROM mysql_all_table");
if (rs.next()) { // Get the BLOB from the result set Blob blob = rs.getBlob("col_blob");
// Get the number bytes in the BLOB long blobLength = blob.length();
// Get bytes from the BLOB in a byte array int pos = 1; // position is 1-based int len = 10; byte[] bytes = blob.getBytes(pos, len);
// Get bytes from the BLOB using a stream InputStream is = blob.getBinaryStream(); int b = is.read(); } } catch (IOException e) { } catch (SQLException e) { }
import oracle.sql.BLOB;
//import oracle.jdbc.driver.OracleResultSet;
import oracle.jdbc.OracleResultSet;
import java.io.*; /**
* 将对象插入到数据库中,发生错误时不处理,抛出到调用层
* @param sID 数据记录的ID
* @param sFilename 要插入到BLOB对象的文件全路径
* @param iFlag 标识插入到哪个栏位 0:送出的数据 1:返回的数据
* @return 插入是否成功
* @throws Exception 含文件IO例外和数据库操作例外
*/
public boolean insertBlob(String sID,String sFilename,int iFlag) throws Exception {
boolean sReturn = false;
String sSQLNewRow = "";
String sSQLLockRow = "";
String sSQLUpdateRow = "";
ResultSet rs = null;
Statement stmt = connection.createStatement();
sSQLNewRow = "update t_jhsjcgb set SCSHJ=EMPTY_BLOB(),SCSJ=sysdate where ID=?";
sSQLLockRow = "select SCSHJ from t_jhsjcgb where ID=? for update";
sSQLUpdateRow = "update t_jhsjcgb set SCSHJ=? where ID=?"; FileInputStream fis = new FileInputStream(sFilename.trim());
CBlob blob = new CBlob(fis); PreparedStatement pstmt = null;
connection.setAutoCommit(false);
pstmt = connection.prepareStatement(sSQLNewRow);
pstmt.setString(1,sID.trim());
pstmt.executeUpdate(); pstmt = connection.prepareStatement(sSQLLockRow);
pstmt.setString(1,sID.trim());
rs = pstmt.executeQuery();
if(rs.next()){
oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)((oracle.jdbc.OracleResultSet)rs).getBlob(1);
pstmt = connection.prepareStatement(sSQLUpdateRow);
pstmt.setString(2,sID.trim());
dbBlob.putBytes(1,blob.getRaw());
pstmt.setBlob(1,dbBlob);
connection.commit();
sReturn = true;
}else{
sReturn = false;
}
connection.setAutoCommit(true);
pstmt.close();
rs.close();
blob = null;
fis.close();
return sReturn;
}
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT col_blob FROM mysql_all_table");
if (rs.next()) {
// Get the BLOB from the result set
Blob blob = rs.getBlob("col_blob");
// Get the number bytes in the BLOB
long blobLength = blob.length();
// Get bytes from the BLOB in a byte array
int pos = 1; // position is 1-based
int len = 10;
byte[] bytes = blob.getBytes(pos, len);
// Get bytes from the BLOB using a stream
InputStream is = blob.getBinaryStream();
int b = is.read();
}
} catch (IOException e) {
} catch (SQLException e) {
}