Example usage for java.sql Statement RETURN_GENERATED_KEYS

List of usage examples for java.sql Statement RETURN_GENERATED_KEYS

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. RETURN_GENERATED_KEYS

Introduction

In this page you can find the example usage for java.sql Statement RETURN_GENERATED_KEYS.

Prototype

int RETURN_GENERATED_KEYS

To view the source code for java.sql Statement RETURN_GENERATED_KEYS.

Click Source Link

Document

The constant indicating that generated keys should be made available for retrieval.

Usage

From source file:org.efaps.db.databases.MySQLDatabase.java

/**
 * Fetches next number for sequence <code>_name</code> by inserting new
 * row into representing table. The new auto generated key is returned as
 * next number of the sequence.//w  w  w .j  a va 2 s .c o m
 *
 * @param _con      SQL connection
 * @param _name     name of the sequence
 * @return current inserted value of the table
 * @throws SQLException if next number from the sequence could not be
 *                      fetched
 * @see #PREFIX_SEQUENCE
 */
@Override
public long nextSequence(final Connection _con, final String _name) throws SQLException {
    final long ret;
    final Statement stmt = _con.createStatement();
    try {
        // insert new line
        final String insertCmd = new StringBuilder().append("INSERT INTO `")
                .append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase()).append("` VALUES ()")
                .toString();
        final int row = stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS);
        if (row != 1) {
            throw new SQLException("no sequence found for '" + _name + "'");
        }

        // fetch new number
        final ResultSet resultset = stmt.getGeneratedKeys();
        if (resultset.next()) {
            ret = resultset.getLong(1);
        } else {
            throw new SQLException("no sequence found for '" + _name + "'");
        }
    } finally {
        stmt.close();
    }
    return ret;
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void update(final Form24 form24) {
    logger.info("update");
    String user = "";
    try {/*from   w w  w.  j a v a  2s  . c o  m*/
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;
    final Timestamp currentDate = new Timestamp(System.currentTimeMillis());

    String sqlform24 = "UPDATE `form24` SET `factory_id`=?,`update_date`=?,`update_by`=? WHERE `form24_id`=?";
    logger.info("update: " + sqlform24);
    this.jdbcTemplate.update(sqlform24,
            new Object[] { form24.getFactory().getFactoryId(), currentDate, userName, form24.getForm24Id() });

    //ID PRODUCT

    String productSql = "UPDATE `form24_product` SET `seq`=?,`productName`=?,`size`=?,`bandColor`=?,`backgroudColor`=?,`licenseNo`=?"
            + ",`grossnumber200`=?,`grossnumber400`=?,`corkScrap`=?,`totalScrap`=?,`update_date`=?,`update_by`=? "
            + "WHERE `product_id`=?";

    final StringBuilder psql = new StringBuilder();
    psql.append(
            "INSERT INTO `form24_product`(`form24_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
            .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

    List<Product> products = form24.getProductList();
    if (products != null) {
        for (final Product p : products) {
            if (BeanUtils.isEmpty(p.getProcuctId())) {
                logger.info("create");
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(psql.toString(),
                                Statement.RETURN_GENERATED_KEYS);
                        long returnidform24 = form24.getForm24Id();
                        ps.setLong(1, returnidform24);
                        ps.setString(2, p.getSeq());
                        ps.setString(3, p.getProductName());
                        ps.setString(4, p.getSize());
                        ps.setString(5, p.getBandColor());
                        ps.setString(6, p.getBackgroudColor());
                        ps.setString(7, p.getLicenseNo());
                        ps.setBigDecimal(8, p.getGrossnumber200());
                        ps.setBigDecimal(9, p.getGrossnumber400());
                        ps.setBigDecimal(10, p.getCorkScrap());
                        ps.setBigDecimal(11, p.getTotalScrap());

                        ps.setTimestamp(12, currentDate);
                        ps.setString(13, userName);
                        ps.setTimestamp(14, currentDate);
                        ps.setString(15, userName);
                        return ps;
                    }

                }, keyHolder);

                long returnidproduct = keyHolder.getKey().longValue();
                p.setProcuctId(returnidproduct);
                logger.info("returnidproduct : " + returnidproduct);
            } else {
                logger.info("update");
                this.jdbcTemplate.update(productSql,
                        new Object[] { p.getSeq(), p.getProductName(), p.getSize(), p.getBandColor(),
                                p.getBackgroudColor(), p.getLicenseNo(), p.getGrossnumber200(),
                                p.getGrossnumber400(), p.getCorkScrap(), p.getTotalScrap(), currentDate,
                                userName, p.getProcuctId() });
            }
        }
    }

}

