将文件保存到数据库，并取出
这几天在做将文件保存到数据库的一个工作，因为在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) AScountOfdata int;BEGINselect count(*) into countOfdata from ad_download_file where fname = filename;if countOfdata = 0 theninsert 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 blockLogger.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())));?
?
?