将文件保存到数据库，并取出

这几天在做将文件保存到数据库的一个工作，因为在cloud中规定不能将文件download下来并且保存在server上。

要做的工作是：

1. 从FTP上download下来字节流并且保存到数据库

2.从数据库读出这个数据流并且显示相应的数据

 

步骤：

1.建立表ad_download_file，字段有fileid，filename，filedata。用BLOB存储文件

CREATE TABLE ad_download_file (
  filename VARCHAR2(50) NOT NULL,
  fileid   INTEGER      NOT NULL,
  filedata BLOB         NULL
)

 

2.因为cloud规定只能用存储过程，因此建了一个存储过程用于插入文件

CREATE OR REPLACE PROCEDURE sp_insert_file(fname IN VARCHAR,fdata out BLOB
) AS
countOfdata int;
BEGIN
select count(*) into countOfdata from ad_download_file where fname = filename;
if countOfdata = 0  then
insert into ad_download_file(fileid,filename,filedata) values(SEQ_AD_DOWNLOAD_FILE_ID.NEXTVAL,fname,empty_blob());
end if;
select filedata into fdata from ad_download_file where fname = filename FOR UPDATE;
END;
/

 在存储过程中，先判断有没有相同名字的文件已经存在，如果不存在，则先插入一个空的blob到数据库。在最后查出这条数据的filedata。注意最后一条select语句后面有个“FOR UPDATE".用于锁定当前这行数据。

