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.carbon.registry.core.jdbc.dao.JDBCPathCache.java

/**
 * Method to add resource path entry to the database.
 *
 * @param path         the path to add.// w  w  w .  j  a v  a 2 s  .c o  m
 * @param parentPathId the parent path's id.
 *
 * @return the path's id.
 * @throws RegistryException if the data access manager was invalid.
 * @throws SQLException      if an error occurs while adding the entry.
 */
public int addEntry(String path, int parentPathId) throws SQLException, RegistryException {
    ResultSet results = null;
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    DataAccessManager dataAccessManager;
    if (CurrentSession.getUserRegistry() != null
            && CurrentSession.getUserRegistry().getRegistryContext() != null) {
        dataAccessManager = CurrentSession.getUserRegistry().getRegistryContext().getDataAccessManager();
    } else {
        // TODO: This code block doesn't seem to get hit. Remove if unused.
        dataAccessManager = RegistryContext.getBaseInstance().getDataAccessManager();
    }
    if (!(dataAccessManager instanceof JDBCDataAccessManager)) {
        String msg = "Failed to add path entry. Invalid data access manager.";
        log.error(msg);
        throw new RegistryException(msg);
    }
    DataSource dataSource = ((JDBCDataAccessManager) dataAccessManager).getDataSource();
    Connection conn = dataSource.getConnection();
    if (conn != null) {
        if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }
        conn.setAutoCommit(false);
    } else {
        log.error("Unable to acquire connection to database.");
        return -1;
    }
    boolean success = false;
    int pathId = 0;

    try {
        String sql = "INSERT INTO REG_PATH(REG_PATH_VALUE, REG_PATH_PARENT_ID, REG_TENANT_ID) "
                + "VALUES (?, ?, ?)";
        String sql1 = "SELECT MAX(REG_PATH_ID) FROM REG_PATH";
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_PATH_ID") });
        } else {
            ps = conn.prepareStatement(sql);
        }
        ps.setString(1, path);
        ps.setInt(2, parentPathId);
        ps.setInt(3, CurrentSession.getTenantId());
        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            results = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_ENTRY_LOCK) {
                ps.executeUpdate();
                ps1 = conn.prepareStatement(sql1);
                results = ps1.executeQuery();
            }
        }
        if (results.next()) {
            pathId = results.getInt(1);
            if (pathId > 0) {
                success = true;
                return pathId;
            }
        }
    } catch (SQLException e) {
        // we have to be expecting an exception with the duplicate value for the path value
        // which can be further checked from here..
        String msg = "Failed to insert resource to " + path + ". " + e.getMessage();
        log.error(msg, e);
        throw e;
    } finally {
        if (success) {
            try {
                conn.commit();
                RegistryCacheEntry e = new RegistryCacheEntry(pathId);
                String connectionId = null;
                if (conn.getMetaData() != null) {
                    connectionId = RegistryUtils.getConnectionId(conn);
                }
                RegistryCacheKey key = RegistryUtils.buildRegistryCacheKey(connectionId,
                        CurrentSession.getTenantId(), path);
                getCache().put(key, e);

            } catch (SQLException e) {
                String msg = "Failed to commit transaction. Inserting " + path + ". " + e.getMessage();
                log.error(msg, e);
            } finally {
                try {
                    try {
                        if (results != null) {
                            results.close();
                        }
                    } finally {
                        try {
                            if (ps1 != null) {
                                ps1.close();
                            }
                        } finally {
                            try {
                                if (ps != null) {
                                    ps.close();
                                }
                            } finally {
                                conn.close();
                            }
                        }
                    }
                } catch (SQLException e) {
                    String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage();
                    log.error(msg, e);
                }
            }
        } else {
            try {
                conn.rollback();

            } catch (SQLException e) {
                String msg = "Failed to rollback transaction. Inserting " + path + ". " + e.getMessage();
                log.error(msg, e);
            } finally {
                try {
                    try {
                        if (results != null) {
                            results.close();
                        }
                    } finally {
                        try {
                            if (ps != null) {
                                ps.close();
                            }
                        } finally {
                            if (conn != null) {
                                conn.close();
                            }
                        }
                    }
                } catch (SQLException e) {
                    String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage();
                    log.error(msg, e);
                }
            }
        }
    }
    return -1;
}

From source file:com.tremolosecurity.provisioning.core.providers.BasicDB.java

