Example usage for java.sql Statement RETURN_GENERATED_KEYS

List of usage examples for java.sql Statement RETURN_GENERATED_KEYS

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. RETURN_GENERATED_KEYS

Introduction

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

Prototype

int RETURN_GENERATED_KEYS

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

Click Source Link

Document

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

Usage

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

/**
 * create jforum user//from  www  .  j  a va 2s . c  o  m
 * @param connection
 * @param username
 * @param email
 * @param firstName
 * @param lastName
 * @param sakaiUserId
 * @return
 * @throws Exception
 */
private int createJforumUser(Connection connection, String username, String email, String firstName,
        String lastName, String sakaiUserId) throws SQLException {
    int jforumUserId = -1;

    try {
        PreparedStatement p = null;
        ResultSet rs = null;

        String addNewUser = null;
        if (sqlService.getVendor().equals("oracle")) {
            addNewUser = "INSERT INTO jforum_users (user_id, username, user_password, "
                    + "user_email, user_regdate, user_fname, user_lname, sakai_user_id) VALUES "
                    + "(jforum_users_seq.nextval, ?, ?, ?,  SYSDATE, ?, ?, ?)";

            p = connection.prepareStatement(addNewUser);
            p.setString(1, username);
            p.setString(2, "password");
            p.setString(3, email);
            p.setString(4, firstName);
            p.setString(5, lastName);
            p.setString(6, sakaiUserId);

            p.executeUpdate();

            p.close();

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

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

            rs.close();
            p.close();
        } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
            addNewUser = "INSERT INTO jforum_users (username, user_password, "
                    + "user_email, user_regdate, user_fname, user_lname, "
                    + "sakai_user_id) VALUES (?, ?, ?, NOW(), ?, ?, ?)";

            p = connection.prepareStatement(addNewUser, Statement.RETURN_GENERATED_KEYS);
            p.setString(1, username);
            p.setString(2, "password");
            p.setString(3, email);
            p.setString(4, firstName);
            p.setString(5, lastName);
            p.setString(6, sakaiUserId);

            p.executeUpdate();

            rs = p.getGeneratedKeys();
            if (rs.next()) {
                jforumUserId = rs.getInt(1);
            }
            rs.close();
            p.close();
        }
    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("createJforumUser():Error occurred while creating user with username : " + username);
        e.printStackTrace();
        throw e;
    }
    return jforumUserId;
}

From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java

/**
 * Resolves counter group database id./*from   ww w  .  ja v a2 s . com*/
 *
 * @param group Given group
 * @param conn Connection to database
 * @return Id
 * @throws java.sql.SQLException
 */
private long getCounterGroupId(CounterGroup group, Connection conn) throws SQLException {
    PreparedStatement select = null;
    PreparedStatement insert = null;
    ResultSet rsSelect = null;
    ResultSet rsInsert = null;

    try {
        select = conn.prepareStatement(crudQueries.getStmtSelectCounterGroup());
        select.setString(1, group.getName());

        rsSelect = select.executeQuery();

        if (rsSelect.next()) {
            return rsSelect.getLong(1);
        }

        insert = conn.prepareStatement(crudQueries.getStmtInsertCounterGroup(),
                Statement.RETURN_GENERATED_KEYS);
        insert.setString(1, group.getName());
        insert.executeUpdate();

        rsInsert = insert.getGeneratedKeys();

        if (!rsInsert.next()) {
            throw new SqoopException(CommonRepositoryError.COMMON_0010);
        }

        return rsInsert.getLong(1);
    } finally {
        closeResultSets(rsSelect, rsInsert);
        closeStatements(select, insert);
    }
}

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

/**
 * {@inheritDoc}/*from w  w  w  .  j a v  a2s .c  o  m*/
 */
