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:org.wso2.carbon.rssmanager.core.dao.impl.UserDatabaseEntryDAOImpl.java

/**
 * @see UserDatabaseEntryDAO#addUserDatabaseEntry(java.sql.PreparedStatement, String, UserDatabaseEntry, int)
 *///from  w w w .  j a va2 s . co m
public int addUserDatabaseEntry(PreparedStatement nativeAttachUserStatement, String environmentName,
        UserDatabaseEntry entry, int tenantId) throws RSSDAOException, RSSDatabaseConnectionException {
    if (entry == null) {
        return -1;
    }
    Connection conn = null;
    PreparedStatement userEntryStatement = null;
    PreparedStatement userPrivilegeEntryStatement = null;
    ResultSet resultSet = null;
    int userEntryId = 0;
    try {
        conn = getDataSourceConnection();//acquire data source connection
        //start transaction with setting auto commit value to false
        conn.setAutoCommit(false);
        String userEntrySql = "INSERT INTO RM_USER_DATABASE_ENTRY(DATABASE_USER_ID, DATABASE_ID) VALUES (?,?)";
        userEntryStatement = conn.prepareStatement(userEntrySql, Statement.RETURN_GENERATED_KEYS);
        userEntryStatement.setInt(1, entry.getUserId());
        userEntryStatement.setInt(2, entry.getDatabaseId());
        userEntryStatement.executeUpdate();
        //get the result of the id inserted to the database user entry table which needs to be inserted to
        //user privilege table as a foreign key
        resultSet = userEntryStatement.getGeneratedKeys();
        while (resultSet.next()) {
            userEntryId = resultSet.getInt(1);
            UserDatabasePrivilege privileges = entry.getUserPrivileges();
            String insertTemplateEntryQuery = "INSERT INTO RM_USER_DATABASE_PRIVILEGE(USER_DATABASE_ENTRY_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 "
                    + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            userPrivilegeEntryStatement = conn.prepareStatement(insertTemplateEntryQuery);
            //set data to be inserted
            userPrivilegeEntryStatement.setInt(1, userEntryId);
            userPrivilegeEntryStatement.setString(2, privileges.getSelectPriv());
            userPrivilegeEntryStatement.setString(3, privileges.getInsertPriv());
            userPrivilegeEntryStatement.setString(4, privileges.getUpdatePriv());
            userPrivilegeEntryStatement.setString(5, privileges.getDeletePriv());
            userPrivilegeEntryStatement.setString(6, privileges.getCreatePriv());
            userPrivilegeEntryStatement.setString(7, privileges.getDropPriv());
            userPrivilegeEntryStatement.setString(8, privileges.getGrantPriv());
            userPrivilegeEntryStatement.setString(9, privileges.getReferencesPriv());
            userPrivilegeEntryStatement.setString(10, privileges.getIndexPriv());
            userPrivilegeEntryStatement.setString(11, privileges.getAlterPriv());
            userPrivilegeEntryStatement.setString(12, privileges.getCreateTmpTablePriv());
            userPrivilegeEntryStatement.setString(13, privileges.getLockTablesPriv());
            userPrivilegeEntryStatement.setString(14, privileges.getCreateViewPriv());
            userPrivilegeEntryStatement.setString(15, privileges.getShowViewPriv());
            userPrivilegeEntryStatement.setString(16, privileges.getCreateRoutinePriv());
            userPrivilegeEntryStatement.setString(17, privileges.getAlterRoutinePriv());
            userPrivilegeEntryStatement.setString(18, privileges.getExecutePriv());
            userPrivilegeEntryStatement.setString(19, privileges.getEventPriv());
            userPrivilegeEntryStatement.setString(20, privileges.getTriggerPriv());
            userPrivilegeEntryStatement.executeUpdate();
        }
        //native user attachment to database statement is not transactional since it will executed after entry is insert
        //user entry to meta repository
        if (nativeAttachUserStatement != null) {
            nativeAttachUserStatement.executeUpdate();
        }
        conn.commit();
    } catch (SQLException e) {
        RSSDAOUtil.rollback(conn, RSSManagerConstants.ADD_USER_PRIVILEGE_TEMPLATE_ENTRY);
        String msg = "Failed to add database user entry to meta repository";
        handleException(msg, e);
    } finally {
        RSSDAOUtil.cleanupResources(null, userPrivilegeEntryStatement, null,
                RSSManagerConstants.ADD_USER_PRIVILEGE_TEMPLATE_ENTRY);
        RSSDAOUtil.cleanupResources(resultSet, userEntryStatement, conn,
                RSSManagerConstants.ADD_USER_PRIVILEGE_TEMPLATE_ENTRY);
    }
    return userEntryId;
}

