MyBatis增删改查之添加、修改、删除操作

一、添加操作
Mapper接口

void add(Brand brand);

SQL语句

<insert id="add">
     insert into tb_brand(brand_name, company_name, ordered, description, status)
     values (#{brandName},#{companyName},#{ordered},#{description},#{status});
</insert>

测试

 @Test
 public void testAdd() throws IOException {
//接收参数
        int status = 1;
        String companyName = "波导手机";
        String brandName = "波导";
        String description= "手机中的战斗机";
        int ordered = 100;
// 处理参数

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

二、修改操作

Mapper接口

int update(Brand brand);

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>
        </set>
        where id = #{id};
</update>

测试

     @Test
    public void testUpdate() throws IOException {
    //接收参数
        int status = 0;
        String companyName = "波导手机";
        String brandName = "波导";
        String description= "手机中的战斗机";
        int ordered = 200;
        int id = 6;
    // 处理参数

    //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
       //brand.setCompanyName(companyName);
        //brand.setBrandName(brandName);
        //brand.setDescription(description);
        //brand.setOrdered(ordered);
        //brand.setBrandName(brandName);
        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();
        //3. 获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4. 执行方法
        int count = brandMapper.update(brand);
        System.out.println(count);
        //5. 释放资源
        sqlSession.close();
    }

三、删除操作

单行删除

Mapper接口

     /*
    根据id删除
     */
    void deleteById(int id);

SQL语句

<delete id="deleteById">
    delete from tb_brand where id = #{id};
</delete>

测试

    @Test
    public void testDelete() throws IOException {
    //接收参数
        int id = 5;
    // 处理参数

    //封装对象
        Brand brand = new Brand();
        //brand.setCompanyName(companyName);
        //brand.setBrandName(brandName);
        //brand.setDescription(description);
        //brand.setOrdered(ordered);
        //brand.setBrandName(brandName);
        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();
        //3. 获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4. 执行方法
        brandMapper.deleteById(id);
        //提交事务
        sqlSession.commit();
        //5. 释放资源
        sqlSession.close();
    }

批量删除

Mapper接口

    /*
    批量删除
     */
    void deleteByIds(int[] ids);

SQL语句

<delete id="deleteByIds">
      delete from tb_brand where id
       in (
        <foreach collection="array" item="id" separator="," open="("close=")">
          #{id}
         </foreach>
         );
</delete>

测试

    @Test
    public void testDeleteIds() throws IOException {
        //接收参数
        int[] ids = {1,2,3};
        // 处理参数

        //封装对象
        Brand brand = new Brand();
        //brand.setCompanyName(companyName);
        //brand.setBrandName(brandName);
        //brand.setDescription(description);
        //brand.setOrdered(ordered);
        //brand.setBrandName(brandName);
        //1. 获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2. 获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4. 执行方法
        brandMapper.deleteByIds(ids);
        //提交事务
        sqlSession.commit();
        //5. 释放资源
        sqlSession.close();
    }