private void insertCreate(User user, Set<String> attributes, Map<String, Attribute> attrs, Connection con,
        Map<String, Object> request) throws SQLException, ProvisioningException {

    int approvalID = 0;

    if (request.containsKey("APPROVAL_ID")) {
        approvalID = (Integer) request.get("APPROVAL_ID");
    }//from  w ww. j  a  va2 s  .  c o  m

    Workflow workflow = (Workflow) request.get("WORKFLOW");

    StringBuffer insert = new StringBuffer();
    insert.append("INSERT INTO ").append(this.userTable).append(" (");
    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            getFieldName(attr, insert).append(",");
        }
    }

    insert.setLength(insert.length() - 1);
    insert.append(") values (");
    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            insert.append("?,");
        }
    }
    insert.setLength(insert.length() - 1);

    insert.append(")");

    PreparedStatement ps = con.prepareStatement(insert.toString(), Statement.RETURN_GENERATED_KEYS);
    int i = 1;

    for (String attr : attributes) {
        if (attrs.get(attr) != null) {

            Attribute.DataType dataType = attrs.get(attr).getDataType();

            switch (dataType) {
            case string:
                ps.setString(i, attrs.get(attr).getValues().get(0));
                break;
            case intNum:
                ps.setInt(i, Integer.parseInt(attrs.get(attr).getValues().get(0)));
                break;
            case longNum:
                ps.setLong(i, Long.parseLong(attrs.get(attr).getValues().get(0)));
                break;

            case date:
                ps.setDate(i, new Date(ISODateTimeFormat.date()
                        .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis()));
                break;
            case timeStamp:
                ps.setTimestamp(i, new Timestamp(ISODateTimeFormat.dateTime()
                        .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis()));
                break;
            }

            i++;
        }

    }

    ps.executeUpdate();
    ResultSet rs = ps.getGeneratedKeys();

    int id;

    if (rs.next() && !this.driver.contains("oracle")) {

        id = (int) rs.getInt(1);
    } else {
        StringBuffer select = new StringBuffer();
        select.append("SELECT ");
        this.getFieldName(this.userPrimaryKey, select).append(" FROM ").append(this.userTable)
                .append(" WHERE ");
        this.getFieldName(this.userName, select).append("=?");
        PreparedStatement getUserId = con.prepareStatement(select.toString()); //con.prepareStatement( + this.userPrimaryKey + " FROM " + this.userTable + " WHERE " + this.userName + "=?");
        getUserId.setString(1, user.getUserID());
        ResultSet userResult = getUserId.executeQuery();
        userResult.next();
        id = (int) userResult.getInt(this.userPrimaryKey);

        userResult.close();
        getUserId.close();
    }

    this.cfgMgr.getProvisioningEngine().logAction(this.name, true, ActionType.Add, approvalID, workflow,
            "userName", user.getUserID());

    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID,
                    workflow, attr, attrs.get(attr).getValues().get(0));
        }
    }

    if (user.getGroups().size() > 0) {
        switch (this.groupMode) {
        case None:
            break;
        case One2Many:
            insert.setLength(0);
            insert.append("INSERT INTO ").append(this.groupTable).append(" (").append(this.groupUserKey)
                    .append(",").append(this.groupName).append(") VALUES (?,?)");
            ps = con.prepareStatement(insert.toString());

            for (String groupName : user.getGroups()) {
                ps.setInt(1, id);
                ps.setString(2, groupName);
                ps.executeUpdate();
                this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID,
                        workflow, "group", groupName);
            }

            break;
        case Many2Many:
            many2manySetGroupsCreate(user, insert, con, id, request);
            break;
        }

    }
}

From source file:com.jabyftw.lobstercraft.player.PlayerHandlerService.java

/**
 * Kick or ban player, online or not. This won't announce to the server and will keep a record on MySQL. This method <b>SHOULD</b> run asynchronously.
 *
 * @param offlinePlayer  player to be kicked
 * @param banType        kick type/*from ww w  . j av a 2 s. co m*/
 * @param reason         reason to be at record, from 4 to 120 characters
 * @param moderatorId    moderator to be stored, can be null
 * @param bannedDuration ban duration, can be null
 * @return a ban response to the CommandSender
 */
