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.cyrusbuilt.guncabinet.dao.DbEngine.java

/**
 * /*from w ww.ja  v  a  2  s.  c  o m*/
 * @param caliber
 * @exception IllegalArgumentException
 * @throws ObjectDisposedException
 * @throws SQLException
 * @throws DbDriverNotFoundException
 */
public void insertOrUpdateCaliber(ChamberCaliberEntity caliber)
        throws ObjectDisposedException, SQLException, DbDriverNotFoundException {
    if (caliber == null) {
        throw new IllegalArgumentException("caliber cannot be null.");
    }

    if (_isDisposed) {
        throw new ObjectDisposedException(this);
    }

    if (!this._isConnected) {
        this.connect();
    }

    DateTime start = this.beginUpdate(Tables.ChamberCaliber);
    this._conn.setAutoCommit(false);
    Savepoint sp = this._conn.setSavepoint();
    PreparedStatement ps = null;
    Integer result = 0;
    Integer id = 0;
    if (this.caliberExists(caliber.getId())) {
        // Record already exists, so we'll just update the values.
        String update = "update " + this._dbName + "." + DbUtils.getTableName(Tables.ChamberCaliber)
                + " set Name='" + caliber.name() + "', Notes='" + caliber.notes() + "', IsNATO='"
                + caliber.isNATO().toString() + "' where Id='" + caliber.getId().toString() + "'";
        ps = this._conn.prepareStatement(update);
        result = ps.executeUpdate();
        id = caliber.getId();
    } else {
        // This is a new record.
        String insert = "insert into " + this._dbName + "." + DbUtils.getTableName(Tables.ChamberCaliber)
                + " values (NUll, ?, ?, ?)";
        ps = this._conn.prepareStatement(insert, PreparedStatement.RETURN_GENERATED_KEYS);
        ps.setString(1, caliber.name());
        ps.setString(2, caliber.notes());
        ps.setString(3, caliber.isNATO().toString());
        result = ps.executeUpdate();

        // Get the ID of the record that was just inserted.
        ResultSet rs = ps.getGeneratedKeys();
        if (rs.next()) {
            id = rs.getInt(1);
        }
    }

    if ((result == PreparedStatement.EXECUTE_FAILED) || (id <= 0)) {
        this._conn.rollback(sp);
        this.notifyTableUpdateFailedListeners(new TableUpdateFailedEventArgs(Tables.ChamberCaliber, id));
    }
    this._conn.setAutoCommit(true);
    this.endUpdate(Tables.ChamberCaliber, start, id);
}

From source file:com.wso2telco.dep.mediator.dao.SMSMessagingDAO.java

public Integer subscriptionEntry(String notifyURL) throws SQLException, Exception {

    Connection con = null;//  w  w w .  ja  v a2 s. c  om
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer newId = 0;

    try {

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

            throw new Exception("Connection not found");
        }

        StringBuilder insertQueryString = new StringBuilder("INSERT INTO ");
        insertQueryString.append(DatabaseTables.SUBSCRIPTIONS.getTableName());
        insertQueryString.append(" (notifyurl, is_active) ");
        insertQueryString.append("VALUES (?, ?)");

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

        ps.setString(1, notifyURL);
        ps.setInt(2, 0);

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

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            newId = rs.getInt(1);
        }
    } catch (SQLException e) {

        log.error("database operation error in subscriptionEntry : ", e);
        throw e;
    } catch (Exception e) {

        log.error("error in subscriptionEntry : ", e);
        throw e;
    } finally {

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

    return newId;
}

From source file:com.webpagebytes.wpbsample.database.WPBDatabase.java