@Override
public void createLink(MLink link, Connection conn) {
    PreparedStatement stmt = null;
    int result;
    try {
        stmt = conn.prepareStatement(STMT_INSERT_LINK, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, link.getName());
        stmt.setLong(2, link.getConnectorId());
        stmt.setBoolean(3, link.getEnabled());
        stmt.setString(4, link.getCreationUser());
        stmt.setTimestamp(5, new Timestamp(link.getCreationDate().getTime()));
        stmt.setString(6, link.getLastUpdateUser());
        stmt.setTimestamp(7, new Timestamp(link.getLastUpdateDate().getTime()));

        result = stmt.executeUpdate();
        if (result != 1) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0012, Integer.toString(result));
        }

        ResultSet rsetConnectionId = stmt.getGeneratedKeys();

        if (!rsetConnectionId.next()) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0013);
        }

        long connectionId = rsetConnectionId.getLong(1);

        createInputValues(STMT_INSERT_LINK_INPUT, connectionId, link.getConnectorLinkConfig().getConfigs(),
                conn);
        link.setPersistenceId(connectionId);

    } catch (SQLException ex) {
        logException(ex, link);
        throw new SqoopException(DerbyRepoError.DERBYREPO_0019, ex);
    } finally {
        closeStatements(stmt);
    }
}

From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java

/**
 * Resolves counter id.//from   w  w w .  j  a va 2s . c  om
 *
 * @param counter Given counter
 * @param conn Connection to database
 * @return Id
 * @throws java.sql.SQLException
 */
private long getCounterId(Counter counter, Connection conn) throws SQLException {
    PreparedStatement select = null;
    PreparedStatement insert = null;
    ResultSet rsSelect = null;
    ResultSet rsInsert = null;

    try {
        select = conn.prepareStatement(crudQueries.getStmtSelectCounter());
        select.setString(1, counter.getName());

        rsSelect = select.executeQuery();

        if (rsSelect.next()) {
            return rsSelect.getLong(1);
        }

        insert = conn.prepareStatement(crudQueries.getStmtInsertCounter(), Statement.RETURN_GENERATED_KEYS);
        insert.setString(1, counter.getName());
        insert.executeUpdate();

        rsInsert = insert.getGeneratedKeys();

        if (!rsInsert.next()) {
            throw new SqoopException(CommonRepositoryError.COMMON_0010);
        }

        return rsInsert.getLong(1);
    } finally {
        closeResultSets(rsSelect, rsInsert);
        closeStatements(select, insert);
    }
}

From source file:edu.ku.brc.specify.conversion.AgentConverter.java

/**
 * @param oldAgentId//from w  w w .  j av  a  2 s .c o m
 * @param agentIDMapper
 * @param tblWriter
 */
