Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.openhab.persistence.mysql.internal.MysqlPersistenceService.java

private String getTable(Item item) {
    PreparedStatement statement = null;
    String sqlCmd = null;/* w ww  .  ja v a2  s. com*/
    int rowId = 0;

    String itemName = item.getName();
    String tableName = sqlTables.get(itemName);

    // Table already exists - return the name
    if (tableName != null)
        return tableName;

    logger.debug("mySQL: no Table found for itemName={} get:{}", itemName, sqlTables.get(itemName));

    // Create a new entry in the Items table. This is the translation of
    // item name to table
    try {
        sqlCmd = new String("INSERT INTO Items (ItemName) VALUES (?)");

        statement = connection.prepareStatement(sqlCmd, PreparedStatement.RETURN_GENERATED_KEYS);
        statement.setString(1, itemName);
        statement.executeUpdate();

        ResultSet resultSet = statement.getGeneratedKeys();
        if (resultSet != null && resultSet.next()) {
            rowId = resultSet.getInt(1);
        }

        if (rowId == 0) {
            throw new SQLException("mySQL: Creating table for item '{}' failed.", itemName);
        }

        // Create the table name
        tableName = new String("Item" + rowId);
        logger.debug("mySQL: new item {} is Item{}", itemName, rowId);
    } catch (SQLException e) {
        errCnt++;
        logger.error("mySQL: Could not create entry for '{}' in table 'Items' with statement '{}': {}",
                itemName, sqlCmd, e.getMessage());
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException logOrIgnore) {
            }
        }
    }

    // An error occurred adding the item name into the index list!
    if (tableName == null) {
        logger.error("mySQL: tableName was null");
        return null;
    }

    String mysqlType = getItemType(item);

    // We have a rowId, create the table for the data
    sqlCmd = new String(
            "CREATE TABLE " + tableName + " (Time DATETIME, Value " + mysqlType + ", PRIMARY KEY(Time));");
    logger.debug("mySQL: query: {}", sqlCmd);

    try {
        statement = connection.prepareStatement(sqlCmd);
        statement.executeUpdate();

        logger.debug("mySQL: Table created for item '{}' with datatype {} in SQL database.", itemName,
                mysqlType);
        sqlTables.put(itemName, tableName);
    } catch (Exception e) {
        errCnt++;

        logger.error("mySQL: Could not create table for item '{}' with statement '{}': {}", itemName, sqlCmd,
                e.getMessage());
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception hidden) {
            }
        }
    }

    // Check if the new entry is in the table list
    // If it's not in the list, then there was an error and we need to do some tidying up
    // The item needs to be removed from the index table to avoid duplicates
    if (sqlTables.get(itemName) == null) {
        logger.error("mySQL: Item '{}' was not added to the table - removing index", itemName);
        sqlCmd = new String("DELETE FROM Items WHERE ItemName=?");
        logger.debug("mySQL: query: {}", sqlCmd);

        try {
            statement = connection.prepareStatement(sqlCmd);
            statement.setString(1, itemName);
            statement.executeUpdate();
        } catch (Exception e) {
            errCnt++;

            logger.error("mySQL: Could not remove index for item '{}' with statement '{}': ", itemName, sqlCmd,
                    e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception hidden) {
                }
            }
        }
    }

    return tableName;
}

From source file:cc.tooyoung.common.db.JdbcTemplate.java

@SuppressWarnings("unchecked")
public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
        throws DataAccessException {

    Assert.notNull(generatedKeyHolder, "KeyHolder must not be null");
    if (ApiLogger.isTraceEnabled()) {
        ApiLogger.trace("Executing SQL update and returning generated keys");
    }/*from w w  w. j  av  a 2  s.  c o  m*/

    Integer result = (Integer) execute(psc, new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
            int rows = ps.executeUpdate();
            List generatedKeys = generatedKeyHolder.getKeyList();
            generatedKeys.clear();
            ResultSet keys = ps.getGeneratedKeys();
            if (keys != null) {
                try {
                    RowMapper rowMapper = getColumnMapRowMapper();
                    RowMapperResultSetExtractor rse = new RowMapperResultSetExtractor(rowMapper, 1);
                    generatedKeys.addAll((List) rse.extractData(keys));
                } finally {
                    JdbcUtils.closeResultSet(keys);
                }
            }
            if (ApiLogger.isTraceEnabled()) {
                ApiLogger.trace(
                        "SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
            }
            return new Integer(rows);
        }
    }, true);
    return result.intValue();
}

