Mybatis【二】

ResultType

在一个项目中，需要关联查询，则需要建立一个实体类，需要把关联查询所要查询的所有字段写入实体类中
案例：

java代码：
package com.tzy.bean;

import java.util.Date;

public class OrderUser {
    //订单的属性
    private int oid;
    private Integer user_id;
    private String number;
    private Date createtime;
    private String note;
    //用户的属性
    private int uid;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public int getOid() {
        return oid;
    }

    public void setOid(int oid) {
        this.oid = oid;
    }

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

xml配置文件：
  <select id="findByUser" resultType="OrderUser">
      select u.id as uid,
    	u.username,
    	u.birthday,
   	 	u.sex,
    	u.address,
    	o.id as oid,
    	o.user_id as userId,
    	o.number,
    	o.createtime,
    	o.note
    	from `user` u left join `order` o
    	on u.id=o.user_id;
    </select>

ResultMap

建立查询字段与实体类成员变量的映射关系
即查询字段是user_id但是Mybatis去赋值userId
ResultMap用来实现一对多、多对多的查询，不用建立实体类
案例：

  <resultMap id="resultMap1" type="com.tzy.bean.Order">
        <id column="id" property="id"></id>
        <!--
          column:字段名
          property:成员变量名
        -->
        <result column="user_id" property="userId"></result>
        <result column="number" property="number"></result>
        <result column="createtime" property="createtime"></result>
        <result column="note" property="note"></result>
    </resultMap>
    <select id="queryAllOrders" resultMap="resultMap1">
        SELECT id,user_id,number,createtime,note FROM orderx
    </select>

• 动态标签
  if标签，where标签，forEach标签（写在xml配置文件中）

if标签
可以判断传入的参数是否为空，如果不为空则拼接sql

 <if test="username != '' and username !=null ">
           and username like '${username}%'
 </if>

where标签
1、不用在初始sql后边写where 1=1
2、不用在第一个拼接的sql前写and,但是你也可以手动写and

  <select id="queryUserBySexAndName"  parameterType="User" resultType="User">
      select * from user
     <where>
          <if test="username != '' and username !=null ">
              and username like '${username}%'
          </if>
          <if test="sex != '' and sex != null">
              and sex = #{sex}
          </if>
         <if test="address != '' and address != null">
             and address = #{address}
         </if>
     </where>
  </select>

foreach标签
向sql传递数组或List，mybatis使用foreach解析
collection：表示方法传入的集合对象的名字 collection=“xxx”
item：遍历集合时，会将集合中的元素赋值给item
open：表示你要拼接的sql以什么开始
close：表示你拼接的sql以什么结束
separator：表示拼接的分隔符
接口中的变量名不能被标签识别，必须在参数的前边加注解@Param(“xxx”)

<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
          #{id}
</foreach>

接口中实现

List<User> findByIds(@Param("ids") List<Integer> list);

一对一关联查询

站在订单的角度看，一个订单有一个用户跟它对应
查询结果可以有两种方式封装数据

resultType：指定一个自定义javaBean类
resultMap：指定映射关系（不用创建新类，使用现有的类）

• resultType方式
  resultType必须重新创建类
  创建一个新类，把所有要查询的字段全部写入

package com.tzy.bean;

import java.util.Date;

public class OrderUser {
    private int oid;
    private Integer user_id;
    private String number;
    private Date createtime;
    private String note;
    private int uid;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public int getOid() {
        return oid;
    }

    public void setOid(int oid) {
        this.oid = oid;
    }

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

xml配置文件

 <select id="findAllOrderUser" resultType="com.tzy.bean.OrderUser">
    	select o.id as oid,
    	o.user_id ,
    	o.number,
    	o.createtime,
    	o.note,
    	u.id as uid,
    	u.username,
    	u.birthday,
    	u.sex,
    	u.address
    	from  `order` o left join `user` u
    	on o.user_id=u.id;
    </select>

• resultMap方式
  不需要重新创建类，只需改造类
  使用association 标签用于成员变量的类型为自定义实体类型
  添加属性：autoMapping=“true”,自动映射

改造user类

public class Order {
    private int id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;
    private User user;
}

xml配置文件

 <resultMap id="findAllOrder2Map" type="com.tzy.bean.Order" autoMapping="true">
        <id column="id" property="id"></id>
        <result column="user_id" property="userId"></result>
        <association property="user" javaType="com.tzy.bean.User" autoMapping="true" >
            <id column="uid" property="id"></id>
        </association>
    </resultMap>
    <select id="findAllOrder2" resultMap="findAllOrder2Map">
   		select o.id as id,
	    	o.user_id as userId,
	    	o.number,
	    	o.createtime,
	    	o.note,
	    	u.id as uid,
	    	u.username,
	    	u.birthday,
	    	u.sex,
	    	u.address
    		from  `order` o left join `user` u
    	on o.user_id=u.id;
    </select>

一对多关联查询

站在用户的角度看，一个用户有多个订单跟他对应
使用collection标签 映射List<元素>

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //变量类型是集合List
    private List<Order> orders;
}

xml配置文件

 <resultMap id="findAllUsersMap" type="com.tzy.bean.User" autoMapping="true">
            <id property="id" column="uid"></id>
            <collection property="orders" ofType="com.tzy.bean.Order" autoMapping="true">
                <id property="id" column="oid"></id>
            </collection>
    </resultMap>
    <select id="findAllUsers" resultMap="findAllUsersMap">
    	select u.id as uid,
    		u.username,
    		u.birthday,
    		u.sex,
    		u.address,
    		o.id as oid,
    		o.user_id as userId,
    		o.number,
	    	o.createtime,
    		o.note
    		from `user` u left join `order` o
    	on u.id=o.user_id;
    </select>