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:cz.muni.fi.javaseminar.kafa.bookregister.BookManagerImpl.java

@Override
@Transactional(readOnly = false)//  w  ww  .ja  va  2  s  .  co m
public void createBook(Book book) {
    validate(book);
    if (book.getId() != null) {
        throw new IllegalArgumentException("book id is already set");
    }

    KeyHolder keyHolder = new GeneratedKeyHolder();
    int updated = jdbcTemplate.update((Connection connection) -> {
        PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO BOOK (name,isbn,published,author_id) VALUES (?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, book.getName());
        ps.setString(2, book.getIsbn());
        Date date = Date.valueOf(book.getPublished());
        ps.setDate(3, date);
        if (book.getAuthorId() == null) {
            ps.setNull(4, Types.BIGINT);
        } else {
            ps.setLong(4, book.getAuthorId());
        }
        return ps;
    }, keyHolder);

    book.setId(keyHolder.getKey().longValue());

    DBUtils.checkUpdatesCount(updated, book, true);
}

From source file:com.gzj.tulip.jade.dataaccess.DataAccessImpl.java

private PreparedStatementCreator getPreparedStatementCreator(//
        final String sql, final Object[] args, final boolean returnKeys) {
    PreparedStatementCreator creator = new PreparedStatementCreator() {

        @Override/*from w w w. ja v  a2 s  . c o m*/
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement(sql);
            if (returnKeys) {
                ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                ps = con.prepareStatement(sql);
            }

            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    Object arg = args[i];
                    if (arg instanceof SqlParameterValue) {
                        SqlParameterValue paramValue = (SqlParameterValue) arg;
                        StatementCreatorUtils.setParameterValue(ps, i + 1, paramValue, paramValue.getValue());
                    } else {
                        StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, arg);
                    }
                }
            }
            return ps;
        }
    };
    return creator;
}

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   w ww  .  ja v a2 s  .  co  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);
    }
}

From source file:org.biblionum.ouvrage.modele.OuvrageModele.java

/**
 * Java method that inserts a row in the generated sql table and returns the
 * new generated id//from w w w.  j  av a2s.c  om
 *
 * @param con (open java.sql.Connection)
 * @param auteur
 * @param editeur
 * @param annee_edition
 * @param resume
 * @param nb_page
 * @param emplacement
 * @param couverture
 * @param ouvrageTipeid
 * @param categorieOuvrageid
 * @param niveauid_niveau
 * @param filiereid
 * @param titre
 * @return id (database row id [id])
 * @throws SQLException
 */
public int insertIntoUtilisateur(DataSource ds, String auteur, String editeur, int annee_edition, String resume,
        int nb_page, String emplacement, String couverture, int ouvrageTipeid, int categorieOuvrageid,
        int niveauid_niveau, int filiereid, String titre) throws SQLException {
    con = ds.getConnection();
    int generatedId = -1;
    String sql = "INSERT INTO ouvrage (auteur, editeur, annee_edition, resume, nb_page, emplacement, "
            + "couverture, ouvrageTipeid, categorieOuvrageid, niveauid_niveau, filiereid, titre" + ")"
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    PreparedStatement statement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    statement.setString(1, auteur);
    statement.setString(2, editeur);
    statement.setInt(3, annee_edition);
    statement.setString(4, resume);
    statement.setInt(5, nb_page);
    statement.setString(6, emplacement);
    statement.setString(7, couverture);
    statement.setInt(8, ouvrageTipeid);
    statement.setInt(9, categorieOuvrageid);
    statement.setInt(10, niveauid_niveau);
    statement.setInt(11, filiereid);
    statement.setString(12, titre);
    statement.execute();
    ResultSet auto = statement.getGeneratedKeys();

    if (auto.next()) {
        generatedId = auto.getInt(1);
    } else {
        generatedId = -1;
    }

    statement.close();
    con.close();
    return generatedId;
}

From source file:com.hs.mail.imap.dao.MySqlUserDao.java