From source file:com.act.lcms.db.model.MS1ScanForWellAndMassCharge.java

public MS1ScanForWellAndMassCharge insert(DB db, MS1ScanForWellAndMassCharge ms1Result)
        throws SQLException, IOException {

    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(
            MS1ScanForWellAndMassCharge.getInstance().getInsertQuery(), Statement.RETURN_GENERATED_KEYS)) {

        bindInsertOrUpdateParameters(stmt, ms1Result.getPlateId(), ms1Result.getPlateRow(),
                ms1Result.getPlateColumn(), ms1Result.getUseSNR(), ms1Result.getScanFilePath(),
                ms1Result.getChemicalName(), ms1Result.getMetlinIons(), ms1Result.getIonsToSpectra(),
                ms1Result.getIonsToIntegral(), ms1Result.getIonsToMax(), ms1Result.getIonsToAvgSignal(),
                ms1Result.getIonsToAvgAmbient(), ms1Result.getIonsToLogSNR(),
                ms1Result.getIndividualMaxIntensities(), ms1Result.getMaxYAxis());

        stmt.executeUpdate();//from  w ww  .j  a v a  2s.  c o m
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
            if (resultSet.next()) {
                // Get auto-generated id.
                int id = resultSet.getInt(1);
                ms1Result.setId(id);
                return ms1Result;
            } else {
                System.err.format("ERROR: could not retrieve autogenerated key for ms1 scan result\n");
                return null;
            }
        }
    }
}

From source file:opengovcrawler.DB.java

/**
 * Insert comments into DB Also, it inserts username and initialId into
 * comment_opengov table//  w w w .  jav  a  2  s. c  om
 *
 * @param articleDbId - The id of the article that the comments refer to
 * @param comments - The arrayList of comment
 * @throws java.sql.SQLException
 */
