Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

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

Prototype

int RETURN_GENERATED_KEYS

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

Click Source Link

Document

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

Usage

From source file:org.openhab.persistence.sql.internal.SqlPersistenceService.java

/**
 * Connects to the database/* ww w .j  a v  a  2 s  .com*/
 */
private void connectToDatabase() {
    try {
        // Reset the error counter
        errCnt = 0;

        logger.debug("SQL: Attempting to connect to database " + url);
        Class.forName(driverClass).newInstance();
        connection = DriverManager.getConnection(url, user, password);
        logger.debug("SQL: Connected to database " + url);

        Statement st = connection.createStatement();
        int result = st.executeUpdate("SHOW TABLES LIKE 'Items'");
        st.close();
        if (result == 0) {
            st = connection.createStatement();
            st.executeUpdate(
                    "CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,PRIMARY KEY (ItemId));",
                    Statement.RETURN_GENERATED_KEYS);
            st.close();
        }

        // Retrieve the table array
        st = connection.createStatement();

        // Turn use of the cursor on.
        st.setFetchSize(50);
        ResultSet rs = st.executeQuery("SELECT ItemId, ItemName FROM Items");
        while (rs.next()) {
            sqlTables.put(rs.getString(2), "Item" + rs.getInt(1));
        }
        rs.close();
        st.close();
    } catch (Exception e) {
        logger.error("SQL: Failed connecting to the SQL database using: driverClass=" + driverClass + ", url="
                + url + ", user=" + user + ", password=" + password, e);
    }
}

---

From source file:com.vigglet.util.ModelUtilBase.java

protected PreparedStatement getInsertStatement() throws SQLException {
    int i = 0;/*from   w  ww  . j av  a 2s .c om*/
    List<String> fieldList = getFieldList();
    StringBuilder fields = new StringBuilder(64);
    StringBuilder values = new StringBuilder(64);
    fields.append(" (");
    values.append(" VALUES (");

    for (String string : fieldList) {
        fields.append("`");
        fields.append(string);
        fields.append("`");

        values.append("?");

        if (++i < fieldList.size()) {
            fields.append(",");

            values.append(",");
        }

    }

    fields.append(")");
    values.append(");");

    return DbConnector.getInstance().getConnection().prepareStatement(
            "INSERT INTO oei." + getTableName() + fields.toString() + values.toString(),
            Statement.RETURN_GENERATED_KEYS);
}

---

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

private long insertAndGetConnectorId(MConnector mc, Connection conn) {
    PreparedStatement baseConnectorStmt = null;
    try {// www . j ava2 s .  c  om
        baseConnectorStmt = conn.prepareStatement(STMT_INSERT_INTO_CONFIGURABLE,
                Statement.RETURN_GENERATED_KEYS);
        baseConnectorStmt.setString(1, mc.getUniqueName());
        baseConnectorStmt.setString(2, mc.getClassName());
        baseConnectorStmt.setString(3, mc.getVersion());
        baseConnectorStmt.setString(4, mc.getType().name());

        int baseConnectorCount = baseConnectorStmt.executeUpdate();
        if (baseConnectorCount != 1) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0012, Integer.toString(baseConnectorCount));
        }

        ResultSet rsetConnectorId = baseConnectorStmt.getGeneratedKeys();

        if (!rsetConnectorId.next()) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0013);
        }
        // connector configurable also have directions
        insertConnectorDirections(rsetConnectorId.getLong(1), mc.getSupportedDirections(), conn);
        return rsetConnectorId.getLong(1);
    } catch (SQLException ex) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0014, mc.toString(), ex);
    } finally {
        closeStatements(baseConnectorStmt);
    }
}

---

From source file:org.ut.biolab.medsavant.server.serverapi.UserManager.java

