Mybatis基于接口代理的方式来开发Dao(二)

接着上一篇继续，首先介绍输入映射：通过parameterType指定输入参数的类型，类型可以是简单类型、hashmap、pojo的包装类型。
需求分析：如果是需要完成用户信息的综合查询，查询的条件可能很复杂，那么我们可以定义一个包装类型的pojo，把所有可能用到的查询条件封装到 此pojo的包装类型里。这里我们定义为QueryVo类：

public class QueryVo {

    private  User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

}

Xml定义：

<!-- 通过queryVo来查询对象 -->
  <select id="getUserByQueryVo" parameterType="queryVo" resultType="user">
    select * from user where address like '%${user.address}%'
  </select>

测试用例：

@Test
    public void getUserByQueryVo() throws Exception {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        QueryVo vo = new QueryVo();
        User user = new User();
        user.setAddress("河南");
        vo.setUser(user);
        List<User> userList  = mapper.getUserByQueryVo(vo);
        for (User user2 : userList) {
            System.out.println(user2.getAddress());
        }
        sqlSession.close();

    }

输出映射：定义resultMap通过resultMap来封装对象

<!-- 定义resultMap来告诉mybatis帮我们如何封装字段
        type:表示这个resultMap最后封装的结果类型
         -->
        <resultMap type="orders" id="baseOrderMap">
             <id column="id" property="id"/> 
            <result column="user_id" property="userId"/>
             <result column="createtime" property="createtime"/>
            <result column="number" property="number"/>
            <result column="note" property="note"/> 
        </resultMap>


    <select id="getOrderList" resultMap="baseOrderMap">
        select * from orders ;
    </select>

测试动用例：

/**
     * 查询订单列表
     * @throws Exception
     */
    @Test
    public void getOrderList() throws Exception {
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.getOrderList();
        for (Orders orders : ordersList) {
            System.out.println(orders.getUserId());
            System.out.println(orders.getNote());
            System.out.println(orders.getCreatetime());
        }
        sqlSession.close();
    }

Mybatis的动态sql：传统的使用JDBC的方法，相信大家在组合复杂的的SQL语句的时候，需要去拼接，稍不注意哪怕少了个空格，都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题， 其通过 if, choose, when, otherwise, trim, where, set, foreach标签，可组合成非常灵活的SQL语句，从而提高开发人员的效率。下面就去感受Mybatis动态SQL的魅力吧：

Mybatis的动态sql之if判断

<!-- 动态sql -->
  <select id="getUserByCond" parameterType="user" resultType="user">
     select * from user where  1=1
     <if test="id !=null and id != '' ">
        and   id = #{id} 
     </if>
    <if test="address !=null and address != '' ">
    and  address like '%${address}%'
    </if>
  </select>

测试用例：

/**
     * 动态sql
     * @throws Exception
     */
    @Test
    public void getUserByCond() throws Exception {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        //user.setId(22);
        user.setAddress("河南");
        List<User> userList = mapper.getUserByCond(user);
        for (User user2 : userList) {
            System.out.println(user2.getAddress());
        }
        sqlSession.close();
    }

第二个动态sql之where

<!-- 动态sql -->
  <select id="getUserByCond" parameterType="user" resultType="user">
     select * from user 
     <where>
          <if test="id !=null and id != '' ">
            and   id = #{id} 
         </if>
        <if test="address !=null and address != '' ">
            and  address like '%${address}%'
        </if>
     </where>
  </select>

动态sql之foreach循环 ：

<!-- 动态sql之foreach -->
  <select id="getUserByIn" parameterType="queryVo" resultType="user">
    select * from user 
    <where>
    <!-- 
        collection: 表示我们传入参数里面的集合属性
        open:表示我们的循环sql以什么开头
        close:表示我们的循环sql以什么结尾
        separator：表示我们的循环sql用什么来做为分隔符
        item：变量，表示我们每次循环，集合中的一个元素

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

测试用例：

/**
     * 动态sql  之foreach
     * @throws Exception
     */
    @Test
    public void getUserIn() throws Exception {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        QueryVo vo = new QueryVo();
        List<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(10);
        list.add(16);
        vo.setIds(list);
        List<User> userList = mapper.getUserByIn(vo);
        for (User user : userList) {
            System.out.println(user.getId());
        }
        sqlSession.close();
    }

动态sql之sql片段：

<sql id="baseSql">
        select  id,username,birthday,sex,address from user 
    </sql>
  <!-- 动态sql之foreach -->
  <select id="getUserByIn" parameterType="queryVo" resultType="user">
        <include refid="baseSql"/>
    <where>
    <!-- 
        collection: 表示我们传入参数里面的集合属性
        open:表示我们的循环sql以什么开头
        close:表示我们的循环sql以什么结尾
        separator：表示我们的循环sql用什么来做为分隔符
        item：变量，表示我们每次循环，集合中的一个元素

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

Mybatis的关联查询
一对一查询：
自定义resultMap

    <resultMap type="orders" id="baseOrderWithUser">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        <association property="user" javaType="user" >
            <result column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="birthday" property="birthday"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>
    <select id="getOrdersWithUserBean" resultMap="baseOrderWithUser">
        SELECT * FROM orders o LEFT  JOIN USER u ON o.user_id = u.id 
    </select>

测试用例：

/**
     * mybatis的关联查询之一对一，通过自定义resultMap来实现
     * @throws Exception
     */
    @Test
    public void getOrdersWithUserBean() throws Exception {
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.getOrdersWithUserBean();
        System.out.println(ordersList.size());
        for (Orders orders : ordersList) {
            System.out.println(orders.getUser().getAddress());
        }
        sqlSession.close();
    }

一对多的关联查询，首选定义关联关系：

package com.yida.mybatis.pojo;

import java.util.Date;
import java.util.List;

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    private List<Orders> ordersList;



    public List<Orders> getOrdersList() {
        return ordersList;
    }
    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    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;
    }

    public User() {
        super();
        // TODO Auto-generated constructor stub
    }
    public User(int id, String username, Date birthday, String sex, String address) {
        super();
        this.id = id;
        this.username = username;
        this.birthday = birthday;
        this.sex = sex;
        this.address = address;
    }

}

定义resultMap

<resultMap type="user" id="baseUserWithOrders">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="birthday" property="birthday"/>
    <result column="sex" property="sex"/>
    <result column="address" property="address"/>
    <!-- 在mybatis一对多的关联关系中，为了表示集合中每一个元素的类型，通常使用ofType来表示 -->
    <collection property="orderList" ofType="orders">
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
    </collection>
  </resultMap>
  <!-- 查询mybatis一对多的关联关系 -->
  <select id="getUserWithOrders" resultMap="baseUserWithOrders">
    SELECT * FROM USER u LEFT JOIN orders o ON u.id = o.user_id   WHERE u.id = 1
  </select>

测试用例：

/**
     * mybatis的一对多关联查询
     */
    @Test
    public void getUserWithOrders() throws Exception {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserWithOrders();
        System.out.println(userList.size());
        for (User user : userList) {
            System.out.println(user.getOrderList().size());
        }
        sqlSession.close();

    }