mybatis框架下BLob文件的插入和获取

BLOB文件的插入

本人将文件转换成byte数组，传进去的参数是byte数组。数据库BLOB类型对应的Java类型是Object，本人尝试用Blob或者byte[]来取得数据库BLob类型的数据，结果这些java对象都是空值。所以用Object类型来对应BLOB类型，后边将object转化成Blob类型。

pojo类型定义如下：

@Table(tablename = "POP_ATTACHMENT_INFO")
public class PersistenceAttachment {
	@TableKey(strategy = Strategy.NORMAL)
	@TableField(columnName = "ID")
	private String ID;

	@TableField(columnName = "PATHNAME")
	private String pathname;
	
	@TableField(columnName = "FILE")
	private Object file;

插入数据的过程：1.将自动事务提交改为false，打开会话。如果不改为false，将报这个错： ORA-22990: LOB 定位器无法扩展事务处理。

2.插入一个empty的blob。

3.获取到这个blob，取得blob的流，往流里写入byte数组。

4.关闭流。

5.提交事务，关闭连接。

代码如下：

public static void newAttach(String ID,byte[] data) throws EOPException{
		SqlSession session = SqlSessionManager.getBizSqlSessionFactory().openSession(false);
		OutputStream out = null;
		try {
			PersistenceAttachmentMapper mapper = session.getMapper(PersistenceAttachmentMapper.class);
//			mapper.insertAttach("21", data);
			mapper.newAttachment("23");
			PersistenceAttachment attachment = mapper.getBlob("23");
			
			Blob blob = (Blob)attachment.getFile();
			if (blob == null) {
				session.rollback(true);
			} else {
				out = blob.setBinaryStream(0);
				out.write(data);
				out.close();

			}
			
//			OutputStream out = file.setBinaryStream(0);
//			out = file.getBinaryOutputStream();//暂时使用这个废弃的方法  
//			out.write(data);
//			out.close();
		} catch (Exception e) {
			throw new EOPException(e, "newAttachment to db error", ExceptionCode.DBPROXY_NEW_ATTACHMENT_ERROR);
		}finally{
			session.commit();
			session.close();
		}
		
	}

mybatis里的mapper方法:

@Insert("INSERT INTO POP_ATTACHMENT_INFO VALUES (#{0},NULL, empty_blob())")
	public void newAttachment(String ID);

@Select("SELECT * FROM POP_ATTACHMENT_INFO WHERE ID = #{ID} FOR UPDATE")
	public PersistenceAttachment getBlob(String ID);

注意getBlob()方法的sql语句要添加for update，为那行记录添加锁，如果没有for update,上面代码执行到out.close()的时候就会抛出异常，因为没有给那行加入锁，oracle会报锁的错误。

Blob文件的获取

public static byte[] getAttach(String ID) throws EOPException {
		byte[] data = null;
		SqlSession session = SqlSessionManager.getBizSqlSessionFactory().openSession(true);
		try {
			PersistenceAttachmentMapper mapper = session.getMapper(PersistenceAttachmentMapper.class);
			PersistenceAttachment pAttachment = mapper.getAttachment(ID);
			if (pAttachment == null) {
				return null;
			} else {
				Blob file = (Blob)pAttachment.getFile();
				int length = (int)file.length();
				data = file.getBytes(1, length);
//				InputStream in = file.getBinaryStream();
//				int length = (int)file.getLength();
//				data = new byte[length];
//				in.read(data);
//				in.close();			
			}	
		} catch (Exception e) {
			throw new EOPException(e, "getAttach from db error", ExceptionCode.DBPROXY_GET_ATTACHMENT_ERROR);
		}
		
		return data;
	}

@Select("SELECT * FROM POP_ATTACHMENT_INFO WHERE ID = #{ID}")
	public PersistenceAttachment getAttachment(String ID);