/**
* @author xiaoxiao
* @date 2021/9/22 10:33
* 库存价格查询
**/
--------对应的是OmsStockGoodsMapper.xml文件
它们与周哥写的返回结果都一样
@Data
public class StockGoods{
private Integer id;
private GoodsEntity goodsEntity;
/**
* 商品编号
*/
private String stockGoodsId;
//其余属性略写。。。。
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cqp.eop.mapper.OmsStockGoodsMapper">
<resultMap id="StockAndGoods" type="com.cqp.eop.entity.StockGoods">
<id column="id" property="id"/>
<result column="GOODS_ID" jdbcType="VARCHAR" property="stockGoodsId" />
<result column="BATCH_NUMBER" jdbcType="VARCHAR" property="stockBatchNumber" />
<result column="SYSTEM_CODE" jdbcType="VARCHAR" property="stockSystemCode" />
<result column="STOCK_NUM" jdbcType="VARCHAR" property="stockNum" />
<result column="APP_ID" jdbcType="VARCHAR" property="stockAppId" />
<result column="AUTH_CODE" jdbcType="VARCHAR" property="stockAuthCode" />
<result column="PRODUCTION_DATE" jdbcType="VARCHAR" property="stockProductionDate" />
<result column="EXPIRY_DATE" jdbcType="VARCHAR" property="stockExpiryDate" />
<result column="SALE_PRICE" jdbcType="VARCHAR" property="stockSalePrice" />
<result column="OMS_CREATE_DATE" jdbcType="TIMESTAMP" property="stockOmsCreateDate" />
<result column="OMS_DATE" jdbcType="TIMESTAMP" property="stockOmsDate" />
<result column="OMS_STATUS" jdbcType="INTEGER" property="stockOmsStatus" />
<result column="OMS_TRANS_ERROR" jdbcType="VARCHAR" property="stockOmsTransError" />
<result column="OMS_BIZ_ID" jdbcType="VARCHAR" property="stockOmsBizId" />
<result column="ROW_NUM" jdbcType="INTEGER" property="stockRowNum" />
<result column="ORG_CODE" jdbcType="VARCHAR" property="stockOrgCode" />
<association property="goodsEntity" javaType="com.cqp.eop.entity.GoodsEntity">
<result column="MD_NUMBER" jdbcType="VARCHAR" property="mdNumber" />
<result column="SYSTEM_CODE" jdbcType="VARCHAR" property="systemCode" />
<result column="MD_CODE" jdbcType="VARCHAR" property="mdCode" />
<result column="ORG_CODE" jdbcType="VARCHAR" property="orgCode" />
<result column="GOODS_NAME" jdbcType="VARCHAR" property="goodsName" />
<result column="GENERAL_NAME" jdbcType="VARCHAR" property="generalName" />
<result column="SPECIFICATION" jdbcType="VARCHAR" property="specification" />
<result column="UNIT" jdbcType="VARCHAR" property="unit" />
<result column="ALLOW_NO" jdbcType="VARCHAR" property="allowNo" />
<result column="PRODUCT_NAME" jdbcType="VARCHAR" property="productName" />
<result column="IS_CHINESE_DRUG" jdbcType="VARCHAR" property="isChineseDrug" />
<result column="VALIDITY" jdbcType="VARCHAR" property="validity" />
<result column="RECOMMENDED_PRICE" jdbcType="VARCHAR" property="recommendedPrice" />
<result column="TAX_RATE" jdbcType="VARCHAR" property="taxRate" />
<result column="PROVIDER_NAME" jdbcType="VARCHAR" property="providerName" />
<result column="PRODUCT_ADDRESS" jdbcType="VARCHAR" property="productAddress" />
<result column="DOSAGE_FORM" jdbcType="VARCHAR" property="dosageForm" />
<result column="DRUG_TYPE" jdbcType="VARCHAR" property="drugType" />
<result column="NATIONAL_PRICE" jdbcType="VARCHAR" property="nationalPrice" />
<result column="VENDOR_CODE" jdbcType="VARCHAR" property="vendorCode" />
<result column="IS_DELETE" jdbcType="VARCHAR" property="isDelete" />
<result column="OMS_CREATE_DATE" jdbcType="TIMESTAMP" property="omsCreateDate" />
<result column="OMS_DATE" jdbcType="TIMESTAMP" property="omsDate" />
<result column="OMS_STATUS" jdbcType="INTEGER" property="omsStatus" />
<result column="OMS_TRANS_ERROR" jdbcType="VARCHAR" property="omsTransError" />
<result column="OMS_BIZ_ID" jdbcType="VARCHAR" property="omsBizId" />
</association>
</resultMap>
<sql id="Base_Column_List">
GOODS_ID, BATCH_NUMBER, SYSTEM_CODE, STOCK_NUM, APP_ID, AUTH_CODE, PRODUCTION_DATE,
EXPIRY_DATE, SALE_PRICE, OMS_CREATE_DATE, OMS_DATE, OMS_STATUS, OMS_TRANS_ERROR,
OMS_BIZ_ID, ROW_NUM, ORG_CODE
</sql>
<!--查询所有-->
<select id="selectAll" resultMap="StockAndGoods">
SELECT
goods.MD_NUMBER, goods.SYSTEM_CODE, goods.MD_CODE, goods.ORG_CODE, goods.GOODS_NAME, goods.GENERAL_NAME, goods.SPECIFICATION,
goods.UNIT, ALLOW_NO, goods.PRODUCT_NAME, goods.IS_CHINESE_DRUG, goods.VALIDITY, goods.RECOMMENDED_PRICE, goods.TAX_RATE,
goods.PROVIDER_NAME, goods.PRODUCT_ADDRESS, goods.DOSAGE_FORM, goods.DRUG_TYPE, goods.NATIONAL_PRICE, goods.VENDOR_CODE,
goods.IS_DELETE, goods.OMS_CREATE_DATE, goods.OMS_DATE, goods.OMS_STATUS, goods.OMS_TRANS_ERROR, goods.OMS_BIZ_ID,
stock.GOODS_ID, stock.BATCH_NUMBER, stock.SYSTEM_CODE, stock.STOCK_NUM, stock.APP_ID, stock.AUTH_CODE, stock.PRODUCTION_DATE,
stock.EXPIRY_DATE, stock.SALE_PRICE, stock.OMS_CREATE_DATE, stock.OMS_DATE, stock.OMS_STATUS, stock.OMS_TRANS_ERROR,
stock.OMS_BIZ_ID, stock.ROW_NUM, stock.ORG_CODE
from oms_stock_goods stock
LEFT join oms_goods goods
on goods.MD_CODE=stock.GOODS_ID
and stock.SYSTEM_CODE = goods.SYSTEM_CODE
</select>
</mapper>
第二种方式:extends="BaseResultMap"
import io.swagger.annotations.ApiModelProperty;
/**
* @author xiaoxiao
* @version 1.0.0
* @date 2021/10/16 22:37
*/
//@JsonInclude(Include.NON_NULL)
--------对应的是OmsStockGoodsDao.xml文件
/**
* OmsStockGoods 为驱动类,必须保证驱动类的属性保持完整,对应的表就是驱动表【库存】;、
* GoodsStock 为非驱动类,若驱动类与非驱动类的属性有重复,只需删除非驱动类中重复的属性即可
*/
public class GoodsStock extends OmsStockGoods {
/**
* 商品名称
*/
@ApiModelProperty(value="商品名称")
private String goodsName;
/**
* 通用名
*/
@ApiModelProperty(value="通用名")
private String generalName;
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cqp.eop.dao.OmsStockGoodsDao">
<resultMap id="BaseResultMap" type="com.cqp.eop.domain.OmsStockGoods">
<!--@mbg.generated-->
<!--@Table oms_stock_goods-->
<id column="GOODS_ID" property="goodsId" />
<id column="BATCH_NUMBER" property="batchNumber" />
<id column="SYSTEM_CODE" property="systemCode" />
<result column="STOCK_NUM" property="stockNum" />
<result column="APP_ID" property="appId" />
<result column="AUTH_CODE" property="authCode" />
<result column="PRODUCTION_DATE" property="productionDate" />
<result column="EXPIRY_DATE" property="expiryDate" />
<result column="SALE_PRICE" property="salePrice" />
<result column="OMS_CREATE_DATE" property="omsCreateDate" />
<result column="OMS_DATE" property="omsDate" />
<result column="OMS_STATUS" property="omsStatus" />
<result column="OMS_TRANS_ERROR" property="omsTransError" />
<result column="OMS_BIZ_ID" property="omsBizId" />
<result column="ROW_NUM" property="rowNum" />
<result column="ORG_CODE" property="orgCode" />
</resultMap>
<resultMap id="GoodsStockMap" type="com.cqp.eop.domain.model.GoodsStock" extends="BaseResultMap">
<result column="GOODS_NAME" property="goodsName" />
<result column="GENERAL_NAME" property="generalName" />
<result column="SPECIFICATION" property="specification" />
<result column="UNIT" property="unit" />
<result column="ALLOW_NO" property="allowNo" />
<result column="PRODUCT_NAME" property="productName" />
<result column="IS_CHINESE_DRUG" property="isChineseDrug" />
<result column="VALIDITY" property="validity" />
<result column="RECOMMENDED_PRICE" property="recommendedPrice" />
<result column="TAX_RATE" property="taxRate" />
<result column="PROVIDER_NAME" property="providerName" />
<result column="PRODUCT_ADDRESS" property="productAddress" />
<result column="DOSAGE_FORM" property="dosageForm" />
<result column="DRUG_TYPE" property="drugType" />
<result column="NATIONAL_PRICE" property="nationalPrice" />
<result column="VENDOR_CODE" property="vendorCode" />
</resultMap>
<sql id="Base_Column_List">
GOODS_ID, BATCH_NUMBER, SYSTEM_CODE, STOCK_NUM, APP_ID, AUTH_CODE, PRODUCTION_DATE,
EXPIRY_DATE, SALE_PRICE, OMS_CREATE_DATE, OMS_DATE, OMS_STATUS, OMS_TRANS_ERROR,
OMS_BIZ_ID, ROW_NUM, ORG_CODE
</sql>
<sql id="Stock_Column">
${alias}.GOODS_ID, ${alias}.BATCH_NUMBER, ${alias}.SYSTEM_CODE, ${alias}.STOCK_NUM, ${alias}.APP_ID, ${alias}.AUTH_CODE,${alias}.PRODUCTION_DATE,
${alias}.EXPIRY_DATE, ${alias}.SALE_PRICE, ${alias}.OMS_CREATE_DATE, ${alias}.OMS_DATE, ${alias}.OMS_STATUS, ${alias}.OMS_TRANS_ERROR,
${alias}.OMS_BIZ_ID, ${alias}.ROW_NUM, ${alias}.ORG_CODE
</sql>
<sql id="Goods_Column">
${alias}.GOODS_NAME, ${alias}.GENERAL_NAME, ${alias}.SPECIFICATION,
${alias}.UNIT, ${alias}.ALLOW_NO, ${alias}.PRODUCT_NAME,${alias}.IS_CHINESE_DRUG, ${alias}.VALIDITY, ${alias}.RECOMMENDED_PRICE, ${alias}.TAX_RATE,
${alias}.PROVIDER_NAME, ${alias}.PRODUCT_ADDRESS, ${alias}.DOSAGE_FORM, ${alias}.DRUG_TYPE, ${alias}.NATIONAL_PRICE, ${alias}.VENDOR_CODE
</sql>
<select id="selectByPrimaryKey" parameterType="map" resultMap="BaseResultMap">
<!--@mbg.generated-->
select
<include refid="Base_Column_List" />
from oms_stock_goods
where GOODS_ID = #{goodsId}
and BATCH_NUMBER = #{batchNumber}
and SYSTEM_CODE = #{systemCode}
</select>
<select id="query" resultType="com.cqp.eop.domain.model.GoodsStock" resultMap="GoodsStockMap">
select
<include refid="Stock_Column" >
<property name="alias" value="stock"/>
</include>,
<include refid="Goods_Column" >
<property name="alias" value="goods"/>
</include>
from oms_stock_goods stock
left join oms_goods goods on stock.GOODS_ID = goods.MD_CODE and stock.SYSTEM_CODE =goods.SYSTEM_CODE
<where>
<if test="batchNumber!=null and batchNumber !='' " >
and stock.BATCH_NUMBER = #{batchNumber}
</if>
<if test="systemCode!= null and systemCode != ''">
and stock.SYSTEM_CODE = #{systemCode}
</if>
<if test="orgCode!= null and orgCode != ''">
and stock.ORG_CODE = #{orgCode}
</if>
<if test="goodsId!=null and goodsId !='' " >
and stock.GOODS_ID = #{goodsId}
</if>
<if test="goodsName!=null and goodsName !='' " >
and goods.GOODS_NAME like '%' #{goodsName} '%'
</if>
</where>
</select>
</mapper>