Example usage for java.sql Statement RETURN_GENERATED

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.tascape.qa.th.db.H2Handler.java

@Override
protected int getTestCaseId(TestCase test) throws SQLException {
    LOG.info("Query for id of test case {} ", test.format());
    try (Connection conn = this.getConnection()) {
        final String sql = "SELECT * FROM " + TestCase.TABLE_NAME + " WHERE " + TestCase.SUITE_CLASS
                + " = ? AND " + TestCase.TEST_CLASS + " = ? AND " + TestCase.TEST_METHOD + " = ? AND "
                + TestCase.TEST_DATA_INFO + " = ? AND " + TestCase.TEST_DATA + " = ?";

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, test.getSuiteClass());
        stmt.setString(2, test.getTestClass());
        stmt.setString(3, test.getTestMethod());
        stmt.setString(4, test.getTestDataInfo());
        stmt.setString(5, test.getTestData());
        stmt.setMaxRows(1);/*from w  w w  . ja va 2s  . c o  m*/

        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            return rs.getInt(TestCase.TEST_CASE_ID);
        }
    }

    try (Connection conn = this.getConnection()) {
        final String sql = "INSERT INTO " + TestCase.TABLE_NAME + " (" + TestCase.SUITE_CLASS + ", "
                + TestCase.TEST_CLASS + ", " + TestCase.TEST_METHOD + ", " + TestCase.TEST_DATA_INFO + ", "
                + TestCase.TEST_DATA + ") VALUES (?,?,?,?,?);";

        PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, test.getSuiteClass());
        stmt.setString(2, test.getTestClass());
        stmt.setString(3, test.getTestMethod());
        stmt.setString(4, test.getTestDataInfo());
        stmt.setString(5, test.getTestData());
        int i = stmt.executeUpdate();
    }

    try (Connection conn = this.getConnection()) {
        final String sql = "SELECT * FROM " + TestCase.TABLE_NAME + " WHERE " + TestCase.SUITE_CLASS
                + " = ? AND " + TestCase.TEST_CLASS + " = ? AND " + TestCase.TEST_METHOD + " = ? AND "
                + TestCase.TEST_DATA_INFO + " = ? AND " + TestCase.TEST_DATA + " = ? ORDER BY "
                + TestCase.TEST_CASE_ID + " DESC;";

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, test.getSuiteClass());
        stmt.setString(2, test.getTestClass());
        stmt.setString(3, test.getTestMethod());
        stmt.setString(4, test.getTestDataInfo());
        stmt.setString(5, test.getTestData());
        stmt.setMaxRows(1);

        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            return rs.getInt(TestCase.TEST_CASE_ID);
        }
    }
    throw new SQLException();
}

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

/**
 * Helper method to insert the configs from the  into the
 * repository.//from   w w w .j a  va 2  s  . c o m
 * @param mDriver The driver instance to use to upgrade.
 * @param conn JDBC link to use for updating the configs
 */
private void insertConfigsForDriver(MDriver mDriver, Connection conn) {
    PreparedStatement baseConfigStmt = null;
    PreparedStatement baseInputStmt = null;
    try {
        baseConfigStmt = conn.prepareStatement(STMT_INSERT_INTO_CONFIG, Statement.RETURN_GENERATED_KEYS);

        baseInputStmt = conn.prepareStatement(STMT_INSERT_INTO_INPUT, Statement.RETURN_GENERATED_KEYS);

        // Register the job config type, since driver config is per job
        registerConfigs(null, null, mDriver.getDriverConfig().getConfigs(), MConfigType.JOB.name(),
                baseConfigStmt, baseInputStmt, conn);

    } catch (SQLException ex) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0014, mDriver.toString(), ex);
    } finally {
        closeStatements(baseConfigStmt, baseInputStmt);
    }
}

From source file:moe.yuna.palinuridae.core.BaseDao.java

/**
 * @param columnValuePair/*from   w w w. j av  a 2s  . c  o  m*/
 * @param tableName
 * @return
 * @throws DBUtilException
 */
