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:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * //from   w  w w  .  j  a va2  s. c om
 */
public static void convertObservations(final Connection oldDBConn, final Connection newDBConn,
        final int disciplineID) {
    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);

    String sql = "SELECT cc.CollectionObjectCatalogID, o.ObservationID, o.Text1, o.Text2, o.Number1, o.Remarks ";
    String baseSQL = " FROM collectionobjectcatalog AS cc Inner Join observation AS o ON cc.CollectionObjectCatalogID = o.BiologicalObjectID";
    String ORDERBY = " ORDER BY cc.CollectionObjectCatalogID";

    Calendar cal = Calendar.getInstance();
    Timestamp tsCreated = new Timestamp(cal.getTimeInMillis());
    IdMapperIFace coMapper = IdMapperMgr.getInstance().get("collectionobjectcatalog",
            "CollectionObjectCatalogID");
    if (coMapper == null) {
        coMapper = IdMapperMgr.getInstance().addTableMapper("collectionobjectcatalog",
                "CollectionObjectCatalogID", false);
    }

    int totalCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) " + baseSQL);
    if (totalCnt < 1)
        return;

    Statement stmt = null;
    PreparedStatement pStmt = null;
    PreparedStatement updateStmt = null;
    PreparedStatement insertStmt = null;
    PreparedStatement updateCOStmt = null;
    try {
        pStmt = newDBConn.prepareStatement(
                "SELECT co.CollectionObjectAttributeID FROM collectionobject AS co WHERE co.CollectionObjectID = ? AND co.CollectionObjectAttributeID IS NOT NULL");
        updateStmt = newDBConn.prepareStatement(
                "UPDATE collectionobjectattribute SET Text1=?, Text2=?, Number1=?, Remarks=? WHERE CollectionObjectAttributeID = ?");
        insertStmt = newDBConn.prepareStatement(
                "INSERT INTO collectionobjectattribute (Version, TimestampCreated, CollectionMemberID, CreatedByAgentID, Text1, Text2, Number1, Remarks) VALUES(0, ?, ?, ?, ?, ?, ?, ?)",
                Statement.RETURN_GENERATED_KEYS);
        updateCOStmt = newDBConn.prepareStatement(
                "UPDATE collectionobject SET CollectionObjectAttributeID=? WHERE CollectionObjectID = ?");

        int cnt = 0;

        stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql + baseSQL + ORDERBY);
        while (rs.next()) {
            int ccId = rs.getInt(1);
            String text1 = rs.getString(3);
            String text2 = rs.getString(4);
            Integer number1 = rs.getInt(5);
            String remarks = rs.getString(6);
            Integer newId = coMapper.get(ccId);
            if (newId == null) {
                log.error("Old Co Id [" + ccId + "] didn't map to new ID.");
                continue;
            }

            pStmt.setInt(1, newId);
            ResultSet rs2 = pStmt.executeQuery();
            if (rs2.next()) {
                updateStmt.setString(1, text1);
                updateStmt.setString(2, text2);
                updateStmt.setInt(3, number1);
                updateStmt.setString(4, remarks);
                updateStmt.setInt(5, rs2.getInt(1));
                if (updateStmt.executeUpdate() != 1) {
                    log.error("Error updating collectionobjectattribute");
                }
            } else {
                int memId = BasicSQLUtils.getCountAsInt(
                        "SELECT CollectionMemberID FROM collectionobject WHERE CollectionObjectID = " + newId);
                insertStmt.setTimestamp(1, tsCreated);
                insertStmt.setInt(2, memId);
                insertStmt.setInt(3, 1); // Created By Agent
                insertStmt.setString(4, text1);
                insertStmt.setString(5, text2);
                insertStmt.setInt(6, number1);
                insertStmt.setString(7, remarks);

                if (insertStmt.executeUpdate() != 1) {
                    log.error("Error inserting collectionobjectattribute");
                }

                int newCOAId = BasicSQLUtils.getInsertedId(insertStmt);

                updateCOStmt.setInt(1, newCOAId);
                updateCOStmt.setInt(2, newId);
                if (updateCOStmt.executeUpdate() != 1) {
                    log.error(
                            "Error updating collectionobject newCOAId[" + newCOAId + "] newId[" + newId + "]");
                }
            }
            rs2.close();

            cnt++;
            if (cnt % 1000 == 0) {
                System.out.println(String.format("%d / %d", cnt, totalCnt));
            }
        }
        rs.close();

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

    } finally {
        try {
            if (stmt != null)
                stmt.close();
            if (pStmt != null)
                pStmt.close();
            if (updateStmt != null)
                updateStmt.close();
            if (insertStmt != null)
                insertStmt.close();
            if (updateCOStmt != null)
                updateCOStmt.close();

        } catch (SQLException ex) {
        }
    }
}

