后端学习笔记（5）--查询

最新推荐文章于 2024-09-14 21:53:48 发布

Lucky_Husky

最新推荐文章于 2024-09-14 21:53:48 发布

阅读量468

点赞数 3

文章标签：学习笔记 mybatis

本文链接：https://blog.csdn.net/qwsadsdga/article/details/141113929

版权

2.查询

*数据库表的字段名称和实体类的属性名称不一样，则不能自动封装数据

*起别名：对不一样的列名起别名，让别名和实体类的属性名一样

*缺点：每次查询都要定义一次别名

*sql片段：缺点：不灵活

*resultMap:

1.定义标签

2.在标签中，使用resultMap属性替换resultType 属性

<resultMap id="brandResult" 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>

1.查询所有数据

*步骤：

1.编写接口方法：Mapper接口

*参数：无

*结果：List

List<Brand> selectAll();

2.编写SQL语句：SQL映射文件

<select id=“selectAll" resultType="brand">
    select * from tb_brand;
</select>

3.执行方法，测试

2.查看详情

1.编写接口方法：Mapper接口

*参数：id

*结果：Brand

Brand selectById(int id);

2.编写SQL语句：SQL映射文件

3.执行方法，测试

<select id="selectById" parameterType="int" resultType="brand">
    select * from tb_brand where id = #{id};
</select>

3.条件查询

1.多条件查询

1.编写接口方法：Mapper接口

*参数：所有查询条件

*结果：List

List<Brand> selectByCondition(@Param("status")int status,@Param("companyName")String companyName,@Param("brandName")String brandName);

List<Brand> selectByCondition(Brand brand);

List<Brand> selectByCondition(Map map);

2.编写SQL语句：SQL映射文件

3.执行方法，测试

<select id="selectByCondition" resultMap="brandResultMap">
    select * from tb_brand
    where
    	status = #{status}
		and company_name like #{cpmpanyNmae}
		and brand_name like #{brandName}
</select>

MyBatisTest.java

    @Test
    public void testSelectByCondition() 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);*/

        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对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //3.获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行方法
       /* List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);*/

        //List<Brand> brands = brandMapper.selectByCondition(brand);

        List<Brand> brands = brandMapper.selectByCondition(map);
        System.out.println(brands);

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

2.多条件动态条件查询

*SQL语句会随着用户的输入或外部条件的变化而编号，成为动态SQL

<select id="selectByCondition" resultMap="brandResultMap">
    select *
    from tb_brand
    where
    		if(status != null)
    		status = #{status}
		and company_name like #{companyName}
		and brand_name like #{brandName}
</select>

*MyBatis对动态SQL有强大的支撑

*if:用于判断参数是否有值，使用test属性进行条件判断

*存在的问题：第一个条件不需要逻辑运算符

*解决方案：

1.使用恒等式让所有条件格式都一样

2.标签替换where关键字

  <!--
        动态条件查询
            *if:条件判断
                *test:逻辑表达式
            *问题：
                * 恒等式
    -->
    <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>
            
            
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        /*where 1=1*/
        <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>

3.单条件动态查询

*choose(when,otherwise):选择，类似于java中的switch语句

<select id="selectByConditionSingle" resultMap="brandResultMap">
    select *
    from tb_brand
    where
    <choose>	//类似于switch
    	<when test="status != null">	//类似于case
    		status=#{status}
		</when> 
    	<when test="companyName != null and companyName !=''">	
    		company_name like #{companyName}
		</when> 
    	<when test="brandName != null and brandName !=''">
    		company_name like #{companyName}
		</when>
        <otherwise>
            1=1
        </otherwise>
    </choose>
</select>
            
   <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="brandName != null and brandName != ''">
                    brand_name like #{brandName}
                </when>
                <otherwise>
                    1 = 1
                </otherwise>
            </choose>

    </select>