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:edu.pitt.sis.infsci2730.finalProject.dao.TransactionDao.java

public static TransactionDBModel InsertTransactionByID(final String[] para) throws SQLException {

    KeyHolder holder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

        String sql = "insert into Transactions (transaction_date, customer_id) "
                + "values (CURRENT_TIMESTAMP,?)";

        @Override/* www .ja  v a2s . c  om*/
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, para[0]);

            return ps;
        }
    }, holder);

    int newId = holder.getKey().intValue();

    return jdbcTemplate.queryForObject("select * from Transactions where TRANSACTION_ID=" + newId,
            new TransactionRowMapper());
}

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

/**
 * This method executes adds new user query on the azure SQL user table.
 * /*from   w  w  w .j a  va2s  .  c om*/
 * @return userEntity
 */
public UserEntity saveNewUser(UserEntity user) throws Exception {
    LOGGER.info("[UserDAOImpl][saveNewUser] start ");
    int userId = 0;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
        preparedStatement = connection.prepareStatement(AzureChatSQLConstants.SAVE_NEW_USER,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement = generatePreparedStatement(preparedStatement, user);
        preparedStatement.executeUpdate();
        resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()) {
            userId = resultSet.getInt(1);
        }
    } catch (Exception e) {
        LOGGER.error(
                "Exception occurred while executing save user query on azure SQL table. Exception Message : "
                        + e.getMessage());
        throw new AzureChatSystemException(
                "Exception occurred while executing save user query on azure SQL table. Exception Message : "
                        + e.getMessage());
    } finally {
        AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet, connection);
    }
    user.setUserID(userId);
    LOGGER.info("[UserDAOImpl][saveNewUser] end ");
    return user;
}

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

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

    Connection con = null;/*from  ww w  . j  a v  a  2  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.OUTBOUND_SUBSCRIPTIONS.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 outboundSubscriptionEntry : " + ps);

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

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

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

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

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

    return newId;
}

From source file:org.mskcc.cbio.portal.dao.DaoPatient.java

public static int addPatient(Patient patient) throws DaoException {
    Connection con = null;//from ww  w  . j  a  v a  2s . c o  m
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = JdbcUtil.getDbConnection(DaoPatient.class);
        pstmt = con.prepareStatement("INSERT INTO patient (`STABLE_ID`, `CANCER_STUDY_ID`) VALUES (?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, patient.getStableId());
        pstmt.setInt(2, patient.getCancerStudy().getInternalId());
        pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            cachePatient(new Patient(patient.getCancerStudy(), patient.getStableId(), rs.getInt(1)),
                    patient.getCancerStudy().getInternalId());
            return rs.getInt(1);
        }
        return -1;
    } catch (SQLException e) {
        throw new DaoException(e);
    } finally {
        JdbcUtil.closeAll(DaoPatient.class, con, pstmt, rs);
    }
}

From source file:cz.muni.fi.javaseminar.kafa.bookregister.AuthorManagerImpl.java

@Override
@Transactional(readOnly = false)/* ww w  . j  a  v  a2s . c  o  m*/
public void createAuthor(Author author) {
    validate(author);
    if (author.getId() != null) {
        throw new IllegalArgumentException("author id is already set");
    }

    KeyHolder keyHolder = new GeneratedKeyHolder();
    int updated = jdbcTemplate.update((Connection connection) -> {
        PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO AUTHOR (firstname,surname,description,nationality,dateofbirth) VALUES (?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, author.getFirstname());
        ps.setString(2, author.getSurname());
        ps.setString(3, author.getDescription());
        ps.setString(4, author.getNationality());

        Date date = Date.valueOf(author.getDateOfBirth());
        ps.setDate(5, date);

        return ps;
    }, keyHolder);

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

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

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

/**
 * This method adds the user preference data in the azure SQL table.
 * //w w w  . j av a 2 s.  co  m
 * @return preferenceMetadataEntity
 * @throws SQLException
 */
@Override
public PreferenceMetadataEntity addPreferenceMetadataEntity(PreferenceMetadataEntity preferenceMetadataEntity)
        throws Exception {
    LOGGER.info("[PreferenceMetadataEntity][addPreferenceMetadataEntity]         start ");
    LOGGER.debug("PreferenceMetadataEntity    " + preferenceMetadataEntity.getPreferenceDesc());
    int prefMetadataId = 0;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
        preparedStatement = connection.prepareStatement(AzureChatSQLConstants.ADD_PREFERENCE_METADATA,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement = generatePreparedStatement(preparedStatement, preferenceMetadataEntity);
        preparedStatement.executeUpdate();
        resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()) {
            prefMetadataId = resultSet.getInt(1);
        }
    } catch (Exception e) {
        LOGGER.error(
                "Exception occurred while adding preference metadata entity to the azure SQL table. Exception Message : "
                        + e.getMessage());
        throw new AzureChatSystemException(
                "Exception occurred while adding preference metadata entity to the azure SQL table. Exception Message : "
                        + e.getMessage());
    } finally {
        AzureChatUtils.closeDatabaseResources(preparedStatement, resultSet);
    }
    preferenceMetadataEntity.setPreferenceId(prefMetadataId);
    LOGGER.info("[PreferenceMetadataEntity][addPreferenceMetadataEntity]         end ");
    return preferenceMetadataEntity;
}

