JCO3.0从SAP获取数据写到本地数据库

/**
	 * 从R3获取数据写到WMS数据库 这里只限制输入参数只能为一个内表的字段，
	 * 
	 * @param functionTemplateName
	 *            函数名
	 * @param jcoCode
	 * @param tableNames
	 *            内表参数名字数组
	 * @param culumns
	 *            要获取的内表参数的字段数组
	 * @param Connection
	 * @return
	 */
	public Map<String, String> importDateFromR3ToWms(
			String functionTemplateName, String jcoCode, String[] tableNames,
			String[][] culumns, String[][] fieldInputs, String tableInput,
			String[][] tableInputParams,String[][] otherInputParams) {
		Connection con = this.getConnection();
		Map<String, String> result = new HashMap<String, String>();
		JCoDestination destination;
		JCoRepository repository = null;
		try {
			destination = JCoDestinationManager
					.getDestination(WMSABAP_AS_POOLED);
		} catch (Exception e) {
			logger.error("Get sap connection pool error:can't find "
					+ WMSABAP_AS_POOLED + ".jcoDestination file .");
			logger.error(e.toString());
			result.put("STATUS", "F");
			result.put("ERRORTP", "1");
			result.put("ERROR", e.toString());
			return result;
		}
		try {
			repository = destination.getRepository();
		} catch (Exception e) {
			logger.error("Get sap Repository error!");
			logger.error(e.toString());
			result.put("STATUS", "F");
			result.put("ERRORTP", "2");
			result.put("ERROR", e.toString());
			return result;
		}
		JCoFunction function;
		try {
			function = repository.getFunction(functionTemplateName);
		} catch (Exception e) {
			logger.error(functionTemplateName + " not found in SAP.");
			logger.error(e.toString());
			result.put("STATUS", "F");
			result.put("ERRORTP", "3");
			result.put("ERROR", e.toString());
			return result;
		}
		int inputSize = 0;
		int fieldInputSize = 0;
		int tableInputParamsSize = 0;

		if (fieldInputs != null && fieldInputs.length != 0) {
			fieldInputSize=fieldInputs.length;
			inputSize = inputSize + fieldInputs.length;
			JCoParameterList input = function.getImportParameterList();
			for (int i = 0; i < fieldInputs.length; i++) {
				input.setValue(fieldInputs[i][0], fieldInputs[i][1]);
			}
		}
		if (tableInput != null && tableInputParams != null
				&& tableInputParams.length > 0) {
			tableInputParamsSize=tableInputParams.length;
			inputSize = inputSize + tableInputParams.length;
			JCoParameterList inputResultParams = function
					.getTableParameterList();
			JCoTable inputTable = inputResultParams.getTable(tableInput);
			inputTable.appendRow();
			for (int i = 0; i < tableInputParams.length; i++) {
				inputTable.setValue(tableInputParams[i][0],
						tableInputParams[i][1]);
			}
		}
		if (otherInputParams != null && otherInputParams.length != 0) {
			inputSize = inputSize + otherInputParams.length;
		}
		try {
			logger.debug("开始从R3查询获取数据......");
			function.execute(destination);
			logger.debug("数据获取完毕......");
		} catch (Exception e) {
			logger.error("execute " + functionTemplateName + " error.");
			logger.error(e.toString());
			result.put("STATUS", "F");
			result.put("ERRORTP", "4");
			result.put("ERROR", e.toString());
			return result;
		}
		JCoParameterList resultParams = function.getTableParameterList();
		JCoTable[] statList = new JCoTable[tableNames.length];
		int[] statTotal = new int[tableNames.length];// 记录从SAP获取的表的记录数
		int[] statlistRows = new int[tableNames.length];// 记录从SAP获取的表的记录数
		for (int i = 0; i < statList.length; i++) {
			statList[i] = resultParams.getTable(tableNames[i]);
			statlistRows[i] = statList[i].getNumRows();
			logger.debug("statlistRows=" + statlistRows);
		}
		int jcoCodetRank = geJcoCodetRank(con, jcoCode);
		String[] sql = new String[tableNames.length];
		String[] inputValues = null;
		if (inputSize > 0) {
			inputValues = new String[inputSize];
			for (int i = 0; i < inputSize; i++) {
				logger.debug("i is "+i);
				logger.debug("inputSize is "+inputSize);
				logger.debug("fieldInputSize is "+fieldInputSize);
				logger.debug("fieldInputSize+tableInputParamsSize is "+fieldInputSize+tableInputParamsSize);
				if (i < fieldInputSize) {
					inputValues[i] = fieldInputs[i][1];
				} else if(i < fieldInputSize+tableInputParamsSize){
					inputValues[i] = tableInputParams[i - fieldInputSize][1];
				}else {
					inputValues[i] = otherInputParams[i -fieldInputSize-tableInputParamsSize][1];
				}
			}
		}
		for (int i = 0; i < sql.length; i++) {
			sql[i] = this.makeJcoSql(jcoCode, jcoCodetRank, tableNames[i],
					TABLE_JCO_DATE_RECEIVE, culumns[i].length, inputValues);
			logger.debug("sql["+i+"] is " + sql[i]);
		}
		int[] batchNums = new int[tableNames.length];
		PreparedStatement[] pss = new PreparedStatement[tableNames.length];
		boolean commit;
		try {
			commit = con.getAutoCommit();
			for (int i = 0; i < tableNames.length; i++) {
				con.setAutoCommit(false);
				pss[i] = con.prepareStatement(sql[i]);
				batchNums[i] = pss[i].getFetchSize();
				pss[i].setFetchSize(SQLFETCCHSIZE);
			}
		} catch (SQLException e1) {
			logger.error("DB Error:", e1);
			result.put("STATUS", "F");
			result.put("ERRORTP", "5");
			result.put("ERROR", e1.toString());
			return result;
		}
		for (int i = 0; i < tableNames.length; i++) {
			logger.debug("开始向WMS插入数据数据......");
			if (statlistRows[i] > 0) {
				do {
					statTotal[i]++;
					try {
						this.excutesetSqlPs(pss[i], culumns[i], statList[i]);
						pss[i].addBatch();
						if (statTotal[i] % batchNums[i] == 0) {// 执行物理批量插入
							pss[i].executeBatch();
							// con.commit();
						}
					} catch (SQLException e) {
						logger.error("insert error:", e);
						result.put("STATUS", "F");
						result.put("ERRORTP", "5");
						result.put("ERROR", e.toString());
						return result;
					}
				} while (statList[i].nextRow());
			}
		}
		try {
			logger.debug("tableNames.length is " + tableNames.length);
			logger.debug("pss.length is " + pss.length);
			for (int i = 0; i < tableNames.length; i++) {
				logger.debug("i is " + i);
				pss[i].executeBatch();
			}
			con.commit();
			logger.debug("完成向WMS插入数据数据......");
			con.setAutoCommit(commit);
		} catch (SQLException e) {
			logger.debug(e.toString());
			result.put("STATUS", "F");
			result.put("ERRORTP", "5");
			result.put("ERROR", e.toString());
		} finally {
			for (int i = 0; i < tableNames.length; i++) {
				DbUtils.closeQuietly(pss[i]);
			}
			DbUtils.closeQuietly(con);
		}
		String totalResult = "";
		for (int i = 1; i <= tableNames.length; i++) {
			if (i == tableNames.length) {
				totalResult = totalResult + statTotal[i - 1];
			} else {
				totalResult = statTotal[i - 1] + ",";
			}
		}
		result.put("STATUS", "S");
		result.put("ERRORTP", "0");
		result.put("TOTATL", totalResult);
		return result;
	}