NCC优化相关

计息查询效率问题

先通过JProfiler监听发现计息在查询放款单聚合vo数据的时候非常耗时

然后通过录spr发现最放款单的一次查询需要查询放款单以及放款单版本，主表的200多个字段，以及7个子表的所有字段

一次查询执行8个sql，这是就知道瓶颈在哪里了，然后就是要考虑怎们优化。

通过查看BaseDAO提供的方法，发现retrieveByClause(Class className, String condition, String[] fields)方法，是可以指定查询字段的。

 基于该方法封装一个查询聚合VO能够指定查询主表指定字段，以及指定查询某些子表以及子表对应的字段的工具类

package nc.bs.cdmc.cdm.calculintst.ace.bp;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;
import java.util.Optional;
import java.util.stream.Collectors;

import nc.bs.dao.BaseDAO;
import nc.vo.pub.SuperVO;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nc.vo.pubapp.pattern.model.entity.bill.AbstractBill;
import nc.vo.tmpub.util.SqlUtil;

/**
 * 查询聚合VO可以设置查询主表的那些字段，以及查询那些子表及对应的字段---计息查询效率优化
 * 
 * @author wangqiangac
 * @verion NCC-1.0
 * @since 2021-09-03 09:13:47
 */
public class AceQueryAggVoBP<T> {
	private static BaseDAO dao = new BaseDAO();
	private Class<? extends AbstractBill> aggVoClass;// 聚合vo的Class
	private String additionalSql;// 额外的查询条件
	private Class<? extends SuperVO> headVoClass;// 主表VO的Class
	private String[] headFileds;// 主表需要查询的字段
	private HashMap<Class<? extends SuperVO>, String[]> bodysMap;// 查询的子表以及对应的字段
	private String pkFields;
	Collection<AbstractBill> abstractBills = null;

	/**
	 * 查询指定聚合Vo的指定主表字段以及指定子表及子表字段
	 * 
	 * @param pks
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public AbstractBill[] queryAggVoBySomeCondition() {
		try {
			Collection<SuperVO> parentVos = dao.retrieveByClause(headVoClass, additionalSql, headFileds);
			parentVos = Optional.ofNullable(parentVos).orElse(new ArrayList<SuperVO>());
			if (parentVos.isEmpty()) {
				return null;
			}
			List<AbstractBill> list = new ArrayList<>();
			List<String> pks = parentVos.stream().map(a -> a.getPrimaryKey()).collect(Collectors.toList());
			String sql = SqlUtil.buildSqlForIn(pkFields, pks.toArray(new String[] {}));
			HashMap<Class<? extends SuperVO>, Collection<SuperVO>> bodydate = new HashMap<>();
			for (Entry<Class<? extends SuperVO>, String[]> entry : bodysMap.entrySet()) {
				Collection<SuperVO> childVos = dao.retrieveByClause(entry.getKey(), sql + " and dr = 0 ",
						entry.getValue());
				bodydate.put(entry.getKey(), childVos);
			}
			for (SuperVO parentVo : parentVos) {
				AbstractBill abstractBill = aggVoClass.newInstance();
				abstractBill.setParent(parentVo);
				String primaryKey = parentVo.getPrimaryKey();
				for (Entry<Class<? extends SuperVO>, Collection<SuperVO>> entry : bodydate.entrySet()) {
					Collection<SuperVO> childrenList = entry.getValue();
					List<SuperVO> childVos = childrenList.stream()
							.filter(r -> primaryKey.equals(r.getAttributeValue(pkFields))).collect(Collectors.toList());
					abstractBill.setChildren(entry.getKey(), childVos.toArray(new SuperVO[] {}));
				}
				list.add(abstractBill);
			}
			return list.toArray(new AbstractBill[] {});
		} catch (Exception e) {
			ExceptionUtils.wrappException(e);
		}
		return null;
	}

	public String getPkFields() {
		return pkFields;
	}

	public AceQueryAggVoBP<T> setPkFields(String pkFields) {
		this.pkFields = pkFields;
		return this;
	}

	public Class<? extends SuperVO> getHeadVoClass() {
		return headVoClass;
	}

	public AceQueryAggVoBP<T> setHeadVoClass(Class<? extends SuperVO> headVoClass) {
		this.headVoClass = headVoClass;
		return this;
	}

	public Class<? extends AbstractBill> getAggVoClass() {
		return aggVoClass;
	}

	public AceQueryAggVoBP<T> setAggVoClass(Class<? extends AbstractBill> aggVoClass) {
		this.aggVoClass = aggVoClass;
		return this;
	}

	public String getAdditionalSql() {
		return additionalSql;
	}

	public AceQueryAggVoBP<T> setAdditionalSql(String additionalSql) {
		this.additionalSql = additionalSql;
		return this;
	}

	public String[] getHeadFileds() {
		return headFileds;
	}

	public AceQueryAggVoBP<T> setHeadFileds(String[] headFileds) {
		this.headFileds = headFileds;
		return this;
	}

	public HashMap<Class<? extends SuperVO>, String[]> getBodysMap() {
		return bodysMap;
	}

	public AceQueryAggVoBP<T> setBodysMap(HashMap<Class<? extends SuperVO>, String[]> bodysMap) {
		this.bodysMap = bodysMap;
		return this;
	}

	/**
	 * 根据表明及字段名及额外的查询条件组装查询条件
	 * 
	 * @param headTablename
	 * @param headFileds
	 * @param additionalSql
	 * @return
	 */
	public String creatQuerySqlBytableAndFileds(String headTablename, String[] headFileds, String additionalSql) {
		StringBuilder sb = new StringBuilder();
		sb.append("select ");
		String fields = Arrays.toString(headFileds);
		sb.append(fields.substring(1, fields.length() - 1));
		sb.append(" from ");
		sb.append("headTablename ");
		sb.append(additionalSql);
		return sb.toString();
	}
//	public static void main(String[] args) {
//		String[] a = null;
//		String[] b = new String[]{};
//		String[] c = Optional.ofNullable(a).orElse(b);
//		System.out.println(c);
//		
//	}
}