public User createUser(User user) throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statementUser = null;
    PreparedStatement statementAccount = null;
    try {/*from w ww .j  a v  a 2 s  .co m*/
        statementUser = connection.prepareStatement(CREATE_USER_STATEMENT);
        connection.setAutoCommit(false);

        statementUser.setString(1, user.getUserName());
        statementUser.setString(2, user.getEmail());
        statementUser.setString(3, user.getPassword());
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(user.getOpen_date().getTime());
        statementUser.setTimestamp(4, sqlDate);
        statementUser.setInt(5, user.getReceiveNewsletter());
        statementUser.setInt(6, user.getConfirmEmailFlag());
        statementUser.setString(7, user.getConfirmEmailRandom());
        java.sql.Timestamp sqlDate1 = new java.sql.Timestamp(user.getConfirmEmailDate().getTime());
        statementUser.setTimestamp(8, sqlDate1);
        statementUser.execute();

        ResultSet rs = statementUser.getGeneratedKeys();
        if (rs.next()) {
            int id = rs.getInt(1);
            user.setId(id);
        }
        statementAccount = connection.prepareStatement(CREATE_ACCOUNT_STATEMENT);
        statementAccount.setInt(1, user.getId());
        statementAccount.setLong(2, 0L);
        statementAccount.execute();
        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            connection.rollback();
        }
        throw e;
    } finally {
        if (statementUser != null) {
            statementUser.close();
        }
        if (statementAccount != null) {
            statementAccount.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
    return user;
}

From source file:org.meerkat.services.WebApp.java

/**
 * addEvent/*from ww  w .j  a v a  2s. c o  m*/
 * 
 * @param event
 */
public final void addEvent(WebAppEvent ev) {
    if (ev.getCurrentResponse().length() > EmbeddedDB.EVENT_MAX_RESPONSE_LENGTH) {
        // truncate the size of response
        ev.setCurrentResponse(ev.getCurrentResponse().substring(0, EmbeddedDB.EVENT_MAX_RESPONSE_LENGTH));
        log.warn("Response of " + this.getName() + " bigger than " + EmbeddedDB.EVENT_MAX_RESPONSE_LENGTH
                + " chars (truncated!).");
    }

    // Get the event ID
    PreparedStatement pstat;
    ResultSet rs = null;
    int evID = -1;
    try {
        pstat = conn.prepareStatement("SELECT ID FROM MEERKAT.EVENTS_RESPONSE WHERE APPNAME = '"
                + this.getName() + "' AND RESPONSE LIKE ?");
        pstat.setString(1, ev.getCurrentResponse());
        rs = pstat.executeQuery();
        while (rs.next()) {
            evID = rs.getInt(1);
        }
        rs.close();
        pstat.close();
    } catch (SQLException e) {
        log.error("Failed query events response existence from DB ");
        log.error("", e);
    }

    if (evID < 0) { // No equal event exists, so add a new one
        PreparedStatement pstatAddEv;
        String queryInsertNewEv = "INSERT INTO MEERKAT.EVENTS_RESPONSE(APPNAME, RESPONSE) VALUES('"
                + this.getName() + "', ?) ";
        try {
            pstatAddEv = conn.prepareStatement(queryInsertNewEv, Statement.RETURN_GENERATED_KEYS);
            pstatAddEv.setString(1, ev.getCurrentResponse().toString());
            pstatAddEv.execute();

            ResultSet generatedKeys = pstatAddEv.getGeneratedKeys();
            if (generatedKeys.next()) {
                evID = (int) generatedKeys.getLong(1);
            } else {
                log.error("Error inserting event response, no generated key obtained.");
                //throw new SQLException("Error inserting event response, no generated key obtained.");
            }

            pstatAddEv.close();
            conn.commit();

        } catch (SQLException e) {
            log.error("Failed to insert event response into DB for app: " + this.getName() + "! - "
                    + e.getMessage());
            //log.error("INSERT DATA IS:"+ev.getCurrentResponse());
            //EmbeddedDB.logSQLException(e);
        }
    }

    // Add the event referencing the event response id
    PreparedStatement statement;
    String queryInsert = "INSERT INTO MEERKAT.EVENTS(APPNAME, CRITICAL, DATEEV, ONLINE, AVAILABILITY, LOADTIME, LATENCY, HTTPSTATUSCODE, DESCRIPTION, RESPONSE_ID) VALUES(";

    String queryValues = "'" + this.getName() + "', " + ev.isCritical() + ", '" + ev.getDate() + "', '"
            + ev.getStatus() + "', " + Double.valueOf(this.getAvailability()) + ", "
            + Double.valueOf(ev.getPageLoadTime()) + ", ";

    // Handle latency - may be null if host not available)
    if (ev.getLatency() == null) {
        queryValues += null;
    } else {
        queryValues += ev.getLatency();
    }

    queryValues += ", " + Integer.valueOf(ev.getHttpStatusCode()) + ", '" + ev.getDescription() + "', " + evID
            + "";

    try {
        statement = conn.prepareStatement(queryInsert + queryValues + ")");
        //statement.setInt(1, evID);
        statement.execute();
        statement.close();
        conn.commit();
    } catch (SQLException e) {
        log.error("Failed to insert event into DB! - " + e.getMessage());
    }

    this.writeWebAppVisualizationDataFile();
}

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

/**
 * Method to persist tags.//w  w w.j  a  va2  s.c  o  m
 *
 * @param resource   the resource
 * @param taggingDOs the tags to be persisted.
 *
 * @throws RegistryException if some error occurs while adding tags
 */
public void addTaggings(ResourceImpl resource, TaggingDO[] taggingDOs) throws RegistryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    long now = System.currentTimeMillis();

    for (TaggingDO taggingDO : taggingDOs) {
        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        ResultSet result = null;
        try {
            String sql = "INSERT INTO REG_TAG (REG_TAG_NAME, REG_USER_ID, REG_TAGGED_TIME, "
                    + "REG_TENANT_ID) VALUES (?,?,?,?)";

            String dbProductName = conn.getMetaData().getDatabaseProductName();
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_ID") });
            ps.setString(1, taggingDO.getTagName());
            ps.setString(2, taggingDO.getTaggedUserName());
            ps.setDate(3, new Date(now));
            ps.setInt(4, CurrentSession.getTenantId());

            ps.executeUpdate();

            result = ps.getGeneratedKeys();
            if (result.next()) {
                int tagId = result.getInt(1);
                String sql2 = "INSERT INTO REG_RESOURCE_TAG (REG_TAG_ID, REG_VERSION, "
                        + "REG_TENANT_ID) VALUES(?,?,?)";
                ps2 = conn.prepareStatement(sql2);

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

                ps2.executeUpdate();
            }
        } catch (SQLException e) {
            String msg = "Failed to add tags to resource " + resource.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            // closing open prepared statements & result sets before moving on to next iteration
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    try {
                        if (ps != null) {
                            ps.close();
                        }
                    } finally {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }
}

From source file:lib.JdbcTemplate.java

@Override
public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
        throws DataAccessException {

    Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
    logger.debug("Executing SQL update and returning generated keys");

    return execute(psc, new PreparedStatementCallback<Integer>() {
        @Override// ww  w  .ja  v a2  s  .c o  m
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
            int rows = ps.executeUpdate();
            List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList();
            generatedKeys.clear();
            ResultSet keys = ps.getGeneratedKeys();
            if (keys != null) {
                try {
                    RowMapperResultSetExtractor<Map<String, Object>> rse = new RowMapperResultSetExtractor<Map<String, Object>>(
                            getColumnMapRowMapper(), 1);
                    generatedKeys.addAll(rse.extractData(keys));
                } finally {
                    JdbcUtils.closeResultSet(keys);
                }
            }
            if (logger.isDebugEnabled()) {
                logger.debug(
                        "SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
            }
            return rows;
        }
    });
}

From source file:org.wso2.carbon.appmgt.expiring.subscription.impl.dao.AppMSubscriptionExtensionDAO.java

public int addSubscription(WorkflowDTO workflowDTO) throws AppManagementException {

    SubscriptionExpiryDTO subscriptionExpiryDTO = null;
    if (workflowDTO instanceof SubscriptionExpiryDTO) {
        subscriptionExpiryDTO = (SubscriptionExpiryDTO) workflowDTO;
    } else {/* w  w  w  .  ja v  a2s .c o m*/
        throw new AppManagementException("Error in casting....");
    }
    Connection connection = null;
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;
    AppMDAO appMDAO = new AppMDAO();
    int subscriptionId = -1;

    try {
        connection = APIMgtDBUtil.getConnection();
        int appId = appMDAO.getAPIID(new APIIdentifier(subscriptionExpiryDTO.getApiProvider(),
                subscriptionExpiryDTO.getApiName(), subscriptionExpiryDTO.getApiVersion()), connection);
        int subscriberId = appMDAO.getSubscriber(subscriptionExpiryDTO.getSubscriber()).getId();

        String sqlQuery = "INSERT INTO APM_SUBSCRIPTION_EXT (APP_ID, SUBSCRIBER_ID, SUBSCRIPTION_TYPE, "
                + "SUBSCRIPTION_TIME, EVALUATION_PERIOD, EXPIRED_ON) VALUES (?,?,?,?,?,?)";

        // Adding data to the APM_SUBSCRIPTION_EXT table.
        preparedStatement = connection.prepareStatement(sqlQuery, new String[] { "SUBSCRIPTION_ID" });
        if (connection.getMetaData().getDriverName().contains("PostgreSQL")) {
            preparedStatement = connection.prepareStatement(sqlQuery, new String[] { "subscription_id" });
        }

        preparedStatement.setInt(1, appId);
        preparedStatement.setInt(2, subscriberId);
        preparedStatement.setString(3, subscriptionExpiryDTO.getSubscriptionType());
        preparedStatement.setTimestamp(4, new Timestamp(subscriptionExpiryDTO.getSubscriptionTime().getTime()));
        preparedStatement.setInt(5, subscriptionExpiryDTO.getEvaluationPeriod());
        preparedStatement.setTimestamp(6, new Timestamp(subscriptionExpiryDTO.getExpireOn().getTime()));

        preparedStatement.executeUpdate();
        ResultSet rs = preparedStatement.getGeneratedKeys();
        while (rs.next()) {
            subscriptionId = rs.getInt(1);
        }
        preparedStatement.close();
        connection.commit();

    } catch (SQLException e) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback the add subscription ", e);
            }
        }
        handleException("Failed to add subscriber data ", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(preparedStatement, connection, resultSet);
    }
    return subscriptionId;
}