public BanResponse kickPlayer(@NotNull OfflinePlayer offlinePlayer, @NotNull final BanType banType,
        @NotNull final String reason, @Nullable Integer moderatorId, @Nullable final Long bannedDuration) {
    // Check if player is registered
    if (!offlinePlayer.isRegistered())
        return BanResponse.PLAYER_NOT_REGISTERED;

    // Set variables
    int playerId = offlinePlayer.getPlayerId();
    long recordDate = System.currentTimeMillis();

    // Check unban date
    Long unbanDate;
    if (banType != BanType.PLAYER_TEMPORARILY_BANNED) // Only temporary banned requires this argument
        unbanDate = null;
    else if (bannedDuration != null)
        unbanDate = recordDate + bannedDuration;
    else
        return BanResponse.BAN_DURATION_NOT_SET;

    // Check if reason has right size
    if (!Util.checkStringLength(reason, 4, 120))
        return BanResponse.INVALID_REASON_LENGTH;

    try {
        // Retrieve connection
        Connection connection = LobsterCraft.dataSource.getConnection();

        // Prepare statement
        PreparedStatement preparedStatement = connection.prepareStatement(
                // 6 arguments
                "INSERT INTO `minecraft`.`ban_records` (`user_playerId`, `user_moderatorId`, `banType`, `recordDate`, `reason`, `unbanDate`) VALUES (?, ?, ?, ?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);

        // Set variables for query
        preparedStatement.setInt(1, playerId);
        preparedStatement.setObject(2, moderatorId, Types.INTEGER); // will write null if is null
        preparedStatement.setByte(3, banType.getTypeId());
        preparedStatement.setLong(4, recordDate);
        preparedStatement.setString(5, reason);
        preparedStatement.setObject(6, unbanDate, Types.BIGINT);

        // Execute statement
        preparedStatement.execute();

        // Retrieve generated keys
        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();

        // Throw error if there is no generated key
        if (!generatedKeys.next())
            throw new SQLException("There is no generated key");

        // Create entry
        BannedPlayerEntry bannedPlayerEntry = new BannedPlayerEntry(generatedKeys.getLong("recordId"),
                moderatorId, banType, recordDate, reason, unbanDate);

        // Add entry to storage
        synchronized (playerBanEntries) {
            playerBanEntries.putIfAbsent(playerId, new HashSet<>());
            playerBanEntries.get(playerId).add(bannedPlayerEntry);
        }

        // Close everything
        generatedKeys.close();
        preparedStatement.close();
        connection.close();

        // Schedule player kick, if he is online
        OnlinePlayer onlinePlayer = offlinePlayer.getOnlinePlayer(null);
        if (onlinePlayer != null)
            Bukkit.getServer().getScheduler().runTask(LobsterCraft.plugin, () -> {
                if (onlinePlayer.getPlayer().isOnline())
                    // Kick player if he is online
                    onlinePlayer.getPlayer().kickPlayer(bannedPlayerEntry.getKickMessage());
            });

        return BanResponse.SUCCESSFULLY_EXECUTED;
    } catch (SQLException exception) {
        exception.printStackTrace();
        return BanResponse.ERROR_OCCURRED;
    }
}

From source file:com.jabyftw.lobstercraft.player.PlayerHandlerService.java

/**
 * This method will insert a database entry and <b>SHOULD</b> run asynchronously
 *
 * @param offlinePlayer player to be muted
 * @param moderator     the administrator that muted the player, can be null (Console)
 * @param reason        the reason to be muted
 * @param muteDuration  duration to be muted
 * @return a response to send to the administrator
 *///from  w  w w  .  j a va2  s  .c  o  m
public MuteResponse mutePlayer(@NotNull OfflinePlayer offlinePlayer, @Nullable OnlinePlayer moderator,
        @NotNull final String reason, @NotNull final long muteDuration) {
    // Check if player is registered
    if (!offlinePlayer.isRegistered())
        return MuteResponse.PLAYER_NOT_REGISTERED;

    // Set variables
    int playerId = offlinePlayer.getPlayerId();
    long recordDate = System.currentTimeMillis();
    long unmuteDate = recordDate + muteDuration;

    // Check if reason has right size
    if (!Util.checkStringLength(reason, 4, 128))
        return MuteResponse.INVALID_REASON_LENGTH;

    try {
        // Retrieve connection
        Connection connection = LobsterCraft.dataSource.getConnection();

        // Prepare statement
        PreparedStatement preparedStatement = connection.prepareStatement(
                "INSERT INTO `minecraft`.`mod_muted_players`(`user_mutedId`, `user_moderatorId`, `muteDate`, `unmuteDate`, `reason`) VALUES (?, ?, ?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);

        // Set variables for query
        preparedStatement.setInt(1, playerId);
        if (moderator != null)
            preparedStatement.setInt(2, moderator.getOfflinePlayer().getPlayerId()); // will write null if is null
        else
            preparedStatement.setNull(2, Types.INTEGER);
        preparedStatement.setLong(3, recordDate);
        preparedStatement.setLong(4, unmuteDate);
        preparedStatement.setString(5, reason);

        // Execute statement
        preparedStatement.execute();

        // Retrieve generated keys
        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();

        // Throw error if there is no generated key
        if (!generatedKeys.next())
            throw new SQLException("There is no generated key");

        // Create entry
        MutePlayerEntry mutedPlayerEntry = new MutePlayerEntry(generatedKeys.getLong("mute_index"),
                moderator != null ? moderator.getOfflinePlayer().getPlayerId() : null, recordDate, unmuteDate,
                reason);

        // Add entry to storage
        synchronized (playerMuteEntries) {
            playerMuteEntries.putIfAbsent(playerId, new HashSet<>());
            playerMuteEntries.get(playerId).add(mutedPlayerEntry);
        }

        // Close everything
        generatedKeys.close();
        preparedStatement.close();
        connection.close();

        // Check if player is online and warn him
        OnlinePlayer onlinePlayer = offlinePlayer.getOnlinePlayer(null);
        if (onlinePlayer != null) {
            StringBuilder stringBuilder = new StringBuilder("cVoc foi silenciado");
            if (moderator != null)
                stringBuilder.append(" por ").append(moderator.getPlayer().getDisplayName());
            stringBuilder.append("c at ").append(Util.formatDate(unmuteDate)).append('\n')
                    .append("pela razo: 4\"").append(reason).append('\"');
            onlinePlayer.getPlayer().sendMessage(stringBuilder.toString());
        }

        return MuteResponse.SUCCESSFULLY_EXECUTED;
    } catch (SQLException exception) {
        exception.printStackTrace();
        return MuteResponse.ERROR_OCCURRED;
    }
}

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

@Override
public int replyToUserCommentGroup(String sessID, int userCommentGroupId, UserComment userComment)
        throws SessionExpiredException, SQLException, RemoteException, SecurityException {
    if (!isAuthorizedForUserComments(sessID)) {
        throw new SecurityException("This user does not have access to view comments");
    }/*  w w w.  j  av a2 s . co m*/

    String username = SessionManager.getInstance().getUserForSession(sessID);
    String ontologyId = userComment.getOntologyTerm().getID();

    //TODO: Insert checks here to make sure user has permissions to change flags.      
    //If they don't, use the flags in 'lastComment' commented out above.       
    Boolean isApproved = userComment.isApproved();
    Boolean isIncluded = userComment.isIncluded();
    Boolean isDeleted = userComment.isDeleted();
    String commentText = userComment.getCommentText();
    String ontology = userComment.getOntologyTerm().getOntology().name();
    TableSchema lcTable = MedSavantDatabase.UserCommentTableSchema;
    InsertQuery iq = new InsertQuery(lcTable.getTable());

    iq.addColumn(
            MedSavantDatabase.UserCommentTableSchema
                    .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_USER_COMMENT_GROUP_ID),
            userCommentGroupId);
    iq.addColumn(MedSavantDatabase.UserCommentTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_ONTOLOGY_ID), ontologyId);
    iq.addColumn(MedSavantDatabase.UserCommentTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_ONTOLOGY), ontology);
    iq.addColumn(MedSavantDatabase.UserCommentTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_USER), username);
    iq.addColumn(MedSavantDatabase.UserCommentTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_APPROVED), isApproved);
    iq.addColumn(MedSavantDatabase.UserCommentTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_INCLUDE), isIncluded);
    iq.addColumn(MedSavantDatabase.UserCommentTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_DELETED), isDeleted);
    iq.addColumn(
            MedSavantDatabase.UserCommentTableSchema
                    .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_CREATION_DATE),
            (new FunctionCall(new CustomSql("NOW"))).addCustomParams());
    iq.addColumn(MedSavantDatabase.UserCommentTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_COMMENT), commentText);
    if (userComment.getOriginalComment() != null) {
        Integer commentId = userComment.getOriginalComment().getCommentID();
        if (commentId == null) {
            throw new IllegalArgumentException(
                    "Cannot post this comment as it refers to a comment with a null identifier");
        }
        if (commentId < 1) {
            throw new IllegalArgumentException(
                    "Cannot post this comment as it refers to a comment with a non-positive identifier: "
                            + commentId);
        }
        iq.addColumn(
                MedSavantDatabase.UserCommentTableSchema.getDBColumn(
                        MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_PARENT_USER_COMMENT_ID),
                commentId);
    }

    PreparedStatement stmt = null;
    PooledConnection conn = ConnectionController.connectPooled(sessID);
    ResultSet res = null;
    int commentId = -1; //Not returned for now.
    try {
        stmt = conn.prepareStatement(iq.toString(), Statement.RETURN_GENERATED_KEYS);
        stmt.execute();
        res = stmt.getGeneratedKeys();
        res.next();
        commentId = res.getInt(1);
        //LOG.info("Inserted new comment with id " + commentId + " for comment group with id " + userCommentGroupId);

        //update original comment's status if necessary.
        if (userComment.getOriginalComment() != null) {
            UpdateQuery uq = new UpdateQuery(lcTable.getTable());
            uq.addCondition(BinaryCondition.equalTo(
                    MedSavantDatabase.UserCommentTableSchema.getDBColumn(
                            MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_USER_COMMENT_ID),
                    userComment.getOriginalComment().getCommentID()));
            uq.addSetClause(
                    MedSavantDatabase.UserCommentTableSchema
                            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_APPROVED),
                    userComment.getOriginalComment().isApproved());
            uq.addSetClause(
                    MedSavantDatabase.UserCommentTableSchema
                            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_INCLUDE),
                    userComment.getOriginalComment().isIncluded());
            uq.addSetClause(
                    MedSavantDatabase.UserCommentTableSchema
                            .getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_DELETED),
                    userComment.getOriginalComment().isDeleted());
            stmt = conn.prepareStatement(uq.toString());
            stmt.execute();
        }
        return commentId;
    } catch (SQLException sqe) {
        LOG.error("SQL Error", sqe);
        throw sqe;
    } finally {
        if (conn != null) {
            conn.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (res != null) {
            res.close();
        }
    }
}

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

