JavaWeb——MyBatis（配置文件完成查询）

目录

环境准备

创建表

创建实体类

测试用例

安装MyBatisX插件​

1.查询

1.1查询所有数据

1.2查看详情

1.3条件查询

BrandMapper.xml:

散装参数的方式

封装对象的方式

Map集合的方式

1.4多条件动态条件查询 

1.5单条件动态条件查询

​ 

---

环境准备

创建表

-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand
(
    -- id 主键
    id           int primary key auto_increment,
    -- 品牌名称
    brand_name   varchar(20),
    -- 企业名称
    company_name varchar(20),
    -- 排序字段
    ordered      int,
    -- 描述信息
    description  varchar(100),
    -- 状态：0：禁用  1：启用
    status       int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok', 1);
      
select * from tb_brand;

创建实体类

package com.pojo;

public class brand {
    // id主键
    private int id;
    // 品牌名称
    private String brandName;
    // 企业名称
    private String companyName;
    // 排序字段
    private String ordered;
    // 描述信息
    private String description;
    // 状态：0禁用  1启用
    private int status;

    public int getId() {return id;}

    public void setId(int id) {this.id = id;}

    public String getBrandName() {return brandName;}

    public void setBrandName(String brandName) {this.brandName = brandName;}

    public String getCompanyName() {return companyName;}

    public void setCompanyName(String companyName) {this.companyName = companyName;}

    public String getOrdered() {return ordered;}

    public void setOrdered(String ordered) {this.ordered = ordered;}

    public String getDescription() {return description;}

    public void setDescription(String description) {this.description = description;}

    public int getStatus() {return status;}

    public void setStatus(int status) {this.status = status;}

    @Override
    public String toString() {
        return "brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered='" + ordered + '\'' +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

测试用例

安装MyBatisX插件

1.查询

1.1查询所有数据

注意：

<!--名称空间-->
<mapper namespace="com.mapper.brandMapper">
    
    <resultMap id="brandResultMap" type="brand">
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>

    <select id="selectAll" resultMap="brandResultMap">
        SELECT * FROM tb_brand
    </select>
    
</mapper>

1.2查看详情

<mapper namespace="com.mapper.BrandMapper">
    
    <resultMap id="brandResultMap" type="Brand">
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>

    <select id="selectAll" resultMap="brandResultMap">
        SELECT * FROM tb_brand
    </select>

    <select id="selectById" resultMap="brandResultMap">
        SELECT * FROM tb_brand WHERE id = #{id};
    </select>

</mapper>

1.3条件查询

BrandMapper.xml:

    <select id="selectByCondition" resultMap="brandResultMap">
        SELECT * from tb_brand
        WHERE status = #{status}
        and company_name like #{companyName}
        and brand_name like #{brandName};
    </select>

散装参数的方式

 //散装参数
 List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);

  @Test
    public void testSelectByCondition() throws IOException {
        //接受参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        //处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

        //1.加载核心配置文件，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取对应的SqlSession对象，用它执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3获取userMapper接口的代理对象(接口的实现类对象)
        BrandMapper BrandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行sql
        List<Brand> brands = BrandMapper.selectByCondition(status,companyName,brandName);//散装参数

        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }

 

封装对象的方式

    //封装对象
    List<Brand> selectByCondition(Brand brand);

    @Test
    public void testSelectByCondition() throws IOException {
        //接受参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        //处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

        //封装对象
        Brand brand = new Brand();
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setStatus(status);

        //1.加载核心配置文件，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取对应的SqlSession对象，用它执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3获取userMapper接口的代理对象(接口的实现类对象)
        BrandMapper BrandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行sql
        List<Brand> brands = BrandMapper.selectByCondition(brand);//封装对象

        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }
}

Map集合的方式

    //map集合
    List<Brand> selectByCondition(Map map);

    @Test
    public void testSelectByCondition() throws IOException {
        //接受参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        //处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

        //map集合
        Map map = new HashMap();
        map.put("status",status);
        map.put("companyName",companyName);
        map.put("brandName",brandName);

        //1.加载核心配置文件，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取对应的SqlSession对象，用它执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3获取userMapper接口的代理对象(接口的实现类对象)
        BrandMapper BrandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行sql
        List<Brand> brands = BrandMapper.selectByCondition(map);//map集合

        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }
}

.

1.4多条件动态条件查询 

 

BrandMapper.xml:

方法1：恒等式

    <!--动态条件查询-->
    <select id="selectByCondition" resultMap="brandResultMap">
        SELECT *
        from tb_brand
        WHERE 1=1
        <if test="status != null">
            and status = #{status}
        </if>
        <if test="companyName != null and companyName != ''">
            and company_name like #{companyName}
        </if>
        <if test="brandName != null and brandName != ''">
            and brand_name like #{brandName};
        </if>
    </select>

方式2：用<where>标签

    <!--动态条件查询-->
    <select id="selectByCondition" resultMap="brandResultMap">
        SELECT *
        from tb_brand
        <where>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="companyName != null and companyName != ''">
                and company_name like #{companyName}
            </if>
            <if test="brandName != null and brandName != ''">
                and brand_name like #{brandName};
            </if>
        </where>
    </select>

1.5单条件动态条件查询

 

 

    //动态单条件查询
    List<Brand> selectByConditionSingle(Brand brand);

   <!--动态单条件查询-->
    <select id="selectByConditionSingle" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <choose>
                <when test="status != null">
                    status = #{status}
                </when>
                <when test="companyName != null and companyName != ''">
                    company_name like #{companyName}
                </when>
                <when test="brand_name != null and brandName != ''">
                    brand_name like #{brandName}
                </when>
            </choose>
        </where>
    </select>

    @Test
    public void testSelectByConditionSingle() throws IOException {
        //接受参数
        int status =1;
        String companyName = "华为";
        String brandName = "华为";

        //处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

        //封装对象
        Brand brand = new Brand();
        //brand.setStatus(status);
        brand.setCompanyName(companyName);
        //brand.setBrandName(brandName);

        //1.加载核心配置文件，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取对应的SqlSession对象，用它执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3获取userMapper接口的代理对象(接口的实现类对象)
        BrandMapper BrandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行sql
        List<Brand> brands = BrandMapper.selectByConditionSingle(brand);//封装对象
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }