mybatis多表关联查询

mybatis多表关联查询

1.一对一

Association元素​

Association元素处理“has-one”（一对一）这种类型关系

<association property="author" column="blog_author_id" javaType=" Author">

<id property="id" column="author_id"/>

<result property="username" column="author_username"/>

</association>

例子：一本书对应一个作者​

model：

public class Book{

private int id;

private String bookname;

private Author​ author;

 

..................（省略get set方法）​

}​

mapper.xml

  <resultMap id="BaseResultMap" type="insuranceOrder" >

  <id column="id" property="id" jdbcType="INTEGER" />

  <result column="bookname" property="bookname" jdbcType="VARCHAR" />

  <association property="author" column="blog_author_id" javaType=" Author">

<id property="id" column="author_id"/>

<result property="name" column="name"/>

<result property="blog_author_id" column="blog_author_id"/>

</association>

  </resultMap>

  <select id="select" resultMap="BaseResultMap">

    from tb_insurance_order

    ORDER BY createDate DESC

  </select>

dao:

public interface BookDao{

Book ​select();

}​

controller处省略

jsp

 ​​​​​​​${list.bookname}

${list.anthor.name}

​

Collection元素​

一对多或者多对多（这里是多对多的例子）​

mapper.xml​

  <resultMap id="BaseResultMap2" type="insuranceOrder" >

  <id column="id" property="id" jdbcType="INTEGER" />

    <result column="cusName" property="cusname" jdbcType="VARCHAR" />

    <result column="carNo" property="carno" jdbcType="VARCHAR" />

    <result column="cusPhone" property="cusphone" jdbcType="VARCHAR" />

    <result column="payType" property="paytype" jdbcType="INTEGER" />

    <result column="address" property="address" jdbcType="VARCHAR" />

    <result column="businessPrice" property="businessprice" jdbcType="DOUBLE" />

    <result column="compelPrice" property="compelprice" jdbcType="DOUBLE" />

    <result column="cartaxPrice" property="cartaxprice" jdbcType="DOUBLE" />

    <result column="totalPrice" property="totalprice" jdbcType="DOUBLE" />

    <result column="scale" property="scale" jdbcType="VARCHAR" />

    <result column="newcarPrice" property="newcarprice" jdbcType="DOUBLE" />

    <result column="danger" property="danger" jdbcType="VARCHAR" />

    <result column="startDate" property="startdate" jdbcType="DATE" />

    <result column="staus" property="staus" jdbcType="INTEGER" />

    <result column="secondPromote" property="secondpromote" jdbcType="VARCHAR" />

    <result column="entry" property="entry" jdbcType="VARCHAR" />

    <result column="attribution" property="attribution" jdbcType="VARCHAR" />

    <result column="createDate" property="createdate" jdbcType="DATE" />

    <collection property="insuranceFollowDto" ofType="insuranceFollowDto">

        <id column="id1" property="id" jdbcType="INTEGER" />

   <result column="revisit" property="revisit" jdbcType="INTEGER" />

   <result column="revisitDate" property="revisitdate" jdbcType="DATE" />

   <result column="insureId" property="insureid" jdbcType="INTEGER" />

   <result column="remark" property="remark" jdbcType="VARCHAR" />

    <result column="flag" property="flag" jdbcType="INTEGER" />

    </collection>

  </resultMap>

 

​

 

  model:

public class InsuranceOrder {

    private Integer id;

    private String cusname;

    private String carno;

    private String cusphone;

    private Integer paytype;

    private String address;

    private Double businessprice;

    private Double compelprice;

    private Double cartaxprice;

    private Double totalprice;

    private String scale;

    private Double newcarprice;

    private String danger;

    private Date startdate;

    private Integer staus;

    private String secondpromote;

    private String entry;

    private String attribution;

    private Date createdate;

    private String note;

    

    private List insuranceFollowDto;

......................................(省略get set方法)​

}

dao

public interface InsuranceOrderDao​{

public List selectFollowInsure();​

}

service和controller省略

jsp

<table class="table table-border table-bordered table-bg table-hover table-sort">

<thead>

<tr class="text-c">

<th width="25">序号</th>

<th>客户姓名</th>

<th>车牌号</th>

<th>联系电话</th>

<th>商业险保费</th>

<th>交强险保费</th>

<th>车船税</th>

<th>总费用</th>

<th>新车购置价</th>

<th>出险次数及赔款</th>

<th>起保时间</th>

<th>回访方式</th>

<th>回访日期</th>

<th>回访备注</th>

<c:if test="${tableListSize>0}">

<th width="65">操作</th>

</c:if>

</tr>

</thead>

<tbody>

<c:forEach items="${insuranceOrderList }" varStatus="i" var="i1">

<c:forEach var="i2" items="${insuranceOrderList.insuranceFollowDto}" varStatus="s2">

<tr>

<td>${s2.index + 1}</td>

<td>${i1.cusname}</td>

<td>${i1.carno}</td>

<td>${i1.cusphone}</td>

<td>${i1.businessprice}</td>

<td>${i1.compelprice}</td>

<td>${i1.cartaxprice}</td>

<td>${i1.totalprice}</td>

<td>${i1.newcarprice}</td>

<td>${i1.danger}</td>

<td>

<fmt:formatDate pattern="yyyy-MM-dd" value="${i1.startdate}" />

</td>

<td>

<c:if test="${i2.revisit==1}">

电话回访

</c:if>

<c:if test="${i2.revisit==2}">

短信回访

</c:if>

</td>

<td>

<fmt:formatDate pattern="yyyy-MM-dd" value="${i2.revisitdate}" />

</td>

<td>${i2.remark}</td>

<c:if test="${tableListSize>0 }">

<td class="f-14">

<c:forEach  items="${tableList}" var="button" varStatus="status">

<c:if test="${status.index!=0 }">|</c:if>

<a rel="${button.description }"action="<%=basePath %>${button.resUrl }?id=${i2.id}" onclick="setClick(this)" class="tablelink"> 

${button.menuName }

</a>

</c:forEach>

</td>

</c:if>

</tr> 

</c:forEach>

</c:forEach>

</tbody>

</table>

​

 

这里jsp用到了双层循环（这里我需要强调一下，刚开始我1层循环里没有用var=“i1”

在二层循环里直接写${insuranceOrderList .insuranceFollowDto},程序报错

错误是:

java.lang.NumberFormatException: For input string: "insuranceFollowDto"​ ）

Discriminator元素​

这个元素我暂时用的少，这里就不多介绍了

有时候一条数据库查询可能会返回包括各种不同的数据类型的结果集。Discriminator（识别器）元素被设计来处理这种情况，以及其它像类继承层次情况。识别器非常好理解，它就像java里的switch语句。