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.wso2.intcloud.core.dao.ApplicationDAO.java

/**
 * Method for adding application details to database.
 *
 * @param dbConnection database connection
 * @param application application object
 * @param tenantId tenant id/*from  w w w .j a  v  a  2 s . com*/
 * @return
 * @throws IntCloudException
 */
public void addApplication(Connection dbConnection, Application application, int tenantId)
        throws IntCloudException {

    PreparedStatement preparedStatement = null;
    int applicationId = 0;
    ResultSet resultSet = null;

    try {

        preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_APPLICATION,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, application.getApplicationName());
        preparedStatement.setString(2, application.getHashId());
        preparedStatement.setString(3, application.getDescription());
        preparedStatement.setInt(4, tenantId);
        preparedStatement.setString(5, application.getDefaultVersion());
        preparedStatement.setString(6, application.getCarbonApplicationName());
        preparedStatement.setString(7, application.getApplicationType());

        preparedStatement.execute();

        resultSet = preparedStatement.getGeneratedKeys();
        while (resultSet.next()) {
            applicationId = resultSet.getInt(1);
        }

        List<Version> versions = application.getVersions();

        if (versions != null) {
            for (Version version : versions) {
                addVersion(dbConnection, version, applicationId, tenantId);
            }
        }

        InputStream iconInputStream = null;
        if (application.getIcon() != null) {
            iconInputStream = IOUtils.toBufferedInputStream(application.getIcon().getBinaryStream());
        }
        updateApplicationIcon(dbConnection, iconInputStream, applicationId);

    } catch (SQLException e) {

        String msg = "Error occurred while adding application : " + application.getApplicationName()
                + " to database " + "in tenant : " + tenantId;
        log.error(msg, e);
        throw new IntCloudException(msg, e);

    } catch (IOException e) {
        String msg = "Error while generating stream of the icon for application : "
                + application.getApplicationName() + " in tenant : " + tenantId;
        log.error(msg, e);
        throw new IntCloudException(msg, e);
    } finally {
        DBUtil.closeResultSet(resultSet);
        DBUtil.closePreparedStatement(preparedStatement);
    }

}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public FeedEntry addFeedEntry(Feed feed, JSONObject data) {
    if (feed == null) {
        Log.e(TAG, "Cannot add entry from a NULL feed");
        return null;
    }/*  ww w.  j av  a2  s  .  co m*/
    FeedEntry entry = null;
    try {
        checkOpenness();
        final String query = "INSERT INTO " + IotHubDataHandler.TABLE_FEED_ENTRY + " ( "
                + IotHubDataHandler.KEY_FEED_ENTRY_FEED_ID + "," + IotHubDataHandler.KEY_FEED_ENTRY_TIMESTAMP
                + "," + IotHubDataHandler.KEY_FEED_ENTRY_DATA + ") VALUES (?,?,?)";
        PreparedStatement ps = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        ps.setLong(1, feed.getId());
        ps.setLong(2, new Date().getTime());
        ps.setString(3, data.toString());
        ps.executeUpdate();
        ResultSet genKeysFeed = ps.getGeneratedKeys();
        if (genKeysFeed.next()) {
            long insertIdEntry = genKeysFeed.getLong(1);
            entry = getFeedEntry(insertIdEntry);
        }
        genKeysFeed.close();
        ps.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        return null;
    }
    return entry;
}

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

/**
 * Method to persist a tag./* ww w  .  j a va  2s  . co m*/
 *
 * @param resource the resource
 * @param userID   the id of the user who added the tag.
 * @param tagName  the name of tag to be persisted.
 *
 * @throws RegistryException if some error occurs while adding a tag
 */
