SSM关联码表的多个字段显示中文流程以及sql写法

超、凢脫俗 2023-10-18 09:22 55阅读 0赞

### 场景描述: ###

实体Model中存放的是数字，

与数字对应的中文状态存放在码表中，

要关联码表在jsp页面来显示对应的中文状态。

### 码表字段： ###

![Image 1][]![70][]

### 要显示的效果： ###

![Image 1][]![70 1][]

### 代码思路： ###

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

要添加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

[Image 1]: 
[70]: https://img-blog.csdn.net/20180929140407659?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 1]: https://img-blog.csdn.net/2018092914041760?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70