From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java

/**
 * Method for adding container service proxy.
 *
 * @param dbConnection          database connection
 * @param containerServiceProxy container service proxy object
 * @param containerId           id of container object
 * @param tenantId              id of tenant
 * @throws AppCloudException//ww w .  j a  v  a  2 s .c o m
 */
public void addContainerServiceProxy(Connection dbConnection, ContainerServiceProxy containerServiceProxy,
        int containerId, int tenantId) throws AppCloudException {

    PreparedStatement preparedStatement = null;

    try {

        preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_CONTAINER_SERVICE_PROXY,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, containerServiceProxy.getServiceName());
        preparedStatement.setString(2, containerServiceProxy.getServiceProtocol());
        preparedStatement.setInt(3, containerServiceProxy.getServicePort());
        preparedStatement.setString(4, containerServiceProxy.getServiceBackendPort());
        preparedStatement.setInt(5, containerId);
        preparedStatement.setInt(6, tenantId);
        preparedStatement.execute();

    } catch (SQLException e) {
        String msg = "Error while inserting container service proxy record in tenant : " + tenantId;
        throw new AppCloudException(msg, e);
    } finally {
        DBUtil.closePreparedStatement(preparedStatement);
    }
}

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

/**
 * log an event in the database.//from  w w w  .j  a v a  2  s . 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.openhab.persistence.mysql.internal.MysqlPersistenceService.java

/**
 * Connects to the database//from  www . j  a va 2  s  .  c o  m
 */
private void connectToDatabase() {
    try {
        // Reset the error counter
        errCnt = 0;

        logger.debug("mySQL: Attempting to connect to database {}", url);
        Class.forName(driverClass).newInstance();
        connection = DriverManager.getConnection(url, user, password);
        logger.debug("mySQL: Connected to database {}", url);

        Statement st = connection.createStatement();
        int result = st.executeUpdate("SHOW TABLES LIKE 'Items'");
        st.close();

        if (waitTimeout != -1) {
            logger.debug("mySQL: Setting wait_timeout to {} seconds.", waitTimeout);
            st = connection.createStatement();
            st.executeUpdate("SET SESSION wait_timeout=" + waitTimeout);
            st.close();
        }
        if (result == 0) {
            st = connection.createStatement();
            st.executeUpdate(
                    "CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,PRIMARY KEY (ItemId));",
                    Statement.RETURN_GENERATED_KEYS);
            st.close();
        }

        // Retrieve the table array
        st = connection.createStatement();

        // Turn use of the cursor on.
        st.setFetchSize(50);
        ResultSet rs = st.executeQuery("SELECT ItemId, ItemName FROM Items");
        while (rs.next()) {
            sqlTables.put(rs.getString(2), "Item" + rs.getInt(1));
        }
        rs.close();
        st.close();
    } catch (Exception e) {
        logger.error(
                "mySQL: Failed connecting to the SQL database using: driverClass={}, url={}, user={}, password={}",
                driverClass, url, user, password, e);
    }
}

From source file:org.geowebcache.storage.jdbc.jobstore.JDBCJobWrapper.java

