MyBtis动态sql及多表查询

本文主要是介绍MyBtis动态sql及多表查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MyBatis映射文件深入

返回主键

userGeneratedKeys

向数据库保存一个user对象后，然后在控制台记录下此新增的user的主键值（id）
userGeneratedKeys属性，此方式支持主键自增

1.接口方法

    /**
     * 向数据库保存一个user对象后, 然后在控制台记录下此新增user的主键值(id)
     * useGeneratedKeys属性 此方式只支持主键自增
     * @param user
     */
   public void addUser(User user);

2.mapper.xml配置文件

   <insert id="addUser" parameterType="User" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        insert into user(username,birthday,sex,address)values
        (#{username},#{birthday},#{sex},#{address})
    </insert>

3.测试代码

 @Test
    public void addUser(){
        //获取sqlSession会话
        SqlSession sqlSession = MybatisUtil.openSession();
        //通过代理加载接口中的方法
        GetGenerateKeysUserMapper mapper = sqlSession.getMapper(GetGenerateKeysUserMapper.class);
        //创建对象
        User user = new User();
        user.setUsername("小明");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("上海");
        //执行方法
        mapper.addUser(user);
        //提交事务，释放资源
        MybatisUtil.commit(sqlSession);
        MybatisUtil.close(sqlSession);

        //获取id
        System.out.println(user.getId());
    }

selectKey

相关属性
keyColumn 表示主键列
keyProperty实体主键属性
resultType实体主键属性类型
order 表示此标签内SQL语句在insert之前执行还是之后执行
after:之后执行
before：之前执行
LAST_INSERT_ID()函数主要用于获取最后一次插入数据时的id
1.接口方法

   /**
     * 向数据库保存一个user对象后, 然后在控制台记录下此新增user的主键值(id)
     * LAST_INSERT_ID()函数 该函数是mysql提供的一个高级查询的函数，主要用于获取最后一次插入数据时的id
     * @param user
     */
   void addUser1(User user);

2.mapper.xml配置文件

    <insert id="addUser1" parameterType="User">
        <selectKey keyColumn="id" keyProperty="id" resultType="int" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into user(username,birthday,sex,address)values
        (#{username},#{birthday},#{sex},#{address})
    </insert>

3.测试类

   @Test
    public void addUser1(){
        SqlSession sqlSession = MybatisUtil.openSession();
        GetGenerateKeysUserMapper mapper = sqlSession.getMapper(GetGenerateKeysUserMapper.class);
        //创建对象
        User user = new User();
        user.setUsername("karyy");
        user.setBirthday(new Date());
        user.setSex("女");
        user.setAddress("深圳");

        mapper.addUser1(user);

        MybatisUtil.commit(sqlSession);
        MybatisUtil.close(sqlSession);
        System.out.println(user.getId());
    }

动态SQL

if标签

1. if 接口

    /**
     * 把id和username封装到user对象中，将user对象中不为空的属性作为查询条件
     * @param user
     * @return
     */
    public List<User>findByAndUserNameIf(User user);

2. mapper.xml配置

 <!--
        if标签 条件判断
        where标签  相当于 where 1=1 功能，如果没有条件情况下 where语句不在sql语句拼接
        可以去掉第一个 and 或者 or
    -->

    <!--把id和username封装到user对象中，将user对象中不为空的属性作为查询条件-->
    <select id="findByAndUserNameIf" parameterType="User" resultType="User">
        select * from user
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="username != null">
                and username = #{username}
            </if>
        </where>
    </select>

3. 测试类

   @Test
    public void findByAndUserNameIf(){
        SqlSession sqlSession = MybatisUtil.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("jack");
        List<User> userList = mapper.findByAndUserNameIf(user);
        for (User user1 : userList) {
            System.out.println(user1);
        }
        MybatisUtil.commit(sqlSession);
        MybatisUtil.close(sqlSession);
    }

set标签

1. set接口

    /**
     * 动态更新user表数据，如果该属性有值就更新，没有值不做处理
     * @param user
     */
    public void updateUserSet(User user);

2. mapper.xml接口

<!--动态更新user表数据，如果该属性有值就更新，没有值不做处理-->
    <select id="updateUserSet" parameterType="User">
        update user
        <set>
            <if test="username != null">
                username = #{username}
            </if>
            <if test="birthday != null">
                birthday = #{birthday}
            </if>
            <if test="sex != null">
                sex = #{sex}
            </if>
            <if test="address != null">
                address = #{address}
            </if>
        </set>
        where id = #{id}
    </select>

3. 测试类

   @Test
    public void updateUserSet(){
        SqlSession sqlSession = MybatisUtil.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(53);
        user.setUsername("java");
        mapper.updateUserSet(user);
        MybatisUtil.commit(sqlSession);
        MybatisUtil.close(sqlSession);

    }

forEachList标签

1. forEachList接口

    /**
     * 根据多个id查询，user对象的集合
     * @param list
     * @return
     */
    public List<User>findUsersForEachList(List<Integer> list);

2. forEachList.xml配置文件

 <!--
        collection代表要遍历的集合元素
        open代表语句的开始部分
        close代表结束部分
        item代表遍历集合的每个元素，生成的变量名
        sperator代表分隔符
    -->
    <select id="findUsersForEachList" parameterType="list" resultType="User">
        select * from user where id in
        <foreach collection="list" open="(" close=")" item="id" separator=",">
           #{id}
        </foreach>
    </select>

3. forEachList类

  @Test
    public void findUsersForEachList(){
        SqlSession sqlSession = MybatisUtil.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        ArrayList<Integer> list = new ArrayList<>();
        list.add(49);
        list.add(50);
        list.add(52);
        List<User> userList = mapper.findUsersForEachList(list);
        for (User user : userList) {
            System.out.println(user);
        }

        MybatisUtil.close(sqlSession);

    }

forEachArray标签

1. forEachArray接口

    /**
     * 普通数组
     * @param ids
     * @return
     */
    public List<User>findUserForEachArray(Integer[] ids);

2. forEachArray.xml配置文件

     <select id="findUserForEachArray" parameterType="int[]" resultType="User">
        select * from user where id in
        <foreach collection="array" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </select>

3. forEachArray测试类

   @Test
    public void findUserForEachArray(){
        SqlSession sqlSession = MybatisUtil.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        ArrayList<Integer> list = new ArrayList<>();
        list.add(49);
        list.add(50);
        list.add(52);
        Integer[] ids = list.toArray(new Integer[list.size()]);
        List<User> userList = mapper.findUserForEachArray(ids);
        for (User user : userList) {
            System.out.println(user);
        }

        MybatisUtil.close(sqlSession);

    }

复杂查询

一对多查询

1. 接口实现

   /**
     * 根据用户的id查询订单(一对多)
     * @param id
     * @return
     */
    public User findByIdWithOrder(Integer id);

2. xml配置

  <!--一对多-->
    <resultMap id="UserMap" type="User">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="birthday" property="birthday"></result>
        <result column="sex" property="sex"></result>
        <!--
            property="orderList" 关联实体集合的属性名
            ofType="com.itfxp.domain.Order" 关联实体的java类型（集合泛型的类型）
        -->
        <collection property="order" ofType="Order">
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="money" property="money"></result>
        </collection>
    </resultMap>
    <select id="findByIdWithOrder" parameterType="int" resultMap="UserMap">
        select *,o.id oid from user u,orders o 
        where u.id = o.uid and u.id = #{id}
    </select>

3. 测试**

   /**
     * 一对多
     */
    @Test
    public void findByIdWithOrder(){
        SqlSession sqlSession = MybatisUtil.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User byIdWithOrder = mapper.findByIdWithOrder(41);
        System.out.println(byIdWithOrder);
        MybatisUtil.close(sqlSession);
    }

一对一查询

1. 接口实现

    /**
     * 根据Order id查询用户
     * @param id
     * @return
     */
    public Order findByIdWithUser(int id);

2. xml配置

    <resultMap id="OrderMap" type="Order">
        <id column="id" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="money" property="money"></result>

        <!--
            property关联实体的属性名
            javaType关联实体java类型
        -->
        <association property="user" javaType="User">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="birthday" property="birthday"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
   <select id="findByIdWithUser" parameterType="int" resultMap="OrderMap">
       select * from orders o,user u
       where o.uid = u.id and o.id = #{id}
   </select>

3. 测试

    /**
     * 根据Order id查询用户
     * @return
     */
    @Test
    public void findByIdWithUser(){
        SqlSession sqlSession = MybatisUtil.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        Order byIdWithUser = mapper.findByIdWithUser(1);
        System.out.println(byIdWithUser);
    }

多对多查询

1. 接口实现

  public User findByIdWithRole(Integer id);

2. xml配置

 <!--多对多-->
    <resultMap id="userAndRoleMap" type="User">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="birthday" property="birthday"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>

        <collection property="roleList" ofType="Role">
            <id column="rid" property="id"></id>
            <result column="role_name" property="roleName"></result>
            <result column="role_desc" property="roleDesc"></result>
        </collection>
    </resultMap>
    <select id="findByIdWithRole" parameterType="int" resultMap="userAndRoleMap">
        select * from user u,role r,user_role ur
        where u.id = ur.uid and r.id = ur.rid and u.id = #{id}
    </select>

3. 测试

 /**
     * 多对多
     */
    @Test
    public void findByIdWithRole(){
        SqlSession sqlSession = MybatisUtil.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.findByIdWithRole(41);
        System.out.println(user);
        MybatisUtil.close(sqlSession);
    }

这篇关于MyBtis动态sql及多表查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！