From source file:com.pinterest.pinlater.backends.mysql.PinLaterMySQLBackend.java

@Override
protected String enqueueSingleJob(String queueName, PinLaterJob job, int numAutoRetries) throws Exception {
    final long currentTimeMillis = System.currentTimeMillis();
    Connection conn = null;/*from  www  .ja va 2s  . c om*/
    PreparedStatement stmt = null;
    ResultSet rs = null;
    final ImmutableMap.Entry<String, MySQLDataSources> shard = getRandomEnqueueableShard();
    try {
        conn = shard.getValue().getGeneralDataSource().getConnection();
        String jobsTableName = MySQLBackendUtils.constructJobsTableName(queueName, shard.getKey(),
                job.getPriority());
        stmt = conn.prepareStatement(String.format(MySQLQueries.ENQUEUE_INSERT, jobsTableName),
                Statement.RETURN_GENERATED_KEYS);
        stmt.setInt(1, PinLaterJobState.PENDING.getValue());
        stmt.setInt(2, job.getNumAttemptsAllowed());
        stmt.setInt(3, job.getNumAttemptsAllowed());
        stmt.setString(4, job.getCustomStatus());
        stmt.setTimestamp(5, new Timestamp(currentTimeMillis));
        stmt.setTimestamp(6, new Timestamp(
                job.isSetRunAfterTimestampMillis() ? job.getRunAfterTimestampMillis() : currentTimeMillis));
        stmt.setBytes(7, job.getBody());
        stmt.executeUpdate();
        rs = stmt.getGeneratedKeys();
        rs.next();
        return new PinLaterJobDescriptor(queueName, shard.getKey(), job.getPriority(), rs.getLong(1))
                .toString();
    } catch (SQLException e) {
        boolean shouldRetry = checkExceptionIsRetriable(e, shard.getKey(), "enqueue");
        if (shouldRetry && numAutoRetries > 0) {
            // Retry the enqueue, potentially on a different shard.
            Stats.incr("enqueue-failures-retry");
            return enqueueSingleJob(queueName, job, numAutoRetries - 1);
        }
        // Out of retries, throw the exception. Wrap it into a PinLaterException if the exception
        // is recognized and return the appropriate error code.
        if (MySQLBackendUtils.isDatabaseDoesNotExistException(e)) {
            throw new PinLaterException(ErrorCode.QUEUE_NOT_FOUND, "Queue not found: " + queueName);
        }
        throw e;
    } finally {
        JdbcUtils.closeResultSet(rs);
        JdbcUtils.closeStatement(stmt);
        JdbcUtils.closeConnection(conn);
    }
}