public static void InsertComments(ArrayList<Comment> comments, int articleDbId) throws SQLException {
    String insertCommentSql = "INSERT INTO comments (url_source, article_id, comment, date_added, revision, depth, source_type_id)"
            + "VALUES (?,?,?,?,?,?,?)";
    PreparedStatement prepInsertComStatement = connection.prepareStatement(insertCommentSql,
            Statement.RETURN_GENERATED_KEYS);
    Statement stmnt = null;
    for (Comment currentComment : comments) {
        String selectCommentSql = "SELECT * FROM comments WHERE url_source = ? AND article_id = ?";
        PreparedStatement prepSelectComStatement = connection.prepareStatement(selectCommentSql);
        prepSelectComStatement.setString(1, currentComment.permalink);
        prepSelectComStatement.setInt(2, articleDbId);
        ResultSet rs = prepSelectComStatement.executeQuery();
        int insertedCommentKeyId = -1;
        if (rs.next()) {
            String comText = rs.getString("comment");
            if (currentComment.contentHash != comText.hashCode()) {
                // Then comment has been changed so
                // we insert it as in the DB as well, as revision-2
                Timestamp comTimestamp = null;
                try {
                    comTimestamp = ConvertDateMonth(currentComment.timestamp);
                } catch (ParseException ex) {
                    ex.printStackTrace();
                }
                int curCommentRevision = rs.getInt("revision");
                curCommentRevision++;
                prepInsertComStatement.setString(1, currentComment.permalink);
                prepInsertComStatement.setInt(2, articleDbId);
                prepInsertComStatement.setString(3, currentComment.content);
                prepInsertComStatement.setTimestamp(4, comTimestamp);
                prepInsertComStatement.setInt(5, curCommentRevision);
                prepInsertComStatement.setInt(6, currentComment.depth);
                prepInsertComStatement.setInt(7, 2);
                prepInsertComStatement.executeUpdate();
                ResultSet rsq = prepInsertComStatement.getGeneratedKeys();
                if (rsq.next()) {
                    insertedCommentKeyId = rsq.getInt(1);
                }
                //                    prepInsertComStatement.addBatch();
                ConsultationThreadedCrawling.newComments++;
                String insertIntoCommentOpengov = "INSERT INTO comment_opengov"
                        + "(opengovid, fullname, id, link_url) " + "VALUES" + "(" + currentComment.initialId
                        + ",'" + currentComment.author + "'," + insertedCommentKeyId + ", '"
                        + currentComment.link_url + "')";
                stmnt = connection.createStatement();
                stmnt.executeUpdate(insertIntoCommentOpengov);
                stmnt.close();
            }
        } else {
            Timestamp comTimestamp = null;
            try {
                comTimestamp = ConvertDateMonth(currentComment.timestamp);
            } catch (ParseException ex) {
                ex.printStackTrace();
            }
            prepInsertComStatement.setString(1, currentComment.permalink);
            prepInsertComStatement.setInt(2, articleDbId);
            prepInsertComStatement.setString(3, currentComment.content);
            prepInsertComStatement.setTimestamp(4, comTimestamp);
            prepInsertComStatement.setInt(5, 1);
            prepInsertComStatement.setInt(6, currentComment.depth);
            prepInsertComStatement.setInt(7, 2);
            prepInsertComStatement.executeUpdate();
            ResultSet rsq = prepInsertComStatement.getGeneratedKeys();
            if (rsq.next()) {
                insertedCommentKeyId = rsq.getInt(1);
            }
            //                prepInsertComStatement.addBatch();
            ConsultationThreadedCrawling.newComments++;
            // Keep track of the opengov users
            String insertIntoCommentOpengov = "INSERT INTO comment_opengov"
                    + "(opengovid, fullname, id, link_url) " + "VALUES" + "(" + currentComment.initialId + ",'"
                    + currentComment.author + "'," + insertedCommentKeyId + ", '" + currentComment.link_url
                    + "')";
            stmnt = connection.createStatement();
            stmnt.executeUpdate(insertIntoCommentOpengov);
            stmnt.close();
        }
        prepSelectComStatement.close();
    }
    //        prepInsertComStatement.executeBatch();
    prepInsertComStatement.close();
}

From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java

/**
 * Method for adding container.//from   w  ww  .j a  v  a2s .  c o m
 *
 * @param dbConnection database connection
 * @param container    container object
 * @param deploymentId id of deployment
 * @param tenantId     id of tenant
 * @throws AppCloudException
 */
public void addContainer(Connection dbConnection, Container container, int deploymentId, int tenantId)
        throws AppCloudException {

    PreparedStatement preparedStatement = null;

    try {
        int containerId = -1;
        preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_CONTAINER,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, container.getImageName());
        preparedStatement.setString(2, container.getImageVersion());
        preparedStatement.setInt(3, deploymentId);
        preparedStatement.setInt(4, tenantId);

        preparedStatement.execute();

        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()) {
            containerId = resultSet.getInt(1);
        }
        for (ContainerServiceProxy containerServiceProxy : container.getServiceProxies()) {
            addContainerServiceProxy(dbConnection, containerServiceProxy, containerId, tenantId);
        }

    } catch (SQLException e) {
        String msg = "Error while inserting deployment container record in tenant : " + tenantId;
        throw new AppCloudException(msg, e);
    } finally {
        DBUtil.closePreparedStatement(preparedStatement);
    }
}

From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java

/**
 * {@inheritDoc}/*from   w ww  .  j  a  v a2  s  .  c  om*/
 */
