Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement 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.kawanfw.sql.servlet.sql.ServerStatementRawExecute.java

/**
 * Execute the passed SQL PreparedStatement as execute() sand return: <br>
 * - The result set as a List of Maps for SELECT statements. <br>
 * - The return code for other statements
 * /*from   ww w. jav  a 2s.  c  o  m*/
 * @param sqlOrder
 *            the qsql order
 * @param sqlParms
 *            the sql parameters
 * @param out
 *            the output stream where to write to result set output
 * 
 * 
 * @throws SQLException
 */
private void executePrepStatement(OutputStream out) throws SQLException, IOException {
    String sqlOrder = statementHolder.getSqlOrder();

    PreparedStatement preparedStatement = null;

    boolean usesAutoGeneratedKeys = false;

    if (statementHolder.getAutoGeneratedKeys() != -1) {
        preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getAutoGeneratedKeys());
        usesAutoGeneratedKeys = true;
    } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) {
        preparedStatement = connection.prepareStatement(sqlOrder,
                statementHolder.getColumnIndexesAutogenerateKeys());
        usesAutoGeneratedKeys = true;
    } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) {
        preparedStatement = connection.prepareStatement(sqlOrder,
                statementHolder.getColumnNamesAutogenerateKeys());
        usesAutoGeneratedKeys = true;
    } else {
        preparedStatement = connection.prepareStatement(sqlOrder);
    }

    Map<Integer, Integer> parameterTypes = null;
    Map<Integer, String> parameterStringValues = null;

    // Class to set all the statement parameters
    ServerPreparedStatementParameters serverPreparedStatementParameters = null;

    try {

        ServerSqlUtil.setStatementProperties(preparedStatement, statementHolder);

        parameterTypes = statementHolder.getParameterTypes();
        parameterStringValues = statementHolder.getParameterStringValues();

        if (!SqlConfiguratorCall.allowExecute(sqlConfigurator, username, connection)) {
            String ipAddress = request.getRemoteAddr();

            SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress,
                    sqlOrder, new Vector<Object>());

            String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized for execute}"
                    + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}"
                    + "{sql values: " + parameterStringValues + "}]";

            throw new SecurityException(message);
        }

        debug("before ServerPreparedStatementParameters");

        serverPreparedStatementParameters = new ServerPreparedStatementParameters(request, username,
                fileConfigurator, preparedStatement, statementHolder);
        serverPreparedStatementParameters.setParameters();

        // Throws a SQL exception if the order is not authorized:
        debug("before new SqlSecurityChecker()");

        boolean isAllowed = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder,
                serverPreparedStatementParameters.getParameterValues());

        if (!isAllowed) {

            String ipAddress = request.getRemoteAddr();

            SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress,
                    sqlOrder, serverPreparedStatementParameters.getParameterValues());

            String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized}"
                    + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}"
                    + "{sql values: " + parameterStringValues + "}]";

            throw new SecurityException(message);
        }

        debug("before preparedStatement.execute()");

        ServerSqlUtil.setMaxRowsToReturn(preparedStatement, sqlConfigurator);

        boolean isResultSet = preparedStatement.execute();

        if (isResultSet) {
            ResultSet rs = preparedStatement.getResultSet();

            try {

                //out.write(TransferStatus.SEND_OK + CR_LF);
                ServerSqlManager.writeLine(out, TransferStatus.SEND_OK);

                ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator,
                        fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder);

                resultSetWriter.write(rs);
            } finally {
                if (rs != null)
                    rs.close();
            }
        } else {
            int rc = preparedStatement.getUpdateCount();

            //out.write(TransferStatus.SEND_OK + CR_LF);
            //out.write("getUpdateCount=" + rc + CR_LF);
            ServerSqlManager.writeLine(out, TransferStatus.SEND_OK);
            ServerSqlManager.writeLine(out, "getUpdateCount=" + rc);

            // Write the preparedStatement.getGeneratedKeys() on the stream
            // if necessary
            if (usesAutoGeneratedKeys) {
                ResultSet rs = preparedStatement.getGeneratedKeys();

                try {
                    ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator,
                            fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder);
                    resultSetWriter.write(rs);
                } finally {
                    if (rs != null)
                        rs.close();
                }
            }

        }

    } catch (SQLException e) {
        ServerLogger.getLogger().log(Level.WARNING,
                Tag.PRODUCT_PRODUCT_FAIL + CR_LF + "Prepared statement: " + sqlOrder + CR_LF + "- sql order : "
                        + sqlOrder + CR_LF + "- sql parms : " + parameterTypes + CR_LF + "- sql values: "
                        + parameterStringValues + CR_LF + "- exception : " + e.toString());
        throw e;
    } finally {
        // Close the ServerPreparedStatementParameters
        if (serverPreparedStatementParameters != null) {
            serverPreparedStatementParameters.close();
        }

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

    }
}

