List of usage examples for java.sql PreparedStatement addBatch
void addBatch() throws SQLException;
PreparedStatement object's batch of commands. From source file:edu.pitt.apollo.db.ApolloDbUtils.java
public void insertDiseaseStateTimeSeriesNegative(PreparedStatement pstmt, int runId, int popId, String disease_state, List<Double> ts) throws SQLException, ClassNotFoundException { logger.info("In insertDiseaseStateTimeSeries, runId={}, popId={}, disease_state={}", runId, popId, disease_state);//ww w .ja v a 2s.co m // logger.info("Time Series is:"); try { int counter = 0; for (int i = -ts.size(); i < 0; i++) { // logger.debug("Time Series[{}] is: {}", i, ts.get(i)); pstmt.setInt(1, runId); pstmt.setInt(2, popId); pstmt.setInt(3, i); pstmt.setDouble(4, ts.get(counter)); pstmt.addBatch(); counter++; } } catch (SQLException e) { logger.error("Error inserting disease state time series for runId={}, popId={}, disease_state={}", runId, popId, disease_state); throw new SQLException("Error inserting disease state time series for internal run id: " + runId + ", disease state: " + disease_state + ". Specific error was:\n" + e.getMessage()); } }
From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java
private void insertDeletedEventLinks(AccessToken editor, Connection con, List<Attendee> attendeetoRemove, Event ev) throws SQLException { String q = "INSERT INTO DeletedEventLink ( deletedeventlink_userobm_id, deletedeventlink_event_id, deletedeventlink_event_ext_id ) VALUES ( ?, ?, ? ) "; PreparedStatement ps = null; logger.info("Event Modification : will insert {} deleted event links.", attendeetoRemove.size()); List<Integer> attendeeUserIds = filterOutContactsAttendees(editor, con, attendeetoRemove); try {//from ww w .j a v a2s. c o m ps = con.prepareStatement(q); for (Integer id : attendeeUserIds) { ps.setInt(1, id); ps.setInt(2, ev.getObmId().getObmId()); ps.setString(3, ev.getExtId().getExtId()); ps.addBatch(); } ps.executeBatch(); } finally { obmHelper.cleanup(null, ps, null); } }
From source file:org.obm.domain.dao.CalendarDaoJdbcImpl.java
private List<Event> insertEventExceptions(AccessToken editor, String calendar, Set<Event> eventException, Connection con, EventObmId id) throws SQLException, FindException, ServerFault { List<Event> newEvExcepts = new LinkedList<Event>(); Event created = null;//w w w .j av a 2s. com Map<EventObmId, Date> eventsEx = new HashMap<EventObmId, Date>(); for (Event evExcept : eventException) { created = createEvent(con, editor, calendar, evExcept); newEvExcepts.add(created); eventsEx.put(created.getObmId(), evExcept.getRecurrenceId()); } PreparedStatement ps = null; try { ps = con.prepareStatement("insert into EventException " + "(eventexception_parent_id, eventexception_child_id, eventexception_date, eventexception_usercreate) " + "values (?, ?, ?, " + editor.getObmId() + ")"); for (Entry<EventObmId, Date> entry : eventsEx.entrySet()) { ps.setInt(1, id.getObmId()); ps.setInt(2, entry.getKey().getObmId()); ps.setTimestamp(3, new Timestamp(entry.getValue().getTime())); ps.addBatch(); } ps.executeBatch(); } finally { obmHelper.cleanup(null, ps, null); } return newEvExcepts; }
From source file:com.strider.datadefender.DatabaseAnonymizer.java
/** * Anonymizes a row of columns.//from w w w. java2s .c o m * * Sets query parameters on the passed updateStmt - this includes the key * values - and calls anonymization functions for the columns. * * @param updateStmt * @param tableColumns * @param keyNames * @param db * @param row * @param columnMetaData * @throws SQLException * @throws NoSuchMethodException * @throws SecurityException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException */ private void anonymizeRow(final PreparedStatement updateStmt, final Collection<Column> tableColumns, final Collection<String> keyNames, final Connection db, final ResultSet row, final List<MatchMetaData> columnMetaData, final String vendor) throws SQLException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, DatabaseAnonymizerException { int fieldIndex = 0; final Map<String, Integer> columnIndexes = new HashMap<>(tableColumns.size()); final Set<String> anonymized = new HashSet<>(tableColumns.size()); for (final Column column : tableColumns) { final String columnName = column.getName(); if (anonymized.contains(columnName)) { continue; } if (!columnIndexes.containsKey(columnName)) { final int columnIndex = ++fieldIndex; columnIndexes.put(columnName, columnIndex); } if (isExcludedColumn(row, column)) { final String columnValue = row.getString(columnName); updateStmt.setString(columnIndexes.get(columnName), columnValue); log.debug("Excluding column: " + columnName + " with value: " + columnValue); continue; } anonymized.add(columnName); final Object colValue = callAnonymizingFunctionFor(db, row, column, vendor); log.debug("colValue = " + colValue); log.debug("type= " + (colValue != null ? colValue.getClass() : "null")); if (colValue == null) { updateStmt.setNull(columnIndexes.get(columnName), Types.NULL); } else if (colValue.getClass() == java.sql.Date.class) { updateStmt.setDate(columnIndexes.get(columnName), CommonUtils.stringToDate(colValue.toString(), "dd-MM-yyyy")); } else if (colValue.getClass() == java.lang.Integer.class) { updateStmt.setInt(columnIndexes.get(columnName), (int) colValue); } else { updateStmt.setString(columnIndexes.get(columnName), getTruncatedColumnValue((String) colValue, columnIndexes.get(columnName), columnMetaData)); } } int whereIndex = fieldIndex; for (final String key : keyNames) { updateStmt.setString(++whereIndex, row.getString(key)); } updateStmt.addBatch(); }
From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java
private List<Event> insertEventExceptions(AccessToken editor, String calendar, Set<Event> eventException, Connection con, EventObmId id, Boolean useObmUser) throws SQLException, FindException, ServerFault { List<Event> newEvExcepts = new LinkedList<Event>(); Event created = null;//from www. j a va 2s . c om Map<EventObmId, Date> eventsEx = new HashMap<EventObmId, Date>(); for (Event evExcept : eventException) { created = createEvent(con, editor, calendar, evExcept, useObmUser); newEvExcepts.add(created); eventsEx.put(created.getObmId(), evExcept.getRecurrenceId()); } PreparedStatement ps = null; try { ps = con.prepareStatement("insert into EventException " + "(eventexception_parent_id, eventexception_child_id, eventexception_date, eventexception_usercreate) " + "values (?, ?, ?, " + editor.getObmId() + ")"); for (Entry<EventObmId, Date> entry : eventsEx.entrySet()) { ps.setInt(1, id.getObmId()); ps.setInt(2, entry.getKey().getObmId()); ps.setTimestamp(3, new Timestamp(entry.getValue().getTime())); ps.addBatch(); } ps.executeBatch(); } finally { obmHelper.cleanup(null, ps, null); } return newEvExcepts; }
From source file:org.seasar.dbflute.logic.replaceschema.loaddata.impl.DfXlsDataHandlerImpl.java
protected boolean doWriteDataRow(DfXlsDataResource resource, File file, DfDataTable dataTable, DfDataRow dataRow, Map<String, DfColumnMeta> columnMetaMap, List<String> columnNameList, Connection conn, PreparedStatement ps, LoggingInsertType loggingInsertType, boolean suppressBatchUpdate) throws SQLException { final String tableDbName = dataTable.getTableDbName(); // ColumnValue and ColumnObject final ColumnContainer columnContainer = createColumnContainer(dataTable, dataRow); final Map<String, Object> columnValueMap; {/*from w w w . ja v a 2 s .c om*/ final Map<String, Object> plainMap = columnContainer.getColumnValueMap(); if (plainMap.isEmpty()) { throwXlsDataColumnDefFailureException(file, dataTable); } if (isColumnValueAllNull(plainMap)) { // against Excel Devil return false; } columnValueMap = convertColumnValue(tableDbName, resource.getDataDirectory(), plainMap, columnMetaMap); } final int rowNumber = dataRow.getRowNumber(); handleLoggingInsert(tableDbName, columnNameList, columnValueMap, loggingInsertType, rowNumber); int bindCount = 1; final Set<Entry<String, Object>> entrySet = columnValueMap.entrySet(); for (Entry<String, Object> entry : entrySet) { final String columnName = entry.getKey(); final Object obj = entry.getValue(); // - - - - - - - - - - - - - - - - - - - // Process Null (against Null Headache) // - - - - - - - - - - - - - - - - - - - if (processNull(tableDbName, columnName, obj, ps, bindCount, columnMetaMap)) { bindCount++; continue; } // - - - - - - - - - - - - - - - // Process NotNull and NotString // - - - - - - - - - - - - - - - // If the value is not null and the value has the own type except string, // It registers the value to statement by the type. if (processNotNullNotString(tableDbName, columnName, obj, conn, ps, bindCount, columnMetaMap)) { bindCount++; continue; } // - - - - - - - - - - - - - - - - - - - // Process NotNull and StringExpression // - - - - - - - - - - - - - - - - - - - final String value = (String) obj; processNotNullString(file, tableDbName, columnName, value, conn, ps, bindCount, columnMetaMap); bindCount++; } if (suppressBatchUpdate) { ps.execute(); } else { ps.addBatch(); } return true; }
From source file:edu.iu.incntre.flowscalestatcollector.StatCollector.java
public void startUp() { logger.trace("Startup of StatCollector"); try {//from w w w.ja v a 2 s. com if (isQuery) { // initiate sqlite database try { Class.forName(databaseClass); conn = DriverManager.getConnection(databaseDriver, dbUsername, dbPassword); } catch (ClassNotFoundException e2) { logger.error("{}", e2); } catch (SQLException e1) { // TODO Auto-generated catch block logger.error("{}", e1); } // end initiate database // start up thread statThread = new Thread(new Runnable() { @Override public void run() { try { logger.trace("Starting Thread .."); logger.trace("Getting flows from switch every {} seconds", intervalTime); List<OFStatistics> portStats; List<OFStatistics> flowStats; List<OFPhysicalPort> portStatus; SwitchDevice swd = null; String[] datapathIdStringElements = datapathIdStrings.split(","); try { while (statThread != null) { calendar = Calendar.getInstance(); logger.trace("getting flows from switches"); //check if conn is null if it is, reset connection if (conn == null) { conn = DriverManager.getConnection(databaseDriver, dbUsername, dbPassword); } for (String datapathIdString : datapathIdStringElements) { try { swd = flowscaleController.getSwitchDevices() .get(HexString.toLong(datapathIdString)); if (swd == null) { logger.info("switch {} does not exist, is it connected?", datapathIdString); continue; } logger.info("Getting flows from switch {} with ID {}", swd.getSwitchName(), datapathIdString); try { portStats = flowscaleController .getSwitchStatisticsFromInterface(datapathIdString, "port"); flowStats = flowscaleController .getSwitchStatisticsFromInterface(datapathIdString, "flow"); portStatus = swd.getPortStates(); if (flowStats != null && portStats != null) { String flowStatsJSON = JSONConverter.toStat(flowStats, "flow") .toJSONString(); String portStatsJSON = JSONConverter.toStat(portStats, "port") .toJSONString(); String portStatusJSON = JSONConverter.toPortStatus(portStatus) .toJSONString(); // initialize or set hashmaps HashMap<Long, Long> tempPortStatTransmitted; HashMap<Long, Long> tempPortStatReceived; HashMap<String, Long> tempFlowStat; long datapathId = HexString.toLong(datapathIdString); if (tempPortStatTransmittedHashMap.get(datapathId) == null) { tempPortStatTransmitted = new HashMap<Long, Long>(); tempPortStatTransmittedHashMap.put(datapathId, tempPortStatTransmitted); } else { tempPortStatTransmitted = tempPortStatTransmittedHashMap .get(datapathId); } if (tempPortStatReceivedHashMap.get(datapathId) == null) { tempPortStatReceived = new HashMap<Long, Long>(); tempPortStatReceivedHashMap.put(datapathId, tempPortStatReceived); } else { tempPortStatReceived = tempPortStatReceivedHashMap .get(datapathId); } if (tempFlowStatHashMap.get(datapathId) == null) { tempFlowStat = new HashMap<String, Long>(); tempFlowStatHashMap.put(datapathId, tempFlowStat); } else { tempFlowStat = tempFlowStatHashMap.get(datapathId); } storeSwitchDetails(HexString.toLong(datapathIdString), portStatsJSON, flowStatsJSON, portStatusJSON, tempPortStatTransmitted, tempPortStatReceived, tempFlowStat); } else { logger.error( "Switch {} returned a null result possibility because the switch is not connected to the controller", datapathIdString); } } catch (NoSwitchException e1) { // TODO Auto-generated catch block logger.error("Switch {} with ID {} is not connected aborting", swd.getSwitchName(), datapathIdString); } catch (IOException e1) { logger.error("IOException {}", e1); } catch (InterruptedException e1) { logger.error("Thread Interrupted {}", e1); killThread(); } catch (ExecutionException e1) { logger.error("Execution Exception {}", e1); } catch (TimeoutException e1) { logger.error("Switch Timeout Exception {}", e1); killThread(); } } catch (Exception e) { logger.error("unchecked exception here {}", e); killThread(); shutDown(); Thread.yield(); } } try { Thread.sleep(intervalTime); } catch (InterruptedException e) { logger.error("{}", e); break; } } } catch (Exception e) { logger.error("exception in while {}", e); shutDown(); } try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } catch (Exception generalException) { logger.error("General Exception throws {} ", generalException); } } /** * insert details into database, 3 tables will be populated: flow_stats, port_stats ,and port_status * * @param datapathId * @param portStats * @param flowStats * @param portStatus * @param tempPortStatTransmitted * @param tempPortStatReceived * @param tempFlowStat */ private void storeSwitchDetails(long datapathId, String portStats, String flowStats, String portStatus, HashMap<Long, Long> tempPortStatTransmitted, HashMap<Long, Long> tempPortStatReceived, HashMap<String, Long> tempFlowStat) { Object obj = JSONValue.parse(portStats); JSONArray jsonArray = (JSONArray) obj; for (int i = 0; i < jsonArray.size(); i++) { JSONObject jsonObject = (JSONObject) jsonArray.get(i); long transmittedPackets = (Long) jsonObject.get("transmit_packets"); long receivedPackets = (Long) jsonObject.get("receive_packets"); long portId = (Long) jsonObject.get("port_id"); // logger.info("the port is {}", portId); // logger.info("{} packets transmitted and {} packets received", // receivedPackets,transmittedPackets); PreparedStatement prep = null; try { prep = null; if (conn != null) { prep = conn.prepareStatement("insert into port_stats values (?,?,?,?,?);"); } else { logger.error("no connection object instantiated aborting .. "); return; } prep.setLong(1, datapathId); prep.setLong(2, calendar.getTimeInMillis()); if (tempPortStatTransmitted.get(portId) != null) { long currentTransmittedPackets = transmittedPackets - tempPortStatTransmitted.get(portId); if (currentTransmittedPackets < 0) { prep.setLong(5, transmittedPackets); } else { prep.setLong(5, currentTransmittedPackets); } } else { prep.setLong(5, transmittedPackets); } tempPortStatTransmitted.put(portId, transmittedPackets); // take care of port received if (tempPortStatReceived.get(portId) != null) { long currentReceivedPackets = receivedPackets - tempPortStatReceived.get(portId); if (currentReceivedPackets < 0) { prep.setLong(4, receivedPackets); } else { prep.setLong(4, currentReceivedPackets); } } else { prep.setLong(4, receivedPackets); } tempPortStatReceived.put(portId, receivedPackets); prep.setLong(3, portId); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); } catch (SQLRecoverableException sqlRecoverableException) { logger.error("{}", sqlRecoverableException); //exit function since there is a timeout return; } catch (SQLException e) { logger.error("{}", e); } finally { if (prep != null) { try { prep.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } } } Object flowJSONobj = JSONValue.parse(flowStats); JSONArray flowJsonArray = (JSONArray) flowJSONobj; for (int i = 0; i < flowJsonArray.size(); i++) { JSONObject jsonObject = (JSONObject) flowJsonArray.get(i); long packets = (Long) jsonObject.get("packet_count"); String matchString = (String) jsonObject.get("match"); String action = (String) jsonObject.get("actions"); long priority = (Long) jsonObject.get("priority"); PreparedStatement prep = null; try { prep = conn.prepareStatement("insert into flow_stats values (?,?,?,?,?,?);"); String insertString = datapathId + "," + calendar.getTimeInMillis() + "," + matchString + "," + action; logger.debug("flow_stat values to insert are {}", insertString); prep.setLong(1, datapathId); prep.setLong(2, calendar.getTimeInMillis()); if (tempFlowStat.get(matchString) != null) { long packetsReceived = packets - tempFlowStat.get(matchString); if (packetsReceived < 0) { prep.setLong(5, packets); } else { prep.setLong(5, packetsReceived); } } else { prep.setLong(5, packets); } tempFlowStat.put(matchString, packets); prep.setString(3, matchString); prep.setString(4, action); prep.setShort(6, (short) priority); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("error when insert flow {} in switch {}", matchString, datapathId); logger.error("{}", e); } finally { if (prep != null) { try { prep.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } } } Object portStatusJSONobj = JSONValue.parse(portStatus); JSONArray portStatusJsonArray = (JSONArray) portStatusJSONobj; for (int i = 0; i < portStatusJsonArray.size(); i++) { byte portStatusValue = 0; JSONObject jsonObject = (JSONObject) portStatusJsonArray.get(i); long portId = (Long) jsonObject.get("port_id"); String portAddress = (String) jsonObject.get("port_address"); try { portStatusValue = (byte) (Integer.parseInt(jsonObject.get("state").toString()) % 2); } catch (NumberFormatException nfe) { logger.error("{}", nfe); continue; } PreparedStatement prep = null; try { prep = conn.prepareStatement("insert into port_status values (?,?,?,?,?);"); prep.setLong(1, datapathId); prep.setLong(2, calendar.getTimeInMillis()); prep.setLong(3, portId); prep.setString(4, portAddress); prep.setByte(5, portStatusValue); prep.addBatch(); conn.setAutoCommit(false); prep.executeBatch(); conn.setAutoCommit(true); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } finally { if (prep != null) { try { prep.close(); } catch (SQLException e) { // TODO Auto-generated catch block logger.error("{}", e); } } } } } }, "Switch Stat Collector"); statThread.start(); } } catch (Exception e) { logger.error("general excecption thrown {}", e); } }
From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java
/** * Method for adding runtime property, which belongs to a version of an application, to the database. * * @param dbConnection database connecton * @param runtimeProperties list of runtime properties * @param versionHashId version hash id * @param tenantId tenant id/*from ww w . j a va 2 s . co m*/ * @throws AppCloudException */ public void addRunTimeProperties(Connection dbConnection, List<RuntimeProperty> runtimeProperties, String versionHashId, int tenantId) throws AppCloudException { PreparedStatement preparedStatement = null; try { preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_RUNTIME_PROPERTY); for (RuntimeProperty runtimeProperty : runtimeProperties) { preparedStatement.setString(1, runtimeProperty.getPropertyName()); preparedStatement.setString(2, runtimeProperty.getPropertyValue()); preparedStatement.setString(3, versionHashId); preparedStatement.setString(4, runtimeProperty.getDescription()); preparedStatement.setInt(5, tenantId); preparedStatement.setBoolean(6, runtimeProperty.isSecured()); preparedStatement.addBatch(); } preparedStatement.executeBatch(); } catch (SQLException e) { String msg = "Error occurred while adding property to the database for version id : " + versionHashId + " in tenant : " + tenantId; throw new AppCloudException(msg, e); } finally { DBUtil.closePreparedStatement(preparedStatement); } }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialDAO.java
/** * add by: ChuDV//from w w w .j ava2s .c o m * * @param ordersDTO yeu cau xuat kho * @param lstGoodsSerialInforDTO - danh sach serial tai len * @param connection * @return * @since 17/12/15 9:30 AM * @Desc: Lay danh sach hang hoa theo serial don le */ public List<GoodsSerialInforDTO> getGoodsBySerial(OrdersDTO ordersDTO, List<GoodsSerialInforDTO> lstGoodsSerialInforDTO, Connection connection) { List<GoodsSerialInforDTO> lstGoodsReturn = new ArrayList(); String message = ParamUtils.SUCCESS; String GOODS_IN_STOCK = "1"; StringBuilder sqlInsertSerialTmp = new StringBuilder(); StringBuilder sqlGetGoodsSerial = new StringBuilder(); StringBuilder sqlGoodsNotEnough = new StringBuilder(); StringBuilder sqlSerialDuplicate = new StringBuilder(); StringBuilder sqlUpdateSerialWrong = new StringBuilder(); PreparedStatement preparedStatement; ResultSet resultSet; GoodsSerialInforDTO serialInforDTO; List<String> lstDupplicateSerial = new ArrayList<>(); try { //Insert serial vao bang tam sqlInsertSerialTmp.append(" INSERT INTO SERIAL_TMP (cust_id,owner_id,owner_type,serial) "); sqlInsertSerialTmp.append(" VALUES (?,?,?,?)"); preparedStatement = connection.prepareStatement(sqlInsertSerialTmp.toString()); for (GoodsSerialInforDTO goodsSerialInforDTO : lstGoodsSerialInforDTO) { preparedStatement.setString(1, ordersDTO.getCustId()); preparedStatement.setString(2, ordersDTO.getOrderStockId()); preparedStatement.setString(3, ParamUtils.OWNER_TYPE.STOCK); preparedStatement.setString(4, goodsSerialInforDTO.getFromSerial()); //SET PARAMS FOR STOCK_TRANS_SERIAL preparedStatement.addBatch(); } //END FOR //Thuc thi batch preparedStatement.executeBatch(); //ChuDV add 25/12/2015 --Cap nhat serial bo ky tu dau sqlUpdateSerialWrong.append(" UPDATE serial_tmp "); sqlUpdateSerialWrong.append(" SET serial = SUBSTR (serial, 2) "); sqlUpdateSerialWrong.append(" WHERE serial IN ( "); sqlUpdateSerialWrong.append(" SELECT serial "); sqlUpdateSerialWrong.append(" FROM serial_tmp "); sqlUpdateSerialWrong.append(" MINUS "); sqlUpdateSerialWrong.append(" SELECT serial "); sqlUpdateSerialWrong.append(" FROM stock_goods_serial sgs "); sqlUpdateSerialWrong.append(" WHERE sgs.cust_id = ? "); sqlUpdateSerialWrong.append(" AND sgs.owner_id = ? "); sqlUpdateSerialWrong.append(" AND sgs.owner_type = ? "); sqlUpdateSerialWrong.append(" AND sgs.status = ? ) "); sqlUpdateSerialWrong.append(" AND (serial like 'S%' OR serial like 's%') "); preparedStatement = connection.prepareCall(sqlUpdateSerialWrong.toString()); preparedStatement.setString(1, ordersDTO.getCustId()); preparedStatement.setString(2, ordersDTO.getOrderStockId()); preparedStatement.setString(3, ParamUtils.OWNER_TYPE.STOCK); preparedStatement.setString(4, GOODS_IN_STOCK); preparedStatement.executeUpdate(); //Kiem tra hang thieu sqlGoodsNotEnough.append(" SELECT serial "); sqlGoodsNotEnough.append(" FROM serial_tmp "); sqlGoodsNotEnough.append(" MINUS "); sqlGoodsNotEnough.append(" SELECT serial "); sqlGoodsNotEnough.append(" FROM stock_goods_serial sgs "); sqlGoodsNotEnough.append(" WHERE sgs.cust_id = ? "); sqlGoodsNotEnough.append(" AND sgs.owner_id = ? "); sqlGoodsNotEnough.append(" AND sgs.owner_type = ? "); sqlGoodsNotEnough.append(" AND sgs.status = ? "); preparedStatement = connection.prepareCall(sqlGoodsNotEnough.toString()); preparedStatement.setString(1, ordersDTO.getCustId()); preparedStatement.setString(2, ordersDTO.getOrderStockId()); preparedStatement.setString(3, ParamUtils.OWNER_TYPE.STOCK); preparedStatement.setString(4, GOODS_IN_STOCK); //Lay ket qua query resultSet = preparedStatement.executeQuery(); // GoodsSerialInforDTO serialInforDTO; while (resultSet.next()) { serialInforDTO = new GoodsSerialInforDTO(); serialInforDTO.setFromSerial(resultSet.getString("serial")); serialInforDTO.setToSerial(resultSet.getString("serial")); serialInforDTO.setNotes("SERIAL_MISSING"); // serialInforDTO.setCustId(ordersDTO.getCustId()); // serialInforDTO.setOwnerId(ordersDTO.getOrderStockId()); lstGoodsReturn.add(serialInforDTO); } //Kiem tra trung sqlSerialDuplicate.append(" SELECT st.serial"); sqlSerialDuplicate.append(" FROM goods g, serial_tmp st, stock_goods_serial sgs "); sqlSerialDuplicate.append(" WHERE g.goods_id = sgs.goods_id "); sqlSerialDuplicate.append(" AND st.cust_id = sgs.cust_id "); sqlSerialDuplicate.append(" AND st.owner_id = sgs.owner_id "); sqlSerialDuplicate.append(" AND st.owner_type = sgs.owner_type "); sqlSerialDuplicate.append(" AND st.serial = sgs.serial "); sqlSerialDuplicate.append(" AND sgs.cust_id = ? "); sqlSerialDuplicate.append(" AND sgs.owner_id = ? "); sqlSerialDuplicate.append(" AND sgs.owner_type = ? "); sqlSerialDuplicate.append(" AND sgs.status = ? "); sqlSerialDuplicate.append(" GROUP BY st.serial HAVING COUNT ( * ) > 1 "); preparedStatement = connection.prepareCall(sqlSerialDuplicate.toString()); preparedStatement.setString(1, ordersDTO.getCustId()); preparedStatement.setString(2, ordersDTO.getOrderStockId()); preparedStatement.setString(3, ParamUtils.OWNER_TYPE.STOCK); preparedStatement.setString(4, GOODS_IN_STOCK); //Lay ket qua query resultSet = preparedStatement.executeQuery(); // GoodsSerialInforDTO serialInforDTO; while (resultSet.next()) { serialInforDTO = new GoodsSerialInforDTO(); serialInforDTO.setFromSerial(resultSet.getString("serial")); serialInforDTO.setToSerial(resultSet.getString("serial")); serialInforDTO.setNotes("SERIAL_DUPPLICATE"); lstDupplicateSerial.add(serialInforDTO.getFromSerial()); // serialInforDTO.setCustId(ordersDTO.getCustId()); // serialInforDTO.setOwnerId(ordersDTO.getOrderStockId()); lstGoodsReturn.add(serialInforDTO); } //Lay danh sach hang hoa sqlGetGoodsSerial.append(" SELECT g.goods_id goodsId, "); sqlGetGoodsSerial.append(" g.code goodsCode, "); sqlGetGoodsSerial.append(" g.name goodsName, "); sqlGetGoodsSerial.append(" st.serial serial, "); sqlGetGoodsSerial.append(" sgs.goods_state goodsState, "); sqlGetGoodsSerial.append(" sgs.cell_code cellCode "); sqlGetGoodsSerial.append(" FROM goods g, serial_tmp st, stock_goods_serial sgs "); sqlGetGoodsSerial.append(" WHERE g.goods_id = sgs.goods_id "); sqlGetGoodsSerial.append(" AND st.cust_id = sgs.cust_id "); sqlGetGoodsSerial.append(" AND st.owner_id = sgs.owner_id "); sqlGetGoodsSerial.append(" AND st.owner_type = sgs.owner_type "); sqlGetGoodsSerial.append(" AND st.serial = sgs.serial "); sqlGetGoodsSerial.append(" AND sgs.cust_id = ? "); sqlGetGoodsSerial.append(" AND sgs.owner_id = ? "); sqlGetGoodsSerial.append(" AND sgs.owner_type = ? "); sqlGetGoodsSerial.append(" AND sgs.status = ? "); sqlGetGoodsSerial.append(" ORDER BY goodsCode, goodsState, serial "); preparedStatement = connection.prepareCall(sqlGetGoodsSerial.toString()); preparedStatement.setString(1, ordersDTO.getCustId()); preparedStatement.setString(2, ordersDTO.getOrderStockId()); preparedStatement.setString(3, ParamUtils.OWNER_TYPE.STOCK); preparedStatement.setString(4, GOODS_IN_STOCK); //Lay ket qua query resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { serialInforDTO = new GoodsSerialInforDTO(); serialInforDTO.setGoodsId(resultSet.getString("goodsId")); serialInforDTO.setGoodsCode(resultSet.getString("goodsCode")); serialInforDTO.setGoodsName(resultSet.getString("goodsName")); serialInforDTO.setGoodsState(resultSet.getString("goodsState")); serialInforDTO.setCellCode(resultSet.getString("cellCode")); serialInforDTO.setFromSerial(resultSet.getString("serial")); serialInforDTO.setToSerial(resultSet.getString("serial")); serialInforDTO.setQuantity("1"); if (lstDupplicateSerial.contains(serialInforDTO.getFromSerial())) { serialInforDTO.setNotes("SERIAL_DUPPLICATE"); } // serialInforDTO.setCustId(ordersDTO.getCustId()); // serialInforDTO.setOwnerId(ordersDTO.getOrderStockId()); lstGoodsReturn.add(serialInforDTO); } preparedStatement.close(); } catch (Exception e) { message = ParamUtils.FAIL; lstGoodsReturn = new ArrayList<>(); e.printStackTrace(); } return lstGoodsReturn; }
From source file:org.wso2.carbon.apimgt.migration.client.MigrateFrom19to110.java
private boolean updateAMAppKeyDomainMapping(Connection connection) throws SQLException { log.info("Updating consumer keys in AM_APP_KEY_DOMAIN_MAPPING"); Statement selectStatement = null; Statement deleteStatement = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null;//from ww w . jav a 2 s. c o m boolean continueUpdatingDB = true; long totalRecords = 0; long decryptionFailedRecords = 0; try { ArrayList<KeyDomainMappingTableDTO> keyDomainMappingTableDTOs = new ArrayList<>(); String query = "SELECT * FROM AM_APP_KEY_DOMAIN_MAPPING"; selectStatement = connection.createStatement(); selectStatement.setFetchSize(50); resultSet = selectStatement.executeQuery(query); while (resultSet.next()) { ConsumerKeyDTO consumerKeyDTO = new ConsumerKeyDTO(); consumerKeyDTO.setEncryptedConsumerKey(resultSet.getString("CONSUMER_KEY")); totalRecords++; if (ResourceModifier.decryptConsumerKeyIfEncrypted(consumerKeyDTO)) { KeyDomainMappingTableDTO keyDomainMappingTableDTO = new KeyDomainMappingTableDTO(); keyDomainMappingTableDTO.setConsumerKey(consumerKeyDTO); keyDomainMappingTableDTO.setAuthzDomain(resultSet.getString("AUTHZ_DOMAIN")); keyDomainMappingTableDTOs.add(keyDomainMappingTableDTO); } else { log.error("Cannot decrypt consumer key : " + consumerKeyDTO.getEncryptedConsumerKey() + " in AM_APP_KEY_DOMAIN_MAPPING table"); decryptionFailedRecords++; //If its not allowed to remove decryption failed entries from DB, we will not continue updating // tables even with successfully decrypted entries to maintain DB integrity if (!removeDecryptionFailedKeysFromDB) { continueUpdatingDB = false; } } } if (continueUpdatingDB) { // Modify table only if decryption is successful preparedStatement = connection.prepareStatement( "INSERT INTO AM_APP_KEY_DOMAIN_MAPPING " + "(CONSUMER_KEY, AUTHZ_DOMAIN) VALUES (?, ?)"); for (KeyDomainMappingTableDTO keyDomainMappingTableDTO : keyDomainMappingTableDTOs) { preparedStatement.setString(1, keyDomainMappingTableDTO.getConsumerKey().getDecryptedConsumerKey()); preparedStatement.setString(2, keyDomainMappingTableDTO.getAuthzDomain()); preparedStatement.addBatch(); } deleteStatement = connection.createStatement(); deleteStatement.execute("DELETE FROM AM_APP_KEY_DOMAIN_MAPPING"); preparedStatement.executeBatch(); log.info("AM_APP_KEY_DOMAIN_MAPPING table updated with " + decryptionFailedRecords + "/" + totalRecords + " of the CONSUMER_KEY entries deleted as they cannot be decrypted"); } else { log.error("AM_APP_KEY_DOMAIN_MAPPING table not updated as " + decryptionFailedRecords + "/" + totalRecords + " of the CONSUMER_KEY entries" + " cannot be decrypted"); } } finally { if (selectStatement != null) selectStatement.close(); if (deleteStatement != null) deleteStatement.close(); if (preparedStatement != null) preparedStatement.close(); if (resultSet != null) resultSet.close(); } return continueUpdatingDB; }