Mybatis高级映射

1、一对一查询

实现一对一查询，可以使用resultType和resultMap分别实现

（1）resultType实现

public class Batch {
 
	private int batch_id;
	private int cus_id;
	private String number;
	private Date createtime;
	private String note;
	//省略set，get
}
 
public class BatchCustomer extends Batch{
 
	private String username;
	private String acno;
    //省略set，get
}

配置mapper.xml文件

<select id="findCustomerAndBatchById" resultType="cn.com.mybatis.po.BatchCustomer">
		select batch.*,customer.username,customer.acno 
			from batch,customer where batch.cus_id=customer.cus_id
	</select>

（2）resultMap实现

使用resultMAp可以将数据库字段映射到名称不一样的相应实体类属性，重要的是，可以映射实体类中包裹的其他实体类，使用resultMap实现一对一查询映射的思路就是，创建一个批次信息包装类BatchItem，除了包含基本的属性外，还包含一个Customer实体类。

public class BatchItem {
 
	private int batch_id;
	private int cus_id;
	private String number;
	private Date createtime;
	private String note;
	private Customer customer;
    //省略set，get
}

mapper.xml配置文件

<select id="OneByOneByResultMapById" resultMap="OneByOneByResultMap">
		select batch.*,customer.username,customer.acno 
			from batch,customer where batch.cus_id=customer.cus_id 
	</select>
<!-- 遇到的问题总结
			第一个：前面的column怎么写？因为没有用别名，所以column和property名称相同
			第二个：association的property后面写的是对象的名字
            第三个：association的javaType -->
<resultMap type="cn.com.mybatis.po.BatchItem" id="OneByOneByResultMap">
		<id column="batch_id" property="batch_id"/>
		<result column="cus_id" property="cus_id"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		<association property="customer" javaType="cn.com.mybatis.po.Customer">
			<id column="cus_id" property="cus_id"/>
			<result column="username" property="username"/>
			<result column="acno" property="acno"/>
			<result column="gender" property="gender"/>
			<result column="phone" property="phone"/>
		</association>
	</resultMap>

小结：实现一对一查询，可以使用resultType，也可以使用resultMap。使用resultType实现时较为简单；使用resultMap时，对映射输出数据单独定义resultMap，实现过程有些繁琐，如果对查询结果又特殊的要求(比如JavaBean里面含有其他JavBean)，使用resultMap可以实现将关联的查询映射到JavaBean的属性中。
 

（3）一对多查询

如果这里仅仅使用resultType定义一个Java封装类，该类包含以上SQL查询出来的所有字段作为Java实体类的属性。但是为了数据层次范明，并且只返回唯一的数据，建议使用resultMap。

public class Batch {
 
	private int batch_id;
	private int cus_id;
	private String number;
	private Date createtime;
	private String note;
	private Customer customer;
    private List<Batchdetial> batchdetialList;
    //set,get
}
 
public class Batchdetial {
 
	private int id;
	private int batch_id;
	private int product_id;
	private int product_num;
 
}

mapper.xml

<select id="oneToMore" resultMap="oneToMoreresultMap">
		select batch.*,customer.username,customer.acno,batchdetial.product_id,
			batchdetial.product_num from batch,customer,batchdetial where 
				batch.cus_id = customer.cus_id and 
					batch.batch_id=batchdetial.batch_id
	</select>
	<resultMap type="cn.com.mybatis.po.BatchDemo" id="oneToMoreresultMap">
		<id column="batch_id" property="batch_id"/>
		<result column="cus_id" property="cus_id"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>association是javaType，而collection是ofType
		<association property="customer" javaType="cn.com.mybatis.po.Customer">
			<id column="cus_id" property="cus_id"/>
			<result column="username" property="username"/>
			<result column="acno" property="acno"/>
			<result column="gender" property="gender"/>
			<result column="phone" property="phone"/>
		</association>
		<collection property="batchdetialList" ofType="cn.com.mybatis.po.Batchdetial">
			<id column="id" property="id"/>
			<result column="batch_id" property="batch_id"/>
			<result column="product_id" property="product_id"/>
			<result column="product_num" property="product_num"/>
		</collection>
	</resultMap>

这例也可以使用extends标签继承上面一对一查询中的名为OneByOneByResultMap的resultMap，如下所示：

<resultMap type="cn.com.mybatis.po.BatchDemo" id="oneToMoreresultMap"         
      extends="OneByOneByResultMap">
      <collection property="batchdetialList" ofType="cn.com.mybatis.po.Batchdetial">
			<id column="id" property="id"/>
			<result column="batch_id" property="batch_id"/>
			<result column="product_id" property="product_id"/>
			<result column="product_num" property="product_num"/>
		</collection>
</resultMap>

多对多查询

public class Customer {
 
	private int cus_id;
	private String username;
	private String acno;
	private String gender;
	private String phone;
	private List<Batch> batchList;
    //set,get
}
public class Batch {
 
	private int batch_id;
	private int cus_id;
	private String number;
	private Date createtime;
	private String note;
	private List<Batchdetial> batchdetialList;
    //set  get
}
public class Batchdetial {
 
	private int id;
	private int batch_id;
	private int product_id;
	private int product_num;
	private FinancialProducts financialProducts;
    //set,get
}
public class FinancialProducts {
 
	private int product_id;
	private String name;
	private float price;
	private String detial;
	private String pic;
	private Date invasttime;
    //set,get
}

mapper.xml配置文件

<select id="moreToMore" resultMap="moreToMoreresultMap">
		select batch.*,customer.username,customer.acno,batchdetial.product_id,
			batchdetial.product_num,finacial_products.name,finacial_products.detial,
				finacial_products.price from batch,customer,batchdetial,finacial_products
					where batch.cus_id = customer.cus_id and batch.batch_id=batchdetial.batch_id
						and finacial_products.product_id=batchdetial.product_id
	</select>
	
	<resultMap type="cn.com.mybatis.po.Customer" id="moreToMoreresultMap">
		<id column="cus_id" property="cus_id"/>
		<result column="username" property="username"/>
		<result column="acno" property="acno"/>
		<result column="gender" property="gender"/>
		<result column="phone" property="phone"/>
		<collection property="batchList" ofType="cn.com.mybatis.po.Batch">
			<id column="batch_id" property="batch_id"/>
			<result column="cus_id" property="cus_id"/>
			<result column="number" property="number"/>
			<result column="createtime" property="createtime"/>
			<result column="note" property="note"/>
			<collection property="batchdetialList" ofType="cn.com.mybatis.po.Batchdetial">
				<id column="id" property="id"/>
				<result column="batch_id" property="batch_id"/>
				<result column="product_id" property="product_id"/>
				<result column="product_num" property="product_num"/>
				<association property="financialProducts" javaType="cn.com.mybatis.po.FinancialProducts">
					<id column="product_id" property="product_id"/>
					<result column="name" property="name"/>
					<result column="price" property="price"/>
					<result column="detial" property="detial"/>
					<result column="pic" property="pic"/>
					<result column="invasttime" property="invasttime"/>
				</association>
			</collection>
		</collection>
	</resultMap>

 

知识点记录：

     首先分析表与表之间的关系，画出关系图，理清楚主外键的关系，写好sql语句，然后再往下操作 

     若javaBean中存在对象，则在resultMap中使用association，使用association标签使用javaType。

      若javaBean中存在List，则在resultMao中使用collection，使用collection标签使用ofType