From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java

/**
 * creates category//from w  w w . j  a  va 2  s  . c  o  m
 * @param connection
 * @param toContextId
 * @param catName
 * @param moderated
 * @return categoy Id
 */
private int createCategory(Connection connection, String toContextId, String catName, int moderated,
        int gradable, Date startDate, Date endDate, int lockEndDate, int exisCatgeoryId) throws Exception {
    if (logger.isDebugEnabled())
        logger.debug("creating category with title :" + catName + " for site : " + toContextId);
    int categoryId = -1;
    try {
        int order = 1;
        String categoryGetMaxOrderSql = "SELECT MAX(display_order) FROM jforum_categories";
        PreparedStatement p = connection.prepareStatement(categoryGetMaxOrderSql);

        ResultSet rs = p.executeQuery();
        if (rs.next()) {
            order = rs.getInt(1) + 1;
        }
        rs.close();
        p.close();

        String categoryAddNewSql = null;
        if (sqlService.getVendor().equals("oracle")) {
            categoryAddNewSql = "INSERT INTO jforum_categories (categories_id, title, display_order, moderated, gradable, start_date, end_date, lock_end_date) "
                    + "VALUES (jforum_categories_seq.nextval, ?, ?, ?, ?, ?, ?, ?)";
            p = connection.prepareStatement(categoryAddNewSql);
            p.setString(1, catName);
            p.setInt(2, order);
            p.setInt(3, moderated);
            p.setInt(4, gradable);

            if (startDate == null) {
                p.setTimestamp(5, null);
            } else {
                p.setTimestamp(5, new Timestamp(startDate.getTime()));
            }

            if (endDate == null) {
                p.setTimestamp(6, null);
                p.setInt(7, 0);
            } else {
                p.setTimestamp(6, new Timestamp(endDate.getTime()));
                p.setInt(7, lockEndDate);
            }

            p.executeUpdate();

            p.close();

            String categoryLastGeneratedCategoryId = "SELECT jforum_categories_seq.currval  FROM DUAL";
            p = connection.prepareStatement(categoryLastGeneratedCategoryId);
            rs = p.executeQuery();

            if (rs.next()) {
                categoryId = rs.getInt(1);
            }

            rs.close();
            p.close();
        } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
            categoryAddNewSql = "INSERT INTO jforum_categories (title, display_order, moderated, gradable, start_date, end_date, lock_end_date) VALUES (?, ?, ?, ?, ?, ?, ?)";
            p = connection.prepareStatement(categoryAddNewSql, Statement.RETURN_GENERATED_KEYS);
            p.setString(1, catName);
            p.setInt(2, order);
            p.setInt(3, moderated);
            p.setInt(4, gradable);

            if (startDate == null) {
                p.setTimestamp(5, null);
            } else {
                p.setTimestamp(5, new Timestamp(startDate.getTime()));
            }

            if (endDate == null) {
                p.setTimestamp(6, null);
                p.setInt(7, 0);
            } else {
                p.setTimestamp(6, new Timestamp(endDate.getTime()));
                p.setInt(7, lockEndDate);
            }

            p.executeUpdate();

            rs = p.getGeneratedKeys();
            if (rs.next()) {
                categoryId = rs.getInt(1);
            }
            rs.close();
            p.close();
        }

        String courseCategoryAddNewSql = "INSERT INTO jforum_sakai_course_categories (course_id,categories_id) VALUES (?, ?)";
        p = connection.prepareStatement(courseCategoryAddNewSql);

        p.setString(1, toContextId);
        p.setInt(2, categoryId);

        p.execute();
        p.close();

        // create grade if category is gradable
        if (gradable == 1) {
            String gradeModelSelectByCategoryId = "SELECT grade_id, context, grade_type, forum_id, topic_id, points, add_to_gradebook, categories_id, min_posts, min_posts_required "
                    + "FROM jforum_grade WHERE forum_id = 0 and topic_id = 0 and categories_id = ?";

            PreparedStatement gradePrepStmnt = connection.prepareStatement(gradeModelSelectByCategoryId);
            gradePrepStmnt.setInt(1, exisCatgeoryId);

            ResultSet rsGrade = gradePrepStmnt.executeQuery();

            float gradePoints = 0f;

            if (rsGrade.next()) {
                gradePoints = rsGrade.getFloat("points");
            }
            int addToGradebook = rsGrade.getInt("add_to_gradebook");
            boolean minPostsRequired = false;
            int minPosts = 0;
            if (rsGrade.getInt("min_posts_required") == 1) {
                minPostsRequired = true;
                minPosts = rsGrade.getInt("min_posts");
            }

            rsGrade.close();
            gradePrepStmnt.close();

            int gradeId = createGrade(connection, toContextId, GRADE_BY_CATEGORY, 0, 0, categoryId, gradePoints,
                    addToGradebook, minPostsRequired, minPosts, catName);

            // add to gradebook
            if ((gradeId > 0) && (addToGradebook == 1)) {
                createGradebookEntry(gradeId, catName, JForumUtil.toDoubleScore(gradePoints), endDate);
            }

        }

    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("createCategory():Error occurred while creating category with title : " + catName);
        e.printStackTrace();
        throw e;
    }

    return categoryId;

}

