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:uk.ac.cam.cl.dtg.segue.quiz.PgQuestionAttempts.java

@Override
public void registerQuestionAttempt(final Long userId, final String questionPageId, final String fullQuestionId,
        final QuestionValidationResponse questionAttempt) throws SegueDatabaseException {

    PreparedStatement pst;// ww w. j a  v a2 s  .  c o  m
    try (Connection conn = database.getDatabaseConnection()) {
        pst = conn.prepareStatement("INSERT INTO question_attempts(user_id, "
                + "question_id, question_attempt, correct, \"timestamp\")"
                + " VALUES (?, ?, ?::text::jsonb, ?, ?);", Statement.RETURN_GENERATED_KEYS);

        pst.setLong(1, userId);
        pst.setString(2, fullQuestionId);
        pst.setString(3, objectMapper.writeValueAsString(questionAttempt));

        if (questionAttempt.isCorrect() != null) {
            pst.setBoolean(4, questionAttempt.isCorrect());
        } else {
            pst.setNull(4, java.sql.Types.NULL);
        }
        pst.setTimestamp(5, new java.sql.Timestamp(questionAttempt.getDateAttempted().getTime()));

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

    } catch (SQLException e) {
        throw new SegueDatabaseException("Postgres exception", e);
    } catch (JsonProcessingException e) {
        throw new SegueDatabaseException("Unable to process json exception", e);
    }
}

From source file:at.alladin.rmbt.controlServer.IpResource.java