private void addKeywords(long id, List<String> keywords, boolean isFeed) throws SQLException {
    if (keywords == null || keywords.size() == 0) {
        Log.d(TAG, "No keywords to be added for " + (isFeed ? "feed" : "field") + " id: " + id);
        return;/*from  w w w.  j  a v  a2s  .  c  o m*/
    }
    String sqlSelectKeyword = "SELECT " + IotHubDataHandler.KEY_KEYWORD_ID + " FROM "
            + IotHubDataHandler.TABLE_KEYWORD + " WHERE " + IotHubDataHandler.KEY_KEYWORD_VALUE + " = ?";
    String sqlInsertKeyword = "insert into " + IotHubDataHandler.TABLE_KEYWORD + "("
            + IotHubDataHandler.KEY_KEYWORD_VALUE + ") values (?)";
    String sqlInsertRelation = isFeed
            ? ("insert into " + IotHubDataHandler.TABLE_KEYWORD_FEED_REL + "("
                    + IotHubDataHandler.KEY_KEYWORD_FEED_KEYWORD_ID + ","
                    + IotHubDataHandler.KEY_KEYWORD_FEED_FEED_ID + ") values (?,?)")
            : ("insert into " + IotHubDataHandler.TABLE_KEYWORD_FIELD_REL + "("
                    + IotHubDataHandler.KEY_KEYWORD_FIELD_KEYWORD_ID + ","
                    + IotHubDataHandler.KEY_KEYWORD_FIELD_FIELD_ID + ") values (?,?)");
    PreparedStatement psSelectKeyword = connection.prepareStatement(sqlSelectKeyword);
    PreparedStatement psInsertKeyword = connection.prepareStatement(sqlInsertKeyword,
            Statement.RETURN_GENERATED_KEYS);
    PreparedStatement psInsertRel = connection.prepareStatement(sqlInsertRelation);
    for (String keyword : keywords) {
        psSelectKeyword.setString(1, keyword);
        ResultSet rs = psSelectKeyword.executeQuery();
        if (rs.next()) {
            long keywordId = rs.getLong(IotHubDataHandler.KEY_KEYWORD_ID);
            psInsertRel.setLong(1, keywordId);
            psInsertRel.setLong(2, id);
            if (psInsertRel.executeUpdate() <= 0) {
                Log.e(TAG, "Linking keyword " + keyword + " and " + (isFeed ? "feed " : "field ") + id
                        + " did not work");
            }
        } else {
            psInsertKeyword.setString(1, keyword);
            psInsertKeyword.executeUpdate();
            ResultSet genKeysFeed = psInsertKeyword.getGeneratedKeys();
            if (genKeysFeed.next()) {
                long insertIdKeyword = genKeysFeed.getLong(1);
                psInsertRel.setLong(1, insertIdKeyword);
                psInsertRel.setLong(2, id);
                if (psInsertRel.executeUpdate() <= 0) {
                    Log.e(TAG, "Linking keyword " + keyword + " and " + (isFeed ? "feed " : "field ") + id
                            + " did not work");
                }
            }
            genKeysFeed.close();
        }
        rs.close();
    }
    psSelectKeyword.close();
    psInsertKeyword.close();
    psInsertRel.close();
}

