List of usage examples for java.sql PreparedStatement toString
public String toString()
From source file:org.wso2.carbon.is.migration.service.v510.migrator.IdentityDataCleaner.java
public void cleanIdentityData() throws MigrationClientException { log.info("MIGRATION-LOGS >> Going to start : cleanIdentityData."); Connection identityConnection = null; PreparedStatement selectFromAccessTokenPS = null; PreparedStatement selectDuplicateUserNamePS = null; PreparedStatement deleteDuplicateUserNamePS = null; PreparedStatement deleteDeleteRow = null; ResultSet accessTokenRS = null; ResultSet duplicateUserNameRS = null; try {//from w w w . j a v a 2 s .com identityConnection = getDataSource().getConnection(); identityConnection.setAutoCommit(false); try { deleteDeleteRow = identityConnection.prepareStatement(SQLQueries.DELETE_USERNAME_WITH_SPACE); int rowCount = deleteDeleteRow.executeUpdate(); log.info("MIGRATION-LOGS >> Deleting " + rowCount + " rows having spaces in user name in " + "IDN_OAUTH2_ACCESS_TOKEN"); log.info("MIGRATION-LOGS >> Executed query : " + deleteDeleteRow.toString()); } catch (Exception e) { log.error("MIGRATION-ERROR-LOGS-009 >> Error while executing the migration.", e); if (!isContinueOnError()) { throw new MigrationClientException("Error while executing the migration.", e); } } try { selectFromAccessTokenPS = identityConnection .prepareStatement(SQLQueries.SELECT_USERNAME_WITHOUT_TENANT); accessTokenRS = selectFromAccessTokenPS.executeQuery(); log.info("MIGRATION-LOGS >> Executed query : " + selectFromAccessTokenPS.toString()); selectDuplicateUserNamePS = identityConnection .prepareStatement(SQLQueries.SELECT_INVALID_USERNAME_WITHOUT_TENANT); deleteDuplicateUserNamePS = identityConnection .prepareStatement(SQLQueries.DELETE_CORRUPTED_ACCESS_TOKEN_DATA); while (accessTokenRS.next()) { selectDuplicateUserNamePS.setString(1, accessTokenRS.getString("CONSUMER_KEY")); selectDuplicateUserNamePS.setString(2, UserCoreUtil.addTenantDomainToEntry( accessTokenRS.getString("AUTHZ_USER"), MultitenantConstants.SUPER_TENANT_DOMAIN_NAME)); selectDuplicateUserNamePS.setString(3, accessTokenRS.getString("USER_TYPE")); selectDuplicateUserNamePS.setString(4, accessTokenRS.getString("TOKEN_SCOPE")); duplicateUserNameRS = selectDuplicateUserNamePS.executeQuery(); if (duplicateUserNameRS.next()) { log.info("MIGRATION-LOGS >> deleting corrupted row : ACCESS_TOKEN-" + accessTokenRS.getString("ACCESS_TOKEN")); deleteDuplicateUserNamePS.setString(1, accessTokenRS.getString("ACCESS_TOKEN")); log.info(deleteDuplicateUserNamePS.toString()); log.info(deleteDuplicateUserNamePS); int i = deleteDuplicateUserNamePS.executeUpdate(); log.info("MIGRATION-LOGS >> Deleting " + i); log.info("MIGRATION-LOGS >> Executed query : " + deleteDuplicateUserNamePS.toString()); } } } catch (Exception e) { log.error("MIGRATION-ERROR-LOGS-010 >> Error while executing the migration.", e); if (!isContinueOnError()) { throw new MigrationClientException("Error while executing the migration.", e); } } identityConnection.commit(); } catch (SQLException e) { log.warn("MIGRATION-LOGS >> Error while cleaning identity data ", e); if (!isContinueOnError()) { throw new MigrationClientException("Error while executing the migration.", e); } } finally { try { IdentityDatabaseUtil.closeResultSet(accessTokenRS); IdentityDatabaseUtil.closeResultSet(duplicateUserNameRS); IdentityDatabaseUtil.closeStatement(selectFromAccessTokenPS); IdentityDatabaseUtil.closeStatement(selectDuplicateUserNamePS); IdentityDatabaseUtil.closeStatement(deleteDuplicateUserNamePS); IdentityDatabaseUtil.closeConnection(identityConnection); } catch (Exception e) { } } log.info("MIGRATION-LOGS >> Done : cleanIdentityData."); }
From source file:com.amazonbird.announce.ProductMgrImpl.java
public List<Announcer> getAnnouncerWhoViewedProduct(long productId) { ArrayList<Announcer> announcerList = new ArrayList<Announcer>(); String query = "select distinct announcer.id as id, announcer.consumerKey as consumerKey, " + "announcer.consumerSecret as consumerSecret, announcer.accessToken as accessToken, announcer.accessTokenSecret as accessTokenSecret, " + "announcer.name as name, announcer.surname as surname, announcer.password as password, " + "announcer.email as email, announcer.screenName as screenName, announcer.suspended as suspended, " + "announcer.creationtime as creationtime, announcer.training as training, announcer.maxFamousAccount2Follow as maxFamousAccount2Follow, " + "announcer.famousAccountFollowed as famousAccountFollowed, announcer.following as following, announcer.follower as follower, " + "announcer.authtoken as authtoken, announcer.sesid as sesid, announcer.suspensiontime as suspensiontime, announcer.description as description, " + "announcer.url as url, announcer.longName as longName, announcer.location as location, announcer.pictureUrl as pictureUrl " + " from announcer, announcement, product, click where click.announcementid = announcement.id and announcement.productid = product.id and announcement.customerid = announcer.id and product.id = ?"; Connection connection = null; PreparedStatement ps = null; ResultSet rs = null;//www . j a v a 2 s.c o m try { connection = dbMgr.getConnection(); ps = connection.prepareStatement(query); ps.setLong(1, productId); rs = ps.executeQuery(); while (rs.next()) { Announcer announcer = new Announcer(); announcer.getDataFromResultSet(rs); announcerList.add(announcer); } logger.debug(DBConstants.QUERY_EXECUTION_SUCC + ps.toString()); } catch (SQLException ex) { logger.error(DBConstants.QUERY_EXECUTION_FAIL + ps.toString(), ex); } finally { dbMgr.closeResources(connection, ps, rs); } return announcerList; }
From source file:storybook.model.oldModel.ModelMigration.java
public int getGeneratedId(PreparedStatement stmt) throws SQLException { int retour = -1; ResultSet rs = null;//from ww w. j av a 2 s . c o m try { rs = stmt.getGeneratedKeys(); int count = 0; while (rs.next()) { if (count > 0) { throw new SQLException("error: got more than one id"); } retour = rs.getInt(1); ++count; } } catch (SQLException exc) { SbApp.error("*** ModelMigration.getGeneratedId(" + stmt.toString() + ")", exc); } finally { this.closeResultSet(rs); } return retour; }
From source file:com.amazonbird.announce.ProductMgrImpl.java
public int getViewCountForProduct(long productId) { ///* ww w .j a v a 2 s. c o m*/ Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; int count = 0; try { connection = dbMgr.getConnection(); ps = connection.prepareStatement( "select count(*) from announcement, product, click where click.announcementid = announcement.id and announcement.productid = product.id and product.id = ?"); ps.setLong(1, productId); rs = ps.executeQuery(); if (rs.next()) { count = rs.getInt(1); } logger.debug(DBConstants.QUERY_EXECUTION_SUCC + ps.toString()); } catch (SQLException ex) { logger.error(DBConstants.QUERY_EXECUTION_FAIL + ps.toString(), ex); } finally { dbMgr.closeResources(connection, ps, rs); } return count; }
From source file:com.amazonbird.announce.ProductMgrImpl.java
public void updateProduct(Product product) { Connection connection = null; PreparedStatement ps = null; try {/*from w ww . j a v a 2 s. com*/ connection = dbMgr.getConnection(); ps = connection.prepareStatement(UPDATE_PRODUCT); ps.setString(1, product.getName()); ps.setDouble(2, product.getPrice()); ps.setString(3, product.getDestination()); ps.setString(4, product.getAlternativeDestionation()); ps.setString(5, product.getLocale()); ps.setLong(6, product.getAnnouncerId()); ps.setLong(7, product.getId()); ps.executeUpdate(); logger.debug(DBConstants.QUERY_EXECUTION_SUCC + ps.toString()); } catch (SQLException ex) { logger.error(DBConstants.QUERY_EXECUTION_FAIL + ps.toString(), ex); } finally { dbMgr.closeResources(connection, ps, null); } }
From source file:org.apache.openaz.xacml.std.pip.engines.jdbc.JDBCEngine.java
protected void getAttributes(PIPRequest pipRequest, PIPFinder pipFinder, JDBCResolver jdbcResolver, StdMutablePIPResponse pipResponse) throws PIPException { /*/* ww w.j ava 2 s .c o m*/ * First we need to get a PreparedStatement */ Connection connection = this.getConnection(); PreparedStatement preparedStatement = jdbcResolver.getPreparedStatement(this, pipRequest, pipFinder, connection); if (preparedStatement == null) { this.logger.debug(this.getName() + " does not handle " + pipRequest.toString()); try { if (connection != null) { connection.close(); } } catch (Exception e) { //NOPMD } return; } /* * Is it in the cache? */ this.logger.debug(preparedStatement.toString()); // Cache<String, PIPResponse> cache = this.getCache(); //if (cache != null) { // TODO - a cache key //} /* * Execute the prepared statement */ ResultSet resultSet = null; try { resultSet = preparedStatement.executeQuery(); } catch (SQLException ex) { this.logger.error("SQLException executing query: " + ex.toString(), ex); // TODO: Should we re-throw the exception, or just return an empty response? } if (resultSet == null) { try { preparedStatement.close(); } catch (SQLException e) { this.logger.error("SQLException closing preparedStatment: " + e.toString(), e); } try { if (connection != null) { connection.close(); } } catch (Exception e) { //NOPMD } return; } try { /* * Get all the results */ while (resultSet.next()) { List<Attribute> listAttributes = jdbcResolver.decodeResult(resultSet); if (listAttributes != null) { pipResponse.addAttributes(listAttributes); } } /* * Save it in the cache */ //if (cache != null) { // TODO //} } catch (SQLException ex) { this.logger.error("SQLException decoding results: " + ex.toString()); // TODO: Should we re-throw the exception or just continue } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { this.logger.error("SQLException closing resultSet: " + e.toString() + " (May be memory leak)"); } } try { preparedStatement.close(); } catch (SQLException e) { this.logger.error( "SQLException closing preparedStatement: " + e.toString() + " (May be memory leak)"); } try { connection.close(); } catch (SQLException e) { this.logger.error("SQLException closing connection: " + e.toString() + " (May be memory leak)"); } } }
From source file:com.wso2telco.dep.reportingservice.dao.TaxDAO.java
/** * Gets the API request times for application. * * @param consumerKey the consumer key//from w w w. j a va 2 s .c o m * @param year the year * @param month the month * @param userId the user id * @return the API request times for application * @throws Exception the exception */ public Map<String, List<APIRequestDTO>> getAPIRequestTimesForApplication(String consumerKey, short year, short month, String userId) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; String sql = "SELECT api_version,total_request_count AS count,STR_TO_DATE(time,'%Y-%m-%d') as date FROM " + ReportingTable.API_REQUEST_SUMMARY + "WHERE consumerKey=? AND userId=? AND month=? AND year=?;"; Map<String, List<APIRequestDTO>> apiRequests = new HashMap<String, List<APIRequestDTO>>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql); ps.setString(1, consumerKey); ps.setString(2, userId); ps.setShort(3, month); ps.setShort(4, year); log.debug("SQL (PS) st ---> " + ps.toString()); results = ps.executeQuery(); log.debug("SQL (PS) ed ---> "); while (results.next()) { APIRequestDTO req = new APIRequestDTO(); req.setApiVersion(results.getString("api_version")); req.setRequestCount(results.getInt("count")); req.setDate(results.getDate("date")); if (apiRequests.containsKey(req.getApiVersion())) { apiRequests.get(req.getApiVersion()).add(req); } else { List<APIRequestDTO> list = new ArrayList<APIRequestDTO>(); list.add(req); apiRequests.put(req.getApiVersion(), list); } } } catch (SQLException e) { handleException("Error occurred while getting Request Times for Application", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } return apiRequests; }
From source file:com.wso2telco.dep.reportingservice.dao.TaxDAO.java
/** * Gets the taxes for subscription.// ww w . ja v a2 s .com * * @param applicationId the application id * @param apiId the api id * @return the taxes for subscription * @throws Exception the exception */ public List<Tax> getTaxesForSubscription(int applicationId, int apiId) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; String sql = "SELECT type,effective_from,effective_to,value FROM " + ReportingTable.TAX + ", " + ReportingTable.SUBSCRIPTION_TAX + "WHERE subscription_tax.application_id=? AND subscription_tax.api_id=? AND tax.type=subscription_tax.tax_type "; List<Tax> taxes = new ArrayList<Tax>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql); log.debug("getTaxesForSubscription for applicationId---> " + applicationId + " apiId--> " + apiId); ps.setInt(1, applicationId); ps.setInt(2, apiId); log.debug("SQL (PS) ---> " + ps.toString()); results = ps.executeQuery(); while (results.next()) { Tax tax = new Tax(); tax.setType(results.getString("type")); tax.setEffective_from(results.getDate("effective_from")); tax.setEffective_to(results.getDate("effective_to")); tax.setValue(results.getBigDecimal("value")); taxes.add(tax); } } catch (SQLException e) { log.error("SQL Error in getTaxesForSubscription"); log.error(e.getStackTrace()); handleException("Error occurred while getting Taxes for Subscription", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } return taxes; }
From source file:org.apache.hawq.ranger.service.HawqClient.java
private List<String> queryHawqPerDbAndSchema(String userInput, Map<String, List<String>> resources, String schemaColumnName, String resultColumnName, String query) throws SQLException { Set<String> uniqueResults = new HashSet<>(); List<String> databases = resources.get("database"); List<String> schemas = resources.get("schema"); PreparedStatement preparedStatement = null; ResultSet resultSet = null;//from w w w. j a v a2s .co m Connection conn = null; if (databases.contains(WILDCARD)) { databases = getDatabaseList(WILDCARD); } for (String db : databases) { if (LOG.isDebugEnabled()) { LOG.debug("<== HawqClient.queryHawqPerDbAndSchema: Connecting to db: " + db); } try { conn = getConnection(connectionProperties, db); preparedStatement = handleWildcardPreparedStatement(userInput, query, conn); if (LOG.isDebugEnabled()) { LOG.debug("<== HawqClient.queryHawqPerDbAndSchema Starting query: " + preparedStatement.toString()); } resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { if (schemas.contains(resultSet.getString(schemaColumnName)) || schemas.contains(WILDCARD)) { uniqueResults.add(resultSet.getString(resultColumnName)); } } if (LOG.isDebugEnabled()) { LOG.debug("<== HawqClient.queryHawqPerDbAndSchema Query result: " + uniqueResults.toString()); } } catch (SQLException e) { LOG.error("<== HawqClient.queryHawqPerDbAndSchema Error: Failed to get results for query: " + query + ", Error: " + e); } finally { closeResultSet(resultSet); closeStatement(preparedStatement); closeConnection(conn); } } return new ArrayList<>(uniqueResults); }
From source file:com.sec.ose.osi.localdb.identification.IdentificationDBManager.java
synchronized public static void execute(PreparedStatement tmpPreparedStatement) { if (tmpPreparedStatement == null) { return;// www . j a v a 2s . c o m } log.debug(tmpPreparedStatement.toString()); try { conn.setAutoCommit(false); tmpPreparedStatement.executeBatch(); conn.commit(); conn.setAutoCommit(true); tmpPreparedStatement.clearBatch(); } catch (SQLException e) { log.warn(e); } }