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.hawkular.inventory.impl.tinkerpop.sql.impl.SqlGraph.java

@Override
public synchronized SqlEdge addEdge(Object id, Vertex outVertex, Vertex inVertex, String label) {
    if (label == null) {
        throw new IllegalArgumentException("null label");
    }/*from   www  .j  a  va 2  s . c  o  m*/

    return withSavePoint(() -> {
        PreparedStatement stmt = statements.getAddEdge((Long) inVertex.getId(), (Long) outVertex.getId(),
                label);

        if (stmt.executeUpdate() == 0) {
            return null;
        }

        long eid = -1;
        try (ResultSet rs = stmt.getGeneratedKeys()) {
            if (!rs.next()) {
                return null;
            }

            eid = rs.getLong(1);
        }

        try (ResultSet rs = statements.getGetEdge(eid).executeQuery()) {
            if (!rs.next()) {
                return null;
            }

            SqlEdge ret = SqlEdge.GENERATOR.generate(this, rs);
            dirty = true;
            return ret;
        }
    });
}

From source file:i5.las2peer.services.loadStoreGraphService.LoadStoreGraphService.java

/**
 * //from   w w  w.j av a 2 s.co m
 * storeGraph
 * 
 * @param graph a JSONObject
 * 
 * @return HttpResponse
 * 
 */
@POST
@Path("/")
@Produces(MediaType.TEXT_PLAIN)
@Consumes(MediaType.APPLICATION_JSON)
@ApiResponses(value = { @ApiResponse(code = HttpURLConnection.HTTP_INTERNAL_ERROR, message = "internalError"),
        @ApiResponse(code = HttpURLConnection.HTTP_CREATED, message = "graphStored") })
@ApiOperation(value = "storeGraph", notes = "")
public HttpResponse storeGraph(@ContentParam String graph) {
    JSONObject graph_JSON = (JSONObject) JSONValue.parse(graph);
    String insertQuery = "";
    int id = (int) graph_JSON.get("graphId");
    if (id == -1) {
        id = 0; // in case of a new graph
    }
    String description = (String) graph_JSON.get("description");
    JSONArray array = (JSONArray) graph_JSON.get("nodes");
    String nodes = array.toJSONString();
    array = (JSONArray) graph_JSON.get("links");
    String links = array.toJSONString();
    Connection conn = null;
    PreparedStatement stmnt = null;
    try {
        conn = dbm.getConnection();
        // formulate statement
        insertQuery = "INSERT INTO graphs ( graphId,  description,  nodes,  links ) " + "VALUES ('" + id
                + "', '" + description + "', '" + nodes + "', '" + links + "') ON DUPLICATE KEY UPDATE "
                + "description = + '" + description + "', " + "nodes = + '" + nodes + "', " + "links = + '"
                + links + "';";
        stmnt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
        // execute query
        stmnt.executeUpdate();
        ResultSet genKeys = stmnt.getGeneratedKeys();
        if (genKeys.next()) {
            int newId = genKeys.getInt(1);
            // return HTTP response on success with new id
            String r = newId + "";
            HttpResponse graphStored = new HttpResponse(r, HttpURLConnection.HTTP_CREATED);
            return graphStored;
        }
        // return HTTP response on success with id of updated graph
        String r = id + "";
        HttpResponse graphStored = new HttpResponse(r, HttpURLConnection.HTTP_CREATED);
        return graphStored;
    } catch (Exception e) {
        String er = "Internal error: " + e.getMessage();
        HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR);
        return internalError;
    } finally {
        // free resources
        if (stmnt != null) {
            try {
                stmnt.close();
            } catch (Exception e) {
                String er = "Internal error: " + e.getMessage();
                HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR);
                return internalError;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                Context.logError(this, e.getMessage());
                String er = "Internal error: " + e.getMessage();
                HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR);
                return internalError;
            }
        }
    }
}

From source file:com.wso2telco.dep.mediator.dao.ProvisionDAO.java