From source file:uk.ac.cam.cl.dtg.segue.dao.users.PgUsers.java

/**
 * createUser.//from ww  w .j a  v a  2s .  c om
 * @param userToCreate - a user object to persist
 * @return a register user as just created.
 * @throws SegueDatabaseException
 */
private RegisteredUser createUser(final RegisteredUser userToCreate) throws SegueDatabaseException {
    // make sure student is default role if none set
    if (null == userToCreate.getRole()) {
        userToCreate.setRole(Role.STUDENT);
    }

    // make sure NOT_VERIFIED is default email verification status if none set
    if (null == userToCreate.getEmailVerificationStatus()) {
        userToCreate.setEmailVerificationStatus(EmailVerificationStatus.NOT_VERIFIED);
    }

    PreparedStatement pst;
    try (Connection conn = database.getDatabaseConnection()) {
        pst = conn.prepareStatement(
                "INSERT INTO users(family_name, given_name, email, role, "
                        + "date_of_birth, gender, registration_date, school_id, "
                        + "school_other, last_updated, email_verification_status, "
                        + "last_seen, default_level, password, secure_salt, reset_token, "
                        + "reset_expiry, email_verification_token) "
                        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
                Statement.RETURN_GENERATED_KEYS);
        // TODO: Change this to annotations or something to rely exclusively on the pojo.
        setValueHelper(pst, 1, userToCreate.getFamilyName());
        setValueHelper(pst, 2, userToCreate.getGivenName());
        setValueHelper(pst, 3, userToCreate.getEmail());
        setValueHelper(pst, 4, userToCreate.getRole());
        setValueHelper(pst, 5, userToCreate.getDateOfBirth());
        setValueHelper(pst, 6, userToCreate.getGender());
        setValueHelper(pst, 7, userToCreate.getRegistrationDate());
        setValueHelper(pst, 8, userToCreate.getSchoolId());
        setValueHelper(pst, 9, userToCreate.getSchoolOther());
        setValueHelper(pst, 10, userToCreate.getLastUpdated());
        setValueHelper(pst, 11, userToCreate.getEmailVerificationStatus());
        setValueHelper(pst, 12, userToCreate.getLastSeen());
        setValueHelper(pst, 13, userToCreate.getDefaultLevel());
        setValueHelper(pst, 14, userToCreate.getPassword());
        setValueHelper(pst, 15, userToCreate.getSecureSalt());
        setValueHelper(pst, 16, userToCreate.getResetToken());
        setValueHelper(pst, 17, userToCreate.getResetExpiry());
        setValueHelper(pst, 18, userToCreate.getEmailVerificationToken());

        if (pst.executeUpdate() == 0) {
            throw new SegueDatabaseException("Unable to save user.");
        }

        try (ResultSet generatedKeys = pst.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                Long id = generatedKeys.getLong(1);
                userToCreate.setId(id);
                return userToCreate;
            } else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        }

    } catch (SQLException e) {
        throw new SegueDatabaseException("Postgres exception", e);
    }
}

