文件上传下载

 

1.上传文件，得到文件流InputStream

@Override
public BCPUploadResult upload(InputStream[] iss, Map<String, Object> map)
throws Exception {

 

2.将文件流写入表的Blob 字段

BCPUploadUtils.writeBlob(iss[0], "T_BCP_UPLOAD", "FILE1_LOB", "ID = "
+ bcpUpload.getId());

　　　　

public static void writeBlob(InputStream ins, String sTable, String sField,
　　String sClause) throws GenericException {
　　DBean db = null;
　　PreparedStatement pst = null;
　　try {
　　　　db = new DBean();
　　　　db.connect();
　　　　Connection conn = db.getConnection();

　　　　String sql = " update " + sTable + " set " + sField
　　　　+ " = EMPTY_BLOB() WHERE " + sClause;
　　　　pst = conn.prepareStatement(sql);
　　　　pst.executeUpdate();
　　　　pst.close();

　　　　DBUtils.writeBlob(ins, sTable, sField, sClause);
　　} catch (Exception ex) {
　　　　throw ExceptionFactory.parse(ex);
　　} finally {
　　　　DBean.closeAll(null, pst, db);
　　}
　}

 

3. 读出Blob字段，解析每行数据

InputStream is1 = new ByteArrayInputStream(DBUtils.readBlob_byte(
"T_BCP_UPLOAD", "FILE1_LOB", "ID = " + bcpUpload.getId()));

result.setUploadId(bcpUpload.getId());
// Convert io stream to VO model
excelGHBBankGLFileDataList = parseToGHBBankGLFileObjList(is1);

 

private List<GHBBankGLFileVO> parseToGHBBankGLFileObjList(InputStream is) {

List<GHBBankGLFileVO> excelDataList = new ArrayList<GHBBankGLFileVO>();
Workbook wb = null;
try {
wb = new HSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
try {
wb = new XSSFWorkbook(is);
} catch (IOException e1) {
e1.printStackTrace();
}
}
try {
Sheet st = wb.getSheetAt(0);

int rows = st.getPhysicalNumberOfRows();
if (rows <= 0) {
return excelDataList;
}

for (int r = 1; r <= rows; r++) {
Row row = st.getRow(r);
if (row == null) {
return excelDataList;
}

// row.getCell(r).setCellType(1);

Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
&& HSSFDateUtil.isCellDateFormatted(cell)) {
// do nothing
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
}

String sequenceNo = getStringCell(row, 0);
String branchCode = getStringCell(row, 1);
String branchName = getStringCell(row, 2);
String branchZone = getStringCell(row, 3);
String loanAccountNo = getStringCell(row, 4);
String applicationNo = getStringCell(row, 5);
String companyCode = getStringCell(row, 6);
String compaynName = getStringCell(row, 7);
String title = getStringCell(row, 8);
String firstName = getStringCell(row, 9);
String lastName = getStringCell(row, 10);
String idCardNo = getStringCell(row, 11);
String paymentMethod = getStringCell(row, 12);
String coverageTerm = getStringCell(row, 13);
String commencementDate = getStringDate(row, 14);
String coverageEndDate = getStringDate(row, 15);
BigDecimal sumInsured = getBigdecimalCell(row, 16);
BigDecimal premium = getBigdecimalCell(row, 17);
BigDecimal bankCommission = getBigdecimalCell(row, 18);
BigDecimal vat = getBigdecimalCell(row, 19);
String transferDate = getStringDate(row, 20);

// read excel end
if (StringUtils.isNullOrEmpty(sequenceNo)
|| StringUtils.isNullOrEmpty(branchCode)
|| StringUtils.isNullOrEmpty(branchName)
|| StringUtils.isNullOrEmpty(branchZone)
|| StringUtils.isNullOrEmpty(loanAccountNo)
|| StringUtils.isNullOrEmpty(applicationNo)
|| StringUtils.isNullOrEmpty(companyCode)
|| StringUtils.isNullOrEmpty(compaynName)
|| StringUtils.isNullOrEmpty(title)
|| StringUtils.isNullOrEmpty(firstName)
|| StringUtils.isNullOrEmpty(lastName)
|| StringUtils.isNullOrEmpty(idCardNo)
|| StringUtils.isNullOrEmpty(paymentMethod)
|| StringUtils.isNullOrEmpty(coverageTerm)
|| StringUtils.isNullOrEmpty(commencementDate)
|| StringUtils.isNullOrEmpty(coverageEndDate)
|| (sumInsured == null && premium == null)
|| (bankCommission == null && vat == null)
|| StringUtils.isNullOrEmpty(transferDate)) {
continue;
}

GHBBankGLFileVO vo = new GHBBankGLFileVO();
vo.setSequenceNo(sequenceNo);
vo.setBranchCode(branchCode);
vo.setBranchName(branchName);
vo.setBranchZone(branchZone);
vo.setLoanAccountNo(loanAccountNo);
vo.setApplicationNo(applicationNo);
vo.setCompanyCode(companyCode);
vo.setCompaynName(compaynName);
vo.setTitle(title);
vo.setFirstName(firstName);
vo.setLastName(lastName);
vo.setIdCardNo(idCardNo);
vo.setPaymentMethod(paymentMethod);
vo.setCoverageTerm(coverageTerm);
vo.setCommencementDate(commencementDate);
vo.setCoverageEndDate(coverageEndDate);
if (sumInsured != null) {
vo.setSumInsured(sumInsured.setScale(2,
BigDecimal.ROUND_HALF_UP));
} else {
vo.setSumInsured(sumInsured);
}
if (premium != null) {
vo.setPremium(premium.setScale(2, BigDecimal.ROUND_HALF_UP));
} else {
vo.setPremium(premium);
}
if (bankCommission != null) {
vo.setBankCommission(bankCommission.setScale(2,
BigDecimal.ROUND_HALF_UP));
} else {
vo.setBankCommission(bankCommission);
}
if (vat != null) {
vo.setVat(vat.setScale(2, BigDecimal.ROUND_HALF_UP));
} else {
vo.setVat(vat);
}
vo.setTransferDate(transferDate);

excelDataList.add(vo);
}

} catch (Exception e) {
throw ExceptionFactory.parse(e);
} finally {
if (is != null) {
try {
is.close();
wb.close();
} catch (IOException e) {
throw ExceptionFactory.parse(e);
}
}
}
return excelDataList;
}