输入、输出映射-动态sql

一 输入映射-包装类型

• 需求：查询用户综合信息，需要传入查询条件（可能包括用户信息，其他信息）

User.java

package entity;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    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;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

UserCustom.java

package entity;

public class UserCustom extends User {
    //扩展User类
}

UserQuerVo.java

package entity;

public class UserQuerVo {
    private UserCustom userCustom;
        //包装UserCustom
    public UserCustom getUserCustom() {
        return userCustom;
    }

    public void setUserCustom(UserCustom userCustom) {
        this.userCustom = userCustom;
    }
}

userMapper.xml

    <!--综合信息查询 根据姓名和性别-->
    <select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
    SELECT * FROM user WHERE username = #{userCustom.username} AND sex LIKE "%${userCustom.sex}%"
    </select>

UserDaoMapperTest.java

    @Test
    public void findUserByinfo() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
        UserQuerVo userQuerVo = new UserQuerVo();
        UserCustom userCustom = new UserCustom();
        userCustom.setUsername("马云");
        userCustom.setSex("男");
        userQuerVo.setUserCustom(userCustom);

        UserCustom userCustom1= userDaoMapper.findUserByinfo(userQuerVo);
        System.out.println(userCustom1);
    }

测试结果

二 输出映射

1 resultType

• 使用resultType进行输出映射，只有查询出来的列明和pojo中的属性名一致，该列才可以映射成功

userMapper.xml

    <!--输出映射 pojo resultType-->
    <select id="findUserCount" parameterType="entity.UserQuerVo" resultType="int">
        SELECT COUNT(*) FROM  user WHERE username LIKE "%${userCustom.username}%"
    </select>

UserDaoMapper.java

    //输出映射 pojo resultType
    public int findUserCount(UserQuerVo userQuerVo) throws Exception;

UserDaoMapperTest.java

    @Test
    public void findUserCount() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
        UserQuerVo userQuerVo = new UserQuerVo();
        UserCustom userCustom = new UserCustom();
        userCustom.setUsername("马");
        userQuerVo.setUserCustom(userCustom);
        int num = userDaoMapper.findUserCount(userQuerVo);
        System.out.println(num);
    }

测试结果

2 resultMap

• 如果查询出来的列名和pojo的属性名不一致。通过定义一个resultMap对列名和pojo属性名之间作一个映射关系。
  • 1.定义 resultMap
  • 2.使用 resultMap作为输出映射类型

userMapper.xml

    <!--配置resultMap映射-->
        <resultMap id="PojoresultMap" type="entity.User">
            <id column="i" property="id"></id>
            <result column="us" property="username"></result>
            <result column="ad" property="address"></result>
        </resultMap>

    <!--输出映射 pojo resultMap-->
    <select id="findUserByResultMap" parameterType="int" resultMap="PojoresultMap">
            SELECT id i,username us, address ad FROM user WHERE id = #{userCustom.id}
        </select>

UserDaoMapper.java

    //输出映射 pojo resultMap
    public User findUserByResultMap(int id) throws Exception;

UserDaoMapperTest.java

    @Test
    public void findUserByResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
        User user = userDaoMapper.findUserByResultMap(10);
        System.out.println(user);
    }

测试结果

三 动态sql

1 解释

• mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接，组装

2 if 判断

• 需求 用户信息综合查询列表和用户信息列表总数这两个statement的定义使用动态sql。对查询条件进行判断，如果输入参数不为空才进行查询条件拼接

    <select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
        SELECT * FROM user
        <where>
            <if test="userCustom.username != null">
                <if test="userCustom.username != null and userCustom.username != ''">
                    and username = #{userCustom.username}
                </if>
                <if test="userCustom.sex != null and userCustom.sex != ''">
                    and sex like "%${userCustom.sex}%"
                </if>
            </if>
        </where>
    </select>

测试执行.png

3 sql片段

userMapper.xml

    <!--动态sql片段
    id:sql片段唯一标识

    基于单表来定义sql片段，这样的话这个sql片段可重用性才高，在sql片段可重用性才高
    在sql片段不要包括where
    -->
    <sql id="qusery_user_where">
        <if test="userCustom.username != null">
            <if test="userCustom.username != null and userCustom.username != ''">
                and username = #{userCustom.username}
            </if>
            <if test="userCustom.sex != null and userCustom.sex != ''">
                and sex like "%${userCustom.sex}%"
            </if>
        </if>
    </sql>


    <select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
    SELECT * FROM user
    <where>
        <!--sql片段引用-->
      <include refid="qusery_user_where"></include>
    </where>

4 foreach

userMapper.xml

    <!--输入list集合 foreach遍历-->
    <select id="findUserList" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
        SELECT * FROM user WHERE
        <!--collection:指定输入对象中集合属性
item:每次遍历生成对象
open:开始遍历时拼接串
close:结束遍历时拼接串
separator:遍历两个对象中需要拼接的串
-->
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                <!--每次遍历需要拼接的串-->
                id = #{id}
            </foreach>
    </select>

UserDaoMapper.java

    //
    public UserCustom findUserList(UserQuerVo userQuerVo) throws Exception;

测试

    @Test
    public void findUserList() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
        UserQuerVo userQuerVo = new UserQuerVo();
        List ids = new ArrayList();
        ids.add(1);
        userQuerVo.setIds(ids);
       UserCustom userList =  userDaoMapper.findUserList(userQuerVo);
        System.out.println(userList);
    }