protected void copyAgentFromOldToNew(final Integer oldAgentId, final IdTableMapper agentIDMapper) {
    boolean doDebug = false;

    DBTableInfo agentTI = DBTableIdMgr.getInstance().getByShortClassName("Agent");
    DBFieldInfo lastNameField = agentTI.getFieldByColumnName("LastName");
    DBFieldInfo firstNameField = agentTI.getFieldByColumnName("FirstName");

    StringBuilder sql = new StringBuilder("SELECT ");
    if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
        BasicSQLUtils.removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
    }
    BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent",
            BasicSQLUtils.myDestinationServerType);

    List<String> oldAgentFieldNames = getFieldNamesFromSchema(oldDBConn, "agent");

    String oldFieldListStr = buildSelectFieldList(oldAgentFieldNames, "agent");
    sql.append(oldFieldListStr);
    sql.append(" FROM agent WHERE AgentID = " + oldAgentId);

    //log.info(oldFieldListStr);

    List<String> newAgentFieldNames = getFieldNamesFromSchema(newDBConn, "agent");
    String newFieldListStr = buildSelectFieldList(newAgentFieldNames, "agent");

    //log.info(newFieldListStr);

    int lastNameLen = 120;

    HashMap<String, Integer> oldIndexFromNameMap = new HashMap<String, Integer>();
    int inx = 1;
    for (String fldName : oldAgentFieldNames) {
        oldIndexFromNameMap.put(fldName, inx++);
    }

    HashMap<String, Integer> newIndexFromNameMap = new HashMap<String, Integer>();
    inx = 1;
    for (String fldName : newAgentFieldNames) {
        newIndexFromNameMap.put(fldName, inx++);
    }

    try {
        // So first we hash each AddressID and the value is set to 0 (false)
        Statement stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet rsX = stmtX.executeQuery(sql.toString());

        int agentIDInx = oldIndexFromNameMap.get("AgentID");
        int agentTypeInx = oldIndexFromNameMap.get("AgentType");
        int nameInx = oldIndexFromNameMap.get("Name");
        int lastNameInx = oldIndexFromNameMap.get("LastName");
        int firstNameInx = oldIndexFromNameMap.get("FirstName");

        // log.debug(sql.toString());

        int cnt = 0;
        while (rsX.next()) {
            int agentId = rsX.getInt(1);

            StringBuilder sqlStr = new StringBuilder();
            sqlStr.append("INSERT INTO agent ");
            sqlStr.append("(" + newFieldListStr);
            sqlStr.append(")");
            sqlStr.append(" VALUES (");

            int fCnt = 0;
            for (String fieldName : newAgentFieldNames) {
                if (fCnt > 0)
                    sqlStr.append(", ");

                if (StringUtils.contains(fieldName.toLowerCase(), "disciplineid")) {
                    sqlStr.append(conv.getDisciplineId());

                } else if (StringUtils.contains(fieldName, "FirstName")) {
                    String firstName = rsX.getString(firstNameInx);
                    if (firstName != null && firstName.length() > firstNameField.getLength()) {
                        String str = firstName.substring(0, firstNameField.getLength());
                        tblWriter.logError("Agent id: " + rsX.getString(agentIDInx)
                                + " - Concatinating First Name FROM [" + firstName + "] to [" + str + "]");
                        firstName = str;
                    }
                    sqlStr.append(BasicSQLUtils.getStrValue(firstName));

                } else if (StringUtils.contains(fieldName, "LastName")) {
                    int oldType = rsX.getInt(agentTypeInx);
                    int srcColInx = oldType != 1 ? nameInx : lastNameInx;
                    String lName = rsX.getString(srcColInx);

                    if (lName == null && oldType != 1) {
                        lName = rsX.getString(lastNameInx);
                    }

                    if (lName != null && lName.length() > lastNameField.getLength()) {
                        String str = lName.substring(0, firstNameField.getLength());
                        tblWriter.logError("Agent id: " + rsX.getString(agentIDInx)
                                + " - Concatinating Last Name FROM [" + lName + "] to [" + str + "]");
                        lName = str;
                    }

                    String lstName = lName;
                    lName = lstName == null ? null
                            : lstName.length() <= lastNameLen ? lstName : lstName.substring(0, lastNameLen);

                    sqlStr.append(BasicSQLUtils.getStrValue(lName));

                } else {
                    String value = "";
                    Integer index;

                    if (fieldName.equals("ModifiedByAgentID")) {
                        index = oldIndexFromNameMap.get("LastEditedBy");
                    } else {
                        index = oldIndexFromNameMap.get(fieldName);
                    }

                    if (index == null) {
                        // log.debug(fieldName);
                        value = "NULL";

                    } else if (fCnt == 0) {
                        value = agentIDMapper.get(agentId).toString();

                    } else {
                        value = BasicSQLUtils.getStrValue(rsX.getObject(index.intValue()));
                    }

                    BasicSQLUtilsMapValueIFace valueMapper = conv.getColumnValueMapper().get(fieldName);
                    if (valueMapper != null) {
                        value = valueMapper.mapValue(value);
                    }
                    sqlStr.append(value);
                }
                fCnt++;
            }
            sqlStr.append(")");
            // log.info(sqlStr.toString());

            Statement updateStatement = newDBConn.createStatement();
            // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
            if (doDebug) {
                log.info(sqlStr.toString());
            }
            updateStatement.executeUpdate(sqlStr.toString(), Statement.RETURN_GENERATED_KEYS);
            Integer newAgentId = BasicSQLUtils.getInsertedId(updateStatement);
            if (newAgentId == null) {
                throw new RuntimeException("Couldn't get the Agent's inserted ID");
            }
            updateStatement.clearBatch();
            updateStatement.close();
            updateStatement = null;

            //conv.addAgentDisciplineJoin(newAgentId, conv.getDisciplineId());

            cnt++;
            BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent",
                    BasicSQLUtils.myDestinationServerType);

        }
    } catch (Exception ex) {
        log.error(ex);
        ex.printStackTrace();
        System.exit(0);
    }
}

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