@Post("json")
public String request(final String entity) {
    addAllowOrigin();/*from   w  w w .  jav a 2s .  com*/
    JSONObject request = null;

    final ErrorList errorList = new ErrorList();
    final JSONObject answer = new JSONObject();
    String answerString;

    final String clientIpRaw = getIP();
    final InetAddress clientAddress = InetAddresses.forString(clientIpRaw);

    System.out.println(MessageFormat.format(labels.getString("NEW_IP_REQ"), clientIpRaw));

    if (entity != null && !entity.isEmpty()) {
        // try parse the string to a JSON object
        try {
            // debug parameters sent
            request = new JSONObject(entity);
            System.out.println(request.toString(4));

            /* sample request data
            {
                "api_level": "21",
                "device": "hammerhead",
                "language": "en",
                "model": "Nexus 5",
                "os_version": "5.0(1570415)",
                "plattform": "Android",
                "product": "hammerhead",
                "softwareRevision": "master_initial-2413-gf89049d",
                "softwareVersionCode": 20046,
                "softwareVersionName": "2.0.46",
                "timezone": "Europe/Vienna",
                "type": "MOBILE",
                "uuid": "........(uuid)........"
                "location": {
            "accuracy": 20,
            "age": 7740,
            "lat": 51.1053539,
            "long": 17.4921002,
            "provider": "network"
             },
            }
            */
            UUID uuid = null;
            final String uuidString = request.optString("uuid", "");
            if (uuidString.length() != 0)
                uuid = UUID.fromString(uuidString);

            final String clientPlattform = request.getString("plattform");
            final String clientModel = request.getString("model");
            final String clientProduct = request.getString("product");
            final String clientDevice = request.getString("device");
            final String clientSoftwareVersionCode = request.getString("softwareVersionCode");
            final String clientApiLevel = request.getString("api_level");

            final JSONObject location = request.optJSONObject("location");

            long geoage = 0; // age in ms
            double geolat = 0;
            double geolong = 0;
            float geoaccuracy = 0; // in m
            double geoaltitude = 0;
            float geospeed = 0; // in m/s
            String geoprovider = "";

            if (!request.isNull("location")) {
                geoage = location.optLong("age", 0);
                geolat = location.optDouble("lat", 0);
                geolong = location.optDouble("long", 0);
                geoaccuracy = (float) location.optDouble("accuracy", 0);
                geoaltitude = location.optDouble("altitude", 0);
                geospeed = (float) location.optDouble("speed", 0);
                geoprovider = location.optString("provider", "");
            }

            if (errorList.getLength() == 0)
                try {
                    PreparedStatement st;
                    st = conn.prepareStatement(
                            "INSERT INTO status(client_uuid,time,plattform,model,product,device,software_version_code,api_level,ip,"
                                    + "age,lat,long,accuracy,altitude,speed,provider)"
                                    + "VALUES(?, NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                    int i = 1;
                    st.setObject(i++, uuid);
                    st.setObject(i++, clientPlattform);
                    st.setObject(i++, clientModel);
                    st.setObject(i++, clientProduct);
                    st.setObject(i++, clientDevice);
                    st.setObject(i++, clientSoftwareVersionCode);
                    st.setObject(i++, clientApiLevel);
                    st.setObject(i++, clientIpRaw);
                    // location information
                    st.setObject(i++, geoage);
                    st.setObject(i++, geolat);
                    st.setObject(i++, geolong);
                    st.setObject(i++, geoaccuracy);
                    st.setObject(i++, geoaltitude);
                    st.setObject(i++, geospeed);
                    st.setObject(i++, geoprovider);

                    final int affectedRows = st.executeUpdate();
                    if (affectedRows == 0)
                        errorList.addError("ERROR_DB_STORE_STATUS");
                } catch (final SQLException e) {
                    errorList.addError("ERROR_DB_STORE_GENERAL");
                    e.printStackTrace();
                }

            answer.put("ip", clientIpRaw);
            if (clientAddress instanceof Inet4Address) {
                answer.put("v", "4");
            } else if (clientAddress instanceof Inet6Address) {
                answer.put("v", "6");
            } else {
                answer.put("v", "0");
            }
        } catch (final JSONException e) {
            errorList.addError("ERROR_REQUEST_JSON");
            System.out.println("Error parsing JSON Data " + e.toString());
        }
    } else {
        errorList.addErrorString("Expected request is missing.");
    }

    try {
        answer.putOpt("error", errorList.getList());
    } catch (final JSONException e) {
        System.out.println("Error saving ErrorList: " + e.toString());
    }

    answerString = answer.toString();

    return answerString;
}

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

/**
 * Method for adding application details to database.
 *
 * @param dbConnection database connection
 * @param application  application object
 * @param tenantId     tenant id/*from www . j  a v  a2 s. c o  m*/
 * @throws AppCloudException
 */
public void addApplication(Connection dbConnection, Application application, int tenantId)
        throws AppCloudException {

    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
        int applicationId = 0;
        preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_APPLICATION,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, application.getApplicationName());
        preparedStatement.setString(2, application.getHashId());
        preparedStatement.setString(3, application.getDescription());
        preparedStatement.setInt(4, tenantId);
        preparedStatement.setString(5, application.getDefaultVersion());
        preparedStatement.setString(6, application.getApplicationType());
        preparedStatement.setString(7, application.getCloudType());

        preparedStatement.execute();

        resultSet = preparedStatement.getGeneratedKeys();
        while (resultSet.next()) {
            applicationId = resultSet.getInt(1);
        }

        List<Version> versions = application.getVersions();

        if (versions != null) {
            for (Version version : versions) {
                addVersion(dbConnection, version, applicationId, tenantId);
            }
        }

        InputStream iconInputStream;
        if (application.getIcon() != null) {
            iconInputStream = IOUtils.toBufferedInputStream(application.getIcon().getBinaryStream());
            if (iconInputStream.available() != 0) {
                updateApplicationIcon(dbConnection, iconInputStream, applicationId, tenantId);
            }
        }

    } catch (SQLException e) {

        String msg = "Error occurred while adding application : " + application.getApplicationName()
                + " to database " + "in tenant : " + tenantId + " and cloud : " + application.getCloudType();
        throw new AppCloudException(msg, e);

    } catch (IOException e) {
        String msg = "Error while generating stream of the icon for application : "
                + application.getApplicationName() + " in tenant : " + tenantId + " and cloud : "
                + application.getCloudType();
        throw new AppCloudException(msg, e);
    } finally {
        DBUtil.closeResultSet(resultSet);
        DBUtil.closePreparedStatement(preparedStatement);
    }

}

From source file:com.act.lcms.db.model.BaseDBModel.java

