Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

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

Introduction

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

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.pentaho.di.core.database.Database.java

/**
 * @param ps/*from  w  ww.  jav  a  2s  .c  o  m*/
 *          The prepared insert statement to use
 * @return The generated keys in auto-increment fields
 * @throws KettleDatabaseException
 *           in case something goes wrong retrieving the keys.
 */
public RowMetaAndData getGeneratedKeys(PreparedStatement ps) throws KettleDatabaseException {
    ResultSet keys = null;
    try {
        keys = ps.getGeneratedKeys(); // 1 row of keys
        ResultSetMetaData resultSetMetaData = keys.getMetaData();
        if (resultSetMetaData == null) {
            resultSetMetaData = ps.getMetaData();
        }
        RowMetaInterface rowMeta;
        if (resultSetMetaData == null) {
            rowMeta = new RowMeta();
            rowMeta.addValueMeta(new ValueMeta("ai-key", ValueMetaInterface.TYPE_INTEGER));
        } else {
            rowMeta = getRowInfo(resultSetMetaData, false, false);
        }

        return new RowMetaAndData(rowMeta, getRow(keys, resultSetMetaData, rowMeta));
    } catch (Exception ex) {
        throw new KettleDatabaseException("Unable to retrieve key(s) from auto-increment field(s)", ex);
    } finally {
        if (keys != null) {
            try {
                keys.close();
            } catch (SQLException e) {
                throw new KettleDatabaseException("Unable to close resultset of auto-generated keys", e);
            }
        }
    }
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCResourceDAO.java

public void addResourceWithoutContentId(ResourceImpl resourceImpl, boolean isUpdatingExisting)
        throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet result = null;/*from   www .  j  a va  2s .  c  om*/
    ResourceIDImpl resourceID = resourceImpl.getResourceIDImpl();
    try {
        String sql = "INSERT INTO REG_RESOURCE (REG_PATH_ID, REG_NAME, REG_MEDIA_TYPE, "
                + "REG_CREATOR, REG_CREATED_TIME, REG_LAST_UPDATOR, "
                + "REG_LAST_UPDATED_TIME, REG_DESCRIPTION, " + "REG_TENANT_ID, REG_UUID) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        String sql1 = "SELECT MAX(REG_VERSION) FROM REG_RESOURCE";

        long now = System.currentTimeMillis();

        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_VERSION") });
        } else {
            ps = conn.prepareStatement(sql);
        }
        ps.setInt(1, resourceID.getPathID());
        ps.setString(2, resourceID.getName());
        ps.setString(3, resourceImpl.getMediaType());
        if (isUpdatingExisting) {
            String authorName = resourceImpl.getAuthorUserName();
            if (authorName == null) {
                authorName = CurrentSession.getUser();
                resourceImpl.setAuthorUserName(authorName);
            }
            ps.setString(4, authorName);

            Date createdTime = resourceImpl.getCreatedTime();
            Timestamp createdTimestamp;
            if (createdTime == null) {
                createdTimestamp = new Timestamp(now);
            } else {
                createdTimestamp = new Timestamp(createdTime.getTime());
            }
            ps.setTimestamp(5, createdTimestamp);
        } else {
            ps.setString(4, CurrentSession.getUser());
            resourceImpl.setAuthorUserName(CurrentSession.getUser());
            ps.setTimestamp(5, new Timestamp(now));
        }
        ps.setString(6, CurrentSession.getUser());
        ps.setTimestamp(7, new Timestamp(now));
        ps.setString(8, resourceImpl.getDescription());
        ps.setInt(9, CurrentSession.getTenantId());
        ps.setString(10, resourceImpl.getUUID());

        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            result = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_RESOURCE_LOCK) {
                ps.executeUpdate();
                ps1 = conn.prepareStatement(sql1);
                result = ps1.executeQuery();
            }
        }
        if (result.next()) {
            long version = result.getLong(1);
            resourceImpl.setVersionNumber(version);
        }

    } catch (SQLException e) {
        String msg = "Failed to add resource to path " + resourceImpl.getPath() + ". " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:nl.nn.adapterframework.jdbc.JdbcTransactionalStorage.java

protected String storeMessageInDatabase(Connection conn, String messageId, String correlationId,
        Timestamp receivedDateTime, String comments, String label, Serializable message)
        throws IOException, SQLException, JdbcException, SenderException {
    PreparedStatement stmt = null;
    try {/*from w ww . ja v a  2 s. c  o m*/
        IDbmsSupport dbmsSupport = getDbmsSupport();
        if (log.isDebugEnabled())
            log.debug("preparing insert statement [" + insertQuery + "]");
        if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) {
            stmt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
        } else {
            stmt = conn.prepareStatement(insertQuery);
        }
        stmt.clearParameters();
        int parPos = 0;

        if (StringUtils.isNotEmpty(getTypeField())) {
            stmt.setString(++parPos, type);
        }
        if (StringUtils.isNotEmpty(getSlotId())) {
            stmt.setString(++parPos, getSlotId());
        }
        if (StringUtils.isNotEmpty(getHostField())) {
            stmt.setString(++parPos, host);
        }
        if (StringUtils.isNotEmpty(getLabelField())) {
            stmt.setString(++parPos, label);
        }
        stmt.setString(++parPos, messageId);
        stmt.setString(++parPos, correlationId);
        stmt.setTimestamp(++parPos, receivedDateTime);
        stmt.setString(++parPos, comments);
        if (type.equalsIgnoreCase(TYPE_MESSAGELOG_PIPE) || type.equalsIgnoreCase(TYPE_MESSAGELOG_RECEIVER)) {
            if (getRetention() < 0) {
                stmt.setTimestamp(++parPos, null);
            } else {
                Date date = new Date();
                Calendar cal = Calendar.getInstance();
                cal.setTime(date);
                cal.add(Calendar.DAY_OF_MONTH, getRetention());
                stmt.setTimestamp(++parPos, new Timestamp(cal.getTime().getTime()));
            }
        } else {
            stmt.setTimestamp(++parPos, null);
        }

        if (!isStoreFullMessage()) {
            if (isOnlyStoreWhenMessageIdUnique()) {
                stmt.setString(++parPos, messageId);
                stmt.setString(++parPos, slotId);
            }
            stmt.execute();
            return null;
        }
        if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) {
            ByteArrayOutputStream out = new ByteArrayOutputStream();

            if (isBlobsCompressed()) {
                DeflaterOutputStream dos = new DeflaterOutputStream(out);
                ObjectOutputStream oos = new ObjectOutputStream(dos);
                oos.writeObject(message);
                dos.close();
            } else {
                ObjectOutputStream oos = new ObjectOutputStream(out);
                oos.writeObject(message);
            }

            stmt.setBytes(++parPos, out.toByteArray());
            if (isOnlyStoreWhenMessageIdUnique()) {
                stmt.setString(++parPos, messageId);
                stmt.setString(++parPos, slotId);
            }
            stmt.execute();
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                return rs.getString(1);
            } else {
                return null;
            }
        }
        if (isOnlyStoreWhenMessageIdUnique()) {
            stmt.setString(++parPos, messageId);
            stmt.setString(++parPos, slotId);
        }
        stmt.execute();
        int updateCount = stmt.getUpdateCount();
        if (log.isDebugEnabled())
            log.debug("update count for insert statement: " + updateCount);
        if (updateCount > 0) {
            if (log.isDebugEnabled())
                log.debug("preparing select statement [" + selectKeyQuery + "]");
            stmt = conn.prepareStatement(selectKeyQuery);
            ResultSet rs = null;
            try {
                // retrieve the key
                rs = stmt.executeQuery();
                if (!rs.next()) {
                    throw new SenderException("could not retrieve key of stored message");
                }
                String newKey = rs.getString(1);
                rs.close();

                // and update the blob
                if (log.isDebugEnabled())
                    log.debug("preparing update statement [" + updateBlobQuery + "]");
                stmt = conn.prepareStatement(updateBlobQuery);
                stmt.clearParameters();
                stmt.setString(1, newKey);

                rs = stmt.executeQuery();
                if (!rs.next()) {
                    throw new SenderException("could not retrieve row for stored message [" + messageId + "]");
                }
                //                  String newKey = rs.getString(1);
                //                  BLOB blob = (BLOB)rs.getBlob(2);
                Object blobHandle = dbmsSupport.getBlobUpdateHandle(rs, 1);
                OutputStream out = dbmsSupport.getBlobOutputStream(rs, 1, blobHandle);
                //               OutputStream out = JdbcUtil.getBlobUpdateOutputStream(rs,1);
                if (isBlobsCompressed()) {
                    DeflaterOutputStream dos = new DeflaterOutputStream(out);
                    ObjectOutputStream oos = new ObjectOutputStream(dos);
                    oos.writeObject(message);
                    oos.close();
                    dos.close();
                } else {
                    ObjectOutputStream oos = new ObjectOutputStream(out);
                    oos.writeObject(message);
                    oos.close();
                }
                out.close();
                dbmsSupport.updateBlob(rs, 1, blobHandle);
                return newKey;

            } finally {
                if (rs != null) {
                    rs.close();
                }
            }
        } else {
            if (isOnlyStoreWhenMessageIdUnique()) {
                return "already there";
            } else {
                throw new SenderException(
                        "update count for update statement not greater than 0 [" + updateCount + "]");
            }
        }

    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

