使用SQL生成Java实体类并集成MyBatis查询语句和结果映射
使用SQL生成Java实体类并集成MyBatis查询语句和结果映射
本示例演示如何使用SQL语句生成带有@Entity、@Table和@Data注解的Java实体类，并生成对应的MyBatis查询语句和结果映射，实现数据库数据与Java对象的映射。
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实体类
@Entity
@Table(name = "DBUSRPUB.goodsbase")
@Data
public class GoodsBase {
/**
* 商品ID
*/
@Id
@Column(name = "CODE")
private String id;
/**
* 商品条形码
*/
@Column(name = "BARCODE")
private String barcode;
/**
* 商品名称
*/
@Column(name = "NAME")
private String name;
/**
* 商品英文名称
*/
@Column(name = "ENG_NAME")
private String engName;
/**
* 商品规格
*/
@Column(name = "SPECIFICATION")
private String specification;
/**
* 是否定价
*/
@Column(name = "GBISDJ")
private String isPricing;
/**
* 管理方式
*/
@Column(name = "GBMANAMODE")
private String manageMethod;
/**
* 计费方式
*/
@Column(name = "ACCOUNT_METH")
private String accountMethod;
/**
* 转移日期
*/
@Column(name = "TRANSFER_DATE")
private Date transferDate;
/**
* 商品单位
*/
@Column(name = "UNIT")
private String unit;
/**
* 纹理成分
*/
@Column(name = "TEXTURE_CONTENT")
private String textureContent;
/**
* 商品分类编码
*/
@Column(name = "CLASSIFICATION")
private String classification;
/**
* 商品品牌ID
*/
@Column(name = "BRANDID")
private String brandId;
/**
* 设计颜色
*/
@Column(name = "DESIGN_COLOR")
private String designColor;
/**
* 商品PLU码
*/
@Column(name = "PLU_CODE")
private String pluCode;
/**
* 商品质量等级
*/
@Column(name = "QUALITY_LEVEL")
private String qualityLevel;
/**
* 商品销售门店
*/
@Column(name = "SALESHOP")
private String saleShop;
/**
* 商品艺术号
*/
@Column(name = "ART_NO")
private String artNo;
/**
* 商品保质期
*/
@Column(name = "DAY_EXP")
private String dayExp;
/**
* 销售增值税率
*/
@Column(name = "SALE_VAT_RATE")
private BigDecimal saleVatRate;
/**
* 商品进价
*/
@Column(name = "PUR_P_RATE")
private BigDecimal purchasePrice;
/**
* 商品采购价
*/
@Column(name = "PUR_P")
private BigDecimal purchasePrice;
/**
* 商品最高售价
*/
@Column(name = "GBMAXSJ")
private BigDecimal maxPrice;
/**
* 商品最低售价
*/
@Column(name = "GBMINSJ")
private BigDecimal minPrice;
/**
* 进项税率
*/
@Column(name = "INPUT_VAT_RATE")
private BigDecimal inputVatRate;
/**
* 地区编码
*/
@Column(name = "REGION_CODE")
private String regionCode;
/**
* 录入人
*/
@Column(name = "INPUT_PERN")
private String inputPerson;
/**
* 录入日期
*/
@Column(name = "INPUT_DATE")
private Date inputDate;
/**
* 修改人
*/
@Column(name = "UPDATE_PERN")
private String updatePerson;
/**
* 修改日期
*/
@Column(name = "UPDATE_DATE")
private Date updateDate;
/**
* 商品税种类型
*/
@Column(name = "DUTY_TYPE")
private String dutyType;
/**
* 商品子分类编码
*/
@Column(name = "SUBCATEGORY_CODE")
private String subcategoryCode;
/**
* NCC编码
*/
@Column(name = "NCCCODE")
private String nccCode;
/**
* 市场
*/
@Column(name = "MARKET")
private String market;
/**
* 商品容量
*/
@Column(name = "CAPACITY")
private BigDecimal capacity;
/**
* 适用人数
*/
@Column(name = "SUIT_NUM")
private BigDecimal suitNum;
/**
* 商品重量
*/
@Column(name = "WEIGHT")
private BigDecimal weight;
/**
* 商品用途
*/
@Column(name = "MARK_USE")
private String markUse;
/**
* HS编码
*/
@Column(name = "HS_CODE")
private String hsCode;
/**
* 商品DIY条形码
*/
@Column(name = "GBARCODE")
private String diyBarcode;
/**
* 商品分类ID
*/
@Column(name = "GBIMMEDCATID")
private String categoryId;
}
MyBatis查询语句和结果映射
<!-- mybatis查询语句与结果映射 -->
<select id="getGoodsBaseList" resultType="com.example.demo.entity.GoodsBase">
select *
from (
select
A.GBID as id,
A.GBBARCODE as barcode,
A.GBCNAME as name,
A.GBENAME as engName,
A.GBSPEC as specification,
A.GBISDJ as isPricing,
A.GBMANAMODE as manageMethod,
case A.GBWMID
when '1' then 0
when '5' then 5
when '4' then 3
else 1
end as accountMethod,
D.TRANSFER_DATE as transferDate,
A.GBUNIT as unit,
A.GBBZHL as textureContent,
A.GBCATCODE as classification,
A.GBPPCODE as brandId,
A.GBSPHS as designColor,
A.GBSPKS as pluCode,
A.GBSPDJ as qualityLevel,
A.GBSPXT as saleShop,
A.GBCSHH as artNo,
A.GBBZYJTS as dayExp,
NVL(A.GBJXTAX, 0) as saleVatRate,
A.GBNEWHSJJ as purchasePrice,
round(NVL(A.GBLASTJJ, A.GBNEWHSJJ / (1 + NVL(A.GBJXTAX, 0))), 4) as purchasePrice,
A.GBMAXSJ as maxPrice,
A.GBMINSJ as minPrice,
NVL(A.GBJXTAX, 0) as inputVatRate,
A.GBCDCODE as regionCode,
A.GBINPUTOR as inputPerson,
A.GBINPUTDATE as inputDate,
A.GBXGR as updatePerson,
A.GBXGDATE as updateDate,
A.GBFLAG3 as dutyType,
A.GBSTR4 as subcategoryCode,
A.GBSTR5 as nccCode,
A.MARKET as market,
NVL(A.GBNUM10, 0) as capacity,
NVL(A.GBNUM8, 1) as suitNum,
A.GBWEIGHT as weight,
A.GBSTR14 as markUse,
A.GBSTR6 as hsCode,
A.GBGBDIYBARCODE as diyBarcode,
A.GBIMMEDCATID as categoryId
from
DBUSRPUB.goodsbase A,
DBUSRNPOS.GOODS_TRANSFER D
where
A.GBSTATUS = 'Y'
and D.GBID = A.GBID
union
select
A.GBID as id,
A.GBBARCODE as barcode,
A.GBCNAME as name,
A.GBENAME as engName,
A.GBSPEC as specification,
A.GBISDJ as isPricing,
A.GBMANAMODE as manageMethod,
case A.GBWMID
when '1' then 0
when '5' then 5
when '4' then 3
else 1
end as accountMethod,
sysdate as transferDate,
A.GBUNIT as unit,
A.GBBZHL as textureContent,
A.GBCATCODE as classification,
A.GBPPCODE as brandId,
A.GBSPHS as designColor,
A.GBSPKS as pluCode,
A.GBSPDJ as qualityLevel,
A.GBSPXT as saleShop,
A.GBCSHH as artNo,
A.GBBZYJTS as dayExp,
NVL(A.GBJXTAX, 0) as saleVatRate,
A.GBNEWHSJJ as purchasePrice,
round(NVL(A.GBLASTJJ, A.GBNEWHSJJ / (1 + NVL(A.GBJXTAX, 0))), 4) as purchasePrice,
A.GBMAXSJ as maxPrice,
A.GBMINSJ as minPrice,
NVL(A.GBJXTAX, 0) as inputVatRate,
A.GBCDCODE as regionCode,
A.GBINPUTOR as inputPerson,
A.GBINPUTDATE as inputDate,
A.GBXGR as updatePerson,
A.GBXGDATE as updateDate,
A.GBFLAG3 as dutyType,
A.GBSTR4 as subcategoryCode,
A.GBSTR5 as nccCode,
A.MARKET as market,
NVL(A.GBNUM10, 0) as capacity,
NVL(A.GBNUM8, 1) as suitNum,
A.GBWEIGHT as weight,
A.GBSTR14 as markUse,
A.GBSTR6 as hsCode,
A.GBGBDIYBARCODE as diyBarcode,
A.GBIMMEDCATID as categoryId
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 transferDate
</select>
说明：
- 代码中的
{CHANL_CODE}是一个占位符，需要根据实际情况替换成具体的渠道代码。 - 代码中的
TerDetail[1]是一个占位符，需要根据实际情况替换成具体的查询数量限制。 - 代码中使用
NVL函数处理空值，避免出现错误。 - 代码中使用
round函数对采购价进行四舍五入，确保数据精度。 - 代码中使用
sysdate获取当前日期，并将其赋值给TRANSFER_DATE字段。
总结：
通过以上步骤，我们可以使用SQL语句生成Java实体类，并生成对应的MyBatis查询语句和结果映射。这可以帮助我们简化代码开发过程，提高开发效率。
注意：
- 代码需要根据具体的数据库结构进行调整。
- 代码中使用到的注解和配置项需要根据具体的框架版本进行调整。
- 代码中使用到的数据库表名和字段名需要根据实际情况进行修改。
希望本示例能够帮助您更好地理解如何使用SQL语句生成Java实体类并集成MyBatis查询语句和结果映射。
原文地址: https://www.cveoy.top/t/topic/oLlX 著作权归作者所有。请勿转载和采集!