Mybatis 03 增删改查及动态SQL

BrandMapper.java

public interface BrandMapper {
    List<Brand> selectAll(); //查询所有
    Brand selectBrandById(int id);//根据id查询
    void insertBrand(Brand brand);//添加
    void deleteBrand(int id);//删除
    void updateBrand(Brand brand); //修改
    List<Brand> selectByName(String brand_name);//根据名字模糊查询
    List<Brand> selectByMap(@Param("map")Map<String,String> map);//根据map类型参数查询 #{map.  }
    List<Brand> selectByArg(@Param("brand_name")String brand_name,@Param("company_name")String company_name);//@Param("参数名") 参数传递
    List<Brand> selectByBrand(Brand brand);//多条件查询 动态SQL
    void updateBrandPart(Brand brand); //多个选项修改 动态SQL
    void deleteList(List<Integer> list); //批量删除 参数为List集合
    void deleteArray(@Param("ids") Integer[] ids);//批量删除 参数为Array数组
}

Mapper.xml

<mapper namespace="com.mapper.BrandMapper">
    <select id="selectAll" resultType="brand">
        select * from tb_brand
    </select>
    <select id="selectBrandById" parameterType="int" resultType="brand">
        select * from tb_brand where id=#{id}
    </select>
    <insert id="insertBrand" parameterType="brand">
        insert into tb_brand(id,brand_name,company_name,ordered,description,status)
            values (null,#{brand_name},#{company_name},#{ordered},#{description},#{status})
    </insert>
    <delete id="deleteBrand" parameterType="int">
        delete from tb_brand where id=#{id}
    </delete>
    <update id="updateBrand" parameterType="brand">
        update tb_brand set brand_name=#{brand_name},company_name=#{company_name},ordered=#{ordered},
        description=#{description},status=#{status} where id=#{id}
    </update>
    <select id="selectByName" parameterType="string" resultType="brand">
        select * from tb_brand where brand_name like #{brand_name}
    </select>
    <select id="selectByMap" parameterType="map" resultType="brand">
        select* from tb_brand where company_name like #{map.company_name} and description like #{map.description}
    </select>
    <select id="selectByArg" resultType="brand">
        select * from tb_brand where brand_name like #{brand_name} and company_name=#{company_name}
    </select>
 <!--批量删除-->
    <delete id="deleteList" parameterType="list">
        delete from tb_brand
        <where>
            <foreach collection="list" item="id" open="id in(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </delete>
    <delete id="deleteArray" parameterType="arraylist">
        delete from tb_brand
        <where>
            <foreach collection="ids" item="id" open="id in(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </delete>
    <!--动态sql 多条件查询-->
    <select id="selectByBrand" resultType="brand">
        select * from tb_brand
       <where> <!--添加where关键字，去除第一个and。 trim的写法：<trim prefix="where" prefixOverrides="and">-->
            <if test="status!=null">
                and status=#{status}
            </if>
            <if test="brand_name!=null and brand_name!=''">
                and brand_name like #{brand_name}
            </if>
            <if test="company_name!=null and company_name!=''">
               and  company_name like #{company_name}
            </if>
       </where>
    </select>


    <update id="updateBrandPart" parameterType="brand">
        update tb_brand
            <set>  <!--添加set关键字，去除最后一个逗号 <trim prefix="set" suffixOverrides=",">-->
                <if test="brand_name!=null and brand_name!=''">
                    brand_name =#{brand_name},
                </if>
                <if test="company_name!=null and company_name!=''">
                    company_name=#{company_name},
                </if>
                <if test="ordered!=null">
                    ordered=#{ordered},
                </if>
                <if test="status!=null">
                    status=#{status},
                </if>
                <if test="description!=null and description!=''">
                    description=#{description},
                </if>
            </set>
            where id=#{id}
    </update>
</mapper>

TestMapper

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.mapper.BrandMapper;
import com.entity.Brand;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestMapper {

    @Test
    public void testSelectAll() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        List<Brand> brandList = mapper.selectAll();
        System.out.println(brandList);
        //4. 释放资源
        sqlSession.close();
    }
    @Test
    public void testSelectByPage() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        PageHelper.startPage(2,2);//pageNum表示当前页码值，pageSize表示每页显示的记录数
        // 把查询到的结果封装到 PageInfo 类中
        List<Brand> list = mapper.selectAll();
        PageInfo<Brand> pageInfo =new PageInfo<Brand>(list);
        System.out.println("总记录数"+pageInfo.getTotal());
        System.out.println("总页数"+pageInfo.getPages());
        System.out.println("每页显示的记录数"+pageInfo.getPageSize());
        System.out.println("当前页码"+pageInfo.getPageNum());
        System.out.println("当前页对应的记录"+pageInfo.getList());
        //4. 释放资源
        sqlSession.close();
    }

    @Test
    public void testSelectById() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        Brand brand = mapper.selectBrandById(2);
        System.out.println(brand);
        //4. 释放资源
        sqlSession.close();
    }
    @Test
    public void testInsertBrand() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(false);
//        ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0)
        Brand brand = new Brand(null,"王老吉","广发",6,"怕上火喝加多宝",0);
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.insertBrand(brand);
        sqlSession.commit();
        //4. 释放资源
        sqlSession.close();
    }

    @Test
    public void testupdateBrand() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(false);
//        ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0)
        Brand brand = new Brand(4,"aaaa","aaaa",6,"aaaaa",0);
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.updateBrand(brand);
        sqlSession.commit();
        //4. 释放资源
        sqlSession.close();
    }

    @Test
    public void testdeleteBrand() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(false);
//        ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0)
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.deleteBrand(4);
        sqlSession.commit();
        //4. 释放资源
        sqlSession.close();
    }
    @Test
    public void testSelectByName() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        String name="松鼠";
        List<Brand> brandList = mapper.selectByName("%"+name+"%");
        System.out.println(brandList);
        //4. 释放资源
        sqlSession.close();
    }
    @Test
    public void testSelectByMap() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(false);
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        Map<String,String> map = new HashMap<String,String>();
        map.put("company_name","%广发%");
        map.put("description","%上火%");
        List<Brand> brandList = mapper.selectByMap(map);
        System.out.println(brandList);
        //4. 释放资源
        sqlSession.close();
    }
    @Test
    public void testSelectByArg() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(false);
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        List<Brand> brandList = mapper.selectByArg("%王老吉%","%广发%");
        System.out.println(brandList);
        //4. 释放资源
        sqlSession.close();
    }
    /*多条件查询*/
    @Test
    public void testSelectByBrand() throws IOException {
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(false);
        //3. 执行sql
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        Brand brand = new Brand();
        brand.setBrand_name("%王老吉%");
        brand.setCompany_name("%广发%");
        List<Brand> brandList = mapper.selectByBrand(brand);
        System.out.println(brandList);
        //4. 释放资源
        sqlSession.close();
    }

    /*多选项修改*/
    @Test
    public void testupdateBrandPart() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        Brand brand = new Brand(4,"零食很忙",null,6,"便宜",1);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.updateBrandPart(brand);
        sqlSession.close();
    }
    /*批量删除List*/
    @Test
    public void testdeleteList() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        List<Integer> list = Arrays.asList(5,6);
        mapper.deleteList(list);
        sqlSession.close();
    }
    /*批量删除Array*/
    @Test
    public void testdeleteArray() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

        Integer[] array = {5,6};
        mapper.deleteArray(array);
        sqlSession.close();
    }
}