Example usage for java.sql PreparedStatement executeBatch

List of usage examples for java.sql PreparedStatement executeBatch

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. executeBatch

Introduction

In this page you can find the example usage for java.sql PreparedStatement executeBatch.

Prototype

int[] executeBatch() throws SQLException;

Source Link

Document

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Usage

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   w w w  .  j  a v  a 2s . com*/
        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.forgerock.openidm.repo.jdbc.impl.GenericTableHandler.java

/**
 * Internal recursive function to add/write properties.
 * If batching is enabled, prepared statements are added to the batch and only executed if they hit the max limit.
 * After completion returns the number of properties that have only been added to the batch but not yet executed.
 * The caller is responsible for executing the batch on remaining items when it deems the batch complete.
 *
 * If batching is not enabled, prepared statements are immediately executed.
 *
 * @param fullId the full URI of the resource the belongs to
 * @param dbId the generated identifier to link the properties table with the main table (foreign key)
 * @param localId the local identifier of the resource these properties belong to
 * @param value the JSON value with the properties to write
 * @param connection the DB connection/*from w  ww .  j  a v a2 s  . c  o  m*/
 * @param propCreateStatement the prepared properties insert statement
 * @param batchingCount the current number of statements that have been batched and not yet executed on the prepared statement
 * @return status of the current batchingCount, i.e. how many statements are not yet executed in the PreparedStatement
 * @throws SQLException if the insert failed
 */
private int writeValueProperties(String fullId, long dbId, String localId, JsonValue value,
        Connection connection, PreparedStatement propCreateStatement, int batchingCount) throws SQLException {

    for (JsonValue entry : value) {
        JsonPointer propPointer = entry.getPointer();
        if (cfg.isSearchable(propPointer)) {
            String propkey = propPointer.toString();
            if (entry.isMap() || entry.isList()) {
                batchingCount = writeValueProperties(fullId, dbId, localId, entry, connection,
                        propCreateStatement, batchingCount);
            } else {
                String propvalue = null;
                Object val = entry.getObject();
                if (val != null) {
                    propvalue = StringUtils.left(val.toString(), getSearchableLength());
                }
                String proptype = null;
                if (propvalue != null) {
                    proptype = entry.getObject().getClass().getName(); // TODO: proper type info
                }
                if (logger.isTraceEnabled()) {
                    logger.trace("Populating statement {} with params {}, {}, {}, {}, {}",
                            queryMap.get(QueryDefinition.PROPCREATEQUERYSTR), dbId, localId, propkey, proptype,
                            propvalue);
                }
                propCreateStatement.setLong(1, dbId);
                propCreateStatement.setString(2, propkey);
                propCreateStatement.setString(3, proptype);
                propCreateStatement.setString(4, propvalue);
                logger.debug("Executing: {}", propCreateStatement);
                if (enableBatching) {
                    propCreateStatement.addBatch();
                    batchingCount++;
                } else {
                    int numUpdate = propCreateStatement.executeUpdate();
                }
                if (logger.isTraceEnabled()) {
                    logger.trace("Inserting objectproperty id: {} propkey: {} proptype: {}, propvalue: {}",
                            fullId, propkey, proptype, propvalue);
                }
            }
            if (enableBatching && batchingCount >= maxBatchSize) {
                int[] numUpdates = propCreateStatement.executeBatch();
                if (logger.isDebugEnabled()) {
                    logger.debug("Batch limit reached, update of objectproperties updated: {}",
                            Arrays.asList(numUpdates));
                }
                propCreateStatement.clearBatch();
                batchingCount = 0;
            }
        }
    }

    return batchingCount;
}

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;/*from   w w  w .  j a  v  a2 s  .  c om*/
    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: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 w w w. ja v a  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.wso2.carbon.dataservices.core.description.query.SQLQuery.java

private Object processPreNormalQuery(InternalParamCollection params, int queryLevel) throws DataServiceFault {
    PreparedStatement stmt = null;
    ResultSet rs = null;//from   w ww  .j a va2 s .com
    boolean isError = false;
    try {
        Connection conn = this.createConnection(queryLevel);
        stmt = this.createProcessedPreparedStatement(SQLQuery.DS_QUERY_TYPE_NORMAL, params, conn);
        /* check if this is a batch request */
        if (this.isJDBCFirstBatchRequest()) {
            this.setBatchPreparedStatement(stmt);
            /* add this to cleanup this query after batch request */
            BatchDataServiceRequest.addParticipant(this);
        }
        /* if updating/inserting stuff, go inside here */
        if (!this.hasResult()
                || (this.hasResult() && (this.isReturnGeneratedKeys() || this.isReturnUpdatedRowCount()))) {
            /* if we are in the middle of a batch request, don't execute it */
            if (this.isJDBCBatchRequest()) {
                /* if this is the last one, execute the full batch */
                if (this.isJDBCLastBatchRequest()) {
                    stmt.executeBatch();
                }
            } else {
                /* normal update operation */
                stmt.executeUpdate();
            }
        } else {
            rs = stmt.executeQuery();
        }
        return new QueryResultInfo(stmt, rs);
    } catch (Throwable e) {
        isError = true;
        throw new DataServiceFault(e, FaultCodes.DATABASE_ERROR,
                "Error in 'SQLQuery.processPreNormalQuery': " + e.getMessage());
    } finally {
        if (isError) {
            this.releaseResources(rs, this.isStatementClosable(isError) ? stmt : null);
        }
    }
}

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  w  ww .  j a  v  a  2s .com
 * @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:edu.iu.incntre.flowscalestatcollector.StatCollector.java

