使用SQL生成Java实体类并生成Mybatis查询语句与结果映射

本文介绍如何使用SQL语句生成Java实体类，并生成Mybatis查询语句和结果映射。示例包含从数据库表中获取数据，并根据字段生成实体类属性，以及编写Mybatis查询语句和结果映射配置。

SQL语句

select * from(
select A.GBID CODE,GBBARCODE BARCODE,GBCNAME NAME, GBENAME ENG_NAME,GBSPEC SPECIFICATION, GBISDJ,GBMANAMODE,
case GBWMID when '1' then 0 when '5' then 5 when '4' then 3 else 1 end account_meth, D.TRANSFER_DATE, GBUNIT UNIT,GBBZHL TEXTURE_CONTENT,GBCATCODE CLASSIFICATION,GBPPCODE BRANDID,GBSPHS DESIGN_COLOR, GBSPKS PLU_CODE,GBSPDJ QUALITY_LEVEL,GBSPXT SALESHOP,GBCSHH ART_NO,GBBZYJTS DAY_EXP, NVL(GBJXTAX,0) SALE_VAT_RATE, GBNEWHSJJ pur_p_rate,round(NVL(GBLASTJJ,GBNEWHSJJ/(1+NVL(GBJXTAX,0))),4) pur_p,GBMAXSJ ,GBMINSJ min_price,NVL(GBJXTAX,0) INPUT_VAT_RATE,GBCDCODE REGION_CODE, GBINPUTOR INPUT_PERN, GBINPUTDATE INPUT_DATE,GBXGR UPDATE_PERN,GBXGDATE UPDATE_DATE,GBFLAG3 DUTY_TYPE, GBSTR4 SUBCATEGORY_CODE,GBSTR5 NCCCODE,MARKET,NVL(GBNUM10,0) CAPACITY,NVL(GBNUM8,1) SUIT_NUM,GBWEIGHT WEIGHT,GBSTR14 MARK_USE,GBSTR6 HS_CODE,A.GBGBDIYBARCODE GBARCODE,A.GBIMMEDCATID
from DBUSRPUB.goodsbase A,DBUSRNPOS.GOODS_TRANSFER D
where A.GBSTATUS='Y' and D.GBID=A.GBID
union
select GBID CODE,GBBARCODE BARCODE,GBCNAME NAME, GBENAME ENG_NAME,GBSPEC SPECIFICATION, GBISDJ,GBMANAMODE,
case GBWMID when '1' then 0 when '5' then 5 when '4' then 3 else 1 end account_meth, sysdate TRANSFER_DATE , GBUNIT UNIT,GBBZHL TEXTURE_CONTENT,GBCATCODE CLASSIFICATION,GBPPCODE BRANDID,GBSPHS DESIGN_COLOR, GBSPKS PLU_CODE,GBSPDJ QUALITY_LEVEL,GBSPXT SALESHOP,GBCSHH ART_NO,GBBZYJTS DAY_EXP, NVL(GBJXTAX,0) SALE_VAT_RATE, GBNEWHSJJ pur_p_rate,round(NVL(GBLASTJJ,GBNEWHSJJ/(1+NVL(GBJXTAX,0))),4) pur_p,GBMAXSJ ,GBMINSJ min_price,NVL(GBJXTAX,0) INPUT_VAT_RATE,GBCDCODE REGION_CODE, GBINPUTOR INPUT_PERN, GBINPUTDATE INPUT_DATE,GBXGR UPDATE_PERN,GBXGDATE UPDATE_DATE,GBFLAG3 DUTY_TYPE, GBSTR4 SUBCATEGORY_CODE,GBSTR5 NCCCODE,MARKET,NVL(GBNUM10,0) CAPACITY,NVL(GBNUM8,1) SUIT_NUM,GBWEIGHT WEIGHT,GBSTR14 MARK_USE,GBSTR6 HS_CODE,A.GBGBDIYBARCODE GBARCODE,A.GBIMMEDCATID
from DBUSRPUB.goodsbase A
where A.GBSTATUS='Y' and exists(select 1 from DBUSRPUB.goodsmframe B where A.GBID=B.GMFGDID and B.GMFMARKET in({CHANL_CODE})) and not exists(select 1 from DBUSRNPOS.GOODSTRANSFER C where C.GBID=A.GBID))
where ROWNUM<={TerDetail[1]} order by TRANSFER_DATE

Java实体类

public class GoodsBase {
    private String code;
    private String barcode;
    private String name;
    private String engName;
    private String specification;
    private String gbisdj;
    private String gbmanamode;
    private int accountMeth;
    private Date transferDate;
    private String unit;
    private String textureContent;
    private String classification;
    private String brandid;
    private String designColor;
    private String pluCode;
    private String qualityLevel;
    private String saleshop;
    private String artNo;
    private int dayExp;
    private double saleVatRate;
    private double purPRate;
    private double purP;
    private double gbmaxsj;
    private double minPrice;
    private double inputVatRate;
    private String regionCode;
    private String inputPern;
    private Date inputDate;
    private String updatePern;
    private Date updateDate;
    private String dutyType;
    private String subcategoryCode;
    private String ncccode;
    private String market;
    private int capacity;
    private int suitNum;
    private double weight;
    private String markUse;
    private String hsCode;
    private String gbarcode;
    private String gbimmedcatid;
    // getter and setter methods
}

Mybatis查询语句及结果映射

<select id="selectGoodsBase" parameterType="map" resultMap="GoodsBaseResultMap">
    select *
    from (
        select A.GBID CODE,A.GBBARCODE BARCODE,A.GBCNAME NAME,A.GBENAME ENG_NAME,A.GBSPEC SPECIFICATION,A.GBISDJ,A.GBMANAMODE, 
        case A.GBWMID when '1' then 0 when '5' then 5 when '4' then 3 else 1 end ACCOUNT_METH, D.TRANSFER_DATE, A.GBUNIT UNIT,A.GBBZHL TEXTURE_CONTENT,A.GBCATCODE CLASSIFICATION,A.GBPPCODE BRANDID,A.GBSPHS DESIGN_COLOR,A.GBSPKS PLU_CODE,A.GBSPDJ QUALITY_LEVEL,A.GBSPXT SALESHOP,A.GBCSHH ART_NO,A.GBBZYJTS DAY_EXP, NVL(A.GBJXTAX,0) SALE_VAT_RATE,A.GBNEWHSJJ PUR_P_RATE,round(NVL(A.GBLASTJJ,A.GBNEWHSJJ/(1+NVL(A.GBJXTAX,0))),4) PUR_P,A.GBMAXSJ,A.GBMINSJ MIN_PRICE,NVL(A.GBJXTAX,0) INPUT_VAT_RATE,A.GBCDCODE REGION_CODE,A.GBINPUTOR INPUT_PERN,A.GBINPUTDATE INPUT_DATE,A.GBXGR UPDATE_PERN,A.GBXGDATE UPDATE_DATE,A.GBFLAG3 DUTY_TYPE,A.GBSTR4 SUBCATEGORY_CODE,A.GBSTR5 NCCCODE,A.MARKET,NVL(A.GBNUM10,0) CAPACITY,NVL(A.GBNUM8,1) SUIT_NUM,A.GBWEIGHT WEIGHT,A.GBSTR14 MARK_USE,A.GBSTR6 HS_CODE,A.GBGBDIYBARCODE GBARCODE,A.GBIMMEDCATID
        from DBUSRPUB.goodsbase A, DBUSRNPOS.GOODS_TRANSFER D
        where A.GBSTATUS='Y' and D.GBID=A.GBID 
        union 
        select A.GBID CODE,A.GBBARCODE BARCODE,A.GBCNAME NAME,A.GBENAME ENG_NAME,A.GBSPEC SPECIFICATION,A.GBISDJ,A.GBMANAMODE, 
        case A.GBWMID when '1' then 0 when '5' then 5 when '4' then 3 else 1 end ACCOUNT_METH, sysdate TRANSFER_DATE, A.GBUNIT UNIT,A.GBBZHL TEXTURE_CONTENT,A.GBCATCODE CLASSIFICATION,A.GBPPCODE BRANDID,A.GBSPHS DESIGN_COLOR,A.GBSPKS PLU_CODE,A.GBSPDJ QUALITY_LEVEL,A.GBSPXT SALESHOP,A.GBCSHH ART_NO,A.GBBZYJTS DAY_EXP, NVL(A.GBJXTAX,0) SALE_VAT_RATE,A.GBNEWHSJJ PUR_P_RATE,round(NVL(A.GBLASTJJ,A.GBNEWHSJJ/(1+NVL(A.GBJXTAX,0))),4) PUR_P,A.GBMAXSJ,A.GBMINSJ MIN_PRICE,NVL(A.GBJXTAX,0) INPUT_VAT_RATE,A.GBCDCODE REGION_CODE,A.GBINPUTOR INPUT_PERN,A.GBINPUTDATE INPUT_DATE,A.GBXGR UPDATE_PERN,A.GBXGDATE UPDATE_DATE,A.GBFLAG3 DUTY_TYPE,A.GBSTR4 SUBCATEGORY_CODE,A.GBSTR5 NCCCODE,A.MARKET,NVL(A.GBNUM10,0) CAPACITY,NVL(A.GBNUM8,1) SUIT_NUM,A.GBWEIGHT WEIGHT,A.GBSTR14 MARK_USE,A.GBSTR6 HS_CODE,A.GBGBDIYBARCODE GBARCODE,A.GBIMMEDCATID
        from DBUSRPUB.goodsbase A 
        where A.GBSTATUS='Y' and exists(
            select 1 from DBUSRPUB.goodsmframe B 
            where A.GBID=B.GMFGDID and B.GMFMARKET in(
                <foreach collection="chanlCodes" index="index" item="chanlCode" separator=",">
                #{chanlCode}
                </foreach>
            )
        ) and not exists(select 1 from DBUSRNPOS.GOODSTRANSFER C where C.GBID=A.GBID)
        ) 
    where ROWNUM&lt;=#{TerDetail[1]}
    order by TRANSFER_DATE
</select>

<resultMap id="GoodsBaseResultMap" type="GoodsBase">
    <result property="code" column="CODE"/>
    <result property="barcode" column="BARCODE"/>
    <result property="name" column="NAME"/>
    <result property="engName" column="ENG_NAME"/>
    <result property="specification" column="SPECIFICATION"/>
    <result property="gbisdj" column="GBISDJ"/>
    <result property="gbmanamode" column="GBMANAMODE"/>
    <result property="accountMeth" column="ACCOUNT_METH"/>
    <result property="transferDate" column="TRANSFER_DATE"/>
    <result property="unit" column="UNIT"/>
    <result property="textureContent" column="TEXTURE_CONTENT"/>
    <result property="classification" column="CLASSIFICATION"/>
    <result property="brandid" column="BRANDID"/>
    <result property="designColor" column="DESIGN_COLOR"/>
    <result property="pluCode" column="PLU_CODE"/>
    <result property="qualityLevel" column="QUALITY_LEVEL"/>
    <result property="saleshop" column="SALESHOP"/>
    <result property="artNo" column="ART_NO"/>
    <result property="dayExp" column="DAY_EXP"/>
    <result property="saleVatRate" column="SALE_VAT_RATE"/>
    <result property="purPRate" column="PUR_P_RATE"/>
    <result property="purP" column="PUR_P"/>
    <result property="gbmaxsj" column="GBMAXSJ"/>
    <result property="minPrice" column="MIN_PRICE"/>
    <result property="inputVatRate" column="INPUT_VAT_RATE"/>
    <result property="regionCode" column="REGION_CODE"/>
    <result property="inputPern" column="INPUT_PERN"/>
    <result property="inputDate" column="INPUT_DATE"/>
    <result property="updatePern" column="UPDATE_PERN"/>
    <result property="updateDate" column="UPDATE_DATE"/>
    <result property="dutyType" column="DUTY_TYPE"/>
    <result property="subcategoryCode" column="SUBCATEGORY_CODE"/>
    <result property="ncccode" column="NCCCODE"/>
    <result property="market" column="MARKET"/>
    <result property="capacity" column="CAPACITY"/>
    <result property="suitNum" column="SUIT_NUM"/>
    <result property="weight" column="WEIGHT"/>
    <result property="markUse" column="MARK_USE"/>
    <result property="hsCode" column="HS_CODE"/>
    <result property="gbarcode" column="GBARCODE"/>
    <result property="gbimmedcatid" column="GBIMMEDCATID"/>
</resultMap>

注意：

上述代码中的SQL语句和Mybatis配置仅供参考，实际应用中需要根据具体情况进行调整。
实体类中的属性类型需要根据数据库字段类型进行对应。
Mybatis结果映射的column属性需要与SQL查询语句中返回的列名一致。
代码中使用了foreach标签来遍历传入的chanlCodes参数，方便实现动态查询。
代码中使用了ROWNUM来限制查询结果数量，实际应用中需要根据具体需求选择合适的方式。
代码中使用了NVL函数来处理数据库中的空值，实际应用中需要根据具体情况进行选择。
代码中使用了round函数来对数据进行格式化，实际应用中需要根据具体需求进行选择。
代码中使用了sysdate函数来获取当前时间，实际应用中需要根据具体情况进行选择。
代码中使用了case语句来实现简单的逻辑判断，实际应用中需要根据具体需求进行选择。
代码中使用了union操作符来合并两个查询结果，实际应用中需要根据具体需求进行选择。
代码中使用了order by子句来对查询结果进行排序，实际应用中需要根据具体需求进行选择。