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:com.softberries.klerk.dao.DocumentDao.java

@Override
public void create(Document d) throws SQLException {
    try {/*from w  ww. j  av a2s .  c  o m*/
        init();
        st = conn.prepareStatement(SQL_INSERT_DOCUMENT, Statement.RETURN_GENERATED_KEYS);
        st.setString(1, d.getTitle());
        st.setString(2, d.getNotes());
        st.setDate(3, new java.sql.Date(d.getCreatedDate().getTime()));
        st.setDate(4, new java.sql.Date(d.getTransactionDate().getTime()));
        st.setDate(5, new java.sql.Date(d.getDueDate().getTime()));
        st.setString(6, d.getPlaceCreated());
        st.setInt(7, d.getDocumentType());
        st.setLong(8, d.getCreator().getId());
        st.setLong(9, d.getBuyer().getId());
        st.setLong(10, d.getSeller().getId());
        // run the query
        int i = st.executeUpdate();
        System.out.println("i: " + i);
        if (i == -1) {
            System.out.println("db error : " + SQL_INSERT_DOCUMENT);
        }
        generatedKeys = st.getGeneratedKeys();
        if (generatedKeys.next()) {
            d.setId(generatedKeys.getLong(1));
        } else {
            throw new SQLException("Creating document failed, no generated key obtained.");
        }
        //if the document creation was successful, add document items
        DocumentItemDao idao = new DocumentItemDao(this.filePath);
        for (DocumentItem di : d.getItems()) {
            di.setDocument_id(d.getId());
            idao.create(di, run, conn, generatedKeys);
        }
        conn.commit();
    } catch (Exception e) {
        //rollback the transaction but rethrow the exception to the caller
        conn.rollback();
        e.printStackTrace();
        throw new SQLException(e);
    } finally {
        close(conn, st, generatedKeys);
    }
}

From source file:dk.netarkivet.harvester.datamodel.GlobalCrawlerTrapListDBDAO.java

@Override
public int create(GlobalCrawlerTrapList trapList) {
    ArgumentNotValid.checkNotNull(trapList, "trapList");
    // Check for existence of a trapList in the database with the same name
    // and throw argumentNotValid if not
    if (exists(trapList.getName())) {
        throw new ArgumentNotValid(
                "Crawlertrap with name '" + trapList.getName() + "'already exists in database");
    }/*from   w ww. j  a  va  2  s. c om*/
    int trapId;
    Connection conn = HarvestDBConnection.get();
    PreparedStatement stmt = null;
    try {
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(INSERT_TRAPLIST_STMT, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, trapList.getName());
        stmt.setString(2, trapList.getDescription());
        stmt.setBoolean(3, trapList.isActive());
        stmt.executeUpdate();
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        trapId = rs.getInt(1);
        trapList.setId(trapId);
        for (String expr : trapList.getTraps()) {
            stmt = conn.prepareStatement(INSERT_TRAP_EXPR_STMT);
            stmt.setInt(1, trapId);
            stmt.setString(2, expr);
            stmt.executeUpdate();
        }
        conn.commit();
    } catch (SQLException e) {
        String message = "SQL error creating global crawler trap list \n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        DBUtils.closeStatementIfOpen(stmt);
        DBUtils.rollbackIfNeeded(conn, "create trap list", trapList);
        HarvestDBConnection.release(conn);
    }
    return trapId;
}

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

/**
 * inserts new user// w  w  w  .  java2s . c o  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 (email, username, auth_type, user_type, password, salt) values (?,?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, user.getEmail());
        stmt.setString(2, user.getUsername());
        stmt.setString(3, user.getAuthType());
        stmt.setString(4, user.getUserType());
        if (StringUtils.isNotEmpty(user.getPassword())) {
            String salt = EncryptionUtil.generateSalt();
            stmt.setString(5, EncryptionUtil.hash(user.getPassword() + salt));
            stmt.setString(6, salt);
        } else {
            stmt.setString(5, null);
            stmt.setString(6, 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:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * @param oldDBConn//from  w  ww  . ja  v  a  2s. co m
 * @param newDBConn
 * @param disciplineID
 * @return
 */
public static boolean convertKUFishCruiseDataOld(final Connection oldDBConn, final Connection newDBConn,
        final int disciplineID) {
    PreparedStatement pStmt1 = null;
    PreparedStatement pStmt2 = null;
    try {
        Timestamp now = new Timestamp(System.currentTimeMillis());
        pStmt1 = newDBConn.prepareStatement(
                "INSERT INTO collectingtrip (CollectingTripName, DisciplineID, TimestampCreated, Version) VALUES(?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pStmt2 = newDBConn.prepareStatement(
                "INSERT INTO collectingevent (CollectingTripID, DisciplineID, stationFieldNumber, Method, StartTime, TimestampCreated, TimestampModified, Version) VALUES(?,?,?,?,?,?,?,?)");

        String sql = "SELECT Text1, Text2, Number1, TimestampCreated, TimestampModified FROM stratigraphy";
        Vector<Object[]> rows = BasicSQLUtils.query(oldDBConn, sql);
        for (Object[] row : rows) {
            pStmt1.setString(1, "Cruise");
            pStmt1.setInt(2, disciplineID);
            pStmt1.setTimestamp(3, now);
            pStmt1.setInt(4, 0);
            pStmt1.execute();

            Integer intsertId = BasicSQLUtils.getInsertedId(pStmt1);
            String vessel = (String) row[0];
            String cruiseName = (String) row[1];
            Integer number = row[2] != null ? ((Double) row[2]).intValue() : null;

            pStmt2.setInt(1, intsertId);
            pStmt2.setInt(2, disciplineID);
            pStmt2.setString(3, vessel);
            pStmt2.setString(4, cruiseName);
            if (number != null) {
                pStmt2.setInt(5, number);
            }
            pStmt2.setTimestamp(6, (Timestamp) row[3]);
            pStmt2.setTimestamp(7, (Timestamp) row[4]);
            pStmt2.setInt(8, 0);

            pStmt2.execute();
        }
        return true;

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (pStmt1 != null)
                pStmt1.close();
            if (pStmt2 != null)
                pStmt2.close();

        } catch (Exception ex) {
        }
    }

    return false;
}

From source file:fi.okm.mpass.shibboleth.profile.metadata.DataSourceMetadataResolverTest.java

protected void insertService(final DataSourceMetadataResolver resolver, final String entityId,
        final String acsUrl) throws Exception {
    final String insertResult = "INSERT INTO mpass_services"
            + " (samlEntityId, samlAcsUrl, startTime) VALUES (?,?,?)";
    try (final Connection conn = resolver.getDataSource().getConnection()) {
        final PreparedStatement statement = conn.prepareStatement(insertResult,
                Statement.RETURN_GENERATED_KEYS);
        statement.setString(1, entityId);
        statement.setString(2, acsUrl);/*  www. j  av a2s  . c  o  m*/
        statement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
        statement.executeUpdate();
    } catch (Exception e) {
        throw e;
    }
    resolver.refresh();
}

From source file:com.enigmastation.ml.perceptron.impl.HSQLDBPerceptronRepository.java

