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.apache.ibatis.jdbc.SqlRunner.java

public int insert(String sql, Object... args) throws SQLException {
    PreparedStatement ps;

    if (useGeneratedKeySupport) {
        ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    } else {/*from   www . j a v a2s  . c  om*/
        ps = connection.prepareStatement(sql);
    }

    try {
        setParameters(ps, args);
        ps.executeUpdate();

        if (useGeneratedKeySupport) {
            List<Map<String, Object>> keys = getResults(ps.getGeneratedKeys());

            if (keys.size() == 1) {
                Map<String, Object> key = keys.get(0);
                Iterator<Object> i = key.values().iterator();

                if (i.hasNext()) {
                    Object genkey = i.next();

                    if (genkey != null) {
                        try {
                            return Integer.parseInt(genkey.toString());
                        } catch (NumberFormatException e) {
                            // ignore, no numeric key suppot
                        }
                    }
                }
            }
        }

        return NO_GENERATED_KEY;
    } finally {
        try {
            ps.close();
        } catch (SQLException e) {
            // ignore
        }
    }
}

From source file:org.wso2.carbon.identity.authorization.core.jdbc.dao.JDBCModuleDAO.java

@Override
protected void insert(PreparedStatement stmt, ResultSet res, Connection connection)
        throws SQLException, UserStoreException {
    String sql = "INSERT INTO UM_MODULE (UM_MODULE_NAME) VALUES(?) ";

    stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    byte count = 0;
    stmt.setString(++count, getModuleName());

    int resCount = stmt.executeUpdate();
    if (resCount == 0) {
        String error = "Insertion faild for the module " + getModuleName();
        log.error(error);//www  .ja va  2s  . c  o m
        throw new UserStoreException(error);
    }
    res = stmt.getGeneratedKeys();
    if (res.next()) {
        setModuleId(res.getInt(1));
    }

}

From source file:dk.netarkivet.harvester.datamodel.GlobalCrawlerTrapListDBDAO.java

@Override
public int create(GlobalCrawlerTrapList trapList) {
    ArgumentNotValid.checkNotNull(trapList, "trapList");
    // Check for existence of a trapList in the database with the same name
    // and throw argumentNotValid if not
    if (exists(trapList.getName())) {
        throw new ArgumentNotValid(
                "Crawlertrap with name '" + trapList.getName() + "'already exists in database");
    }//from  w  w  w . ja v  a 2s  . c o m
    int trapId;
    Connection conn = HarvestDBConnection.get();
    PreparedStatement stmt = null;
    try {
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(INSERT_TRAPLIST_STMT, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, trapList.getName());
        stmt.setString(2, trapList.getDescription());
        stmt.setBoolean(3, trapList.isActive());
        stmt.executeUpdate();
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        trapId = rs.getInt(1);
        trapList.setId(trapId);
        for (String expr : trapList.getTraps()) {
            stmt = conn.prepareStatement(INSERT_TRAP_EXPR_STMT);
            stmt.setInt(1, trapId);
            stmt.setString(2, expr);
            stmt.executeUpdate();
        }
        conn.commit();
    } catch (SQLException e) {
        String message = "SQL error creating global crawler trap list \n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        DBUtils.closeStatementIfOpen(stmt);
        DBUtils.rollbackIfNeeded(conn, "create trap list", trapList);
        HarvestDBConnection.release(conn);
    }
    return trapId;
}

From source file:com.microsoftopentechnologies.azchat.web.dao.PreferenceMetadataDAOImpl.java

/**
 * This method adds the user preference data in the azure SQL table.
 * // w ww  . j av a 2  s. c o m
 * @return preferenceMetadataEntity
 * @throws SQLException
 */
@Override
public PreferenceMetadataEntity addPreferenceMetadataEntity(PreferenceMetadataEntity preferenceMetadataEntity)
        throws Exception {
    LOGGER.info("[PreferenceMetadataEntity][addPreferenceMetadataEntity]         start ");
    LOGGER.debug("PreferenceMetadataEntity    " + preferenceMetadataEntity.getPreferenceDesc());
    int prefMetadataId = 0;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
        preparedStatement = connection.prepareStatement(AzureChatSQLConstants.ADD_PREFERENCE_METADATA,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement = generatePreparedStatement(preparedStatement, preferenceMetadataEntity);
        preparedStatement.executeUpdate();
        resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()) {
            prefMetadataId = resultSet.getInt(1);
        }
    } catch (Exception e) {
        LOGGER.error(
                "Exception occurred while adding preference metadata entity to the azure SQL table. Exception Message : "
                        + e.getMessage());
        throw new AzureChatSystemException(
                "Exception occurred while adding preference metadata entity to the azure SQL table. Exception Message : "
                        + e.getMessage());
    } finally {
        AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet);
    }
    preferenceMetadataEntity.setPreferenceId(prefMetadataId);
    LOGGER.info("[PreferenceMetadataEntity][addPreferenceMetadataEntity]         end ");
    return preferenceMetadataEntity;
}