@Override
public void createLink(MLink link, Connection conn) {
    PreparedStatement stmt = null;
    int result;
    try {
        stmt = conn.prepareStatement(crudQueries.getStmtInsertLink(), Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, link.getName());
        stmt.setLong(2, link.getConnectorId());
        stmt.setBoolean(3, link.getEnabled());
        stmt.setString(4, link.getCreationUser());
        stmt.setTimestamp(5, new Timestamp(link.getCreationDate().getTime()));
        stmt.setString(6, link.getLastUpdateUser());
        stmt.setTimestamp(7, new Timestamp(link.getLastUpdateDate().getTime()));

        result = stmt.executeUpdate();
        if (result != 1) {
            throw new SqoopException(CommonRepositoryError.COMMON_0009, Integer.toString(result));
        }

        ResultSet rsetConnectionId = stmt.getGeneratedKeys();

        if (!rsetConnectionId.next()) {
            throw new SqoopException(CommonRepositoryError.COMMON_0010);
        }

        long connectionId = rsetConnectionId.getLong(1);

        createInputValues(crudQueries.getStmtInsertLinkInput(), connectionId,
                link.getConnectorLinkConfig().getConfigs(), conn);
        link.setPersistenceId(connectionId);

    } catch (SQLException ex) {
        logException(ex, link);
        throw new SqoopException(CommonRepositoryError.COMMON_0016, ex);
    } finally {
        closeStatements(stmt);
    }
}

From source file:com.iana.boesc.dao.BOESCDaoImpl.java