From source file:br.com.cobranca.util.Util.java

public static <T> int inserirRegistro(T obj, Connection con) throws Exception {

    int id = 0;/*  w ww.  j a v  a 2s .c o  m*/

    String nomeTabela = obj.getClass().getSimpleName();

    String strSql = "INSERT INTO " + nomeTabela.toUpperCase() + " (";
    boolean usarVirgula = false;

    for (Field field : obj.getClass().getDeclaredFields()) {
        field.setAccessible(true);

        if (usarVirgula) {
            strSql = strSql + ", ";
        }

        strSql = strSql + field.getName();

        if (!usarVirgula) {
            usarVirgula = true;
        }
    }

    strSql = strSql + ") VALUES (";

    usarVirgula = false;

    for (Field field : obj.getClass().getDeclaredFields()) {
        field.setAccessible(true);

        if (usarVirgula) {
            strSql = strSql + ", ";
        }

        strSql = strSql + "?";

        if (!usarVirgula) {
            usarVirgula = true;
        }
    }

    strSql = strSql + ")";

    PreparedStatement ps = con.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);

    try {

        int i = 1;
        for (Field field : obj.getClass().getDeclaredFields()) {

            String tipoColuna = field.getType().getSimpleName();

            if (tipoColuna.toUpperCase().contains("INT")) {
                tipoColuna = "Int";
            } else {
                tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna);
            }

            // obj . get + nome do campo
            Method met = obj.getClass().getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName()));

            if (tipoColuna.equals("Int")) {

                Integer valor = (Integer) met.invoke(obj);

                if (valor == null) {
                    ps.setString(i, null);
                } else {
                    ps.setInt(i, valor);
                }

            } else if (tipoColuna.equals("String")) {
                String valor = (String) met.invoke(obj);
                ps.setString(i, valor);
            } else if (tipoColuna.equals("Double")) {

                Double valor = (Double) met.invoke(obj);

                if (valor == null) {
                    ps.setString(i, null);
                } else {
                    ps.setDouble(i, valor);
                }

            } else if (tipoColuna.equals("Float")) {

                Float valor = (Float) met.invoke(obj);

                if (valor == null) {
                    ps.setString(i, null);
                } else {
                    ps.setFloat(i, valor);
                }

            } else if (tipoColuna.equals("Long")) {

                Long valor = (Long) met.invoke(obj);

                if (valor == null) {
                    ps.setString(i, null);
                } else {
                    ps.setLong(i, valor);
                }

            } else if (tipoColuna.equals("Boolean")) {
                Boolean valor = (Boolean) met.invoke(obj);

                if (valor == null) {
                    ps.setString(i, null);
                } else {
                    ps.setBoolean(i, valor);
                }

            } else if (tipoColuna.equals("Date")) {
                Date valor = (Date) met.invoke(obj);

                if (valor == null) {
                    ps.setString(i, null);
                } else {
                    ps.setDate(i, new java.sql.Date(valor.getTime()));
                }

            } else {
                return 0;
            }

            i++;
        }

        int qtdLinhasAfetadas = ps.executeUpdate();

        if (qtdLinhasAfetadas > 0) {

            try (ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) {
                    id = rs.getInt(1);
                }
            }

        }
    } catch (Exception ex) {
        throw new Exception(ex.getMessage());
    } finally {
        ps.close();
    }

    return id;
}

