在项目中遇到了个问题:使用实体来接受Oracle数据库中的数据,如果数据表中字段类型是Blob类型的话,实体中的该字段类型是byte[]时,就会将该字段的数据缺省,经过排查发现:我们需要使用Blob类型的数据进行接收,在将其转化为byte[]数组。
1、配置数据源
public JdbcTemplate getJdbcTemplate(){
DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://192.168.55.33:3306/uap?characterEncoding=utf-8&useSSL=true");
dataSource.setUsername("root");
dataSource.setPassword("root");
JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
return jdbcTemplate;
}
2、将Blob类型的数据转化为byte[]数组的方法
public byte[] blobToBytes(Blob blob) {
BufferedInputStream is = null;
try {
is = new BufferedInputStream(blob.getBinaryStream());
byte[] bytes = new byte[(int) blob.length()];
int len = bytes.length;
int offset = 0;
int read = 0;
while (offset < len
&& (read = is.read(bytes, offset, len - offset)) >= 0) {
offset += read;
}
return bytes;
} catch (Exception e) {
return null;
} finally {
try {
is.close();
is = null;
} catch (IOException e) {
return null;
}
}
}
3、查询数据的方法(SignInfo是一个实体类)
public List<SignInfo> findSignInfo() {
String sql = "SELECT t.* FROM OLSS_SIGN_INFO t WHERE t.form_type in ('1','2','3') and (t.document_id is null) and is_upload_uds = '1'";
List<SignInfo> signInfoList = getJdbcTemplate().query(sql, new Object[] { }, new RowMapper<SignInfo>(){
@Override
public SignInfo mapRow(ResultSet rs, int rowNum)
throws SQLException {
SignInfo info = new SignInfo();
// TODO Auto-generated method stub
//sign_file在数据库中是Blob类型的数据
Blob blob = rs.getBlob("sign_file");
//将Blob类型的数据转化为byte[]
byte[] bytes = blobToBytes(blob);
info.setSignFile(bytes);
info.setSignFlowId(rs.getString("sign_flow_id"));
info.setServiceId(rs.getString("service_id"));
info.setFileName(rs.getString("file_name"));
info.setUserId(rs.getString("user_id"));
info.setUserName(rs.getString("user_name"));
info.setDeptId(rs.getString("dept_id"));
info.setDeptName(rs.getString("dept_name"));
info.setUnitId(rs.getString("unit_id"));
info.setUnitName(rs.getString("unit_name"));
info.setReplaceDeptId(rs.getString("replace_user_id"));
info.setReplaceUserName(rs.getString("replace_user_name"));
info.setReplaceUserId(rs.getString("replace_dept_id"));
info.setReplaceDeptName(rs.getString("replace_dept_name"));
info.setReplaceUnitId(rs.getString("replace_unit_id"));
info.setReplaceUnitName(rs.getString("replace_unit_name"));
info.setClientIp(rs.getString("client_ip"));
info.setServerIp(rs.getString("server_ip"));
info.setSignPos(rs.getInt("sign_pos"));
info.setSignStatus(rs.getInt("sign_status"));
info.setCreateTime(rs.getTimestamp("create_time"));
info.setModifyTime(rs.getTimestamp("modify_time"));
info.setActiveTime(rs.getTimestamp("active_time"));
info.setSignTime(rs.getTimestamp("sign_time"));
info.setConsumeTime(rs.getLong("consume_time"));
info.setIsReplace(rs.getInt("is_replace"));
info.setInfoStatus(rs.getInt("info_status"));
info.setMsgSynchStatus(rs.getLong("msg_synch_status"));
info.setErrorMsg(rs.getString("error_msg"));
info.setDocumentId(rs.getString("document_id"));
info.setVersionId(rs.getString("version_id"));
info.setMd5(rs.getString("md5"));
info.setUploadTime(rs.getTimestamp("upload_time"));
info.setRetryTimes(rs.getInt("retry_times"));
info.setIsUploadUds(rs.getInt("is_upload_uds"));
info.setFormType(rs.getInt("form_type"));
info.setSignPosText(rs.getString("sign_pos_text"));
return info;
}
});
return signInfoList;
}
在Oracel数据表中如果字段的类型的Blob类型,则需要使用Blob类型进行接收,在将其转化为byte[]字节数组。