@Override
public boolean getPopulatedDataAndInsert(EDI322Bean eb, String boescUserId, String userType, File file,
        List<String> finalErrorList, Map<Integer, Object> fileTransStatus) throws Exception {
    logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId
            + " userType ::" + userType);
    String status = "";
    logger.info("----- ISA  ............");
    String gsHeader = "";
    String gsControl = "";
    String st_control = "";
    String n7Header = "";
    String q5Header = "";
    String equip_prefix = "";
    String equip_number = "";
    String w2Header = "";
    String r4Header = "";
    String r13Header = "";
    String n9Header = "";
    String eventType = "";
    String port_qual = "";
    String iana_splc = "";

    QueryRunner qrun = new QueryRunner(getDataSource());
    logger.info("----- GE  ............");
    for (int i = 0; i < eb.getListGSDetails().size(); i++) {
        gsHeader = eb.getListGSDetails().get(i).getHeaderDetails();
        gsControl = eb.getListGSDetails().get(i).getGroupControlNumber();
        logger.info("gsControl ::" + gsControl + " gsHeader ::" + gsHeader);
        int startIndex = i + 1;
        logger.info("----- ST & SE  ............");
        for (int a = 0; a < eb.getSTDetailsMap().get(startIndex).size(); a++) {

            Connection conn = getConnection();
            conn.setAutoCommit(false);/*from  ww w.  ja v a2 s  .  c  o  m*/
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            StringBuilder sbQuery = new StringBuilder(
                    "INSERT INTO BOESC_TRAN_SET (ISA_HEADER, GS_HEADER, INPUT_TYPE, SENDER_ID, SENDER_TYPE, ");
            sbQuery.append(
                    " ISA_DATETIME, GS_CONTROL, ST_CONTROL, EVENT_TYPE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, IEP_DOT, PORT_QUAL, IANA_SPLC, ");
            sbQuery.append(" POOL_ID, POOL_NAME, Q5_SEG, N7_SEG, W2_SEG, R4_SEG, N9_SEG, R13_SEG, ");

            if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) {
                sbQuery.append(" RECEIVER_ID, REC_STATUS   ");
            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) {
                sbQuery.append(" MRV_ID, MRV_STATUS   ");
            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) {
                sbQuery.append(" FO_ID, FO_STATUS ");

            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_SU)) {

            }

            try {
                status = "";
                int changedIndex = a + 1; //very important Variable
                if (fileTransStatus != null && fileTransStatus.size() > 0) {
                    logger.info("-------------------- changedIndex ::" + changedIndex
                            + " fileTransStatus.get(startIndex)  ::" + fileTransStatus.get(changedIndex));
                    if (fileTransStatus.get(changedIndex) == null) {
                        status = GlobalVariables.STATUS_PENDING;
                    } else {
                        status = GlobalVariables.STATUS_REJECTED;
                    }
                } else {
                    status = GlobalVariables.STATUS_PENDING;
                }

                r13Header = "";
                r4Header = "";
                n9Header = "";
                port_qual = "";
                iana_splc = "";
                GIERInfoDetails gierInfo = null;

                st_control = eb.getSTDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber();
                logger.info("  st_control :" + st_control);

                /*String transactionControlNumberSE  = eb.getSEDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber();
                logger.info("  transactionControlNumberSE :"+transactionControlNumberSE );*/

                logger.info("----- N7  ............");

                for (int q = 0; q < eb.getN7DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    n7Header = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    logger.info("n7Header ::" + n7Header);
                    equip_prefix = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getEquipmentInitial();
                    equip_number = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getEquipmentNumber();

                    logger.info("equip_prefix ::" + equip_prefix);
                    logger.info("equip_number ::" + equip_number);
                    equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? ""
                            : equip_prefix;
                    equip_number = equip_number == null || equip_number.trim().length() == 0 ? ""
                            : equip_number;
                    gierInfo = getDVIRAdditionaldetails(equip_prefix, equip_number);
                    //logger.info("gierInfo ::"+gierInfo);

                }

                logger.info("----- Q5  ............");
                for (int q = 0; q < eb.getQ5DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    q5Header = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    eventType = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getStatusCode();
                    logger.info("q5Header ::" + q5Header + " eventType ::" + eventType);
                }

                logger.info("----- W2  ............");
                for (int q = 0; q < eb.getW2DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    w2Header = eb.getW2DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    logger.info("w2Header ::" + w2Header);
                }

                logger.info("----- R4  ............");
                String tempR4Header = "";
                String tempPort_qual = "";
                String tempIana_splc = "";
                for (int q = 0; q < eb.getR4DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempR4Header = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    tempPort_qual = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getLocationQualifier();
                    tempIana_splc = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getLocationIdentifier();
                    r4Header = r4Header + GlobalVariables.FIELD_SEPARATOR + tempR4Header;
                    port_qual = port_qual + GlobalVariables.FIELD_SEPARATOR + tempPort_qual;
                    iana_splc = iana_splc + GlobalVariables.FIELD_SEPARATOR + tempIana_splc;
                    logger.info("r4Header ::" + r4Header + " port_qual:: " + port_qual + " iana_splc ::"
                            + iana_splc);

                }
                r4Header = r4Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r4Header.substring(1)
                        : r4Header;
                port_qual = port_qual.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? port_qual.substring(1)
                        : port_qual;
                iana_splc = iana_splc.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? iana_splc.substring(1)
                        : iana_splc;

                logger.info("----- R13  ............");
                String tempR13Header = "";
                for (int q = 0; q < eb.getR13DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempR13Header = eb.getR13DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    r13Header = r13Header + GlobalVariables.FIELD_SEPARATOR + tempR13Header;
                    logger.info("r13Header ::" + r13Header);
                }
                r13Header = r13Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? r13Header.substring(1)
                        : r13Header;

                logger.info("----- N9  ............");
                String tempN9Header = "";
                for (int q = 0; q < eb.getN9DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempN9Header = eb.getN9DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    n9Header = n9Header + GlobalVariables.FIELD_SEPARATOR + tempN9Header;
                    logger.info("n9Header ::" + n9Header);
                }
                n9Header = n9Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? n9Header.substring(1)
                        : n9Header;

                sbQuery.append(
                        " , CREATED_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

                if (gierInfo == null) {
                    gierInfo = new GIERInfoDetails(); //this situation happen when all segment are missing except : ISA,SE,ST,GE,GS,IEA
                }

                equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix;
                equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number;

                pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, eb.getISADetails().getHeaderDetails());
                pstmt.setString(2, gsHeader);
                pstmt.setString(3, GlobalVariables.INPUT_TYPE_BOESC_322);
                pstmt.setString(4, eb.getISADetails().getInterchangeSenderId());
                pstmt.setString(5, userType);
                pstmt.setString(6, eb.getISADetails().getInterchangeDate());
                pstmt.setString(7, gsControl);
                pstmt.setString(8, st_control);
                pstmt.setString(9, eventType);
                pstmt.setString(10, equip_prefix);
                pstmt.setString(11, equip_number);
                pstmt.setString(12, equip_prefix + equip_number);
                pstmt.setString(13, gierInfo.getCompanySCACCode() == null ? "" : gierInfo.getCompanySCACCode());
                pstmt.setString(14, gierInfo.getUsDotNumber() == null ? "" : gierInfo.getUsDotNumber());
                pstmt.setString(15, port_qual);
                pstmt.setString(16, iana_splc);
                pstmt.setString(17, gierInfo.getChassisPoolId() == null ? "" : gierInfo.getChassisPoolId());
                pstmt.setString(18, gierInfo.getChassisPoolName() == null ? "" : gierInfo.getChassisPoolName());
                pstmt.setString(19, q5Header);
                pstmt.setString(20, n7Header);
                pstmt.setString(21, w2Header);
                pstmt.setString(22, r4Header);
                pstmt.setString(23, n9Header);
                pstmt.setString(24, r13Header);
                pstmt.setString(25, boescUserId);
                pstmt.setString(26, status);
                pstmt.setObject(27, DateTimeFormater.getSqlSysTimestamp());

                logger.info("query :: " + sbQuery.toString());
                int dbStat = 0;
                int boescKey = 0;
                dbStat = pstmt.executeUpdate();
                rs = pstmt.getGeneratedKeys();
                if (dbStat != 0) {
                    if (rs != null) {
                        while (rs.next()) {
                            boescKey = rs.getInt(1);
                            logger.info("boescKey: " + boescKey);
                        }
                    }

                    conn.commit();
                } else {
                    conn.rollback();
                }
                if (boescKey != 0) {
                    //Update BOESC_UNIQUE_NO : using business logic
                    String sql = "UPDATE BOESC_TRAN_SET SET BOESC_UNIQUE_NO = ? WHERE BOESC_TRAN_ID = ? ";
                    qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(boescKey, "BOESC-"), boescKey });
                    logger.info("Record Inserted successfully for BOESC..." + file.getName());
                    return true;
                } else {
                    logger.error("Failure Data insertion in BOESC..");
                }
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (pstmt != null) {
                        pstmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException ex1) {
                    logger.error("Caught SQL exception while closing prepared statement /resultset "
                            + ex1.getMessage());
                    ex1.printStackTrace();
                    throw ex1;
                } catch (Exception e) {
                    logger.error("Caught SQL exception in finally block " + e.getMessage());
                    e.printStackTrace();
                    throw e;
                }
            }
        }
    }

    return false;
}

