场景描述:
实体Model中存放的是数字,
与数字对应的中文状态存放在码表中,
要关联码表在jsp页面来显示对应的中文状态。
码表字段:
要显示的效果:
代码思路:
1.编写Model类的扩展类,此扩展类要继承原来的Model类
public class BusUserDemandRefundAlterExt extends BusUserDemandRefundAlter {
2.在扩展Model中增加String类型的对应要关联码表显示的字段。
private String operationTypeName;
private String auditStatusName;
以及相应的set和get方法。
3.修改对应的action以及service的方法相应的返回类型为扩展的Model类。
PageResult<BusUserDemandRefundAlterExt> pageResult = PageUtil.pageSet(this.getClass(), pageSize, pageCurrent, orderField, orderDirection);
pageResult=this.alterService.getPageResultInBackstage(pageResult);
4.编写mapper文件
除了用代码生成工具自动生成的mapper之外,在新增mapper的扩展包,在下面新建同名mapper文件.
在mapper文件中
resultMap要继承自动生成的mapper文件中的resultMap
<resultMap id="BaseExtResultMap"
type="com.wongoing.bus.model.ext.BusUserDemandRefundAlterExt" extends="BaseResultMap">
<result column="OperationTypeName" property="operationTypeName"
jdbcType="VARCHAR" />
<result column="AuditStatusName" property="auditStatusName"
jdbcType="VARCHAR" />
要添加result映射,column要对应sql语句中关联查询的别名,property要对应扩展类的添加的属性名。
sql写法:
<select id="getRefundExtInBackstage" parameterType="java.util.Map"
resultMap="BaseExtResultMap">
select dra.*,buds.PassengerName ,
sc.CodeName OperationTypeName,
sc1.CodeName AuditStatusName
from bus_user_demand_refund_alter dra
left join bus_user_demand_suborder buds on buds.id = dra.OriginalSubOrderId
left join sys_code sc
on sc.CodeType="operationType"
and sc.CodeValue = dra.OperationType
left join sys_code sc1
on sc1.CodeType="auditStatus"
and sc1.CodeValue = dra.auditStatus
where dra.Auditor = 1
如果要关联一个表中的两个字段作为别名,可以left join 这个表两次 取两个别名,这个别名要和result中的column对应。
如果要关联的字段很多,还可以用这种写法:
SELECT
o.*,
bmg.MerchantName,bmg.startCityName,bmg.endCityName,
buo.*,buo.DepositPrice AS OneDepositPrice,buo.OrderUserName SonOrderUserName,buo.OrderUserPhoneNum SonOrderUserName,
(select sc.codeName from sys_code sc where o.GoodsType=sc.codeValue and codeType="goodsType") AS GoodsTypeName,
(select sc.codeName from sys_code sc where o.FlightType=sc.codeValue and codeType="flightType") AS FlightTypeName,
(select sc.codeName from sys_code sc where buo.OrderSonStatusId=sc.codeValue and codeType="orderSonStatus") AS OrderSonStatusName,
(select sc.codeName from sys_code sc where o.OrderStatusId=sc.codeValue and codeType="orderMainStatus") AS OrderMainStatusName,
(select sc.codeName from sys_code sc where o.DepositPayWay=sc.codeValue and codeType="payType") AS DepositPayWayName,
(select sc.codeName from sys_code sc where o.TailMoneyPayWay=sc.codeValue and codeType="payType") AS TailMoneyPayWayName
FROM bus_user_goodsorder o
LEFT JOIN bus_merchant_goods bmg ON o.GoodsId = bmg.Id
LEFT JOIN bus_user_goodsorderdetail buo ON buo.OrderId = o.Id
WHERE 1=1