From source file:pt.iflow.flows.FlowHolderBean.java

private synchronized State insertOrUpdateFlow(UserInfoInterface userInfo, String file, String name, byte[] data,
        boolean forceCreate, boolean makeVersion, String comment) {
    // recorrer a um metodo privado para efectuar a actualizacao
    // propriamente dita.
    // Esse mesmo metodo sera usado pelo deploy no caso de ser necessario
    // actualizar o catalogo.

    State result = new State();
    Connection db = null;/*from ww w  .  j  ava  2  s.  co  m*/
    PreparedStatement pst = null;
    ResultSet rs = null;
    boolean flowFound = false;
    int flowid = -1;
    try {
        db = Utils.getDataSource().getConnection();
        db.setAutoCommit(false);

        String query = "select flowid,flowversion from flow where flowfile=? and organizationid=?";

        Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query1: " + query);
        pst = db.prepareStatement(query);
        pst.setString(1, file);
        pst.setString(2, userInfo.getOrganization());

        rs = pst.executeQuery();

        if (rs.next()) {
            flowFound = true;
            flowid = rs.getInt("flowid");
            result.version = rs.getInt("flowversion");
        }
        Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow",
                "Flow exists " + flowFound + "id: " + flowid + "; version " + result.version);

        rs.close();
        pst.close();

        boolean copyToHistory = false;
        if (flowFound) {
            int arg = 0;
            query = "update flow set " + (StringUtils.isNotEmpty(name) ? "flowname=?," : "") + "flowdata=?,"
                    + (makeVersion ? "flowversion=flowversion+1," : "") + "modified=? where flowid=?";
            Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query2a: " + query);
            pst = db.prepareStatement(query);
            if (StringUtils.isNotEmpty(name))
                pst.setString(++arg, name);
            pst.setBinaryStream(++arg, new ByteArrayInputStream(data), data.length);
            pst.setTimestamp(++arg, new Timestamp(System.currentTimeMillis()));
            pst.setInt(++arg, flowid);
            int upd = pst.executeUpdate();
            pst.close();
            result.created = false;
            copyToHistory = (upd != 0);
        } else if (forceCreate) {
            if (null == name)
                name = file;
            Timestamp now = new Timestamp(System.currentTimeMillis());
            query = DBQueryManager.getQuery("FlowHolder.INSERT_FLOW");
            Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query2b: " + query);
            pst = db.prepareStatement(query, new String[] { "flowid" });
            pst.setString(1, name);
            pst.setString(2, file);
            pst.setTimestamp(3, now);
            pst.setString(4, userInfo.getOrganization());
            pst.setBinaryStream(5, new ByteArrayInputStream(data), data.length);
            pst.setTimestamp(6, now);
            pst.executeUpdate();
            rs = pst.getGeneratedKeys();
            if (rs.next()) {
                result.version = 1;
                result.created = true;
                flowid = rs.getInt(1);
                copyToHistory = true;
            }
            rs.close();
            pst.close();

            notifyNewFlow(userInfo, flowid);

        } else {
            throw new Exception("Cannot create flow.");
        }

        if (copyToHistory && makeVersion) {
            if (null != comment && comment.length() > MAX_COMMENT_SIZE)
                comment = comment.substring(0, MAX_COMMENT_SIZE);
            query = DBQueryManager.getQuery("FlowHolder.COPY_FLOW_TO_HISTORY");
            Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query3: " + query);
            pst = db.prepareStatement(query);
            pst.setString(1, comment);
            pst.setInt(2, flowid);
            pst.executeUpdate();
            pst.close();
            result.version++;
        }

        db.commit();
        result.success = true;
    } catch (Exception e) {
        try {
            db.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
        result.success = false;
    } finally {
        DatabaseInterface.closeResources(db, pst, rs);
    }

    result.flowid = flowid;

    return result;
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///from w ww. ja va2 s. co  m
public void insertTopic(Topic topic, int virtualWikiId, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC);
            int topicId = this.nextTopicId(conn);
            topic.setTopicId(topicId);
            stmt.setInt(index++, topic.getTopicId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        stmt.setInt(index++, virtualWikiId);
        stmt.setString(index++, topic.getName());
        stmt.setInt(index++, topic.getTopicType().id());
        stmt.setInt(index++, (topic.getReadOnly() ? 1 : 0));
        if (topic.getCurrentVersionId() == null) {
            stmt.setNull(index++, Types.INTEGER);
        } else {
            stmt.setInt(index++, topic.getCurrentVersionId());
        }
        stmt.setTimestamp(index++, topic.getDeleteDate());
        stmt.setInt(index++, (topic.getAdminOnly() ? 1 : 0));
        stmt.setString(index++, topic.getRedirectTo());
        stmt.setInt(index++, topic.getNamespace().getId());
        stmt.setString(index++, topic.getPageName());
        stmt.setString(index++, topic.getPageName().toLowerCase());
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            topic.setTopicId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *//*from  ww w.  j a  v  a2 s. co m*/
public void insertUserBlock(UserBlock userBlock, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_USER_BLOCK);
            int blockId = this.nextUserBlockId(conn);
            userBlock.setBlockId(blockId);
            stmt.setInt(index++, userBlock.getBlockId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_USER_BLOCK_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        if (userBlock.getWikiUserId() == null) {
            stmt.setNull(index++, Types.INTEGER);
        } else {
            stmt.setInt(index++, userBlock.getWikiUserId());
        }
        stmt.setString(index++, userBlock.getIpAddress());
        stmt.setTimestamp(index++, userBlock.getBlockDate());
        stmt.setTimestamp(index++, userBlock.getBlockEndDate());
        stmt.setString(index++, userBlock.getBlockReason());
        stmt.setInt(index++, userBlock.getBlockedByUserId());
        stmt.setTimestamp(index++, userBlock.getUnblockDate());
        stmt.setString(index++, userBlock.getUnblockReason());
        if (userBlock.getUnblockedByUserId() == null) {
            stmt.setNull(index++, Types.INTEGER);
        } else {
            stmt.setInt(index++, userBlock.getUnblockedByUserId());
        }
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            userBlock.setBlockId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *//*from  w w  w. j av  a  2s .c o  m*/
public void insertWikiFileVersion(WikiFileVersion wikiFileVersion, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_FILE_VERSION);
            int fileVersionId = this.nextWikiFileVersionId(conn);
            wikiFileVersion.setFileVersionId(fileVersionId);
            stmt.setInt(index++, wikiFileVersion.getFileVersionId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_FILE_VERSION_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        if (wikiFileVersion.getUploadDate() == null) {
            Timestamp uploadDate = new Timestamp(System.currentTimeMillis());
            wikiFileVersion.setUploadDate(uploadDate);
        }
        stmt.setInt(index++, wikiFileVersion.getFileId());
        stmt.setString(index++, wikiFileVersion.getUploadComment());
        stmt.setString(index++, wikiFileVersion.getUrl());
        if (wikiFileVersion.getAuthorId() == null) {
            stmt.setNull(index++, Types.INTEGER);
        } else {
            stmt.setInt(index++, wikiFileVersion.getAuthorId());
        }
        stmt.setString(index++, wikiFileVersion.getAuthorDisplay());
        stmt.setTimestamp(index++, wikiFileVersion.getUploadDate());
        stmt.setString(index++, wikiFileVersion.getMimeType());
        stmt.setLong(index++, wikiFileVersion.getFileSize());
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            wikiFileVersion.setFileVersionId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
}

From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java

private int createGrade(Connection connection, String toContextId, int gradeType, int forumId, int topicId,
        int categoryId, float gradePoints, int addToGradebook, boolean minPostsRequired, int minPosts,
        String gradebookTitle) throws SQLException {

    int gradeId = -1;
    ResultSet rs = null;// w ww . j  a v  a 2  s.com

    String gradebookToolId = ServerConfigurationService.getString(JForumGradeService.GRADEBOOK_TOOL_ID);

    if ((gradebookToolId == null) || (gradebookToolId.trim().length() == 0)) {
        gradebookToolId = "sakai.gradebook.tool";
    }

    Site site = null;
    try {
        site = SiteService.getSite(ToolManager.getCurrentPlacement().getContext());
    } catch (IdUnusedException e) {

        if (logger.isWarnEnabled()) {
            logger.warn(e.toString(), e);
        }
    }

    boolean gradebookExists = false;
    if ((site != null) && (site.getToolForCommonId(gradebookToolId) != null)) {
        gradebookExists = true;
    }

    if (sqlService.getVendor().equals("oracle")) {
        String gradeModelAddNewsql = "INSERT INTO jforum_grade(grade_id, context, grade_type, forum_id, topic_id, categories_id, points, add_to_gradebook, min_posts_required, min_posts) "
                + "VALUES (jforum_grade_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        PreparedStatement p = connection.prepareStatement(gradeModelAddNewsql);
        p.setString(1, toContextId);
        p.setInt(2, gradeType);
        p.setInt(3, forumId);
        p.setInt(4, topicId);
        p.setInt(5, categoryId);
        p.setFloat(6, gradePoints);

        if (gradebookExists) {
            String gradebookUid = ToolManager.getInstance().getCurrentPlacement().getContext();
            JForumGBService jForumGBService = null;
            jForumGBService = (JForumGBService) ComponentManager
                    .get("org.etudes.api.app.jforum.JForumGBService");
            if (!jForumGBService.isAssignmentDefined(gradebookUid, gradebookTitle)) {
                p.setInt(7, addToGradebook);
            } else {
                addToGradebook = 0;
                p.setInt(7, 0);
            }
        } else {
            p.setInt(7, 0);
        }

        if (minPostsRequired) {
            p.setInt(8, 1);
            p.setInt(9, minPosts);
        } else {
            p.setInt(8, 0);
            p.setInt(9, 0);
        }

        p.executeUpdate();
        p.close();

        String gradeLastGeneratedGradeId = "SELECT jforum_grade_seq.currval FROM DUAL";
        p = connection.prepareStatement(gradeLastGeneratedGradeId);
        rs = p.executeQuery();

        if (rs.next()) {
            gradeId = rs.getInt(1);
        }

        rs.close();
        p.close();

    } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
        String gradeModelAddNewsql = "INSERT INTO jforum_grade(context, grade_type, forum_id, topic_id, categories_id, points, add_to_gradebook, min_posts_required, min_posts) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        PreparedStatement p = connection.prepareStatement(gradeModelAddNewsql, Statement.RETURN_GENERATED_KEYS);
        p.setString(1, toContextId);
        p.setInt(2, gradeType);
        p.setInt(3, forumId);
        p.setInt(4, topicId);
        p.setInt(5, categoryId);
        p.setFloat(6, gradePoints);

        if (gradebookExists) {
            String gradebookUid = ToolManager.getInstance().getCurrentPlacement().getContext();
            JForumGBService jForumGBService = null;
            jForumGBService = (JForumGBService) ComponentManager
                    .get("org.etudes.api.app.jforum.JForumGBService");
            if (!jForumGBService.isAssignmentDefined(gradebookUid, gradebookTitle)) {
                p.setInt(7, addToGradebook);
            } else {
                addToGradebook = 0;
                p.setInt(7, 0);
            }
        } else {
            p.setInt(7, 0);
        }

        if (minPostsRequired) {
            p.setInt(8, 1);
            p.setInt(9, minPosts);
        } else {
            p.setInt(8, 0);
            p.setInt(9, 0);
        }

        p.executeUpdate();

        rs = p.getGeneratedKeys();
        if (rs.next()) {
            gradeId = rs.getInt(1);
        }
        rs.close();
        p.close();
    }

    return gradeId;
}

From source file:org.kawanfw.sql.servlet.sql.ServerStatement.java

/**
 * Execute the passed SQL Statement and return: <br>
 * - The result set as a List of Maps for SELECT statements. <br>
 * - The return code for other statements
 * /*from  w w w.  j  a va2s . c o m*/
 * @param sqlOrder
 *            the qsql order
 * @param sqlParms
 *            the sql parameters
 * @param out
 *            the output stream where to write to result set output
 * 
 * 
 * @throws SQLException
 */
private void executeQueryOrUpdatePrepStatement(OutputStream out) throws SQLException, IOException {

    String sqlOrder = statementHolder.getSqlOrder();

    debug("statementHolder: " + statementHolder.getSqlOrder());
    debug("sqlOrder       : " + sqlOrder);

    // sqlOrder = HtmlConverter.fromHtml(sqlOrder);

    if (statementHolder.isDoExtractResultSetMetaData()) {
        sqlOrder = DbVendorManager.addLimit1(sqlOrder, connection);
    }

    PreparedStatement preparedStatement = null;

    boolean usesAutoGeneratedKeys = false;

    if (statementHolder.getAutoGeneratedKeys() != -1) {
        preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getAutoGeneratedKeys());
        usesAutoGeneratedKeys = true;
    } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) {
        preparedStatement = connection.prepareStatement(sqlOrder,
                statementHolder.getColumnIndexesAutogenerateKeys());
        usesAutoGeneratedKeys = true;
    } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) {
        preparedStatement = connection.prepareStatement(sqlOrder,
                statementHolder.getColumnNamesAutogenerateKeys());
        usesAutoGeneratedKeys = true;
    } else {
        preparedStatement = connection.prepareStatement(sqlOrder);
    }

    Map<Integer, Integer> parameterTypes = null;
    Map<Integer, String> parameterStringValues = null;

    // Class to set all the statement parameters
    ServerPreparedStatementParameters serverPreparedStatementParameters = null;

    try {

        ServerSqlUtil.setStatementProperties(preparedStatement, statementHolder);

        parameterTypes = statementHolder.getParameterTypes();
        parameterStringValues = statementHolder.getParameterStringValues();

        debug("before ServerPreparedStatementParameters");

        serverPreparedStatementParameters = new ServerPreparedStatementParameters(request, username,
                fileConfigurator, preparedStatement, statementHolder);
        serverPreparedStatementParameters.setParameters();

        // Throws a SQL exception if the order is not authorized:
        debug("before new SqlSecurityChecker()");

        boolean isAllowedAfterAnalysis = sqlConfigurator.allowStatementAfterAnalysis(username, connection,
                sqlOrder, serverPreparedStatementParameters.getParameterValues());

        if (!isAllowedAfterAnalysis) {

            String ipAddress = request.getRemoteAddr();

            SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress,
                    sqlOrder, serverPreparedStatementParameters.getParameterValues());

            debug("Before SqlConfiguratorCall.runIfStatementRefused");
            SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, ipAddress, connection, ipAddress,
                    sqlOrder, serverPreparedStatementParameters.getParameterValues());
            debug("After  SqlConfiguratorCall.runIfStatementRefused");

            String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized}"
                    + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}"
                    + "{sql values: " + parameterStringValues + "}]";

            throw new SecurityException(message);
        }

        isAllowedAfterAnalysis = SqlConfiguratorCall.allowResultSetGetMetaData(sqlConfigurator, username,
                connection);

        if (statementHolder.isDoExtractResultSetMetaData() && !isAllowedAfterAnalysis) {
            String message = Tag.PRODUCT_SECURITY + " ResultSet.getMetaData() Query not authorized.";
            throw new SecurityException(message);
        }

        debug("before executeQuery() / executeUpdate()");

        if (statementHolder.isExecuteUpdate()) {

            if (!SqlConfiguratorCall.allowExecuteUpdate(sqlConfigurator, username, connection)) {
                String ipAddress = request.getRemoteAddr();

                SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress,
                        sqlOrder, serverPreparedStatementParameters.getParameterValues());

                String message = Tag.PRODUCT_SECURITY + " ["
                        + "{Prepared Statement not authorized for executeUpdate}" + "{sql order : " + sqlOrder
                        + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues
                        + "}]";

                throw new SecurityException(message);
            }

            int rc = preparedStatement.executeUpdate();

            //br.write(TransferStatus.SEND_OK + CR_LF);
            //br.write(rc + CR_LF);

            ServerSqlManager.writeLine(out, TransferStatus.SEND_OK);
            ServerSqlManager.writeLine(out, "" + rc);

            // Write the preparedStatement.getGeneratedKeys() on the stream
            // if necessary
            if (usesAutoGeneratedKeys) {
                ResultSet rs = null;

                try {
                    rs = preparedStatement.getGeneratedKeys();

                    ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator,
                            fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder);
                    resultSetWriter.write(rs);
                } finally {

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

        } else {

            ResultSet rs = null;

            try {

                if (statementHolder.isDoExtractResultSetMetaData()) {
                    preparedStatement.setMaxRows(1);
                } else {
                    ServerSqlUtil.setMaxRowsToReturn(preparedStatement, sqlConfigurator);
                }

                rs = preparedStatement.executeQuery();

                //br.write(TransferStatus.SEND_OK + CR_LF);
                ServerSqlManager.writeLine(out, TransferStatus.SEND_OK);

                // If a a ResultSet.getMetaData() has been asked, send it
                // back!
                if (statementHolder.isDoExtractResultSetMetaData()) {
                    ResultSetMetaDataWriter resultSetMetaDataWriter = new ResultSetMetaDataWriter(out,
                            commonsConfigurator, sqlConfigurator);
                    resultSetMetaDataWriter.write(rs);
                } else {
                    // print(rs, br);
                    ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator,
                            fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder);
                    resultSetWriter.write(rs);
                }

            } finally {

                if (rs != null) {
                    rs.close();
                }
            }
        }
    } catch (SQLException e) {
        ServerLogger.getLogger().log(Level.WARNING,
                Tag.PRODUCT_EXCEPTION_RAISED + CR_LF + "Prepared statement: " + sqlOrder + CR_LF
                        + "- sql order : " + sqlOrder + CR_LF + "- sql parms : " + parameterTypes + CR_LF
                        + "- sql values: " + parameterStringValues + CR_LF + "- exception : " + e.toString());
        throw e;
    } finally {
        // Close the ServerPreparedStatementParameters
        if (serverPreparedStatementParameters != null) {
            serverPreparedStatementParameters.close();
        }

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

        // Clean all
        parameterTypes = null;
        parameterStringValues = null;
        serverPreparedStatementParameters = null;

    }
}