From source file:org.wso2.carbon.device.mgt.core.dao.impl.DeviceDAOImpl.java

@Override
public int addEnrollment(Device device, int tenantId) throws DeviceManagementDAOException {
    Connection conn;//www  .j a  va  2  s .  co  m
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int enrolmentId = -1;
    try {
        conn = this.getConnection();
        String sql = "INSERT INTO DM_ENROLMENT(DEVICE_ID, OWNER, OWNERSHIP, STATUS,DATE_OF_ENROLMENT, "
                + "DATE_OF_LAST_UPDATE, TENANT_ID) VALUES(?, ?, ?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setInt(1, device.getId());
        stmt.setString(2, device.getEnrolmentInfo().getOwner());
        stmt.setString(3, device.getEnrolmentInfo().getOwnership().toString());
        stmt.setString(4, device.getEnrolmentInfo().getStatus().toString());
        stmt.setTimestamp(5, new Timestamp(new Date().getTime()));
        stmt.setTimestamp(6, new Timestamp(new Date().getTime()));
        stmt.setInt(7, tenantId);
        stmt.execute();

        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            enrolmentId = rs.getInt(1);
        }
        return enrolmentId;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException("Error occurred while adding enrolment", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:org.rimudb.Table.java

/**
 * Populate the Records with the generated keys.
 * /*from  w  w w .j  a  v  a2s .co  m*/
 * @param stmt PreparedStatement
 * @param records Record[]
 * @throws SQLException
 * @throws RimuDBException 
 */
protected void populateGeneratedKeys(PreparedStatement stmt, Record[] records)
        throws SQLException, RimuDBException {
    ResultSet rs = null;
    try {
        rs = stmt.getGeneratedKeys();
        recordBinder.bindResultSetToAutoIncrementKeys(rs, records);
    } finally {
        if (rs != null) {
            rs.close();
        }
    }
}

From source file:com.webpagebytes.wpbsample.database.WPBDatabase.java

public Transaction createTransaction(int source_user_id, int destination_user_id, long amount)
        throws SQLException {
    Connection connection = getConnection();
    PreparedStatement statement = null;
    PreparedStatement statementAccountS = null;
    PreparedStatement statementAccountD = null;

    Transaction transaction = new Transaction();
    try {/* w w  w.  j  a  v a  2s.c om*/
        statement = connection.prepareStatement(CREATE_ACCOUNTOPERATIONS_STATEMENT);
        connection.setAutoCommit(false);

        statement.setInt(1, source_user_id);
        statement.setInt(2, ACCOUNT_OPERATION_PAYMENT);

        statement.setLong(3, amount);
        Date now = new Date();
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(now.getTime());
        statement.setTimestamp(4, sqlDate);
        statement.setInt(5, source_user_id);
        statement.setInt(6, destination_user_id);

        statement.execute();
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()) {
            transaction.setId(rs.getLong(1));
            transaction.setAmount(amount);
            transaction.setDate(now);
            transaction.setSource_user_id(source_user_id);
            transaction.setDestination_user_id(destination_user_id);
        }

        statementAccountS = connection.prepareStatement(UPDATE_ACCOUNT_FOR_ADDITION_STATEMENT);
        statementAccountS.setLong(1, -amount);
        statementAccountS.setInt(2, source_user_id);
        statementAccountS.execute();

        statementAccountD = connection.prepareStatement(UPDATE_ACCOUNT_FOR_ADDITION_STATEMENT);
        statementAccountD.setLong(1, amount);
        statementAccountD.setInt(2, destination_user_id);
        statementAccountD.execute();

        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            connection.rollback();
        }
        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (statementAccountD != null) {
            statementAccountD.close();
        }
        if (statementAccountS != null) {
            statementAccountS.close();
        }
        if (connection != null) {
            connection.close();
        }

    }
    return transaction;
}

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