/**
 * create post/*ww  w . ja  v a2 s.co  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:org.lockss.db.DbMigrator.java

/**
 * Migrates a table./*from   www.  j  a v  a2s . c om*/
 * 
 * @param sourceConn
 *          A Connection with the connection to the source database.
 * @param targetConn
 *          A Connection with the connection to the target database.
 * @param columns
 *          A DbColumn[] with the definition of the table columns.
 * @throws DbMigratorException
 *           if any problem occurred migrating the table.
 */
private void migrateTable(Connection sourceConn, Connection targetConn, DbTable table)
        throws DbMigratorException {
    final String DEBUG_HEADER = "migrateTable(): ";
    if (log.isDebug2())
        log.debug2(DEBUG_HEADER + "Starting...");

    try {
        String tableName = table.getName();
        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "tableName = '" + tableName + "'");

        String createTableQuery = table.getCreateQuery();
        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "createTableQuery = '" + createTableQuery + "'");

        boolean created = true;

        // Check whether any existing data in the target table needs to be
        // deleted because the table may have multiple identical rows.
        if (table.isRepeatedRowsAllowed()) {
            // Yes: Remove the table if it does exist.
            removeTargetTableIfPresent(targetConn, tableName);

            // Create the target table.
            targetDbManagerSql.createTable(targetConn, tableName, createTableQuery);
        } else {
            // No: Create the target table, if necessary.
            created = createTableIfNeeded(targetConn, tableName, createTableQuery);
        }

        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "created = " + created);

        // Check whether there may be data in the target table.
        if (!created && !tableExists(targetConn, SEQ_TRANSLATION_TABLE, true)) {
            // Validate that the entire table has been successfully migrated and
            // finish.
            long rowCount = validateTableRowCount(sourceConn, targetConn, table.getRow());
            log.info("Table '" + tableName + "' successfully migrated - " + rowCount + " rows.");

            if (log.isDebug2())
                log.debug2(DEBUG_HEADER + "Done.");
            return;
        }

        boolean hasPrimaryKey = false;
        int pkIndex = -1;
        int index = 0;

        DbRow row = table.getRow();
        DbColumn[] columns = row.getColumnsAsArray();

        // Find out whether the table has a primary key by looping on its columns.
        for (DbColumn column : columns) {
            // Check whether this column is the table primary key.
            if (column.isPk()) {
                if (!sequenceTranslation.containsKey(tableName)) {
                    // Yes: Initialize the primary key sequence translator data in
                    // memory.
                    sequenceTranslation.put(tableName, new HashMap<Long, Long>());
                }

                // Populate the primary key sequence translator data in memory with
                // the data in the database.
                populateTargetSequenceTranslation(targetConn, tableName);

                // Remember the primary key of this table.
                hasPrimaryKey = true;
                if (log.isDebug3())
                    log.debug3(DEBUG_HEADER + "hasPrimaryKey = " + hasPrimaryKey);

                pkIndex = index;
                if (log.isDebug3())
                    log.debug3(DEBUG_HEADER + "pkIndex = " + pkIndex);
                break;
            } else {
                // No: Try the next column.
                index++;
            }
        }

        // Determine whether any foreign key columns in this table that need
        // translation can be translated.
        boolean canTranslate = true;

        for (DbColumn column : columns) {
            if (column.getFkTable() != null) {
                String fkTable = column.getFkTable().toLowerCase();
                log.debug3(DEBUG_HEADER + "fkTable = '" + fkTable + "'.");

                if (!sequenceTranslation.containsKey(fkTable)) {
                    canTranslate = false;
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "canTranslate = " + canTranslate);
                    break;
                }
            }
        }

        String readSourceQuery = row.getReadRowSql();
        PreparedStatement readSource = null;
        ResultSet sourceResultSet = null;

        try {
            // Get the rows from the source table.
            readSource = sourceDbManagerSql.prepareStatement(sourceConn, readSourceQuery);
            sourceResultSet = sourceDbManagerSql.executeQuery(readSource);

            // Loop through all the rows from the source table.
            while (sourceResultSet.next()) {
                // Get the values of the various columns for this row.
                for (DbColumn column : columns) {
                    column.getValue(sourceResultSet);
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "Read source " + column.getName() + " = '" + column.getValue()
                                + "'.");
                }

                // Check whether the table has not been created and it has a primary
                // key.
                if (!created && hasPrimaryKey) {
                    // Yes: Check whether the row has already been migrated.
                    if (sequenceTranslation.get(tableName).containsKey(columns[pkIndex].getValue())) {
                        // Yes: Continue with the next row.
                        if (log.isDebug3())
                            log.debug3(DEBUG_HEADER + "Translated PK found.");
                        continue;
                    }
                }

                // Check whether the row cannot be translated.
                if (!canTranslate) {
                    // Yes: Continue with the next row.
                    continue;
                }

                boolean translated = false;

                // Check whether the table already existed and it is possible to
                // identify this row in it.
                if (!created && !table.isRepeatedRowsAllowed()) {
                    for (DbColumn column : columns) {
                        if (column.getFkTable() != null) {
                            // Translate this foreign key.
                            Long translatedFk = sequenceTranslation.get(column.getFkTable().toLowerCase())
                                    .get(column.getValue());
                            if (log.isDebug3())
                                log.debug3(DEBUG_HEADER + "FK conversion: " + column.getValue() + " => "
                                        + translatedFk);
                            column.setValue(translatedFk);
                        }
                    }

                    translated = true;

                    // Try to find this row in the existing target table.
                    long rowCount = countMatchingTargetRows(targetConn, table);
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "rowCount = " + rowCount);

                    // Determine whether this row had already been migrated.
                    if (rowCount == 1) {
                        // Yes: Do nothing more with this row from the source table.
                        continue;
                    }
                }

                // Write the row to the target table.
                String writeTargetQuery = row.getWriteRowSql();
                PreparedStatement writeTarget = null;
                ResultSet targetResultSet = null;

                try {
                    // Handle a table with a primary key differently to be able to
                    // extract the generated primary key.
                    if (hasPrimaryKey) {
                        writeTarget = targetDbManagerSql.prepareStatement(targetConn, writeTargetQuery,
                                Statement.RETURN_GENERATED_KEYS);
                    } else {
                        writeTarget = targetDbManagerSql.prepareStatement(targetConn, writeTargetQuery);
                    }

                    index = 1;

                    // Loop through all the columns in the table.
                    for (DbColumn column : columns) {
                        // Check whether this is a primary key.
                        if (column.isPk()) {
                            // Yes: No parameter is set in this case, as the value is
                            // generated.
                            if (log.isDebug3())
                                log.debug3(DEBUG_HEADER + "Skip write " + "target parameter primary key '"
                                        + column.getName() + "'.");
                        } else {
                            // No: Check whether this is a foreign key.
                            if (column.getFkTable() != null && !translated) {
                                // Yes: Translate this foreign key.
                                Long translatedFk = sequenceTranslation.get(column.getFkTable().toLowerCase())
                                        .get(column.getValue());
                                if (log.isDebug3())
                                    log.debug3(DEBUG_HEADER + "FK conversion: " + column.getValue() + " => "
                                            + translatedFk);
                                column.setValue(translatedFk);
                            }

                            // Set the parameter for this column in the prepared statement.
                            if (log.isDebug3())
                                log.debug3(DEBUG_HEADER + "Set write " + "target parameter " + index + " with '"
                                        + column.getValue() + "'.");
                            column.setParameter(writeTarget, index++);
                        }
                    }

                    // Write the row.
                    int addedCount = targetDbManagerSql.executeUpdate(writeTarget);
                    if (log.isDebug3())
                        log.debug3(DEBUG_HEADER + "addedCount = " + addedCount);

                    // Check whether this column is the primary key.
                    if (hasPrimaryKey) {
                        // Yes: Get the generated primary key.
                        targetResultSet = writeTarget.getGeneratedKeys();

                        if (!targetResultSet.next()) {
                            throw new DbMigratorException("No primary key created.");
                        }

                        Long targetPkSeq = targetResultSet.getLong(1);
                        if (log.isDebug3())
                            log.debug3(DEBUG_HEADER + "targetPkSeq = " + targetPkSeq);

                        // Save the translation of the primary key of this row.
                        saveSequenceTranslation(targetConn, tableName, (Long) columns[pkIndex].getValue(),
                                targetPkSeq);
                    }
                } catch (SQLException sqle) {
                    String message = "Cannot write the target '" + tableName + "' table";
                    log.error(message, sqle);
                    log.error("SQL = '" + writeTargetQuery + "'.");
                    for (DbColumn column : columns) {
                        log.error(column.getName() + " = '" + column.getValue() + "'.");
                    }
                    throw new DbMigratorException(message, sqle);
                } catch (RuntimeException re) {
                    String message = "Cannot write the target '" + tableName + "' table";
                    log.error(message, re);
                    log.error("SQL = '" + writeTargetQuery + "'.");
                    for (DbColumn column : columns) {
                        log.error(column.getName() + " = '" + column.getValue() + "'.");
                    }
                    throw new DbMigratorException(message, re);
                } finally {
                    DbManagerSql.safeCloseStatement(writeTarget);
                }
            }
        } catch (SQLException sqle) {
            String message = "Cannot read the source '" + tableName + "' table";
            log.error(message, sqle);
            log.error("SQL = '" + readSourceQuery + "'.");
            throw new DbMigratorException(message, sqle);
        } catch (RuntimeException re) {
            String message = "Cannot read the source '" + tableName + "' table";
            log.error(message, re);
            log.error("SQL = '" + readSourceQuery + "'.");
            throw new DbMigratorException(message, re);
        } finally {
            DbManagerSql.safeCloseResultSet(sourceResultSet);
            DbManagerSql.safeCloseStatement(readSource);
        }

        // Compare the rows in both tables.
        long rowCount = validateTableRowCount(sourceConn, targetConn, row);
        log.info("Table '" + tableName + "' successfully migrated - " + rowCount + " rows.");
    } catch (SQLException sqle) {
        throw new DbMigratorException(sqle);
    } catch (RuntimeException re) {
        throw new DbMigratorException(re);
    } finally {
        try {
            DbManagerSql.commitOrRollback(targetConn, log);
            DbManagerSql.rollback(sourceConn, log);
        } catch (SQLException sqle) {
            throw new DbMigratorException(sqle);
        } catch (RuntimeException re) {
            throw new DbMigratorException(re);
        }
    }

    if (log.isDebug2())
        log.debug2(DEBUG_HEADER + "Done.");
}