public long addUser(final User user) {
    final String sql = "INSERT INTO user (userid, passwd, maxmail_size, forward) VALUES(?, ?, ?, ?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    getJdbcTemplate().update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, user.getUserID());
            pstmt.setString(2, user.getPassword());
            pstmt.setLong(3, user.getQuota());
            pstmt.setString(4, user.getForwardTo());
            return pstmt;
        }//from ww w  . ja  va 2  s . c o m
    }, keyHolder);
    long id = keyHolder.getKey().longValue();
    user.setID(id);
    return id;
}

From source file:uk.ac.cam.cl.dtg.isaac.dos.eventbookings.PgEventBookings.java

@Override
public EventBooking add(final String eventId, final Long userId, final BookingStatus status,
        Map<String, String> additionalEventInformation) throws SegueDatabaseException {
    PreparedStatement pst;/*from   w  w  w  .  j  ava2 s . co  m*/

    if (null == additionalEventInformation) {
        additionalEventInformation = Maps.newHashMap();
    }

    try (Connection conn = ds.getDatabaseConnection()) {
        Date creationDate = new Date();
        pst = conn.prepareStatement(
                "INSERT INTO event_bookings (id, user_id, event_id, status, created, updated, additional_booking_information) VALUES (DEFAULT, ?, ?, ?, ?, ?, ?::text::jsonb)",
                Statement.RETURN_GENERATED_KEYS);
        pst.setLong(1, userId);
        pst.setString(2, eventId);
        pst.setString(3, status.name());
        pst.setTimestamp(4, new java.sql.Timestamp(creationDate.getTime()));
        pst.setTimestamp(5, new java.sql.Timestamp(creationDate.getTime()));
        pst.setString(6, objectMapper.writeValueAsString(additionalEventInformation));
        if (pst.executeUpdate() == 0) {
            throw new SegueDatabaseException("Unable to save event booking.");
        }

        try (ResultSet generatedKeys = pst.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                Long id = generatedKeys.getLong(1);
                return new PgEventBooking(ds, id, userId, eventId, status, creationDate, creationDate,
                        additionalEventInformation);
            } else {
                throw new SQLException("Creating event booking failed, no ID obtained.");
            }
        }

    } catch (SQLException e) {
        throw new SegueDatabaseException("Postgres exception", e);
    } catch (JsonProcessingException e) {
        throw new SegueDatabaseException("Unable to convert json to string for persistence.", e);
    }
}

From source file:com.orangeandbronze.jblubble.jdbc.PgJdbcBlobstoreService.java

@Override
public BlobKey createBlob(BlobstoreWriteCallback callback, String name, String contentType)
        throws IOException, BlobstoreException {
    boolean resetCommitMode = false;
    try (Connection connection = dataSource.getConnection()) {
        if (connection.getAutoCommit()) {
            connection.setAutoCommit(false);
            resetCommitMode = true;//from   w  ww .j av  a2  s .  c  o m
        }
        try {
            int rowCount;
            try (PreparedStatement ps = connection.prepareStatement(getInsertSql(),
                    Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, name);
                ps.setString(2, contentType);
                ps.setTimestamp(3, new java.sql.Timestamp(new java.util.Date().getTime()));
                rowCount = ps.executeUpdate();
                if (rowCount == 0) {
                    throw new BlobstoreException("Creating blob failed, no rows created.");
                }
                final long generatedId = getGeneratedKey(ps);
                long size;
                String md5Hash = null;
                try (PreparedStatement ps2 = connection.prepareStatement(getSelectContentByIdSql())) {
                    ps2.setLong(1, generatedId);
                    ResultSet rs = ps2.executeQuery();
                    if (!rs.next()) {
                        throw new BlobstoreException("Creating blob failed, no rows created.");
                    }
                    Blob contentBlob = rs.getBlob(1);
                    try {
                        OutputStream out = new BufferedOutputStream(contentBlob.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 {
                                try {
                                    countingOutputStream.close();
                                } catch (IOException e) {
                                    // Since digestOutputStream gets closed,
                                    // the wrapped countingOutputStream does
                                    // not really need to get closed again.
                                }
                            }
                        } finally {
                            try {
                                out.close();
                            } catch (IOException e) {
                                // Since digestOutputStream gets closed,
                                // the wrapped buffered OutputStream does
                                // not really need to get closed again.
                            }
                        }
                    } finally {
                        contentBlob.free();
                    }
                }
                try (PreparedStatement ps3 = connection.prepareStatement(getUpdateSizeSql())) {
                    ps3.setLong(1, size);
                    ps3.setString(2, md5Hash);
                    ps3.setLong(3, generatedId);
                    rowCount = ps3.executeUpdate();
                    if (rowCount == 0) {
                        throw new BlobstoreException("Creating blob failed, no rows created.");
                    }
                }
                if (resetCommitMode) {
                    connection.commit();
                }
                return new BlobKey(String.valueOf(generatedId));
            }
        } catch (Exception e) {
            connection.rollback();
            throw e;
        } finally {
            if (resetCommitMode) {
                connection.setAutoCommit(true);
            }
        }
    } catch (SQLException e) {
        throw new BlobstoreException("Error when creating blob", e);
    }
}

