Java tutorial
/* * Copyright (C) 2010---2014 (wuweixing)<349446658@qq.com> * * This file is part of Wabacus * * Wabacus is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package com.wabacus.system.dataimport; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import com.wabacus.config.Config; import com.wabacus.config.database.type.AbsDatabaseType; import com.wabacus.config.resource.dataimport.configbean.AbsDataImportConfigBean; import com.wabacus.config.resource.dataimport.configbean.DataImportSqlBean; import com.wabacus.exception.WabacusDataImportException; import com.wabacus.system.assistant.FilePathAssistant; import com.wabacus.system.assistant.WabacusAssistant; import com.wabacus.system.dataimport.filetype.AbsFileTypeProcessor; import com.wabacus.system.datatype.AbsDateTimeType; import com.wabacus.system.datatype.IDataType; import com.wabacus.system.datatype.VarcharType; import com.wabacus.util.Consts_Private; import com.wabacus.util.Tools; public class DataImportItem { private static Log log = LogFactory.getLog(DataImportItem.class); private AbsDataImportConfigBean configBean; AbsFileTypeProcessor fileProcessor; private File datafileObj; private String dynimportype;//??????append/overwrite/delete???? private int batchupdatesize = 0; private List<String> lstColNamesTrace; private List lstErrorColValuesTrace; private String errorSqlTrace; private HttpServletRequest request; private HttpSession session; public HttpServletRequest getRequest() { return request; } public void setRequest(HttpServletRequest request) { this.request = request; } public HttpSession getSession() { return session; } public void setSession(HttpSession session) { this.session = session; } public DataImportItem(AbsDataImportConfigBean configBean, File datafileObj) { this.configBean = configBean; this.datafileObj = datafileObj; if (datafileObj == null) { throw new WabacusDataImportException("??"); } String strbatchupdatesize = Config.getInstance().getSystemConfigValue("dataimport-batchupdate-size", "10"); try { this.batchupdatesize = Integer.parseInt(strbatchupdatesize); } catch (NumberFormatException e) { log.warn("wabacus.cfg.xml?dataimport-batchupdate-size" + strbatchupdatesize + "?"); this.batchupdatesize = 10; } } public String getDynimportype() { return dynimportype; } public void setDynimportype(String dynimportype) { this.dynimportype = dynimportype; } public AbsDataImportConfigBean getConfigBean() { return configBean; } public AbsFileTypeProcessor getFileProcessor() { return fileProcessor; } public File getDatafileObj() { return datafileObj; } public void doImportData() { fileProcessor = configBean.createDataImportProcessor(); if (datafileObj == null) { throw new WabacusDataImportException( "?" + configBean.getReskey() + "??"); } if (!datafileObj.exists() || datafileObj.isDirectory()) { throw new WabacusDataImportException("?" + configBean.getReskey() + "?" + datafileObj.getAbsolutePath() + "?"); } Connection conn = null; try { fileProcessor.init(datafileObj); if (configBean.getInterceptor() != null) { boolean flag = configBean.getInterceptor().doImportStart(this); if (!flag) return; } conn = Config.getInstance().getDataSource(configBean.getDatasource()).getConnection(); AbsDatabaseType dbtype = Config.getInstance().getDataSource(configBean.getDatasource()).getDbType(); if (dynimportype == null || dynimportype.trim().equals("") || dynimportype.trim().equals(configBean.getImporttype())) {//????? doUpdateData(conn, dbtype, configBean.getLstImportSqlObjs(null)); } else if (dynimportype.trim().equals(Consts_Private.DATAIMPORTTYPE_APPEND) || dynimportype.trim().equals(Consts_Private.DATAIMPORTTYPE_OVERWRITE)) { doUpdateData(conn, dbtype, configBean.getLstImportSqlObjs(dynimportype)); } else if (dynimportype.trim().equals(Consts_Private.DATAIMPORTTYPE_DELETE)) { doDeleteData(conn, dbtype); } else { log.warn("?" + this.datafileObj.getAbsolutePath() + "?????"); doUpdateData(conn, dbtype, configBean.getLstImportSqlObjs(null)); } log.info("?" + datafileObj.getAbsolutePath() + "" + configBean.getReskey() + "?"); try { if (configBean.getInterceptor() != null) { configBean.getInterceptor().doImportEnd(1, this, null); } } catch (Exception e) { throw new WabacusDataImportException( "?" + datafileObj.getAbsolutePath() + "?", e); } if (!conn.getAutoCommit()) conn.commit(); } catch (Exception e) { if (configBean.getInterceptor() != null) { configBean.getInterceptor().doImportEnd(-1, this, e); } StringBuffer errorBuf = new StringBuffer(); if (this.errorSqlTrace != null && !this.errorSqlTrace.trim().equals("")) { errorBuf.append("?SQL" + this.errorSqlTrace + ""); } if (this.lstErrorColValuesTrace != null && this.lstErrorColValuesTrace.size() > 0) { errorBuf.append("?"); for (int i = 0; i < this.lstErrorColValuesTrace.size(); i++) { errorBuf.append("["); if (this.lstColNamesTrace != null && this.lstColNamesTrace.size() == this.lstErrorColValuesTrace.size()) { errorBuf.append(this.lstColNamesTrace.get(i)).append(":"); } errorBuf.append(this.lstErrorColValuesTrace.get(i)).append("]"); } } else if (this.batchupdatesize != 1) { errorBuf.append( "?wabacus.cfg.xmldataimport-batchupdate-size??1"); } log.error(errorBuf.toString(), e); throw new WabacusDataImportException("?" + datafileObj.getAbsolutePath() + ""); } finally { fileProcessor.destroy(); WabacusAssistant.getInstance().release(conn, null); } } private void doDeleteData(Connection conn, AbsDatabaseType dbtype) throws SQLException { PreparedStatement pstmtDelete = null; try { DataImportSqlBean disqlbean = null; if (fileProcessor.isEmpty()) {//???? disqlbean = new DataImportSqlBean(); disqlbean.setSql("delete from " + configBean.getTablename()); } else { disqlbean = configBean.getLstImportSqlObjs(dynimportype).get(0); } log.debug(disqlbean.getSql()); this.errorSqlTrace = disqlbean.getSql(); pstmtDelete = conn.prepareStatement(disqlbean.getSql()); if (disqlbean.getLstParamColsInFile() == null || disqlbean.getLstParamColsInFile().size() == 0) { pstmtDelete.executeUpdate(); } else { boolean matchFileIndex = configBean.getColMapBean().getFileMapType().equals("index"); List<String> lstColNames = getLstColNames(matchFileIndex); this.lstColNamesTrace = lstColNames; boolean hasUnCommitData = false;//???? List lstDataColValuesPerRow; Map<String, Object> mDataColValues = null; int i = fileProcessor.getStartrecordindex(); for (int len = fileProcessor.getStartrecordindex() + fileProcessor.getRecordcount(); i < len; i++) { lstDataColValuesPerRow = fileProcessor.getRowData(i); if (lstDataColValuesPerRow == null || lstDataColValuesPerRow.size() == 0) continue; if (configBean.getInterceptor() != null) { boolean flag = configBean.getInterceptor().beforeImportRow(conn, this, lstColNames, lstDataColValuesPerRow); if (!flag) continue; } if (!matchFileIndex) { mDataColValues = getAllColTitleAndValueMap(lstColNames, lstDataColValuesPerRow); } updateDBRowData(pstmtDelete, dbtype, disqlbean.getLstParamColsInFile(), disqlbean.getLstParamTypes(), matchFileIndex, lstDataColValuesPerRow, mDataColValues); if (configBean.getInterceptor() != null) { configBean.getInterceptor().afterImportRow(conn, this, lstColNames, lstDataColValuesPerRow); } hasUnCommitData = true; if (shouldBatchCommit(i)) { pstmtDelete.executeBatch(); hasUnCommitData = false; } } if (hasUnCommitData) { pstmtDelete.executeBatch(); } } } finally { fileProcessor.destroy(); WabacusAssistant.getInstance().release(null, pstmtDelete); } } private boolean shouldBatchCommit(int rownum) { if (this.batchupdatesize == 1) return true;//1????? if (this.batchupdatesize > 0 && rownum != 0 && rownum % this.batchupdatesize == 0) return true; return false; } private void doUpdateData(Connection conn, AbsDatabaseType dbtype, List<DataImportSqlBean> lstDisqlbean) throws SQLException { PreparedStatement pstmtDelete = null; PreparedStatement pstmtInsert = null; try { String importype = null; if (this.dynimportype != null && (this.dynimportype.trim().equals(Consts_Private.DATAIMPORTTYPE_APPEND) || this.dynimportype.trim().equals(Consts_Private.DATAIMPORTTYPE_OVERWRITE))) { importype = this.dynimportype.trim(); } else { importype = configBean.getImporttype(); } if (fileProcessor.isEmpty()) return; if (importype.equals(Consts_Private.DATAIMPORTTYPE_OVERWRITE)) { DataImportSqlBean disqlbean = lstDisqlbean.get(0); log.debug(disqlbean.getSql()); this.errorSqlTrace = disqlbean.getSql(); pstmtDelete = conn.prepareStatement(disqlbean.getSql()); pstmtDelete.executeUpdate(); disqlbean = lstDisqlbean.get(1); log.debug(disqlbean.getSql()); pstmtInsert = conn.prepareStatement(disqlbean.getSql()); updateRowDataToDB(dbtype, conn, null, pstmtInsert, null, disqlbean); } else { DataImportSqlBean disqlbeanDelete = null; DataImportSqlBean disqlbeanInsert = null; if (lstDisqlbean.size() == 2) {//?keyfields?? disqlbeanDelete = lstDisqlbean.get(0); log.debug(disqlbeanDelete.getSql()); pstmtDelete = conn.prepareStatement(disqlbeanDelete.getSql()); disqlbeanInsert = lstDisqlbean.get(1); } else { disqlbeanInsert = lstDisqlbean.get(0); } log.debug(disqlbeanInsert.getSql()); pstmtInsert = conn.prepareStatement(disqlbeanInsert.getSql()); updateRowDataToDB(dbtype, conn, pstmtDelete, pstmtInsert, disqlbeanDelete, disqlbeanInsert); } } finally { WabacusAssistant.getInstance().release(null, pstmtDelete); WabacusAssistant.getInstance().release(null, pstmtInsert); } } private void updateRowDataToDB(AbsDatabaseType dbtype, Connection conn, PreparedStatement pstmtDelete, PreparedStatement pstmtInsert, DataImportSqlBean disqlbeanDelete, DataImportSqlBean disqlbeanInsert) throws SQLException { List lstColsInFileDelete = null; List<IDataType> lstColTypeDelete = null; if (disqlbeanDelete != null) { lstColsInFileDelete = disqlbeanDelete.getLstParamColsInFile(); lstColTypeDelete = disqlbeanDelete.getLstParamTypes(); } List lstColsInFileInsert = disqlbeanInsert.getLstParamColsInFile(); List<IDataType> lstColTypeInsert = disqlbeanInsert.getLstParamTypes(); boolean matchFileIndex = configBean.getColMapBean().getFileMapType().equals("index"); List<String> lstColNames = this.getLstColNames(matchFileIndex);//??? boolean hasUnCommitData = false; List lstDataColValuesPerRow; Map<String, Object> mDataColValues = null; int i = fileProcessor.getStartrecordindex(); for (int len = fileProcessor.getStartrecordindex() + fileProcessor.getRecordcount(); i < len; i++) { lstDataColValuesPerRow = fileProcessor.getRowData(i); if (lstDataColValuesPerRow == null || lstDataColValuesPerRow.size() == 0) continue; if (configBean.getInterceptor() != null) { boolean flag = configBean.getInterceptor().beforeImportRow(conn, this, lstColNames, lstDataColValuesPerRow); if (!flag) continue; } if (!matchFileIndex) { mDataColValues = getAllColTitleAndValueMap(lstColNames, lstDataColValuesPerRow); } if (pstmtDelete != null) { this.errorSqlTrace = disqlbeanDelete.getSql(); updateDBRowData(pstmtDelete, dbtype, lstColsInFileDelete, lstColTypeDelete, matchFileIndex, lstDataColValuesPerRow, mDataColValues); if (this.shouldBatchCommit(i)) { pstmtDelete.executeBatch(); } } this.errorSqlTrace = disqlbeanInsert.getSql(); updateDBRowData(pstmtInsert, dbtype, lstColsInFileInsert, lstColTypeInsert, matchFileIndex, lstDataColValuesPerRow, mDataColValues); hasUnCommitData = true; if (this.shouldBatchCommit(i)) { hasUnCommitData = false; pstmtInsert.executeBatch(); } if (configBean.getInterceptor() != null) { configBean.getInterceptor().afterImportRow(conn, this, lstColNames, lstDataColValuesPerRow); } } if (hasUnCommitData) {//???pstmt if (pstmtDelete != null) { this.errorSqlTrace = disqlbeanDelete.getSql(); pstmtDelete.executeBatch(); } this.errorSqlTrace = disqlbeanInsert.getSql(); pstmtInsert.executeBatch(); } } private List<String> getLstColNames(boolean matchFileIndex) { List<String> lstColNames = fileProcessor.getLstColnameData(); if (matchFileIndex || lstColNames == null || lstColNames.size() == 0) { return lstColNames; } List<String> lstColNameResults = new ArrayList<String>(); for (String colNameTmp : lstColNames) { if (colNameTmp == null) { lstColNameResults.add(null); } else { lstColNameResults.add(colNameTmp.trim().toUpperCase()); } } return lstColNameResults; } public void backupOrDeleteDataFile() { backupOrDeleteDataFile(datafileObj); datafileObj = null; } public static void backupOrDeleteDataFile(File fileObj) { try { if (fileObj == null || !fileObj.exists()) { return; } String backuppath = Config.getInstance().getSystemConfigValue("dataimport-backuppath", ""); if (backuppath.equals("")) { log.debug("?" + fileObj.getAbsolutePath()); } else {//? String filename = fileObj.getName(); int idxdot = filename.lastIndexOf("."); if (idxdot > 0) { filename = filename.substring(0, idxdot) + "_" + Tools.getStrDatetime("yyyyMMddHHmmss", new Date()) + filename.substring(idxdot); } else { filename = filename + "_" + Tools.getStrDatetime("yyyyMMddHHmmss", new Date()); } File destFile = new File( FilePathAssistant.getInstance().standardFilePath(backuppath + File.separator + filename)); log.debug("?" + fileObj.getAbsolutePath() + "" + backuppath + ""); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(new FileInputStream(fileObj)); bos = new BufferedOutputStream(new FileOutputStream(destFile)); byte[] btmp = new byte[1024]; while (bis.read(btmp) != -1) { bos.write(btmp); } } catch (Exception e) { log.error("?" + fileObj.getAbsolutePath() + "", e); } finally { try { if (bis != null) bis.close(); } catch (IOException e) { e.printStackTrace(); } try { if (bos != null) bos.close(); } catch (IOException e) { e.printStackTrace(); } } } fileObj.delete(); } catch (Exception e) { log.error("?" + fileObj.getAbsolutePath() + "", e); } } private Map<String, Object> getAllColTitleAndValueMap(List<String> lstColNames, List lstDataColValues) { Map<String, Object> mDataColValues = null; mDataColValues = new HashMap<String, Object>(); for (int k = 0; k < lstColNames.size(); k++) { mDataColValues.put(lstColNames.get(k), lstDataColValues.get(k)); } return mDataColValues; } private void updateDBRowData(PreparedStatement pstmt, AbsDatabaseType dbtype, List lstColsInFile, List<IDataType> lstColType, boolean matchFileIndex, List lstDataColValuesPerRow, Map<String, Object> mDataColValues) throws SQLException { Object objDataVal, objCol; IDataType varcharTypeObj = Config.getInstance().getDataTypeByClass(VarcharType.class); if (this.batchupdatesize == 1) this.lstErrorColValuesTrace = lstDataColValuesPerRow; for (int i = 0; i < lstColsInFile.size(); i++) { objCol = lstColsInFile.get(i); objDataVal = null; if (Tools.isDefineKey("request", String.valueOf(objCol))) { if (request == null) { log.warn("??request?" + objCol + "?requestnull??"); } else { objDataVal = WabacusAssistant.getInstance().getValueFromRequest(request, String.valueOf(objCol)); } } else if (Tools.isDefineKey("session", String.valueOf(objCol))) { if (session == null) { log.warn("??session?" + objCol + "?sessionnull??"); } else { objDataVal = WabacusAssistant.getInstance().getValueFromSession(session, String.valueOf(objCol)); } } else if (matchFileIndex) { if ((Integer) objCol < lstDataColValuesPerRow.size()) objDataVal = lstDataColValuesPerRow.get((Integer) objCol); } else { objDataVal = mDataColValues.get(objCol); } if (lstColType.get(i) instanceof AbsDateTimeType) { if (objDataVal instanceof Date) { ((AbsDateTimeType) lstColType.get(i)).setPreparedStatementValue(i + 1, (Date) objDataVal, pstmt); } else {//? varcharTypeObj.setPreparedStatementValue(i + 1, String.valueOf(objDataVal), pstmt, dbtype); } } else { lstColType.get(i).setPreparedStatementValue(i + 1, (String) objDataVal, pstmt, dbtype); } } pstmt.addBatch(); } }