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.biblionum.ouvrage.modele.OuvrageTypeModele.java

/**
 * Java method that inserts a row in the generated sql table and returns the
 * new generated id/*w  ww  .  j av a2s  . co m*/
 *
 * @param con (open java.sql.Connection)
 * @param designation_typeou
 * @return id (database row id [id])
 * @throws SQLException
 */
public int insertIntoOuvragetype(DataSource ds, String designation_typeou) throws SQLException {
    con = ds.getConnection();
    int generatedId = -1;
    String sql = "INSERT INTO ouvragetype (designation_typeou)" + "VALUES (?)";
    PreparedStatement statement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    statement.setString(1, designation_typeou);
    statement.execute();
    ResultSet auto = statement.getGeneratedKeys();

    if (auto.next()) {
        generatedId = auto.getInt(1);
    } else {
        generatedId = -1;
    }

    statement.close();
    con.close();
    return generatedId;
}

From source file:com.orangeandbronze.jblubble.jdbc.springframework.SpringJdbcBlobstoreService.java

protected long getGeneratedKey(PreparedStatement ps) throws SQLException {
    long generatedId;
    try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
        if (generatedKeys.next()) {
            generatedId = generatedKeys.getLong(1);
        } else {//from ww w .ja v  a 2  s. c  o m
            throw new BlobstoreException("No unique key generated");
        }
    }
    return generatedId;
}

From source file:org.apache.hadoop.raid.DBUtils.java

public static List<List<Object>> runInsertSelect(DBConnectionFactory connectionFactory, String sql,
        List<Object> sqlParams, boolean isWrite, int numRetries, int retryMaxInternalSec, boolean insert,
        boolean getGeneratedKeys) throws IOException {
    int waitMS = 3000; // wait for at least 3s before next retry.
    for (int i = 0; i < numRetries; ++i) {
        Connection conn = null;/*from  w ww .j  a  va2  s  .  c  om*/
        ResultSet generatedKeys = null;
        PreparedStatement pstmt = null;
        String url = null;
        try {
            try {
                url = connectionFactory.getUrl(isWrite);
            } catch (IOException ioe) {
                LOG.warn("Cannot get DB URL, fall back to the default one", ioe);
                url = defaultUrls.get(isWrite);
                if (url == null) {
                    throw ioe;
                }
            }
            LOG.info("Attepting connection with URL " + url);
            conn = connectionFactory.getConnection(url);
            defaultUrls.put(isWrite, url);
            pstmt = getPreparedStatement(conn, sql, sqlParams, getGeneratedKeys);
            if (insert) {
                int recordsUpdated = pstmt.executeUpdate();
                LOG.info("rows inserted: " + recordsUpdated + " sql: " + sql);
                List<List<Object>> results = null;
                if (getGeneratedKeys) {
                    generatedKeys = pstmt.getGeneratedKeys();
                    results = getResults(generatedKeys);
                }
                Thread.sleep(connectionFactory.getDBOpsSleepTime() + rand.nextInt(1000));
                return results;
            } else {
                generatedKeys = pstmt.executeQuery();
                List<List<Object>> results = getResults(generatedKeys);
                pstmt.clearBatch();
                LOG.info("rows selected: " + results.size() + " sql: " + sql);
                Thread.sleep(connectionFactory.getDBOpsSleepTime() + rand.nextInt(1000));
                return results;
            }
        } catch (Exception e) {
            // We should catch a better exception than Exception, but since
            // DBConnectionUrlFactory.getUrl() defines throws Exception, it's hard
            // for us to figure out the complete set it can throw. We follow
            // DBConnectionUrlFactory.getUrl()'s definition to catch Exception.
            // It shouldn't be a big problem as after numRetries, we anyway exit.
            LOG.info("Exception " + e + ". Will retry " + (numRetries - i) + " times.");
            // Introducing a random factor to the wait time before another retry.
            // The wait time is dependent on # of failures and a random factor.
            // At the first time of getting a SQLException, the wait time
            // is a random number between [0,300] msec. If the first retry
            // still fails, we will wait 300 msec grace period before the 2nd retry.
            // Also at the second retry, the waiting window is expanded to 600 msec
            // alleviating the request rate from the server. Similarly the 3rd retry
            // will wait 600 msec grace period before retry and the waiting window
            // is
            // expanded to 1200 msec.

            waitMS += waitMS;
            if (waitMS > retryMaxInternalSec * 1000) {
                waitMS = retryMaxInternalSec * 1000;
            }
            double waitTime = waitMS + waitMS * rand.nextDouble();
            if (i + 1 == numRetries) {
                LOG.error("Still got Exception after " + numRetries + "  retries.", e);
                throw new IOException(e);
            }
            try {
                Thread.sleep((long) waitTime);
            } catch (InterruptedException ie) {
                throw new IOException(ie);
            }
        } finally {
            DBUtils.close(generatedKeys, new PreparedStatement[] { pstmt }, conn);
        }
    }
    return null;
}

From source file:com.keybox.manage.db.UserDB.java

/**
 * inserts new user/*from  w  w w  .jav a2  s.co  m*/
 * 
 * @param con DB connection 
 * @param user user object
 */