From source file:org.apache.nifi.admin.dao.impl.StandardUserDAO.java

@Override
public NiFiUser createUser(NiFiUser user) throws DataAccessException {
    if (user.getIdentity() == null) {
        throw new IllegalArgumentException("User identity must be specified.");
    }//from www  . j a  va 2  s  .  c  o  m

    // ensure the user identity is not too lengthy
    if (user.getIdentity().length() > 4096) {
        throw new IllegalArgumentException("User identity must be less than 4096 characters.");
    }

    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        final String id = UUID.nameUUIDFromBytes(user.getIdentity().getBytes(StandardCharsets.UTF_8))
                .toString();

        // create a statement
        statement = connection.prepareStatement(INSERT_USER, Statement.RETURN_GENERATED_KEYS);
        statement.setString(1, id);
        statement.setString(2, StringUtils.left(user.getIdentity(), 4096));
        statement.setString(3, StringUtils.left(user.getUserName(), 4096));
        statement.setString(4, StringUtils.left(user.getUserGroup(), 100));
        if (user.getLastVerified() != null) {
            statement.setTimestamp(5, new java.sql.Timestamp(user.getLastVerified().getTime()));
        } else {
            statement.setTimestamp(5, null);
        }
        statement.setString(6, StringUtils.left(user.getJustification(), 500));
        statement.setString(7, user.getStatus().toString());

        // insert the user
        int updateCount = statement.executeUpdate();
        if (updateCount == 1) {
            user.setId(id);
        } else {
            throw new DataAccessException("Unable to insert user.");
        }

        return user;
    } catch (SQLException sqle) {
        throw new DataAccessException(sqle);
    } catch (DataAccessException dae) {
        throw dae;
    } finally {
        RepositoryUtils.closeQuietly(rs);
        RepositoryUtils.closeQuietly(statement);
    }
}

From source file:module.entities.NameFinder.DB.java