From source file:dk.netarkivet.archive.arcrepositoryadmin.ReplicaCacheHelpers.java

/**
 * Method to create a new entry in the file table in the database.
 * The file_id is automatically created by the database, and the argument
 * is used for the filename for this new entry to the table.
 * This will also create a replicafileinfo entry for each replica.
 *
 * @param filename The filename for the new entry in the file table.
 * @param connection An open connection to the archive database
 * @throws IllegalState If the file cannot be inserted into the database.
 * @return created file_id for the new entry.
 *//*from   w w  w .  j  a v a2 s  .  c  o m*/
protected static long insertFileIntoDB(String filename, Connection connection) throws IllegalState {
    log.debug("Insert file '" + filename + "' into database");
    PreparedStatement statement = null;
    try {

        // Make the SQL statement for putting the replica into the database
        // and insert the variables for the entry to the replica table.
        statement = connection.prepareStatement("INSERT INTO file (filename) " + "VALUES ( ? )",
                Statement.RETURN_GENERATED_KEYS);
        statement.setString(1, filename);

        // execute the SQL statement
        statement.executeUpdate();
        // Retrieve the fileId for the just inserted file.
        ResultSet resultset = statement.getGeneratedKeys();
        resultset.next();
        long fileId = resultset.getLong(1);
        connection.commit();

        // Create replicafileinfo for each replica.
        createReplicaFileInfoEntriesInDB(fileId, connection);
        log.debug("Insert file '" + filename + "' into database completed. Assigned fileID=" + fileId);
        return fileId;
    } catch (SQLException e) {
        throw new IllegalState("Cannot add file '" + filename + "' to the database.", e);
    } finally {
        DBUtils.closeStatementIfOpen(statement);
    }
}

From source file:com.untzuntz.coredata.QueryRunner.java

private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
        throws SQLException {
    if (conn == null) {
        throw new SQLException("Null connection");
    }/*from   ww  w . j av  a2  s  . co  m*/

    if (sql == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null SQL statement");
    }

    if (rsh == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null ResultSetHandler");
    }

    PreparedStatement stmt = null;
    T generatedKeys = null;

    try {
        stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        this.fillStatement(stmt, params);
        stmt.executeUpdate();
        ResultSet resultSet = stmt.getGeneratedKeys();
        generatedKeys = rsh.handle(resultSet);
    } catch (SQLException e) {
        this.rethrow(e, sql, params);
    } finally {
        close(stmt);
        if (closeConn) {
            close(conn);
        }
    }

    return generatedKeys;
}

From source file:org.forumj.dbextreme.db.dao.FJPostDao.java

public Long create(IFJPost post, Connection conn, boolean updateThread)
        throws IOException, DBException, SQLException, ConfigurationException {
    Long postId = null;//from   w  w w.  j av a 2  s . c o m
    String createPostQuery = getCreatePostQuery();
    FJForumDao forumDao = new FJForumDao();
    String tableBody = forumDao.getCurretBodyTable();
    String tableHead = forumDao.getCurretBodyHeadTable();
    String createPostBodyQuery = getCreatePostBodyQuery(tableBody);
    String createPostHeadQuery = getCreatePostHeadQuery(tableHead);
    PreparedStatement st = null;
    try {
        st = conn.prepareStatement(createPostQuery, new String[] { "id" });
        st.setLong(1, post.getThreadId());
        st.setInt(2, post.getState());
        st.setString(3, tableBody);
        st.setString(4, tableHead);
        st.executeUpdate();
        ResultSet idRs = st.getGeneratedKeys();
        if (idRs.next()) {
            postId = idRs.getLong(1);
            post.setId(postId);
            st.close();
            st = conn.prepareStatement(createPostBodyQuery);
            IFJPostBody postBody = post.getBody();
            st.setLong(1, postId);
            st.setLong(2, postId);
            st.setString(3, postBody.getBody());
            st.executeUpdate();
            st.close();
            st = conn.prepareStatement(createPostHeadQuery);
            IFJPostHead postHead = post.getHead();
            st.setLong(1, postId);
            st.setLong(2, postId);
            st.setLong(3, postHead.getAuth());
            st.setLong(4, postHead.getThreadId());
            st.setString(5, postHead.getTitle());
            st.setLong(6, postHead.getCreateTime());
            st.setString(7, postHead.getIp());
            st.setString(8, postHead.getDomen());
            st.setString(9, " ");
            st.executeUpdate();
            if (updateThread) {
                FJThreadDao threadDao = new FJThreadDao();
                FJThread thread = threadDao.read(post.getThreadId());
                thread.setLastPostId(postId);
                thread.setLastPostAuthId(postHead.getAuth());
                thread.setLastPostTime(new Date(postHead.getCreateTime()));
                thread.setLastPostNick(postHead.getAuthor().getNick());
                thread.setPcount(thread.getPcount() + 1);
                threadDao.update(thread, conn);
            }
        } else {
            throw new DBException("Post wasn't created");
        }
    } finally {
        readFinally(null, st);
    }
    return postId;
}

