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:com.feedzai.commons.sql.abstraction.engine.impl.MySqlEngine.java

@Override
public synchronized Long persist(String name, EntityEntry entry, boolean useAutoInc)
        throws DatabaseEngineException {
    ResultSet generatedKeys = null;
    try {//from  w  ww. j a v a2  s .  c  om
        getConnection();

        final MappedEntity me = entities.get(name);

        if (me == null) {
            throw new DatabaseEngineException(String.format("Unknown entity '%s'", name));
        }

        PreparedStatement ps = null;
        if (useAutoInc) {
            ps = entities.get(name).getInsert();
        } else {
            ps = entities.get(name).getInsertWithAutoInc();
        }

        entityToPreparedStatement(me.getEntity(), ps, entry, useAutoInc);

        ps.execute();

        long ret = 0;
        if (useAutoInc) {
            generatedKeys = ps.getGeneratedKeys();

            if (generatedKeys.next()) {
                ret = generatedKeys.getLong(1);
            }

            generatedKeys.close();
        }

        return ret == 0 ? null : ret;
    } catch (Exception ex) {
        throw new DatabaseEngineException("Something went wrong persisting the entity", ex);
    } finally {
        try {
            if (generatedKeys != null) {
                generatedKeys.close();
            }
        } catch (Exception e) {
            logger.trace("Error closing result set.", e);
        }
    }
}

From source file:net.mindengine.oculus.frontend.service.test.JdbcTestDAO.java

private Long createTestParameter(TestParameter parameter) throws Exception {
    PreparedStatement ps = getConnection().prepareStatement(
            "insert into test_parameters (name,description, type, control_type, default_value, possible_values, test_id) "
                    + "values (?,?,?,?,?,?,?)");
    ps.setString(1, parameter.getName());
    ps.setString(2, parameter.getDescription());
    ps.setString(3, parameter.getType());
    ps.setString(4, parameter.getControlType());
    ps.setString(5, parameter.getDefaultValue());
    ps.setString(6, parameter.getPossibleValues());
    ps.setLong(7, parameter.getTestId());

    logger.info(ps);// w ww.  j a  v a  2s .  c  o  m
    ps.executeUpdate();

    ResultSet rs = ps.getGeneratedKeys();
    Long parameterId = null;
    if (rs.next()) {
        parameterId = rs.getLong(1);
    }
    return parameterId;
}

From source file:org.apache.stratos.adc.mgt.utils.PersistenceManager.java

public static int persistSubscription(CartridgeSubscription cartridgeSubscription) throws Exception {

    int cartridgeSubscriptionId = 0;
    int repoId = 0;
    int dataCartridgeId = 0;
    ResultSet res = null;//  w w w .ja v  a 2 s. c om
    PreparedStatement insertSubscriptionStmt = null;
    PreparedStatement insertRepoStmt = null;
    PreparedStatement insertDataCartStmt = null;

    Connection con = null;

    // persist cartridge_subscription
    try {
        con = StratosDBUtils.getConnection();
        // persist repo
        if (cartridgeSubscription.getRepository() != null) {
            String encryptedRepoUserPassword = encryptPassword(
                    cartridgeSubscription.getRepository().getRepoUserPassword());
            String insertRepo = "INSERT INTO REPOSITORY (REPO_NAME,STATE,REPO_USER_NAME,REPO_USER_PASSWORD)"
                    + " VALUES (?,?,?,?)";

            insertRepoStmt = con.prepareStatement(insertRepo, Statement.RETURN_GENERATED_KEYS);
            insertRepoStmt.setString(1, cartridgeSubscription.getRepository().getRepoName());
            insertRepoStmt.setString(2, "ACTIVE");
            insertRepoStmt.setString(3, cartridgeSubscription.getRepository().getRepoUserName());
            insertRepoStmt.setString(4, encryptedRepoUserPassword);
            if (log.isDebugEnabled()) {
                log.debug("Executing insert: " + insertRepo);
            }
            insertRepoStmt.executeUpdate();
            res = insertRepoStmt.getGeneratedKeys();
            if (res.next()) {
                repoId = res.getInt(1);
            }
            StratosDBUtils.closeResultSet(res);
        }

        // persist data cartridge
        if (cartridgeSubscription.getDataCartridge() != null) {
            String insertDataCartridge = "INSERT INTO DATA_CARTRIDGE (TYPE,USER_NAME,PASSWORD,STATE)"
                    + " VALUES (?,?,?,?)";
            insertDataCartStmt = con.prepareStatement(insertDataCartridge, Statement.RETURN_GENERATED_KEYS);
            insertDataCartStmt.setString(1, cartridgeSubscription.getDataCartridge().getDataCartridgeType());
            insertDataCartStmt.setString(2, cartridgeSubscription.getDataCartridge().getUserName());
            insertDataCartStmt.setString(3, cartridgeSubscription.getDataCartridge().getPassword());
            insertDataCartStmt.setString(4, "ACTIVE");
            if (log.isDebugEnabled()) {
                log.debug("Executing insert: " + insertDataCartridge);
            }
            insertDataCartStmt.executeUpdate();
            res = insertDataCartStmt.getGeneratedKeys();
            if (res.next()) {
                dataCartridgeId = res.getInt(1);
            }
            StratosDBUtils.closeResultSet(res);
        }

        String insertSubscription = "INSERT INTO CARTRIDGE_SUBSCRIPTION (TENANT_ID, CARTRIDGE, PROVIDER,"
                + "HOSTNAME, POLICY, CLUSTER_DOMAIN, " + "CLUSTER_SUBDOMAIN, MGT_DOMAIN, MGT_SUBDOMAIN, STATE, "
                + "ALIAS, TENANT_DOMAIN, BASE_DIR, REPO_ID, DATA_CARTRIDGE_ID)"
                + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        insertSubscriptionStmt = con.prepareStatement(insertSubscription, Statement.RETURN_GENERATED_KEYS);
        insertSubscriptionStmt.setInt(1, cartridgeSubscription.getTenantId());
        insertSubscriptionStmt.setString(2, cartridgeSubscription.getCartridge());
        insertSubscriptionStmt.setString(3, cartridgeSubscription.getProvider());
        insertSubscriptionStmt.setString(4, cartridgeSubscription.getHostName());
        insertSubscriptionStmt.setString(5, cartridgeSubscription.getPolicy());
        insertSubscriptionStmt.setString(6, cartridgeSubscription.getClusterDomain());
        insertSubscriptionStmt.setString(7, cartridgeSubscription.getClusterSubdomain());
        insertSubscriptionStmt.setString(8, cartridgeSubscription.getMgtClusterDomain());
        insertSubscriptionStmt.setString(9, cartridgeSubscription.getMgtClusterSubDomain());
        insertSubscriptionStmt.setString(10, cartridgeSubscription.getState());
        insertSubscriptionStmt.setString(11, cartridgeSubscription.getAlias());
        insertSubscriptionStmt.setString(12, cartridgeSubscription.getTenantDomain());
        insertSubscriptionStmt.setString(13, cartridgeSubscription.getBaseDirectory());
        insertSubscriptionStmt.setInt(14, repoId);
        insertSubscriptionStmt.setInt(15, dataCartridgeId);
        if (log.isDebugEnabled()) {
            log.debug("Executing insert: " + insertSubscription);
        }
        insertSubscriptionStmt.executeUpdate();
        res = insertSubscriptionStmt.getGeneratedKeys();
        if (res.next()) {
            cartridgeSubscriptionId = res.getInt(1);
        }

        List<PortMapping> portMapping = cartridgeSubscription.getPortMappings();
        // persist port map
        if (portMapping != null && !portMapping.isEmpty()) {
            for (PortMapping portMap : portMapping) {
                String insertPortMapping = "INSERT INTO PORT_MAPPING (SUBSCRIPTION_ID, TYPE, PRIMARY_PORT, PROXY_PORT, STATE)"
                        + " VALUES (?,?,?,?,?)";

                PreparedStatement insertPortsStmt = con.prepareStatement(insertPortMapping);
                insertPortsStmt.setInt(1, cartridgeSubscriptionId);
                insertPortsStmt.setString(2, portMap.getType());
                insertPortsStmt.setString(3, portMap.getPrimaryPort());
                insertPortsStmt.setString(4, portMap.getProxyPort());
                insertPortsStmt.setString(5, "ACTIVE");
                if (log.isDebugEnabled()) {
                    log.debug("Executing insert: " + insertPortMapping);
                }
                insertPortsStmt.executeUpdate();
                StratosDBUtils.closeStatement(insertPortsStmt);
            }
        }
        con.commit(); // Commit manually
    } catch (Exception e) {
        if (con != null) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback", e);
            }
        }
        ;
        log.error(e.getMessage());
        String msg = "Exception : " + e.getMessage();
        log.error(msg, e);
        throw new Exception("Subscription failed!", e);
    } finally {
        StratosDBUtils.closeResultSet(res);
        StratosDBUtils.closeAllConnections(con, insertRepoStmt, insertDataCartStmt, insertSubscriptionStmt);
    }
    return cartridgeSubscriptionId;
}

From source file:org.wso2.carbon.is.migration.dao.ClaimDAO.java

/**
 * Add claim//from www  . jav  a  2  s .  c o m
 *
 * @param connection
 * @param claimDialectURI
 * @param claimURI
 * @param tenantId
 * @return
 * @throws ISMigrationException
 */
public int addClaim(Connection connection, String claimDialectURI, String claimURI, int tenantId)
        throws ISMigrationException {

    PreparedStatement prepStmt = null;
    ResultSet rs = null;

    int claimId = 0;
    String query = SQLConstants.ADD_CLAIM;
    try {
        String dbProductName = connection.getMetaData().getDatabaseProductName();
        prepStmt = connection.prepareStatement(query, new String[] {
                DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, SQLConstants.ID_COLUMN) });

        prepStmt.setString(1, claimDialectURI);
        prepStmt.setInt(2, tenantId);
        prepStmt.setString(3, claimURI);
        prepStmt.setInt(4, tenantId);
        prepStmt.executeUpdate();

        rs = prepStmt.getGeneratedKeys();

        if (rs.next()) {
            claimId = rs.getInt(1);
        }
    } catch (SQLException e) {
        throw new ISMigrationException(
                "Error while adding claim " + claimURI + " to dialect " + claimDialectURI, e);
    } finally {
        IdentityDatabaseUtil.closeResultSet(rs);
        IdentityDatabaseUtil.closeStatement(prepStmt);
    }

    return claimId;
}

From source file:org.mskcc.cbio.portal.dao.DaoCancerStudy.java

/**
 * Adds a cancer study to the Database./*from w  w w .ja v  a2  s  . c  o  m*/
 * @param cancerStudy
 * @param overwrite if true, overwrite if exist.
 * @throws DaoException 
 */
public static void addCancerStudy(CancerStudy cancerStudy, boolean overwrite) throws DaoException {

    // make sure that cancerStudy refers to a valid TypeOfCancerId
    // TODO: have a foreign key constraint do this; why not?
    TypeOfCancer aTypeOfCancer = DaoTypeOfCancer.getTypeOfCancerById(cancerStudy.getTypeOfCancerId());
    if (null == aTypeOfCancer) {
        throw new DaoException("cancerStudy.getTypeOfCancerId() '" + cancerStudy.getTypeOfCancerId()
                + "' does not refer to a TypeOfCancer.");
    }

    // CANCER_STUDY_IDENTIFIER cannot be null
    String stableId = cancerStudy.getCancerStudyStableId();
    if (stableId == null) {
        throw new DaoException("Cancer study stable ID cannot be null.");
    }

    CancerStudy existing = getCancerStudyByStableId(stableId);
    if (existing != null) {
        if (overwrite) {
            //setStatus(Status.UNAVAILABLE, stableId);
            deleteCancerStudy(existing.getInternalId());
        } else {
            throw new DaoException("Cancer study " + stableId + "is already imported.");
        }
    }

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = JdbcUtil.getDbConnection(DaoCancerStudy.class);
        pstmt = con.prepareStatement(
                "INSERT INTO cancer_study " + "( `CANCER_STUDY_IDENTIFIER`, `NAME`, "
                        + "`DESCRIPTION`, `PUBLIC`, `TYPE_OF_CANCER_ID`, "
                        + "`PMID`, `CITATION`, `GROUPS`, `SHORT_NAME`, `STATUS` ) VALUES (?,?,?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, stableId);
        pstmt.setString(2, cancerStudy.getName());
        pstmt.setString(3, cancerStudy.getDescription());
        pstmt.setBoolean(4, cancerStudy.isPublicStudy());
        pstmt.setString(5, cancerStudy.getTypeOfCancerId());
        pstmt.setString(6, cancerStudy.getPmid());
        pstmt.setString(7, cancerStudy.getCitation());
        Set<String> groups = cancerStudy.getGroups();
        if (groups == null) {
            pstmt.setString(8, null);
        } else {
            pstmt.setString(8, StringUtils.join(groups, ";"));
        }
        pstmt.setString(9, cancerStudy.getShortName());
        //status is UNAVAILABLE until other data is loaded for this study. Once all is loaded, the 
        //data loading process can set this to AVAILABLE:
        //TODO - use this field in parts of the system that build up the list of studies to display in home page:
        pstmt.setInt(10, Status.UNAVAILABLE.ordinal());
        pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            int autoId = rs.getInt(1);
            cancerStudy.setInternalId(autoId);
        }

        cacheCancerStudy(cancerStudy, new java.util.Date());
    } catch (SQLException e) {
        throw new DaoException(e);
    } finally {
        JdbcUtil.closeAll(DaoCancerStudy.class, con, pstmt, rs);
    }

    reCacheAll();
}