From source file:Classes.Database.java

/**
 * Makes a save SQL statement and executes it
 *
 * @param sql       The query, use an "?" at the place of a input. Like this:
 *                  INSERT INTO TABLE('name', 'lastname' , enz ) VALUES(?,?, enz);
 * @param arguments The arguments correspont to same questionmark.
 * @return The generated key//  w w  w .j  a v a 2  s. c  om
 * @throws SQLException
 */
public Integer setDatabase(String sql, Object... arguments) {
    Connection conn = null;
    PreparedStatement psta = null;
    ResultSet rs = null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, username, password);
        psta = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        EscapeSQL(psta, arguments);

        psta.executeUpdate();
        rs = psta.getGeneratedKeys();
        if (rs != null && rs.next()) {
            if (rs.getInt(1) == 0) { //maybe errors
                return -1;
            }
            return rs.getInt(1);
        }
        return -1;
    } catch (SQLException e) {
        Logger.getAnonymousLogger().log(Level.WARNING, "SQL Error: " + e.getMessage(), e);
        return -1;
    } catch (ClassNotFoundException e) {
        Logger.getAnonymousLogger().log(Level.WARNING, "Class Error " + e.getMessage(), e);
        return -1;
    } finally {
        if (conn != null) {
            //close and commit
            Logger.getAnonymousLogger().log(Level.INFO, "Commit" + sql);
            try {
                conn.commit();
            } catch (SQLException e) {
                Logger.getAnonymousLogger().log(Level.WARNING, e.getMessage(), e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                Logger.getAnonymousLogger().log(Level.WARNING, e.getMessage(), e);
            }
        }

        if (psta != null) {
            try {
                psta.close();
            } catch (SQLException e) {
                Logger.getAnonymousLogger().log(Level.WARNING, e.getMessage(), e);
            }
        }

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                Logger.getAnonymousLogger().log(Level.WARNING, e.getMessage(), e);
            }
        }
    }
}

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

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

    PreparedStatement preparedStatement = null;
    int applicationId = 0;
    ResultSet resultSet = null;

    try {

        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.getCarbonApplicationName());
        preparedStatement.setString(7, application.getApplicationType());

        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 = null;
        if (application.getIcon() != null) {
            iconInputStream = IOUtils.toBufferedInputStream(application.getIcon().getBinaryStream());
        }
        updateApplicationIcon(dbConnection, iconInputStream, applicationId);

    } catch (SQLException e) {

        String msg = "Error occurred while adding application : " + application.getApplicationName()
                + " to database " + "in tenant : " + tenantId;
        log.error(msg, e);
        throw new IntCloudException(msg, e);

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

}

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

/**
 * Ask the database for next auto increment
 * //  ww  w . j a v  a 2 s.  c  o  m
 * @throws SQLException
 */
private Long doInsert(String table, String key) throws SQLException {
    Long res = null;

    final Connection connection = wrpr.getConnection();
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
        String query = "INSERT INTO " + table + " (value) VALUES (?)";

        prep = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

        prep.setString(1, key);

        prep.executeUpdate();

        rs = prep.getGeneratedKeys();
        rs.first();
        res = Long.valueOf(rs.getLong(1));
    } catch (SQLException se) {
        log.error(se.getMessage());
    } finally {
        close(rs);
        close(prep);
        close(connection);
    }

    return res;
}

From source file:com.nortal.petit.orm.statement.InsertStatement.java