protected T insert(DB db, T toInsert, String errMsg) throws SQLException, IOException {
    Connection conn = db.getConn();
    try (PreparedStatement stmt = conn.prepareStatement(getInsertQuery(), Statement.RETURN_GENERATED_KEYS)) {
        bindInsertOrUpdateParameters(stmt, toInsert);
        stmt.executeUpdate();//w w w . j a v  a  2  s.  c o m
        try (ResultSet resultSet = stmt.getGeneratedKeys()) {
            if (resultSet.next()) {
                // Get auto-generated id.
                int id = resultSet.getInt(1);
                toInsert.setId(id);
                return toInsert;
            } else {
                // TODO: log error here.
                if (errMsg != null) {
                    System.err.format("ERROR: %s\n", errMsg);
                } else {
                    System.err.format("ERROR: could not retrieve autogenerated key for inserted row\n");
                }
                return null;
            }
        }
    }
}

From source file:data.AbstractRepository.java

/**
*
* @param t//from w  w w.  ja  v  a2s . com
* @throws DataException
*/
@Override
public void save(T t) throws DataException {
    String query;
    int generatedKeys;

    if (t.getId() < 0) {
        query = getInsertQuery(t);
        generatedKeys = Statement.RETURN_GENERATED_KEYS;
    } else {
        query = getUpdateQuery(t);
        generatedKeys = Statement.NO_GENERATED_KEYS;
    }

    Connection connection;
    PreparedStatement statement;
    try {
        connection = DriverManager.getConnection(url, username, password);
        statement = connection.prepareStatement(query, generatedKeys);
        statement.executeUpdate();

        if (t.getId() > 0)
            return;

        ResultSet keys = statement.getGeneratedKeys();

        try {

            if (keys.next()) {
                t.setId(keys.getInt(1));
            }
        } finally {
            keys.close();
            statement.close();
            connection.close();
        }
    } catch (SQLException ex) {
        throw new DataException("Error saving: " + t);
    }
}

From source file:org.cerberus.crud.dao.impl.TestCaseExecutionDAO.java

@Override
public long insertTCExecution(TestCaseExecution tCExecution) throws CerberusException {
    boolean throwEx = false;
    final String query = "INSERT INTO testcaseexecution(test, testcase, build, revision, environment, environmentData, country, browser, application, ip, "
            + "url, port, tag, verbose, status, start, controlstatus, controlMessage, crbversion, finished, browserFullVersion, executor, screensize,"
            + "conditionOper, conditionVal1Init, conditionVal2Init, conditionVal1, conditionVal2, manualExecution) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    Connection connection = this.databaseSpring.connect();
    try {/*from w ww  .jav  a  2 s. c  om*/
        PreparedStatement preStat = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        try {
            int i = 1;
            preStat.setString(i++, tCExecution.getTest());
            preStat.setString(i++, tCExecution.getTestCase());
            preStat.setString(i++, tCExecution.getBuild());
            preStat.setString(i++, tCExecution.getRevision());
            preStat.setString(i++, tCExecution.getEnvironment());
            preStat.setString(i++, tCExecution.getEnvironmentData());
            preStat.setString(i++, tCExecution.getCountry());
            preStat.setString(i++, tCExecution.getBrowser());
            preStat.setString(i++, tCExecution.getApplicationObj().getApplication());
            preStat.setString(i++, tCExecution.getIp());
            preStat.setString(i++, tCExecution.getUrl());
            preStat.setString(i++, tCExecution.getPort());
            preStat.setString(i++, tCExecution.getTag());
            preStat.setInt(i++, tCExecution.getVerbose());
            preStat.setString(i++, tCExecution.getStatus());
            preStat.setTimestamp(i++, new Timestamp(tCExecution.getStart()));
            preStat.setString(i++, tCExecution.getControlStatus());
            preStat.setString(i++, StringUtil.getLeftString(tCExecution.getControlMessage(), 500));
            preStat.setString(i++, tCExecution.getCrbVersion());
            preStat.setString(i++, tCExecution.getFinished());
            preStat.setString(i++, tCExecution.getBrowserFullVersion());
            preStat.setString(i++, tCExecution.getExecutor());
            preStat.setString(i++, tCExecution.getScreenSize());
            preStat.setString(i++, tCExecution.getConditionOper());
            preStat.setString(i++, tCExecution.getConditionVal1Init());
            preStat.setString(i++, tCExecution.getConditionVal2Init());
            preStat.setString(i++, tCExecution.getConditionVal1());
            preStat.setString(i++, tCExecution.getConditionVal2());
            preStat.setString(i++, tCExecution.isManualExecution() ? "Y" : "N");

            preStat.executeUpdate();
            ResultSet resultSet = preStat.getGeneratedKeys();
            try {
                if (resultSet.first()) {
                    return resultSet.getInt(1);
                }
            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
                throwEx = true;
            } finally {
                resultSet.close();
            }

        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
            throwEx = true;
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
        throwEx = true;
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    if (throwEx) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.EXECUTION_FA));
    }
    return 0;
}

