Springboot+Mybatis+Oracle实现数据增删改、批量插入和懒加载查询

AskSpecialAgreement.java

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("AskSpecialAgreement")
@ApiModel
@JsonIgnoreProperties(value = {"handler"})
public class AskSpecialAgreement implements Serializable {
    private Integer autoId = 0; // number(10) not null primary key, -- 主键 自增长id
    private String askNo; // varchar2(30)  not null, -- 询价单号
    private String BatchNo; // varchar2(30) default '1' not null, -- 批次号
    private LmSpecialAgreement LmSpecialAgreement; // varchar2(10) not null, -- 特约编码 -> LmSpecialAgreement.agreementCode
    //    private String SpecialType; // varchar2(10) not null, -- 特约类别 -> LmSpecialAgreement.
//    private String SpecialWords; // varchar2(200) not null, -- 特约话术 LmSpecialAgreement.agreementInfo
    private String PlanOther; //  varchar2(private String 40), -- 计划别的
    private String Hospital; //  varchar2(25), -- 医院
    private String DateTime; //  varchar2(20), -- 时间
    private Double PaymentLimit; // number(12,4), -- 给付限额
    private Double DeductibleExcess; // number(12,4), -- 免赔额
    private Double PaymentProportion; // number(12,4), -- 给付比例
    private Double LimiteHospital; // number(12,4), -- 住院限额
    private Double Quota; // number(12,4), -- 限额
    private Double AnnualTotalLimit; // number(12,4), -- 年度限额
    private Double PublicInsuranceTenThousand; // number(12,4), -- 公共保额(万)
    private Double LimitPaymentTenThousand; // number(12,4), -- 给付限额(万)
    private Double TotalTeamQuotaTenThousand; // number(12,4), -- 团队总限额(万)
    private String InsuranceType; // varchar2(10), -- 险种
    private String Plan; // varchar2(30), -- 计划
    private Double BelowAnnualTotalInsuranceRate; // number(12,4), -- 低于保险年度总赔付率值
    private Double YearBeforeDiscountRate; // number(12,4), -- 享前一年度总保费百分比的优惠
    private Double RefundRate; // number(12,4), -- 退费比率
    private String Frequencies; // varchar2(10) --频率

    /**
     * @Date 2020年11月27日15:49:46
     */
    private String CountryOrRegion;  // VARCHAR2(100)-- 国家或地区 输入框[100]
    private String Location;    // VARCHAR2(100)-- 就诊地 输入框[100]
    private Double AnnualAccomPayment; // number(12,4)-- 年度通融赔付金额 元
    private Double BasicInsuredAmount;//  --基本保险金额比例, 选择框[10 20 100]

    private String StandFlag1; // 备用字段1
    private String StandFlag2; // 备用2
    private String StandFlag3; // 备用3
}

LmSpecialAgreement.java

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("LmSpecialAgreement")
@JsonIgnoreProperties(value = { "handler" })
public class LmSpecialAgreement implements Serializable{
    private String agreementCode;
    private String attribute;
    private String direction;
    private String agreementInfo;
}

Mapper接口

@Repository
@Mapper
public interface AskSpecialAgreementMapper {
    AskSpecialAgreement getById(int autoId); //ok

    ArrayList<AskSpecialAgreement> getByAskNo(String askNo); //ok

    int insert(AskSpecialAgreement askSpecialAgreement); //ok

    int insertList(@Param("list") List<AskSpecialAgreement> askSpecialAgreementList); // ok

    int deleteById(int autoId); // ok

    int deleteByAskNo(String askNo); // ok

    int update(AskSpecialAgreement askSpecialAgreement); // ok
}

Mapper.xml