public Integer provisionServiceEntry(String notifyURL, String serviceProvider) throws SQLException, Exception {

    Connection con = null;//  w  ww .  j  av a2 s.  c o  m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer newId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        if (con == null) {

            throw new Exception("Connection not found");
        }

        StringBuilder insertQueryString = new StringBuilder("INSERT INTO ");
        insertQueryString.append(DatabaseTables.PROVISION_SERVICE_ENTRY.getTableName());
        insertQueryString.append(" (notifyurl, service_provider, is_active) ");
        insertQueryString.append("VALUES (?, ?, ?)");

        ps = con.prepareStatement(insertQueryString.toString(), Statement.RETURN_GENERATED_KEYS);

        ps.setString(1, notifyURL);
        ps.setString(2, serviceProvider);
        ps.setInt(3, 0);

        log.debug("sql query in provisionServiceEntry : " + ps);

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            newId = rs.getInt(1);
        }
    } catch (SQLException e) {

        log.error("database operation error in provisionServiceEntry : ", e);
        throw e;
    } catch (Exception e) {

        log.error("error in provisionServiceEntry : ", e);
        throw e;
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return newId;
}

From source file:org.wso2.carbon.identity.openidconnect.dao.RequestObjectDAOImpl.java

/**
 * Store request object related data into related db tables.
 *
 * @param consumerKey    consumer key/*w w w  .  j a v a  2s .co m*/
 * @param sessionDataKey session data key
 * @param claims         request object claims
 * @throws IdentityOAuth2Exception
 */
@Override
public void insertRequestObjectData(String consumerKey, String sessionDataKey,
        List<List<RequestedClaim>> claims) throws IdentityOAuth2Exception {

    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    String sqlStmt = SQLQueries.STORE_IDN_OIDC_REQ_OBJECT_REFERENCE;
    Connection connection = null;
    try {
        connection = IdentityDatabaseUtil.getDBConnection();
        String dbProductName = connection.getMetaData().getDatabaseProductName();
        prepStmt = connection.prepareStatement(sqlStmt,
                new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, ID) });
        prepStmt.setString(1, consumerKey);
        prepStmt.setString(2, sessionDataKey);
        prepStmt.executeUpdate();
        int requestObjectId = -1;
        rs = prepStmt.getGeneratedKeys();
        if (rs.next()) {
            requestObjectId = rs.getInt(1);
        } else {
            log.warn("Unable to persist Request Object reference for : " + sessionDataKey);
        }
        connection.commit();
        if (requestObjectId != -1) {
            if (log.isDebugEnabled()) {
                log.debug("Successfully stored the Request Object reference: " + requestObjectId + " for "
                        + "sessionDataKey: " + sessionDataKey);
            }
            if (CollectionUtils.isNotEmpty(claims)) {
                insertRequestObjectClaims(requestObjectId, claims, connection);
            }
        }
    } catch (SQLException e) {

        String errorMessage = "Error when storing the request object reference";
        log.error(errorMessage, e);
        throw new IdentityOAuth2Exception(errorMessage, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, rs, prepStmt);
    }
}

From source file:nl.nn.adapterframework.jdbc.JdbcQuerySenderBase.java

protected ResultSet getReturnedColumns(String[] columns, PreparedStatement st) throws SQLException {
    return st.getGeneratedKeys();
}

From source file:org.bidtime.dbutils.QueryRunnerEx.java

/**
 * Executes the given INSERT SQL statement.
 * @param conn The connection to use for the query call.
 * @param closeConn True if the connection should be closed, false otherwise.
 * @param sql The SQL statement to execute.
 * @param rsh The handler used to create the result object from
 * the <code>ResultSet</code> of auto-generated keys.
 * @param params The query replacement parameters.
 * @return An object generated by the handler.
 * @throws SQLException If there are database or parameter errors.
 * @since 1.6/*  w w  w .j a  va2s  .c  o m*/
 */
private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
        throws SQLException {
    if (conn == null) {
        throw new SQLException("Null connection");
    }

    if (sql == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null SQL statement");
    }

    if (rsh == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null ResultSetHandler");
    }

    PreparedStatement stmt = null;
    long startTime = System.currentTimeMillis();
    T generatedKeys = null;
    try {
        stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setQueryTimeout(StmtParams.getInstance().getStmtUpdateTimeOut());
        this.fillStatement(stmt, params);
        stmt.executeUpdate();
        ResultSet resultSet = stmt.getGeneratedKeys();
        generatedKeys = rsh.handle(resultSet);
    } catch (SQLException e) {
        this.rethrow(e, sql, params);
    } finally {
        close(stmt);
        if (closeConn) {
            close(conn);
        }
        if (LogInsertSql.logInfoOrDebug()) {
            LogInsertSql.logFormatTimeNow(startTime, sql, params);
        }
    }
    return generatedKeys;
}

From source file:com.webpagebytes.cms.local.WPBLocalDataStoreDao.java