From source file:org.efaps.admin.user.Person.java

/**
 * @param _jaasSystem JAAS system which want to create a new person in eFaps
 * @param _jaasKey key of the person in the JAAS system
 * @param _userName name in the eFaps system (used as proposal, it's tested
 *            for uniqueness and changed if needed!)
 * @return new created person/*from   ww  w.  j  a  v  a  2  s.c o m*/
 * @throws EFapsException if person could not be created in eFaps
 * @see #assignToJAASSystem
 */
public static Person createPerson(final JAASSystem _jaasSystem, final String _jaasKey, final String _userName)
        throws EFapsException {
    long persId = 0;
    final Type persType = CIAdminUser.Person.getType();
    ConnectionResource rsrc = null;
    try {
        final Context context = Context.getThreadContext();

        rsrc = context.getConnectionResource();

        PreparedStatement stmt = null;
        try {
            StringBuilder cmd = new StringBuilder();

            // TODO: check for uniqueness!
            // TODO: hard coded mofifier and creator
            if (Context.getDbType().supportsGetGeneratedKeys()) {
                cmd.append("insert into ").append(persType.getMainTable().getSqlTable())
                        .append("(TYPEID,NAME,CREATOR,CREATED,MODIFIER,MODIFIED) ").append("values (");
            } else {
                persId = Context.getDbType().getNewId(rsrc.getConnection(),
                        persType.getMainTable().getSqlTable(), "ID");
                cmd.append("insert into ").append(persType.getMainTable().getSqlTable())
                        .append("(ID,TYPEID,NAME,CREATOR,CREATED,MODIFIER,MODIFIED) ").append("values (")
                        .append(persId).append(",");
            }
            cmd.append(persType.getId()).append(",").append("'").append(_userName).append("',")
                    .append(context.getPersonId()).append(",").append(Context.getDbType().getCurrentTimeStamp())
                    .append(",").append(context.getPersonId()).append(",")
                    .append(Context.getDbType().getCurrentTimeStamp()).append(")");

            if (persId == 0) {
                stmt = rsrc.getConnection().prepareStatement(cmd.toString(), new String[] { "ID" });
            } else {
                stmt = rsrc.getConnection().prepareStatement(cmd.toString());
            }

            int rows = stmt.executeUpdate();
            if (rows == 0) {
                Person.LOG.error(
                        "could not execute '" + cmd.toString() + "' for JAAS system '" + _jaasSystem.getName()
                                + "' person with key '" + _jaasKey + "' and user name '" + _userName + "'");
                throw new EFapsException(Person.class, "createPerson.NotInserted", cmd.toString(),
                        _jaasSystem.getName(), _jaasKey, _userName);
            }
            if (persId == 0) {
                final ResultSet resultset = stmt.getGeneratedKeys();
                if (resultset.next()) {
                    persId = resultset.getLong(1);
                }
            }

            stmt.close();

            cmd = new StringBuilder();
            cmd.append("insert into T_USERPERSON").append("(ID,FIRSTNAME,LASTNAME,EMAIL) ").append("values (")
                    .append(persId).append(",'-','-','-')");
            stmt = rsrc.getConnection().prepareStatement(cmd.toString());
            rows = stmt.executeUpdate();
            if (rows == 0) {
                Person.LOG.error(
                        "could not execute '" + cmd.toString() + "' for JAAS system '" + _jaasSystem.getName()
                                + "' person with key '" + _jaasKey + "' and user name '" + _userName + "'");
                throw new EFapsException(Person.class, "createPerson.NotInserted", cmd.toString(),
                        _jaasSystem.getName(), _jaasKey, _userName);
            }

        } catch (final SQLException e) {
            Person.LOG.error("could not create for JAAS system '" + _jaasSystem.getName()
                    + "' person with key '" + _jaasKey + "' and user name '" + _userName + "'", e);
            throw new EFapsException(Person.class, "createPerson.SQLException", e, _jaasSystem.getName(),
                    _jaasKey, _userName);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (final SQLException e) {
                throw new EFapsException(Person.class, "createPerson.SQLException", e, _jaasSystem.getName(),
                        _jaasKey);
            }
        }
        rsrc.commit();
    } finally {
        if (rsrc != null && rsrc.isOpened()) {
            rsrc.abort();
        }
    }

    final Person ret = Person.get(persId);
    ret.assignToJAASSystem(_jaasSystem, _jaasKey);
    return ret;
}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

