MyBatisMyBatis基本配备与行(三)

平等、输入映射和输出映射

mapper.xml映射文件中定义了操作数据库的sql,每条sql就是一个statement,映射文件是MyBatis的基本。

1、parameterType(输入型)

  • 简单类型(常用来SQL查询的主干型字段)
  • pojo对象(常用于insert、update操作)
  • vo对象(ViewObject –
    表现层对象,pojo的性能一般也基本型,vo的性质可以是引用类型)

第一步:定义mapper映射文件(定义了statement
id、SQL语句、parameterType、resultType)

<!--当查询条件中有多个固定数量的查询条件, 可以通过传入一个vo来进行操作,sql的传参来自vo属性的属性(user.username、user.sex)-->
<select id="findUserByVo" parameterType="cn.it.pojo.QueryVo" resultType="cn.it.pojo.User">
    SELECT * FROM user WHERE username LIKE '%${user.username}%' AND sex =#{user.sex}
</select>

第二步:定义mapper接口

List<User> findUserByVo(QueryVo queryVo);

老三步:定义ViewObject(vo封装了User对象,也得以依照需要封装其他内容,用以保存其他查询采取)

package cn.it.pojo;

import java.util.List;

/**
 * Created by Eric on 3/12/17.
 */
public class QueryVo {
    private User user;

    private List<Integer> ids;

    public User getUser() {
        return user;
    }

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

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

第四步:测试,

    @Test
    public void findUserByQueryVo() throws Exception {
        SqlSession openSession = sqlSessionFactory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);

        User user = new User();
        user.setUsername("王");
        user.setSex("2");

        QueryVo queryVo = new QueryVo();
        queryVo.setUser(user);

        List<User> userList = mapper.findUserByVo(queryVo);
        for (User userInList : userList) {
            System.out.println(userInList);
        }
    }

2、resultType(输出类型)

  • 出口简单类型(仅以SQL语句重回结果是平等履行一列是,输出类型才晤面是中央项目)
  • 输出pojo对象(selectOne)
  • 出口pojo对象列表(selectList)

首先步:定义mapper映射文件

    <!-- 仅在SQL语句返回一行一列的情况下, 才会使用基本类型作为resultType -->
    <select id="findUserCount" resultType="int">
        SELECT count(*) FROM user
    </select>

第二步:定义mapper接口

    int findUserCount();

第三步:测试

    @Test
    public void findUserCount() throws Exception {
        SqlSession openSession = sqlSessionFactory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);

        int count = mapper.findUserCount();
        System.out.println("查询结果集的行数为: " + count);
    }

二、动态SQL拼接

1、if、where、sql、foreach标签的行使

第一步:定义mapper映射文件

    <!-- 动态SQL举例: SELECT * FROM user WHERE 1=1 AND username LIKE '%${username}% AND sex=#{sex}  -->
    <select id="findUserByUsernameAndSex" resultType="user" parameterType="user">
        SELECT * FROM user
        <include refid="user_where"/>
    </select>

    <sql id="user_where">
        <!--where标签可以让你省去书写难看的 1=1 ; 并自动帮你把第一个条件sql的AND关键字去掉-->
        <where>
            <if test="username!=null and username!=''">username LIKE '%${username}%'</if>
            <if test="sex!=null and sex!=''">AND sex=#{sex}</if>
        </where>
    </sql>

    <!--SQL语句: SELECT * FROM user WHERE id IN (1,2,3,4),foreach标签前的AND可以写,如果是第一个where条件也可以不写 -->
    <select id="findUserByIds" parameterType="queryVo" resultType="user">
        SELECT * FROM user
        <where>
            <if test="ids.isEmpty() != true">
                AND <foreach collection="ids" item="element" open="id IN (" close=")" separator=",">#{element}</foreach>
            </if>
        </where>
    </select>

第二步:定义mapper接口

    List<User> findUserByUsernameAndSex(User user);

    List<User> findUserByIds(QueryVo queryVo);

其三步:定义输入型及出口类型(User、QueryVo)