public void addTagging(String tagName, ResourceImpl resource, String userID) throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;
    PreparedStatement ps3 = null;
    ResultSet result = null;
    try {
        String sql1 = "INSERT INTO REG_TAG (REG_TAG_NAME, REG_USER_ID, REG_TAGGED_TIME, "
                + "REG_TENANT_ID) VALUES (?,?,?,?)";
        String sql2 = "SELECT MAX(REG_ID) FROM REG_TAG";
        long now = System.currentTimeMillis();

        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps1 = conn.prepareStatement(sql1, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, DatabaseConstants.ID_FIELD) });
        } else {
            ps1 = conn.prepareStatement(sql1);
        }
        ps1.setString(1, tagName);
        ps1.setString(2, userID);
        ps1.setDate(3, new Date(now));
        ps1.setInt(4, CurrentSession.getTenantId());
        if (returnsGeneratedKeys) {
            ps1.executeUpdate();
            result = ps1.getGeneratedKeys();
        } else {
            synchronized (ADD_TAG_LOCK) {
                ps1.executeUpdate();
                ps2 = conn.prepareStatement(sql2);
                result = ps2.executeQuery();
            }
        }
        if (result.next()) {
            int tagId = result.getInt(1);
            String sql3 = "INSERT INTO REG_RESOURCE_TAG (REG_TAG_ID, REG_VERSION, REG_TENANT_ID) "
                    + "VALUES(?,?,?)";
            ps3 = conn.prepareStatement(sql3);

            ps3.setInt(1, tagId);
            ps3.setLong(2, resource.getVersionNumber());
            ps3.setInt(3, CurrentSession.getTenantId());

            ps3.executeUpdate();
        }

    } catch (SQLException e) {

        String msg = "Failed to add tag " + tagName + " to resource " + resource.getPath() + " by user "
                + userID + ". " + 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 {
                    try {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    } finally {
                        if (ps3 != null) {
                            ps3.close();
                        }
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:org.apache.beehive.controls.system.jdbc.JdbcControlImpl.java

/**
 * Create and exec a {@link PreparedStatement}
 *
 * @param method the method to invoke/* ww w.  ja v a 2  s . c  o m*/
 * @param args the method's arguments
 * @return the return value from the {@link PreparedStatement}
 * @throws Throwable any exception that occurs; the caller should handle these appropriately
 */
protected Object execPreparedStatement(Method method, Object[] args) throws Throwable {

    final SQL methodSQL = (SQL) _context.getMethodPropertySet(method, SQL.class);
    if (methodSQL == null || methodSQL.statement() == null) {
        throw new ControlException("Method " + method.getName() + " is missing @SQL annotation");
    }

    setTypeMappers(methodSQL.typeMappersOverride());

    //
    // build the statement and execute it
    //

    PreparedStatement ps = null;
    try {
        Class returnType = method.getReturnType();

        SqlStatement sqlStatement = _sqlParser.parse(methodSQL.statement());
        ps = sqlStatement.createPreparedStatement(_context, _connection, _cal, method, args);

        if (LOGGER.isInfoEnabled()) {
            LOGGER.info("PreparedStatement: "
                    + sqlStatement.createPreparedStatementString(_context, _connection, method, args));
        }

        //
        // special processing for batch updates
        //
        if (sqlStatement.isBatchUpdate()) {
            return ps.executeBatch();
        }

        //
        // execute the statement
        //
        boolean hasResults = ps.execute();

        //
        // callable statement processing
        //
        if (sqlStatement.isCallableStatement()) {
            SQLParameter[] params = (SQLParameter[]) args[0];
            for (int i = 0; i < params.length; i++) {
                if (params[i].dir != SQLParameter.IN) {
                    params[i].value = ((CallableStatement) ps).getObject(i + 1);
                }
            }
            return null;
        }

        //
        // process returned data
        //
        ResultSet rs = null;
        int updateCount = ps.getUpdateCount();

        if (hasResults) {
            rs = ps.getResultSet();
        }

        if (sqlStatement.getsGeneratedKeys()) {
            rs = ps.getGeneratedKeys();
            hasResults = true;
        }

        if (!hasResults && updateCount > -1) {
            boolean moreResults = ps.getMoreResults();
            int tempUpdateCount = ps.getUpdateCount();

            while ((moreResults && rs == null) || tempUpdateCount > -1) {
                if (moreResults) {
                    rs = ps.getResultSet();
                    hasResults = true;
                    moreResults = false;
                    tempUpdateCount = -1;
                } else {
                    moreResults = ps.getMoreResults();
                    tempUpdateCount = ps.getUpdateCount();
                }
            }
        }

        Object returnObject = null;
        if (hasResults) {

            //
            // if a result set mapper was specified in the methods annotation, use it
            // otherwise find the mapper for the return type in the hashmap
            //
            final Class resultSetMapperClass = methodSQL.resultSetMapper();
            final ResultSetMapper rsm;
            if (!UndefinedResultSetMapper.class.isAssignableFrom(resultSetMapperClass)) {
                if (ResultSetMapper.class.isAssignableFrom(resultSetMapperClass)) {
                    rsm = (ResultSetMapper) resultSetMapperClass.newInstance();
                } else {
                    throw new ControlException(
                            "Result set mappers must be subclasses of ResultSetMapper.class!");
                }
            } else {
                if (_resultMappers.containsKey(returnType)) {
                    rsm = _resultMappers.get(returnType);
                } else {
                    if (_xmlObjectClass != null && _xmlObjectClass.isAssignableFrom(returnType)) {
                        rsm = _resultMappers.get(_xmlObjectClass);
                    } else {
                        rsm = DEFAULT_MAPPER;
                    }
                }
            }

            returnObject = rsm.mapToResultType(_context, method, rs, _cal);
            if (rsm.canCloseResultSet() == false) {
                getResources().add(ps);
            }

            //
            // empty ResultSet
            //
        } else {
            if (returnType.equals(Void.TYPE)) {
                returnObject = null;
            } else if (returnType.equals(Integer.TYPE)) {
                returnObject = new Integer(updateCount);
            } else if (!sqlStatement.isCallableStatement()) {
                throw new ControlException(
                        "Method " + method.getName() + "is DML but does not return void or int");
            }
        }
        return returnObject;

    } finally {
        // Keep statements open that have in-use result sets
        if (ps != null && !getResources().contains(ps)) {
            ps.close();
        }
    }
}

From source file:org.nuxeo.ecm.directory.sql.SQLSession.java

@Override
public DocumentModel createEntry(Map<String, Object> fieldMap) {
    checkPermission(SecurityConstants.WRITE);

    Field schemaIdField = schemaFieldMap.get(idField);

    String idFieldName = schemaIdField.getName().getPrefixedName();

    acquireConnection();//from   ww  w . j  a  v a 2 s  . c o  m
    if (autoincrementIdField) {
        fieldMap.remove(idFieldName);
    } else {
        // check id that was given
        Object rawId = fieldMap.get(idFieldName);
        if (rawId == null) {
            throw new DirectoryException("Missing id");
        }
        String id = String.valueOf(rawId);
        if (hasEntry(id)) {
            throw new DirectoryException(String.format("Entry with id %s already exists", id));
        }

        if (isMultiTenant()) {
            String tenantId = getCurrentTenantId();
            if (!StringUtils.isBlank(tenantId)) {
                fieldMap.put(TENANT_ID_FIELD, tenantId);
                if (computeMultiTenantId) {
                    fieldMap.put(idFieldName, computeMultiTenantDirectoryId(tenantId, id));
                }
            }
        }
    }

    List<Column> columnList = new ArrayList<>(table.getColumns());
    Column idColumn = null;
    for (Iterator<Column> i = columnList.iterator(); i.hasNext();) {
        Column column = i.next();
        if (column.isIdentity()) {
            idColumn = column;
        }
        String prefixField = schemaFieldMap.get(column.getKey()).getName().getPrefixedName();
        if (fieldMap.get(prefixField) == null) {
            i.remove();
        }
    }
    Insert insert = new Insert(table);
    for (Column column : columnList) {
        insert.addColumn(column);
    }
    // needed for Oracle for empty map insert
    insert.addIdentityColumn(idColumn);
    String sql = insert.getStatement();

    if (logger.isLogEnabled()) {
        List<Serializable> values = new ArrayList<>(columnList.size());
        for (Column column : columnList) {
            String prefixField = schemaFieldMap.get(column.getKey()).getName().getPrefixedName();
            Object value = fieldMap.get(prefixField);
            values.add(fieldValueForWrite(value, column));
        }
        logger.logSQL(sql, values);
    }

    DocumentModel entry;
    PreparedStatement ps = null;
    Statement st = null;
    try {
        if (autoincrementIdField && dialect.hasIdentityGeneratedKey()) {
            ps = sqlConnection.prepareStatement(sql, new String[] { idField });
        } else {
            ps = sqlConnection.prepareStatement(sql);
        }
        int index = 1;
        for (Column column : columnList) {
            String prefixField = schemaFieldMap.get(column.getKey()).getName().getPrefixedName();
            Object value = fieldMap.get(prefixField);
            setFieldValue(ps, index, column, value);
            index++;
        }
        ps.execute();
        if (autoincrementIdField) {
            Column column = table.getColumn(idField);
            ResultSet rs;
            if (dialect.hasIdentityGeneratedKey()) {
                rs = ps.getGeneratedKeys();
            } else {
                // needs specific statements
                sql = dialect.getIdentityGeneratedKeySql(column);
                st = sqlConnection.createStatement();
                rs = st.executeQuery(sql);
            }
            if (!rs.next()) {
                throw new DirectoryException("Cannot get generated key");
            }
            if (logger.isLogEnabled()) {
                logger.logResultSet(rs, Collections.singletonList(column));
            }
            Serializable rawId = column.getFromResultSet(rs, 1);
            fieldMap.put(idFieldName, rawId);
            rs.close();
        }
        entry = fieldMapToDocumentModel(fieldMap);
    } catch (SQLException e) {
        checkConcurrentUpdate(e);
        throw new DirectoryException("createEntry failed", e);
    } finally {
        try {
            if (ps != null) {
                ps.close();
            }
            if (st != null) {
                st.close();
            }
        } catch (SQLException sqle) {
            throw new DirectoryException(sqle);
        }
    }

    // second step: add references fields
    String sourceId = entry.getId();
    for (Reference reference : getDirectory().getReferences()) {
        String referenceFieldName = schemaFieldMap.get(reference.getFieldName()).getName().getPrefixedName();
        if (getDirectory().getReferences(reference.getFieldName()).size() > 1) {
            log.warn("Directory " + getDirectory().getName() + " cannot create field "
                    + reference.getFieldName() + " for entry " + fieldMap.get(idFieldName)
                    + ": this field is associated with more than one reference");
            continue;
        }
        @SuppressWarnings("unchecked")
        List<String> targetIds = (List<String>) fieldMap.get(referenceFieldName);
        if (reference instanceof TableReference) {
            // optim: reuse the current session
            // but still initialize the reference if not yet done
            TableReference tableReference = (TableReference) reference;
            tableReference.maybeInitialize(this);
            tableReference.addLinks(sourceId, targetIds, this);
        } else {
            reference.addLinks(sourceId, targetIds);
        }
    }
    getDirectory().invalidateCaches();
    return entry;
}

From source file:org.owasp.dependencycheck.data.nvdcve.CveDB.java

/**
 * Updates the vulnerability within the database. If the vulnerability does
 * not exist it will be added./*from   w  w  w.  jav a2s. c om*/
 *
 * @param vuln the vulnerability to add to the database
 * @throws DatabaseException is thrown if the database
 */
public synchronized void updateVulnerability(Vulnerability vuln) throws DatabaseException {
    clearCache();
    ResultSet rs = null;
    try {
        int vulnerabilityId = 0;
        final PreparedStatement selectVulnerabilityId = getPreparedStatement(SELECT_VULNERABILITY_ID);
        selectVulnerabilityId.setString(1, vuln.getName());
        rs = selectVulnerabilityId.executeQuery();
        if (rs.next()) {
            vulnerabilityId = rs.getInt(1);
            // first delete any existing vulnerability info. We don't know what was updated. yes, slower but atm easier.
            final PreparedStatement deleteReference = getPreparedStatement(DELETE_REFERENCE);
            deleteReference.setInt(1, vulnerabilityId);
            deleteReference.execute();

            final PreparedStatement deleteSoftware = getPreparedStatement(DELETE_SOFTWARE);
            deleteSoftware.setInt(1, vulnerabilityId);
            deleteSoftware.execute();
        }

        DBUtils.closeResultSet(rs);

        if (vulnerabilityId != 0) {
            if (vuln.getDescription().contains("** REJECT **")) {
                final PreparedStatement deleteVulnerability = getPreparedStatement(DELETE_VULNERABILITY);
                deleteVulnerability.setInt(1, vulnerabilityId);
                deleteVulnerability.executeUpdate();
            } else {
                final PreparedStatement updateVulnerability = getPreparedStatement(UPDATE_VULNERABILITY);
                updateVulnerability.setString(1, vuln.getDescription());
                updateVulnerability.setString(2, vuln.getCwe());
                updateVulnerability.setFloat(3, vuln.getCvssScore());
                updateVulnerability.setString(4, vuln.getCvssAccessVector());
                updateVulnerability.setString(5, vuln.getCvssAccessComplexity());
                updateVulnerability.setString(6, vuln.getCvssAuthentication());
                updateVulnerability.setString(7, vuln.getCvssConfidentialityImpact());
                updateVulnerability.setString(8, vuln.getCvssIntegrityImpact());
                updateVulnerability.setString(9, vuln.getCvssAvailabilityImpact());
                updateVulnerability.setInt(10, vulnerabilityId);
                updateVulnerability.executeUpdate();
            }
        } else {
            final PreparedStatement insertVulnerability = getPreparedStatement(INSERT_VULNERABILITY);
            insertVulnerability.setString(1, vuln.getName());
            insertVulnerability.setString(2, vuln.getDescription());
            insertVulnerability.setString(3, vuln.getCwe());
            insertVulnerability.setFloat(4, vuln.getCvssScore());
            insertVulnerability.setString(5, vuln.getCvssAccessVector());
            insertVulnerability.setString(6, vuln.getCvssAccessComplexity());
            insertVulnerability.setString(7, vuln.getCvssAuthentication());
            insertVulnerability.setString(8, vuln.getCvssConfidentialityImpact());
            insertVulnerability.setString(9, vuln.getCvssIntegrityImpact());
            insertVulnerability.setString(10, vuln.getCvssAvailabilityImpact());
            insertVulnerability.execute();
            try {
                rs = insertVulnerability.getGeneratedKeys();
                rs.next();
                vulnerabilityId = rs.getInt(1);
            } catch (SQLException ex) {
                final String msg = String.format("Unable to retrieve id for new vulnerability for '%s'",
                        vuln.getName());
                throw new DatabaseException(msg, ex);
            } finally {
                DBUtils.closeResultSet(rs);
            }
        }

        PreparedStatement insertReference = getPreparedStatement(INSERT_REFERENCE);
        int countReferences = 0;
        for (Reference r : vuln.getReferences()) {
            insertReference.setInt(1, vulnerabilityId);
            insertReference.setString(2, r.getName());
            insertReference.setString(3, r.getUrl());
            insertReference.setString(4, r.getSource());
            if (isBatchInsertEnabled()) {
                insertReference.addBatch();
                countReferences++;
                if (countReferences % getBatchSize() == 0) {
                    insertReference.executeBatch();
                    insertReference = getPreparedStatement(INSERT_REFERENCE);
                    LOGGER.trace(getLogForBatchInserts(countReferences,
                            "Completed %s batch inserts to references table: %s"));
                    countReferences = 0;
                } else if (countReferences == vuln.getReferences().size()) {
                    if (LOGGER.isTraceEnabled()) {
                        LOGGER.trace(getLogForBatchInserts(countReferences,
                                "Completed %s batch inserts to reference table: %s"));
                    }
                    insertReference.executeBatch();
                    countReferences = 0;
                }
            } else {
                insertReference.execute();
            }
        }

        PreparedStatement insertSoftware = getPreparedStatement(INSERT_SOFTWARE);
        int countSoftware = 0;
        for (VulnerableSoftware vulnerableSoftware : vuln.getVulnerableSoftware()) {
            int cpeProductId = 0;
            final PreparedStatement selectCpeId = getPreparedStatement(SELECT_CPE_ID);
            selectCpeId.setString(1, vulnerableSoftware.getName());
            try {
                rs = selectCpeId.executeQuery();
                if (rs.next()) {
                    cpeProductId = rs.getInt(1);
                }
            } catch (SQLException ex) {
                throw new DatabaseException(
                        "Unable to get primary key for new cpe: " + vulnerableSoftware.getName(), ex);
            } finally {
                DBUtils.closeResultSet(rs);
            }

            if (cpeProductId == 0) {
                final PreparedStatement insertCpe = getPreparedStatement(INSERT_CPE);
                insertCpe.setString(1, vulnerableSoftware.getName());
                insertCpe.setString(2, vulnerableSoftware.getVendor());
                insertCpe.setString(3, vulnerableSoftware.getProduct());
                insertCpe.executeUpdate();
                cpeProductId = DBUtils.getGeneratedKey(insertCpe);
            }
            if (cpeProductId == 0) {
                throw new DatabaseException("Unable to retrieve cpeProductId - no data returned");
            }

            insertSoftware.setInt(1, vulnerabilityId);
            insertSoftware.setInt(2, cpeProductId);

            if (vulnerableSoftware.getPreviousVersion() == null) {
                insertSoftware.setNull(3, java.sql.Types.VARCHAR);
            } else {
                insertSoftware.setString(3, vulnerableSoftware.getPreviousVersion());
            }
            if (isBatchInsertEnabled()) {
                insertSoftware.addBatch();
                countSoftware++;
                if (countSoftware % getBatchSize() == 0) {
                    executeBatch(vuln, vulnerableSoftware, insertSoftware);
                    insertSoftware = getPreparedStatement(INSERT_SOFTWARE);
                    LOGGER.trace(getLogForBatchInserts(countSoftware,
                            "Completed %s batch inserts software table: %s"));
                    countSoftware = 0;
                } else if (countSoftware == vuln.getVulnerableSoftware().size()) {
                    if (LOGGER.isTraceEnabled()) {
                        LOGGER.trace(getLogForBatchInserts(countSoftware,
                                "Completed %s batch inserts software table: %s"));
                        countReferences = 0;
                    }
                    executeBatch(vuln, vulnerableSoftware, insertSoftware);
                }
            } else {
                try {
                    insertSoftware.execute();
                } catch (SQLException ex) {
                    if (ex.getMessage().contains("Duplicate entry")) {
                        final String msg = String.format("Duplicate software key identified in '%s:%s'",
                                vuln.getName(), vuln.getName());
                        LOGGER.info(msg, ex);
                    } else {
                        throw ex;
                    }
                }
            }
        }
    } catch (SQLException ex) {
        final String msg = String.format("Error updating '%s'", vuln.getName());
        LOGGER.debug(msg, ex);
        throw new DatabaseException(msg, ex);
    } finally {
        DBUtils.closeResultSet(rs);
    }
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.PolicyDAOImpl.java

@Override
public Criterion addCriterion(Criterion criteria) throws PolicyManagerDAOException {

    Connection conn;/*  www  .ja  v a2 s.  c  om*/
    PreparedStatement stmt = null;
    ResultSet generatedKeys;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();

    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_CRITERIA (TENANT_ID, NAME) VALUES (?, ?)";
        stmt = conn.prepareStatement(query, new String[] { "id" });
        stmt.setInt(1, tenantId);
        stmt.setString(2, criteria.getName());
        stmt.executeUpdate();

        generatedKeys = stmt.getGeneratedKeys();
        while (generatedKeys.next()) {
            criteria.setId(generatedKeys.getInt(1));
        }
    } catch (SQLException e) {
        throw new PolicyManagerDAOException(
                "Error occurred while inserting the criterion (" + criteria.getName() + ") to database.", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, null);
    }
    return criteria;
}

From source file:com.wso2telco.dep.ratecardservice.dao.RateDefinitionDAO.java

public RateDefinitionDTO addRateDefinition(RateDefinitionDTO rateDefinition) throws BusinessException {

    Connection con = null;// ww w .  ja  v a  2s  .c o m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer rateDefId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.RATE_DEF.getTObject());
        query.append(
                " (rate_defname, rate_defdesc, rate_defdefault, currencyid, rate_typeid, rate_defcategorybase, tariffid, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?, ?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addRateDefinition : " + ps);

        ps.setString(1, rateDefinition.getRateDefName());
        ps.setString(2, rateDefinition.getRateDefDescription());
        ps.setInt(3, rateDefinition.getRateDefDefault());
        ps.setInt(4, rateDefinition.getCurrency().getCurrencyId());
        ps.setInt(5, rateDefinition.getRateType().getRateTypeId());
        ps.setInt(6, rateDefinition.getRateDefCategoryBase());
        ps.setInt(7, rateDefinition.getTariff().getTariffId());
        ps.setString(8, rateDefinition.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            rateDefId = rs.getInt(1);
        }

        rateDefinition.setRateDefId(rateDefId);
    } catch (SQLException e) {

        log.error("database operation error in addRateDefinition : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addRateDefinition : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return rateDefinition;
}

From source file:org.apache.ode.bpel.extvar.jdbc.JdbcExternalVariableModule.java

RowKey execInsert(DbExternalVariable dbev, Locator locator, RowKey keys, RowVal values) throws SQLException {
    Connection conn = dbev.dataSource.getConnection();
    PreparedStatement stmt = null;
    try {/*w w  w.j  a  va2s . com*/
        if (__log.isDebugEnabled()) {
            __log.debug("execInsert: keys=" + keys + " values=" + values);
            __log.debug("Prepare statement: " + dbev.insert);
            __log.debug("missingDatabaseGeneratedValues: " + keys.missingDatabaseGeneratedValues());
            __log.debug("_autoColNames: " + ObjectPrinter.stringifyNvList(dbev._autoColNames));
        }

        stmt = keys.missingDatabaseGeneratedValues() ? conn.prepareStatement(dbev.insert, dbev._autoColNames)
                : conn.prepareStatement(dbev.insert);

        int idx = 1;
        for (Column c : dbev._inscolumns) {
            Object val = c.getValue(c.name, keys, values, locator.iid);
            values.put(c.name, val);
            if (__log.isDebugEnabled())
                __log.debug("Set parameter " + idx + ": " + val);
            if (val == null)
                stmt.setNull(idx, c.dataType);
            else
                stmt.setObject(idx, val);
            idx++;
        }

        stmt.execute();

        for (Column ck : keys._columns) {
            Object val = values.get(ck.name);
            if (__log.isDebugEnabled())
                __log.debug("Key " + ck.name + ": " + val);
            keys.put(ck.name, val);
        }

        if (keys.missingDatabaseGeneratedValues()) {
            // With JDBC 3, we can get the values of the key columns (if the db supports it)
            ResultSet keyRS = stmt.getGeneratedKeys();
            try {
                if (keyRS == null)
                    throw new SQLException("Database did not return generated keys");
                keyRS.next();
                for (Column ck : keys._columns) {
                    Object value = keyRS.getObject(ck.idx + 1);
                    if (__log.isDebugEnabled())
                        __log.debug("Generated key " + ck.name + ": " + value);
                    keys.put(ck.name, value);
                }
            } finally {
                keyRS.close();
            }
        }
        return keys;
    } finally {
        if (stmt != null)
            stmt.close();
        try {
            conn.close();
        } catch (SQLException e) {
            // ignore
        }
    }
}

From source file:org.jumpmind.db.sql.JdbcSqlTemplate.java

protected long insertWithGeneratedKey(Connection conn, String sql, String column, String sequenceName,
        Object[] args, int[] types) throws SQLException {
    long key = 0;
    PreparedStatement ps = null;
    try {//  w  ww  .  ja  v  a 2  s.c  o m
        boolean supportsGetGeneratedKeys = supportsGetGeneratedKeys();
        boolean supportsReturningKeys = supportsReturningKeys();
        if (allowsNullForIdentityColumn()) {
            if (supportsGetGeneratedKeys) {
                ps = conn.prepareStatement(sql, new int[] { 1 });
            } else if (supportsReturningKeys) {
                ps = conn.prepareStatement(sql + " returning " + column);
            } else {
                ps = conn.prepareStatement(sql);
            }
        } else {
            String replaceSql = sql.replaceFirst("\\([\"|\\w]*,", "(").replaceFirst("\\(null,", "(");
            if (supportsGetGeneratedKeys) {
                ps = conn.prepareStatement(replaceSql, Statement.RETURN_GENERATED_KEYS);
            } else {
                ps = conn.prepareStatement(replaceSql);
            }
        }
        ps.setQueryTimeout(settings.getQueryTimeout());
        setValues(ps, args, types, lobHandler.getDefaultHandler());

        ResultSet rs = null;
        if (supportsGetGeneratedKeys) {
            ps.execute();
            try {
                rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    key = rs.getLong(1);
                }
            } finally {
                close(rs);
            }
        } else if (supportsReturningKeys) {
            try {
                rs = ps.executeQuery();
                if (rs.next()) {
                    key = rs.getLong(1);
                }
            } finally {
                close(rs);
            }
        } else {
            Statement st = null;
            ps.execute();
            try {
                st = conn.createStatement();
                rs = st.executeQuery(getSelectLastInsertIdSql(sequenceName));
                if (rs.next()) {
                    key = rs.getLong(1);
                }
            } finally {
                close(rs);
                close(st);
            }
        }
    } finally {
        close(ps);
    }
    return key;
}