private BigInteger addDataServiceRunForAllMessageTypes(Object message, int md5CollisionId,
        Authentication authentication, SoftwareIdentification dataServiceSoftwareId, int sourceSoftwareId)
        throws ApolloDatabaseException, Md5UtilsException {

    String userName = authentication.getRequesterId();
    String password = authentication.getRequesterPassword();

    String[] userIdTokens = parseUserId(userName);
    userName = userIdTokens[0];//  ww  w . j  a v a2  s  .co  m

    int softwareKey = getSoftwareIdentificationKey(dataServiceSoftwareId);
    int userKey = getUserKey(userName, password);

    try (Connection conn = datasource.getConnection()) {
        BigInteger simulationGroupId = getNewSimulationGroupId();

        String query = "INSERT INTO run (md5_hash_of_run_message, software_id, requester_id, last_service_to_be_called, simulation_group_id, md5_collision_id) VALUES (?, ?, ?, ?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, md5Utils.getMd5(message));
        pstmt.setInt(2, softwareKey);
        pstmt.setInt(3, userKey);
        pstmt.setInt(4, 1);
        pstmt.setInt(5, simulationGroupId.intValue());
        pstmt.setInt(6, md5CollisionId);
        pstmt.execute();

        ResultSet rs = pstmt.getGeneratedKeys();
        BigInteger runId;
        if (rs.next()) {
            runId = new BigInteger(rs.getString(1));
        } else {
            throw new ApolloDatabaseRecordNotInsertedException("Record not inserted!");
        }

        // ALSO NEED TO ADD serialized run data service message (JSON) to
        // run_data_content table...
        // use insertDataContentForRun for this
        int dataContentKey = addTextDataContent(jsonUtils.getJSONString(message));
        int runDataDescriptionId = getRunDataDescriptionId(ContentDataFormatEnum.TEXT,
                "data_retrieval_request_message.json", ContentDataTypeEnum.RUN_MESSAGE, sourceSoftwareId,
                getSoftwareIdentificationKey(dataServiceSoftwareId));
        // int runDataId = the following line returns the runDataId, but
        // it's not used at this point.
        associateContentWithRunId(new BigInteger(String.valueOf(runId)), dataContentKey, runDataDescriptionId);

        List<BigInteger> runIdsForDataService = new ArrayList<>();
        runIdsForDataService.add(runId);
        addRunIdsToSimulationGroup(simulationGroupId, runIdsForDataService);

        updateStatusOfRun(runId, MethodCallStatusEnum.LOADED_RUN_CONFIG_INTO_DATABASE,
                "Adding config information to the database for runId: " + runId.toString());

        return runId;
    } catch (SQLException ex) {
        throw new ApolloDatabaseException("SQLException attempting to add simulation run: " + ex.getMessage());
    }
}