@Override
public void exec() {
    prepare();/*from   w  w w.  j  a  v  a  2  s. c o m*/
    if (!CollectionUtils.isEmpty(getBeans())) {
        if (getMapping().id() == null) {
            execBatchUpdate();
        } else {
            final KeyHolder keyHolder = new GeneratedKeyHolder();
            final InterceptorCalls interceptorCalls = new InterceptorCalls();
            getJdbcTemplate().execute(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                    return con.prepareStatement(getSql(), Statement.RETURN_GENERATED_KEYS);
                }
            }, new PreparedStatementCallback<Object>() {
                @Override
                public Object doInPreparedStatement(PreparedStatement ps)
                        throws SQLException, DataAccessException {
                    MappingParamFunction<B> paramFunction = new MappingParamFunction<B>(getMapping());

                    for (B bean : getBeans()) {
                        paramFunction.setBean(bean);
                        Object[] params = getParams(paramFunction);
                        Object[] queryParams = params.length == 1 && params[0] instanceof Object[]
                                ? (Object[]) params[0]
                                : params;
                        interceptorCalls.setBeanValues(bean, queryParams);
                        ArgPreparedStatementSetter.setValues(ps, queryParams, 1);

                        ps.executeUpdate();
                        extractKeys(ps);
                    }
                    return null;
                }

                /**
                 * @param ps
                 * @throws SQLException
                 */
                private void extractKeys(PreparedStatement ps) throws SQLException {
                    ResultSet keys = ps.getGeneratedKeys();
                    if (keys != null) {
                        try {
                            RowMapperResultSetExtractor<Map<String, Object>> rse = new RowMapperResultSetExtractor<Map<String, Object>>(
                                    new ColumnMapRowMapper(), 1);
                            keyHolder.getKeyList().addAll(rse.extractData(keys));
                        } finally {
                            JdbcUtils.closeResultSet(keys);
                        }
                    }
                }
            });

            try {
                Property<B, Object> idProperty = getMapping().id();
                for (int i = 0; i < getBeans().size(); i++) {
                    B bean = getBeans().get(i);
                    Object key = keyHolder.getKeyList().get(i).get(idProperty.column());
                    idProperty.write(bean, key);
                    interceptorCalls.setBeanId(bean, key);
                }
            } catch (Exception e) {
                throw new PersistenceException("InsertStatement.exec: unable to write bean primary key", e);
            }
            interceptorCalls.callInterceptor();
        }
    } else {
        getJdbcTemplate().update(getSql(), getParams(null));
    }
}

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

/**
 * Ussd request entry.//  w  ww.  jav a  2  s  .  co  m
 *
 * @param notifyURL
 *            the notifyURL
 * @return the integer
 * @throws Exception
 *             the exception
 */
public Integer ussdRequestEntry(String notifyURL, String consumerKey, String operatorId, String userId)
        throws SQLException, Exception {

    Connection con = null;
    PreparedStatement insert_statement = null;
    PreparedStatement select_statement = null;
    ResultSet insert_result = null;
    ResultSet select_result = null;
    Integer selectId = 0;
    Integer newId = 0;

    try {

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

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

        con.setAutoCommit(false);
        StringBuilder queryString = new StringBuilder(" SELECT MAX(ussd_request_did) maxid ");
        queryString.append("FROM ");
        queryString.append(DatabaseTables.USSD_REQUEST_ENTRY.getTableName());

        select_statement = con.prepareStatement(queryString.toString());
        select_result = select_statement.executeQuery(queryString.toString());

        if (select_result.next()) {
            selectId = select_result.getInt("maxid") + 1;
        }

        StringBuilder insertQueryString = new StringBuilder(" INSERT INTO ");
        insertQueryString.append(DatabaseTables.USSD_REQUEST_ENTRY.getTableName());
        insertQueryString.append(" (ussd_request_did,notifyurl,sp_consumerKey,operatorId,userId) ");
        insertQueryString.append("VALUES (?, ? ,? ,? ,? )");

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

        insert_statement.setInt(1, selectId);
        insert_statement.setString(2, notifyURL);
        insert_statement.setString(3, consumerKey);
        insert_statement.setString(4, operatorId);
        insert_statement.setString(5, userId);

        log.debug("sql query in ussdRequestEntry : " + insert_statement);

        insert_statement.executeUpdate();

        insert_result = insert_statement.getGeneratedKeys();

        while (insert_result.next()) {
            newId = insert_result.getInt(1);
        }

    } catch (SQLException e) {

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

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

        DbUtils.closeAllConnections(insert_statement, con, insert_result);
        DbUtils.closeAllConnections(select_statement, null, select_result);

    }

    return newId;
}

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

public void create(Address c, QueryRunner run, Connection conn, ResultSet generatedKeys) throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_INSERT_ADDRESS, Statement.RETURN_GENERATED_KEYS);
    st.setString(1, c.getCountry());//w ww  .j a  va2 s  .  c om
    st.setString(2, c.getCity());
    st.setString(3, c.getStreet());
    st.setString(4, c.getPostCode());
    st.setString(5, c.getHouseNumber());
    st.setString(6, c.getFlatNumber());
    st.setString(7, c.getNotes());
    st.setBoolean(8, c.isMain());
    if (c.getPerson_id().longValue() == 0 && c.getCompany_id().longValue() == 0) {
        throw new SQLException("For Address either Person or Company needs to be specified");
    }
    if (c.getPerson_id().longValue() != 0) {
        st.setLong(9, c.getPerson_id());
    } else {
        st.setNull(9, java.sql.Types.NUMERIC);
    }
    if (c.getCompany_id().longValue() != 0) {
        st.setLong(10, c.getCompany_id());
    } else {
        st.setNull(10, java.sql.Types.NUMERIC);
    }
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_INSERT_ADDRESS);
    }
    generatedKeys = st.getGeneratedKeys();
    if (generatedKeys.next()) {
        c.setId(generatedKeys.getLong(1));
    } else {
        throw new SQLException("Creating address failed, no generated key obtained.");
    }
}

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