From source file:edu.corgi.uco.UserBean.java

public String addSecretary() {
    try (Connection conn = dataSource.getConnection()) {
        PreparedStatement addUser = conn.prepareStatement(
                "insert into UserTable (email, password, firstname, lastname) values (?, ?, ?, ?)",
                Statement.RETURN_GENERATED_KEYS);

        addUser.setString(1, email);//www . j a v a2 s. com
        addUser.setString(2, SHA256Encrypt.encrypt("temp1234"));
        addUser.setString(3, firstName);
        addUser.setString(4, lastName);

        addUser.executeUpdate();

        ResultSet results = addUser.getGeneratedKeys();

        //for whatever really fun reason it says userid is not a field
        int id = 0;
        while (results.next()) {
            id = results.getInt(1);
        }

        PreparedStatement addUserToGroup = conn.prepareStatement(
                "insert into GroupTable (userID, groupname, email) values (?, 'secretary', ?)");

        addUserToGroup.setInt(1, id);
        addUserToGroup.setString(2, email);

        addUserToGroup.executeUpdate();
    } catch (SQLException ex) {
        Logger.getLogger(UserBean.class.getName()).log(Level.SEVERE, null, ex);
        FacesContext.getCurrentInstance().addMessage(email,
                new FacesMessage("Something went wrong. Please try again later."));
    }
    FacesContext.getCurrentInstance().addMessage(email, new FacesMessage("Success"));
    return null;
}

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);/*ww w .j  a  va  2 s  .com*/

    //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:org.biblionum.ouvrage.modele.OuvrageModele.java

/**
 * Java method that inserts a row in the generated sql table and returns the
 * new generated id//  w  w w  . java 2s. co  m
 *
 * @param con (open java.sql.Connection)
 * @param auteur
 * @param editeur
 * @param annee_edition
 * @param resume
 * @param nb_page
 * @param emplacement
 * @param couverture
 * @param ouvrageTipeid
 * @param categorieOuvrageid
 * @param niveauid_niveau
 * @param filiereid
 * @param titre
 * @return id (database row id [id])
 * @throws SQLException
 */
public int insertIntoUtilisateur(DataSource ds, String auteur, String editeur, int annee_edition, String resume,
        int nb_page, String emplacement, String couverture, int ouvrageTipeid, int categorieOuvrageid,
        int niveauid_niveau, int filiereid, String titre) throws SQLException {
    con = ds.getConnection();
    int generatedId = -1;
    String sql = "INSERT INTO ouvrage (auteur, editeur, annee_edition, resume, nb_page, emplacement, "
            + "couverture, ouvrageTipeid, categorieOuvrageid, niveauid_niveau, filiereid, titre" + ")"
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    PreparedStatement statement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    statement.setString(1, auteur);
    statement.setString(2, editeur);
    statement.setInt(3, annee_edition);
    statement.setString(4, resume);
    statement.setInt(5, nb_page);
    statement.setString(6, emplacement);
    statement.setString(7, couverture);
    statement.setInt(8, ouvrageTipeid);
    statement.setInt(9, categorieOuvrageid);
    statement.setInt(10, niveauid_niveau);
    statement.setInt(11, filiereid);
    statement.setString(12, titre);
    statement.execute();
    ResultSet auto = statement.getGeneratedKeys();

    if (auto.next()) {
        generatedId = auto.getInt(1);
    } else {
        generatedId = -1;
    }

    statement.close();
    con.close();
    return generatedId;
}