public <T> T addRecord(T object, String keyFieldName) throws SQLException, WPBSerializerException {
    Connection connection = getConnection();
    PreparedStatement preparedStatement = null;
    try {/*from   ww  w . j  av  a 2s . com*/
        Set<String> ignoreFields = new HashSet<String>();
        ignoreFields.add(keyFieldName);
        String sqlStatement = getSQLStringForInsert(object, ignoreFields);
        connection.setAutoCommit(true);
        preparedStatement = connection.prepareStatement(sqlStatement);
        buildStatementForInsertUpdate(object, ignoreFields, preparedStatement, connection);
        preparedStatement.execute();
        ResultSet resultKey = preparedStatement.getGeneratedKeys();
        if (resultKey.next()) {
            Long key = resultKey.getLong(1);
            setObjectProperty(object, keyFieldName, key);
        }
        return object;
    } catch (SQLException e) {
        throw e;
    } finally {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        connection.close();
    }
}

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

protected Long wrappedInsert(PreparedStatement st) throws SQLException {
    ResultSet rs = null;/*from ww  w  .j av  a  2 s  . c  o m*/

    try {
        st.executeUpdate();
        rs = st.getGeneratedKeys();

        if (rs.next()) {
            return Long.valueOf(rs.getLong(1));
        }

        return null;

    } finally {
        close(rs);
    }
}

From source file:org.forumj.dbextreme.db.dao.FJThreadDao.java

public void create(IFJThread thread, IFJPost post)
        throws IOException, DBException, SQLException, ConfigurationException {
    String createThreadQuery = getCreateThreadQuery();
    Connection conn = null;//from  w  w w . j  av  a 2 s  .c o  m
    PreparedStatement st = null;
    boolean error = true;
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        st = conn.prepareStatement(createThreadQuery, new String[] { "id" });
        st.setLong(1, thread.getAuthId());
        st.setString(2, thread.getHead());
        Date date = new Date();
        st.setDate(3, new java.sql.Date(date.getTime()));
        st.setDate(4, new java.sql.Date(date.getTime()));
        st.setString(5, thread.getNick());
        st.setInt(6, thread.getType().getType());
        st.executeUpdate();
        ResultSet idRs = st.getGeneratedKeys();
        if (idRs.next()) {
            Long threadId = idRs.getLong(1);
            thread.setId(threadId);
            post.setThreadId(threadId);
            post.getHead().setThreadId(threadId);
            post.getHead().setCreateTime(date.getTime());
            FJPostDao postDao = new FJPostDao();
            Long postId = postDao.create(post, conn, false);
            thread.setLastPostId(postId);
            thread.setLastPostAuthId(post.getHead().getAuth());
            thread.setLastPostTime(new Date(post.getHead().getCreateTime()));
            thread.setLastPostNick(post.getHead().getAuthor().getNick());
            update(thread, conn);
            if (thread instanceof FJQuestionThread) {
                FJQuestNodeDao answersDao = new FJQuestNodeDao();
                FJQuestionThread questionThread = (FJQuestionThread) thread;
                List<IQuestNode> answers = questionThread.getAnswers();
                QuestNode question = new QuestNode();
                question.setNode(questionThread.getQuestion());
                question.setGol(0);
                question.setHead(threadId);
                question.setNumb(0);
                question.setType(0);
                question.setUserId((long) 0);
                answersDao.create(question, conn);
                for (int answerIndex = 0; answerIndex < answers.size(); answerIndex++) {
                    IQuestNode answer = answers.get(answerIndex);
                    answer.setHead(threadId);
                    answersDao.create(answer, conn);
                }
            }
        } else {
            throw new DBException("Thread wasn't created");
        }
        error = false;
    } finally {
        writeFinally(conn, st, error);
    }
}

From source file:org.wso2.carbon.rssmanager.core.environment.dao.impl.DatabasePrivilegeTemplateDAOImpl.java

/**
 * @see DatabasePrivilegeTemplateDAO#addDatabasePrivilegeTemplate(org.wso2.carbon.rssmanager.core.dto.common.DatabasePrivilegeTemplate, int)
 *///from   ww  w .j av a  2 s . c o m