@Override
public UserRole addRole(String sessID, String roleName, String roleDescription)
        throws RemoteException, SessionExpiredException, SQLException, SecurityException {
    String thisDatabase = SessionManager.getInstance().getDatabaseForSession(sessID);
    checkAdmin(sessID);//from   w w  w  .  j a  v a2s.  c  om

    //Check if role already exists, and if so, return it.
    Set<UserRole> roles = getAllRoles(sessID);
    for (UserRole r : roles) {
        if (r.getDatabase().equals(thisDatabase) && r.getRoleName().equals(roleName)) {
            return r;
        }
    }

    TableSchema roleTable = MedSavantDatabase.UserRoleTableSchema;
    InsertQuery iq = new InsertQuery(roleTable.getTableName());
    iq.addColumn(roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLENAME), roleName);
    iq.addColumn(roleTable.getDBColumn(MedSavantDatabase.UserRoleTableSchema.COLUMNNAME_OF_ROLE_DESCRIPTION),
            roleDescription);

    PooledConnection conn = ConnectionController.connectPooled(sessID);
    PreparedStatement stmt = null;
    ResultSet res = null;
    int roleId = -1;
    try {
        stmt = conn.prepareStatement(iq.toString(), Statement.RETURN_GENERATED_KEYS);
        stmt.execute();
        res = stmt.getGeneratedKeys();
        res.next();
        roleId = res.getInt(1);

        return new UserRole(roleId, roleName, roleDescription, thisDatabase);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        if (res != null) {
            res.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

---

From source file:com.vigglet.util.ModelUtilBase.java

protected PreparedStatement getUpdateStatement() throws SQLException {
    int i = 0;/*from  ww w .  j  a v  a 2s .co m*/
    List<String> fieldList = getFieldList();
    StringBuilder sql = new StringBuilder(64);

    sql.append(" SET ");
    for (String string : fieldList) {
        sql.append("`");
        sql.append(string);
        sql.append("`=?");

        if (++i < fieldList.size()) {
            sql.append(",");
        }
    }

    return DbConnector.getInstance().getConnection().prepareStatement(
            "UPDATE oei." + getTableName() + sql.toString() + " WHERE id=?;", Statement.RETURN_GENERATED_KEYS);
}

---

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

/**
 * Adds a cancer study to the Database./*ww  w.j  av a 2 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:org.openbel.framework.internal.KAMCatalogDao.java

/**
 * Saves the {@link KamDbObject} object to the KAM catalog database.
 *
 * <p>/*from  ww w .  j av  a 2 s  .  co  m*/
 * If the <tt>kamDb</tt> doesn't exist then create it, otherwise update
 * the record's information.  This method will look for an existing
 * <tt>kamDb</tt> first by id, if that is not <tt>null</tt>, then
 * by name.  It can be used to update the name of the record, but it
 * cannot be used to update the id.
 * </p>
 *
 * @param kamInfo {@link KamDbObject}, the kam info to save to the kam catalog,
 * which cannot be null, and must contain a non-null name
 * @throws SQLException Thrown if a SQL error occurred saving the kam info
 * to the kam catalog
 * @throws InvalidArgument Thrown if <tt>kamDb</tt> is null or contains
 * a null name.
 */
public void saveToCatalog(KamDbObject updated) throws SQLException {

    if (updated == null) {
        throw new InvalidArgument("kamInfo", updated);
    }

    if (updated.getName() == null) {
        throw new InvalidArgument("kamInfo contains a null name");
    }

    if (updated.getDescription() == null) {
        throw new InvalidArgument("kamInfo contains a null description");
    }

    // First check to see if the KAM already exists in the Catalog. This
    // returns the name of the schema for the KAM or null if the KAM is
    // not already there.  The existence of KAMs is checked first by id
    // then by name.
    KamInfo originalInfo = null;
    final Integer updatedId = updated.getId();
    if (updatedId != null) {
        originalInfo = getKamInfoById(updatedId.intValue());
    }
    if (originalInfo == null) {
        originalInfo = getKamInfoByName(updated.getName());
    }

    // If the KAM exists we update the current catalog entry
    try {
        if (null != originalInfo) {
            KamDbObject original = originalInfo.getKamDbObject();
            updated.setSchemaName(original.getSchemaName());

            //must update the kam info record.
            PreparedStatement skips = getPreparedStatement(UPDATE_KAM_SQL);
            skips.setString(1, updated.getName());
            skips.setString(2, updated.getDescription());
            skips.setTimestamp(3, new Timestamp(updated.getLastCompiled().getTime()));
            skips.setString(4, original.getSchemaName());
            skips.setInt(5, original.getId());
            skips.execute();
        } else {
            // Otherwise we insert a new kam. Schema name is automatically
            // generated

            // find next available schema name
            String schemaName = findNextSchemaName();
            updated.setSchemaName(schemaName);

            PreparedStatement ps = getPreparedStatement(INSERT_KAM_SQL, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, updated.getName());
            ps.setString(2, updated.getDescription());
            ps.setTimestamp(3, new Timestamp(updated.getLastCompiled().getTime()));
            ps.setString(4, updated.getSchemaName());
            ps.execute();
        }
    } catch (SQLException ex) {
        throw ex;
    } finally {
        // Nothing to do
    }
}

---

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

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

    Connection con = null;//from   www .j  av  a 2 s . c  o  m
    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.webapp.security.SecurityDataContext.java

/**
* @param login/*from   w w w .ja v a 2 s.  co m*/
* 
* @return
*/
private String createLogin(final Login login) {

    final String insertSql = "insert into SECURITY_LOGIN (CONTEXT,NAME,PASSWORD,PARTY) values (?,?,?,?)";

    LOG.info("Inserting new login (" + login.getPrincipal().getName() + ") into " + getName());

    if (login.getCredentials() != null && login.getCredentials().contains(CredentialSet.PASSWORD)) {

        // Get the credentials
        byte[] value = login.getCredentials().getValue(CredentialSet.PASSWORD);

        // If there is a value for the password...
        if (value != null && value.length > 0) {

            // turn it into a hex string
            final String dbValue = ByteUtil.bytesToHex(value, null);

            KeyHolder keyHolder = new GeneratedKeyHolder();

            try {
                jdbcTemplate.update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {

                        PreparedStatement ps = connection.prepareStatement(insertSql.toString(),
                                Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, getName().toLowerCase()); //context
                        ps.setString(2, login.getPrincipal().getName().toLowerCase()); // name
                        ps.setString(3, dbValue); // password
                        ps.setLong(4, 0);// No party ID for now
                        return ps;
                    }
                }, keyHolder);
            } catch (DataAccessException e) {
                LOG.error("Could not create login: " + e.getMessage());
                return null;
            }

            // Return the ID of the login
            return keyHolder.getKey().toString();

        } else {
            LOG.error("Empty password - login not added");
        }
    } else {
        LOG.error("No login credentials - login not added");
    }

    return null;
}

---

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

private long insertAndGetDriverId(MDriver mDriver, Connection conn) {
    PreparedStatement baseDriverStmt = null;
    try {//ww w . j a  v a 2 s  .  c o  m
        baseDriverStmt = conn.prepareStatement(STMT_INSERT_INTO_CONFIGURABLE, Statement.RETURN_GENERATED_KEYS);
        baseDriverStmt.setString(1, mDriver.getUniqueName());
        baseDriverStmt.setString(2, Driver.getClassName());
        baseDriverStmt.setString(3, mDriver.getVersion());
        baseDriverStmt.setString(4, mDriver.getType().name());

        int baseDriverCount = baseDriverStmt.executeUpdate();
        if (baseDriverCount != 1) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0012, Integer.toString(baseDriverCount));
        }

        ResultSet rsetDriverId = baseDriverStmt.getGeneratedKeys();

        if (!rsetDriverId.next()) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0013);
        }
        return rsetDriverId.getLong(1);
    } catch (SQLException ex) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0050, mDriver.toString(), ex);
    } finally {
        closeStatements(baseDriverStmt);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• »