/**
 * Starts the activity log// w  ww  . ja  va 2s.com
 *
 * @param startTime - The start time of the crawling procedure
 * @return - The activity's log id
 * @throws java.sql.SQLException
 */
public static int LogRegexFinder(long startTime) throws SQLException {
    String insertLogSql = "INSERT INTO log.activities (module_id, start_date, end_date, status_id, message) VALUES (?,?,?,?,?)";
    PreparedStatement prepLogCrawlStatement = connection.prepareStatement(insertLogSql,
            Statement.RETURN_GENERATED_KEYS);
    prepLogCrawlStatement.setInt(1, 4);
    prepLogCrawlStatement.setTimestamp(2, new java.sql.Timestamp(startTime));
    prepLogCrawlStatement.setTimestamp(3, null);
    prepLogCrawlStatement.setInt(4, 1);
    prepLogCrawlStatement.setString(5, null);
    prepLogCrawlStatement.executeUpdate();
    ResultSet rsq = prepLogCrawlStatement.getGeneratedKeys();
    int crawlerId = 0;
    if (rsq.next()) {
        crawlerId = rsq.getInt(1);
    }
    prepLogCrawlStatement.close();
    return crawlerId;
}

From source file:uk.ac.cam.cl.dtg.segue.dao.users.PgUsers.java

/**
 * createUser./* w ww. j  ava2 s .  com*/
 * @param userToCreate - a user object to persist
 * @return a register user as just created.
 * @throws SegueDatabaseException
 */
private RegisteredUser createUser(final RegisteredUser userToCreate) throws SegueDatabaseException {
    // make sure student is default role if none set
    if (null == userToCreate.getRole()) {
        userToCreate.setRole(Role.STUDENT);
    }

    // make sure NOT_VERIFIED is default email verification status if none set
    if (null == userToCreate.getEmailVerificationStatus()) {
        userToCreate.setEmailVerificationStatus(EmailVerificationStatus.NOT_VERIFIED);
    }

    PreparedStatement pst;
    try (Connection conn = database.getDatabaseConnection()) {
        pst = conn.prepareStatement(
                "INSERT INTO users(family_name, given_name, email, role, "
                        + "date_of_birth, gender, registration_date, school_id, "
                        + "school_other, last_updated, email_verification_status, "
                        + "last_seen, default_level, password, secure_salt, reset_token, "
                        + "reset_expiry, email_verification_token) "
                        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);
        // TODO: Change this to annotations or something to rely exclusively on the pojo.
        setValueHelper(pst, 1, userToCreate.getFamilyName());
        setValueHelper(pst, 2, userToCreate.getGivenName());
        setValueHelper(pst, 3, userToCreate.getEmail());
        setValueHelper(pst, 4, userToCreate.getRole());
        setValueHelper(pst, 5, userToCreate.getDateOfBirth());
        setValueHelper(pst, 6, userToCreate.getGender());
        setValueHelper(pst, 7, userToCreate.getRegistrationDate());
        setValueHelper(pst, 8, userToCreate.getSchoolId());
        setValueHelper(pst, 9, userToCreate.getSchoolOther());
        setValueHelper(pst, 10, userToCreate.getLastUpdated());
        setValueHelper(pst, 11, userToCreate.getEmailVerificationStatus());
        setValueHelper(pst, 12, userToCreate.getLastSeen());
        setValueHelper(pst, 13, userToCreate.getDefaultLevel());
        setValueHelper(pst, 14, userToCreate.getPassword());
        setValueHelper(pst, 15, userToCreate.getSecureSalt());
        setValueHelper(pst, 16, userToCreate.getResetToken());
        setValueHelper(pst, 17, userToCreate.getResetExpiry());
        setValueHelper(pst, 18, userToCreate.getEmailVerificationToken());

        if (pst.executeUpdate() == 0) {
            throw new SegueDatabaseException("Unable to save user.");
        }

        try (ResultSet generatedKeys = pst.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                Long id = generatedKeys.getLong(1);
                userToCreate.setId(id);
                return userToCreate;
            } else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        }

    } catch (SQLException e) {
        throw new SegueDatabaseException("Postgres exception", e);
    }
}