这几天在做将文件保存到数据库的一个工作，因为在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 Arr