From source file:org.lockss.db.DbMigrator.java

/**
 * Migrates a table./*from   www . j a v  a 2 s .  c  o m*/
 * 
 * @param sourceConn
 *          A Connection with the connection to the source database.
 * @param targetConn
 *          A Connection with the connection to the target database.
 * @param columns
 *          A DbColumn[] with the definition of the table columns.
 * @throws DbMigratorException
 *           if any problem occurred migrating the table.
 */
private void migrateTable(Connection sourceConn, Connection targetConn, DbTable table)
        throws DbMigratorException {
    final String DEBUG_HEADER = "migrateTable(): ";
    if (log.isDebug2())
        log.debug2(DEBUG_HEADER + "Starting...");

    try {
        String tableName = table.getName();
        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "tableName = '" + tableName + "'");

        String createTableQuery = table.getCreateQuery();
        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "createTableQuery = '" + createTableQuery + "'");

        boolean created = true;

        // Check whether any existing data in the target table needs to be
        // deleted because the table may have multiple identical rows.
        if (table.isRepeatedRowsAllowed()) {
            // Yes: Remove the table if it does exist.
            removeTargetTableIfPresent(targetConn, tableName);

            // Create the target table.
            targetDbManagerSql.createTable(targetConn, tableName, createTableQuery);
        } else {
            // No: Create the target table, if necessary.
            created = createTableIfNeeded(targetConn, tableName, createTableQuery);
        }

        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "created = " + created);

        // Check whether there may be data in the target table.
        if (!created && !tableExists(targetConn, SEQ_TRANSLATION_TABLE, true)) {
            // Validate that the entire table has been successfully migrated and
            // finish.
            long rowCount = validateTableRowCount(sourceConn, targetConn, table.getRow());
            log.info("Table '" + tableName + "' successfully migrated - " + rowCount + " rows.");

            if (log.isDebug2())
                log.debug2(DEBUG_HEADER + "Done.");
            return;
        }

        boolean hasPrimaryKey = false;
        int pkIndex = -1;
        int index = 0;

        DbRow row = table.getRow();
        DbColumn[] columns = row.getColumnsAsArray();

        // Find out whether the table has a primary key by looping on its columns.
        for (DbColumn column : columns) {
            // Check whether this column is the table primary key.
            if (column.isPk()) {
                if (!sequenceTranslation.containsKey(tableName)) {
                    // Yes: Initialize the primary key sequence translator data in
                    // memory.
                    sequenceTranslation.put(tableName, new HashMap<Long, Long>());
                }

                // Populate the primary key sequence translator data in memory with
                // the data in the database.
                populateTargetSequenceTranslation(targetConn, tableName);

                // Remember the primary key of this table.
                hasPrimaryKey = true;
                if (log.isDebug3())
                    log.debug3(DEBUG_HEADER + "hasPrimaryKey = " + hasPrimaryKey);

                pkIndex = index;
                if (log.isDebug3())
                    log.debug3(DEBUG_HEADER + "pkIndex = " + pkIndex);
                break;
            } else {
                // No: Try the next column.
                index++;
            }
        }

        // Determine whether any foreign key columns in this table that need
        // translation can be translated.
        boolean canTranslate = true;

        for (DbColumn column : columns) {
            if (column.getFkTable() != null) {
                String fkTable = column.getFkTable().toLowerCase();
                log.debug3(DEBUG_HEADER + "fkTable = '" + fkTable + "'.");

                if (!sequenceTranslation.containsKey(fkTable)) {
                    canTranslate = false;
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "canTranslate = " + canTranslate);
                    break;
                }
            }
        }

        String readSourceQuery = row.getReadRowSql();
        PreparedStatement readSource = null;
        ResultSet sourceResultSet = null;

        try {
            // Get the rows from the source table.
            readSource = sourceDbManagerSql.prepareStatement(sourceConn, readSourceQuery);
            sourceResultSet = sourceDbManagerSql.executeQuery(readSource);

            // Loop through all the rows from the source table.
            while (sourceResultSet.next()) {
                // Get the values of the various columns for this row.
                for (DbColumn column : columns) {
                    column.getValue(sourceResultSet);
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "Read source " + column.getName() + " = '" + column.getValue()
                                + "'.");
                }

                // Check whether the table has not been created and it has a primary
                // key.
                if (!created && hasPrimaryKey) {
                    // Yes: Check whether the row has already been migrated.
                    if (sequenceTranslation.get(tableName).containsKey(columns[pkIndex].getValue())) {
                        // Yes: Continue with the next row.
                        if (log.isDebug3())
                            log.debug3(DEBUG_HEADER + "Translated PK found.");
                        continue;
                    }
                }

                // Check whether the row cannot be translated.
                if (!canTranslate) {
                    // Yes: Continue with the next row.
                    continue;
                }

                boolean translated = false;

                // Check whether the table already existed and it is possible to
                // identify this row in it.
                if (!created && !table.isRepeatedRowsAllowed()) {
                    for (DbColumn column : columns) {
                        if (column.getFkTable() != null) {
                            // Translate this foreign key.
                            Long translatedFk = sequenceTranslation.get(column.getFkTable().toLowerCase())
                                    .get(column.getValue());
                            if (log.isDebug3())
                                log.debug3(DEBUG_HEADER + "FK conversion: " + column.getValue() + " => "
                                        + translatedFk);
                            column.setValue(translatedFk);
                        }
                    }

                    translated = true;

                    // Try to find this row in the existing target table.
                    long rowCount = countMatchingTargetRows(targetConn, table);
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "rowCount = " + rowCount);

                    // Determine whether this row had already been migrated.
                    if (rowCount == 1) {
                        // Yes: Do nothing more with this row from the source table.
                        continue;
                    }
                }

                // Write the row to the target table.
                String writeTargetQuery = row.getWriteRowSql();
                PreparedStatement writeTarget = null;
                ResultSet targetResultSet = null;

                try {
                    // Handle a table with a primary key differently to be able to
                    // extract the generated primary key.
                    if (hasPrimaryKey) {
                        writeTarget = targetDbManagerSql.prepareStatement(targetConn, writeTargetQuery,
                                Statement.RETURN_GENERATED_KEYS);
                    } else {
                        writeTarget = targetDbManagerSql.prepareStatement(targetConn, writeTargetQuery);
                    }

                    index = 1;

                    // Loop through all the columns in the table.
                    for (DbColumn column : columns) {
                        // Check whether this is a primary key.
                        if (column.isPk()) {
                            // Yes: No parameter is set in this case, as the value is
                            // generated.
                            if (log.isDebug3())
                                log.debug3(DEBUG_HEADER + "Skip write " + "target parameter primary key '"
                                        + column.getName() + "'.");
                        } else {
                            // No: Check whether this is a foreign key.
                            if (column.getFkTable() != null && !translated) {
                                // Yes: Translate this foreign key.
                                Long translatedFk = sequenceTranslation.get(column.getFkTable().toLowerCase())
                                        .get(column.getValue());
                                if (log.isDebug3())
                                    log.debug3(DEBUG_HEADER + "FK conversion: " + column.getValue() + " => "
                                            + translatedFk);
                                column.setValue(translatedFk);
                            }

                            // Set the parameter for this column in the prepared statement.
                            if (log.isDebug3())
                                log.debug3(DEBUG_HEADER + "Set write " + "target parameter " + index + " with '"
                                        + column.getValue() + "'.");
                            column.setParameter(writeTarget, index++);
                        }
                    }

                    // Write the row.
                    int addedCount = targetDbManagerSql.executeUpdate(writeTarget);
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "addedCount = " + addedCount);

                    // Check whether this column is the primary key.
                    if (hasPrimaryKey) {
                        // Yes: Get the generated primary key.
                        targetResultSet = writeTarget.getGeneratedKeys();

                        if (!targetResultSet.next()) {
                            throw new DbMigratorException("No primary key created.");
                        }

                        Long targetPkSeq = targetResultSet.getLong(1);
                        if (log.isDebug3())
                            log.debug3(DEBUG_HEADER + "targetPkSeq = " + targetPkSeq);

                        // Save the translation of the primary key of this row.
                        saveSequenceTranslation(targetConn, tableName, (Long) columns[pkIndex].getValue(),
                                targetPkSeq);
                    }
                } catch (SQLException sqle) {
                    String message = "Cannot write the target '" + tableName + "' table";
                    log.error(message, sqle);
                    log.error("SQL = '" + writeTargetQuery + "'.");
                    for (DbColumn column : columns) {
                        log.error(column.getName() + " = '" + column.getValue() + "'.");
                    }
                    throw new DbMigratorException(message, sqle);
                } catch (RuntimeException re) {
                    String message = "Cannot write the target '" + tableName + "' table";
                    log.error(message, re);
                    log.error("SQL = '" + writeTargetQuery + "'.");
                    for (DbColumn column : columns) {
                        log.error(column.getName() + " = '" + column.getValue() + "'.");
                    }
                    throw new DbMigratorException(message, re);
                } finally {
                    DbManagerSql.safeCloseStatement(writeTarget);
                }
            }
        } catch (SQLException sqle) {
            String message = "Cannot read the source '" + tableName + "' table";
            log.error(message, sqle);
            log.error("SQL = '" + readSourceQuery + "'.");
            throw new DbMigratorException(message, sqle);
        } catch (RuntimeException re) {
            String message = "Cannot read the source '" + tableName + "' table";
            log.error(message, re);
            log.error("SQL = '" + readSourceQuery + "'.");
            throw new DbMigratorException(message, re);
        } finally {
            DbManagerSql.safeCloseResultSet(sourceResultSet);
            DbManagerSql.safeCloseStatement(readSource);
        }

        // Compare the rows in both tables.
        long rowCount = validateTableRowCount(sourceConn, targetConn, row);
        log.info("Table '" + tableName + "' successfully migrated - " + rowCount + " rows.");
    } catch (SQLException sqle) {
        throw new DbMigratorException(sqle);
    } catch (RuntimeException re) {
        throw new DbMigratorException(re);
    } finally {
        try {
            DbManagerSql.commitOrRollback(targetConn, log);
            DbManagerSql.rollback(sourceConn, log);
        } catch (SQLException sqle) {
            throw new DbMigratorException(sqle);
        } catch (RuntimeException re) {
            throw new DbMigratorException(re);
        }
    }

    if (log.isDebug2())
        log.debug2(DEBUG_HEADER + "Done.");
}

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

