用JdbcTemplate查询Oracle数据库中的数据，并对Blob类型的数据进行处理

在项目中遇到了个问题：使用实体来接受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[]字节数组。