public Object save(Map<String, Object> columnValuePair, String tableName) throws DBUtilException {
    try {
        final List<Object> paras = new ArrayList<>();
        final String sql = getDialect().insert(tableName, columnValuePair, paras);
        log.debug("insert sql:" + sql);
        int id = 0;
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update((PrepareStatementCreator) (conn) -> {
            PreparedStatement stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0, size = paras.size(); i < size; i++) {
                stat.setObject(i + 1, paras.get(i));
            }

            return stat;
        }, keyHolder);
        Number key = keyHolder.getKey();
        id = key != null ? key.intValue() : 0;
        return id;
    } catch (DataAccessException ex) {
        throw new DBUtilException(ex);
    }
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public AtomicFeed addAtomicFeed(String name, String metadata, List<String> keywords, Feature feature) {
    AtomicFeed feed = null;/*from   ww w.j a va2  s  .co  m*/
    if (feature == null) {
        Log.e(TAG, "One cannot create a composed feed with no feature");
        return null;
    }
    try {
        checkOpenness();
        connection.setAutoCommit(false);
        //First things first, insert the feed's values to the feed table
        String sqlFeedInsert = "INSERT INTO " + IotHubDataHandler.TABLE_FEED + "("
                + IotHubDataHandler.KEY_FEED_NAME + "," + IotHubDataHandler.KEY_FEED_METADATA + ","
                + IotHubDataHandler.KEY_FEED_TYPE + "," + IotHubDataHandler.KEY_FEED_STORAGE + ","
                + IotHubDataHandler.KEY_FEED_READABLE + "," + IotHubDataHandler.KEY_FEED_WRITABLE
                + ") VALUES (?,?,?,?,?,?)";
        PreparedStatement psFeedInsert = connection.prepareStatement(sqlFeedInsert,
                Statement.RETURN_GENERATED_KEYS);
        psFeedInsert.setString(1, name);
        psFeedInsert.setString(2, metadata);
        psFeedInsert.setString(3, IotHubDataHandler.ATOMIC_FEED);
        psFeedInsert.setInt(4, 0);
        psFeedInsert.setInt(5, 0);
        psFeedInsert.setInt(6, 0);
        psFeedInsert.executeUpdate();
        ResultSet genKeysFeed = psFeedInsert.getGeneratedKeys();
        if (genKeysFeed.next()) {
            long insertIdFeed = genKeysFeed.getLong(1);
            //Now we add the keywords
            addFeedKeywords(insertIdFeed, keywords);
            //Now we add the fields
            addFeedFeatureRelation(insertIdFeed, feature.getId());
            //At point we should have everything set so it is time to retrieve the atomic feed from the database
            //Log.d(TAG, "Now i will try to collect the atomic feed that was just added to the db");
            feed = getAtomicFeed(insertIdFeed);
            if (feed == null) {
                Log.e(TAG, "The feed should not be null");
            }
            //Now I want to make some checks
            if (!compareAtomicFeeds(feed, name, metadata, keywords, feature)) {
                Log.e(TAG, "Retrieving feed " + name + " did not work");
                feed = null;
            }
        } else {
            Log.e(TAG, "The insert of feed " + name + " did not work");
        }
        genKeysFeed.close();
        psFeedInsert.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        feed = null;
    }
    try {
        if (feed == null) {
            connection.rollback();
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return feed;
}

From source file:com.tremolosecurity.provisioning.core.providers.BasicDB.java

private void insertCreate(User user, Set<String> attributes, Map<String, Attribute> attrs, Connection con,
        Map<String, Object> request) throws SQLException, ProvisioningException {

    int approvalID = 0;

    if (request.containsKey("APPROVAL_ID")) {
        approvalID = (Integer) request.get("APPROVAL_ID");
    }//  w w  w  .ja va2 s .  c om

    Workflow workflow = (Workflow) request.get("WORKFLOW");

    StringBuffer insert = new StringBuffer();
    insert.append("INSERT INTO ").append(this.userTable).append(" (");
    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            getFieldName(attr, insert).append(",");
        }
    }

    insert.setLength(insert.length() - 1);
    insert.append(") values (");
    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            insert.append("?,");
        }
    }
    insert.setLength(insert.length() - 1);

    insert.append(")");

    PreparedStatement ps = con.prepareStatement(insert.toString(), Statement.RETURN_GENERATED_KEYS);
    int i = 1;

    for (String attr : attributes) {
        if (attrs.get(attr) != null) {

            Attribute.DataType dataType = attrs.get(attr).getDataType();

            switch (dataType) {
            case string:
                ps.setString(i, attrs.get(attr).getValues().get(0));
                break;
            case intNum:
                ps.setInt(i, Integer.parseInt(attrs.get(attr).getValues().get(0)));
                break;
            case longNum:
                ps.setLong(i, Long.parseLong(attrs.get(attr).getValues().get(0)));
                break;

            case date:
                ps.setDate(i, new Date(ISODateTimeFormat.date()
                        .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis()));
                break;
            case timeStamp:
                ps.setTimestamp(i, new Timestamp(ISODateTimeFormat.dateTime()
                        .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis()));
                break;
            }

            i++;
        }

    }

    ps.executeUpdate();
    ResultSet rs = ps.getGeneratedKeys();

    int id;

    if (rs.next() && !this.driver.contains("oracle")) {

        id = (int) rs.getInt(1);
    } else {
        StringBuffer select = new StringBuffer();
        select.append("SELECT ");
        this.getFieldName(this.userPrimaryKey, select).append(" FROM ").append(this.userTable)
                .append(" WHERE ");
        this.getFieldName(this.userName, select).append("=?");
        PreparedStatement getUserId = con.prepareStatement(select.toString()); //con.prepareStatement( + this.userPrimaryKey + " FROM " + this.userTable + " WHERE " + this.userName + "=?");
        getUserId.setString(1, user.getUserID());
        ResultSet userResult = getUserId.executeQuery();
        userResult.next();
        id = (int) userResult.getInt(this.userPrimaryKey);

        userResult.close();
        getUserId.close();
    }

    this.cfgMgr.getProvisioningEngine().logAction(this.name, true, ActionType.Add, approvalID, workflow,
            "userName", user.getUserID());

    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID,
                    workflow, attr, attrs.get(attr).getValues().get(0));
        }
    }

    if (user.getGroups().size() > 0) {
        switch (this.groupMode) {
        case None:
            break;
        case One2Many:
            insert.setLength(0);
            insert.append("INSERT INTO ").append(this.groupTable).append(" (").append(this.groupUserKey)
                    .append(",").append(this.groupName).append(") VALUES (?,?)");
            ps = con.prepareStatement(insert.toString());

            for (String groupName : user.getGroups()) {
                ps.setInt(1, id);
                ps.setString(2, groupName);
                ps.executeUpdate();
                this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID,
                        workflow, "group", groupName);
            }

            break;
        case Many2Many:
            many2manySetGroupsCreate(user, insert, con, id, request);
            break;
        }

    }
}