@Override
public boolean insertDVIRRecord(DVIR_EDI322Bean eb, DVIRRCDStatisticBean bean, String boescUserId,
        String userType, File file) throws Exception {
    logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId
            + " userType ::" + userType);
    QueryRunner qrun = new QueryRunner(getDataSource());
    Connection conn = getConnection();
    conn.setAutoCommit(false);/*from  w w w  .  ja va2 s  . c  o m*/
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

        StringBuilder sbQuery = new StringBuilder(
                "INSERT INTO DVIR_TRAN_SET (ISA_HEADER, GS_HEADER, SENDER_ID, SENDER_TYPE, ISA_DATETIME, GS_CONTROL, ST_CONTROL,");
        sbQuery.append(
                " INSP_DATE, INSP_TIME, INSP_TIME_ZONE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, PORT_QUAL, PORT_ID,  ");
        sbQuery.append(
                " DRV_STATE_ABBR, DRV_LIC_NO, DRV_NAME, MC_SCAC, MC_NAME, RCD_INFO, IEP_DOT, MC_EIN, MC_DOT, IDD_PIN,   ");
        sbQuery.append(
                " Q5_SEG, N7_SEG, R4_SEG, N1_SEG, N1_DR, RCD_00, RCD_01, RCD_02, RCD_03, RCD_04, RCD_05, RCD_06, RCD_07, RCD_08, RCD_09,  ");
        if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) {
            sbQuery.append(" IEP_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) {
            sbQuery.append(" MRV_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) {
            sbQuery.append(" FO_ID, STATUS ");
        }

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

        //get Additional Details from GIER DB
        GIERInfoDetails gierInfo = null;
        gierInfo = getDVIRAdditionaldetails(eb.getEqpInitial(), eb.getEqpNumber());
        System.out.println("Before UIIA Datasource");
        UIIAInfoDetails uiiaInfo = null;
        uiiaInfo = getUIIAdetailsforDVIR(eb);

        //logger.info("gierInfo ::"+gierInfo);

        pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, eb.getIsaheader());
        pstmt.setString(2, eb.getGsHeader());
        pstmt.setString(3, eb.getSenderId());
        pstmt.setString(4, userType);
        pstmt.setString(5, eb.getIsaDateTime());
        pstmt.setString(6, eb.getGsControl());
        pstmt.setString(7, eb.getStControl());
        pstmt.setString(8, eb.getInspDate());
        pstmt.setString(9, eb.getInspTime());
        pstmt.setString(10, eb.getInspTimeZone());
        pstmt.setString(11, eb.getEqpInitial());
        pstmt.setString(12, eb.getEqpNumber());
        pstmt.setString(13, eb.getChassisId());
        pstmt.setString(14, gierInfo.getCompanySCACCode());
        pstmt.setString(15, eb.getPortQualifier());
        pstmt.setString(16, eb.getPortIdentifier());
        pstmt.setString(17, eb.getDrvState());
        pstmt.setString(18, eb.getDrvLicNo());
        pstmt.setString(19, uiiaInfo.getDrvName());
        pstmt.setString(20, eb.getMcScac());
        pstmt.setString(21, eb.getMcName());
        pstmt.setString(22, eb.getRcdInfo());
        pstmt.setString(23, gierInfo.getUsDotNumber());
        pstmt.setString(24, uiiaInfo.getMcEin());
        pstmt.setString(25, uiiaInfo.getMcDot());
        pstmt.setString(26, uiiaInfo.getIddPin());
        pstmt.setString(27, eb.getQ5Details());
        pstmt.setString(28, eb.getN7Details());
        pstmt.setString(29, eb.getR4Details());
        pstmt.setString(30, eb.getN1Details());
        pstmt.setString(31, eb.getN1DrDetails());
        pstmt.setInt(32, bean.getNoDefectsCount());
        pstmt.setInt(33, bean.getBrakesCount());
        pstmt.setInt(34, bean.getLightsCount());
        pstmt.setInt(35, bean.getWheelCount());
        pstmt.setInt(36, bean.getAirlineCount());
        pstmt.setInt(37, bean.getCouplingCount());
        pstmt.setInt(38, bean.getFrameCount());
        pstmt.setInt(39, bean.getBolsterCount());
        pstmt.setInt(40, bean.getFastenerCount());
        pstmt.setInt(41, bean.getSliderCount());
        pstmt.setString(42, boescUserId);
        pstmt.setString(43, GlobalVariables.STATUS_PENDING);
        pstmt.setObject(44, DateTimeFormater.getSqlSysTimestamp());

        int dbStat = 0;
        int dvirKey = 0;
        dbStat = pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        if (dbStat != 0) {
            if (rs != null) {
                while (rs.next()) {
                    dvirKey = rs.getInt(1);
                    logger.info("dvirKey: " + dvirKey);
                }
            }
        }
        if (dvirKey != 0) {
            conn.commit();
            //Update BOESC_UNIQUE_NO : using business logic
            String sql = "UPDATE DVIR_TRAN_SET SET DVIR_NO = ? WHERE DVIR_TRAN_ID = ? ";
            qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(dvirKey, "DVIR-"), dvirKey });
            logger.info("Record Inserted successfully for DVIR..." + file.getName());
            return true;
        } else {
            conn.rollback();
            logger.error("Failure Data insertion in DVIR..");
        }
    } 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:pt.iflow.flows.FlowHolderBean.java

private synchronized State insertOrUpdateSubFlow(UserInfoInterface userInfo, String file, String name,
        byte[] data, boolean forceCreate, boolean makeVersion, String comment) {
    // recorrer a um metodo privado para efectuar a actualizacao
    // propriamente dita.
    // Esse mesmo metodo sera usado pelo deploy no caso de ser necessario
    // actualizar o catalogo.

    State result = new State();
    Connection db = null;//from  ww  w  . j av  a 2  s .c  o  m
    PreparedStatement pst = null;
    ResultSet rs = null;
    boolean flowFound = false;
    int flowid = -1;
    try {
        db = Utils.getDataSource().getConnection();
        db.setAutoCommit(false);

        String query = "select flowid,flowversion from sub_flow where flowfile=? and organizationid=?";
        Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateSubFlow", "Query1: " + query);
        pst = db.prepareStatement(query);
        pst.setString(1, file);
        pst.setString(2, userInfo.getOrganization());

        rs = pst.executeQuery();

        if (rs.next()) {
            flowFound = true;
            flowid = rs.getInt("flowid");
            result.version = rs.getInt("flowversion");
        }

        rs.close();
        pst.close();

        boolean copyToHistory = false;
        if (flowFound) {
            query = "update sub_flow set flowdata=?," + (makeVersion ? "flowversion=flowversion+1," : "")
                    + "modified=? where flowid=?";
            Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateSubFlow", "Query2a: " + query);
            pst = db.prepareStatement(query);
            pst.setBinaryStream(1, new ByteArrayInputStream(data), data.length);
            pst.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            pst.setInt(3, flowid);
            int upd = pst.executeUpdate();
            pst.close();
            result.created = false;
            copyToHistory = (upd != 0);
        } else if (forceCreate) {
            if (null == name)
                name = file;
            Timestamp now = new Timestamp(System.currentTimeMillis());
            query = DBQueryManager.getQuery("FlowHolder.INSERT_SUBFLOW");
            Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateSubFlow", "Query2b: " + query);
            pst = db.prepareStatement(query, new String[] { "flowid" });
            pst.setString(1, name);
            pst.setString(2, file);
            pst.setTimestamp(3, now);
            pst.setString(4, userInfo.getOrganization());
            pst.setBinaryStream(5, new ByteArrayInputStream(data), data.length);
            pst.setTimestamp(6, now);
            pst.executeUpdate();
            rs = pst.getGeneratedKeys();
            if (rs.next()) {
                result.created = true;
                flowid = rs.getInt(1);
                copyToHistory = true;
            }
            rs.close();
            pst.close();
        } else {
            throw new Exception("Cannot create sub flow.");
        }

        // Copy to flow history.
        if (copyToHistory && makeVersion) {
            if (null != comment && comment.length() > MAX_COMMENT_SIZE)
                comment = comment.substring(0, MAX_COMMENT_SIZE);
            query = DBQueryManager.getQuery("FlowHolder.COPY_SUB_FLOW_TO_HISTORY");
            Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateSubFlow", "Query3: " + query);
            pst = db.prepareStatement(query);
            pst.setString(1, comment);
            pst.setInt(2, flowid);
            pst.executeUpdate();
            pst.close();
            result.version++;
        }

        db.commit();
        result.success = true;
    } catch (Exception e) {
        try {
            db.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
        result.success = false;
    } finally {
        DatabaseInterface.closeResources(db, pst, rs);
    }

    result.flowid = flowid;

    return result;
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *//*from w w w. j a  va2  s  .  c  om*/
public void insertWikiUser(WikiUser user, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_USER);
            int nextUserId = this.nextWikiUserId(conn);
            user.setUserId(nextUserId);
            stmt.setInt(index++, user.getUserId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_USER_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        stmt.setString(index++, user.getUsername());
        stmt.setString(index++, user.getDisplayName());
        stmt.setTimestamp(index++, user.getCreateDate());
        stmt.setTimestamp(index++, user.getLastLoginDate());
        stmt.setString(index++, user.getCreateIpAddress());
        stmt.setString(index++, user.getLastLoginIpAddress());
        stmt.setString(index++, user.getEmail());
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            user.setUserId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
    // Store user preferences
    Map<String, String> defaults = this.lookupUserPreferencesDefaults(conn);
    Map<String, String> preferences = user.getPreferences();
    try {
        stmt = conn.prepareStatement(STATEMENT_INSERT_USER_PREFERENCE);
        // Only store preferences that are not default
        for (String key : defaults.keySet()) {
            String defVal = defaults.get(key);
            String cusVal = preferences.get(key);
            if (StringUtils.isBlank(cusVal)) {
                user.setPreference(key, defVal);
            } else if (StringUtils.isBlank(defVal) || !defaults.get(key).equals(preferences.get(key))) {
                stmt.setInt(1, user.getUserId());
                stmt.setString(2, key);
                stmt.setString(3, cusVal);
                stmt.executeUpdate();
            }
        }
    } finally {
        DatabaseConnection.closeStatement(stmt);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *//*from   w w  w .ja  va  2 s.com*/
public void insertWikiFile(WikiFile wikiFile, int virtualWikiId, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_FILE);
            int fileId = this.nextWikiFileId(conn);
            wikiFile.setFileId(fileId);
            stmt.setInt(index++, wikiFile.getFileId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_FILE_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        stmt.setInt(index++, virtualWikiId);
        stmt.setString(index++, wikiFile.getFileName());
        stmt.setString(index++, wikiFile.getUrl());
        stmt.setString(index++, wikiFile.getMimeType());
        stmt.setInt(index++, wikiFile.getTopicId());
        stmt.setTimestamp(index++, wikiFile.getDeleteDate());
        stmt.setInt(index++, (wikiFile.getReadOnly() ? 1 : 0));
        stmt.setInt(index++, (wikiFile.getAdminOnly() ? 1 : 0));
        stmt.setLong(index++, wikiFile.getFileSize());
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            wikiFile.setFileId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *//*from  w w w .j  a v a  2  s .  com*/
public void insertVirtualWiki(VirtualWiki virtualWiki, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_VIRTUAL_WIKI);
            int virtualWikiId = this.nextVirtualWikiId(conn);
            virtualWiki.setVirtualWikiId(virtualWikiId);
            stmt.setInt(index++, virtualWiki.getVirtualWikiId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_VIRTUAL_WIKI_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        stmt.setString(index++, virtualWiki.getName());
        stmt.setString(index++, (virtualWiki.isDefaultRootTopicName() ? null : virtualWiki.getRootTopicName()));
        stmt.setString(index++, (virtualWiki.isDefaultLogoImageUrl() ? null : virtualWiki.getLogoImageUrl()));
        stmt.setString(index++,
                (virtualWiki.isDefaultMetaDescription() ? null : virtualWiki.getMetaDescription()));
        stmt.setString(index++, (virtualWiki.isDefaultSiteName() ? null : virtualWiki.getSiteName()));
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            virtualWiki.setVirtualWikiId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
}

From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCResourceDAO.java

public void addResourceWithoutContentId(ResourceImpl resourceImpl, boolean isUpdatingExisting)
        throws RepositoryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet result = null;/*from w  w  w.  j  a v a 2  s. c om*/
    ResourceIDImpl resourceID = resourceImpl.getResourceIDImpl();

    try {
        String sql = "INSERT INTO REG_RESOURCE (REG_PATH_ID, REG_NAME, REG_MEDIA_TYPE, "
                + "REG_CREATOR, REG_CREATED_TIME, REG_LAST_UPDATOR, "
                + "REG_LAST_UPDATED_TIME, REG_DESCRIPTION, " + "REG_TENANT_ID, REG_UUID) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        String sql1 = "SELECT MAX(REG_VERSION) FROM REG_RESOURCE";

        long now = System.currentTimeMillis();

        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);

        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_VERSION") });
        } else {
            ps = conn.prepareStatement(sql);
        }

        ps.setInt(1, resourceID.getPathID());
        ps.setString(2, resourceID.getName());
        ps.setString(3, resourceImpl.getMediaType());

        String userName = CurrentContext.getUser();
        String authorName = null;

        if (isUpdatingExisting) {
            authorName = resourceImpl.getAuthorUserName();
            if (authorName == null) {
                authorName = userName;

                if (authorName == null) {
                    authorName = "RepositoryUser";
                }

                resourceImpl.setAuthorUserName(authorName);
            }

            ps.setString(4, authorName);

            Date createdTime = resourceImpl.getCreatedTime();
            Timestamp createdTimestamp;
            if (createdTime == null) {
                createdTimestamp = new Timestamp(now);
            } else {
                createdTimestamp = new Timestamp(createdTime.getTime());
            }
            ps.setTimestamp(5, createdTimestamp);
        } else {
            if (authorName == null && userName != null) {
                authorName = userName;
            } else {
                authorName = "RepositoryUser";
            }

            ps.setString(4, authorName);
            resourceImpl.setAuthorUserName(authorName);
            ps.setTimestamp(5, new Timestamp(now));
        }

        ps.setString(6, authorName);
        ps.setTimestamp(7, new Timestamp(now));
        ps.setString(8, resourceImpl.getDescription());
        ps.setInt(9, CurrentContext.getTenantId());
        ps.setString(10, resourceImpl.getUUID());

        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            result = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_RESOURCE_LOCK) {
                ps.executeUpdate();
                ps1 = conn.prepareStatement(sql1);
                result = ps1.executeQuery();
            }
        }
        if (result.next()) {
            long version = result.getLong(1);
            resourceImpl.setVersionNumber(version);
        }
    } catch (SQLException e) {
        String msg = "Failed to add resource to path " + resourceImpl.getPath() + ". " + e.getMessage();
        log.error(msg, e);
        throw new RepositoryDBException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}