From source file:org.openhab.persistence.sql.internal.SqlPersistenceService.java

private String getTable(Item item) {
    Statement statement = null;//w  w  w.j  a  va 2s.  c o m
    String sqlCmd = null;
    int rowId = 0;

    String itemName = item.getName();

    String tableName = sqlTables.get(itemName);

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

    // 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 ('" + itemName + "')");

        statement = connection.createStatement();
        statement.executeUpdate(sqlCmd, Statement.RETURN_GENERATED_KEYS);

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

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

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

    // An error occurred!
    if (tableName == null)
        return null;

    // Default the type to double
    String mysqlType = new String("DOUBLE");
    String itemType = item.getClass().toString().toUpperCase();
    itemType = itemType.substring(itemType.lastIndexOf('.') + 1);
    if (sqlTypes.get(itemType) != null) {
        mysqlType = sqlTypes.get(itemType);
    }

    // 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("SQL: " + sqlCmd);

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

        logger.debug("SQL: Table created for item '" + itemName + "' with datatype " + mysqlType
                + " in SQL database.");
        sqlTables.put(itemName, tableName);
    } catch (Exception e) {
        logger.error("SQL: Could not create table for item '" + itemName + "' with statement '" + sqlCmd + "': "
                + e.getMessage());
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception hidden) {
            }
        }
    }

    return tableName;
}

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

public static PreparedStatement preparedUpdateStatement(Class<? extends DomainEntity> entityClass,
        String tableName, Connection connection) throws SQLException {
    ArrayList<String> fieldCouples = new ArrayList<String>();
    try {/*from   w w  w  . j av a 2  s  . c om*/
        ArrayList<String> fieldNames = ComponingFieldsFactory.create(entityClass);
        for (String aFieldName : fieldNames) {
            fieldCouples.add(aFieldName + "=?");
        }
    } catch (Exception exception) {
        // TODO Auto-generated catch block
        exception.printStackTrace();
    }

    String[] parameters = new String[fieldCouples.size()];
    Arrays.fill(parameters, "?");

    StringBuilder sql = new StringBuilder().append("update ").append(tableName).append(" set ")
            .append(StringUtils.join(fieldCouples, ", ")).append(" where ").append(DomainEntity.ID)
            .append("=?;");

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

From source file:com.softberries.klerk.dao.ProductDao.java

@Override
public void create(Product p) throws SQLException {
    try {// w  ww . j a  v a 2s .  co  m
        init();
        st = conn.prepareStatement(SQL_INSERT_PRODUCT, Statement.RETURN_GENERATED_KEYS);
        st.setString(1, p.getCode());
        st.setString(2, p.getName());
        st.setString(3, p.getDescription());
        // run the query
        int i = st.executeUpdate();
        System.out.println("i: " + i);
        if (i == -1) {
            System.out.println("db error : " + SQL_INSERT_PRODUCT);
        }
        generatedKeys = st.getGeneratedKeys();
        if (generatedKeys.next()) {
            p.setId(generatedKeys.getLong(1));
        } else {
            throw new SQLException("Creating user failed, no generated key obtained.");
        }
        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:com.keybox.manage.db.SessionAuditDB.java

/**
 * insert new session record for user//from ww  w.  j  a v  a  2s.c om
 *
 * @param con    DB connection
 * @param userId user id
 * @return session id
 */
public static Long createSessionLog(Connection con, Long userId) {
    Long sessionId = null;
    try {

        //insert
        PreparedStatement stmt = con.prepareStatement("insert into session_log (user_id) values(?)",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setLong(1, userId);
        stmt.execute();
        ResultSet rs = stmt.getGeneratedKeys();
        if (rs != null && rs.next()) {
            sessionId = rs.getLong(1);
        }

        DBUtils.closeStmt(stmt);

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

}