工具类的使用-查询放款单

工具类的使用-查询放款单版本

计息批量操作后前端局部刷新

批量操作计息成功后会再次调用查询计息数据，本身计息查询效率就不高，操作完后再次查询使得效率极其低，所以就考虑在计息操作后台返回数据后，在前端进行局部刷新数据，避免一次前后台交互

具体实现方法

indexpks  如果是计息节点操作需要将批量操作的放款单的主键和index存贮到map中用于后面局部刷新当前结息日和下一结息日

 

贷款、委借、委贷混合参照查询优化

之前该参照代没有做分页，直接查贷款、委借、委贷的聚合VO

/**
 * 贷款放款和委托借款、委托贷款参照
 * 
 * @author wangyufeng
 */
public class FinpayAndTrustGridRefAction implements ICommonAction {
	
	/** 由于币种信息基本无变化，为减少查库次数 */
	private static CurrtypeVO[] currtypeVOs = getCurrtypeVOs();
	//主键和利率名字
	private static 	Map<String, String> PKRateNameMap = new HashMap<>();
	//主键和币种名字
	private static 	Map<String, String> PKNameMap = new HashMap<>();
	//金融机构和非金融机构 客户和供应商
	private static 	Set< String> IDNameSet = new HashSet<>();

	private  IFinancepayPublicService financepayPublicService = ServiceLocator.find(IFinancepayPublicService.class);
	@Override
	public Object doAction(IRequest request) {
		RefQueryResult refQueryResult = new RefQueryResult();
		try {
			//1 .解析前端过滤条件
			IJson json = JsonFactory.create();
			String read = request.read();
			TreeRefQueryInfo info = json.fromJson(read, TreeRefQueryInfo.class);
			String repaytype = info.getQueryCondition().get("repaytype");
			//2. 查询数据
			String whereSql = this.getWhereSql(info,repaytype);
			
			// 是否过滤冻结态单据 liglt 20200212
			String checksagafrozen = info.getQueryCondition().get("checksagafrozen");
			if(!"false".equals(checksagafrozen)){
				//判断冻结状态
				whereSql += " and isnull(saga_frozen, 0)=0 ";
			}
			
			//查询贷款放款的数据
			AggFinancepayVO[] aggFinVOs = getFinQueryService().getAggVOsByCondition(whereSql);
			if("adjust".equals(repaytype)) {//如果是利息调整页面调用，需要过滤掉系统生成的放款单
				whereSql = whereSql + " and datacomefrom = 'MANUAL'";
			}
			//interestlist
			//查询委托借款数据
			AggConsignDebitFinancepayVO [] aggBorFinpayVOs = getDebitQueryService().getAggVOsByCondition(whereSql);
			//查询委托贷款数据
			AggConsignCreditFinancepayVO [] aggLoanFinVOs = getCreditQueryService().getAggVOsByCondition(whereSql);
			
			//利率数据不多，所以查询所有数据
			List<RateCodeVO> rateCodeVOS = getRateconfigQueryService().queryAllRateCodeVO();
			
			//3.处理放款数据
			List<RefRow> rows = new ArrayList<>();
			if(!ArrayUtil.isNull(aggFinVOs)) {
				rows.addAll(this.aggVO2RefRow(aggFinVOs,rateCodeVOS));
			}
			//4.处理委托借款、委托贷款数据
			if(!ArrayUtil.isNull(aggBorFinpayVOs)) {
				rows.addAll(this.aggVO2RefRow(aggBorFinpayVOs,rateCodeVOS));
			}
			if(!ArrayUtil.isNull(aggLoanFinVOs)) {
				rows.addAll(this.aggVO2RefRow(aggLoanFinVOs,rateCodeVOS));
			}
			getFininstitutionIDName(rows);
			//5.合并贷款放款、委托借款和委托贷款
			refQueryResult = this.constructResult(rows.toArray(new RefRow[0]), info.getPageInfo());
			
		} catch (BusinessException e) {
			Logger.error(e);
			ExceptionUtils.wrapBusinessException(e.getMessage());
		}
		return refQueryResult;
	}
	
	

public Object doAction(IRequest request) {
		RefQueryResult refQueryResult = new RefQueryResult();
		try {
			// 1 .解析前端过滤条件
			IJson json = JsonFactory.create();

			String read = request.read();
			TreeRefQueryInfo info = json.fromJson(read, TreeRefQueryInfo.class);
			String repaytype = info.getQueryCondition().get("repaytype");
			// 2. 查询数据
			String whereSql = this.getWhereSql(info, repaytype);

			// 是否过滤冻结态单据 liglt 20200212
			String checksagafrozen = info.getQueryCondition().get("checksagafrozen");
			if (!"false".equals(checksagafrozen)) {
				// 判断冻结状态
				whereSql += " and isnull(saga_frozen, 0)=0 ";
			}
			List<String> allPks = new ArrayList<String>();
			// 第一步 ： 根据查询条件查询放款单的所有pks
			List<String> financePks = financepayPublicService
					.queryPksByCondition("select pk_financepay from cdmc_financepay where " + whereSql);
			if (financePks != null && financePks.size() > 0) {
				allPks.addAll(financePks);
			}
			if ("adjust".equals(repaytype)) {// 如果是利息调整页面调用，需要过滤掉系统生成的放款单
				whereSql = whereSql + " and datacomefrom = 'MANUAL'";
			}
			// interestlist
			List<String> consignDebitPks = financepayPublicService
					.queryPksByCondition("select pk_financepay from cdmc_consignfinancepay where  " + whereSql);
			if (consignDebitPks != null && consignDebitPks.size() > 0) {
				allPks.addAll(consignDebitPks);
			}
			// 第二步 ： 根据分页参数获取到当前的分页pk集合
			List<String> currpks = getCurrPagePks(info, allPks);
			if(currpks.size()==0) {
				return null;
			}
			// 查询贷款放款的数据
			String pkWhereSql = SqlUtil.buildSqlForIn(FinancepayVO.PK_FINANCEPAY, currpks.toArray(new String[] {}));
			Collection<SuperVO> FinVos = getFinQueryService().queryAggVoByCondition(pkWhereSql, getHeadField());
			// 查询委托借款数据
			StringBuilder debitPkWhereSql = new StringBuilder(pkWhereSql).append(" and busitype = 'DEBIT' and isnull(dr,0) = 0");
			Collection<SuperVO> aggBorFinpayVOs = getDebitQueryService()
					.queryAggVoByCondition(debitPkWhereSql.toString(), getCreditHeadField());
			// 查询委托贷款数据
			StringBuilder creditPkWhereSql = new StringBuilder(pkWhereSql).append(" and busitype = 'CREDIT' and isnull(dr,0) = 0");
			Collection<SuperVO> aggLoanFinVOs = getCreditQueryService()
					.queryAggVoByCondition(creditPkWhereSql.toString(), getCreditHeadField());

			// 利率数据不多，所以查询所有数据
			List<RateCodeVO> rateCodeVOS = getRateconfigQueryService().queryAllRateCodeVO();

			// 3.处理放款数据
			List<RefRow> rows = new ArrayList<>();
			if (!ArrayUtil.isNull(FinVos.toArray())) {
				rows.addAll(this.aggVO2RefRow(FinVos.toArray(new CircularlyAccessibleValueObject[] {}), rateCodeVOS));
			}
//			//4.处理委托借款、委托贷款数据
			if (!ArrayUtil.isNull(aggBorFinpayVOs.toArray())) {
				rows.addAll(this.aggVO2RefRow(aggBorFinpayVOs.toArray(new CircularlyAccessibleValueObject[] {}),
						rateCodeVOS));
			}
			if (!ArrayUtil.isNull(aggLoanFinVOs.toArray())) {
				rows.addAll(this.aggVO2RefRow(aggLoanFinVOs.toArray(new CircularlyAccessibleValueObject[] {}),
						rateCodeVOS));
			}
			getFininstitutionIDName(rows);
			// 5.合并贷款放款、委托借款和委托贷款
			refQueryResult = this.constructResult(rows.toArray(new RefRow[0]), info.getPageInfo(), allPks);

		} catch (BusinessException e) {
			Logger.error(e);
			ExceptionUtils.wrapBusinessException(e.getMessage());
		}
		return refQueryResult;
	}