From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java

private PreparedStatement createProcessedPreparedStatement(int queryType, InternalParamCollection params,
        Connection conn) throws DataServiceFault {
    try {// w w w.  j  a va2 s .  co  m
        /*
         * lets see first if there's already a batch prepared statement
         * created
         */
        boolean inTheMiddleOfABatch = false;
        PreparedStatement stmt = this.getBatchPreparedStatement();
        int currentParamCount = this.getParamCount();

        /* create a new prepared statement */
        if (stmt == null) {
            /* batch mode is not supported for dynamic queries */
            Object[] result = this.processDynamicQuery(this.getQuery(), params);
            String dynamicSQL = (String) result[0];
            currentParamCount = (Integer) result[1];
            String processedSQL = this.createProcessedQuery(dynamicSQL, params, currentParamCount);
            if (queryType == SQLQuery.DS_QUERY_TYPE_NORMAL) {
                if (this.isReturnGeneratedKeys()) {
                    if (this.getKeyColumns() != null) {
                        stmt = conn.prepareStatement(processedSQL, this.getKeyColumns());
                    } else {
                        stmt = conn.prepareStatement(processedSQL, Statement.RETURN_GENERATED_KEYS);
                    }
                } else {
                    stmt = conn.prepareStatement(processedSQL);
                }
            } else if (queryType == SQLQuery.DS_QUERY_TYPE_STORED_PROC) {
                stmt = conn.prepareCall(processedSQL);
            } else {
                throw new DataServiceFault("Unsupported query type: " + queryType);
            }
        } else {
            inTheMiddleOfABatch = true;
        }

        if (!inTheMiddleOfABatch) {
            /* set query timeout */
            if (this.isHasQueryTimeout()) {
                stmt.setQueryTimeout(this.getQueryTimeout());
            }
            /* adding the try catch to avoid setting this for jdbc drivers that do not implement this method. */
            try {
                /* set fetch direction */
                if (this.isHasFetchDirection()) {
                    stmt.setFetchDirection(this.getFetchDirection());
                }
                /* set fetch size - user's setting */
                if (this.isHasFetchSize()) {
                    stmt.setFetchSize(this.getFetchSize());
                } else {
                    /*
                     * stream data by sections - avoid the full result set
                     * to be loaded to memory, and only stream if there
                     * aren't any OUT parameters, MySQL fails in the
                     * scenario of streaming and OUT parameters, so the
                     * possibility is there for other DBMSs
                     */
                    if (!this.hasOutParams() && this.getFetchSizeProperty().isChangeFetchSize()) {
                        stmt.setFetchSize(this.getFetchSizeProperty().getFetchSize());
                    }
                }
            } catch (Throwable e) {
                log.debug("Exception while setting fetch size: " + e.getMessage(), e);
            }
            /* set max field size */
            if (this.isHasMaxFieldSize()) {
                stmt.setMaxFieldSize(this.getMaxFieldSize());
            }
            /* set max rows */
            if (this.isHasMaxRows()) {
                stmt.setMaxRows(this.getMaxRows());
            }
        }

        int currentOrdinal = 0;
        InternalParam param;
        ParamValue value;
        for (int i = 1; i <= currentParamCount; i++) {
            param = params.getParam(i);
            value = param.getValue();
            /*
             * handle array values, if value is null, this param has to be
             * an OUT param
             */
            if (value != null && value.getValueType() == ParamValue.PARAM_VALUE_ARRAY) {
                for (ParamValue arrayElement : value.getArrayValue()) {
                    this.setParamInPreparedStatement(stmt, param,
                            arrayElement == null ? null : arrayElement.toString(), queryType, currentOrdinal);
                    currentOrdinal++;
                }
            } else { /* scalar value */
                this.setParamInPreparedStatement(stmt, param, value != null ? value.getScalarValue() : null,
                        queryType, currentOrdinal);
                currentOrdinal++;
            }
        }

        /* if we are in JDBC batch processing mode, batch it! */
        if (this.isJDBCBatchRequest()) {
            stmt.addBatch();
        }

        return stmt;
    } catch (SQLException e) {
        throw new DataServiceFault(e, "Error in 'createProcessedPreparedStatement'");
    }
}

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