From source file:com.mirth.connect.server.util.DatabaseConnection.java

public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression, List<Object> parameters)
        throws SQLException {
    PreparedStatement statement = null;

    try {//from w  w  w .j av  a  2s  .  c om
        statement = connection.prepareStatement(expression, Statement.RETURN_GENERATED_KEYS);
        logger.debug("executing prepared statement:\n" + expression);

        ListIterator<Object> iterator = parameters.listIterator();

        while (iterator.hasNext()) {
            int index = iterator.nextIndex() + 1;
            Object value = iterator.next();
            logger.debug("adding parameter: index=" + index + ", value=" + value);
            statement.setObject(index, value);
        }

        statement.executeUpdate();
        CachedRowSetImpl crs = new CachedRowSetImpl();
        crs.populate(statement.getGeneratedKeys());
        return crs;
    } catch (SQLException e) {
        throw e;
    } finally {
        DbUtils.closeQuietly(statement);
    }
}

From source file:ca.fastenalcompany.servlet.ProductServlet.java

public int update(String query, String... params) {
    Connection conn = null;/*  w w  w .  j  a  v  a2s . c  om*/
    int result = -1;
    try {
        conn = DBManager.getMysqlConn();
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        for (int i = 1; i <= params.length; i++) {
            pstmt.setString(i, params[i - 1]);
        }
        System.out.println(query);
        int rowsEffected = pstmt.executeUpdate();
        ResultSet rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            result = rs.getInt(1);
        } else if (rowsEffected > 0) {
            result = Integer.parseInt(params[params.length - 1]);
        }
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
        try {
            System.out.println("DB connection closed");
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    return result;
}

From source file:Crawler.CrawlerClass.java

public void InsertToIndexDb(DBConnection Conn, String url, String[] Keywords) throws SQLException, IOException {

    String sql = "select * from contentdb where URL = '" + url + "'";
    ResultSet rs = Conn.executeStatement(sql);

    if (rs.next()) {
        //store the URL to database to avoid parsing again
        int ID = rs.getInt("ID");
        sql = "INSERT INTO `keyworddb`(`ID`, `Keyword`) " + "VALUES(?,?);";
        PreparedStatement stmt = Conn.conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        for (String words : Keywords) {
            stmt.setInt(1, ID);// w  ww .  java  2  s .c  o  m
            stmt.setString(2, words.trim());
        }

        stmt.execute();

    }
}

From source file:org.ulyssis.ipp.snapshot.Snapshot.java

public void save(Connection connection) throws SQLException {
    if (id != -1)
        return;// w  w w .j a  va  2  s  . c om
    try (PreparedStatement statement = connection.prepareStatement(
            "INSERT INTO \"snapshots\" (\"time\",\"data\",\"event\") VALUES (?,?,?)",
            Statement.RETURN_GENERATED_KEYS)) {
        statement.setTimestamp(1, Timestamp.from(snapshotTime));
        String serialized;
        try {
            serialized = Serialization.getJsonMapper().writeValueAsString(this);
        } catch (JsonProcessingException e) {
            assert false; // TODO(Roel): Programming error
            return;
        }
        statement.setString(2, serialized);
        statement.setLong(3, eventId);
        statement.executeUpdate();
        ResultSet keys = statement.getGeneratedKeys();
        keys.next();
        this.id = keys.getLong(1);
    }
}

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

/**
 * Method for adding version details to database.
 *
 * @param dbConnection  database connection
 * @param version       version object/*from   w w w.j av a2  s  .com*/
 * @param applicationId application id
 * @param tenantId      tenant id
 * @throws AppCloudException
 */
public void addVersion(Connection dbConnection, Version version, int applicationId, int tenantId)
        throws AppCloudException {

    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {

        preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_VERSION,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, version.getVersionName());
        preparedStatement.setString(2, version.getHashId());
        preparedStatement.setInt(3, applicationId);
        preparedStatement.setInt(4, version.getRuntimeId());
        preparedStatement.setInt(5, tenantId);
        preparedStatement.setString(6, version.getConSpecCpu());
        preparedStatement.setString(7, version.getConSpecMemory());
        preparedStatement.setString(8, version.getExposureLevel());

        preparedStatement.execute();

        resultSet = preparedStatement.getGeneratedKeys();

        List<Tag> tags = version.getTags();
        if (tags != null) {
            addTags(dbConnection, tags, version.getHashId(), tenantId);
        }

        List<RuntimeProperty> runtimeProperties = version.getRuntimeProperties();
        if (runtimeProperties != null) {
            addRunTimeProperties(dbConnection, runtimeProperties, version.getHashId(), tenantId);
        }

    } catch (SQLException e) {
        String msg = "Error occurred while adding application version to database for application id : "
                + applicationId + " version : " + version.getVersionName() + " in tenant : " + tenantId;
        throw new AppCloudException(msg, e);
    } finally {
        DBUtil.closeResultSet(resultSet);
        DBUtil.closePreparedStatement(preparedStatement);
    }

}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage