JavaWeb：MyBatis（6）查看详情、条件查询

查看详情

1．编写接口方法: Mapper接口

参数: id

结果:Brand

Brand selectById(int id);

 /*
   * 查看详情
   * */
   Brand selectById(int id);

2．编写SQL语句: SQL映射文件

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

 <!--
        参数占位符：
            1.#{}:会将其替换为？，为了防止sql注入
            2.${}：拼sql，会存在sql注入问题
            3.使用时机：
                参数传递的时候：#{}
                表名或者列名不固定的情况下：${}会存在sql注入问题
        参数类型：parameterType：可以省略
        特殊字符的处理：
            1.转义字符：
            2.CDATA区：

    -->
    <select id="selectById" parameterType="int" resultMap="brandResultMap">
        select * from tb_brand where id = #{id};
    </select>

3.执行方法，测试

@Test
    public void testSelectById()throws Exception{

        //接收id
        int id = 1;
        //1.加载mybatis的核心配置文件，获取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.执行方法
        Brand brand = brandMapper.selectById(id);
        System.out.println(brand);

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

条件查询

多条件的查询

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

参数:所有查询条件

结果:List<Brand>

/*
   * 条件查询
   * 参数接收：
   *     1.散装参数：有多个参数，需要使用@Param（“SQL参数占位符名称”）注解
   *     2.对象参数:对象的属性名称要和参数占位符名称一致
   *     3.map集合的参数
   */

   List<Brand> selectByCondition1(@Param("status") int status,@Param("companyName") String companyName,@Param("brandName") String brandName);
   List<Brand> selectByCondition2(Brand brand);
   List<Brand> selectByCondition3(Map map);

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

<!--
        条件查询
    -->
    <select id="selectByCondition1" resultMap="brandResultMap">
        select *
        from tb_brand
        where
        status = #{status} and company_name like #{companyName} and brand_name like #{brandName}
    </select>
    <select id="selectByCondition2" resultMap="brandResultMap">
        select *
        from tb_brand
        where
        status = #{status} and company_name like #{companyName} and brand_name like #{brandName}
    </select>
    <select id="selectByCondition3" resultMap="brandResultMap">
        select *
        from tb_brand
        where
        status = #{status} and company_name like #{companyName} and brand_name like #{brandName}
    </select>

3．执行方法，测试

@Test
    public void testSelectByCondition1()throws Exception{

        //接收id
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        //处理

        companyName = "%"+companyName+"%";
        brandName = "%"+brandName+"%";

        //1.加载mybatis的核心配置文件，获取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.selectByCondition1(status, companyName, brandName);
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void testSelectByCondition2()throws Exception{

        //接收id
        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.加载mybatis的核心配置文件，获取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.selectByCondition2(brand);
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }
    @Test
    public void testSelectByCondition3()throws Exception{

        //接收id
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        //处理

        companyName = "%"+companyName+"%";
        brandName = "%"+brandName+"%";

        //封装对象
        Map map = new HashMap();
        map.put("status" ,status);
        map.put("companyName" ,companyName);
        map.put("brandName" ,brandName);

        //1.加载mybatis的核心配置文件，获取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.selectByCondition3(map);
        System.out.println(brands);

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

注：若查询结果为空需要配置核心文件mybatis-config.xml文件中的

<property name="url" value="jdbc:mysql:///mybatis?characterEncoding=utf8&amp;useSSL=false"/>