List of usage examples for org.springframework.jdbc.core ResultSetExtractor ResultSetExtractor
ResultSetExtractor
From source file:org.hyperic.hq.appdef.server.session.AppdefStatDAO.java
public ResourceTreeNode[] getNavMapDataForApplication(AuthzSubject subject, final Application app) throws SQLException { StringBuffer buf = new StringBuffer().append("SELECT appsvc.service_id, pm.name,") .append(" appsvc.service_type_id,").append(" svct.name as service_type_name,") .append(" appsvc.application_id, appsvc.group_id").append(" FROM EAM_APP_SERVICE appsvc, ") .append(TBL_SERVICE).append("_TYPE svct, ").append(TBL_GROUP).append(" grp, (") .append(getPermGroupSQL(subject.getId())).append(") pm") .append(" WHERE svct.id = appsvc.service_type_id AND ") .append(" grp.id = appsvc.group_id AND pm.group_id = grp.id") .append(" AND appsvc.application_id = ").append(app.getId()).append(" UNION ALL ") .append("SELECT appsvc.service_id, res2.name,").append(" appsvc.service_type_id,") .append(" svct.name as service_type_name,").append(" appsvc.application_id, appsvc.group_id") .append(" FROM EAM_APP_SERVICE appsvc, ").append(TBL_SERVICE).append("_TYPE svct, (") .append(getPermServiceSQL(subject.getId())).append(") pm, ").append(TBL_SERVICE) .append(" svc JOIN ").append(TBL_RES).append(" res2 ON svc.resource_id = res2.id ") .append(" WHERE svct.id = appsvc.service_type_id AND ").append(" svc.id = appsvc.service_id AND ") .append(" pm.service_id = svc.id AND ").append(" appsvc.application_id = ").append(app.getId()) .append(" ORDER BY service_type_id, service_id"); if (log.isDebugEnabled()) { log.debug(buf.toString());/*from w w w .j a v a 2s .c om*/ } StopWatch timer = new StopWatch(); ResourceTreeNode[] appNode = this.jdbcTemplate.query(buf.toString(), new ResultSetExtractor<ResourceTreeNode[]>() { public ResourceTreeNode[] extractData(ResultSet rs) throws SQLException, DataAccessException { Map<String, ResourceTreeNode> svcMap = new HashMap<String, ResourceTreeNode>(); ResourceTreeNode appNode = new ResourceTreeNode(app.getName(), getAppdefTypeLabel(app.getEntityId().getType(), app.getAppdefResourceType().getName()), app.getEntityId(), ResourceTreeNode.RESOURCE); int svc_id_col = rs.findColumn("service_id"), name_col = rs.findColumn("name"), service_type_col = rs.findColumn("service_type_id"), type_name_col = rs.findColumn("service_type_name"), group_id_col = rs.findColumn("group_id"); while (rs.next()) { int serviceId = rs.getInt(svc_id_col); String serviceName = rs.getString(name_col); int serviceTypeId = rs.getInt(service_type_col); String serviceTypeName = rs.getString(type_name_col); int groupId = rs.getInt(group_id_col); String thisGroupName = rs.getString(name_col); // means that column is null, hence row is not a group if (groupId == 0) { thisGroupName = null; } else { serviceName = null; } if (thisGroupName != null) { String key = APPDEF_TYPE_GROUP + "-" + groupId; svcMap.put(key, new ResourceTreeNode(thisGroupName, getAppdefTypeLabel(APPDEF_TYPE_GROUP, serviceTypeName), AppdefEntityID.newGroupID(new Integer(groupId)), ResourceTreeNode.CLUSTER)); } else if (serviceName != null) { String key = APPDEF_TYPE_SERVICE + "-" + serviceId; svcMap.put(key, new ResourceTreeNode(serviceName, getAppdefTypeLabel(APPDEF_TYPE_SERVICE, serviceTypeName), AppdefEntityID.newServiceID(new Integer(serviceId)), app.getEntityId(), serviceTypeId)); } } appNode.setSelected(true); ResourceTreeNode[] svcNodes = svcMap.values().toArray(new ResourceTreeNode[0]); ResourceTreeNode.alphaSortNodes(svcNodes); appNode.addDownChildren(svcNodes); return new ResourceTreeNode[] { appNode }; } }); if (log.isDebugEnabled()) { log.debug("getNavMapDataForApplication() executed in: " + timer); log.debug("SQL: " + buf); } return appNode; }
From source file:org.hyperic.hq.appdef.server.session.AppdefStatDAO.java
public ResourceTreeNode[] getNavMapDataForAutoGroup(AuthzSubject subject, final AppdefEntityID[] parents, AppdefResourceType type, final int pEntityType, final int cEntityType) throws AppdefEntityNotFoundException, PermissionException, SQLException { final String sqlStmt; String bindMarkerStr = ""; String authzResName;//from ww w. java 2s. c om String authzOpName; final int appdefTypeUndefined = -1; List<ResourceTreeNode> parentNodes = null; // If the auto-group has parents, fetch the resources if (parents != null) { parentNodes = new ArrayList<ResourceTreeNode>(parents.length); for (int x = 0; x < parents.length; x++) { AppdefEntityValue av = new AppdefEntityValue(parents[x], subject); parentNodes.add(new ResourceTreeNode(av.getName(), getAppdefTypeLabel(pEntityType, av.getTypeName()), parents[x], ResourceTreeNode.RESOURCE)); } } // Platforms don't have a auto-group parents if (pEntityType != appdefTypeUndefined) { for (int x = 0; x < parents.length; x++) { bindMarkerStr += (x < parents.length - 1) ? "?," : "?"; } } final String res_join = " JOIN " + TBL_RES + " res on resource_id = res.id "; final String platAGSql = "SELECT p.id as platform_id, res.name as platform_name, " + " pt.id as platform_type_id, pt.name as platform_type_name " + "FROM " + TBL_PLATFORM + "_TYPE pt, " + TBL_PLATFORM + " p " + res_join + " WHERE p.platform_type_id=pt.id AND platform_type_id=" + type.getId() + " AND " + "EXISTS (" + getResourceTypeSQL("p.id", subject.getId(), PLATFORM_RES_TYPE, PLATFORM_OP_VIEW_PLATFORM) + ") "; final String svrAGSql = "SELECT s.id as server_id, res.name as server_name, " + " st.id as server_type_id, st.name as server_type_name " + "FROM " + TBL_SERVER + "_TYPE st, " + TBL_SERVER + " s " + res_join + " WHERE s.server_type_id=st.id AND platform_id in ( " + bindMarkerStr + " ) " + " AND server_type_id=" + type.getId() + " AND EXISTS (" + getResourceTypeSQL("s.id", subject.getId(), SERVER_RES_TYPE, SERVER_OP_VIEW_SERVER) + ") "; final String svcAGSql = "SELECT s.id as service_id, res.name as service_name, " + " st.id as service_type_id, st.name as service_type_name " + "FROM " + TBL_SERVICE + "_TYPE st, " + TBL_SERVICE + " s " + res_join + " WHERE s.service_type_id=st.id AND s.server_id in ( " + bindMarkerStr + " ) AND " + "s.service_type_id=" + type.getId() + " AND EXISTS (" + getResourceTypeSQL("s.id", subject.getId(), SERVICE_RES_TYPE, SERVICE_OP_VIEW_SERVICE) + ") "; final String appSvcAGSql = "SELECT s.id as service_id, res.name as service_name, " + " st.id as service_type_id, st.name as service_type_name " + "FROM " + TBL_SERVICE + "_TYPE st, EAM_APP_SERVICE aps, " + TBL_SERVICE + " s " + res_join + " WHERE s.service_type_id=st.id and s.id=aps.service_id AND " + "aps.application_id in ( " + bindMarkerStr + " ) AND " + "s.service_type_id=" + type.getId() + " AND EXISTS (" + getResourceTypeSQL("s.id", subject.getId(), SERVICE_RES_TYPE, SERVICE_OP_VIEW_SERVICE) + ") "; switch (pEntityType) { case APPDEF_TYPE_PLATFORM: sqlStmt = svrAGSql; authzResName = AuthzConstants.serverResType; authzOpName = AuthzConstants.serverOpViewServer; break; case APPDEF_TYPE_SERVER: sqlStmt = svcAGSql; authzResName = AuthzConstants.serviceResType; authzOpName = AuthzConstants.serviceOpViewService; break; case (AppdefEntityConstants.APPDEF_TYPE_APPLICATION): sqlStmt = appSvcAGSql; authzResName = AuthzConstants.serviceResType; authzOpName = AuthzConstants.serviceOpViewService; break; case (appdefTypeUndefined): sqlStmt = platAGSql; authzResName = AuthzConstants.platformResType; authzOpName = AuthzConstants.platformOpViewPlatform; break; default: throw new IllegalArgumentException("No auto-group support " + "for specified type"); } if (log.isDebugEnabled()) { log.debug(sqlStmt); } final ResourceTreeNode agNode = new ResourceTreeNode(type.getName(), getAppdefTypeLabel(cEntityType, type.getName()), parents, type.getId().intValue(), ResourceTreeNode.AUTO_GROUP); final Set<ResourceTreeNode> entitySet = new HashSet<ResourceTreeNode>(); final List<ResourceTreeNode> parentNodeList = parentNodes; StopWatch timer = new StopWatch(); ResourceTreeNode[] groupNode = jdbcTemplate.query(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement stmt = con.prepareStatement(sqlStmt); if (pEntityType != appdefTypeUndefined) { for (int x = 0; x < parents.length; x++) { stmt.setInt(x + 1, parents[x].getID()); } } return stmt; } }, new ResultSetExtractor<ResourceTreeNode[]>() { public ResourceTreeNode[] extractData(ResultSet rs) throws SQLException, DataAccessException { while (rs.next()) { int thisEntityId = rs.getInt(1); String thisEntityName = rs.getString(2); String thisEntityTypeName = rs.getString(4); entitySet.add(new ResourceTreeNode(thisEntityName, getAppdefTypeLabel(cEntityType, thisEntityTypeName), new AppdefEntityID(cEntityType, thisEntityId), ResourceTreeNode.RESOURCE)); } agNode.setSelected(true); if (parentNodeList != null) { ResourceTreeNode[] parNodeArr = parentNodeList.toArray(new ResourceTreeNode[0]); ResourceTreeNode.alphaSortNodes(parNodeArr, true); agNode.addUpChildren(parNodeArr); } ResourceTreeNode[] members = entitySet.toArray(new ResourceTreeNode[0]); ResourceTreeNode.alphaSortNodes(members); agNode.addDownChildren(members); return new ResourceTreeNode[] { agNode }; } }); if (log.isDebugEnabled()) { log.debug("getNavMapDataForAutoGroup() executed in: " + timer); log.debug("SQL: " + sqlStmt); int i; for (i = 0; i < parents.length; i++) { log.debug("Arg " + (i + 1) + ": " + parents[i].getID()); } i = 1; log.debug("Arg " + (i++) + ": " + type.getId()); log.debug("Arg " + (i++) + ": " + subject.getId()); log.debug("Arg " + (i++) + ": " + subject.getId()); log.debug("Arg " + (i++) + ": " + authzResName); log.debug("Arg " + (i++) + ": " + authzOpName); } return groupNode; }
From source file:org.hyperic.hq.measurement.server.session.DataCompressionDAO.java
/** * Get the most recent measurement.//w ww. jav a 2s .c om */ public long getMaxTimestamp(String dataTable) { Connection connection; try { connection = jdbcTemplate.getDataSource().getConnection(); } catch (SQLException e) { throw jdbcTemplate.getExceptionTranslator().translate("Obtaining connection from DataSource", null, e); } String sql; try { if (DBUtil.isPostgreSQL(connection)) { // Postgres handles this much better sql = "SELECT timestamp FROM " + dataTable + " ORDER BY timestamp DESC LIMIT 1"; } else { sql = "SELECT MAX(timestamp) FROM " + dataTable; } } catch (SQLException e) { throw jdbcTemplate.getExceptionTranslator().translate("Determining if the database is PostGres", null, e); } return jdbcTemplate.query(sql, new ResultSetExtractor<Long>() { public Long extractData(ResultSet rs) throws SQLException, DataAccessException { if (rs.next()) { return rs.getLong(1); } else { // New installation return 0l; } } }); }
From source file:org.jasig.portal.jgroups.protocols.JdbcPingDao.java
@Override public Map<Address, PhysicalAddress> getAddresses(String clusterName) { if (!isReady()) { return Collections.emptyMap(); }// w w w . j a va2s .c om 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) { logger.warnDebug("Ignoring address result due to data parsing error", e); } } logger.debug("Found {} addresses in cluster: {}", result.size(), result); return result; } }); }
From source file:org.kuali.kfs.gl.dataaccess.impl.IcrEncumbranceDaoJdbc.java
/** * @see org.kuali.kfs.gl.dataaccess.IcrEncumbranceDao#buildIcrEncumbranceFeed() *//*w w w.j a va 2 s . c o m*/ @Override public void buildIcrEncumbranceFeed(Integer fiscalYear, final String fiscalPeriod, final String icrEncumbOriginCode, final Collection<String> icrEncumbBalanceTypes, final Collection<String> icrCostTypes, final String[] expenseObjectTypes, final String costShareSubAccountType, final Writer fw) throws IOException { final String rateSql = "select distinct t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, " + getDbPlatform().getIsNullFunction("t3.fin_series_id", "t2.fin_series_id") + " fin_series_id, " + getDbPlatform().getIsNullFunction("t3.icr_typ_cd", "t2.acct_icr_typ_cd") + " acct_icr_typ_cd " + "from gl_encumbrance_t t1 join ca_account_t t2 on (t1.fin_coa_cd = t2.fin_coa_cd and t1.account_nbr = t2.account_nbr) " + "left join ca_a21_sub_acct_t t3 on (t1.fin_coa_cd = t3.fin_coa_cd and t1.account_nbr = t3.account_nbr and t1.sub_acct_nbr = t3.sub_acct_nbr) " + "where t1.fin_balance_typ_cd in (" + inString(icrEncumbBalanceTypes.size()) + ") and t1.fs_origin_cd <> ? " + "and t1.univ_fiscal_yr >= ? " + "and (t3.sub_acct_typ_cd is null or t3.sub_acct_typ_cd <> ?) " + "and acct_icr_typ_cd not in (" + inString(icrCostTypes.size()) + ")"; List<Object> queryArguments = new ArrayList<Object>(); for (String balanceType : icrEncumbBalanceTypes) { queryArguments.add(balanceType); } queryArguments.add(icrEncumbOriginCode); queryArguments.add(fiscalYear); queryArguments.add(costShareSubAccountType); for (String icrCostType : icrCostTypes) { queryArguments.add(icrCostType); } //prevent SQL errors in the event that the INDIRECT_COST_TYPES parameter contains no value if (icrCostTypes.size() < 1) { queryArguments.add("1"); } getJdbcTemplate().query(rateSql, queryArguments.toArray(), new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { String newLine = System.getProperty("line.separator"); while (rs.next()) { String fin_series_id = rs.getString("fin_series_id"); String acct_icr_typ_cd = rs.getString("acct_icr_typ_cd"); String fiscalYear = rs.getString("univ_fiscal_yr"); String chartCode = rs.getString("fin_coa_cd"); String accountNbr = rs.getString("account_nbr"); String subAccountNbr = rs.getString("sub_acct_nbr"); List<Object> encArgs = new ArrayList<Object>(); encArgs.add(fin_series_id); encArgs.add(acct_icr_typ_cd); encArgs.add(fiscalYear); encArgs.add(chartCode); encArgs.add(accountNbr); encArgs.add(subAccountNbr); for (String balanceType : icrEncumbBalanceTypes) { encArgs.add(balanceType); } encArgs.add(icrEncumbOriginCode); for (String expenseObjectType : expenseObjectTypes) { encArgs.add(expenseObjectType); } executeEncumbranceSql(fiscalPeriod, icrEncumbOriginCode, icrEncumbBalanceTypes, expenseObjectTypes, encArgs.toArray(), fw); } } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } return null; } }); }
From source file:org.kuali.kfs.gl.dataaccess.impl.IcrEncumbranceDaoJdbc.java
/** * Retrieves and formats ICR Encumbrance information and writes output records to the file writer * * @param fiscalPeriod the current fiscal period * @param icrEncumbOriginCode the ICR origin code - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_ORIGINATION * @param icrEncumbBalanceTypes a list of balance types - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_BALANCE_TYPES * @param expenseObjectTypes a list of expense object types * @param encArgs a list of query arguments * @param fw the file writer/*from w w w. j a v a 2 s .c o m*/ */ protected void executeEncumbranceSql(final String fiscalPeriod, final String icrEncumbOriginCode, final Collection<String> icrEncumbBalanceTypes, final String[] expenseObjectTypes, Object[] encArgs, final Writer fw) { final String encumbSql = "select t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, t5.fin_object_cd, t1.fin_balance_typ_cd, " + "t1.fdoc_typ_cd, t1.fdoc_nbr, " + "sum(" + getDbPlatform().getIsNullFunction("t1.acln_encum_amt - t1.acln_encum_cls_amt", "0") + " * " + getDbPlatform().getIsNullFunction("t5.awrd_icr_rate_pct", "0") + " * .01) encumb_amt " + "from gl_encumbrance_t t1 " + "join ca_icr_auto_entr_t t5 on t5.fin_series_id = ? and t5.univ_fiscal_yr = t1.univ_fiscal_yr " + "and t5.trn_debit_crdt_cd = 'D' " + "join ca_object_code_t t4 on t4.univ_fiscal_yr = t1.univ_fiscal_yr and t4.fin_coa_cd = t1.fin_coa_cd and t4.fin_object_cd = t1.fin_object_cd " + "where not exists (select 1 from ca_icr_excl_type_t where acct_icr_typ_cd = ? " + "and acct_icr_excl_typ_actv_ind = 'Y' and fin_object_cd = t1.fin_object_cd) " + "and t1.univ_fiscal_yr = ? and t1.fin_coa_cd = ? and t1.account_nbr = ? and t1.sub_acct_nbr = ? " + "and t1.fin_balance_typ_cd in (" + inString(icrEncumbBalanceTypes.size()) + ") and t1.fs_origin_cd <> ? " + "and t4.fin_obj_typ_cd in (" + inString(expenseObjectTypes.length) + ") group by t1.univ_fiscal_yr, t1.fin_coa_cd, t1.account_nbr, t1.sub_acct_nbr, t5.fin_object_cd, t1.fin_balance_typ_cd, " + "t1.fdoc_typ_cd, t1.fdoc_nbr"; getJdbcTemplate().query(encumbSql, encArgs, new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { String newLine = System.getProperty("line.separator"); while (rs.next()) { String fiscalYear = rs.getString("univ_fiscal_yr"); String chartCode = rs.getString("fin_coa_cd"); String accountNbr = rs.getString("account_nbr"); String subAccountNbr = rs.getString("sub_acct_nbr"); String objectCode = rs.getString("fin_object_cd"); String balanceType = rs.getString("fin_balance_typ_cd"); String docType = rs.getString("fdoc_typ_cd"); String docNbr = rs.getString("fdoc_nbr"); KualiDecimal encumb_amt = new KualiDecimal(rs.getDouble("encumb_amt")); KualiDecimal current_amt = KualiDecimal.ZERO; Object[] icrArgs = new String[9]; icrArgs[0] = fiscalYear; icrArgs[1] = chartCode; icrArgs[2] = accountNbr; icrArgs[3] = subAccountNbr; icrArgs[4] = objectCode; icrArgs[5] = balanceType; icrArgs[6] = docType; icrArgs[7] = docNbr; icrArgs[8] = icrEncumbOriginCode; Double icrAmount = getCurrentEncumbranceAmount(icrArgs); if (icrAmount != null) { current_amt = new KualiDecimal(icrAmount); } KualiDecimal new_encumb_amt = encumb_amt.subtract(current_amt); if (new_encumb_amt.isZero()) { // ignore zero dollar amounts continue; } icrArgs = new String[3]; icrArgs[0] = fiscalYear; icrArgs[1] = chartCode; icrArgs[2] = objectCode; String objectTypeCode = getICRObjectTypeCode(icrArgs); String desc = "ICR Encumbrance " + docType + " " + docNbr; String debitCreditInd = "D"; if (new_encumb_amt.isNegative()) { debitCreditInd = "C"; } fw.write("" + fiscalYear // Fiscal year 1-4 + chartCode // Chart code 5-6 + accountNbr // Account Number 7-13 + StringUtils.rightPad(subAccountNbr, 5)// Sub Account 14-18 + objectCode // Object Code 19-22 + "---" // Sub Object 23-25 + balanceType // balance type code + objectTypeCode // Object Type 28-29 + fiscalPeriod // Fiscal Period 30-31 + StringUtils.rightPad(docType, 4) // Document Type 32-35 + icrEncumbOriginCode // Origin Code 36-37 + StringUtils.rightPad(docNbr, 14) // Doc Number 38-51 + StringUtils.rightPad("", 5, '0') // Entry Seq Nbr 52-56 + StringUtils.rightPad(StringUtils.substring(desc, 0, 40), 40) // Description 57-96 + StringUtils.leftPad(new_encumb_amt.abs().toString(), 21, '0') // Amount 97-116 + debitCreditInd // Debit/Credit 117-117 + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) // Trans Date 118-127 + " " // Org Doc Nbr 128-137 + " " // Project Code 138-147 + " " // orig ref id 148-155 + " " // ref doc type 156-159 + " " // ref origin code 160-161 + " " // ref doc number 162-175 + " " // reversal date 176-185 + "D" // Enc update code 186-186 ); fw.write(newLine); fw.flush(); } } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } catch (IOException ex) { throw new RuntimeException(ex); } return null; } }); }
From source file:org.kuali.kfs.gl.dataaccess.impl.IcrEncumbranceDaoJdbc.java
/** * Returns the current encumbrance amount * * @param icrEncumbOriginCode the ICR origin code - system parameter INDIRECT_COST_RECOVERY_ENCUMBRANCE_ORIGINATION * @param icrArgs a list of query arguments * @return the current encumbrance amount if found, null otherwise *//*from w ww .ja v a 2 s .co m*/ protected Double getCurrentEncumbranceAmount(Object[] icrArgs) { final String icrSql = "select sum(" + getDbPlatform().getIsNullFunction("acln_encum_amt - acln_encum_cls_amt", "0") + ") current_amt " + "from gl_encumbrance_t where univ_fiscal_yr = ? and fin_coa_cd = ? and account_nbr = ? and sub_acct_nbr = ? and fin_object_cd = ? " + "and fin_balance_typ_cd = ? and fdoc_typ_cd = ? and fdoc_nbr = ? and fs_origin_cd = ?"; Double icrAmount = (Double) getJdbcTemplate().query(icrSql, icrArgs, new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { if (rs.next()) { return rs.getDouble("current_amt"); } return null; } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } } }); return icrAmount; }
From source file:org.kuali.kfs.gl.dataaccess.impl.IcrEncumbranceDaoJdbc.java
/** * Returns the object type code for the object code associated with the ICR Rate * * @param icrArgs a list of query arguments * @return the object type code if found, null otherwise *//*from w w w . j av a 2s . c o m*/ protected String getICRObjectTypeCode(Object[] icrArgs) { final String icrSql = "select fin_obj_typ_cd " + "from ca_object_code_t where univ_fiscal_yr = ? and fin_coa_cd = ? and fin_object_cd = ?"; String objectTypeCode = (String) getJdbcTemplate().query(icrSql, icrArgs, new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { try { if (rs.next()) { return rs.getString("fin_obj_typ_cd"); } return null; } catch (SQLException e) { throw new RuntimeException(e); } catch (DataAccessException ed) { throw new RuntimeException(ed); } } }); return objectTypeCode; }
From source file:org.kuali.kfs.gl.dataaccess.impl.TrialBalanceDaoJdbc.java
@Override public List<TrialBalanceReport> findBalanceByFields(String selectedFiscalYear, String chartCode, String periodCode) {//from w w w.j av a 2 s . com final List<TrialBalanceReport> report = new ArrayList<TrialBalanceReport>(); List<Object> queryArguments = new ArrayList<Object>(2); String YTDQuery = buildYTDQueryString(periodCode); StringBuilder queryBuilder = new StringBuilder(); queryBuilder.append("SELECT A0.FIN_OBJECT_CD, A0.FIN_COA_CD, A1.FIN_OBJ_CD_NM, A2.FIN_OBJTYP_DBCR_CD,"); queryBuilder.append(YTDQuery + " AS YTD "); queryBuilder.append( "FROM GL_BALANCE_T A0 JOIN CA_OBJECT_CODE_T A1 on A1.FIN_COA_CD = A0.FIN_COA_CD AND A1.UNIV_FISCAL_YR = A0.UNIV_FISCAL_YR and A1.FIN_OBJECT_CD = A0.FIN_OBJECT_CD "); queryBuilder.append("JOIN CA_OBJ_TYPE_T A2 on A2.FIN_OBJ_TYP_CD = A1.FIN_OBJ_TYP_CD "); queryBuilder.append("JOIN CA_ACCTG_CTGRY_T A3 on A3.ACCTG_CTGRY_CD = A2.ACCTG_CTGRY_CD "); queryBuilder.append("WHERE A0.FIN_BALANCE_TYP_CD = 'AC' "); queryBuilder.append("AND A0.UNIV_FISCAL_YR = ? "); queryArguments.add(selectedFiscalYear); if (StringUtils.isNotBlank(chartCode)) { queryBuilder.append("AND A0.FIN_COA_CD=? "); queryArguments.add(chartCode); } queryBuilder.append( "GROUP BY A0.FIN_OBJECT_CD, A0.FIN_COA_CD, A1.FIN_OBJ_CD_NM, A2.FIN_OBJTYP_DBCR_CD, A3.FIN_REPORT_SORT_CD "); queryBuilder.append("HAVING " + YTDQuery + " <> 0 "); queryBuilder.append("ORDER BY A0.FIN_COA_CD, A3.FIN_REPORT_SORT_CD, A0.FIN_OBJECT_CD"); getJdbcTemplate().query(queryBuilder.toString(), queryArguments.toArray(), new ResultSetExtractor() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { TrialBalanceReport reportLine = null; KualiDecimal ytdAmount = null; KualiDecimal totalDebit = KualiDecimal.ZERO; KualiDecimal totalCredit = KualiDecimal.ZERO; String objectTypeDebitCreditCd = null; int index = 1; // Iterator the search result and build the lookup object for trial balance report while (rs != null && rs.next()) { reportLine = new TrialBalanceReport(); reportLine.setIndex(index++); reportLine.setChartOfAccountsCode(rs.getString("FIN_COA_CD")); reportLine.setObjectCode(rs.getString("FIN_OBJECT_CD")); reportLine.setFinancialObjectCodeName(rs.getString("FIN_OBJ_CD_NM")); objectTypeDebitCreditCd = rs.getString("FIN_OBJTYP_DBCR_CD"); ytdAmount = new KualiDecimal(rs.getBigDecimal("YTD")); if ((ytdAmount.isPositive() && KFSConstants.GL_CREDIT_CODE.equals(objectTypeDebitCreditCd)) || (ytdAmount.isNegative() && KFSConstants.GL_DEBIT_CODE.equals(objectTypeDebitCreditCd))) { reportLine.setCreditAmount(ytdAmount.abs()); // sum the total credit totalCredit = totalCredit.add(reportLine.getCreditAmount()); } else if ((ytdAmount.isPositive() && KFSConstants.GL_DEBIT_CODE.equals(objectTypeDebitCreditCd)) || (ytdAmount.isNegative() && KFSConstants.GL_CREDIT_CODE.equals(objectTypeDebitCreditCd))) { reportLine.setDebitAmount(ytdAmount.abs()); // sum the total debit totalDebit = totalDebit.add(reportLine.getDebitAmount()); } report.add(reportLine); } // add a final line for total credit and debit if (!report.isEmpty()) { reportLine = new TrialBalanceReport(); reportLine.setIndex(index++); reportLine.setChartOfAccountsCode("Total"); reportLine.setDebitAmount(totalDebit); reportLine.setCreditAmount(totalCredit); report.add(reportLine); } return null; } }); return report; }
From source file:org.openkoala.koala.monitor.application.MonitorDataManageApplicationImpl.java
@SuppressWarnings("deprecation") @Override//from w w w .ja v a 2 s. co m public Map<Integer, Integer> getJdbcConnTimeStat(String nodeId, long timeoutLimit) { timeoutLimit = timeoutLimit * 1000;//to if (timeoutLimit < 0) { Set<MonitorComponent> conponents = MonitorNode.getAllNodesCache().get(nodeId).getConponents(); for (MonitorComponent com : conponents) { if (com.getType().equals("JDBC")) { timeoutLimit = Long.parseLong(com.getProperties().get("trace-timeout")); break; } } } final Map<Integer, Integer> result = new TreeMap<Integer, Integer>(); Date now = new Date(); Date before24h = DateUtils.addHours(now, -24); String sql = "select m.hour, count(*) from KM_JDBC_CONN_DETAILS c left join KM_MAIN_STAT m on c.THREAD_KEY = m.THREAD_KEY and m.fk_node_id=? and (m.begin_time between ? and ?) and TIME_CONSUME>? group by m.hour order by m.hour"; Object[] params = new Object[] { nodeId, before24h, now, timeoutLimit }; getJdbcTemplate().query(sql, params, new ResultSetExtractor<Object>() { @Override public Object extractData(ResultSet rs) throws SQLException, DataAccessException { while (rs.next()) { if (rs.getObject(1) != null) { result.put(rs.getInt(1), rs.getInt(2)); } } return null; } }); int beginPoint = before24h.getHours() + 1; int endPoint = now.getHours(); int count = 0; for (int i = beginPoint; i < 24; i++) { count = result.containsKey(i) ? result.get(i) : 0; result.put(i, count); } for (int i = 0; i <= endPoint; i++) { count = result.containsKey(i) ? result.get(i) : 0; result.put(i, count); } return result; }