mybatis的动态sql和关联查询

1. 动态sql

通过mybatis提供的各种标签方法实现动态拼接sql。

1.1 if

UserMapper.xml

<select id="findUserByUserNameAndSex" parameterType="com.dml.pojo.User" resultType="com.dml.pojo.User">
        select * from user where 1=1 
        <!-- username和sex是User的字段名，如果为空就不加入此条件-->
        <if test="username != null and username != ''">
            and username like '%${username}%'
        </if>
        <if test="sex != null and sex != ''">
            and sex=#{sex}
        </if>
    </select>

UserMapper.java接口

//动态加入查询条件
public List<User> findUserByUserNameAndSex(User user);

UserMapperTest.java测试

//多条件查询,查询条件不确定
    @Test
    public void testFindUserByUserNameAndSex() throws Exception{
        SqlSession openSession = factory.openSession();
        //通过getMapper方法来实例化接口
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("小");//可以有，也可以没有
        user.setSex("2");//可以有，也可以没有
        //此时的sql语句为select * from user where 1=1 and username like '%小%' and sex='2'
        //如果sex不传值sql语句为select * from user where 1=1 and username like '%小%' 
        List<User> list = mapper.findUserByUserNameAndSex(user);
        System.out.println(list);
    }

1.2 where

where标签作用：
1) 会自动向sql语句中添加where关键字
2) 会去掉第一个条件的and关键字
可以将上面的sql改为如下：
UserMapper.xml

<!-- 封装sql条件，封装后可以重用
        id:是这个sql条件的唯一标识
     -->
<sql id="user_Where">
        <where>
            <if test="username != null and username != ''">
            and username like '%${username}%'
            </if>
            <if test="sex != null and sex != ''">
                and sex=#{sex}
            </if>
        </where>
    </sql>

<!-- 多条件查询 -->
    <select id="findUserByUserNameAndSex" parameterType="com.dml.pojo.User" resultType="com.dml.pojo.User">
        select * from user 
        <!-- 调用sql条件 -->
        <include refid="user_Where"></include>

    </select>

1.3 foreach

向sql传递数组或List，mybatis使用foreach解析，如下：
select * from user where id in(1,26,31);

1. 在pojo中定义list属性ids存储多个用户id，并添加getter/setter方法

public class QueryVo {
    private User user;
    private List<Integer> ids;//id集合
    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;
    }

}

UserMapper.xml

<select id="findUserByIds" parameterType="com.dml.pojo.QueryVo" resultType="com.dml.pojo.User">
        select * from user 
        <where>
            <if test="ids != null">
                <!-- 
                    select * from user where id in(1,26,31);
                    foreach：循环传入的集合参数
                    collection：传入的集合的变量名称
                    item:每次循环将循环出的数据放入这个变量中
                    open:循环开始拼接的字符串
                    close:循环结束拼接的字符串
                    separator：循环中拼接的分隔符

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

UserMapper.java

//通过ids数组查询
    public List<User> findUserByIds(QueryVo vo);

UserMapperTest.java测试

//通过数组ids查询用户
    @Test
    public void testFindUserByIds() throws Exception{
        SqlSession openSession = factory.openSession();
        //通过getMapper方法来实例化接口
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        QueryVo vo = new QueryVo();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(26);
        ids.add(31);
        vo.setIds(ids);
        List<User> list = mapper.findUserByIds(vo);
        System.out.println(list);
    }

2. 关联查询

2.1 一对一查询

方式1：自动映射

<!-- 一对一：自动映射 -->
    <select id="findOrdersAndUser1" resultType="com.dml.pojo.CustomerOrders">
        select a.*,b.id uid,username,birthday,sex,address from orders a,user b where a.user_id = b.id;
    </select>

UserMapper.java

//多表查询  一对一：自动映射
    public List<CustomerOrders> findOrdersAndUser1();

UserMapperTest.java测试

    @Test
    public void testFindOrdersAndUser1() throws Exception{
        SqlSession openSession = factory.openSession();
        //通过getMapper方法来实例化接口
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        List<CustomerOrders> list = mapper.findOrdersAndUser1();
        for (CustomerOrders co : list) {
            System.out.println(co.getUsername() +"======="+co.getId() +"=======" +co.getUid());
        }

    }

CustomerOrders.java

public class CustomerOrders extends Orders{
    private int uid;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
    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 String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }

方法2：手动映射

<!-- 一对一：手动映射 -->
    <!-- 
        id:resultMap的唯一标识
        type：将查询出的数据放入这个指定的对象中
        注意：手动映射需要指定数据库中标的字段名与java中pojo类的属性名称的对应关系
     -->
    <resultMap type="com.dml.pojo.Orders" id="orderAndUserResultMap">
        <!-- id标签指定主键字段对应关系
            column:列，数据库中的字段名称
            property:属性，java中pojo中的属性名称
         -->
        <id column="id" property="id"/>

        <!-- result：标签指定非主键字段的对应关系 -->
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>

        <!-- 这个标签指定单个对象的对应关系 
            property:指定将数据放入orders中的user属性中
            javaType：user属性的类型
        -->
        <association property="user" javaType="com.dml.pojo.User">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="birthday" property="birthday"/>
            <result column="address" property="address"/>
        </association>

    </resultMap>
    <select id="findOrdersAndUser2" resultMap="orderAndUserResultMap">
        select a.*,b.id uid,username,birthday,sex,address from orders a,user b where a.user_id = b.id;
    </select>

UserMapper.java

//一对一：手动映射
    public List<Orders> findOrdersAndUser2();

UserMapperTest.java

//手动    一对一：手动映射
    @Test
    public void testFindOrdersAndUser2() throws Exception{
        SqlSession openSession = factory.openSession();
        //通过getMapper方法来实例化接口
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        List<Orders> list = mapper.findOrdersAndUser2();
        for (Orders co : list) {
            System.out.println(co.getUser().getUsername() +"======="+co.getId() +"=======" +co.getUser().getId());
        }
    }

注意：需要在Orders.java中添加 private User user;和get/set方法

2.2 一对多查询

<!-- 一对多 -->
    <resultMap type="com.dml.pojo.User" id="userAndOrdersResultMap">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>

        <!-- 指定对应的集合对象关系映射
        property:将数据放入User对象中的ordersList属性中
        ofType: 指定ordersList属性的泛型类型
         -->
        <collection property="ordersList" ofType="com.dml.pojo.Orders">
            <id column="oid" 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>
    <select id="findUserAndOrders" resultMap="userAndOrdersResultMap">
        select a.*,b.id oid,user_id,number,createtime from user a,orders b where a.id = b.user_id
    </select>

UserMapper.java

//一对多
    public List<User> findUserAndOrders();

UserMapperTest.java

//一对多 
    @Test
    public void testFindUserAndOrders() throws Exception{
        SqlSession openSession = factory.openSession();
        //通过getMapper方法来实例化接口
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        List<User> list = mapper.findUserAndOrders();
        for (User user : list) {
            System.out.print(user.getId() +"======="+ user.getUsername() +"=======");
            List<Orders> list2 = user.getOrdersList();
            for (Orders orders : list2) {
                System.out.print(orders.getId() + "===");
            }
            System.out.println();
        }
    }

注意：需要在User.java 中添加private List<Orders> ordersList;提供get/set方法