MyBatis知识总结和梳理 （3）Mybatis动态Sql

本文为上课时的实例总结，篇幅较长，耐心阅览

1.查询功能

(1)UserMapper.java

public List<User> findByUserC(User user);

(2)UserMapper.xml

<select id="findByUserC" parameterType="cn.kgc.entity.User" resultType="cn.kgc.entity.User">
      select * from smbms_user where 1 = 1
      <if test="userName !=null and userName !=''">
                and userName = #{userName}
      </if>
      <if test="userPassword !=null and userPassword !=''">
                and userPassword = #{userPassword}
      </if>
    </select>

(3)TestUserMapper.java

@Test
    public void testfindByUserC() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserName("张华");
//        user.setUserPassword("0000000");
        List<User> list = sqlSession.getMapper(UserMapper.class).findByUserC(user);
        for(User u:list){
            System.out.println(u.getGender());
        }
        sqlSession.close();
    }

2.添加功能

(1)UserMapper.java

public Integer addUserC(User user);

(2)UserMapper.xml

<insert id="addUserC" parameterType="cn.kgc.entity.User">
        insert into smbms_user(
        <trim suffixOverrides=",">
            <if test="userName !=null">
                userName,
            </if>
            <if test="userPassword !=null">
                userPassword,
            </if>
        </trim>
        )
        values(
        <trim suffixOverrides=",">
            <if test="userName !=null">
                #{userName},
            </if>
            <if test="userPassword !=null">
                #{userPassword},
            </if>
        </trim>
        )
    </insert>

(3)TestUserMapper.java

@Test
    public void testaddUserC() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserName("张华3333333");
//        user.setUserPassword("0000000");
        int flag = sqlSession.getMapper(UserMapper.class).addUserC(user);
        if (flag>0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
        sqlSession.commit();
        sqlSession.close();
    }

3.修改功能

(1)UserMapper.java

public Integer updateUserC(User user);

(2)UserMapper.xml

<update id="updateUserC" parameterType="cn.entity.User">
            update smbms_user
            <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
                <if test="userName != null">userCode = #{userName},</if>
                <if test="userPassword!= null">userPassword = #{userPassword },</if>
            </trim>
    </update>

4.功能-参数为集合list

(1)UserMapper.java

public List<User> findByIds(List<Integer> idList);

(2)UserMapper.xml

 <select id="findByIds" resultType="cn.kgc.entity.User">
        select * from smbms_user
        where id in
        <foreach collection="list" item="idList" open="(" separator="," close=")">
            #{idList}
        </foreach>
    </select>

(3)UserMapperTest.java

@Test
    public void testfindByIds() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        List<Integer> idList = new ArrayList<>();
        idList.add(7);
        idList.add(10);
        idList.add(12);
        List<User> byIds = sqlSession.getMapper(UserMapper.class).findByIds(idList);
        for(User user :byIds){
            System.out.println(user.getUserName());
        }
        sqlSession.close();
    }

5.功能-参数为map

(1)UserMapper.java

public List<User> findByMap(Map<String,Object> idmap);

(2)UserMapper.xml

<select id="findByMap" resultType="cn.entity.User">
        select * from smbms_user
        where id in
        <foreach collection="idKeys" item="idmap" open="(" separator="," close=")">
            #{idmap}
        </foreach>
    </select>

(3)TestUserMapper.java

@Test
    public void demofindByMap(){
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        Map<String, Object> map = new HashMap<String,Object>();
        List<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(5);
        map.put("idKeys",list);

        List<User> lists = sqlSession.getMapper(UserMapper.class).findByMap(map);
        for (int i = 0; i <lists.size() ; i++) {
            System.out.println(lists.get(i).getUserName());
        }
    }