public void putJob(JobObject stObj) throws SQLException, StorageException {

    String query = "MERGE INTO " + "JOBS(job_id, layer_name, state, time_spent, time_remaining, tiles_done, "
            + "tiles_total, failed_tile_count, bounds, gridset_id, srs, thread_count, "
            + "zoom_start, zoom_stop, format, job_type, throughput, max_throughput, "
            + "priority, schedule, run_once, spawned_by, filter_update, parameters, "
            + "time_first_start, time_latest_start, time_finish) " + "KEY(job_id) "
            + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    final Connection conn = getConnection();

    try {//from  w ww.  j  a v  a 2  s  .c  om
        Long insertId;
        PreparedStatement prep = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        try {
            if (stObj.getJobId() == -1) {
                prep.setNull(1, java.sql.Types.BIGINT);
            } else {
                prep.setLong(1, stObj.getJobId());
            }

            prep.setString(2, stObj.getLayerName());
            prep.setString(3, stObj.getState().name());
            prep.setLong(4, stObj.getTimeSpent());
            prep.setLong(5, stObj.getTimeRemaining());
            prep.setLong(6, stObj.getTilesDone());
            prep.setLong(7, stObj.getTilesTotal());
            prep.setLong(8, stObj.getFailedTileCount());

            prep.setString(9, stObj.getBounds().toString());
            prep.setString(10, stObj.getGridSetId());
            prep.setInt(11, stObj.getSrs().getNumber());

            prep.setInt(12, stObj.getThreadCount());
            prep.setInt(13, stObj.getZoomStart());
            prep.setInt(14, stObj.getZoomStop());
            prep.setString(15, stObj.getFormat());
            prep.setString(16, stObj.getJobType().name());
            prep.setFloat(17, stObj.getThroughput());
            prep.setInt(18, stObj.getMaxThroughput());
            prep.setString(19, stObj.getPriority().name());
            prep.setString(20, stObj.getSchedule());
            prep.setBoolean(21, stObj.isRunOnce());
            prep.setLong(22, stObj.getSpawnedBy());
            prep.setBoolean(23, stObj.isFilterUpdate());
            prep.setString(24, stObj.getEncodedParameters());

            prep.setTimestamp(25, stObj.getTimeFirstStart());
            prep.setTimestamp(26, stObj.getTimeLatestStart());
            prep.setTimestamp(27, stObj.getTimeFinish());

            insertId = wrappedInsert(prep);
        } finally {
            close(prep);
        }
        if (insertId == null) {
            log.error("Did not receive an id for " + query);
        } else {
            if (stObj.getJobId() == -1) {
                // only use the inserted id if we were doing an insert.
                // what insertid will be if we weren't doing an insert is not defined.
                stObj.setJobId(insertId.longValue());
            }
        }

        putRecentJobLogs(stObj, conn);

    } finally {
        conn.close();
    }
}

From source file:com.buckwa.dao.impl.excise4.Form24DaoImpl.java

@Override
public void updateStep(final String form24Id, final String nexStep) {

    try {/*from  ww w .  j  a v a2 s.co  m*/
        final String userName = BuckWaUtils.getUserNameFromContext();
        final String fullName = BuckWaUtils.getFullNameFromContext();
        final Timestamp currentDate = new java.sql.Timestamp(System.currentTimeMillis());
        final String docNo = ExciseHelper.getDocNo();

        logger.info(" docNo:" + docNo + "  userName:" + userName + "  fuulName:" + fullName + " currentDate+"
                + currentDate);

        if (ExciseConstants.FORM24_STEP_2.equals(nexStep)) {
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(""
                            + "  update  form24  set step=?,doc_no=?,audit_by=?,audit_by_full_name=?,audit_date=? where form24_id=? "
                            + "", Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, nexStep);
                    ps.setString(2, docNo);
                    ps.setString(3, userName);
                    ps.setString(4, fullName);
                    ps.setTimestamp(5, currentDate);
                    ps.setLong(6, new Long(form24Id));
                    return ps;
                }
            });

        } else {
            jdbcTemplate.update(" update form24 set step ='" + nexStep + "' where form24_id=" + form24Id);
        }

    } catch (BuckWaException ex) {
        ex.printStackTrace();
        //throw ex;
    }
}

From source file:edu.umd.cs.marmoset.modelClasses.Project.java

public void insert(Connection conn) throws SQLException {
    String insert = Queries.makeInsertStatement(ATTRIBUTE_NAME_LIST.length, ATTRIBUTES, TABLE_NAME);

    PreparedStatement stmt = null;
    try {/*from  w  w w. ja v  a2 s . c  o  m*/
        stmt = conn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);

        int index = 1;
        putValues(stmt, index);

        stmt.executeUpdate();

        setProjectPK(Project.asPK(Queries.getGeneratedPrimaryKey(stmt)));

    } finally {
        Queries.closeStatement(stmt);
    }

    // [NAT] make sure all existing teams in the course do not have
    //       release test access access to this project
    StudentSubmitStatus.banExistingTeamsFromProject(conn, getCoursePK(), getProjectPK());
}

