SSM学习笔记（一）——多表联查

在刚刚接触SSM的时候，最初遇到的难题便是多表联查的问题。mybatis由于他的特性一般情况下映射的结果与pojo(entity)实体类里面的属性都是一一对应的。所以如果是多张表联合查询然后返回结果集的话，由于存在pojo里面的属性不全的问题无法完成映射，因为这个问题纠结了2天。

找了许多的网站和例子也没有找到解决的方案，在一次偶然的尝试中发现了解决的方法，因此在这里做下笔记，作为留念。

需求：从Seorder,  Seorderentry,  Icitemcore获取数据显示到页面上

要求显示的数据

Seorder 表的 FBillNo

Seorderentry 表的 FMapNumber，FEntrySelfS0164

Icitemcore 表的 FNumber FShortNumbe FName

首先写sql语句

SeorderKDao.xml

<?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="cn.gsp.dao.SeorderKDao"><sql id="seorderwhere"><where><if test="FBillNo!=null and FBillNo!=''">and t1.FBillNo=#{FBillNo}</if><if test="FMapNumber!=null and FMapNumber!=''">and t2.FMapNumber=#{FMapNumber}</if><if test="FEntrySelfS0164!=null and FEntrySelfS0164!=''">and t2.FEntrySelfS0164=#{FEntrySelfS0164}</if></where></sql><select id="getByFBillNo" resultType="cn.gsp.pojo.SeorderK"  >SELECTt1.FBillNo,t2.FMapNumber,t2.FEntrySelfS0164,t3.FNumber,t3.FShortNumber,t3.FNameFROMSEOrder t1inner join SEOrderEntry t2 on t1.FInterID=t2.FInterIDinner join t_ICItemCore t3 on t2.FItemID=t3.FItemID<include refid="seorderwhere"></include></select></mapper>

然后根据xml写出对应的dao接口，dao中的方法名字与xml中select的id要相同

SeorderKDao.java

package cn.gsp.dao;import java.util.List;import cn.gsp.pojo.SeorderK;public interface SeorderKDao {/** * 通过查询条件来查询数据 */public List<SeorderK> getByFBillNo()throws Exception;}

之后是重点，实体类

这里要写4个实体类，3个是数据库中对应的表的实体类，第四个则是映射结果集的实体类，这点非常重要。

1.Seorder.java

package cn.gsp.pojo;public class Seorder {private String FBillNo;private int FInterID;private Seorderentry seorderentry;private Icitemcore icitemcore;public Icitemcore getIcitemcore() {return icitemcore;}public void setIcitemcore(Icitemcore icitemcore) {this.icitemcore = icitemcore;}public Seorderentry getSeorderentry() {return seorderentry;}public void setSeorderentry(Seorderentry seorderentry) {this.seorderentry = seorderentry;}public String getFBillNo() {return FBillNo;}public void setFBillNo(String fBillNo) {FBillNo = fBillNo;}public int getFInterID() {return FInterID;}public void setFInterID(int fInterID) {FInterID = fInterID;}@Overridepublic String toString() {return "Seorder [FBillNo=" + FBillNo + ", FInterID=" + FInterID + ", seorderentry=" + seorderentry + "]";}}

2.Seorderentry.java

package cn.gsp.pojo;public class Seorderentry { private String FMapNumber;private String FEntrySelfS0164;private int FInterID;private int FItemID;private Icitemcore icitemcore;private Seorder seorder;public Seorder getSeorder() {return seorder;}public void setSeorder(Seorder seorder) {this.seorder = seorder;}public Icitemcore getIcitemcore() {return icitemcore;}public void setIcitemcore(Icitemcore icitemcore) {this.icitemcore = icitemcore;}public String getFMapNumber() {return FMapNumber;}public void setFMapNumber(String fMapNumber) {FMapNumber = fMapNumber;}public String getFEntrySelfS0164() {return FEntrySelfS0164;}public void setFEntrySelfS0164(String fNewNo) {FEntrySelfS0164 = fNewNo;}public int getFInterID() {return FInterID;}public void setFInterID(int fInterID) {FInterID = fInterID;}public int getFItemID() {return FItemID;}public void setFItemID(int fItemID) {FItemID = fItemID;}@Overridepublic String toString() {return "Seorderentry [FMapNumber=" + FMapNumber + ", FEntrySelfS0164=" + FEntrySelfS0164 + ", FInterID="+ FInterID + ", FItemID=" + FItemID + ", icitemcore=" + icitemcore + "]";}}

