mybatis
<select id="selectUserList" resultType="map">
select * from user
</select><!--根据主键查询一条-->
<select id="selectById" resultType="map" parameterType="java.lang.Integer">
select * from user where user id = #{id1}
</select><!--
map.put("user_id",1);
map.put("username","root");
传人一个Map对象进行插人
--!>
<insert id="insertMap" parameterType="map">
INSERT INTO `user`(`username`,`password`,`realname`,`telephone`,`user_type` ,`lastlogin`)
VALUES
(#{#{username},#{password},#{realname},#{telephone},#{user_type},#{lastlogin})
</insert>
一对一：
延迟加载：
一对多：
mybatis缓存
<select id="leaseholdQueryForm" resultType="com.kingtechfin.ktfcp.sif.pdm.report.dto.deyin.LeaseholdResultDto">SELECT psr.id,pp.project_no,pcc.contract_no,pcc.contract_name, pcm.business_manage,pps.business_model as businessModel ,ppm.approval_time projectInitiationTime ,ppm.inquire_time approvalTime, pcm.business_category,pcm.business_type,pcc.contract_state as contract_state,ifnull( (select min(plo.loan_pass_time) from t_pdm_loan plo where plo.merge_loan_id = psr.loan_merge_id and plo.sys_logic_del='0' and plo.present ='1') , psr.pass_time ) as startingOperationTime,psr.start_rent startRent,pql.annual_interest_rate rateLease,round(case when pql.repayment_frequency = '0' then pql.total_lease_termwhen pql.repayment_frequency = '2' then pql.total_lease_term/3when pql.repayment_frequency = '3' then pql.total_lease_term/6when pql.repayment_frequency = '4' then pql.total_lease_term/12 end,0) as leasePeriodsOld,( SELECT COUNT(*) FROM t_pdm_collection pcLEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_idWHERE pc.collection_type in ('0','4') AND pc.sys_logic_del='0' andpbc.con_bundle_id = pcc.con_bundle_id) leasePeriods,pql.total_lease_term as leaseTermOld,(SELECT PERIOD_DIFF(FROM_UNIXTIME(max(pc.date_collection)/1000,'%Y%m'),FROM_UNIXTIME(min(pc.date_collection)/1000,'%Y%m'))+1 FROM t_pdm_collection pcLEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_idWHERE pc.collection_type in ('0','4') AND pc.sys_logic_del='0'AND pbc.con_bundle_id = pcc.con_bundle_id) AS leaseTerm,pcm.cust_name ,ifnull(pps.channel_name,(select pbc.channel_name fromt_pdm_redundancy_info pbcwhere pbc.con_bundle_id = pcc.con_bundle_id limit 1)) channel_name ,ifnull((SELECTSUBSTRING_INDEX(business_region, ',', 1) FROM t_crm_cust_basic_information WHERE id = pps.channel_id),(SELECTSUBSTRING_INDEX(business_region, ',', 1) FROM t_crm_cust_basic_information WHERE id = pcc.bp_id_agent)) AS region,ifnull((SELECT office FROM t_crm_cust_basic_information WHERE id = pps.channel_id),(SELECT office FROM t_crm_cust_basic_information WHERE id = pcc.bp_id_agent))office,pvl.lease_type as classificationAsset,pvl.brand vehicleBrand,pvl.model_code modelSpecification,pvl.vehicle_type as vehicleType,pvl.vehicle_category as vehicleCategory,IF(pvl.lease_status='8','是','否') earlySettlementSign,if(pcc.contract_state='28','是',(SELECT IF(l.reclaim_sublease_flag='1','是','否') FROM `t_pdm_settlement_lease` lLEFT JOIN `t_pdm_rent_plan_change` c ON l.`change_id` = c.idWHERE c.change_state = '2' AND l.settle_flag='1' AND l.lease_id =pvl.id LIMIT 1 )) reclaimSubleaseFlag,pvl.invoice_seller invoiceSeller,ifnull(ifnull(pvl.et_date,(SELECT MAX(c.`flow_end_time`) FROM `t_pdm_settlement_lease` lLEFT JOIN `t_pdm_rent_plan_change` c ON l.`change_id` = c.idWHERE c.change_state = '2' and l.settle_flag='1' AND l.lease_id =pvl.id )),pcc.con_end_time) vehicleSettlementDate,pcc.con_end_time contractSettlementDate,pvl.contract_small_no contractSmall, pvl.vehicle_frame_no frameNo,pvl.license_no licenseNumber,pvl.sold_out_time soldTime, pvl.vehicle_sales_price salesPrice,pvl.purchase_tax purchaseTax, pvl.insurance_fee insurancePrice,pvl.lease_total_price totalRentalPrice,pp.product_config_name productScheme,pvl.manufacturer ,pvl.is_affiliated as affiliationFlag,IF(pvl.affiliated_company IS NOT NULL,ifnull((SELECT cust_name FROM t_crm_cust_basic_information WHERE id = pvl.affiliated_company limit 1),pvl.affiliated_company),'') affiliationCompany,pvl.the_license_province as registeredProvince,pvl.the_license_city as registeredCity,pvl.the_license_county as registeredArea,pvl.association_project_no aAssociatedItems,pvl.remark,'' deliveryPlace, '' blacklistArea,'' materialReturnMark,'' releaseCulvertMark,pvl.is_blacklist_area as blacklistAreaFlag,'' ABS,ifnull(pvl.gps_brand,'陕西天行健车联网信息技术有限公司') AS gps_brand,(SELECT COUNT(*) FROM t_pdm_collection pcLEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_idWHERE pc.collection_type in ('0','4') AND pc.sys_logic_del='0'AND pc.check_state!='1' AND pbc.con_bundle_id = pcc.con_bundle_id) AS remainingPeriods,(SELECT COUNT(*) FROM t_pdm_collection pcLEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_idWHERE pc.collection_type in ('0','4') AND pc.sys_logic_del='0'AND pc.check_state='1' AND pbc.con_bundle_id = pcc.con_bundle_id) AS paidPeriodsFROM(select pvl.id,pvl.lease_type,pvl.brand,pvl.model_code,pvl.vehicle_type,pvl.vehicle_category,pvl.lease_status,pvl.invoice_seller,pvl.et_date,pvl.contract_small_no,pvl.vehicle_frame_no,pvl.license_no,pvl.sold_out_time, pvl.vehicle_sales_price,pvl.purchase_tax, pvl.insurance_fee,pvl.lease_total_price,pvl.manufacturer,pvl.is_affiliated,pvl.affiliated_company,pvl.the_license_province,pvl.the_license_city,pvl.the_license_county,pvl.association_project_no,pvl.remark,pvl.is_blacklist_area,pvl.gps_brand,pvl.contract_bundle_idfrom t_plm_vehicle_lease pvl where pvl.sys_logic_del=0union allselect pi.id,'设备' lease_type,'' brand,'' model_code,'' vehicle_type,'' vehicle_category,pi.lease_status,'' invoice_seller,null et_date,'' contract_small_no,'' vehicle_frame_no,'' license_no,null sold_out_time, null vehicle_sales_price,null purchase_tax ,null insurance_fee ,pi.purchase_amount lease_total_price,pi.manufacturer,'' is_affiliated,'' affiliated_company,'' the_license_province,'' the_license_city,'' the_license_county,'' association_project_no,'' remark,'' is_blacklist_area,'无' gps_brand,pi.con_bundle_id contract_bundle_idfrom t_plm_lease_essential_information pi where pi.sys_logic_del=0) pvlLEFT JOIN t_pcm_contract_content pcc ON pcc.con_bundle_id = pvl.contract_bundle_idLEFT JOIN t_pcm_backup_record cr ON cr.record_id = pcc.idLEFT JOIN `t_pcm_contract_manage` pcm ON pcm.id = cr.`manage_id`LEFT JOIN t_pcm_backup_record pr ON pr.manage_id = pcm.project_manager_id AND pr.present = '1'LEFT JOIN t_pcm_project_manager ppm ON ppm.id = pr.manage_idLEFT JOIN `t_pcm_project` pp ON pp.id = pr.record_idLEFT JOIN t_pcm_project_sublist pps ON pps.project_id = pr.record_idLEFT JOIN t_pdm_start_rent psr ON psr.con_bundle_id = pcc.con_bundle_id AND psr.sys_logic_del='0'left join t_pdm_rent_plan_manage prpm on prpm.loan_merge_id = psr.loan_merge_id and prpm.sys_logic_del=0LEFT JOIN t_pdm_quotation_lease pql ON pql.record_id = prpm.id and pql.sys_logic_del='0'WHERE cr.present='1'<include refid="leaseholdQueryFormSql"/> </select><sql id="leaseholdQueryFormSql"><if test="dto.modelSpecification!=null and dto.modelSpecification!=''">AND pvl.model_code like concat('%',#{dto.modelSpecification}, '%')</if><if test="dto.startRentLeft!=null and dto.startRentLeft!=''">AND psr.start_rent >= #{dto.startRentLeft}</if><if test="dto.startRentRight!=null and dto.startRentRight!=''">AND psr.start_rent <= #{dto.startRentRight}</if><if test="dto.businessType != null and dto.businessType != ''">AND FIND_IN_SET(pcm.business_type, #{dto.businessType})</if><if test="dto.businessCategory != null and dto.businessCategory != ''">AND FIND_IN_SET(pcm.business_category, #{dto.businessCategory})</if><if test="dto.productScheme != null and dto.productScheme != ''">and pp.product_config_name = #{dto.productScheme}</if><if test="dto.frameNo != null and dto.frameNo != ''">and pvl.vehicle_frame_no like concat('%',#{dto.frameNo}, '%')</if><if test="dto.custName != null and dto.custName != ''">and pcm.cust_name like concat('%',#{dto.custName}, '%')</if><if test="dto.businessManage != null and dto.businessManage != ''">and pcm.business_manage = #{dto.businessManage}</if><if test="dto.projectNo != null and dto.projectNo != ''">and pp.project_no like concat('%',#{dto.projectNo}, '%')</if><if test="dto.contractNo != null and dto.contractNo != ''">and pcc.contract_no like concat('%',#{dto.contractNo}, '%')</if><if test="dto.contractName != null and dto.contractName != ''">and pcc.contract_name like concat('%',#{dto.contractName}, '%')</if><if test="dto.contractState != null and dto.contractState != ''">and pcc.contract_state = #{dto.contractState}</if><if test="dto.channelName != null and dto.channelName != ''">and pps.channel_name like concat('%',#{dto.channelName}, '%')</if><if test="dto.businessModel != null and dto.businessModel != ''">and pps.business_model = #{dto.businessModel}</if></sql>
package com.kingtechfin.ktfcp.sif.pdm.report.dto.deyin;import com.kingtechfin.ktfcp.common.utils.BigDecimalUtils;
import com.kingtechfin.ktfcp.framework.annotation.DictionaryBiz;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;import java.math.BigDecimal;/*** description :** @author : Xiyhua* @version : v1* @date : 2021/7/23 10:28*/
@Data
@ApiModel(value = "LeaseholdResultDto", description = "租赁物查询表结果")
public class LeaseholdResultDto {@ApiModelProperty(value = "项目编号")private String projectNo;@ApiModelProperty(value = "合同编号")private String contractNo;@ApiModelProperty(value = "项目经理")private String businessManage;@ApiModelProperty(value = "GPS安装费")private BigDecimal gpsInstallationFee;@ApiModelProperty(value = "GPS设备费")private BigDecimal gpsEquipmentFee;@ApiModelProperty(value = "商业模式")private String businessModel;@ApiModelProperty(value = "经销商合作协议号")private String channelCooperationNo;@ApiModelProperty(value = "项目立项时间")private Long projectInitiationTime;@ApiModelProperty(value = "信审通过时间")private Long approvalTime;@ApiModelProperty(value = "合同号")private String contractName;@DictionaryBiz(dictKey = "dimension")@ApiModelProperty(value = "业务类别")private String businessCategory;@DictionaryBiz(dictKey = "dimension")@ApiModelProperty(value = "租赁类型")private String businessType;@ApiModelProperty(value = "合同状态")private String contractState;@ApiModelProperty(value = "起租操作时间")private Long startingOperationTime;@ApiModelProperty(value = "起租日")private Long startRent;@ApiModelProperty(value = "租赁利率")private BigDecimal rateLease;@ApiModelProperty(value = "还款期数（现行）")private Integer leasePeriods;@ApiModelProperty(value = "还款期数（原始）")private Integer leasePeriodsOld;@ApiModelProperty(value = "租赁期限（现行）")private Integer leaseTerm;@ApiModelProperty(value = "租赁期限（原始）")private Integer leaseTermOld;@ApiModelProperty(value = "承租人")private String custName;@ApiModelProperty(value = "经销商名称")private String channelName;@ApiModelProperty(value = "区域")private String region;@ApiModelProperty(value = "办事处")private String office;@ApiModelProperty(value = "资产分类")private String classificationAsset;@ApiModelProperty(value = "车辆类型")private String vehicleType;@ApiModelProperty(value = "车辆品牌")private String vehicleBrand;@ApiModelProperty(value = "规格型号")private String modelSpecification;@ApiModelProperty(value = "车辆类别")private String vehicleCategory;@ApiModelProperty(value = "提前结清标志")private String earlySettlementSign;@ApiModelProperty(value = "收回转租标志")private String reclaimSubleaseFlag;@ApiModelProperty(value = "车辆结清日")private Long vehicleSettlementDate;@ApiModelProperty(value = "合同结清日")private Long contractSettlementDate;@ApiModelProperty(value = "合同小号")private String contractSmall;@ApiModelProperty(value = "车架号")private String frameNo;@ApiModelProperty(value = "车牌号")private String licenseNumber;@ApiModelProperty(value = "已售时间")private Long soldTime ;@ApiModelProperty(value = "销售价")private BigDecimal salesPrice;@ApiModelProperty(value = "购置税")private BigDecimal purchaseTax;@ApiModelProperty(value = "保险价格")private BigDecimal insurancePrice;@ApiModelProperty(value = "租赁总价")private BigDecimal totalRentalPrice;@ApiModelProperty(value = "产品方案")private String productScheme;@ApiModelProperty(value = "发票销货方")private String invoiceSeller;@ApiModelProperty(value = "制造商")private String manufacturer;@ApiModelProperty(value = "是否挂靠")private String affiliationFlag;@ApiModelProperty(value = "挂靠公司")private String affiliationCompany;@ApiModelProperty(value = "上牌地省")private String registeredProvince;@ApiModelProperty(value = "上牌地市")private String registeredCity;@ApiModelProperty(value = "上牌地区县")private String registeredArea;@ApiModelProperty(value = "关联项目")private String aAssociatedItems;@ApiModelProperty(value = "备注")private String remark;@ApiModelProperty(value = "交付地")private String deliveryPlace;@ApiModelProperty(value = "黑名单区域")private String blacklistArea;@ApiModelProperty(value = "资料物归还标记")private String materialReturnMark;@ApiModelProperty(value = "解押涵生成标记")private String releaseCulvertMark;@ApiModelProperty(value = "是否黑名单区域")private String blacklistAreaFlag;@ApiModelProperty(value = "ABS")private String ABS;@ApiModelProperty(value = "已还期数")private String paidPeriods;@ApiModelProperty(value = "剩余期数")private String remainingPeriods;@ApiModelProperty(value = "GPS品牌")private String gpsBrand;}
<select id="queryRentPlanList" resultType="com.kingtechfin.ktfcp.sif.pdm.repaymentplan.interfaces.dto.PdmRentPlanListDto">SELECTprp.id, prp.rent_plan_manage_id, prp.rent_plan_code,prp.placement_no,prp.rent_plan_state,prp.pay_table_change_time,pql.annual_interest_rate,prp.amt_loan,pbi.project_name,pbi.cust_name,pbi.contract_no,pbi.business_category,pbi.business_type,pbi.project_type,pql.lease_term,pql.irr,pql.removal_tax_irr,pql.interest_rate_modelFROMt_pdm_rent_plan prp left join t_pdm_base_info pbi on pbi.rent_plan_id = prp.idLEFT JOIN t_pdm_quotation_lease pql on prp.id = pql.record_idWHEREprp.sys_logic_del = '0' and prp.id IS NOT NULL and prp.present = 1<if test="dto.rentPlanState != null and dto.rentPlanState != ''">and find_in_set(prp.rent_plan_state,#{dto.rentPlanState})</if><if test="dto.custName != null and dto.custName != ''">and pbi.cust_name like CONCAT('%',#{dto.custName},'%')</if><if test="dto.projectName != null and dto.projectName != ''">and pbi.project_name like CONCAT('%',#{dto.projectName},'%')</if><if test="dto.contractNo != null and dto.contractNo != ''">and pbi.contract_no like CONCAT('%',#{dto.contractNo},'%')</if><if test="dto.rentPlanCode != null and dto.rentPlanCode != ''">and prp.rent_plan_code like CONCAT('%',#{dto.rentPlanCode},'%')</if>ORDER BY pbi.project_manager_id, pbi.con_bundle_id, prp.sys_ctime</select>package com.kingtechfin.ktfcp.sif.pdm.repaymentplan.interfaces.dto;import com.baomidou.mybatisplus.annotation.TableField;
import com.kingtechfin.ktfcp.common.entity.po.BasePo;
import com.kingtechfin.ktfcp.framework.annotation.DictionaryBiz;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;/*** 描述 : 租金计划管理表.** @author : shilili* @version : v1* @date : 2021-03-30*/
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@ApiModel(value = "PdmRentPlanListDto", description = "还款计划管理列表")
public class PdmRentPlanListDto extends BasePo {@ApiModelProperty(value = "是否多报价标识")private String multiQuotationFlag;@ApiModelProperty(value = "项目管理id")private String projectManagerId;@ApiModelProperty(value = "核算主体id")private String accountingEntityId;@ApiModelProperty(value = "核算主体")private String accountingEntity;@ApiModelProperty(value = "还款计划审批数量")private BigDecimal approvalNum;@ApiModelProperty(value = "产品方案配置ID")private String productConfigId;@ApiModelProperty(value = "还款计划管理表Id，varchar(32)")private String rentPlanManageId;@ApiModelProperty(value = "还款计划Id，varchar(32)")private String rentPlanId;@ApiModelProperty(value = "投放Id，varchar(32)")private String loanId;@ApiModelProperty(value = "还款计划编号，varchar(32)")private String rentPlanCode;@ApiModelProperty(value = "放款期次，varchar(32)")private String placementNo;@ApiModelProperty(value = "投放金额，decimal(20,7)")private BigDecimal amtLoan;@ApiModelProperty(value = "利率模式，varchar(16)")private String interestRateModel;@ApiModelProperty(value = "年利率，decimal(20,7)")private BigDecimal annualInterestRate;@ApiModelProperty(value = "租金计划状态，varchar(8)")private String rentPlanState;@ApiModelProperty(value = "租金支付表更新时间，bigint(20)")private Long payTableChangeTime;@ApiModelProperty(value = "租户id，varchar(32)")private String sysTenantId;@ApiModelProperty(value = "是否当前，varchar(8)")private String present;@ApiModelProperty(value = "是否合并，varchar(8)")private String isMergePlan;@ApiModelProperty(value = "客户名称，varchar(128)")private String custName;@ApiModelProperty(value = "项目名称，varchar(256)")private String projectName;@ApiModelProperty(value = "项目编号，varchar(256)")private String projectNo;@ApiModelProperty(value = "类别")@DictionaryBiz(dictKey = "dimension")private String businessCategory;@ApiModelProperty(value = "类型")@DictionaryBiz(dictKey = "dimension")private String businessType;@ApiModelProperty(value = "项目类型")@DictionaryBiz(dictKey = "dimension")private String projectType;@ApiModelProperty(value = "合同绑定id，varchar(32)")private String conBundleId;@ApiModelProperty(value = "合同编号，varchar(64)")private String contractNo;@ApiModelProperty(value = "合同名称，varchar(128)")private String contractName;@ApiModelProperty(value = "计算方式，varchar(32)")@TableField("calculate_rent_method")private String calculateRentMethod;@ApiModelProperty(value = "期限，int(4)")private Integer leaseTerm;@ApiModelProperty(value = "还款频次，varchar(10)")private String repaymentFrequency;@ApiModelProperty(value = "合同类型，varchar(10)")private String contractType;@ApiModelProperty(value = "本金(含税)，decimal(20,7)")private BigDecimal principal;@ApiModelProperty(value = "已收本金，decimal(20,7)")private BigDecimal amtPrincipalVer;@ApiModelProperty(value = "未收本金，decimal(20,7)")private BigDecimal unCollectedPrincipal;@ApiModelProperty(value = "租金，decimal(20,7)")private BigDecimal rent;@ApiModelProperty(value = "已收租金，decimal(20,7)")private BigDecimal paidRent;@ApiModelProperty(value = "未收租金，decimal(20,7)")private BigDecimal unPaidRent;@ApiModelProperty(value = "利息(含税)，decimal(20,7)")private BigDecimal interest;@ApiModelProperty(value = "已收利息，decimal(20,7)")private BigDecimal amtInterestVer;@ApiModelProperty(value = "未收利息，decimal(20,7)")private BigDecimal unCollectedInterest;@ApiModelProperty(value = "irr，decimal(20,7)")private BigDecimal irr;@ApiModelProperty(value = "去税irr，decimal(20,7)")private BigDecimal removalTaxIrr;@ApiModelProperty(value = "融资品种（52-租赁同业转让 | 59-无追索权保理）字典项")private String breed;@ApiModelProperty(value = "是否融资 0-融资 1-不融资")private String financingFlag;@ApiModelProperty(value = "融资合同编号/债券代码")private String finContractNo;@ApiModelProperty(value = "融资合同状态")private String contractStatus;@ApiModelProperty(value = "子还款计划")@TableField(exist = false)private List<PdmRentPlanListDto> children = new ArrayList<>();@ApiModelProperty(value = "业务还款计划-类型 字典项70356")@TableField(exist = false)private String repaymentPlanType;@ApiModelProperty(value = "渠道名称")@TableField(exist = false)private String channelName;@ApiModelProperty(value = "助贷资方名称")@TableField(exist = false)private String assistanceLoanManagementName;@ApiModelProperty(value = "业务还款计划管理ID")@TableField(exist = false)private List<String> rentPlanManageIdList;
}
foreach:
<select id="queryRentPlanListByManageIds" resultType="com.kingtechfin.ktfcp.sif.pdm.repaymentplan.entity.po.PdmRentPlanTableEntityPo">SELECTprp.*FROMt_pdm_rent_plan prp<where><if test="list!=null">AND prp.rent_plan_manage_id in<foreach close=")" collection="list" index="" item="item" open="(" separator=",">#{item.id}</foreach></if></where>ORDER BY (prp.placement_no+0) asc</select>
