List of usage examples for org.springframework.jdbc.core ResultSetExtractor ResultSetExtractor
ResultSetExtractor
From source file:org.ohmage.query.impl.UserQueries.java
@Override public QueryResultsList<UserInformation> getUserInformation(final String requesterUsername, final Collection<String> usernames, final Collection<String> emailAddresses, final Boolean admin, final Boolean enabled, final Boolean newAccount, final Boolean canCreateCampaigns, final Boolean canCreateClasses, final Collection<String> firstNames, final Collection<String> lastNames, final Collection<String> organizations, final Collection<String> personalIds, final Collection<String> campaignIds, final Collection<String> classIds, final long numToSkip, final long numToReturn, final boolean settingUpUser) throws DataAccessException { // The initial SELECT selects everything. StringBuilder sql = new StringBuilder("SELECT u.username, " + "u.email_address, " + "u.admin, " + "u.enabled, " + "u.new_account, " + "u.campaign_creation_privilege, " + "u.class_creation_privilege, " + "u.user_setup_privilege, " + "up.first_name, " + "up.last_name, " + "up.organization, " + "up.personal_id " + "FROM user u " + "LEFT JOIN user_personal up ON " + "u.id = up.user_id, " + "user ru " + "WHERE ru.username = ? " + // ACL "AND (" + settingUpUser + " OR " + "(u.id = ru.id)" + " OR " + "(ru.admin = true)" + " OR EXISTS(" + // If the requesting user shares a campaign // with the desired user and is a // supervisor in that campaign. "SELECT ru.id " + "FROM user_role ur, " + "user_role_campaign urc, " + "user_role_campaign rurc " + // The requesting user is associated with a // campaign. "WHERE ru.id = rurc.user_id " + // The requesting user is a supervisor in // that campaign. "AND ur.id = rurc.user_role_id " + "AND ur.role = '" + Campaign.Role.SUPERVISOR.toString() + "' " + // The queried user is also in a campaign. "AND u.id = urc.user_id " + // And that campaign is the same as the one // in which the requesting user is a // supervisor. "AND urc.campaign_id = rurc.campaign_id" + ")" + " OR EXISTS(" + // If the requesting user shares a class // with the desired user and is privileged // in that class. "SELECT ru.id " + "FROM user_class_role ucr, " + "user_class uc, " + "user_class ruc " + // The requesting user is associated with a // class. "WHERE ru.id = ruc.user_id " + // The requesting user is privileged in // that class. "AND ucr.id = ruc.user_class_role_id " + "AND ucr.role = '" + Clazz.Role.PRIVILEGED.toString() + "' " + // The queried user is also in a class. "AND u.id = uc.user_id " + // And that class is the same as the one in // which the requesting user is privileged. "AND uc.class_id = ruc.class_id" + ")" + ")"); // The initial parameter list doesn't have any items. Collection<Object> parameters = new LinkedList<Object>(); parameters.add(requesterUsername);//from w ww.ja v a2s . c om // If the list of usernames is present, add a WHERE clause component // that limits the results to only those users whose exact username is // in the list. if (usernames != null) { if (usernames.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND ("); boolean firstPass = true; for (String username : usernames) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("LOWER(u.username) LIKE ?"); parameters.add(username); } sql.append(")"); } // If the list of email addresses is present, add a WHERE clause that // that contains all of the tokens in their own OR. if (emailAddresses != null) { if (emailAddresses.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND ("); boolean firstPass = true; for (String emailAddressToken : emailAddresses) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("LOWER(u.email_address) LIKE ?"); parameters.add(emailAddressToken); } sql.append(")"); } // If "admin" is present, add a WHERE clause component that limits the // results to only those whose admin boolean is the same as this // boolean. if (admin != null) { sql.append(" AND u.admin = ?"); parameters.add(admin); } // If "enabled" is present, add a WHERE clause component that limits // the results to only those whose enabled value is the same as this // boolean if (enabled != null) { sql.append(" AND u.enabled = ?"); parameters.add(enabled); } // If "newAccount" is present, add a WHERE clause component that limits // the results to only those whose new account status is the same as // this boolean. if (newAccount != null) { sql.append(" AND u.new_account = ?"); parameters.add(newAccount); } // If "canCreateCampaigns" is present, add a WHERE clause component // that limits the results to only those whose campaign creation // privilege is the same as this boolean. if (canCreateCampaigns != null) { sql.append(" AND u.campaign_creation_privilege = ?"); parameters.add(canCreateCampaigns); } // If "canCreateClasses" is present, add a WHERE clause component that // limits the results to only those whose campaign creation privilege // is the same as this boolean. if (canCreateClasses != null) { sql.append(" AND u.class_creation_privilege = ?"); parameters.add(canCreateClasses); } // If the list of first name tokens is present, add a WHERE clause that // contains all of the tokens in their own OR. if (firstNames != null) { if (firstNames.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND ("); boolean firstPass = true; for (String firstName : firstNames) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("LOWER(up.first_name) LIKE ?"); parameters.add(firstName); } sql.append(")"); } // If the list of last name tokens is present, add a WHERE clause that // contains all of the tokens in their own OR. if (lastNames != null) { if (lastNames.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND ("); boolean firstPass = true; for (String lastName : lastNames) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("LOWER(up.last_name) LIKE ?"); parameters.add(lastName); } sql.append(")"); } // If the list of organization tokens is present, add a WHERE clause // that contains all of the tokens in their own OR. if (organizations != null) { if (organizations.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND ("); boolean firstPass = true; for (String organization : organizations) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("LOWER(up.organization) LIKE ?"); parameters.add(organization); } sql.append(")"); } // If the list of personal ID tokens is present, add a WHERE clause // that contains all of the tokens in their own OR. if (personalIds != null) { if (personalIds.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND ("); boolean firstPass = true; for (String personalId : personalIds) { if (firstPass) { firstPass = false; } else { sql.append(" OR "); } sql.append("LOWER(up.personal_id) LIKE ?"); parameters.add(personalId); } sql.append(")"); } // If a collection of campaign IDs is present, add a WHERE clause // component that limits the results to only those in any of the // campaigns. if (campaignIds != null) { if (campaignIds.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND u.id IN (" + "SELECT urc.user_id " + "FROM campaign c, user_role_campaign urc " + "WHERE c.urn IN " + StringUtils.generateStatementPList(campaignIds.size()) + " " + "AND c.id = urc.campaign_id" + ")"); parameters.addAll(campaignIds); } // If a collection of class IDs is present, add a WHERE clause // component that limits the results to only those in any of the // classes. if (classIds != null) { if (classIds.size() == 0) { return (new QueryResultListBuilder<UserInformation>()).getQueryResult(); } sql.append(" AND u.id IN (" + "SELECT uc.user_id " + "FROM class c, user_class uc " + "WHERE c.urn IN " + StringUtils.generateStatementPList(classIds.size()) + " " + "AND c.id = uc.class_id" + ")"); parameters.addAll(classIds); } // Always order the results by username to facilitate paging. sql.append(" ORDER BY u.username"); // Returns the results as queried by the database. try { return getJdbcTemplate().query(sql.toString(), parameters.toArray(), new ResultSetExtractor<QueryResultsList<UserInformation>>() { /** * Extracts the data into the results and then returns * the total number of results found. */ @Override public QueryResultsList<UserInformation> extractData(final ResultSet rs) throws SQLException, org.springframework.dao.DataAccessException { QueryResultListBuilder<UserInformation> builder = new QueryResultListBuilder<UserInformation>(); int numSkipped = 0; while (numSkipped++ < numToSkip) { if (rs.next()) { builder.increaseTotalNumResults(); } else { return builder.getQueryResult(); } } long numReturned = 0; while (numReturned++ < numToReturn) { if (rs.next()) { builder.addResult(mapRow(rs)); } else { return builder.getQueryResult(); } } while (rs.next()) { builder.increaseTotalNumResults(); } return builder.getQueryResult(); } /** * Creates a new UserInformation object from the * user information. */ private UserInformation mapRow(final ResultSet rs) throws SQLException { String username = rs.getString("username"); String emailAddress = rs.getString("email_address"); boolean admin = rs.getBoolean("admin"); boolean enabled = rs.getBoolean("enabled"); boolean newAccount = rs.getBoolean("new_account"); boolean canCreateCampaigns = rs.getBoolean("campaign_creation_privilege"); boolean canCreateClasses = rs.getBoolean("class_creation_privilege"); boolean canSetupUsers = rs.getBoolean("user_setup_privilege"); String firstName = rs.getString("first_name"); String lastName = rs.getString("last_name"); String organization = rs.getString("organization"); String personalId = rs.getString("personal_id"); UserPersonal personalInfo = null; if ((firstName != null) && (lastName != null) && (organization != null) && (personalId != null)) { try { personalInfo = new UserPersonal(firstName, lastName, organization, personalId); } catch (DomainException e) { throw new SQLException("Error creating the user's " + "personal information.", e); } } try { return new UserInformation(username, emailAddress, admin, enabled, newAccount, canCreateCampaigns, canCreateClasses, canSetupUsers, null, null, personalInfo); } catch (DomainException e) { throw new SQLException("Error creating the user's information.", e); } } }); } catch (org.springframework.dao.DataAccessException e) { throw new DataAccessException( "Error executing the following SQL '" + sql.toString() + "' with parameter(s): " + parameters); } }
From source file:annis.administration.AdministrationDao.java
/** * Retrieves the name of the top level corpus in the corpus.tab file. * * <p>/* w ww .j a v a 2s . c o m*/ * At this point, the tab files must be in the staging area.</p> * * @return The name of the toplevel corpus or an empty String if no top level * corpus is found. */ private String getTopLevelCorpusFromTmpArea() { String sql = "SELECT name FROM " + tableInStagingArea("corpus") + " WHERE type='CORPUS'\n" + "AND pre = (SELECT min(pre) FROM " + tableInStagingArea("corpus") + ")\n" + "AND post = (SELECT max(post) FROM " + tableInStagingArea("corpus") + ")"; return getJdbcTemplate().query(sql, new ResultSetExtractor<String>() { @Override public String extractData(ResultSet rs) throws SQLException, DataAccessException { if (rs.next()) { return rs.getString("name"); } else { return null; } } }); }
From source file:org.apache.fineract.infrastructure.dataexport.helper.DataExportUtils.java
/** * Gets the meta data of the columns of the specified table * /*w w w . jav a 2s . c om*/ * @param tableName * @param jdbcTemplate * @return List of {@link EntityColumnMetaData} objects */ public static List<EntityColumnMetaData> getTableColumnsMetaData(final String tableName, final JdbcTemplate jdbcTemplate) { final List<EntityColumnMetaData> entityColumnsMetaData = new ArrayList<>(); final List<String> columnNames = new ArrayList<>(); final DataExportCoreTable coreTable = DataExportCoreTable.newInstance(tableName); Set<String> columnsToBeRemovedFromListsOfEntityColumns = new HashSet<>( Arrays.asList(DataExportEntityColumnName.TRANSFER_TO_OFFICE_ID, DataExportEntityColumnName.VERSION, DataExportEntityColumnName.IMAGE_ID, DataExportEntityColumnName.ACCOUNT_TYPE_ENUM, DataExportEntityColumnName.DEPOSIT_TYPE_ENUM, DataExportEntityColumnName.SUB_STATUS, DataExportEntityColumnName.FULL_NAME)); try { // see - http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html // LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. // It can also be employed to obtain the types of the result columns if you are using a MySQL API // that makes result set metadata available. final ResultSetMetaData resultSetMetaData = jdbcTemplate .query("select * from " + tableName + " limit 0", new ResultSetExtractor<ResultSetMetaData>() { @Override public ResultSetMetaData extractData(ResultSet rs) throws SQLException, DataAccessException { return rs.getMetaData(); } }); if (resultSetMetaData != null) { final int numberOfColumns = resultSetMetaData.getColumnCount(); for (int i = 1; i <= numberOfColumns; i++) { String columnName = resultSetMetaData.getColumnName(i); String columnLabel = resultSetMetaData.getColumnName(i); String columnType = resultSetMetaData.getColumnTypeName(i); Integer columnIsNullable = resultSetMetaData.isNullable(i); boolean isNullable = (columnIsNullable != 0); if (coreTable != null) { switch (coreTable) { case M_LOAN_TRANSACTION: columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.UNRECOGNIZED_INCOME_PORTION); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.SUSPENDED_INTEREST_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.SUSPENDED_FEE_CHARGES_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.SUSPENDED_PENALTY_CHARGES_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.OUTSTANDING_LOAN_BALANCE_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.RECOVERED_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PAYMENT_DETAIL_ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.OFFICE_ID); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.IS_ACCCOUNT_TRANSFER); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.APPUSER_ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.EXTERNAL_ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.CREATED_DATE); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.TRANSACTION_TYPE_ENUM); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.LOAN_ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.AMOUNT); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PRINCIPAL_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.INTEREST_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.FEE_CHARGES_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PENALTY_CHARGES_PORTION_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.OVERPAYMENT_PORTION_DERIVED); break; case M_SAVINGS_ACCOUNT_TRANSACTION: columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.OVERDRAFT_AMOUNT_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.RUNNING_BALANCE_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.CUMULATIVE_BALANCE_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.BALANCE_NUMBER_OF_DAYS_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.BALANCE_END_DATE_DERIVED); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.CREATED_DATE); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.TRANSACTION_TYPE_ENUM); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.APPUSER_ID); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.SAVINGS_ACCOUNT_ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.AMOUNT); break; case M_LOAN_REPAYMENT_SCHEDULE: columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.LOAN_ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.FROMDATE); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.INSTALLMENT); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PRINCIPAL_COMPLETED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PRINCIPAL_WRITTENOFF_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.INTEREST_COMPLETED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.INTEREST_WAIVED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.INTEREST_WRITTENOFF_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.ACCRUAL_INTEREST_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.SUSPENDED_INTEREST_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.FEE_CHARGES_WRITTENOFF_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.FEE_CHARGES_COMPLETED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.FEE_CHARGES_WAIVED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.ACCRUAL_FEE_CHARGES_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.SUSPENDED_FEE_CHARGES_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PENALTY_CHARGES_COMPLETED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PENALTY_CHARGES_WAIVED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.PENALTY_CHARGES_WRITTEN_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.ACCRUAL_PENALTY_CHARGES_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.SUSPENDED_PENALTY_CHARGES_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.TOTAL_PAID_IN_ADVANCE_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.TOTAL_PAID_LATE_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.COMPLETED_DERIVED); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.CREATED_BY_ID); columnsToBeRemovedFromListsOfEntityColumns.add(DataExportEntityColumnName.CREATED_DATE); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.LAST_MODIFIED_BY_ID); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.LAST_MODIFIED_DATE); columnsToBeRemovedFromListsOfEntityColumns .add(DataExportEntityColumnName.RECALCULATED_INTEREST_COMPONENT); break; default: break; } } if (!columnsToBeRemovedFromListsOfEntityColumns.contains(columnName)) { if (columnName.equals(DataExportEntityColumnName.ID)) { columnLabel = DataExportEntityColumnName.TRANSACTION_ID; } EntityColumnMetaData entityColumnMetaData = EntityColumnMetaData.newInstance(columnName, columnLabel, columnType, isNullable); entityColumnsMetaData.add(entityColumnMetaData); columnNames.add(columnName); } } } } catch (Exception exception) { exception.printStackTrace(); } return entityColumnsMetaData; }
From source file:org.apache.syncope.core.persistence.jpa.content.ContentLoaderHandler.java
private Object[] getParameters(final String tableName, final Attributes attrs) { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); Map<String, Integer> colTypes = jdbcTemplate.query("SELECT * FROM " + tableName + " WHERE 0=1", new ResultSetExtractor<Map<String, Integer>>() { @Override//from w w w . ja va 2s . c o m public Map<String, Integer> extractData(final ResultSet rs) throws SQLException { Map<String, Integer> colTypes = new HashMap<>(); for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { colTypes.put(rs.getMetaData().getColumnName(i).toUpperCase(), rs.getMetaData().getColumnType(i)); } return colTypes; } }); Object[] parameters = new Object[attrs.getLength()]; for (int i = 0; i < attrs.getLength(); i++) { Integer colType = colTypes.get(attrs.getQName(i).toUpperCase()); if (colType == null) { LOG.warn("No column type found for {}", attrs.getQName(i).toUpperCase()); colType = Types.VARCHAR; } switch (colType) { case Types.INTEGER: case Types.TINYINT: case Types.SMALLINT: try { parameters[i] = Integer.valueOf(attrs.getValue(i)); } catch (NumberFormatException e) { LOG.error("Unparsable Integer '{}'", attrs.getValue(i)); parameters[i] = attrs.getValue(i); } break; case Types.NUMERIC: case Types.DECIMAL: case Types.BIGINT: try { parameters[i] = Long.valueOf(attrs.getValue(i)); } catch (NumberFormatException e) { LOG.error("Unparsable Long '{}'", attrs.getValue(i)); parameters[i] = attrs.getValue(i); } break; case Types.DOUBLE: try { parameters[i] = Double.valueOf(attrs.getValue(i)); } catch (NumberFormatException e) { LOG.error("Unparsable Double '{}'", attrs.getValue(i)); parameters[i] = attrs.getValue(i); } break; case Types.REAL: case Types.FLOAT: try { parameters[i] = Float.valueOf(attrs.getValue(i)); } catch (NumberFormatException e) { LOG.error("Unparsable Float '{}'", attrs.getValue(i)); parameters[i] = attrs.getValue(i); } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: try { parameters[i] = FormatUtils.parseDate(attrs.getValue(i)); } catch (ParseException e) { LOG.error("Unparsable Date '{}'", attrs.getValue(i)); parameters[i] = attrs.getValue(i); } break; case Types.BIT: case Types.BOOLEAN: parameters[i] = "1".equals(attrs.getValue(i)) ? Boolean.TRUE : Boolean.FALSE; break; case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: try { parameters[i] = Hex.decodeHex(attrs.getValue(i).toCharArray()); } catch (DecoderException | IllegalArgumentException e) { parameters[i] = attrs.getValue(i); } break; case Types.BLOB: try { parameters[i] = Hex.decodeHex(attrs.getValue(i).toCharArray()); } catch (DecoderException | IllegalArgumentException e) { LOG.warn("Error decoding hex string to specify a blob parameter", e); parameters[i] = attrs.getValue(i); } catch (Exception e) { LOG.warn("Error creating a new blob parameter", e); } break; default: parameters[i] = attrs.getValue(i); } } return parameters; }
From source file:org.apereo.portal.jgroups.protocols.JdbcPingDao.java
@Override public Map<Address, PhysicalAddress> getAddresses(String clusterName) { if (!isReady()) { return Collections.emptyMap(); }/*from www . j a v a 2 s . co m*/ final Map<String, Object> paramMap = new HashMap<String, Object>(); paramMap.put(PRM_CLUSTER_NAME, clusterName); return this.namedParameterJdbcOperations.query(SELECT_CLUSTER_SQL, paramMap, new ResultSetExtractor<Map<Address, PhysicalAddress>>() { @Override public Map<Address, PhysicalAddress> extractData(ResultSet rs) throws SQLException, DataAccessException { final Map<Address, PhysicalAddress> result = new HashMap<Address, PhysicalAddress>(); while (rs.next()) { try { final Address memberAddress = getStreamableParam(rs, Table.COL_MEMBER_ADDRESS); final PhysicalAddress physicalAddress = getStreamableParam(rs, Table.COL_PHYSICAL_ADDRESS); result.put(memberAddress, physicalAddress); } catch (Exception e) { if (logger.isDebugEnabled()) { logger.warn("Ignoring address result due to data parsing error", e); } else { logger.warn("Ignoring address result due to data parsing error"); } } } logger.debug("Found {} addresses in cluster: {}", result.size(), result); return result; } }); }
From source file:org.dbist.dml.impl.DmlJdbc.java
private <T> List<T> query(String sql, Map<String, ?> paramMap, final Class<T> requiredType, final Table table, int pageIndex, int pageSize, int firstResultIndex, int maxResultSize) throws Exception { boolean pagination = pageIndex >= 0 && pageSize > 0; boolean fragment = firstResultIndex > 0 || maxResultSize > 0; List<T> list = null;// ww w.j a va 2 s . c om if (isSupportedPaginationQuery() || (!pagination && !fragment)) { list = this.namedParameterJdbcOperations.query(sql, paramMap, new RowMapper<T>() { public T mapRow(ResultSet rs, int rowNum) throws SQLException { return newInstance(rs, requiredType, table); } }); } else { if (!pagination) { pageIndex = 0; pageSize = 0; } if (firstResultIndex < 0) firstResultIndex = 0; if (maxResultSize < 0) maxResultSize = 0; int pageFromIndex = pagination ? pageIndex * pageSize : 0; int offset = pageFromIndex + firstResultIndex; long limit = 0; if (pageSize > 0) { limit = pageSize - firstResultIndex; if (maxResultSize > 0) limit = Math.min(limit, maxResultSize); } else if (maxResultSize > 0) { limit = maxResultSize; } else if (limit == 0) { limit = Long.MAX_VALUE; } final int _offset = offset; final long _limit = limit; list = this.namedParameterJdbcOperations.query(sql, paramMap, new ResultSetExtractor<List<T>>() { public List<T> extractData(ResultSet rs) throws SQLException, DataAccessException { List<T> list = new ArrayList<T>(); for (int i = 0; i < _offset; i++) { if (rs.next()) continue; return list; } int i = 0; while (rs.next()) { if (i++ == _limit) break; list.add(newInstance(rs, requiredType, table)); } return list; } }); } return list; }
From source file:org.easyrec.store.dao.web.impl.LoaderDAOMysqlImpl.java
/** * easyrec pre 0.96 stored settings for plugins in the tenantConfig column of the tenantsTable from 0.96 * onwards they are stored as XML serialized GeneratorConfigurations in the plugin_configuration table * this snippet converts the existing ARM configurations (and only ARM configurations) to the new XML * version.//from w w w . ja v a 2 s.com * * @throws IOException */ private void update_0_96f() throws IOException { // final String RENAME_SOURCETYPE_QUERY = "UPDATE sourcetype SET name=? WHERE name=?"; getJdbcTemplate().update(RENAME_SOURCETYPE_QUERY, "http://www.easyrec.org/plugins/ARM/0.96", "ARM"); getJdbcTemplate().update(RENAME_SOURCETYPE_QUERY, "http://www.easyrec.org/plugins/slopeone/0.96", "http://www.easyrec.org/plugins/slopeone/0.95"); ResultSetExtractor<Map<Integer, String>> rse = new ResultSetExtractor<Map<Integer, String>>() { public Map<Integer, String> extractData(ResultSet rs) throws SQLException, DataAccessException { Map<Integer, String> result = Maps.newHashMap(); while (rs.next()) { int id = rs.getInt("id"); String config = rs.getString("tenantConfig"); result.put(id, config); } return result; } }; Map<Integer, String> tenantConfigs = getJdbcTemplate().query("SELECT id, tenantConfig FROM tenant", rse); for (Map.Entry<Integer, String> tenantConfig : tenantConfigs.entrySet()) { String tenantConfigString = tenantConfig.getValue(); if (tenantConfigString == null) tenantConfigString = ""; int tenantId = tenantConfig.getKey(); StringReader reader = new StringReader(tenantConfigString); Properties propertiesConfig = new Properties(); propertiesConfig.load(reader); String configViewedTogether = generateXmlConfigurationFromProperties(propertiesConfig, "VIEWED_TOGETHER", "VIEW"); String configGoodRatedTogether = generateXmlConfigurationFromProperties(propertiesConfig, "GOOD_RATED_TOGETHER", "RATE"); String configBoughtTogether = generateXmlConfigurationFromProperties(propertiesConfig, "BOUGHT_TOGETHER", "BUY"); // write back config with arm settings removed // getJdbcTemplate().update("UPDATE tenant SET tenantConfig = ? WHERE id = ?", // propertiesConfig.toString(), tenantConfig.getKey()); final String CONFIG_QUERY = "INSERT INTO plugin_configuration(tenantId, assocTypeId, pluginId, pluginVersion, name, configuration, active) VALUES " + "(?, ?, 'http://www.easyrec.org/plugins/ARM', ?, 'Default Configuration', ?, b'1')"; // generate configuration entries getJdbcTemplate().update(CONFIG_QUERY, tenantId, 1, currentVersion, configViewedTogether); getJdbcTemplate().update(CONFIG_QUERY, tenantId, 2, currentVersion, configGoodRatedTogether); getJdbcTemplate().update(CONFIG_QUERY, tenantId, 3, currentVersion, configBoughtTogether); final String slopeOneXmlConfig = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" + "<slopeOneConfiguration>" + "<configurationName>Default Configuration</configurationName>" + "<associationType>IS_RELATED</associationType>" + "<maxRecsPerItem>10</maxRecsPerItem>" + "<minRatedCount xsi:nil=\"true\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"/>" + "<nonPersonalizedSourceInfo>slopeone-nonpersonalized</nonPersonalizedSourceInfo>" + "<actionType>RATE</actionType><itemTypes/>" + "<viewType>COMMUNITY</viewType>" + "</slopeOneConfiguration>"; final String GET_ISRELATED_ASSOCTYPE_QUERY = "SELECT id FROM assoctype WHERE name = 'IS_RELATED' AND tenantId = ?"; int isRelatedAssocType = 0; // for tenants without a type 'IS_RELATED' this throws an exception - so catch!! try { isRelatedAssocType = getJdbcTemplate().queryForInt(GET_ISRELATED_ASSOCTYPE_QUERY, tenantId); } catch (EmptyResultDataAccessException erdar) { isRelatedAssocType = 0; } if (isRelatedAssocType == 0) { final String GET_MAX_ASSOCTYPE_QUERY = "SELECT MAX(id) FROM assoctype WHERE tenantId = ?"; isRelatedAssocType = getJdbcTemplate().queryForInt(GET_MAX_ASSOCTYPE_QUERY, tenantId) + 1; final String INSERT_ASSOCTYPE_QUERY = "INSERT INTO assoctype(tenantId, name, id, visible) VALUES (?, 'IS_RELATED', ?, b'1')"; getJdbcTemplate().update(INSERT_ASSOCTYPE_QUERY, tenantId, isRelatedAssocType); } // add sourcetype for slopeone where missing final String GET_SLOPEONE_SOURCETYPE_QUERY = "SELECT id FROM sourcetype WHERE name = 'http://www.easyrec.org/plugins/slopeone/0.96' AND tenantId = ?"; int slopeOneSourceType = 0; // for tenants without a type 'http://www.easyrec.org/plugins/slopeone/0.96' this throws an exception - so catch!! try { slopeOneSourceType = getJdbcTemplate().queryForInt(GET_SLOPEONE_SOURCETYPE_QUERY, tenantId); } catch (EmptyResultDataAccessException erdar) { slopeOneSourceType = 0; } if (slopeOneSourceType == 0) { // this means sourcetype not found, so update final String GET_MAX_SOURCETYPE_QUERY = "SELECT MAX(id) FROM sourcetype WHERE tenantId = ?"; slopeOneSourceType = getJdbcTemplate().queryForInt(GET_MAX_SOURCETYPE_QUERY, tenantId) + 1; final String INSERT_SOURCETYPE_QUERY = "INSERT INTO sourcetype(tenantId, name, id) VALUES (?, 'http://www.easyrec.org/plugins/slopeone/0.96', ?)"; getJdbcTemplate().update(INSERT_SOURCETYPE_QUERY, tenantId, slopeOneSourceType); } final String SLOPEONE_CONFIG_QUERY = "INSERT INTO plugin_configuration(tenantId, assocTypeId, pluginId, pluginVersion, name, configuration, active) VALUES " + "(?, ?, 'http://www.easyrec.org/plugins/slopeone', ?, 'Default Configuration', ?, b'1')"; getJdbcTemplate().update(SLOPEONE_CONFIG_QUERY, tenantId, isRelatedAssocType, currentVersion, slopeOneXmlConfig); } }
From source file:org.hyperic.hq.appdef.server.session.AppdefStatDAO.java
public ResourceTreeNode[] getNavMapDataForPlatform(AuthzSubject subject, final Platform plat) throws SQLException { final ResourceTreeNode aPlatformNode = new ResourceTreeNode(plat.getName(), getAppdefTypeLabel(APPDEF_TYPE_PLATFORM, plat.getAppdefResourceType().getName()), plat.getEntityId(), ResourceTreeNode.RESOURCE); String falseStr = DBUtil.getBooleanValue(false, jdbcTemplate.getDataSource().getConnection()); StringBuffer buf = new StringBuffer(); buf.append("SELECT svr_svrt_svc_svct.server_id, ").append("svr_svrt_svc_svct.server_name, ") .append(" svr_svrt_svc_svct.server_type_id, ").append("svr_svrt_svc_svct.server_type_name, ") .append(" svr_svrt_svc_svct.service_id, ").append("svr_svrt_svc_svct.service_name, ") .append(" svr_svrt_svc_svct.service_type_id, ").append("svr_svrt_svc_svct.service_type_name ") .append("FROM (SELECT app.id as application_id, ").append("appsvc.service_id as service_id ") .append(" FROM EAM_APP_SERVICE appsvc "); if (isOracle8()) { buf.append(", ").append(TBL_APP).append(" app ") .append("WHERE app.id=appsvc.application_id(+) AND EXISTS (") .append(getResourceTypeSQL("app.id", subject.getId(), APPLICATION_RES_TYPE, APPLICATION_OP_VIEW_APPLICATION)) .append(") ) app_appsvc, "); } else {/*w ww . j a v a 2 s .c o m*/ buf.append("RIGHT JOIN ").append(TBL_APP).append(" app ON app.id=appsvc.application_id ") .append("WHERE EXISTS (").append(getResourceTypeSQL("app.id", subject.getId(), APPLICATION_RES_TYPE, APPLICATION_OP_VIEW_APPLICATION)) .append(") ) app_appsvc RIGHT JOIN "); } buf.append("(SELECT svr_svrt.server_id, ").append("svr_svrt.server_name, ") .append(" svr_svrt.server_type_id, ").append("svr_svrt.server_type_name, ") .append(" svc_svct.service_id, ").append("svc_svct.service_name, ") .append(" svc_svct.service_type_id, ").append("svc_svct.service_type_name ") .append(" FROM ( SELECT svc.id as service_id, ") .append(" res2.name as service_name, ") .append(" svct.id as service_type_id, ") .append(" svct.name as service_type_name,") .append(" svc.server_id as server_id ").append(" FROM ").append(TBL_SERVICE) .append("_TYPE svct, ").append(TBL_SERVICE).append(" svc ").append(" JOIN " + TBL_RES) .append(" res2 ON svc.resource_id = res2.id ").append(" WHERE svc.service_type_id=svct.id ") .append(" AND EXISTS (") .append(getResourceTypeSQL("svc.id", subject.getId(), SERVICE_RES_TYPE, SERVICE_OP_VIEW_SERVICE)) .append(") ) svc_svct "); if (isOracle8()) { buf.append(","); } else { buf.append(" RIGHT JOIN"); } buf.append(" ( SELECT svr.id as server_id, ").append(" res1.name as server_name, ") .append(" svrt.id as server_type_id,") .append(" svrt.name as server_type_name ").append(" FROM ") .append(TBL_SERVER).append("_TYPE svrt, ").append(TBL_SERVER).append(" svr ") .append(" JOIN " + TBL_RES).append(" res1 ON svr.resource_id = res1.id ") .append(" WHERE svr.platform_id=").append(plat.getId()) // exclude virtual server types from the navMap .append(" AND svrt.fvirtual = " + falseStr) .append(" AND svrt.id=svr.server_type_id ") .append(" AND EXISTS (") .append(getResourceTypeSQL("svr.id", subject.getId(), SERVER_RES_TYPE, SERVER_OP_VIEW_SERVER)) .append(") ) svr_svrt "); if (isOracle8()) { buf.append(" WHERE svr_svrt.server_id=svc_svct.server_id(+)").append(" ) svr_svrt_svc_svct ") .append("WHERE svr_svrt_svc_svct.service_id=app_appsvc.service_id(+)"); } else { buf.append(" ON svr_svrt.server_id=svc_svct.server_id ").append(" ) svr_svrt_svc_svct ") .append("ON svr_svrt_svc_svct.service_id=app_appsvc.service_id "); } buf.append(" ORDER BY svr_svrt_svc_svct.server_id, ").append("svr_svrt_svc_svct.server_type_id, ") .append(" svr_svrt_svc_svct.service_id, ").append("svr_svrt_svc_svct.service_type_id "); if (log.isDebugEnabled()) { log.debug(buf.toString()); } ResourceTreeNode[] platformNode = this.jdbcTemplate.query(buf.toString(), new ResultSetExtractor<ResourceTreeNode[]>() { public ResourceTreeNode[] extractData(ResultSet rs) throws SQLException, DataAccessException { final Set<ResourceTreeNode> servers = new HashSet<ResourceTreeNode>(); final Set<ResourceTreeNode> services = new HashSet<ResourceTreeNode>(); while (rs.next()) { int thisSvrId = rs.getInt(1); String thisServerName = rs.getString(2); int thisServerTypeId = rs.getInt(3); String thisServerTypeName = rs.getString(4); int thisSvcId = rs.getInt(5); String thisServiceName = rs.getString(6); int thisServiceTypeId = rs.getInt(7); String thisServiceTypeName = rs.getString(8); if (thisServerTypeName != null) { servers.add(new ResourceTreeNode(thisServerName, getAppdefTypeLabel(APPDEF_TYPE_SERVER, thisServerTypeName), AppdefEntityID.newServerID(new Integer(thisSvrId)), plat.getEntityId(), thisServerTypeId)); } if (thisServiceTypeName != null) { services.add(new ResourceTreeNode(thisServiceName, getAppdefTypeLabel(APPDEF_TYPE_SERVICE, thisServiceTypeName), AppdefEntityID.newServiceID(new Integer(thisSvcId)), AppdefEntityID.newServerID(new Integer(thisSvrId)), thisServiceTypeId)); } } // XXX Leave out service data No current way to represent it // (ResourceTreeNode[]) serviceMap.values() // .toArray(new ResourceTreeNode[0]); aPlatformNode.setSelected(true); ResourceTreeNode[] svrNodes = servers.toArray(new ResourceTreeNode[0]); ResourceTreeNode.alphaSortNodes(svrNodes, true); aPlatformNode.addUpChildren(svrNodes); return new ResourceTreeNode[] { aPlatformNode }; } }); return platformNode; }
From source file:org.hyperic.hq.appdef.server.session.AppdefStatDAO.java
public ResourceTreeNode[] getNavMapDataForServer(AuthzSubject subject, final Server server) throws SQLException { StringBuffer buf = new StringBuffer(); buf.append("SELECT svc_svct_svr_plat.platform_id, ").append("svc_svct_svr_plat.platform_name, ") .append(" svc_svct_svr_plat.platform_type_id, ") .append("svc_svct_svr_plat.platform_type_name, ").append(" svc_svct_svr_plat.service_id, ") .append("svc_svct_svr_plat.service_name, ").append(" svc_svct_svr_plat.service_type_id, ") .append("svc_svct_svr_plat.service_type_name ").append("FROM (SELECT app.id as application_id, ") .append("appsvc.service_id as service_id ").append(" FROM EAM_APP_SERVICE appsvc "); if (isOracle()) { buf.append(" , ").append(TBL_APP).append(" app ") .append("WHERE app.id=appsvc.application_id(+) AND EXISTS (") .append(getResourceTypeSQL("app.id", subject.getId(), APPLICATION_RES_TYPE, APPLICATION_OP_VIEW_APPLICATION)) .append(") ) app_appsvc, "); } else {//from w w w .j a v a2 s .c om buf.append(" RIGHT JOIN ").append(TBL_APP).append(" app ON app.id=appsvc.application_id ") .append(" WHERE EXISTS (").append(getResourceTypeSQL("app.id", subject.getId(), APPLICATION_RES_TYPE, APPLICATION_OP_VIEW_APPLICATION)) .append(") ) app_appsvc RIGHT JOIN "); } buf.append(" (SELECT svc_svct.service_id, ").append("svc_svct.service_name, ") .append(" svc_svct.service_type_id, ").append("svc_svct.service_type_name, ") .append(" plat.id as platform_id, ").append("res0.name as platform_name, ") .append(" platt.id as platform_type_id, ").append("platt.name as platform_type_name ") .append(" FROM (SELECT svc.id as service_id, ") .append(" res2.name as service_name, ") .append(" svct.id as service_type_id,") .append(" svct.name as service_type_name,") .append(" svc.server_id as server_id ").append(" FROM ").append(TBL_SERVICE) .append("_TYPE svct, ").append(TBL_SERVICE).append(" svc ").append(" JOIN " + TBL_RES) .append(" res2 ON svc.resource_id = res2.id ") .append(" WHERE svc.service_type_id=svct.id AND EXISTS (") .append(getResourceTypeSQL("svc.id", subject.getId(), SERVICE_RES_TYPE, SERVICE_OP_VIEW_SERVICE)) .append(") ) svc_svct "); if (isOracle()) { buf.append(" ," + TBL_SERVER + " svr, "); } else { buf.append(" RIGHT JOIN " + TBL_SERVER + " svr ").append("ON svc_svct.server_id=svr.id, "); } buf.append(TBL_PLATFORM).append("_TYPE platt, ").append(TBL_PLATFORM).append(" plat JOIN ").append(TBL_RES) .append(" res0 ON plat.resource_id = res0.id").append(" WHERE svr.id=").append(server.getId()) .append(" AND platt.id=plat.platform_type_id ") .append(" AND plat.id=svr.platform_id AND EXISTS (").append(getResourceTypeSQL("plat.id", subject.getId(), PLATFORM_RES_TYPE, PLATFORM_OP_VIEW_PLATFORM)) .append(") "); if (isOracle()) { buf.append(" AND svr.id=svc_svct.server_id(+) ").append(" ) svc_svct_svr_plat ") .append(" WHERE svc_svct_svr_plat.service_id=app_appsvc.service_id(+)"); } else { buf.append(" ) svc_svct_svr_plat ").append(" ON svc_svct_svr_plat.service_id=app_appsvc.service_id "); } buf.append("order by service_type_id "); StopWatch timer = new StopWatch(); final Map<Integer, ResourceTreeNode> serviceMap = new HashMap<Integer, ResourceTreeNode>(); final ResourceTreeNode aServerNode = new ResourceTreeNode(server.getName(), getAppdefTypeLabel(server.getEntityId().getType(), server.getAppdefResourceType().getName()), server.getEntityId(), ResourceTreeNode.RESOURCE); ResourceTreeNode[] serverNode = this.jdbcTemplate.query(buf.toString(), new ResultSetExtractor<ResourceTreeNode[]>() { public ResourceTreeNode[] extractData(ResultSet rs) throws SQLException, DataAccessException { int thisPlatId = 0; ResourceTreeNode aPlatformNode = null; while (rs.next()) { if (thisPlatId == 0) { thisPlatId = rs.getInt(1); String thisPlatformName = rs.getString(2); int thisPlatformTypeId = rs.getInt(3); String thisPlatformTypeName = rs.getString(4); aPlatformNode = new ResourceTreeNode(thisPlatformName, getAppdefTypeLabel(APPDEF_TYPE_PLATFORM, thisPlatformTypeName), AppdefEntityID.newPlatformID(new Integer(thisPlatId)), (AppdefEntityID) null, thisPlatformTypeId); } int thisSvcId = rs.getInt(5); String thisServiceName = rs.getString(6); int thisServiceTypeId = rs.getInt(7); String thisServiceTypeName = rs.getString(8); if (thisServiceName != null) { serviceMap.put(new Integer(thisSvcId), new ResourceTreeNode(thisServiceName, getAppdefTypeLabel(APPDEF_TYPE_SERVICE, thisServiceTypeName), AppdefEntityID.newServiceID(new Integer(thisSvcId)), server.getEntityId(), thisServiceTypeId)); } } aServerNode.setSelected(true); ResourceTreeNode[] services = serviceMap.values().toArray(new ResourceTreeNode[0]); ResourceTreeNode.alphaSortNodes(services, true); aServerNode.addUpChildren(services); // aPlatformNode can be null if user is unauthz if (aPlatformNode != null) { aServerNode.addDownChild(aPlatformNode); } return new ResourceTreeNode[] { aServerNode }; } }); if (log.isDebugEnabled()) { log.debug("getNavMapDataForServer() executed in: " + timer); log.debug("SQL: " + buf); } return serverNode; }
From source file:org.hyperic.hq.appdef.server.session.AppdefStatDAO.java
public ResourceTreeNode[] getNavMapDataForService(AuthzSubject subject, final Service service) throws SQLException { final String trueStr = DBUtil.getBooleanValue(true, jdbcTemplate.getDataSource().getConnection()); StringBuffer buf = new StringBuffer(); buf.append("SELECT plat.platform_id, ").append("platform_name, ").append(" platform_type_name, ") .append("asvc_svr.server_id, ").append(" asvc_svr.server_name, ") .append("asvc_svr.server_type_name, ").append(" asvc_svr.application_id, ") .append("asvc_svr.application_name, ").append(" asvc_svr.application_type_name, ") .append("fvirtual ") .append("FROM (SELECT plat.id as platform_id, " + "res0.name as platform_name, " + "platt.name as platform_type_name " + "FROM " + TBL_PLATFORM + "_TYPE platt, " + TBL_PLATFORM + " plat JOIN " + TBL_RES + " res0 ON plat.resource_id = res0.id " + "WHERE plat.platform_type_id=platt.id AND " + " EXISTS (") .append(getResourceTypeSQL("plat.id", subject.getId(), PLATFORM_RES_TYPE, PLATFORM_OP_VIEW_PLATFORM)) .append(")) plat "); if (isOracle8()) { buf.append(", "); } else {//from w ww .ja v a 2 s. co m buf.append("RIGHT JOIN "); } buf.append("( SELECT asvc.application_id, ").append("asvc.application_name, ") .append(" asvc.application_type_name, ").append("svr.id as server_id, ") .append(" res1.name as server_name, ").append(" svrt.name as server_type_name, ") .append(" svr.platform_id, fvirtual ").append(" FROM ").append(TBL_RES) .append(" res1 JOIN ").append(TBL_SERVER).append(" svr ON res1.id = svr.resource_id "); if (isOracle8()) { buf.append(" , "); } else { buf.append(" RIGHT JOIN "); } buf.append(" (SELECT app_appsvc.application_id, ").append("app_appsvc.application_name, ") .append(" app_appsvc.application_type_name, ").append("svc.server_id as server_id ") .append(" FROM (SELECT app.id as application_id, ").append("r.name as application_name, ") .append(" EAM_APPLICATION_TYPE.name as application_type_name, ") .append(" appsvc.service_id as service_id ") .append(" FROM EAM_APP_SERVICE appsvc "); if (isOracle8()) { buf.append(" , ").append(TBL_APP).append(" app, EAM_APPLICATION_TYPE, ").append(TBL_RES).append(" r ") .append(" WHERE app.id=appsvc.application_id(+) ") .append(" AND EAM_APPLICATION_TYPE.id=app.application_type_id ") .append(" AND app.resource_id = r.id AND EXISTS (") .append(getResourceTypeSQL("app.id", subject.getId(), APPLICATION_RES_TYPE, APPLICATION_OP_VIEW_APPLICATION)) .append(") ) app_appsvc, ").append(TBL_SERVICE) .append(" svc WHERE svc.id=app_appsvc.service_id(+) AND svc.id=").append(service.getId()) .append(") asvc "); } else { buf.append(" RIGHT JOIN ").append(TBL_APP).append(" app ON app.id=appsvc.application_id ") .append(" RIGHT JOIN ").append(TBL_RES).append(" r ON app.resource_id = r.id, ") .append(" EAM_APPLICATION_TYPE ") .append(" WHERE EAM_APPLICATION_TYPE.id=app.application_type_id ").append(" AND EXISTS (") .append(getResourceTypeSQL("app.id", subject.getId(), APPLICATION_RES_TYPE, APPLICATION_OP_VIEW_APPLICATION)) .append(") ) app_appsvc RIGHT JOIN ").append(TBL_SERVICE) .append(" svc ON svc.id=app_appsvc.service_id ").append(" WHERE svc.id=") .append(service.getId()).append(") asvc "); } if (isOracle8()) { buf.append(" , ").append(TBL_SERVER).append("_TYPE svrt ").append(" WHERE svr.server_type_id=svrt.id ") .append(" AND asvc.server_id=svr.id(+) ").append(" AND (fvirtual = ").append(trueStr) .append(" OR EXISTS (") .append(getResourceTypeSQL("svr.id", subject.getId(), SERVER_RES_TYPE, SERVER_OP_VIEW_SERVER)) .append(")) ) asvc_svr, ").append(TBL_PLATFORM + "_TYPE platt ") .append("WHERE plat.platform_type_id=platt.id ") .append(" AND asvc_svr.platform_id=plat.id(+) AND EXISTS (") .append(getResourceTypeSQL("plat.id", subject.getId(), PLATFORM_RES_TYPE, PLATFORM_OP_VIEW_PLATFORM)) .append(") "); } else { buf.append(" ON asvc.server_id=svr.id, ").append(TBL_SERVER).append("_TYPE svrt ") .append(" WHERE svr.server_type_id=svrt.id ").append(" AND (fvirtual = ").append(trueStr) .append(" OR EXISTS (") .append(getResourceTypeSQL("svr.id", subject.getId(), SERVER_RES_TYPE, SERVER_OP_VIEW_SERVER)) .append(")) ) asvc_svr ").append(" ON asvc_svr.platform_id = plat.platform_id"); } StopWatch timer = new StopWatch(); ResourceTreeNode[] serviceNode = this.jdbcTemplate.query(buf.toString(), new ResultSetExtractor<ResourceTreeNode[]>() { public ResourceTreeNode[] extractData(ResultSet rs) throws SQLException, DataAccessException { ResourceTreeNode aPlatformNode = null; ResourceTreeNode aServerNode = null; ResourceTreeNode aServiceNode = new ResourceTreeNode(service.getName(), getAppdefTypeLabel(service.getEntityId().getType(), service.getAppdefResourceType().getName()), service.getEntityId(), ResourceTreeNode.RESOURCE); Map<Integer, ResourceTreeNode> appMap = new HashMap<Integer, ResourceTreeNode>(); while (rs.next()) { int i = 1; int thisPlatId = rs.getInt(i++); String thisPlatformName = rs.getString(i++); String thisPlatformTypeName = rs.getString(i++); int thisSvrId = rs.getInt(i++); String thisServerName = rs.getString(i++); String thisServerTypeName = rs.getString(i++); int thisAppId = rs.getInt(i++); String thisApplicationName = rs.getString(i++); String thisApplicationDesc = rs.getString(i++); String virtualServer = rs.getString(i++); if (thisPlatformName != null) { aPlatformNode = new ResourceTreeNode(thisPlatformName, getAppdefTypeLabel(APPDEF_TYPE_PLATFORM, thisPlatformTypeName), AppdefEntityID.newPlatformID(new Integer(thisPlatId)), ResourceTreeNode.RESOURCE); } if (thisServerName != null && !trueStr.startsWith(virtualServer)) { aServerNode = new ResourceTreeNode(thisServerName, getAppdefTypeLabel(APPDEF_TYPE_SERVER, thisServerTypeName), AppdefEntityID.newServerID(new Integer(thisSvrId)), ResourceTreeNode.RESOURCE); } if (thisApplicationName != null) { appMap.put(new Integer(thisAppId), new ResourceTreeNode(thisApplicationName, getAppdefTypeLabel(AppdefEntityConstants.APPDEF_TYPE_APPLICATION, thisApplicationDesc), AppdefEntityID.newAppID(new Integer(thisAppId)), ResourceTreeNode.RESOURCE)); } } aServiceNode.setSelected(true); // server nodes and platform nodes can be null if user is // unauthz if (aServerNode != null) { if (aPlatformNode != null) { aServerNode.addDownChild(aPlatformNode); } aServiceNode.addDownChild(aServerNode); } else if (aPlatformNode != null) { aServiceNode.addDownChild(aPlatformNode); } ResourceTreeNode[] appNodes = appMap.values().toArray(new ResourceTreeNode[0]); ResourceTreeNode.alphaSortNodes(appNodes, true); aServiceNode.addUpChildren(appNodes); return new ResourceTreeNode[] { aServiceNode }; } }); if (log.isDebugEnabled()) { log.debug("getNavMapDataForService() executed in: " + timer); log.debug("SQL: " + buf); } return serviceNode; }