private int createNode(Object token, Layer layer) {
    int id;/*from ww  w .  jav  a  2s  . co  m*/
    PreparedStatement ps;
    ResultSet rs;
    try (Connection conn = getConnection()) {
        ps = conn.prepareStatement("insert into node (create_key, layer) values (?, ?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, token.toString());
        ps.setInt(2, layer.ordinal());
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        rs.next();
        id = rs.getInt(1);
        rs.close();
        ps.close();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return id;
}

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

/**
 * inserts new user/*from   w w  w.  j a  v  a  2 s . c  om*/
 * 
 * @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:DAO.BestellingDAOJson.java

@Override
public Bestelling createBestelling(Klant klant) throws SQLException {

    Bestelling bestelling = new Bestelling();
    bestelling.setKlant_id(klant.getKlantID());

    String query = "insert into bestelling (klant_klant_id) values (" + klant.getKlantID() + ")";
    try (Connection connection = ConnectionFactory.getMySQLConnection();
            PreparedStatement stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);) {
        stmt.executeUpdate();/*w ww . j a va2s.  com*/
        try (ResultSet resultSet = stmt.getGeneratedKeys();) {
            if (resultSet.isBeforeFirst()) {
                resultSet.next();
                bestelling.setBestelling_id(resultSet.getInt(1));
            }
        }
    }
    logger.info("bestelling gecreeerd");
    return bestelling;
}

From source file:org.biokoframework.system.repository.sql.util.SqlStatementsHelper.java

public static PreparedStatement preparedDeleteByIdStatement(Class<? extends DomainEntity> entityClass,
        String tableName, Connection connection) throws SQLException {
    StringBuilder sql = new StringBuilder().append("delete from ").append(tableName).append(" where ")
            .append(DomainEntity.ID).append("=?;");

    return connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
}

From source file:edu.ku.brc.specify.dbsupport.BuildFromGeonames.java

/**
 * Builds the Geography tree from the geonames table.
 * @param earthId the id of the root.//from  www  . j a v a2  s  .  com
 * @return true on success
 */
public boolean build(final int earthId) {
    Statement stmt = null;
    try {
        DBConnection currDBConn = DBConnection.getInstance();
        if (updateConn == null) {
            updateConn = currDBConn.createConnection();
        }
        pStmt = updateConn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);

        readConn = currDBConn.createConnection();

        stmt = readConn.createStatement();

        Integer count = BasicSQLUtils.getCount(readConn, CNT_SQL);
        doProgress(0, count, "Initializing Reference Geography...");

        Hashtable<String, String> continentCodeFromName = new Hashtable<String, String>();
        ResultSet rs = stmt.executeQuery("SELECT code, name from continentcodes");
        while (rs.next()) {
            continentNameFromCode.put(rs.getString(1), rs.getString(2));
            continentCodeFromName.put(rs.getString(2), rs.getString(1));
        }
        rs.close();

        /*if (false) // For testing
        {
        int delCnt = BasicSQLUtils.update(currConn, "DELETE FROM geography WHERE GeographyID > 1 AND RankId = 400");
        log.debug("Deleted "+delCnt+" geography records.");
        delCnt = BasicSQLUtils.update(currConn, "DELETE FROM geography WHERE GeographyID > 1 AND RankId = 300");
        log.debug("Deleted "+delCnt+" geography records.");
        delCnt = BasicSQLUtils.update(currConn, "DELETE FROM geography WHERE GeographyID > 1 AND RankId = 200");
        log.debug("Deleted "+delCnt+" geography records.");
        delCnt = BasicSQLUtils.update(currConn, "DELETE FROM geography WHERE GeographyID > 1 AND RankId = 100");
        log.debug("Deleted "+delCnt+" geography records.");
        }*/

        doProgress("Initializing Reference Continents..."); // I18N

        String earthSQL = "UPDATE geography SET GeographyCode='..' WHERE GeographyID = " + earthId;
        BasicSQLUtils.update(earthSQL);

        int cnt = 0;

        //////////////////////
        // Continent
        //////////////////////
        String sqlStr = "SELECT continentcodes.name, geoname.latitude, geoname.longitude, continentcodes.code FROM geoname Inner Join continentcodes ON geoname.name = continentcodes.name";
        rs = stmt.executeQuery(sqlStr);
        while (rs.next()) {
            doProgress(cnt);

            if (buildInsert(rs, 100, earthId)) {
                pStmt.executeUpdate();
                Integer newId = BasicSQLUtils.getInsertedId(pStmt);
                contToIdHash.put(rs.getString(4), newId);
            }

            cnt++;
            if (cnt % 100 == 0) {
                doProgress(cnt);
            }
        }
        rs.close();

        //////////////////////
        // Countries
        //////////////////////

        // Make hash of Country Codes
        HashSet<String> countryCodes = new HashSet<String>();
        rs = stmt.executeQuery("SELECT DISTINCT country FROM geoname WHERE fcode = 'PCLI'");
        while (rs.next()) {
            countryCodes.add(rs.getString(1));
        }
        rs.close();

        doProgress("Initializing Reference Countries...");

        // First map all Countries to Continents
        rs = stmt.executeQuery(
                "SELECT name, iso_alpha2 AS CountryCode, continent FROM countryinfo ORDER BY continent, iso_alpha2");
        while (rs.next()) {
            String countryCode = rs.getString(2);
            String continentCode = rs.getString(3);
            countryStateCodeToIdHash.put(countryCode, new Hashtable<String, Integer>());
            countryToContHash.put(countryCode, continentCode);
        }
        rs.close();

        // Now create all the countries in the geoname table
        sqlStr = "SELECT asciiname, latitude, longitude, country FROM geoname WHERE fcode LIKE 'PCL%' ORDER BY name";
        rs = stmt.executeQuery(sqlStr);
        while (rs.next()) {
            if (buildInsert(rs, 200, earthId)) {
                pStmt.executeUpdate();
                Integer newId = BasicSQLUtils.getInsertedId(pStmt);
                countryCodeToIdHash.put(rs.getString(4), newId);
            }

            cnt++;
            if (frame != null && cnt % 100 == 0) {
                doProgress(cnt);
            }

        }
        rs.close();

        // Create an Countries that referenced in the geoname table
        rs = stmt.executeQuery(
                "SELECT name, iso_alpha2 AS CountryCode, continent, iso_alpha2 FROM countryinfo ORDER BY continent, iso_alpha2");
        while (rs.next()) {
            String countryCode = rs.getString(2);
            String continentCode = rs.getString(3);

            if (BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM geography WHERE RankID = 200 AND Abbrev = '"
                    + countryCode + "'") == 0) {
                String countryName = rs.getString(1);

                log.error("Adding country[" + countryName + "][" + countryCode + "][" + continentCode + "]");

                createCountry(countryName, countryCode, continentCode, 200);

                pStmt.executeUpdate();
                Integer newId = BasicSQLUtils.getInsertedId(pStmt);
                countryCodeToIdHash.put(countryCode, newId);
            }
        }
        rs.close();

        doProgress("Initializing Reference States...");

        //////////////////////
        // States
        //////////////////////
        sqlStr = "SELECT asciiname, latitude, longitude, country, admin1 as StateCode, ISOCode FROM geoname WHERE fcode = 'ADM1' ORDER BY name";
        rs = stmt.executeQuery(sqlStr);
        while (rs.next()) {
            if (buildInsert(rs, 300, earthId)) {
                String nameStr = rs.getString(1);
                String countryCode = rs.getString(4);
                String stateCode = rs.getString(5);

                pStmt.executeUpdate();

                Integer newId = BasicSQLUtils.getInsertedId(pStmt);
                Hashtable<String, Integer> stateToIdHash = countryStateCodeToIdHash.get(countryCode);
                if (stateToIdHash != null) {
                    stateToIdHash.put(stateCode, newId);
                } else {
                    log.error("****** Error - No State for code [" + stateCode + "]  Country: " + countryCode
                            + "   Name: " + nameStr);
                }
            }

            cnt++;
            if (frame != null && cnt % 100 == 0) {
                doProgress(cnt);
            }
        }
        rs.close();

        //-------------------------------------------------------------
        // Create States that are referenced by Counties in Countries
        //-------------------------------------------------------------
        sqlStr = "SELECT asciiname AS CountyName, latitude, longitude, country, admin1 as StateCode, ISOCode FROM geoname WHERE fcode = 'ADM2' ORDER BY name";
        rs = stmt.executeQuery(sqlStr);
        while (rs.next()) {
            String countryCode = rs.getString(4);
            String stateCode = rs.getString(5);

            Hashtable<String, Integer> stateToIdHash = countryStateCodeToIdHash.get(countryCode);
            if (stateToIdHash != null && stateToIdHash.get(stateCode) == null) {
                rowData.clear();
                rowData.add(rs.getString(1)); // State Name, same as Code
                rowData.add(new BigDecimal(-1000));
                rowData.add(new BigDecimal(-1000));
                rowData.add(countryCode);
                rowData.add(stateCode);
                rowData.add(rs.getString(6));

                if (buildInsert(rowData, 300, earthId)) {
                    log.debug("Adding State [" + rs.getString(1) + "][" + stateCode + "] for Country ["
                            + countryCode + "]");

                    pStmt.executeUpdate();
                    Integer newId = BasicSQLUtils.getInsertedId(pStmt);
                    stateToIdHash.put(stateCode, newId);
                }
            }

            /*cnt++;
            if (frame != null && cnt % 100 == 0)
            {
            doProgress(cnt);
            }*/

        }
        rs.close();

        doProgress("Initializing Reference Counties...");

        //////////////////////
        // County
        //////////////////////
        sqlStr = "SELECT asciiname, latitude, longitude, country, admin1 as StateCode, ISOCode FROM geoname WHERE fcode = 'ADM2' ORDER BY name";
        rs = stmt.executeQuery(sqlStr);
        while (rs.next()) {
            if (buildInsert(rs, 400, earthId)) {
                pStmt.executeUpdate();
            }

            cnt++;
            if (frame != null && cnt % 100 == 0) {
                doProgress(cnt);
            }
        }
        rs.close();

        doProgress(count);

        return true;

    } catch (Exception ex) {
        ex.printStackTrace();
        edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BuildFromGeonames.class, ex);

        try {
            updateConn.rollback();

        } catch (Exception exr) {
            exr.printStackTrace();
        }
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (readConn != null) {
                readConn.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            if (updateConn != DBConnection.getInstance()) {
                updateConn.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    if (frame != null) {
        frame.setVisible(false);
    }
    return false;
}