@Override
public Enabler addEnabler(String name, String metadata, PluginInfo plugin, String pluginInfoConfig) {
    Enabler enabler = null;/* w w w.  j a va2 s. co  m*/
    if (name == null || plugin == null) {
        Log.e(TAG, "One cannot create a enabler where name is null or with no plugin");
        return null;
    }
    try {
        checkOpenness();
        connection.setAutoCommit(false);
        String sqlEnablerInsert = "INSERT INTO " + IotHubDataHandler.TABLE_ENABLER + "("
                + IotHubDataHandler.KEY_ENABLER_NAME + "," + IotHubDataHandler.KEY_ENABLER_METADATA + ","
                + IotHubDataHandler.KEY_ENABLER_PLUGIN_INFO + ","
                + IotHubDataHandler.KEY_ENABLER_PLUGIN_INFO_CONFIG + ") VALUES (?,?,?,?)";
        PreparedStatement psEnablerInsert = connection.prepareStatement(sqlEnablerInsert,
                Statement.RETURN_GENERATED_KEYS);
        psEnablerInsert.setString(1, name);
        psEnablerInsert.setString(2, metadata);
        psEnablerInsert.setLong(3, plugin.getId());
        psEnablerInsert.setString(4, pluginInfoConfig);
        psEnablerInsert.executeUpdate();
        ResultSet genKeysEnabler = psEnablerInsert.getGeneratedKeys();
        if (genKeysEnabler.next()) {
            long insertIdEnabler = genKeysEnabler.getLong(1);
            //At point we should have everything set so it is time to retrieve the plugin from the database
            //Log.d(TAG, "Now i will try to collect the enabler that was just added to the db");
            enabler = getEnabler(insertIdEnabler);
            if (enabler == null) {
                Log.e(TAG, "The enabler should not be null");
            }
            //TODO maybe check that the plugins are the same
        } else {
            Log.e(TAG, "The insert of enabler " + name + " did not work");
        }
        genKeysEnabler.close();
        psEnablerInsert.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        enabler = null;
    }
    try {
        if (enabler == null) {
            connection.rollback();
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return enabler;
}

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

/**
 * create topic/*from w ww . j a v a 2 s .  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;
}