From source file:net.mindengine.oculus.frontend.service.issue.JdbcIssueDAO.java

@Override
public long createIssue(Issue issue) throws Exception {
    PreparedStatement ps = getConnection().prepareStatement(
            "insert into issues (name, link, summary, description, author_id, date, fixed, fixed_date, project_id, subproject_id) "
                    + "values (?,?,?,?,?,?,?,?,?,?)");

    ps.setString(1, issue.getName());/*from  w w w .  j  a v a2s .  c o m*/
    ps.setString(2, issue.getLink());
    ps.setString(3, issue.getSummary());
    ps.setString(4, issue.getDescription());
    ps.setLong(5, issue.getAuthorId());
    ps.setTimestamp(6, new Timestamp(issue.getDate().getTime()));
    ps.setInt(7, issue.getFixed());
    ps.setTimestamp(8, new Timestamp(issue.getFixedDate().getTime()));
    ps.setLong(9, issue.getProjectId());
    ps.setLong(10, issue.getSubProjectId());

    logger.info(ps);

    ps.execute();

    ResultSet rs = ps.getGeneratedKeys();
    if (rs.next()) {
        return rs.getLong(1);
    }
    return 0;
}

From source file:org.springframework.jdbc.core.JdbcTemplate.java

public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
        throws DataAccessException {
    if (logger.isDebugEnabled()) {
        String sql = getSql(psc);
        logger.debug(//w  w  w  .j  a v a  2  s  .c o  m
                "Executing SQL update and returning generated keys" + (sql != null ? " [" + sql + "]" : ""));
    }
    Integer result = (Integer) execute(psc, new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
            int rows = ps.executeUpdate();
            List generatedKeys = generatedKeyHolder.getKeyList();
            generatedKeys.clear();
            ResultSet keys = ps.getGeneratedKeys();
            if (keys != null) {
                ListResultSetExtractor lrse = new ListResultSetExtractor();
                generatedKeys.addAll((List) lrse.extractData(keys));
            }
            if (logger.isDebugEnabled()) {
                logger.debug(
                        "SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
            }
            return new Integer(rows);
        }
    });
    return result.intValue();
}

From source file:org.wso2.carbon.is.migration.service.v530.dao.ClaimDAO.java

/**
 * Add claim//from   ww  w  .ja  v  a  2 s. c  o  m
 *
 * @param connection
 * @param claimDialectURI
 * @param claimURI
 * @param tenantId
 * @return
 * @throws MigrationClientException
 */
public int addClaim(Connection connection, String claimDialectURI, String claimURI, int tenantId)
        throws MigrationClientException {

    PreparedStatement prepStmt = null;
    ResultSet rs = null;

    int claimId = 0;
    String query = SQLConstants.ADD_CLAIM;
    try {
        String dbProductName = connection.getMetaData().getDatabaseProductName();
        prepStmt = connection.prepareStatement(query, new String[] {
                DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, SQLConstants.ID_COLUMN) });

        prepStmt.setString(1, claimDialectURI);
        prepStmt.setInt(2, tenantId);
        prepStmt.setString(3, claimURI);
        prepStmt.setInt(4, tenantId);
        prepStmt.executeUpdate();

        rs = prepStmt.getGeneratedKeys();

        if (rs.next()) {
            claimId = rs.getInt(1);
        }
    } catch (SQLException e) {
        throw new MigrationClientException(
                "Error while adding claim " + claimURI + " to dialect " + claimDialectURI, e);
    } finally {
        IdentityDatabaseUtil.closeResultSet(rs);
        IdentityDatabaseUtil.closeStatement(prepStmt);
    }

    return claimId;
}