public static Long insertUser(Connection con, User user) {

    Long userId = null;

    try {
        PreparedStatement stmt = con.prepareStatement(
                "insert into users (first_nm, last_nm, email, username, auth_type, user_type, password, salt) values (?,?,?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, user.getFirstNm());
        stmt.setString(2, user.getLastNm());
        stmt.setString(3, user.getEmail());
        stmt.setString(4, user.getUsername());
        stmt.setString(5, user.getAuthType());
        stmt.setString(6, user.getUserType());
        if (StringUtils.isNotEmpty(user.getPassword())) {
            String salt = EncryptionUtil.generateSalt();
            stmt.setString(7, EncryptionUtil.hash(user.getPassword() + salt));
            stmt.setString(8, salt);
        } else {
            stmt.setString(7, null);
            stmt.setString(8, null);
        }
        stmt.execute();
        ResultSet rs = stmt.getGeneratedKeys();
        if (rs != null && rs.next()) {
            userId = rs.getLong(1);
        }
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

    return userId;

}

From source file:com.anyuan.thomweboss.persistence.jdbcimpl.user.UserDaoJdbcImpl.java

/**
 * TODO/*from ww  w. java 2  s  .c  om*/
 * @author Thomsen
 * @since Jul 14, 2013 10:34:57 PM
 * @param id
 * @param preState
 * @return
 * @throws SQLException
 */
private long generateId(PreparedStatement preState) throws SQLException {
    long id = -1;
    ResultSet rs = preState.getGeneratedKeys();
    if (null != rs && rs.next()) {
        id = rs.getLong(1);
    }
    return id;
}

From source file:genepi.db.JdbcDataAccessObject.java

public int insert(String sql, Object[] params) throws SQLException {

    Connection connection = database.getDataSource().getConnection();

    try {/* w w w.ja va  2  s .  co m*/
        PreparedStatement statement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

        runner.fillStatement(statement, params);

        statement.executeUpdate();

        ResultSet rs = statement.getGeneratedKeys();
        rs.beforeFirst();
        rs.next();
        int id = rs.getInt(1);
        connection.close();
        return id;
    } catch (Exception e) {
        throw e;
    } finally {
        connection.close();
    }
}

From source file:org.mskcc.cbio.cgds.dao.DaoGistic.java

public static void addGistic(Gistic gistic) throws DaoException, validationException {
    if (gistic == null) {
        throw new DaoException("Given a null gistic object");
    }/*from w w  w  .  j a  va  2 s  . c  o m*/

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    ValidateGistic.validateBean(gistic);

    try {
        con = JdbcUtil.getDbConnection(DaoGistic.class);
        // insert into SQL gistic table
        pstmt = con.prepareStatement("INSERT INTO gistic (`CANCER_STUDY_ID`," + "`CHROMOSOME`, "
                + "`CYTOBAND`, " + "`WIDE_PEAK_START`, " + "`WIDE_PEAK_END`, " + "`Q_VALUE`, " + "`AMP`) "
                + "VALUES (?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

        pstmt.setInt(1, gistic.getCancerStudyId());
        pstmt.setInt(2, gistic.getChromosome());
        pstmt.setString(3, gistic.getCytoband());
        pstmt.setInt(4, gistic.getPeakStart());
        pstmt.setInt(5, gistic.getPeakEnd());
        pstmt.setDouble(6, gistic.getqValue());
        pstmt.setBoolean(7, gistic.getAmp());
        pstmt.executeUpdate();

        // insert into SQL gistic_to_gene table
        rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            int autoId = rs.getInt(1);
            gistic.setInternalId(autoId);
        }
        addGisticGenes(gistic, con);

    } catch (SQLException e) {
        throw new DaoException(e);
    } finally {
        JdbcUtil.closeAll(DaoGistic.class, con, pstmt, rs);
    }
}

From source file:com.redhat.victims.database.VictimsSQL.java

/**
 * Insert a new record with the given hash and return the record id.
 *
 * @param hash//from   w ww .ja va2 s . co  m
 * @return A record id if it was created correctly, else return -1.
 * @throws SQLException
 */
protected int insertRecord(Connection connection, String hash) throws SQLException {
    int id = -1;
    PreparedStatement ps = setObjects(connection, Query.INSERT_RECORD, hash);
    ps.execute();
    ResultSet rs = ps.getGeneratedKeys();
    try {
        while (rs.next()) {
            id = rs.getInt(1);
            break;
        }
    } finally {
        rs.close();
        ps.close();
    }

    return id;
}

From source file:at.becast.youploader.database.SQLite.java

public static int addUpload(File file, Video data, VideoMetadata metadata, Date startAt)
        throws SQLException, IOException {
    PreparedStatement prest = null;
    ObjectMapper mapper = new ObjectMapper();
    String sql = "INSERT INTO `uploads` (`account`, `file`, `lenght`, `data`,`enddir`, `metadata`, `status`,`starttime`) "
            + "VALUES (?,?,?,?,?,?,?,?)";
    prest = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    prest.setInt(1, metadata.getAccount());
    prest.setString(2, file.getAbsolutePath());
    prest.setLong(3, file.length());//ww w  .  j  av  a  2 s .  com
    prest.setString(4, mapper.writeValueAsString(data));
    prest.setString(5, metadata.getEndDirectory());
    prest.setString(6, mapper.writeValueAsString(metadata));
    prest.setString(7, UploadManager.Status.NOT_STARTED.toString());
    if (startAt == null) {
        prest.setString(8, "");
    } else {
        prest.setDate(8, new java.sql.Date(startAt.getTime()));
    }
    prest.execute();
    ResultSet rs = prest.getGeneratedKeys();
    prest.close();
    if (rs.next()) {
        int id = rs.getInt(1);
        rs.close();
        return id;
    } else {
        return -1;
    }
}

From source file:pl.edu.agh.iosr.lsf.dao.DatabaseHelper.java

public int insert(PreparedStatement ps) throws SQLException {
    ps.execute();// w  w w . ja v a2  s  .com
    try (ResultSet rs = ps.getGeneratedKeys()) {
        while (rs.next()) {
            return rs.getInt(1);
        }
    }
    return -1;
}