From source file:i5.las2peer.services.mobsos.SurveyService.java

/**
 * Stores a new questionnaire described with JSON into the MobSOS database.
 * The MobSOS database thereby generates a new id returned by this method.
 * @throws UnsupportedEncodingException 
 * @throws ParseException /*from   ww w. j a v a  2  s  .c om*/
 */
private int storeNewQuestionnaire(JSONObject questionnaire)
        throws IllegalArgumentException, SQLException, UnsupportedEncodingException, ParseException {

    String sub = (String) getActiveUserInfo().get("sub");

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rset = null;

    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement("insert into " + jdbcSchema
                + ".questionnaire(owner, organization, logo, name, description, lang) values (?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);

        stmt.clearParameters();
        stmt.setString(1, sub); // active agent becomes owner automatically
        stmt.setString(2, (String) questionnaire.get("organization"));
        stmt.setString(3, (String) questionnaire.get("logo"));
        stmt.setString(4, (String) questionnaire.get("name"));
        stmt.setString(5, (String) questionnaire.get("description"));
        stmt.setString(6, (String) questionnaire.get("lang"));

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

        if (rs.next()) {
            return rs.getInt(1);
        } else {
            throw new NoSuchElementException("No new questionnaire was created!");
        }

    } catch (UnsupportedOperationException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rset != null)
                rset.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    throw new NoSuchElementException("No new questionnaire was created!");
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///  w ww . j a v a 2s  .  c o  m
public void insertTopicVersions(List<TopicVersion> topicVersions, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    boolean useBatch = (topicVersions.size() > 1);
    try {
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION);
        } else if (useBatch) {
            // generated keys don't work in batch mode
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT);
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        int topicVersionId = -1;
        if (!this.autoIncrementPrimaryKeys() || useBatch) {
            // manually retrieve next topic version id when using batch
            // mode or when the database doesn't support generated keys.
            topicVersionId = this.nextTopicVersionId(conn);
        }
        for (TopicVersion topicVersion : topicVersions) {
            if (!this.autoIncrementPrimaryKeys() || useBatch) {
                // FIXME - if two threads update the database simultaneously then
                // it is possible that this code could set the topic version ID
                // to a value that is different from what the database ends up
                // using.
                topicVersion.setTopicVersionId(topicVersionId++);
            }
            this.prepareTopicVersionStatement(topicVersion, stmt);
            if (useBatch) {
                stmt.addBatch();
            } else {
                stmt.executeUpdate();
            }
            if (this.autoIncrementPrimaryKeys() && !useBatch) {
                rs = stmt.getGeneratedKeys();
                if (!rs.next()) {
                    throw new SQLException("Unable to determine auto-generated ID for database record");
                }
                topicVersion.setTopicVersionId(rs.getInt(1));
            }
        }
        if (useBatch) {
            stmt.executeBatch();
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
        stmt = null;
        rs = null;
    }
}