在开发中遇到读取BLOB大字段问题,在这里总结下。
BLOB全称为二进制大型对象(Binary Large Object)。它用于存储数据库中的大型二进制对象。可存储的最大大小为4G字节。
CLOB全称为字符大型对象(Character Large Object)。它与LONG数据类型类似,只不过CLOB用于存储数据库中的大型单字节字符数据块,不支持宽度不等的字符集。可存储的最大大小为4G字节。
通常像图片、文件、音乐等信息就用BLOB字段来存储,先将文件转为二进制再存储进去。而像文章或者是较长的文字,就用CLOB存储,这样对以后的查询更新存储等操作都提供很大的方便。
本人业务开发只涉及到BLOB大字段读取,代码如下:
/**
* BLOB大字段
* @Description: TODO(病程查询(BLOB大字段))
*/
public List<BingChengBean> getMedicalRecords(String zhuYuanHao, String startDate, String endDate) {
StringBuffer sql = new StringBuffer();
sql.append("SELECT ZHU_YUAN_HAO, JILU_DATE, BINGCHENG_INFO FROM VIEW_APP_MEDICAL where ZHU_YUAN_HAO=:zhuyuanHao");
MapSqlParameterSource msps = new MapSqlParameterSource();
msps.addValue("zhuyuanHao", zhuYuanHao);
if (startDate != null && !startDate.isEmpty()) {
sql.append(" and JILU_DATE>=:startDate");
msps.addValue("startDate", startDate.trim());
}
if (endDate != null && !endDate.isEmpty()) {
sql.append(" and JILU_DATE<=:endDate");
msps.addValue("endDate", endDate.trim() + " 23:59:59");
}
sql.append(" order by JILU_DATE desc");
return emrJdbcTemplete.query(sql.toString(), msps, new RowMapper<BingChengBean>() {
@Override
public BingChengBean mapRow(ResultSet rs, int rownum) throws SQLException {
BingChengBean bingChengBean = new BingChengBean();
bingChengBean.setBCDate(rs.getString("JILU_DATE"));
InputStream is = rs.getBlob("BINGCHENG_INFO").getBinaryStream();
ByteArrayOutputStream out = new ByteArrayOutputStream();
int len = 0;
try {
while((len=is.read())!=-1){
out.write(len);
}
bingChengBean.setDes(new String(out.toByteArray()));
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if(null!=out){
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if(is!=null){
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
return bingChengBean;
}
});
}