···
<mapper namespace="cn.com.XXX.mapper.AskSpecialAgreementMapper">

    <sql id="attr">
        AUTOID, ASKNO, BATCHNO, SPECIALCODE, SPECIALTYPE, SPECIALWORDS, PLANOTHER, HOSPITAL, DATETIME, PAYMENTLIMIT, DEDUCTIBLEEXCESS,
         PAYMENTPROPORTION, LIMITEHOSPITAL, QUOTA, ANNUALTOTALLIMIT, PUBLICINSURANCETENTHOUSAND, LIMITPAYMENTTENTHOUSAND,
          TOTALTEAMQUOTATENTHOUSAND, INSURANCETYPE, PLAN, BELOWANNUALTOTALINSURANCERATE, YEARBEFOREDISCOUNTRATE, REFUNDRATE, FREQUENCIES,
          <!--新添加 2020年11月27日16:04:46-->
          CountryOrRegion,Location,AnnualAccomPayment,BasicInsuredAmount,StandFlag1,StandFlag2,StandFlag3
    </sql>

	<!-- 字段映射 -->
    <resultMap id="BaseMap" type="cn.com.XXX.entity.AskSpecialAgreement">
        <id property="autoId" column="AUTOID"/>
        <result property="askNo" column="ASKNO"/>
        <result property="BatchNo" column="BATCHNO"/>
        
        <!-- LmSpecialAgreement属性根据SpecialCode查询的 -->
        <result property="PlanOther" column="PLANOTHER"/>
        
        <result property="Hospital" column="HOSPITAL"/>
        <result property="DateTime" column="DATETIME"/>
        <result property="PaymentLimit" column="PAYMENTLIMIT"/>
        <result property="DeductibleExcess" column="DEDUCTIBLEEXCESS"/>
        <result property="PaymentProportion" column="PAYMENTPROPORTION"/>
        <result property="LimiteHospital" column="LIMITEHOSPITAL"/>
        <result property="Quota" column="QUOTA"/>
        <result property="AnnualTotalLimit" column="ANNUALTOTALLIMIT"/>
        <result property="PublicInsuranceTenThousand" column="PUBLICINSURANCETENTHOUSAND"/>
        <result property="LimitPaymentTenThousand" column="LIMITPAYMENTTENTHOUSAND"/>
        <result property="TotalTeamQuotaTenThousand" column="TOTALTEAMQUOTATENTHOUSAND"/>
        <result property="InsuranceType" column="INSURANCETYPE"/>
        <result property="Plan" column="PLAN"/>
        <result property="BelowAnnualTotalInsuranceRate" column="BELOWANNUALTOTALINSURANCERATE"/>
        <result property="YearBeforeDiscountRate" column="YEARBEFOREDISCOUNTRATE"/>
        <result property="RefundRate" column="REFUNDRATE"/>
        <result property="Frequencies" column="FREQUENCIES"/>
        <result property="CountryOrRegion" column="CountryOrRegion"/>
        <result property="Location" column="Location"/>
        <result property="AnnualAccomPayment" column="AnnualAccomPayment"/>
        <result property="BasicInsuredAmount" column="BasicInsuredAmount"/>
        <result property="StandFlag1" column="StandFlag1"/>
        <result property="StandFlag2" column="StandFlag2"/>
        <result property="StandFlag3" column="StandFlag3"/>
    </resultMap>

	<!-- 查询懒加载 -->
    <resultMap id="special" type="cn.com.XXX.entity.AskSpecialAgreement" extends="BaseMap">
        <!-- 这是LmSpecialAgreement属性 -->
        <association property="LmSpecialAgreement" column="SpecialCode" fetchType="lazy"
                     javaType="cn.com.XXX.entity.LmSpecialAgreement"
                     select="cn.com.XXX.mapper.LmSpecialAgreementMapper.queryByCode">
        </association>

    </resultMap>

    <!-- 批量插入-->
    <insert id="insertList" timeout="2000" parameterType="cn.com.XXX.entity.AskSpecialAgreement">
        insert all
        <foreach collection="list" item="item" index="index" separator=" ">
            into ASKSPECIALAGREEMENT(<include refid="attr"/>)
            values(0, #{item.askNo,jdbcType=VARCHAR}, #{item.BatchNo,jdbcType=VARCHAR} ,#{item.LmSpecialAgreement.agreementCode,jdbcType=VARCHAR}, #{item.LmSpecialAgreement.direction,jdbcType=VARCHAR}, #{item.LmSpecialAgreement.agreementInfo,jdbcType=VARCHAR}, #{item.PlanOther,jdbcType=VARCHAR},#{item.Hospital,jdbcType=VARCHAR},
            #{item.DateTime,jdbcType=VARCHAR},#{item.PaymentLimit, jdbcType=DOUBLE},#{item.DeductibleExcess, jdbcType=DOUBLE},#{item.PaymentProportion, jdbcType=DOUBLE},#{item.LimiteHospital, jdbcType=DOUBLE},#{item.Quota,jdbcType=VARCHAR},
            #{item.AnnualTotalLimit, jdbcType=DOUBLE},#{item.PublicInsuranceTenThousand, jdbcType=DOUBLE},#{item.LimitPaymentTenThousand, jdbcType=DOUBLE},#{item.TotalTeamQuotaTenThousand, jdbcType=DOUBLE},
            #{item.InsuranceType,jdbcType=VARCHAR},#{item.Plan,jdbcType=VARCHAR},#{item.BelowAnnualTotalInsuranceRate, jdbcType=DOUBLE},#{item.YearBeforeDiscountRate,jdbcType=DOUBLE},
            #{item.RefundRate, jdbcType=DOUBLE},#{item.Frequencies, jdbcType=VARCHAR},
            <!--新添加字段-->
            #{item.CountryOrRegion, jdbcType=VARCHAR},#{item.Location, jdbcType=VARCHAR},#{item.AnnualAccomPayment, jdbcType=DOUBLE},#{item.BasicInsuredAmount, jdbcType=DOUBLE},#{item.StandFlag1, jdbcType=VARCHAR},#{item.StandFlag2, jdbcType=VARCHAR},#{item.StandFlag3, jdbcType=VARCHAR}
            )
        </foreach>
        select 1 from dual
    </insert>


    <insert id="insert" timeout="2000" parameterType="cn.com.XXX.entity.AskSpecialAgreement">
        insert into ASKSPECIALAGREEMENT(<include refid="attr"/>)
            values(0, #{askNo,jdbcType=VARCHAR}, #{BatchNo,jdbcType=VARCHAR} ,#{LmSpecialAgreement.agreementCode,jdbcType=VARCHAR}, #{LmSpecialAgreement.direction,jdbcType=VARCHAR}, #{LmSpecialAgreement.agreementInfo,jdbcType=VARCHAR}, #{PlanOther,jdbcType=VARCHAR},#{Hospital,jdbcType=VARCHAR},
            #{DateTime,jdbcType=VARCHAR},#{PaymentLimit, jdbcType=DOUBLE},#{DeductibleExcess, jdbcType=DOUBLE},#{PaymentProportion, jdbcType=DOUBLE},#{LimiteHospital, jdbcType=DOUBLE},#{Quota,jdbcType=VARCHAR},
            #{AnnualTotalLimit, jdbcType=DOUBLE},#{PublicInsuranceTenThousand, jdbcType=DOUBLE},#{LimitPaymentTenThousand, jdbcType=DOUBLE},#{TotalTeamQuotaTenThousand, jdbcType=DOUBLE},
            #{InsuranceType,jdbcType=VARCHAR},#{Plan,jdbcType=VARCHAR},#{BelowAnnualTotalInsuranceRate, jdbcType=DOUBLE},#{YearBeforeDiscountRate,jdbcType=DOUBLE},
            #{RefundRate, jdbcType=DOUBLE},#{Frequencies, jdbcType=VARCHAR},
            <!--新添加字段-->
            #{item.CountryOrRegion, jdbcType=VARCHAR},#{item.Location, jdbcType=VARCHAR},#{item.AnnualAccomPayment, jdbcType=DOUBLE},#{item.BasicInsuredAmount, jdbcType=DOUBLE},#{item.StandFlag1, jdbcType=VARCHAR},#{item.StandFlag2, jdbcType=VARCHAR},#{item.StandFlag3, jdbcType=VARCHAR}
            )
    </insert>

    <select id="getById" resultType="cn.com.XXX.entity.AskSpecialAgreement" resultMap="special">
        select <include refid="attr"/> from ASKSPECIALAGREEMENT where autoId = #{autoId} order by autoId
    </select>

    <select id="getByAskNo" resultType="cn.com.XXX.entity.AskSpecialAgreement" resultMap="special">
        select <include refid="attr"/> from ASKSPECIALAGREEMENT where askNo = #{askNo} order by autoId
    </select>

    <delete id="deleteById" parameterType="int">
        delete from ASKSPECIALAGREEMENT where AUTOID=#{autoId}
    </delete>

    <delete id="deleteByAskNo">
        delete from ASKSPECIALAGREEMENT where ASKNO=#{askNo}
    </delete>

    <update id="update"  parameterType="cn.com.XXX.entity.AskSpecialAgreement">
        update ASKSPECIALAGREEMENT set askNo=#{askNo,jdbcType=VARCHAR}, BatchNo=#{BatchNo,jdbcType=VARCHAR},
        <if test="LmSpecialAgreement.agreementCode!=null and LmSpecialAgreement.agreementCode!=''">
            SPECIALCODE=#{LmSpecialAgreement.agreementCode,jdbcType=VARCHAR},
            SPECIALTYPE=#{LmSpecialAgreement.direction,jdbcType=VARCHAR},
            SPECIALWORDS=#{LmSpecialAgreement.agreementInfo,jdbcType=VARCHAR},
        </if>
          PlanOther=#{PlanOther,jdbcType=VARCHAR}, Hospital=#{Hospital,jdbcType=VARCHAR},
          DateTime=#{DateTime,jdbcType=VARCHAR}, PaymentLimit=#{PaymentLimit,jdbcType=DOUBLE}, DeductibleExcess=#{DeductibleExcess,jdbcType=DOUBLE},
          PaymentProportion=#{PaymentProportion,jdbcType=DOUBLE}, LimiteHospital=#{LimiteHospital,jdbcType=DOUBLE} ,Quota=#{Quota,jdbcType=DOUBLE} ,
          AnnualTotalLimit=#{AnnualTotalLimit,jdbcType=DOUBLE} , PublicInsuranceTenThousand=#{PublicInsuranceTenThousand,jdbcType=DOUBLE} ,
          LimitPaymentTenThousand=#{LimitPaymentTenThousand,jdbcType=DOUBLE} , TotalTeamQuotaTenThousand=#{TotalTeamQuotaTenThousand,jdbcType=DOUBLE},
        InsuranceType=#{InsuranceType,jdbcType=VARCHAR} , Plan=#{Plan,jdbcType=VARCHAR} , BelowAnnualTotalInsuranceRate=#{BelowAnnualTotalInsuranceRate,jdbcType=DOUBLE} ,
        YearBeforeDiscountRate=#{YearBeforeDiscountRate,jdbcType=DOUBLE} , RefundRate=#{RefundRate,jdbcType=DOUBLE} ,Frequencies=#{Frequencies,jdbcType=VARCHAR},
        <!--新添加 2020年11月27日16:15:10-->
        CountryOrRegion=#{CountryOrRegion,jdbcType=VARCHAR},Location=#{Location,jdbcType=VARCHAR},AnnualAccomPayment=#{AnnualAccomPayment,jdbcType=DOUBLE},BasicInsuredAmount=#{BasicInsuredAmount,jdbcType=DOUBLE},
        StandFlag1=#{StandFlag1,jdbcType=VARCHAR},StandFlag2=#{StandFlag2,jdbcType=VARCHAR},StandFlag3=#{StandFlag3,jdbcType=VARCHAR}
        where autoId=#{autoId}
    </update>
</mapper>

LmSpecialAgreementMapper接口

public interface LmSpecialAgreementMapper{
	// 根据code查询LmSpecialAgreement
    LmSpecialAgreement queryByCode(String agreementCode);
}
···
<mapper namespace="cn.com.XXX.mapper.LmSpecialAgreementMapper">
    <select id="queryByCode" resultType="cn.com.XXX.entity.LmSpecialAgreement">
        select agreementCode,attribute,direction,agreementInfo from lmspecialagreement where agreementcode = #{agreementcode}
    </select>
</mapper>
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值