3.java 代码

 

  public ArrayList doExecuteProcedure(String query, BaseVO valueObject) throws SWTException
    {
	ArrayList outputList = new ArrayList();
	Connection con = null;
	CallableStatement callTest = null;
	try
	{

	    DBQueryDetails dBQueryDetails = ApplicationDataConfig.getDaomap().lookupQueryHandler(query);
	    setBaseVO(valueObject);

	    con = initDBConnection(dBQueryDetails.getDatabaseName());

	    Logger.debug("connection Object =" + con);

	    boolean autoCommit = con.getAutoCommit();
	    con.setAutoCommit(false);
	    ArrayList<String> inputParamNamesArrayList = dBQueryDetails.getInputParamNamesArrayList();
	    Logger.debug("inputParamNamesArrayList =" + inputParamNamesArrayList);

	    if (inputParamNamesArrayList != null && inputParamNamesArrayList.size() > 0)
	    {
		intializeArrayListInputs(inputParamNamesArrayList, con);
	    }

	    callTest = con.prepareCall("call " + dBQueryDetails.getQuery());

	    ArrayList<String> inputParamNames = dBQueryDetails.getInputParamNames();
	    ArrayList inputParamValues = intializeQueryInputs(inputParamNames);

	    int i = 1;
	    for (int j = 0; j < inputParamValues.size(); j++, i++)
	    {
		callTest.setObject(i, inputParamValues.get(j));
	    }
	    ArrayList<String> outputParamNames = dBQueryDetails.getOutputParamNames();
	    Class VOClass = baseVO.getClass();
	    HashMap<String, String> fieldDataTypes = getFieldDataTypes(VOClass, outputParamNames);
	    for (int j = 0; j < outputParamNames.size(); j++, i++)
	    {
		callTest.registerOutParameter(i, getJavaToOracleDatatype(fieldDataTypes.get(outputParamNames.get(j))));
	    }

	    callTest.execute();
            //将数据通过反射设置到对象，并且返回对象链表
	    outputList = populateVO(i, callTest, outputParamNames, fieldDataTypes);

	    con.commit();
	    con.setAutoCommit(autoCommit);

	}
	catch (SQLException e)
	{
	    Logger.error("DBAction.doExecuteProcedure() ==> " + e.getMessage(), e);
	    throw new SWTException(e);
	}
	catch (Exception e)
	{
	    Logger.error("DBExecute.doExecuteProcedure() ==> " + e.getMessage());
	    throw new SWTException(e);
	}
	finally
	{
	    try
	    {
		if (callTest != null)
		{
		    callTest.close();
		    callTest = null;
		}
	    }
	    catch (SQLException ex)
	    {
		Logger.error("DBAction.doExecuteProcedure() ==> " + ex.getMessage(), ex);
		throw new SWTException(ex);
	    }
	    if (con != null)
	    {
		Logger.debug("doExecuteProcedure close conn:" + con);
		connMgr.freeConnection(ApplicationDataConfig.getConfiguration().getDbPoolName(), con);
	    }
	}
	return outputList;
    }

 //将数据通过反射设置到对象，并且返回对象链表
 private ArrayList populateVO(int i, CallableStatement callTest, ArrayList<String> outputParamNames,
	    HashMap<String, String> fieldDataTypes) throws SWTException
    {
	ArrayList outputList = new ArrayList();
	InputStream input = null;
	OutputStream blobOutput = null;
	try
	{
	    Class VOClass = baseVO.getClass();
	    Object VOObject = VOClass.newInstance();

	    for (int j = i - outputParamNames.size(), k = 0; j < i; j++, k++)
	    {
		String tempFieldName = outputParamNames.get(k);
		String tempMethName = createMethodName(tempFieldName, "set");
		Method method = VOClass.getMethod(tempMethName, Class.forName(fieldDataTypes.get(tempFieldName)));
		String typeName = fieldDataTypes.get(tempFieldName);

		if (DBConstants.BIGDECIMAL.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getDouble(j));
		}
		if (DBConstants.BOOLEAN.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getBoolean(j));
		}
		if (DBConstants.BYTE.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getByte(j));
		}
		if (DBConstants.SQL_DATE.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getDate(j));
		}
		if (DBConstants.UTIL_DATE.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getDate(j));
		}
		if (DBConstants.DOUBLE.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getDouble(j));
		}
		if (DBConstants.FLOAT.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getFloat(j));
		}
		if (DBConstants.INTEGER.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getInt(j));
		}
		if (DBConstants.LONG.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getLong(j));
		}
		if (DBConstants.OBJECT.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getObject(j));
		}
		if (DBConstants.SHORT.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getShort(j));
		}
		if (DBConstants.TIME.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getTime(j));
		}
		if (DBConstants.SQL_TIMESTAMP.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getTimestamp(j));
		}
		if (DBConstants.STRING.equalsIgnoreCase(typeName))
		{
		    method.invoke(VOObject, callTest.getString(j));
		}
		if (DBConstants.BLOB.equalsIgnoreCase(typeName))
		{
		    // When have outputStream in the object, then update to the
		    // database;
		    method.invoke(VOObject, callTest.getBlob(j));

		    // get the OutputStream parameter from the baseVO
		    String getMethName = createMethodName(tempFieldName, "get");
		    BLOB blob = (BLOB) callTest.getBlob(j);
		    if (blob != null)
		    {
			Object obj = null;
			String outputStreamName = null;

			try
			{ 
//通过反射拿到后缀名为OutputStream变量，例如
// private BLOB filedata;
// private ByteArrayOutputStream filedataOutputStream;
//filedataOutputStream用于保存数据库返回的文件数据，或者要传到数据库的文件数据
//这样做是因为文件的数据必须在connection关闭前都从数据库中拿出来。如果只返回Blob对象
//那么Blob对象中只保存了一行的数据
                            outputStreamName = tempFieldName + "OutputStream";
			    Method getOutputStreamMethod = VOClass.getMethod(createMethodName(outputStreamName, "get"));
			    obj = getOutputStreamMethod.invoke(baseVO);
			}
			catch (Exception e)
			{
			    Logger.info("DBAction.populateVO() ==> " + e.getMessage(), e);
			}
// when OutputStream is empty,need set the data into
// OutputStream object
  //判断是要更新数据还是要取数据，如果OutputStream是空的表示要取数据，否则是更新数据 
			if (obj == null)
			{
                            //拿到二进制数组字节流，并且写入到字节数组中
                            Method setOutputStreamMethod = VOClass.getMethod(createMethodName(outputStreamName, "set"), VOClass
				    .getDeclaredField(outputStreamName).getType());
			    ByteArrayOutputStream baot = new ByteArrayOutputStream();
			    input = blob.getBinaryStream();
			    int data = -1;
			    while ((data = input.read()) != -1)
				baot.write(data);

			    setOutputStreamMethod.invoke(VOObject, baot);
			}
// when OutputStream is not empty,update OutputStream
// object into database
 //通过toByteArray()方法将数据转成字节数组
			else
			{
			    ByteArrayOutputStream baot = (ByteArrayOutputStream) obj;
			    blobOutput = blob.setBinaryStream(0);
			    blobOutput.write(baot.toByteArray());
			    blobOutput.flush();
			}
		    }
		}
	    }

	    outputList.add(VOObject);
	}
	catch (Exception e)
	{
	    Logger.error("DBAction.populateVO() ==> " + e.getMessage(), e);
	    throw new SWTException(e);
	}
	finally
	{

	    try
	    {
		if (input != null)
		{
		    input.close();
		}
		if (blobOutput != null)
		{
		    blobOutput.close();
		}
	    }
	    catch (IOException e)
	    {
		// TODO Auto-generated catch block
		Logger.error("DBAction.populateVO() ==> " + e.getMessage(), e);
	    }

	}
	return outputList;
    }

总结：其实这个很简单，写这篇文章的目的是记录一下怎么去做大文件存取。并总结一下要注意的地方。一共有三点

1.必须把setAutoCommit()设置成false，因为如果是true，那么select出blob对象的时候，就commit了，那么BLOB对象也就断开了和数据库的链接

2.在存文件到BLOB对象之前需要先SELECT出BLOB对象，并且用FOR UPDATA锁住这行，以便在java程序中更新

3.在connection close之前需要拿出BLOB的所有数据。

4.在文件保存的时候肯定涉及到Byte和String 之间的转换，可以用下面代码将ByteArrayOutputStream转成BufferedReader

BufferedReader in = new BufferedReader(new InputStreamReader(new ByteArrayInputStream(readByteArrayOutputStream(fileName,
            ProcessesConstants.SP_SELECT_FILE).toByteArray())));