3.Icitemcore.java

package cn.gsp.pojo;public class Icitemcore {private String FNumber;private String FShortNumber;private String FName;private int FItemID;private Seorderentry seorderentry;private Seorder seorder;public Seorder getSeorder() {return seorder;}public void setSeorder(Seorder seorder) {this.seorder = seorder;}public Seorderentry getSeorderentry() {return seorderentry;}public void setSeorderentry(Seorderentry seorderentry) {this.seorderentry = seorderentry;}public String getFNumber() {return FNumber;}public void setFNumber(String fNumber) {FNumber = fNumber;}public String getFShortNumber() {return FShortNumber;}public void setFShortNumber(String fShortNumber) {FShortNumber = fShortNumber;}public String getFName() {return FName;}public void setFName(String fName) {FName = fName;}public int getFItemID() {return FItemID;}public void setFItemID(int fItemID) {FItemID = fItemID;}@Overridepublic String toString() {return "Icitemcore [FNumber=" + FNumber + ", FShortNumber=" + FShortNumber + ", FName=" + FName+ ", FItemID=" + FItemID + "]";}}

4.SeorderK.java   这个正是映射结果的实体类

package cn.gsp.pojo;public class SeorderK extends Seorder{private String FMapNumber;private String FEntrySelfS0164;private String FNumber;private String FShortNumber;private String FName;public String getFMapNumber() {return FMapNumber;}public void setFMapNumber(String fMapNumber) {FMapNumber = fMapNumber;}public String getFEntrySelfS0164() {return FEntrySelfS0164;}public void setFEntrySelfS0164(String fEntrySelfS0164) {FEntrySelfS0164 = fEntrySelfS0164;}public String getFNumber() {return FNumber;}public void setFNumber(String fNumber) {FNumber = fNumber;}public String getFShortNumber() {return FShortNumber;}public void setFShortNumber(String fShortNumber) {FShortNumber = fShortNumber;}public String getFName() {return FName;}public void setFName(String fName) {FName = fName;}}

写完实体类后，开始业务层代码，这里只是简单的写一个显示列表的代码

SeorderService.java   Service的接口

package cn.gsp.service;import java.util.List;import cn.gsp.pojo.SeorderK;public interface SeorderService {List<SeorderK> getByFBillNo() throws Exception;}

接着写接口对应的实现方法

SeoderServiceImpl.java

package cn.gsp.service;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;import cn.gsp.dao.SeorderKDao;import cn.gsp.pojo.SeorderK;@Service@Transactional(readOnly = true)public class SeoderServiceImpl implements SeorderService{@Autowiredprivate SeorderKDao seorderkDao;@Overridepublic List<SeorderK> getByFBillNo() throws Exception {List<SeorderK> list=seorderkDao.getByFBillNo();return list;}}

接下来是controller层

package cn.gsp.controller;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.ui.Model;import org.springframework.web.bind.annotation.RequestMapping;import cn.gsp.pojo.SeorderK;import cn.gsp.service.SeorderService;@Controller@RequestMapping("/seorder")public class SeorderController {@Autowiredprivate SeorderService seorderService;@RequestMapping("/list")public String list(Model model) throws Exception{//查询数据列表和数据总数List<SeorderK> resutList = seorderService.getByFBillNo();model.addAttribute("resutList", resutList);return "seorder";}}

根据最后的return"seorder" 再写一个返回的页面就可以了

seorder.jsp 的主要部分

<div class="resutList"><div class="col-lg-12"><div class="panel panel-default"><div class="panel-heading">信息列表</div><!-- /.panel-heading --><table class="table table-bordered table-striped"><thead><tr><th>订单编号</th><th>客户代码</th><th>新编码</th><th>GSP长代码</th><th>GSP短代码</th><th>GSP名称</th></tr></thead><tbody><c:forEach items="${resutList}" var="resutList"><tr><td>${resutList.FBillNo}</td><td>${resutList.FMapNumber}</td><td>${resutList.FEntrySelfS0164}</td><td>${resutList.FNumber}</td><td>${resutList.FShortNumber}</td><td>${resutList.FName}</td></tr></c:forEach></tbody></table></div></div></div></div>

以上的内容全为初学者的一己之见，如有问题和错误，感谢提出宝贵的建议，谢谢！！！