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:at.alladin.rmbt.controlServer.SyncResource.java

@Post("json")
public String request(final String entity) {
    addAllowOrigin();/*w ww.ja v  a 2s. c  o  m*/

    JSONObject request = null;

    final ErrorList errorList = new ErrorList();
    final JSONObject answer = new JSONObject();
    String answerString;

    System.out.println(MessageFormat.format(labels.getString("NEW_SYNC_REQUEST"), getIP()));

    if (entity != null && !entity.isEmpty())
        // try parse the string to a JSON object
        try {
            request = new JSONObject(entity);

            String lang = request.optString("language");

            // Load Language Files for Client

            final List<String> langs = Arrays
                    .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*"));

            if (langs.contains(lang)) {
                errorList.setLanguage(lang);
                labels = ResourceManager.getSysMsgBundle(new Locale(lang));
            } else
                lang = settings.getString("RMBT_DEFAULT_LANGUAGE");

            //                System.out.println(request.toString(4));

            if (conn != null) {

                final JSONArray syncList = new JSONArray();

                UUID uuid = null;
                if (request.optString("uuid").length() > 0)
                    uuid = UUID.fromString(request.getString("uuid"));

                if (uuid != null && request.optString("sync_code").length() == 0) {

                    String syncCode = "";

                    try {

                        final PreparedStatement st = conn
                                .prepareStatement("SELECT rmbt_get_sync_code(CAST (? AS UUID)) AS code");
                        st.setString(1, uuid.toString());

                        final ResultSet rs = st.executeQuery();

                        if (rs.next())
                            syncCode = rs.getString("code");
                        else
                            errorList.addError("ERROR_DB_GET_SYNC_SQL");
                        // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"),
                        // new Object[] {uuid}));

                        rs.close();
                        st.close();
                    } catch (final SQLException e) {
                        e.printStackTrace();
                        errorList.addError("ERROR_DB_GET_SYNC_SQL");
                        // errorList.addError("ERROR_DB_GET_CLIENT_SQL");
                    }

                    if (errorList.getLength() == 0) {
                        final JSONObject jsonItem = new JSONObject();
                        //lower case code is easier to enter on mobile devices
                        jsonItem.put("sync_code", syncCode.toLowerCase(Locale.US));

                        syncList.put(jsonItem);

                    }
                } else if (uuid != null && request.optString("sync_code").length() > 0) {

                    final String syncCode = request.getString("sync_code").toUpperCase(Locale.US);
                    int syncGroup1 = 0;
                    int uid1 = 0;
                    int syncGroup2 = 0;
                    int uid2 = 0;

                    String msgTitle = labels.getString("SYNC_SUCCESS_TITLE");
                    String msgText = labels.getString("SYNC_SUCCESS_TEXT");

                    boolean error = false;

                    try {

                        PreparedStatement st = conn.prepareStatement(
                                "SELECT * FROM client WHERE sync_code = ? AND sync_code_timestamp + INTERVAL '1 month' > NOW()");
                        st.setString(1, syncCode);

                        ResultSet rs = st.executeQuery();

                        if (rs.next()) {
                            syncGroup1 = rs.getInt("sync_group_id");
                            uid1 = rs.getInt("uid");
                        } else {
                            msgTitle = labels.getString("SYNC_CODE_TITLE");
                            msgText = labels.getString("SYNC_CODE_TEXT");
                            error = true;
                            // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"),
                            // new Object[] {uuid}));
                        }
                        rs.close();
                        st.close();

                        st = conn.prepareStatement("SELECT * FROM client WHERE uuid = CAST(? AS UUID)");
                        st.setString(1, uuid.toString());

                        rs = st.executeQuery();

                        if (rs.next()) {
                            syncGroup2 = rs.getInt("sync_group_id");
                            uid2 = rs.getInt("uid");
                        } else {
                            msgTitle = labels.getString("SYNC_UUID_TITLE");
                            msgText = labels.getString("SYNC_UUID_TEXT");
                            error = true;
                            // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"),
                            // new Object[] {uuid}));
                        }
                        rs.close();
                        st.close();

                        if (syncGroup1 > 0 && syncGroup1 == syncGroup2) {
                            msgTitle = labels.getString("SYNC_GROUP_TITLE");
                            msgText = labels.getString("SYNC_GROUP_TEXT");
                            error = true;
                        }

                        if (uid1 > 0 && uid1 == uid2) {
                            msgTitle = labels.getString("SYNC_CLIENT_TITLE");
                            msgText = labels.getString("SYNC_CLIENT_TEXT");
                            error = true;
                        }

                        if (!error)
                            if (syncGroup1 == 0 && syncGroup2 == 0) {

                                int key = 0;

                                // create new group
                                st = conn.prepareStatement("INSERT INTO sync_group(tstamp) " + "VALUES(now())",
                                        Statement.RETURN_GENERATED_KEYS);

                                int affectedRows = st.executeUpdate();
                                if (affectedRows == 0)
                                    errorList.addError("ERROR_DB_STORE_SYNC_GROUP");
                                else {

                                    rs = st.getGeneratedKeys();
                                    if (rs.next())
                                        // Retrieve the auto generated
                                        // key(s).
                                        key = rs.getInt(1);
                                    rs.close();
                                }
                                st.close();

                                if (key > 0) {
                                    st = conn.prepareStatement(
                                            "UPDATE client SET sync_group_id = ? WHERE uid = ? OR uid = ?");
                                    st.setInt(1, key);
                                    st.setInt(2, uid1);
                                    st.setInt(3, uid2);

                                    affectedRows = st.executeUpdate();

                                    if (affectedRows == 0)
                                        errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP");
                                }

                            } else if (syncGroup1 == 0 && syncGroup2 > 0) {

                                // add 1 to 2

                                st = conn.prepareStatement("UPDATE client SET sync_group_id = ? WHERE uid = ?");
                                st.setInt(1, syncGroup2);
                                st.setInt(2, uid1);

                                final int affectedRows = st.executeUpdate();

                                if (affectedRows == 0)
                                    errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP");

                            } else if (syncGroup1 > 0 && syncGroup2 == 0) {

                                // add 2 to 1

                                st = conn
                                        .prepareStatement("UPDATE client SET sync_group_id = ? WHERE uid = ? ");
                                st.setInt(1, syncGroup1);
                                st.setInt(2, uid2);

                                final int affectedRows = st.executeUpdate();

                                if (affectedRows == 0)
                                    errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP");

                            } else if (syncGroup1 > 0 && syncGroup2 > 0) {

                                // add all of 2 to 1

                                st = conn.prepareStatement(
                                        "UPDATE client SET sync_group_id = ? WHERE sync_group_id = ?");
                                st.setInt(1, syncGroup1);
                                st.setInt(2, syncGroup2);

                                int affectedRows = st.executeUpdate();

                                if (affectedRows == 0)
                                    errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP");
                                else {

                                    // Delete empty group
                                    st = conn.prepareStatement("DELETE FROM sync_group WHERE uid = ?");
                                    st.setInt(1, syncGroup2);

                                    affectedRows = st.executeUpdate();

                                    if (affectedRows == 0)
                                        errorList.addError("ERROR_DB_DELETE_SYNC_GROUP");
                                }

                            }

                    } catch (final SQLException e) {
                        e.printStackTrace();
                        errorList.addError("ERROR_DB_GET_SYNC_SQL");
                        // errorList.addError("ERROR_DB_GET_CLIENT_SQL");
                    }

                    if (errorList.getLength() == 0) {

                        final JSONObject jsonItem = new JSONObject();

                        jsonItem.put("msg_title", msgTitle);
                        jsonItem.put("msg_text", msgText);
                        jsonItem.put("success", !error);
                        syncList.put(jsonItem);

                    }
                }

                answer.put("sync", syncList);

            } else
                errorList.addError("ERROR_DB_CONNECTION");

        } catch (final JSONException e) {
            errorList.addError("ERROR_REQUEST_JSON");
            System.out.println("Error parsing JSDON Data " + e.toString());
        }
    else
        errorList.addErrorString("Expected request is missing.");

    try {
        answer.putOpt("error", errorList.getList());
    } catch (final JSONException e) {
        System.out.println("Error saving ErrorList: " + e.toString());
    }

    answerString = answer.toString();

    return answerString;
}

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

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

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

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

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

        stmt.clearParameters();
        stmt.setString(1, sub); // active agent becomes owner automatically
        stmt.setString(2, (String) survey.get("organization"));
        stmt.setString(3, (String) survey.get("logo"));
        stmt.setString(4, (String) survey.get("name"));
        stmt.setString(5, (String) survey.get("description"));
        stmt.setString(6, (String) survey.get("resource"));
        stmt.setTimestamp(7,
                new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("start")).getTimeInMillis()));
        stmt.setTimestamp(8,
                new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("end")).getTimeInMillis()));
        stmt.setString(9, (String) survey.get("lang"));

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

        if (rs.next()) {
            return rs.getInt(1);
        } else {
            throw new NoSuchElementException("No new survey 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 survey was created!");
}

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