public void addDatabasePrivilegeTemplate(DatabasePrivilegeTemplate databasePrivilegeTemplate, int environmentId)
        throws RSSDAOException, RSSDatabaseConnectionException {
    Connection conn = null;
    PreparedStatement templateStatement = null;
    PreparedStatement templateEntryStatement = null;
    ResultSet result = null;
    int templateId;
    try {
        conn = getDataSourceConnection();//acquire data source connection
        conn.setAutoCommit(false);
        String insertTemplateQuery = "INSERT INTO RM_DB_PRIVILEGE_TEMPLATE(ENVIRONMENT_ID, NAME, TENANT_ID) VALUES(?,?,?)";
        templateStatement = conn.prepareStatement(insertTemplateQuery, Statement.RETURN_GENERATED_KEYS);
        templateStatement.setInt(1, environmentId);
        templateStatement.setString(2, databasePrivilegeTemplate.getName());
        templateStatement.setInt(3, databasePrivilegeTemplate.getTenantId());
        templateStatement.executeUpdate();
        //get inserted template id to be set as foreign key for template entry table
        result = templateStatement.getGeneratedKeys();
        if (result.next()) {
            templateId = result.getInt(1);
            DatabasePrivilegeTemplateEntry privilegeTemplateEntry = databasePrivilegeTemplate.getEntry();
            String insertTemplateEntryQuery = "INSERT INTO RM_DB_PRIVILEGE_TEMPLATE_ENTRY(TEMPLATE_ID, SELECT_PRIV, "
                    + "INSERT_PRIV, UPDATE_PRIV, DELETE_PRIV, CREATE_PRIV, DROP_PRIV, GRANT_PRIV, REFERENCES_PRIV, "
                    + "INDEX_PRIV, ALTER_PRIV, CREATE_TMP_TABLE_PRIV, LOCK_TABLES_PRIV, CREATE_VIEW_PRIV, SHOW_VIEW_PRIV, "
                    + "CREATE_ROUTINE_PRIV, ALTER_ROUTINE_PRIV, EXECUTE_PRIV, EVENT_PRIV, TRIGGER_PRIV) VALUES "
                    + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            templateEntryStatement = conn.prepareStatement(insertTemplateEntryQuery);
            templateEntryStatement.setInt(1, templateId);
            templateEntryStatement.setString(2, privilegeTemplateEntry.getSelectPriv());
            templateEntryStatement.setString(3, privilegeTemplateEntry.getInsertPriv());
            templateEntryStatement.setString(4, privilegeTemplateEntry.getUpdatePriv());
            templateEntryStatement.setString(5, privilegeTemplateEntry.getDeletePriv());
            templateEntryStatement.setString(6, privilegeTemplateEntry.getCreatePriv());
            templateEntryStatement.setString(7, privilegeTemplateEntry.getDropPriv());
            templateEntryStatement.setString(8, privilegeTemplateEntry.getGrantPriv());
            templateEntryStatement.setString(9, privilegeTemplateEntry.getReferencesPriv());
            templateEntryStatement.setString(10, privilegeTemplateEntry.getIndexPriv());
            templateEntryStatement.setString(11, privilegeTemplateEntry.getAlterPriv());
            templateEntryStatement.setString(12, privilegeTemplateEntry.getCreateTmpTablePriv());
            templateEntryStatement.setString(13, privilegeTemplateEntry.getLockTablesPriv());
            templateEntryStatement.setString(14, privilegeTemplateEntry.getCreateViewPriv());
            templateEntryStatement.setString(15, privilegeTemplateEntry.getShowViewPriv());
            templateEntryStatement.setString(16, privilegeTemplateEntry.getCreateRoutinePriv());
            templateEntryStatement.setString(17, privilegeTemplateEntry.getAlterRoutinePriv());
            templateEntryStatement.setString(18, privilegeTemplateEntry.getExecutePriv());
            templateEntryStatement.setString(19, privilegeTemplateEntry.getEventPriv());
            templateEntryStatement.setString(20, privilegeTemplateEntry.getTriggerPriv());
            templateEntryStatement.executeUpdate();
        }
        conn.commit();
    } catch (SQLException e) {
        RSSDAOUtil.rollback(conn, RSSManagerConstants.ADD_PRIVILEGE_TEMPLATE_ENTRY);
        String msg = "Failed to add database template" + databasePrivilegeTemplate.getName()
                + "to the metadata repository";
        handleException(msg, e);
    } finally {
        RSSDAOUtil.cleanupResources(null, templateEntryStatement, null,
                RSSManagerConstants.ADD_PRIVILEGE_TEMPLATE_ENTRY);
        RSSDAOUtil.cleanupResources(result, templateEntryStatement, conn,
                RSSManagerConstants.ADD_PRIVILEGE_TEMPLATE_ENTRY);
    }
}