MyBatis增删改查之查询操作

1.查询所有

Mapper接口

public interface BrandMapper {
/**
* 查询所有
*/
List<Brand> selectAll();
    
}

SQL语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.BrandMapper">
<select id="selectAll" resultType="brand">
select *
from tb_brand;
</select>
</mapper>

测试

public class MyBrandTest {   
@Test
    public void testselectAll() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        //3.获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
 
        List<Brand> brands = brandMapper.selectAll();
 
        System.out.println(brands);
        sqlSession.close();
    }
}

 

2.查询详情

Mapper接口

  public interface BrandMapper {
      /*
    查看详情
     */
    Brand selectById( int id);
      
 }

SQL语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.BrandMapper">
    <select id="selectById" resultType="brand">
        select  * from tb_brand where id = #{id};
    </select>
</mapper>

测试

public class MyBrandTest {   
 
    @Test
    public void testselectById() throws IOException {
        int id=1;
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 
        //获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        //3.获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
 
        Brand brand = brandMapper.selectById(id);
 
        System.out.println(brand);
        sqlSession.close();
 
    }
}

3.多条件查询

Mapper接口

public interface BrandMapper {
    /*
    方法一:使用 @Param("参数名称") 标记每一个参数，在映射配置文件中就需要使用 #{参数名称} 进行占位
    */
    List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
 
    /*
    方法二:将多个参数封装成一个 实体对象 ，将该实体对象作为接口的方法参数。该方式要求在映射配置文件的SQL中使用 #{内容} 时，里面的内容必须和实体类属性名保持一致。
    */
    List<Brand> selectByConditionSingle(Brand brand);
    
    /*
    方法三:将多个参数封装到map集合中，将map集合作为接口的方法参数。该方式要求在映射配置文件的SQL中使用 #{内容}时，里面的内容必须和map集合中键的名称一致。
    */
    List<Brand> selectByCondition(Map map);
}

SQL语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.BrandMapper">
<select id="selectByCondition" resultType="brand">
    select *
    from tb_brand
    where status = #{status}
    and company_name like #{companyName}
    and brand_name like #{brandName}
</select>
</mapper>

 测试

public class MyBrandTest {   
 
    @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对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        //3.获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        //方法一:接口方法参数使用 @Param 方式调用的方法
        //List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
        //方法二:接口方法参数是 实体类对象 方式调用的方法
        /* 封装对象
           Brand brand = new Brand();
           brand.setStatus(status);
           brand.setCompanyName(setCompanyName);
           brand.setBrandName(setBrandName);
         */
        //List<Brand> brands = brandMapper.selectByCondition(brand);
        //方式三 ：接口方法参数是 map集合对象方式调用的方法
        Map map = new HashMap();
        map.put("status" , status);
        map.put("companyName", companyName);
        map.put("brandName" , brandName);
        List<Brand> brands = brandMapper.selectByCondition(map);
        
        System.out.println(brands);
        //5.释放资源
        sqlSession.close();
    }
}

4.动态SQL

多条件查询 SQL语句

<select id="selectByCondition" resultType="brand">
        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>

单条件查询 

Mapper接口 

public interface BrandMapper {
    /**
    * 单条件动态查询
    * @param brand
    * @return
    */
    List<Brand> selectByConditionSingle(Brand brand);
}

SQL语句

<select id="selectByConditionSingle" resultType="brand">
        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 !=''">
                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对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4. 执行方法
        List<Brand> brands = brandMapper.selectByConditionSingle(brand);
        System.out.println(brands);
        //5. 释放资源
        sqlSession.close();
    }
}