From source file:org.openbel.framework.core.kam.JdbcKAMLoaderImpl.java

/**
 * Saves an entry to the object table./* w w w  . ja  va 2s.  c o  m*/
 *
 * @param tid {@code int}, the object type id
 * @param v {@link String}, the non-null object value
 * @return {@code int}, the object primary key
 * @throws SQLException - Thrown if a sql error occurred saving an entry to
 * the object table
 */
protected int saveObject(int tid, String v) throws SQLException {
    final String objectsIdColumn = (dbConnection.isPostgresql() ? OBJECTS_ID_COLUMN_POSTGRESQL
            : OBJECTS_ID_COLUMN);
    PreparedStatement ps = getPreparedStatement(OBJECTS_SQL, new String[] { objectsIdColumn });
    ResultSet rs = null;

    if (v == null) {
        throw new InvalidArgument("object value cannot be null");
    }

    try {
        String encryptedString = encryptionService.encrypt(v);

        // Insert into objects_text if we are over MAX_VARCHAR_LENGTH
        Integer objectsTextId = null;
        if (encryptedString.length() > MAX_VARCHAR_LENGTH) {
            final String objectsTextColumn = (dbConnection.isPostgresql() ? OBJECTS_TEXT_COLUMN_POSTGRESQL
                    : OBJECTS_TEXT_COLUMN);
            PreparedStatement otps = getPreparedStatement(OBJECTS_TEXT_SQL, new String[] { objectsTextColumn });
            ResultSet otrs = null;
            StringReader sr = null;

            try {
                sr = new StringReader(encryptedString);
                otps.setClob(1, sr, encryptedString.length());
                otps.execute();
                otrs = otps.getGeneratedKeys();
                if (otrs.next()) {
                    objectsTextId = otrs.getInt(1);
                }
            } finally {
                close(otrs);

                if (sr != null) {
                    sr.close();
                }
            }
        }

        // FIXME Hardcoding objects_type to 1?
        ps.setInt(1, 1);

        if (objectsTextId == null) {
            // insert value into objects table
            ps.setString(2, encryptedString);
            ps.setNull(3, Types.INTEGER);
        } else {
            ps.setNull(2, Types.VARCHAR);
            ps.setInt(3, objectsTextId);
        }

        ps.execute();
        rs = ps.getGeneratedKeys();
        int oid;
        if (rs.next()) {
            oid = rs.getInt(1);
        } else {
            throw new IllegalStateException("object insert failed.");
        }
        return oid;
    } catch (EncryptionServiceException e) {
        throw new SQLException("Unable to encrypt string for object table.", e);
    } finally {
        close(rs);

    }
}

From source file:org.apache.sqoop.common.test.db.DatabaseProvider.java

/**
 * Execute given insert query in a new statement object and return
 * generated IDs.//from  w ww.  j  av a2s. c o  m
 *
 * @param query Query to execute
 * @return Generated ID.
 */
public Long executeInsertQuery(String query, Object... args) {
    LOG.info("Executing query: " + query);
    ResultSet rs = null;

    try {
        PreparedStatement stmt = databaseConnection.prepareStatement(query,
                PreparedStatement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < args.length; ++i) {
            if (args[i] instanceof String) {
                stmt.setString(i + 1, (String) args[i]);
            } else if (args[i] instanceof Long) {
                stmt.setLong(i + 1, (Long) args[i]);
            } else if (args[i] instanceof Boolean) {
                stmt.setBoolean(i + 1, (Boolean) args[i]);
            } else {
                stmt.setObject(i + 1, args[i]);
            }
        }

        stmt.execute();
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            return rs.getLong(1);
        }
    } catch (SQLException e) {
        LOG.error("Error in executing query", e);
        throw new RuntimeException("Error in executing query", e);
    } finally {
        closeResultSetWithStatement(rs);
    }

    return -1L;
}