Mybatis实现修改的操作

Mybatis实现修改的操作

1 修改全部字段

接口

/**
 * 修改
 */
int update(Brand brand);

sql映射

<!--    修改-->
<update id="update">
    update tb_brand
    set brand_name   = #{brandName},
        company_name = #{companyName},
        ordered      = #{ordered},
        description  = #{description},
        status       = #{status}
    where id = #{id};
</update>

测试

  @Test
    public void testUpdate() throws IOException {
//        设置参数
        int status = 1;
        String companyName = "波导手机";
        String brandName = "波导";
        String description = "波导手机，手机中的战斗机";
        int ordered = 100;
        int id = 5;

//        封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setDescription(description);
        brand.setOrdered(ordered);
        brand.setId(id);
//    1.获取SQLSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//        2. 获取SqlSession对象
//        SqlSession sqlSession = sqlSessionFactory.openSession();
//        设置为自动提交事务
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
//        3. 获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//        4. 执行方法
        int update = brandMapper.update(brand);
        System.out.println(update);
//        5. 释放资源
        sqlSession.close();
    }

2 修改动态字段

也就是修改哪一个不一定，所以不能写死，要使用动态SQL

这里只需要更改sql配置文件

<update id="update">
    update tb_brand
    set
    <if test="brandName != null and brandName != ''">
        brand_name = #{brandName},
    </if>
    <if test="companyName != null and companyName != ''">
        company_name = #{companyName},
    </if>
    <if test="ordered != null">
        ordered = #{ordered},
    </if>
    <if test="description != null and description != ''">
        description = #{description},
    </if>
    <if test="status != null">
        status = #{status}
    </if>
    where id = #{id};
</update>

这个动态SQL存在两个bug

1. 如果status没有更改，那么description就是最后一个修改的属性，他的SQL语句后面有逗号，会导致语法错误
2. 如果全部属性都没有修改，set后面就是空的

如何解决这两个问题？

使用标签

<!--    修改-->
<update id="update">
    update tb_brand
    <set>
        <if test="brandName != null and brandName != ''">
            brand_name = #{brandName},
        </if>
        <if test="companyName != null and companyName != ''">
            company_name = #{companyName},
        </if>
        <if test="ordered != null">
            ordered = #{ordered},
        </if>
        <if test="description != null and description != ''">
            description = #{description},
        </if>
        <if test="status != null">
            status = #{status}
        </if>
    </set>
    where id = #{id};
</update>

测试

 @Test
    public void testUpdate() throws IOException {
//        设置参数
        int status = 1;
        String companyName = "波导手机";
        String brandName = "波导";
        String description = "波导手机，手机中的战斗机";
        int ordered = 200;
        int id = 5;

//        封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
//        brand.setCompanyName(companyName);
//        brand.setBrandName(brandName);
//        brand.setDescription(description);
        brand.setOrdered(ordered);
        brand.setId(id);
//    1.获取SQLSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//        2. 获取SqlSession对象
//        SqlSession sqlSession = sqlSessionFactory.openSession();
//        设置为自动提交事务
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
//        3. 获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//        4. 执行方法
        int update = brandMapper.update(brand);
        System.out.println(update);
//        5. 释放资源
        sqlSession.close();
    }