From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java

public boolean insertContent(String folder, Object key, Object bean) throws WGBackendException {

    ResultSet resultSet = null;// w  w  w .  j  a v  a 2 s  .  c o  m
    try {

        // Gather all columns to set as values, including those keys that are set. Find out the key to generate, if present.
        Map allColumns = new HashMap();
        Map valuesMap = (Map) bean;
        allColumns.putAll(valuesMap);
        String keyToGenerate = null;
        Map keyMap = (Map) key;
        Iterator keys = keyMap.keySet().iterator();
        while (keys.hasNext()) {
            String keyName = (String) keys.next();
            Object value = keyMap.get(keyName);
            if (value != null) {
                allColumns.put(keyName, value);
            } else {
                keyToGenerate = keyName;
            }
        }

        // Execute Statement
        PreparedStatement stmt = getInsertStatement(folder, allColumns);
        int rows = stmt.executeUpdate();
        if (rows != 1) {
            throw new WGBackendException("Insert failed. Wrong number of inserted rows returned: " + rows);
        }

        if (keyToGenerate == null) {
            return true;
        }

        // Try to retrieve generated key and store it at the bean and the key map
        if (!stmt.getConnection().getMetaData().supportsGetGeneratedKeys()) {
            throw new WGBackendException(
                    "Row was inserted but JDBC Driver does not support returning of generated keys. Usage of a table with generated key is not possible with this driver.");
        }
        ResultSet generatedKeys = stmt.getGeneratedKeys();
        generatedKeys.first();
        Object generatedKey = generatedKeys.getObject(1);
        valuesMap.put(keyToGenerate, generatedKey);
        keyMap.put(keyToGenerate, generatedKey);

        if (getConnection().getAutoCommit() == false) {
            getConnection().commit();
        }

        return true;

        /*String whereClause = getWhereClause(folder, key);
        resultSet = getTableResultSet(folder, whereClause, true);
                
        if (resultSet != null) {
        startResultSet(resultSet);
          if (!resultSet.next()) {
             resultSet.moveToInsertRow();
             pushRowData(resultSet, (Map) bean);
             resultSet.insertRow();
             if (getConnection().getAutoCommit() == false) {
           getConnection().commit();
             }
             return true;
          }
          else {
             throw new WGBackendException("The key '" + key + "' already exists in table '" + folder + "'");
          }
        }
        else {
          return false;
        }*/

    } catch (SQLException e) {
        try {
            if (getConnection().getAutoCommit() == false) {
                getConnection().rollback();
            }
        } catch (SQLException e1) {
            Logger.getLogger(LOGGER_NAME).error("Error rolling back content insertion", e);
        }
        throw new WGBackendException("Error inserting row", e);
    } finally {
        closeResultSet(resultSet);
    }

}

From source file:com.wso2telco.dep.mediator.dao.SMSMessagingDAO.java

public Integer outboundSubscriptionEntry(String notifyURL, String serviceProvider)
        throws SQLException, Exception {

    Connection con = null;//from   w ww .j  a  v a2  s.  c  om
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer newId = 0;

    try {

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

            throw new Exception("Connection not found");
        }

        StringBuilder insertQueryString = new StringBuilder("INSERT INTO ");
        insertQueryString.append(DatabaseTables.OUTBOUND_SUBSCRIPTIONS.getTableName());
        insertQueryString.append(" (notifyurl, service_provider, is_active) ");
        insertQueryString.append("VALUES (?, ?, ?)");

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

        ps.setString(1, notifyURL);
        ps.setString(2, serviceProvider);
        ps.setInt(3, 0);

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

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            newId = rs.getInt(1);
        }
    } catch (SQLException e) {

        log.error("database operation error in outboundSubscriptionEntry : ", e);
        throw e;
    } catch (Exception e) {

        log.error("error in outboundSubscriptionEntry : ", e);
        throw e;
    } finally {

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

    return newId;
}