private int createForum(Connection connection, String forumName, int catgId, String forumDescription,
        int forumType, int accessType, boolean forumModerated, int gradeType, Date startDate, Date endDate,
        int lockEndDate) throws SQLException {
    if (logger.isDebugEnabled())
        logger.debug("creating forum with forumName :" + forumName + " for categoryId : " + catgId);
    if (logger.isDebugEnabled())
        logger.debug("Entering createForum......");

    int forumId = -1;

    try {// w  w w.  j a  v a2s  . c  o  m
        String forumGetMaxOrder = "SELECT MAX(forum_order) FROM jforum_forums";
        PreparedStatement p = connection.prepareStatement(forumGetMaxOrder);
        ResultSet rs = p.executeQuery();
        int order = 1;
        if (rs.next()) {
            order = rs.getInt(1) + 1;
        }

        rs.close();
        p.close();
        //if (logger.isInfoEnabled()) logger.info("forum order : "+ order);
        String forumAddNew = null;

        if (sqlService.getVendor().equals("oracle")) {
            forumAddNew = "INSERT INTO jforum_forums (forum_id, categories_id, forum_name, "
                    + "forum_desc, forum_order, forum_type, forum_access_type, forum_grade_type, start_date, end_date, lock_end_date) "
                    + "VALUES (jforum_forums_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            p = connection.prepareStatement(forumAddNew);
            p.setInt(1, catgId);
            p.setString(2, forumName);
            p.setString(3, forumDescription);
            p.setInt(4, order);
            p.setInt(5, forumType);
            p.setInt(6, accessType);
            if (gradeType == GRADE_BY_FORUM || gradeType == GRADE_BY_TOPIC)
                p.setInt(7, gradeType);
            else
                p.setInt(7, GRADE_DISABLED);

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

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

            p.executeUpdate();

            p.close();

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

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

            rs.close();
            p.close();
        } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
            forumAddNew = "INSERT INTO jforum_forums (categories_id, forum_name, forum_desc, "
                    + "forum_order, forum_type, forum_access_type, forum_grade_type, start_date, end_date, lock_end_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            p = connection.prepareStatement(forumAddNew, Statement.RETURN_GENERATED_KEYS);
            p.setInt(1, catgId);
            p.setString(2, forumName);
            p.setString(3, forumDescription);
            p.setInt(4, order);
            p.setInt(5, forumType);
            p.setInt(6, accessType);
            if (gradeType == GRADE_BY_FORUM || gradeType == GRADE_BY_TOPIC)
                p.setInt(7, gradeType);
            else
                p.setInt(7, GRADE_DISABLED);

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

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

            p.executeUpdate();

            rs = p.getGeneratedKeys();
            if (rs.next()) {
                forumId = rs.getInt(1);

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

        if (logger.isDebugEnabled())
            logger.debug("Exiting createForum......");
    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("createForum():Error while creating forum : " + e.toString());
        e.printStackTrace();
        throw e;
    }
    return forumId;
}

From source file:org.openanzo.jdbc.container.sql.NodeSQL.java

/**
 * Runs the insertCommonValueWithIdentity prepared statement.
  * <code>/*  ww w.j av a 2  s . c  o  m*/
 *        INSERT INTO {0} ( VALUE) VALUES( ?)    
 * </code>
 *
 *@param stmtProvider
 *         factory and cache of PreparedStatments
 *@param connection
 *          connection to underlying database
 *
 *@param value template parameter
 *
 *@param commonValuesTable template parameter
 *@return  Long
 *@throws  org.openanzo.jdbc.utils.RdbException
 */
public static Long insertCommonValueWithIdentity(
        final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider,
        final java.sql.Connection connection, String value, String commonValuesTable)
        throws org.openanzo.jdbc.utils.RdbException {
    java.sql.PreparedStatement ps = null;
    //long startTimer=System.currentTimeMillis();
    try {
        ps = stmtProvider.getPreparedSQLStatementWithGeneratedIDS(insertCommonValueWithIdentity,
                new String[] { commonValuesTable }, connection);
        int argc = 1;
        if (value == null) {
            throw new org.openanzo.jdbc.utils.RdbException(
                    org.openanzo.exceptions.ExceptionConstants.RDB.NULL_PARAMETER, "value",
                    "insertCommonValueWithIdentity");
        } else {
            ps.setString(argc++, value);
        }
        java.sql.ResultSet rs = null;
        try {
            if (ps.execute()) {
                rs = ps.getResultSet();
            } else {
                rs = ps.getGeneratedKeys();
            }
            if (rs != null && rs.next()) {
                return rs.getLong(1);
            } else {
                return null;
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (java.sql.SQLException sqle) {
                    if (log.isDebugEnabled())
                        log.debug(org.openanzo.exceptions.LogUtils.RDB_MARKER, "Error closing result set",
                                sqle);
                }
            }
        }

    } catch (java.sql.SQLException e) {
        throw new org.openanzo.jdbc.utils.RdbException(
                org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e,
                "insertCommonValueWithIdentity", stmtProvider.getSqlString(insertCommonValueWithIdentity),
                "" + "value=" + ((value != null) ? value.toString() : "null"), "" + "commonValuesTable="
                        + ((commonValuesTable != null) ? commonValuesTable.toString() : "null"));
    } finally {
        if (ps != null) {
            try {
                ps.close();
            } catch (java.sql.SQLException sqle) {
                if (log.isDebugEnabled())
                    log.debug(org.openanzo.exceptions.LogUtils.RDB_MARKER, "Error closing prepared statement",
                            sqle);
            }
        }
        //long endtimer=(System.currentTimeMillis()-startTimer);
        //if(endtimer>CUTOFF)System.out.println("[insertCommonValueWithIdentity]"+endtimer);
    }
}

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

/**
 * create topic/*  w ww . j a  v  a2s  . c  o m*/
 * @param connection connection
 * @param forumId forum id
 * @param topicTitle topic title
 * @param userId user id
 * @param topicType topic type
 * @param firstPostId first post id
 * @return
 */
private int createTopic(Connection connection, String toContextId, int fromForumId, int fromTopicId,
        int forumId, String topicTitle, int userId, int topicType, int topicGrade, int firstPostId,
        Date startDate, Date endDate, int lockEndDate, Date gradebookEndDate) {
    if (logger.isDebugEnabled())
        logger.debug("creating topic with topicName :" + topicTitle + " for forum_id : " + forumId);
    if (logger.isDebugEnabled())
        logger.debug("Entering createTopic......");

    int topicId = -1;
    try {
        PreparedStatement p = null;
        ResultSet rs = null;

        String topicAddNew = null;
        if (sqlService.getVendor().equals("oracle")) {
            topicAddNew = "INSERT INTO jforum_topics (topic_id, forum_id, topic_title, "
                    + "user_id, topic_time, topic_first_post_id, topic_last_post_id, "
                    + "topic_type, moderated, topic_grade, topic_export, start_date, end_date, lock_end_date)"
                    + "VALUES (jforum_topics_seq.nextval, ?, ?, ?, ?, SYSDATE, ?, ?, ?, ?, ?, ?, ?, ?)";

            p = connection.prepareStatement(topicAddNew);
            p.setInt(1, forumId);
            p.setString(2, topicTitle);
            p.setInt(3, userId);
            //p.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            p.setInt(4, 0);
            p.setInt(5, 0);
            p.setInt(6, topicType);
            p.setInt(7, 0);
            if (topicGrade == GRADE_YES)
                p.setInt(8, topicGrade);
            else
                p.setInt(8, GRADE_NO);

            p.setInt(9, EXPORT_YES);

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

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

            p.executeUpdate();

            p.close();

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

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

            rs.close();
            p.close();
        } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
            topicAddNew = "INSERT INTO jforum_topics (forum_id, topic_title, user_id, "
                    + "topic_time, topic_first_post_id, topic_last_post_id, topic_type, moderated, topic_grade, topic_export, start_date, end_date, lock_end_date)"
                    + "VALUES (?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            p = connection.prepareStatement(topicAddNew, Statement.RETURN_GENERATED_KEYS);
            p.setInt(1, forumId);
            p.setString(2, topicTitle);
            p.setInt(3, userId);
            //p.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            p.setInt(4, 0);
            p.setInt(5, 0);
            p.setInt(6, topicType);
            p.setInt(7, 0);
            if (topicGrade == GRADE_YES)
                p.setInt(8, topicGrade);
            else
                p.setInt(8, GRADE_NO);
            p.setInt(9, EXPORT_YES);
            if (startDate == null) {
                p.setTimestamp(10, null);
            } else {
                p.setTimestamp(10, new Timestamp(startDate.getTime()));
            }

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

            rs = p.getGeneratedKeys();
            if (rs.next()) {
                topicId = rs.getInt(1);

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

        //create grade for grade topic
        if (topicGrade == GRADE_YES) {

            String gradeModelSelectByForumTopicId = "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 = ? and topic_id = ?";

            PreparedStatement gradePrepStmnt = connection.prepareStatement(gradeModelSelectByForumTopicId);
            gradePrepStmnt.setInt(1, fromForumId);
            gradePrepStmnt.setInt(2, fromTopicId);

            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_TOPIC, forumId, topicId, 0, gradePoints,
                    addToGradebook, minPostsRequired, minPosts, topicTitle);

            if (startDate != null || endDate != null) {
            } else if (gradebookEndDate != null) {
                endDate = gradebookEndDate;
            }

            if ((gradeId > 0) && (addToGradebook == 1)) {
                createGradebookEntry(gradeId, topicTitle, gradePoints, endDate);
            }

        }
    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("createTopic():Error while creating topic : " + e.toString());
        e.printStackTrace();
    }

    if (logger.isDebugEnabled())
        logger.debug("Exiting createTopic......");
    return topicId;
}

From source file:SeedGenerator.MainForm.java

private void insertNounSynset(NounSynset[] nounSynset, int wordId, String objectName, String tableName) {
    try {/*  ww w  .j a  va2  s . c o  m*/
        for (NounSynset h : nounSynset) {
            for (String s : h.getWordForms()) {
                PreparedStatement existingTerm = con.prepareStatement(
                        "select id from recommender_" + objectName + "_" + tableName + " where term = ?;");
                existingTerm.setString(1, s);
                ResultSet rs = existingTerm.executeQuery();
                int termid = 0;
                if (rs.next()) {
                    termid = rs.getInt("id");
                } else {
                    PreparedStatement insert1 = con.prepareStatement(
                            "insert into recommender_" + objectName + "_" + tableName + " (term) values (?);",
                            Statement.RETURN_GENERATED_KEYS);
                    insert1.setString(1, s);
                    insert1.executeUpdate();

                    try (ResultSet generatedKeys = insert1.getGeneratedKeys()) {
                        if (generatedKeys.next()) {
                            termid = generatedKeys.getInt(1);
                        } else {
                            // throw new SQLException("Creating user failed, no ID obtained.");
                        }
                    }
                }
                PreparedStatement insert = con.prepareStatement("insert into recommender_" + objectName + "_"
                        + tableName + "_word " + " (termid, idfid) values (?,?);");
                insert.setInt(1, termid);
                insert.setInt(2, wordId);
                insert.executeUpdate();
                insert.close();
            }
        }
    } catch (Exception ex) {
        System.out.println(ex.getMessage());
    }
}

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

/**
 * create post//from  ww w. jav a 2s . c o m
 * @param connection
 * @param exisPostId
 * @param topicId
 * @param forumId
 * @param userId
 * @return
 */
private int createPost(Connection connection, int exisPostId, int topicId, int forumId, int userId) {
    if (logger.isDebugEnabled())
        logger.debug("Entering createPost......");
    //use exisPostId for post properties

    String addNewPost = null;
    PreparedStatement p = null;
    ResultSet rs = null;
    int postId = -1;

    try {
        String exisPostDetails = "SELECT post_id, topic_id, forum_id, user_id, post_time, "
                + "enable_bbcode, enable_html, enable_smilies, enable_sig,"
                + "attach , need_moderate FROM jforum_posts WHERE post_id = ?";
        PreparedStatement exisPostDetailsStmnt = connection.prepareStatement(exisPostDetails);
        exisPostDetailsStmnt.setInt(1, exisPostId);
        ResultSet rsexisPostDetails = exisPostDetailsStmnt.executeQuery();

        int enableBbcode = 0;
        int enableHtml = 0;
        int enableSmilies = 0;
        int enableSig = 0;
        int attach = 0;
        int needModerate = 0;

        if (rsexisPostDetails.next()) {
            enableBbcode = rsexisPostDetails.getInt("enable_bbcode");
            enableHtml = rsexisPostDetails.getInt("enable_html");
            enableSmilies = rsexisPostDetails.getInt("enable_smilies");
            enableSig = rsexisPostDetails.getInt("enable_sig");
            attach = rsexisPostDetails.getInt("attach");
            needModerate = rsexisPostDetails.getInt("need_moderate");
        }
        exisPostDetailsStmnt.close();
        rsexisPostDetails.close();

        if (sqlService.getVendor().equals("oracle")) {
            addNewPost = "INSERT INTO jforum_posts (post_id, topic_id, forum_id, user_id, "
                    + "post_time, poster_ip, enable_bbcode, enable_html, enable_smilies, "
                    + "enable_sig, post_edit_time, attach, need_moderate) "
                    + "VALUES (jforum_posts_seq.nextval, ?, ?, ?, SYSDATE, ?, ?, ?, ?,?, SYSDATE, ?, ?)";

            p = connection.prepareStatement(addNewPost);
            p.setInt(1, topicId);
            p.setInt(2, forumId);
            p.setLong(3, userId);
            //p.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            UsageSession usageSession = UsageSessionService.getSession();
            p.setString(4, usageSession.getIpAddress());
            p.setInt(5, enableBbcode);
            p.setInt(6, enableHtml);
            p.setInt(7, enableSmilies);
            p.setInt(8, enableSig);
            p.setInt(9, attach);
            p.setInt(10, needModerate);
            p.executeUpdate();

            p.close();

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

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

            rs.close();
            p.close();
        } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
            addNewPost = "INSERT INTO jforum_posts (topic_id, forum_id, user_id, "
                    + "post_time, poster_ip, enable_bbcode, enable_html, enable_smilies, "
                    + "enable_sig, post_edit_time, attach, need_moderate) "
                    + "VALUES (?, ?, ?, NOW(), ?, ?, ?, ?, ?, NOW(), ?, ?)";
            p = connection.prepareStatement(addNewPost, Statement.RETURN_GENERATED_KEYS);
            p.setInt(1, topicId);
            p.setInt(2, forumId);
            p.setLong(3, userId);
            //p.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            UsageSession usageSession = UsageSessionService.getSession();
            p.setString(4, usageSession.getIpAddress());
            p.setInt(5, enableBbcode);
            p.setInt(6, enableHtml);
            p.setInt(7, enableSmilies);
            p.setInt(8, enableSig);
            p.setInt(9, attach);
            p.setInt(10, needModerate);
            p.executeUpdate();

            rs = p.getGeneratedKeys();
            if (rs.next()) {
                postId = rs.getInt(1);

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

    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("createPost():Error while creating post : " + e.toString());
        e.printStackTrace();
    }
    if (logger.isDebugEnabled())
        logger.debug("Exiting createPost......");

    return postId;
}

From source file:uk.ac.ebi.sail.server.data.DataManager.java

public Integer addProjection(ProjectionShadow p) throws ProjectionManagementException {
    Connection conn = null;/*  w w  w .j  a  v  a 2 s .co  m*/
    ResultSet rst = null;
    try {

        Projection pj = p.createProjection();
        for (int clid : p.getClassifiers()) {
            Classifier cl = classifiers.get(clid);

            if (cl == null)
                throw new ProjectionManagementException("Invalid classifer ID=" + clid,
                        ProjectionManagementException.INV_CLASSIFIER_ID);

            pj.addClassifier(cl);
        }

        conn = dSrc.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(insertProjectionSQL, Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1, p.getName());
        pstmt.setString(2, p.getDescription());

        pstmt.executeUpdate();

        rst = pstmt.getGeneratedKeys();

        int id = -1;
        if (rst.next())
            id = rst.getInt(1);

        pstmt.close();

        pstmt = conn.prepareStatement(insertProjectionContentSQL);

        int n = 1;
        for (int clid : p.getClassifiers()) {
            pstmt.setInt(1, id);
            pstmt.setInt(2, clid);
            pstmt.setInt(3, n++);

            pstmt.executeUpdate();
        }

        pstmt.close();

        p.setId(id);

        projectionList.add(p);

        return id;

    } catch (SQLException e) {
        Log.error("SQL error", e);
        throw new ProjectionManagementException("SQL error", e, ProjectionManagementException.SQL_ERROR);
    } finally {
        if (rst != null)
            try {
                rst.close();
            } catch (SQLException e) {
            }

        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                Log.error("Connection closing error", e);
            }

    }
}

From source file:uk.ac.ebi.sail.server.data.DataManager.java

public int addCollection(CollectionShadow rs) throws CollectionManagementException {
    Connection conn = null;/*  w  ww.java  2s .c  o  m*/
    ResultSet rst = null;
    try {

        Collection<Annotation> annots = null;
        if (rs.getAnnotations() != null) {
            annots = new ArrayList<Annotation>(rs.getAnnotations().size());

            for (AnnotationShadow ant : rs.getAnnotations()) {
                Tag t = tags.get(ant.getTag());

                if (t == null)
                    throw new CollectionManagementException("Invalid tag ID=" + ant.getTag(),
                            CollectionManagementException.INV_TAG_ID);

                Annotation a = new Annotation();
                a.setText(ant.getText());
                a.setTag(t);

                annots.add(a);
            }
        }

        conn = dSrc.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(insertCollectionSQL, Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1, rs.getName());
        pstmt.setLong(2, System.currentTimeMillis());

        pstmt.executeUpdate();

        rst = pstmt.getGeneratedKeys();

        int id = -1;
        if (rst.next())
            id = rst.getInt(1);

        pstmt.close();

        if (rs.getAnnotations() != null) {
            pstmt = conn.prepareStatement(insertCollectionAnnotationSQL);

            for (AnnotationShadow ant : rs.getAnnotations()) {
                pstmt.setInt(1, id);
                pstmt.setInt(2, ant.getTag());
                pstmt.setString(3, ant.getText());

                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        rs.setId(id);
        collectionList.add(rs);

        SampleCollection r = rs.createCollection();
        r.setAnnotations(annots);
        collections.put(rs.getId(), r);

        return id;

    } catch (SQLException e) {
        Log.error("SQL error", e);
        throw new CollectionManagementException("SQL error", e, CollectionManagementException.SQL_ERROR);
    } finally {
        if (rst != null)
            try {
                rst.close();
            } catch (SQLException e) {
            }

        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                Log.error("Connection closing error", e);
            }

    }
}

From source file:uk.ac.ebi.sail.server.data.DataManager.java

public int addStudy(StudyShadow rs) throws StudyManagementException {
    Connection conn = null;/*w  w w. j  a  v  a  2 s  .c om*/
    ResultSet rst = null;
    try {

        Collection<Annotation> annots = null;
        if (rs.getAnnotations() != null) {
            annots = new ArrayList<Annotation>(rs.getAnnotations().size());

            for (AnnotationShadow ant : rs.getAnnotations()) {
                Tag t = tags.get(ant.getTag());

                if (t == null)
                    throw new StudyManagementException("Invalid tag ID=" + ant.getTag(),
                            StudyManagementException.INV_TAG_ID);

                Annotation a = new Annotation();
                a.setText(ant.getText());
                a.setTag(t);

                annots.add(a);
            }
        }

        conn = dSrc.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(insertStudySQL, Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1, rs.getName());
        pstmt.setLong(2, System.currentTimeMillis());

        pstmt.executeUpdate();

        rst = pstmt.getGeneratedKeys();

        int id = -1;
        if (rst.next())
            id = rst.getInt(1);

        pstmt.close();

        if (rs.getAnnotations() != null) {
            pstmt = conn.prepareStatement(insertStudyAnnotationSQL);

            for (AnnotationShadow ant : rs.getAnnotations()) {
                pstmt.setInt(1, id);
                pstmt.setInt(2, ant.getTag());
                pstmt.setString(3, ant.getText());

                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        if (rs.getCollections() != null) {
            pstmt = conn.prepareStatement(insertStudyCollectionsSQL);

            for (int cohId : rs.getCollections()) {
                pstmt.setInt(1, id);
                pstmt.setInt(2, cohId);

                pstmt.executeUpdate();
            }

            pstmt.close();
        }

        rs.setId(id);
        studyList.add(rs);

        return id;

    } catch (SQLException e) {
        Log.error("SQL error", e);
        throw new StudyManagementException("SQL error", e, StudyManagementException.SQL_ERROR);
    } finally {
        if (rst != null)
            try {
                rst.close();
            } catch (SQLException e) {
            }

        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                Log.error("Connection closing error", e);
            }

    }
}