 查询参照所需字段，不查询整个聚合vo

/**
	 * 根据查询条件查询放款单的部分字段
	 * @param whereSql
	 * @param headFields
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Collection<SuperVO> queryAggVoByCondition(String whereSql, String[] headFields) throws BusinessException {
		Collection<SuperVO> voList = getBaseDao().retrieveByClause(ConsignDebitFinancepayVO.class, whereSql, headFields);
		voList = Optional.ofNullable(voList).orElse(new ArrayList<SuperVO>());
		return voList;
	}

财务组织超过一千走临时表

之前代码是在Action层

SqlUtil.buildSqlForIn(FinancepayVO.PK_FINANCEPAY, pks)

不建议使用这个，数据量上千时同业会很慢

建议使用nccloud.impl.platform.db.processor.InSqlManager

InSqlManager.getInSQLValue(pk_orgs)

(select  pk  from   temp_xx )   需要保证在同一个事务里面

public static String buildSqlForIn(final String fieldname,
			final String[] fieldvalue) {
		StringBuffer sbSQL = new StringBuffer();
		sbSQL.append("(" + fieldname + " IN ( ");
		int len = fieldvalue.length;
		// 循环写入条件
		for (int i = 0; i < len; i++) {
			if (fieldvalue[i] != null && fieldvalue[i].trim().length() > 0) {
				sbSQL.append("'").append(fieldvalue[i].toString()).append("'");
				// 单独处理 每个取值后面的",", 对于最后一个取值后面不能添加"," 并且兼容 oracle 的 IN 254 限制。每
				// 200 个 数据 or 一次。时也不能添加","
				if (i != (fieldvalue.length - 1)
						&& !(i > 0 && (i + 1) % SQL_IN_LIST_LIMIT == 0)) {
					sbSQL.append(",");
				}
			} else {
				return null;
			}

			// 兼容 oracle 的 IN 254 限制。每 200 个 数据 or 一次。
			if (i > 0
					&& (i + 1) % SQL_IN_LIST_LIMIT == 0
					&& i != (fieldvalue.length - 1)) {
				sbSQL.append(" ) OR ").append(fieldname).append(" IN ( ");
			}
		}
		sbSQL.append(" )) ");

		return sbSQL.toString();
	}

 之前代码是直接查整个聚合VO，也会有效率问题

优化为只查询放款单主键

/**
	 *  查询所有数据的pk
	 * @param operaParam
	 * @return
	 * @throws BusinessException
	 */
	@Override
	protected Map<String, String> qryAllPks(QueryTreeFormatVO operaParam) throws BusinessException {
		Map<String, String> pkBusiTypeMap = new HashMap<>();
		// 查询条件
		String whereSql = this.getWhereSql(operaParam,LOAN);
		// 查询贷款放款主键
		List<String> pks= CalIntstServiceUtil.getFinQueryService().getPkField(FinancepayVO.getDefaultTableName(),whereSql,FinancepayVO.PK_FINANCEPAY,pkorgs);
		// 查询委托放款、借款数据主键
		Map<String, String> pkBusitype = CalIntstServiceUtil.getCreditQueryService().getPkField(
				ConsignCreditFinancepayVO.getDefaultTableName(), this.getWhereSql(operaParam,null), ConsignCreditFinancepayVO.PK_FINANCEPAY,
				ConsignCreditFinancepayVO.BUSITYPE,pkorgs);

		Map<String, String> finpkLoanMap = new HashMap<>();
		for (String string : pks) {
			finpkLoanMap.put(string, BusiTypeEnum.LOAN.toStringValue());
		}
		// 合并map
		pkBusiTypeMap.putAll(finpkLoanMap);
		pkBusiTypeMap.putAll(pkBusitype);
		pkorgs = new String[] {};
		return pkBusiTypeMap;
	}

 

@Override
	public Map<String, String> getPkField(String tableName, String whereSql, String pkField, String field,String[] pk_orgs)
			throws BusinessException {
		whereSql = whereSql +" and "+ConsignCreditFinancepayVO.PK_ORG +" in " + InSqlManager.getInSQLValue(pk_orgs);
		StringBuffer sb = new StringBuffer();
		sb.append("select " + pkField + "," + field + " from " + tableName + " where ");
		Map<String, String> pkCollection = (Map<String, String>) new BaseDAO()
				.executeQuery(sb.append(whereSql).toString(), new ResultSetProcessor() {
					public Object handleResultSet(ResultSet rs) throws SQLException {
						Map<String, String> pkType = new HashMap<>();
						while (rs.next()) {
							pkType.put(rs.getString(1), rs.getString(2));
						}

						return pkType;
					}

				});
		return pkCollection;
	}