From source file:org.geowebcache.storage.jdbc.metastore.JDBCMBWrapper.java

public void putTile(TileObject stObj) throws SQLException {

    String query = "MERGE INTO "
            + "TILES(LAYER_ID,X,Y,Z,GRIDSET_ID,FORMAT_ID,PARAMETERS_ID,BLOB_SIZE,LOCK,CREATED) "
            + "KEY(LAYER_ID,X,Y,Z,GRIDSET_ID,FORMAT_ID,PARAMETERS_ID) " + "VALUES(?,?,?,?,?,?,?,?,NOW(),?)";

    long[] xyz = stObj.getXYZ();

    final Connection conn = getConnection();

    try {// w  w  w . j a  v a2 s  .c  o m
        Long insertId;
        PreparedStatement prep = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        try {
            prep.setLong(1, stObj.getLayerId());
            prep.setLong(2, xyz[0]);
            prep.setLong(3, xyz[1]);
            prep.setLong(4, xyz[2]);
            prep.setLong(5, stObj.getGridSetIdId());
            prep.setLong(6, stObj.getFormatId());
            if (stObj.getParametersId() == -1L) {
                prep.setNull(7, java.sql.Types.BIGINT);
            } else {
                prep.setLong(7, stObj.getParametersId());
            }
            prep.setInt(8, stObj.getBlobSize());
            prep.setLong(9, System.currentTimeMillis());
            insertId = wrappedInsert(prep);
        } finally {
            close(prep);
        }
        if (insertId == null) {
            log.error("Did not receive a id for " + query);
        } else {
            stObj.setId(insertId.longValue());
        }

    } finally {
        conn.close();
    }

}

From source file:com.jabyftw.lobstercraft.player.PlayerHandlerService.java

/**
 * Register player/*from   w  ww. j a va 2  s.c om*/
 *
 * @param encryptedPassword player's encrypted password
 * @return a LoginResponse to send the CommandSender ("LOGIN_WENT_ASYNCHRONOUS_SUCCESSFULLY" is a success)
 * @see Util#encryptString(String) for password encrypting
 */