/**
 * Create a new schedule./*from   w w w . j a  va  2  s  .  c  om*/
 *
 * @param schedule The schedule to create
 * @throws ArgumentNotValid if schedule is null
 * @throws PermissionDenied if a schedule already exists
 */
public synchronized void create(Schedule schedule) {
    ArgumentNotValid.checkNotNull(schedule, "schedule");

    Connection c = HarvestDBConnection.get();
    PreparedStatement s = null;
    try {
        if (exists(c, schedule.getName())) {
            String msg = "Cannot create already existing schedule " + schedule;
            log.debug(msg);
            throw new PermissionDenied(msg);
        }

        s = c.prepareStatement("INSERT INTO schedules " + "( name, comments, startdate, enddate, maxrepeats, "
                + "timeunit, numtimeunits, anytime, onminute, onhour," + " ondayofweek, ondayofmonth, edition )"
                + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )", Statement.RETURN_GENERATED_KEYS);
        setScheduleParameters(s, schedule);
        final long edition = 1;
        s.setLong(13, edition);
        s.executeUpdate();
        schedule.setID(DBUtils.getGeneratedID(s));
        schedule.setEdition(edition);
    } catch (SQLException e) {
        throw new IOFailure(
                "SQL error while creating schedule " + schedule + "\n" + ExceptionUtils.getSQLExceptionCause(e),
                e);
    } finally {
        HarvestDBConnection.release(c);
    }
}

From source file:com.bc.fiduceo.db.AbstractDriver.java

@Override
public int insert(Sensor sensor) throws SQLException {
    final PreparedStatement preparedStatement = connection
            .prepareStatement("INSERT INTO SENSOR VALUES(default, ?)", Statement.RETURN_GENERATED_KEYS);
    preparedStatement.setString(1, sensor.getName());
    preparedStatement.executeUpdate();/*from  w  ww  . j ava 2 s .c  om*/

    final ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
    if (generatedKeys.next()) {
        return generatedKeys.getInt(1);
    }
    return -1;
}

From source file:com.orangeandbronze.jblubble.jdbc.springframework.SpringJdbcBlobstoreService.java

@Override
public BlobKey createBlob(BlobstoreWriteCallback callback, String name, String contentType)
        throws IOException, BlobstoreException {
    try {/*from  w  w w  . ja v  a 2  s . c o  m*/
        return jdbcTemplate.execute(new ConnectionCallback<BlobKey>() {
            @Override
            public BlobKey doInConnection(Connection connection) throws SQLException, DataAccessException {
                try (PreparedStatement ps = connection.prepareStatement(getInsertSql(),
                        Statement.RETURN_GENERATED_KEYS)) {
                    ps.setString(1, name);
                    ps.setString(2, contentType);
                    Blob content = connection.createBlob();
                    try {
                        long size;
                        String md5Hash = null;
                        OutputStream out = new BufferedOutputStream(content.setBinaryStream(1L),
                                getBufferSize());
                        try {
                            CountingOutputStream countingOutputStream = new CountingOutputStream(out);
                            try {
                                MessageDigest md5;
                                try {
                                    md5 = MessageDigest.getInstance(MD5_ALGORITHM_NAME);
                                    try (DigestOutputStream digestOutputStream = new DigestOutputStream(
                                            countingOutputStream, md5)) {
                                        size = callback.writeToOutputStream(digestOutputStream);
                                        if (size == -1L) {
                                            size = countingOutputStream.getByteCount();
                                        }
                                        md5Hash = new String(encodeHex(md5.digest()));
                                    }
                                } catch (NoSuchAlgorithmException e) {
                                    throw new BlobstoreException(e);
                                }
                            } finally {
                                countingOutputStream.close();
                            }
                        } finally {
                            out.close();
                        }
                        ps.setBlob(3, content);
                        ps.setLong(4, size);
                        ps.setTimestamp(5, new java.sql.Timestamp(new java.util.Date().getTime()));
                        ps.setString(6, md5Hash);
                        int rowCount = ps.executeUpdate();
                        if (rowCount == 0) {
                            throw new BlobstoreException("Creating blob failed, no rows created.");
                        }
                        long generatedId = getGeneratedKey(ps);
                        return new BlobKey(String.valueOf(generatedId));
                    } finally {
                        content.free();
                    }
                } catch (IOException e) {
                    throw new BlobstoreException("Error when creating blob", e);
                }
            }
        });
    } catch (DataAccessException e) {
        throw new BlobstoreException(e);
    }
}