public void startUp() {

    logger.trace("Startup of StatCollector");
    try {//  w w w .j  a  v  a2  s.  c  om
        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.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java

/**
 * @param applicationId//  ww  w  . j  a va2s  . c  o  m
 * @param authenticators
 * @param connection
 * @throws IdentityApplicationManagementException
 */
private void updateRequestPathAuthenticators(int applicationId, RequestPathAuthenticatorConfig[] authenticators,
        Connection connection) throws IdentityApplicationManagementException {

    int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId();
    PreparedStatement storeReqPathAuthenticators = null;

    try {
        storeReqPathAuthenticators = connection
                .prepareStatement(ApplicationMgtDBQueries.STORE_REQ_PATH_AUTHENTICATORS);
        if (authenticators != null && authenticators.length > 0) {
            for (RequestPathAuthenticatorConfig auth : authenticators) {
                // TENANT_ID, AUTHENTICATOR_NAME, APP_ID
                storeReqPathAuthenticators.setInt(1, tenantID);
                storeReqPathAuthenticators.setString(2, CharacterEncoder.getSafeText(auth.getName()));
                storeReqPathAuthenticators.setInt(3, applicationId);
                storeReqPathAuthenticators.addBatch();
            }
            storeReqPathAuthenticators.executeBatch();
        }
    } catch (SQLException e) {
        throw new IdentityApplicationManagementException("Error while retrieving all application");
    } finally {
        IdentityApplicationManagementUtil.closeStatement(storeReqPathAuthenticators);
    }
}

From source file:com.viettel.logistic.wms.dao.StockGoodsSerialDAO.java

/**
 * add by: ChuDV//w w w .ja  va  2s .co  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:com.viettel.logistic.wms.dao.StockGoodsSerialDAO.java

public List<GoodsSerialInforDTO> getGoodsBySerialInventory(OrdersDTO ordersDTO,
        List<GoodsSerialInforDTO> lstGoodsSerialInforDTO, Connection connection) {
    List<GoodsSerialInforDTO> lstGoodsReturn = new ArrayList();
    String message = ParamUtils.SUCCESS;
    String GOODS_IN_STOCK = "1,2";
    StringBuilder sqlInsertSerialTmp = new StringBuilder();
    StringBuilder sqlGetGoodsSerial = new StringBuilder();
    StringBuilder sqlGoodsNotEnough = new StringBuilder();
    StringBuilder sqlSerialDuplicate = new StringBuilder();
    StringBuilder sqlUpdateSerialWrong = new StringBuilder();
    PreparedStatement preparedStatement;
    ResultSet resultSet;/*  w w w  .ja v a 2  s.com*/
    GoodsSerialInforDTO serialInforDTO;
    List<String> lstDupplicateSerial = new ArrayList<>();
    try {
        //Insert serial vao bang tam
        sqlInsertSerialTmp.append(" INSERT INTO SERIAL_INVENTORY_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().toUpperCase());
            //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_inventory_tmp ");
        sqlUpdateSerialWrong.append(" SET   serial = SUBSTR (serial, 2) ");
        sqlUpdateSerialWrong.append(" WHERE   serial IN ( ");
        sqlUpdateSerialWrong.append(" SELECT   UPPER(serial) ");
        sqlUpdateSerialWrong.append("   FROM   serial_inventory_tmp ");
        sqlUpdateSerialWrong.append(" MINUS ");
        sqlUpdateSerialWrong.append(" SELECT   UPPER(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 IN (1,2) ) ");
        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.executeUpdate();

        //Kiem tra hang thieu       
        sqlGoodsNotEnough.append(" SELECT   serial");
        sqlGoodsNotEnough.append("   FROM   serial_inventory_tmp ");
        sqlGoodsNotEnough.append(" MINUS ");
        sqlGoodsNotEnough.append(" SELECT   UPPER(serial) 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 IN (1,2) ");
        preparedStatement = connection.prepareCall(sqlGoodsNotEnough.toString());
        preparedStatement.setString(1, ordersDTO.getCustId());
        preparedStatement.setString(2, ordersDTO.getOrderStockId());
        preparedStatement.setString(3, ParamUtils.OWNER_TYPE.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_inventory_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 UPPER(st.serial) = UPPER(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 IN (1,2) ");
        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);
        //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("          g.unit_type unitType, ");
        sqlGetGoodsSerial.append("          sgs.barcode barcode, ");
        sqlGetGoodsSerial.append("          st.serial serial, ");
        sqlGetGoodsSerial.append("          sgs.goods_state goodsState, ");
        sqlGetGoodsSerial.append("          sgs.cell_code cellCode ");
        sqlGetGoodsSerial.append("   FROM   goods g, serial_inventory_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 UPPER(st.serial) = UPPER(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 IN (1,2) ");
        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);
        //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.setBarcode(resultSet.getString("barcode"));
            serialInforDTO.setFromSerial(resultSet.getString("serial"));
            serialInforDTO.setToSerial(resultSet.getString("serial"));
            serialInforDTO.setUnitType(resultSet.getString("unitType"));
            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;
}