From source file:com.nabla.wapp.server.basic.general.ImportService.java

@Override
public String executeAction(final HttpServletRequest request, final List<FileItem> sessionFiles)
        throws UploadActionException {
    final UserSession userSession = UserSession.load(request);
    if (userSession == null) {
        if (log.isTraceEnabled())
            log.trace("missing user session");
        throw new UploadActionException("permission denied");
    }//from   w ww.  j  av a 2s  .  c o  m
    Assert.state(sessionFiles.size() == 1);
    try {
        for (FileItem file : sessionFiles) {
            if (file.isFormField())
                continue;
            if (log.isDebugEnabled()) {
                log.debug("field '" + file.getFieldName() + "': uploading " + file.getName());
                log.debug("field: " + file.getFieldName());
                log.debug("filename: " + file.getName());
                log.debug("content_type: " + file.getContentType());
                log.debug("size: " + file.getSize());
            }
            final Connection conn = db.getConnection();
            try {
                final PreparedStatement stmt = conn.prepareStatement(
                        "INSERT INTO import_data (field_name, file_name, content_type, length, content, userSessionId) VALUES(?,?,?,?,?,?);",
                        Statement.RETURN_GENERATED_KEYS);
                try {
                    stmt.setString(1, file.getFieldName());
                    stmt.setString(2, file.getName());
                    stmt.setString(3, file.getContentType());
                    stmt.setLong(4, file.getSize());
                    stmt.setString(6, userSession.getSessionId());
                    final InputStream fs = file.getInputStream();
                    try {
                        stmt.setBinaryStream(5, fs);
                        if (stmt.executeUpdate() != 1) {
                            if (log.isErrorEnabled())
                                log.error("failed to add imported file record");
                            throw new UploadActionException("internal error");
                        }
                        final ResultSet rsKey = stmt.getGeneratedKeys();
                        try {
                            rsKey.next();
                            final Integer id = rsKey.getInt(1);
                            if (log.isDebugEnabled())
                                log.debug(
                                        "uploading " + file.getName() + " successfully completed. id = " + id);
                            return id.toString();
                        } finally {
                            rsKey.close();
                        }
                    } finally {
                        fs.close();
                    }
                } catch (IOException e) {
                    if (log.isErrorEnabled())
                        log.error("error reading file " + file.getName(), e);
                    throw new UploadActionException("internal error");
                } finally {
                    Database.close(stmt);
                }
            } finally {
                // remove any orphan import records i.e. older than 48h (beware of timezone!)
                final Calendar dt = Util.dateToCalendar(new Date());
                dt.add(GregorianCalendar.DATE, -2);
                try {
                    Database.executeUpdate(conn, "DELETE FROM import_data WHERE created < ?;",
                            Util.calendarToSqlDate(dt));
                } catch (final SQLException __) {
                }
                Database.close(conn);
            }
        }
    } catch (SQLException e) {
        if (log.isErrorEnabled())
            log.error("error uploading file", e);
        throw new UploadActionException("internal error");
    } finally {
        super.removeSessionFileItems(request);
    }
    return null;
}

From source file:ca.fastenalcompany.jsonconfig.ProductJson.java

public int update(String query, String... params) {
    Connection conn = null;//from w  ww .j av  a  2  s  .com
    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 rowsEffect = pstmt.executeUpdate();
        ResultSet rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            result = rs.getInt(1);
        } else if (rowsEffect > 0) {
            result = Integer.parseInt(params[params.length - 1]);
        }
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
        try {
            System.out.println(PropertyManager.getProperty("db_conn_closed"));
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    return result;
}

From source file:org.ado.biblio.desktop.db.DatabaseConnection.java

public Book insertBook(Book book) throws SQLException {
    String query = "INSERT INTO Book (title, author, isbn, tags) VALUES (?,?,?,?)";
    final PreparedStatement statement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
    statement.setString(1, book.getTitle());
    statement.setString(2, book.getAuthor());
    statement.setString(3, book.getIsbn());
    statement.setString(4, book.getTags());
    final int i = statement.executeUpdate();
    if (i == 0) {
        throw new SQLException("Update book failed, no rows affected.");
    }//from  w w  w.j  av a  2  s.  c  o  m
    final ResultSet resultSet = statement.getGeneratedKeys();
    if (resultSet.next()) {
        final int id = resultSet.getInt(1);
        return new Book(id, book.getTitle(), book.getAuthor(), book.getIsbn(), new java.util.Date(),
                book.getTags());
    }
    return null;
}

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

/**
 * @see DatabaseUserDAO#addDatabaseUser(java.sql.PreparedStatement, org.wso2.carbon.rssmanager.core.dto.restricted.DatabaseUser)
 *//*from www  .jav a2  s.  c  o m*/
public void addDatabaseUser(PreparedStatement nativeAddUserStatement, DatabaseUser user)
        throws RSSDAOException, RSSDatabaseConnectionException {
    Connection conn = null;
    PreparedStatement createUserStatement = null;
    PreparedStatement createUserEntryStatement;
    ResultSet result = null;
    try {
        conn = getDataSourceConnection();//acquire data source connection
        //start transaction with setting auto commit value to false
        conn.setAutoCommit(false);
        String createDBUserQuery = "INSERT INTO RM_DATABASE_USER(USERNAME, ENVIRONMENT_ID, TYPE, TENANT_ID) VALUES(?,?,?,?)";
        if (!RSSManagerConstants.RSSManagerTypes.RM_TYPE_SYSTEM.equalsIgnoreCase(user.getType())) {
            createDBUserQuery = "INSERT INTO RM_DATABASE_USER(USERNAME, ENVIRONMENT_ID, TYPE, TENANT_ID, RSS_INSTANCE_ID) VALUES(?,?,?,?,?)";
        }

        createUserStatement = conn.prepareStatement(createDBUserQuery, Statement.RETURN_GENERATED_KEYS);
        //insert user data to the statement to insert
        createUserStatement.setString(1, user.getName());
        createUserStatement.setInt(2, user.getEnvironmentId());
        createUserStatement.setString(3, user.getType());
        createUserStatement.setInt(4, user.getTenantId());
        /*if(RSSManagerConstants.RSSManagerTypes.RM_TYPE_SYSTEM.equalsIgnoreCase(user.getType())){
           createUserStatement.setString(5, user.getType());
        } else {
           createUserStatement.setString(5, user.getInstances().iterator().next().getName());
        }*/
        if (!RSSManagerConstants.RSSManagerTypes.RM_TYPE_SYSTEM.equalsIgnoreCase(user.getType())) {
            createUserStatement.setInt(5, user.getInstances().iterator().next().getId());
        }

        createUserStatement.executeUpdate();
        //get the inserted database user id from result
        //which will be inserted as a foreign key to user rss instance entry table
        result = createUserStatement.getGeneratedKeys();
        //if user inserted to several rss instances, then add rss instances user entries as batch operation to the table
        String createDBUserEntryQuery = "INSERT INTO RM_USER_INSTANCE_ENTRY(RSS_INSTANCE_ID, DATABASE_USER_ID) VALUES(?,?)";
        createUserEntryStatement = conn.prepareStatement(createDBUserEntryQuery);
        while (result.next()) {
            for (RSSInstance rssInstance : user.getInstances()) {
                createUserEntryStatement.setInt(1, rssInstance.getId());
                createUserEntryStatement.setInt(2, result.getInt(1));
                createUserEntryStatement.addBatch();
            }
            createUserEntryStatement.executeBatch();
        }
        if (nativeAddUserStatement != null) {
            //since native user add statements are not transactional, execute add user statement will add new
            //user to the rss instance
            nativeAddUserStatement.executeUpdate();
        }
        conn.commit();
    } catch (SQLException e) {
        RSSDAOUtil.rollback(conn, RSSManagerConstants.ADD_DATABASE_USER_ENTRY);
        String msg = "Failed to add database user" + user.getName() + "in rssInstance"
                + user.getRssInstanceName() + "to meta repository";
        handleException(msg, e);
    } finally {
        RSSDAOUtil.cleanupResources(result, createUserStatement, conn,
                RSSManagerConstants.ADD_DATABASE_USER_ENTRY);
    }
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage