mybatis书写

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_term when pql.repayment_frequency = '2' then pql.total_lease_term/3 when pql.repayment_frequency = '3' then pql.total_lease_term/6 when pql.repayment_frequency = '4' then pql.total_lease_term/12 end,0) as leasePeriodsOld ,( SELECT COUNT(*) FROM t_pdm_collection pc LEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_id WHERE pc.collection_type in ('0','4') AND pc.sys_logic_del='0' and pbc.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 pc LEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_id WHERE 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 from t_pdm_redundancy_info pbc where pbc.con_bundle_id = pcc.con_bundle_id limit 1)) channel_name , ifnull((SELECT SUBSTRING_INDEX(business_region, ',', 1) FROM t_crm_cust_basic_information WHERE id = pps.channel_id), (SELECT SUBSTRING_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` l LEFT JOIN `t_pdm_rent_plan_change` c ON l.`change_id` = c.id WHERE 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` l LEFT JOIN `t_pdm_rent_plan_change` c ON l.`change_id` = c.id WHERE 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 pc LEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_id WHERE 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 pc LEFT JOIN t_pdm_redundancy_info pbc ON pbc.redundancy_relation_id = pc.redundancy_relation_id WHERE 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 paidPeriods FROM (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_id from t_plm_vehicle_lease pvl where pvl.sys_logic_del=0 union all select 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_id from t_plm_lease_essential_information pi where pi.sys_logic_del=0 ) pvl LEFT JOIN t_pcm_contract_content pcc ON pcc.con_bundle_id = pvl.contract_bundle_id LEFT JOIN t_pcm_backup_record cr ON cr.record_id = pcc.id LEFT 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_id LEFT JOIN `t_pcm_project` pp ON pp.id = pr.record_id LEFT JOIN t_pcm_project_sublist pps ON pps.project_id = pr.record_id LEFT 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=0 LEFT 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 &gt;= #{dto.startRentLeft} </if> <if test="dto.startRentRight!=null and dto.startRentRight!=''"> AND psr.start_rent &lt;= #{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 mon.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"> SELECT prp.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_model FROM t_pdm_rent_plan prp left join t_pdm_base_info pbi on pbi.rent_plan_id = prp.id LEFT JOIN t_pdm_quotation_lease pql on prp.id = pql.record_id WHERE prp.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 mon.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"> SELECT prp.* FROM t_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>