From source file:org.wso2.carbon.device.mgt.core.dao.impl.DeviceTypeSensorDAOImpl.java

@Override
public void addDeviceTypeSensor(DeviceTypeSensorTransactionObject deviceTypeSensorTransactionObject)
        throws DeviceManagementDAOException {
    Connection conn;//from   w w w  .jav a2  s  . co  m
    ResultSet rs;
    PreparedStatement stmt = null;

    int deviceTypeSensorId;
    int deviceTypeId = deviceTypeSensorTransactionObject.getDeviceTypeId();
    DeviceTypeSensor deviceTypeSensor = deviceTypeSensorTransactionObject.getDeviceTypeSensor();
    String deviceTypeSensorName = deviceTypeSensor.getUniqueSensorName();
    String deviceTypeSensorTypeTAG = deviceTypeSensor.getSensorTypeTAG();
    Map<String, String> deviceTypeSensorProperties = deviceTypeSensor.getStaticProperties();

    try {
        conn = this.getConnection();
        String insertDBQuery = "INSERT INTO DM_DEVICE_TYPE_SENSOR (" + "SENSOR_NAME," + "DEVICE_TYPE_ID,"
                + "DESCRIPTION," + "SENSOR_TYPE," + "STREAM_DEFINITION) " + "VALUES (?,?,?,?,?)";

        stmt = conn.prepareStatement(insertDBQuery, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, deviceTypeSensorName);
        stmt.setInt(2, deviceTypeId);
        stmt.setString(3, deviceTypeSensor.getDescription());
        stmt.setString(4, deviceTypeSensorTypeTAG);
        stmt.setString(5, deviceTypeSensor.getStreamDefinition());
        int rows = stmt.executeUpdate();

        if (rows > 0) {
            if (log.isDebugEnabled()) {
                log.debug("Details of DeviceTypeSensor [" + deviceTypeSensorName + "] of "
                        + "DeviceType with Id [" + deviceTypeId + "] was added successfully.");
            }

            rs = stmt.getGeneratedKeys();
            rs.next();
            deviceTypeSensorId = rs.getInt(1);

            if (deviceTypeSensorProperties != null
                    && !addDeviceTypeSensorProperties(deviceTypeSensorId, deviceTypeSensorProperties)) {
                String msg = "Error occurred whilst adding Properties of the registered new DeviceTypeSensor "
                        + "[" + deviceTypeSensorName + "] whose Id is [" + deviceTypeSensorId + "]";
                log.error(msg);
                throw new DeviceManagementDAOException(msg);
            }
        }
    } catch (SQLException e) {
        String msg = "Error occurred whilst registering a new DeviceTypeSensor [" + deviceTypeSensorName
                + "] for the DeviceType with Id [" + deviceTypeId + "]";
        log.error(msg, e);
        throw new DeviceManagementDAOException(msg, e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
}

From source file:com.microsoftopentechnologies.azchat.web.dao.UserPreferenceDAOImpl.java

/**
 * This method executes add user preference query on azure SQL user
 * preference table./*from  www.j  a  v a  2  s.  c o m*/
 * 
 * @param userPreferenceEntity
 * @return
 * @throws Exception
 * @author Rupesh_Shirude
 */
@Override
public UserPreferenceEntity addUserPreferenceEntity(UserPreferenceEntity userPreferenceEntity)
        throws Exception {
    LOGGER.info("[UserPreferenceEntity][addUserPreferenceEntity] start ");
    int userPreferenceId = 0;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
        preparedStatement = connection.prepareStatement(AzureChatSQLConstants.ADD_USER_PREFERENCE,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement = generatePreparedStatement(preparedStatement, userPreferenceEntity);
        preparedStatement.executeUpdate();
        resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()) {
            userPreferenceId = resultSet.getInt(1);
        }
    } catch (SQLException e) {
        LOGGER.error(
                "Exception occurred while executing add user preference query on azure SQL user preference table. Exception Message  : "
                        + e.getMessage());
        throw new AzureChatSystemException(
                "Exception occurred while executing add user preference query on azure SQL user preference table. Exception Message  : "
                        + e.getMessage());
    } finally {
        AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet, connection);
    }
    userPreferenceEntity.setUserPreferenceID(userPreferenceId);
    LOGGER.info("[UserPreferenceEntity][addUserPreferenceEntity] end ");
    return userPreferenceEntity;
}

