List of usage examples for java.sql Statement RETURN_GENERATED_KEYS
int RETURN_GENERATED_KEYS
To view the source code for java.sql Statement RETURN_GENERATED_KEYS.
Click Source Link
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); } }