/**
 * log an event in the database.//w  w w .j a  va2s.  c o m
 * 
 * @param userId
 *            -
 * @param anonymousUserId
 *            -
 * @param eventType
 *            -
 * @param eventDetails
 *            -
 * @param ipAddress
 *            -
 * @throws JsonProcessingException
 *             - if we are unable to serialize the eventDetails as a string.
 * @throws SegueDatabaseException - if we cannot persist the event in the database.
 */
private void persistLogEvent(final String userId, final String anonymousUserId, final String eventType,
        final Object eventDetails, final String ipAddress)
        throws JsonProcessingException, SegueDatabaseException {
    // don't do anything if logging is not enabled.
    if (!this.loggingEnabled) {
        return;
    }

    LogEvent logEvent = this.buildLogEvent(userId, anonymousUserId, eventType, eventDetails, ipAddress);

    PreparedStatement pst;
    try (Connection conn = database.getDatabaseConnection()) {
        pst = conn.prepareStatement(
                "INSERT INTO logged_events"
                        + "(user_id, anonymous_user, event_type, event_details_type, event_details, "
                        + "ip_address, timestamp) " + "VALUES (?, ?, ?, ?, ?::text::jsonb, ?::inet, ?);",
                Statement.RETURN_GENERATED_KEYS);

        pst.setString(1, logEvent.getUserId());
        pst.setBoolean(2, logEvent.isAnonymousUser());
        pst.setString(3, logEvent.getEventType());
        pst.setString(4, logEvent.getEventDetailsType());
        pst.setString(5, objectMapper.writeValueAsString(logEvent.getEventDetails()));
        pst.setString(6, logEvent.getIpAddress());
        pst.setTimestamp(7, new java.sql.Timestamp(new Date().getTime()));

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

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

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

From source file:org.skfiy.typhon.spi.pvp.PvpProvider.java

private int saveWarReport(WarReport warReport) {
    Connection conn = null;/* w  w w. j av a  2 s .  co  m*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    int id = 0;

    try {
        conn = connectionProvider.getConnection();
        ps = conn.prepareStatement("insert into t_pvp_report(data,creationTime) values(?,?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, JSON.toJSONString(warReport));
        ps.setLong(2, System.currentTimeMillis());

        if (ps.executeUpdate() > 0) {
            rs = ps.getGeneratedKeys();
            rs.next();
            id = rs.getInt(1);
        }
        DbUtils.commitQuietly(conn);

    } catch (SQLException e) {
        DbUtils.rollbackQuietly(conn);
        throw new DbException(e);
    } finally {
        DbUtils.closeQuietly(conn, ps, rs);
    }
    return id;
}

From source file:com.ywang.alone.handler.task.AuthTask.java

/**
 * //from   w  w w  . j ava  2  s  .  c  om
 * 
 * @param msg
 *            {
 *            'phoneNum':'ywang','password':'e10adc3949ba59abbe56e057f20f883
 *            e ' , 'deviceToken':'8a2597aa1d37d432a88a446d82b6561e',
 *            'lng':'117.157954','lat':'31.873432','osVersion':'8.0',
 *            'systemType':'iOS','phoneModel':'iPhone 5s','key':''}
 * 
 * @return
 */
private static JSONObject regNewUser(String msg) {
    JSONObject jsonObject = AloneUtil.newRetJsonObject();
    JSONObject user = JSON.parseObject(msg);
    DruidPooledConnection conn = null;
    PreparedStatement updatestmt = null;
    try {
        conn = DataSourceFactory.getInstance().getConn();
        conn.setAutoCommit(false);
        String uuid = UUID.randomUUID().toString();
        uuid = uuid.replaceAll("-", "");
        String token = MD5.getMD5String(uuid);

        String im_user = user.getString("phoneNum").trim();
        UserInfo userInfo = new UserInfo();
        userInfo.setRegTime(System.currentTimeMillis());
        userInfo.setOnline("1");
        userInfo.setKey(token);
        userInfo.setMessageUser(im_user);
        userInfo.setMessagePwd("alone123456");

        updatestmt = conn.prepareStatement(
                "insert into userbase (PHONE_NUM, PWD, REG_TIME, LNG, LAT, DEVICE_TOKEN, SYSTEM_TYPE, OS_VERSION,PHONE_MODEL, PKEY, MESSAGE_USER, MESSAGE_PWD) VALUES (?,?,?, ?,?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        updatestmt.setString(1, user.getString("phoneNum").trim());
        updatestmt.setString(2, user.getString("password").trim());
        updatestmt.setLong(3, userInfo.getRegTime());
        updatestmt.setString(4, user.getString("lng").trim());
        updatestmt.setString(5, user.getString("lat").trim());

        updatestmt.setString(6, user.getString("deviceToken").trim());
        updatestmt.setString(7, user.getString("systemType").trim());
        updatestmt.setString(8, user.getString("osVersion").trim());
        updatestmt.setString(9, user.getString("phoneModel").trim());
        updatestmt.setString(10, userInfo.getKey());
        updatestmt.setString(11, userInfo.getMessageUser());
        updatestmt.setString(12, "alone123456");

        int result = updatestmt.executeUpdate();

        if (result == 1) {
            ResultSet idRS = updatestmt.getGeneratedKeys();
            if (idRS.next()) {
                int userId = idRS.getInt(1);
                userInfo.setUserId(userId + "");
            }
            jsonObject.put("ret", Constant.RET.REG_SUCC);
            jsonObject.put("data", userInfo);
        } else {
            jsonObject.put("ret", Constant.RET.SYS_ERR);
            jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
            jsonObject.put("errDesc", Constant.ErrorCode.SYS_ERR);
            LoggerUtil.logServerErr("insert into userbase no result");
        }

        conn.commit();
        conn.setAutoCommit(true);
    } catch (SQLException e) {
        LoggerUtil.logServerErr(e);
        jsonObject.put("ret", Constant.RET.SYS_ERR);
        jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
        jsonObject.put("errDesc", Constant.ErrorCode.SYS_ERR);
    } finally {
        try {
            if (null != updatestmt) {
                updatestmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (SQLException e) {
            LoggerUtil.logServerErr(e.getMessage());
        }
    }

    return jsonObject;
}

From source file:net.mindengine.oculus.frontend.service.runs.JdbcTestRunDAO.java

@Override
public Long createTestRun(TestRun testRun) throws Exception {
    PreparedStatement ps = getConnection().prepareStatement("insert into test_runs " + "(suite_run_id, "
            + "test_id, " + "start_time, " + "end_time, " + "reasons, " + "report, " + "name, " + "status, "
            + "project_id, description) " + "values (?,?,?,?,?,?,?,?,?,?)");

    if (testRun.getReasons() == null) {
        testRun.setReasons("");
    }/*from ww  w.j av  a  2s  . co m*/
    if (testRun.getName() == null) {
        testRun.setName("");
    }

    if (testRun.getSuiteRunId() == null) {
        testRun.setSuiteRunId(0L);
    }

    ps.setLong(1, testRun.getSuiteRunId());
    ps.setLong(2, testRun.getTestId());
    ps.setTimestamp(3, new Timestamp(testRun.getStartTime().getTime()));
    ps.setTimestamp(4, new Timestamp(testRun.getEndTime().getTime()));
    ps.setString(5, testRun.getReasons());
    ps.setString(6, testRun.getReport());
    ps.setString(7, testRun.getName());
    ps.setString(8, testRun.getStatus());
    ps.setLong(9, testRun.getProjectId());
    ps.setString(10, testRun.getDescription());
    logger.info(ps);
    ps.execute();

    ResultSet rs = ps.getGeneratedKeys();
    if (rs.next()) {
        return rs.getLong(1);
    }

    return null;
}

From source file:opengovcrawler.DB.java

/**
 * Insert comments into DB Also, it inserts username and initialId into
 * comment_opengov table//w  w  w .  j ava2s .com
 *
 * @param articleDbId - The id of the article that the comments refer to
 * @param comments - The arrayList of comment
 * @throws java.sql.SQLException
 */
public static void InsertComments(ArrayList<Comment> comments, int articleDbId) throws SQLException {
    String insertCommentSql = "INSERT INTO comments (url_source, article_id, comment, date_added, revision, depth, source_type_id)"
            + "VALUES (?,?,?,?,?,?,?)";
    PreparedStatement prepInsertComStatement = connection.prepareStatement(insertCommentSql,
            Statement.RETURN_GENERATED_KEYS);
    Statement stmnt = null;
    for (Comment currentComment : comments) {
        String selectCommentSql = "SELECT * FROM comments WHERE url_source = ? AND article_id = ?";
        PreparedStatement prepSelectComStatement = connection.prepareStatement(selectCommentSql);
        prepSelectComStatement.setString(1, currentComment.permalink);
        prepSelectComStatement.setInt(2, articleDbId);
        ResultSet rs = prepSelectComStatement.executeQuery();
        int insertedCommentKeyId = -1;
        if (rs.next()) {
            String comText = rs.getString("comment");
            if (currentComment.contentHash != comText.hashCode()) {
                // Then comment has been changed so
                // we insert it as in the DB as well, as revision-2
                Timestamp comTimestamp = null;
                try {
                    comTimestamp = ConvertDateMonth(currentComment.timestamp);
                } catch (ParseException ex) {
                    ex.printStackTrace();
                }
                int curCommentRevision = rs.getInt("revision");
                curCommentRevision++;
                prepInsertComStatement.setString(1, currentComment.permalink);
                prepInsertComStatement.setInt(2, articleDbId);
                prepInsertComStatement.setString(3, currentComment.content);
                prepInsertComStatement.setTimestamp(4, comTimestamp);
                prepInsertComStatement.setInt(5, curCommentRevision);
                prepInsertComStatement.setInt(6, currentComment.depth);
                prepInsertComStatement.setInt(7, 2);
                prepInsertComStatement.executeUpdate();
                ResultSet rsq = prepInsertComStatement.getGeneratedKeys();
                if (rsq.next()) {
                    insertedCommentKeyId = rsq.getInt(1);
                }
                //                    prepInsertComStatement.addBatch();
                ConsultationThreadedCrawling.newComments++;
                String insertIntoCommentOpengov = "INSERT INTO comment_opengov"
                        + "(opengovid, fullname, id, link_url) " + "VALUES" + "(" + currentComment.initialId
                        + ",'" + currentComment.author + "'," + insertedCommentKeyId + ", '"
                        + currentComment.link_url + "')";
                stmnt = connection.createStatement();
                stmnt.executeUpdate(insertIntoCommentOpengov);
                stmnt.close();
            }
        } else {
            Timestamp comTimestamp = null;
            try {
                comTimestamp = ConvertDateMonth(currentComment.timestamp);
            } catch (ParseException ex) {
                ex.printStackTrace();
            }
            prepInsertComStatement.setString(1, currentComment.permalink);
            prepInsertComStatement.setInt(2, articleDbId);
            prepInsertComStatement.setString(3, currentComment.content);
            prepInsertComStatement.setTimestamp(4, comTimestamp);
            prepInsertComStatement.setInt(5, 1);
            prepInsertComStatement.setInt(6, currentComment.depth);
            prepInsertComStatement.setInt(7, 2);
            prepInsertComStatement.executeUpdate();
            ResultSet rsq = prepInsertComStatement.getGeneratedKeys();
            if (rsq.next()) {
                insertedCommentKeyId = rsq.getInt(1);
            }
            //                prepInsertComStatement.addBatch();
            ConsultationThreadedCrawling.newComments++;
            // Keep track of the opengov users
            String insertIntoCommentOpengov = "INSERT INTO comment_opengov"
                    + "(opengovid, fullname, id, link_url) " + "VALUES" + "(" + currentComment.initialId + ",'"
                    + currentComment.author + "'," + insertedCommentKeyId + ", '" + currentComment.link_url
                    + "')";
            stmnt = connection.createStatement();
            stmnt.executeUpdate(insertIntoCommentOpengov);
            stmnt.close();
        }
        prepSelectComStatement.close();
    }
    //        prepInsertComStatement.executeBatch();
    prepInsertComStatement.close();
}