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方法