Spring Spring mvc MyBatis 中使用数据库查询别名进行映射

方法1

XXMapper.xml

<select id="getfindAll" resultMap="com.hfepc.vo.andon.AndonExceptionKanban1VO">
SELECT
A.excp_id AS 'excpId',
E.line_name AS 'lineName',
D.equ_name AS 'equName',
F.type_name AS 'type',
G.end_time AS 'triggerTime',
H.end_time AS 'attendTime',
(H.end_time - G.end_time)/60 AS 'totalExceptionTime',
J.user_name AS 'userName',
K.node_name AS 'status'
FROM andon_exception A
LEFT JOIN andon_exception_equ B ON A.excp_id = B.excp_id
LEFT JOIN equ_book C ON B.eb_id = C.eb_id
LEFT JOIN equ_info D ON C.equ_id = D.equ_id
LEFT JOIN line_info E ON A.line_id = E.line_id
LEFT JOIN andon_type F ON A.type_id = F.type_id
LEFT JOIN andon_flow_prcs G ON A.excp_id = G.excp_id AND G.node_id = 1
LEFT JOIN andon_flow_prcs H ON A.excp_id = H.excp_id AND H.node_id = 2
LEFT JOIN andon_flow_prcs I ON A.excp_id = I.excp_id AND I.node_id = 3
LEFT JOIN sys_user J ON H.operator_id = J.user_id
LEFT JOIN andon_node K ON A.excp_status = K.node_id
WHERE 1=1
AND I.end_time LIKE date_format(now(),'%Y-%m-%d%')
AND E.ws_id = (
SELECT ws_id
FROM line_info
WHERE line_code = #{lineCode}
)
</select>

</mapper>

XXMapper.java

public List<AndonExceptionKanban1VO> getGG(@Param("lineCode") String lineCode);

AndonExceptionKanban1VO.java

public class AndonExceptionKanban1VO{

private Integer excpId;// 安灯异常ID
private String lineName; // 产线
private String type; // 异常类型
private String equName; // 设备
private Date triggerTime; // 提报时间
private Date attendTime; // 签到时间
private long totalExceptionTime; // 累计时间
private String userName; // 用户名(签到)
private String status; // 状态

public String getLineName() {
return lineName;
}

public void setLineName(String lineName) {
this.lineName = lineName;
}

方法2

XXMapper.xml

<select id="getGG" resultMap="voMap">
SELECT
A.excp_id AS 'excpId',
E.line_name AS 'lineName',
D.equ_name AS 'equName',
F.type_name AS 'type',
G.end_time AS 'triggerTime',
H.end_time AS 'attendTime',
(H.end_time - G.end_time)/60 AS 'totalExceptionTime',
J.user_name AS 'userName',
K.node_name AS 'status'
FROM andon_exception A
LEFT JOIN andon_exception_equ B ON A.excp_id = B.excp_id
LEFT JOIN equ_book C ON B.eb_id = C.eb_id
LEFT JOIN equ_info D ON C.equ_id = D.equ_id
LEFT JOIN line_info E ON A.line_id = E.line_id
LEFT JOIN andon_type F ON A.type_id = F.type_id
LEFT JOIN andon_flow_prcs G ON A.excp_id = G.excp_id AND G.node_id = 1
LEFT JOIN andon_flow_prcs H ON A.excp_id = H.excp_id AND H.node_id = 2
LEFT JOIN andon_flow_prcs I ON A.excp_id = I.excp_id AND I.node_id = 3
LEFT JOIN sys_user J ON H.operator_id = J.user_id
LEFT JOIN andon_node K ON A.excp_status = K.node_id
WHERE 1=1
AND I.end_time LIKE date_format(now(),'%Y-%m-%d%')
AND E.ws_id = (
SELECT ws_id
FROM line_info
WHERE line_code = #{lineCode}
)
</select>

</mapper>

XXMapper.java

public List<AndonExceptionKanban1VO> getGG(@Param("lineCode") String lineCode);

小知识点：
1、resultMap 中的对应column字段是完成SQL查询语句之后显示的字段名（而非查询前显示的字段名）
2、通过SSM框架的SpringMVC映射到前台的字段与对应的domain层无关系，而与传递的map对应的键值KEY有关系，如上中显示的userName字段但是domain中显示的是attendName字段。前台映射是${xx.userName } 而非attendName
3、SQL 返回的对象对应键值关系表resultMap="voMap"。根据Map进行返回

痛苦预示着超脱