public OnlinePlayer.LoginResponse registerPlayer(@NotNull final OnlinePlayer onlinePlayer,
        @NotNull final String encryptedPassword) {
    final OfflinePlayer offlinePlayer = onlinePlayer.getOfflinePlayer();
    // Check if player is registered
    if (offlinePlayer.isRegistered())
        return OnlinePlayer.LoginResponse.ALREADY_REGISTERED;

    Bukkit.getScheduler().runTaskAsynchronously(LobsterCraft.plugin, () -> {
        try {
            // Set offline player's attributes (lastIp is just set on login)
            offlinePlayer.lastIp = onlinePlayer.getPlayer().getAddress().getAddress().getHostAddress();
            offlinePlayer.encryptedPassword = encryptedPassword;
            offlinePlayer.databaseState = DatabaseState.INSERT_TO_DATABASE;

            // Register player on database
            Connection connection = LobsterCraft.dataSource.getConnection();

            // Prepare statement
            PreparedStatement preparedStatement = connection
                    .prepareStatement("INSERT INTO `minecraft`.`user_profiles`"
                            + "(`playerName`, `password`, `moneyAmount`, `city_cityId`, `cityOccupation`, `lastTimeOnline`, `timePlayed`, `lastIp`)"
                            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?);", Statement.RETURN_GENERATED_KEYS);

            // Set variables
            preparedStatement.setString(1, offlinePlayer.getPlayerName().toLowerCase()); // Lower case it just to make sure
            preparedStatement.setString(2, offlinePlayer.getEncryptedPassword());
            preparedStatement.setDouble(3, offlinePlayer.getMoneyAmount());
            preparedStatement.setObject(4, offlinePlayer.getCityId(), Types.SMALLINT); // Will write null if is null
            preparedStatement.setObject(5,
                    offlinePlayer.getCityOccupation() != null
                            ? offlinePlayer.getCityOccupation().getOccupationId()
                            : null,
                    Types.TINYINT);
            preparedStatement.setLong(6, offlinePlayer.getLastTimeOnline());
            preparedStatement.setLong(7, offlinePlayer.getTimePlayed());
            preparedStatement.setString(8, offlinePlayer.getLastIp());

            // Execute statement
            preparedStatement.execute();

            // Retrieve generated keys
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();

            // Check if key exists
            if (!generatedKeys.next())
                throw new SQLException("Query didn't return any generated key");

            offlinePlayer.playerId = generatedKeys.getInt("playerId");

            // Close everything
            generatedKeys.close();
            preparedStatement.close();
            connection.close();

            // Check if was successful
            if (offlinePlayer.getPlayerId() == null || offlinePlayer.getPlayerId() <= 0)
                throw new IllegalStateException(Util.appendStrings("Failed to register player: playerId is ",
                        offlinePlayer.getPlayerId()));

            // Change player's instance location
            synchronized (playerMapsLock) {
                unregisteredOfflinePlayers_name.remove(offlinePlayer.getPlayerName(), offlinePlayer);
                registeredOfflinePlayers_name.put(offlinePlayer.getPlayerName(), offlinePlayer);
                registeredOfflinePlayers_id.put(offlinePlayer.getPlayerId(), offlinePlayer);

                // Check if player has a city (even though he just registered...)
                if (offlinePlayer.getCityId() != null) {
                    if (!registeredOfflinePlayers_cityId.containsKey(offlinePlayer.getCityId()))
                        registeredOfflinePlayers_cityId.put(offlinePlayer.getCityId(), new HashSet<>());
                    registeredOfflinePlayers_cityId.get(offlinePlayer.getCityId()).add(offlinePlayer);
                }
            }

            // Update database state
            offlinePlayer.databaseState = DatabaseState.ON_DATABASE;
            onlinePlayer.onlineState = OnlinePlayer.OnlineState.PRE_LOGIN;

            // Force login
            forceLoginPlayer(onlinePlayer);
        } catch (Exception exception) {
            exception.printStackTrace();
            onlinePlayer.getPlayer().kickPlayer("4Um erro ocorreu ao registrar!");
        }
    });

    return OnlinePlayer.LoginResponse.LOGIN_WENT_ASYNCHRONOUS_SUCCESSFULLY;
}

From source file:org.wso2.carbon.device.mgt.iot.virtualfirealarm.plugin.impl.dao.VirtualFirealarmSensorDAO.java

private boolean addSensorProperties(String sensorIdentifier, Map<String, String> deviceSensorProperties)
        throws DeviceSensorDAOException {
    Connection conn;/*www.j  ava  2s  .com*/
    PreparedStatement stmt = null;
    try {
        for (String property : deviceSensorProperties.keySet()) {
            String value = deviceSensorProperties.get(property);

            conn = this.getConnection();
            String insertDBQuery = "INSERT INTO VIRTUAL_FIREALARM_SENSOR_DYNAMIC_PROPERTIES ("
                    + "SENSOR_IDENTIFIER," + "PROPERTY_KEY," + "PROPERTY_VALUE) " + "VALUES (?,?,?)";

            stmt = conn.prepareStatement(insertDBQuery, Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, sensorIdentifier);
            stmt.setString(2, property);
            stmt.setString(3, value);
            int rows = stmt.executeUpdate();

            if (rows > 0 && log.isDebugEnabled()) {
                log.debug(
                        "Properties of Sensor with Id [" + sensorIdentifier + "] " + "was added successfully.");
            }
        }
    } catch (SQLException e) {
        String msg = "Error occurred whilst adding properties (after adding the sensor) of a new "
                + "Sensor whose is Id [" + sensorIdentifier + "].";
        log.error(msg, e);
        throw new DeviceSensorDAOException(msg, e);
    } catch (VirtualFirealarmDeviceMgtPluginException e) {
        String msg = "Error occurred whilst trying to open connection to DB to register a new Sensor.";
        log.error(msg, e);
        throw new DeviceSensorDAOException(msg, e);
    } finally {
        VirtualFireAlarmUtils.cleanupResources(stmt, null);
    }
    return true;
}