From source file:com.nabla.wapp.server.auth.UserManager.java

public boolean initializeDatabase(final IRoleListProvider roleListProvider, final String rootPassword)
        throws SQLException {
    Assert.argumentNotNull(roleListProvider);

    final LockTableGuard lock = new LockTableGuard(conn, LOCK_USER_TABLES);
    try {/*from  w w  w  . j ava  2s  .  c  om*/
        if (!Database.isTableEmpty(conn, IRoleTable.TABLE))
            return true;
        if (log.isDebugEnabled())
            log.debug("initializing role tables");
        final Map<String, String[]> roles = roleListProvider.get();
        Assert.state(!roles.containsKey(IRootUser.NAME));
        final ConnectionTransactionGuard guard = new ConnectionTransactionGuard(conn);
        try {
            final PreparedStatement stmtRole = conn.prepareStatement(
                    "INSERT INTO role (name,uname,privilege,internal) VALUES(?,?,?,?);",
                    Statement.RETURN_GENERATED_KEYS);
            final Map<String, Integer> roleIds = new HashMap<String, Integer>();
            try {
                stmtRole.clearBatch();
                stmtRole.setBoolean(4, true);
                // add privileges and default roles
                for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                    stmtRole.setString(1, role.getKey());
                    stmtRole.setString(2, role.getKey().toUpperCase());
                    stmtRole.setBoolean(3, role.getValue() == null);
                    stmtRole.addBatch();
                }
                if (!Database.isBatchCompleted(stmtRole.executeBatch()))
                    return false;
                final ResultSet rsKey = stmtRole.getGeneratedKeys();
                try {
                    for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                        rsKey.next();
                        roleIds.put(role.getKey(), rsKey.getInt(1));
                    }
                } finally {
                    rsKey.close();
                }
            } finally {
                stmtRole.close();
            }
            final PreparedStatement stmtDefinition = conn
                    .prepareStatement("INSERT INTO role_definition (role_id,child_role_id) VALUES(?,?);");
            try {
                stmtDefinition.clearBatch();
                for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                    final String[] definition = role.getValue();
                    if (definition == null)
                        continue;
                    stmtDefinition.setInt(1, roleIds.get(role.getKey()));
                    for (final String child : definition) {
                        final Integer childId = roleIds.get(child);
                        if (childId == null) {
                            if (log.isErrorEnabled())
                                log.error("child role '" + child + "' not defined!");
                            return false;
                        }
                        stmtDefinition.setInt(2, childId);
                        stmtDefinition.addBatch();
                    }
                }
                if (!Database.isBatchCompleted(stmtDefinition.executeBatch()))
                    return false;
            } finally {
                stmtDefinition.close();
            }
            // add 'root' user
            Database.executeUpdate(conn, "INSERT INTO user (name,uname,active,password) VALUES(?,?,TRUE,?);",
                    IRootUser.NAME, IRootUser.NAME.toUpperCase(),
                    getPasswordEncryptor().encryptPassword(rootPassword));
            return guard.setSuccess(true);
        } finally {
            guard.close();
        }
    } finally {
        lock.close();
    }
}