Mybatis动态Sql实现查询添加修改功能

Mybatis动态Sql

1、接口

 //动态sq通过用户名和密码查询列表
    public  List<User> findByUserNameAndPwd(@Param("userName") String userName,@Param("userPassword") String userPassword);

    //动态sql添加功能
    public  Integer addUserNew(User user);

    //动态sql修改功能
    public  Integer updateUserNew(User user);
    // 动态sql查询参数list
    public  List<User> findByList(List<Integer> idList);

2、xml里面的sql实现

<select id="findByUserNameAndPwd" resultType="cn.kgc.entity.User">
        select * from  smbms_user a where 1=1
        <if test="userName!=null and userName !=''">
            and  userName like concat('%',#{userName},'%')
        </if>
        <if test="userPassword!=null and userPassword !=''">
            and  userPassword=#{userPassword}
        </if>
    </select>

    <insert id="addUserNew" parameterType="cn.kgc.entity.User">
      insert  into  smbms_user(
      <trim suffixOverrides=",">
        <if test="userCode !=null userCode !=''">
           userCode,
        </if>
        <if test="userName !=null and userName !=''">
              userName,
          </if>

      </trim>)
      values(
         <trim suffixOverrides=",">
        <if test="userCode !=null and userCode !=''">
           #{userCode},
        </if>
          <if test="userName !=null and userName !=''" >
              #{userName},
          </if>
      </trim>)
    </insert>

   <update id="updateUserNew" parameterType="cn.kgc.entity.User" >
        update  smbms_user
        <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
        <if test="userCode !=null and userCode !=''">
            userCode=#{userCode},
        </if>
        <if test="userName !=null and userName!=''">
            userName= #{userName},
        </if>
        <if test="userPassword !=null and userPassword!=''">
            userPassword= #{userPassword},
        </if>
        <if test="gender !=null and gender!=''">
            gender= #{gender},
        </if>
        <if test="birthday !=null and birthday!=''">
            birthday=#{birthday},
        </if>
        <if test="phone !=null and phone!=''">
            phone= #{phone},
        </if>
        <if test="address !=null and address!=''">
            address=#{address},
        </if>
        <if test="userRole !=null and userRole!=''">
            userRole=#{userCode},
        </if>
        <if test="createdBy !=null and createdBy!=''">
            createdBy= #{createdBy},
        </if>
        <if test="creationDate !=null and creationDate!=''">
            creationDate= #{creationDate},
        </if>
        <if test="modifyBy !=null and modifyBy!=''">
            modifyBy=#{modifyBy},
        </if>
        <if test="modifyDate !=null and modifyDate!=''">
            modifyDate=#{modifyDate},
        </if>
        </trim>

    </update>

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

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

3、测试类

 @Test
    public void testFindByUserNameAndPwd() {
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        //String  userName="";
        //String userPassword="";

        String  userName="王";
        String userPassword="";

       // String  userName="";
        // String userPassword="1111111";

        //String  userName="张";
        //String userPassword="1111111";
        List<User> list=sqlSession.getMapper(UserMapper.class).findByUserNameAndPwd(userName,userPassword);
        for(User u:list){
            System.out.println("姓名："+u.getUserName()+"  用户密码："+u.getUserPassword());
        }
        sqlSession.close();
    }
    @Test
    public void testAddUserNew() {
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        User u = new User();
        u.setUserCode("zhangsan");
        u.setUserName("李梅");


        sqlSession.getMapper(UserMapper.class).addUserNew(u);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testUpdateUserNew() {
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        User u = new User();
        u.setId(16);
        u.setUserName("李梅");


        sqlSession.getMapper(UserMapper.class).updateUserNew(u);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testFindByList() {
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        ArrayList<Integer> list=new ArrayList<>();
        list.add(7);
        list.add(8);
        list.add(15);


        List<User> list2=sqlSession.getMapper(UserMapper.class).findByList(list);
        for(User u:list2){
            System.out.println("userName:"+u.getUserName()+"   id:"+u.getId());
        }
        sqlSession.close();
    }
    @Test
    public void testFindByMapNew() {
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        HashMap<String,Object> map=new HashMap<>();
        ArrayList<Object> list=new ArrayList<>();
        list.add("5");
        list.add("15");
        map.put("map2",list);

        List<User> list2=sqlSession.getMapper(UserMapper.class).findByMapNew(map);
        for(User u:list2){
            System.out.println("userName:"+u.getUserName()+"   id:"+u.getId());
        }
        sqlSession.close();
    }