第四步:测试

    @Test
    public void findUserByUsernameAndSex() throws Exception {
        SqlSession openSession = sqlSessionFactory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);

        User user1 = new User();
        User user2 = new User();
        User user3 = new User();
        User user4 = new User();

        user1.setUsername("王");
        user2.setSex("2");
        user3.setUsername("王");
        user3.setSex("2");

        System.out.println("查询'username=王': " + mapper.findUserByUsernameAndSex(user1));
        System.out.println("查询'sex=2': " + mapper.findUserByUsernameAndSex(user2));
        System.out.println("查询 '王 + 性别' 组合: " + mapper.findUserByUsernameAndSex(user3));
        System.out.println("查询'null': " + mapper.findUserByUsernameAndSex(user4));
    }

    @Test
    public void findUserByIdSet() throws Exception {
        SqlSession openSession = sqlSessionFactory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);

        List<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(16);
        list.add(30);

        QueryVo queryVo = new QueryVo();
        queryVo.setIds(list);

        List<User> userList = mapper.findUserByIds(queryVo);
        for (User user : userList) {
            System.out.println(user);
        }
    }

老三、关联查询

  • orders表和user表的关系查询
    • 遍历用户表,每个用户发生反复爆发一个依旧六只订单,称为“一对大多询问”;
    • 遍历订单阐明,每个订单必定关联一个用户,称为“一对同样询问”;

  • resultType的改变
    • user表的pojo对象啊User.java;orders表的pojo对象为Orders.java。它们只可以作为单表查询的parameterType和resultType
    • 联表查询时,字段数扩充,需要构建一个新的pojo来作为输入输出类型

      • 机动映射:
        • A、B联表查询,extend
          A表的pojo,在子类中扩展(B表字段对应之)属性,两讲明的同名字段问题能够经SQL
          alias解决
        • 亮点:只要保证resultType的pojo属性名(property)和查询结果的字段名(column)一致,MyBatis可以自动把出口结果注入及resultType对象被
        • 缺陷:多表查询,No;一对准多询问,No
      • 手动映射:
        • A、B联表查询,将中间一个pojo对象作为性能添加到其他一个pojo中
        • 长:多表查询,OK;一针对多询问,OK
        • 缺陷:配置繁琐

  • 一如既往针对性相同询问
    • 电动映射(可行)
    • 手动映射(可行)
  • 相同针对性几近询问
    • 自动映射(不可行)
    • 手动映射(可行)

1、一针对性同一询问(手动映射)

首先步:定义mapper映射文件

    <!-- 一对一: 手动映射 (列出每一个订单, 每个订单必然有一个下单用户) -->
    <select id="findOrdersAndUser" resultMap="ResultMap1">
        SELECT a.*, b.id uid, username, birthday, sex, address FROM orders a, user b WHERE a.user_id = b.id
    </select>
    <resultMap id="ResultMap1" type="cn.manual.pojo.Orders">
        <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="cn.manual.pojo.User">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="birthday" property="birthday"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>

第二步:定义mapper接口

    List<Orders> findOrdersAndUser();

第三步:定义pojo

第四步:测试

    @Test
    public void findOrdersAndUserManual() throws Exception {
        SqlSession openSession = sqlSessionFactory.openSession();
        UserMapper mapper = openSession.getMapper(Mapper.class);

        List<Orders> ordersList = mapper.findOrdersAndUser();
        for (Orders orders : ordersList) {
            System.out.println(orders + "  " + orders.getUser());
        }
    }

2、一对大多询问(手动映射)

首先步:定义mapper映射文件

    <!--一多关系: 手动映射 (列出每个用户, 每个用户可能有一笔或者多笔订单) -->
    <select id="findUserAndOrders" resultMap="ResultMap2">
        SELECT b.id uid, username, birthday, sex, address, a.* FROM orders a, user b WHERE a.user_id=b.id
    </select>
    <resultMap id="ResultMap2" type="cn.manual.pojo.User">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>

        <collection property="ordersList" ofType="cn.manual.pojo.Orders">
            <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"/>
        </collection>
    </resultMap>

第二步:定义mapper接口

    List<User> findUserAndOrders();

第三步:定义pojo

第四步:测试

    @Test
    public void findUserAndOrders() throws Exception {
        SqlSession openSession = sqlSessionFactory.openSession();
        UserMapper mapper = openSession.getMapper(Mapper.class);

        List<User> userList = mapper.findUserAndOrders();
        for (User user : userList) {
            for (Orders orders : user.getOrders()) {
                System.out.println(user + "  " + orders);
            }
        }
    }

 

以上合代码来于: https://github.com/echo1937/mybatis-demo的mybatis-exercise模块

 

网站地图xml地图