在java层,我还定义了一个可以获五个字段的类
/** * 规则号 */ public String ruleNo; /** * 公司账套 */ @RuleDtlTypeInfo(value ="30") private String businessBook; /** * 公司账套名称 */ private String businessBookDesc; /** * 机构级别 */ @RuleDtlTypeInfo(value ="31") private String branchLevel; /** * 机构名称 */ private String branchLevelDesc; /** * 所在预算中心 */ @RuleDtlTypeInfo(value ="32") private String placeBudgetCenter; /** * 所在预算中心名称 */ private String placeBudgetCenterDesc;
......等等
可以发现我为每个写了注解
/**功能描述:该注释是用来标识使用该注释的字段是属于规则明细设置表中哪种类型
* @author fangjunjie.wb
* @date 2020/4/22
* @return
*/
@Target( { ElementType.FIELD })
@Retention(RetentionPolicy.RUNTIME)
public @interface RuleDtlTypeInfo {
/**
* 规则明细类型值
*
* @return
*/
String value();
}
最后根据反射解析五个字段（貌似不知5个）核心代码如下,需要保存的字段我都添加了@RuleDtlTypeInfo
@Override
public List<RuleDetailSetDomain> analysisFilterMask(Object o,String ruleNo) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
List<RuleDetailSetDomain> ruleDetailSetDomainList=new ArrayList<RuleDetailSetDomain>();
Field[] field = o.getClass().getDeclaredFields();
for (int i = 0; i < field.length; i++) {
//获取注解的规则明细类型,如果字段没有添加RuleDtlTypeInfo注解，则不进行保存
RuleDtlTypeInfo ruleDtlTypeInfo= field[i].getAnnotation(RuleDtlTypeInfo.class);
if (ruleDtlTypeInfo!=null){
RuleDetailSetDomain ruleDetailSetDomain=new RuleDetailSetDomain();
String ruleDtlNo = commonService.getSequence("BEPRULE", "RULE_DETAIL_SET", "RULE_DETAIL_NO");
ruleDetailSetDomain.setRuleNo(ruleNo);
ruleDetailSetDomain.setRuleDetailNo(ruleDtlNo);
ruleDetailSetDomain.setRuleDtlType(ruleDtlTypeInfo.value());
// 获取属性的名字
String name = field[i].getName();
// 将属性的首字母大写
Method m = o.getClass().getMethod("get" + StringUtil.UpperFirst(name));
// 调用getter方法获取属性值
String ruleDtlValue = (String) m.invoke(o);
ruleDetailSetDomain.setRuleDtlValue(ruleDtlValue);
ruleDetailSetDomainList.add(ruleDetailSetDomain);
}
}
return ruleDetailSetDomainList;
}
当不能用join 的时候,where使用exists做过滤的时候,并且在select也做了子查询,这个是不明智的,可以把where的exists去掉,重新包裹一次，然后在外层做过滤，可以少查询一次表,如下例子:
SELECT *
FROM (select rownum r,
union_tb.*
from (select aa.fld01 as branchId,
bi.name as branchidName,
aa.fld02 applyDeptNo,
(select a.dept_name
from department_info a
where a.dept_no =
aa.fld02
and a.business_book =
aa.fld03
and rownum = 1) applyDeptName,
aa.fld03 as businessBook,
aa.fld04 as projectCode,
(select a.project_name
from project_info a
where a.project_code =
aa.fld04
and a.business_book =
aa.fld03
and rownum = 1) projectName,
aa.fld05 as imageNo,
aa.fld06 as isCentralApprove,
aa.fld07 as budgetCenter,
bct.description as budgetCenterName,
aa.fld08 as applyUser,
oei.employee_name as applyName,
aa.fld09 as responsibleUser,
oei2.employee_name as responsibleName,
aa.fld10 as businessNoType,
btt.description as billTypeName,
aa.fld11 as businessNo,
aa.fld12 as bepVoucherNo,
aa.fld14 as economicIssuesNo,
eis.economic_issues as economicIssues,
eis.issues_class as issuesClass,
(select a.description
from issues_class_tbl a
where a.issues_class =
eis.issues_class
and rownum = 1) issuesClassName,
eit.description as economicIssuesName,
aa.fld15 as applyDate,
to_char(aa.fld200,
'YYYY-MM-DD') as applyEndDate,
aa.fld16 as applyStatus,
decode(aa.fld17,
'01',
'保存',
'02',
'提交',
'03',
'审批中',
'04',
'审批通过',
'05',
'审批不通过',
'06',
'申请单关闭',
'24',
'单据删除',
'25',
'借款单关闭',
'其他') approveStatus,
aa.fld100 as applyAmount,
aa.fld19 as applyReason,
aa.fld202 as realPayDate,
aa.fld20 as applySourceDesc,
(
SELECT max(bwfh.flow_net_end_date) FROM bill_work_flow_history bwfh
WHERE bwfh.business_no=aa.fld11
and bwfh.business_no_type=aa.fld10
AND bwfh.flow_net_code='1'
)flowNetEndDate -- 获取处理时间
from bep_report_tmp aa,
bill_type_tbl btt,
branch_info bi,
oa_employee_info oei,
oa_employee_info oei2,
budget_center_tbl bct,
economic_issues_set eis,
economic_issues_tbl eit
where aa.fld01 =
bi.branchid
and aa.fld10 =
btt.bill_type
and aa.fld07 =
bct.budget_center(+)
and aa.fld03 =
bct.business_book(+)
and aa.fld14 =
eis.economic_issues_no(+)
and aa.fld03 =
eis.business_book(+)
and eis.economic_issues =
eit.economic_issues(+)
and aa.fld08 =
oei.employee_id
and aa.fld03 =
oei.business_book
and aa.fld09 =
oei2.employee_id(+)
and aa.fld03 =
oei2.business_book(+)
and aa.fld00 = 'commonReport202101081508200656'
AND aa.fld03 = 'SINO_FDIH_LEDGER'
and exists
(
SELECT bwfh.business_no_type,bwfh.business_no FROM bill_work_flow_history bwfh
WHERE bwfh.business_no=aa.fld11
and bwfh.business_no_type=aa.fld10
AND bwfh.flow_net_code='1' -- 前端传的
group by bwfh.business_no_type,bwfh.business_no
having MAX(bwfh.Flow_Net_End_Date)>date'2020-10-22' -- 前端传的
and MAX(bwfh.Flow_Net_End_Date)<date'2020-12-23' -- 前端传的
) --这里只是过滤bwfh.Flow_Net_End_Date,可以放到外层去做
order by flowNetEndDate ) union_tb)
总结:写代码的时候，要知道这个代码只在做什么事情,或许可以避免很多垃圾代码
