学习mybatis查看详情、动态多条件/单条件查询
点击查看代码
<?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">
<!--
数据库表的字段名称 和 实体类的属性名称 不一样，不能自动封装数据
*起别名
*resultMap
-->
<!--
id:唯一标识
type:映射类型，支持别名
-->
<resultMap id="brandResultMap" type="brand">
<!--
id:主键映射
result:一般字段
column:表列名
property:实体类属性名
-->
<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="selectAll" resultType="brand">
select id,brand_name as brandName,company_name as companyName,ordered,description,status
from tb_brand;
</select>-->
<!--<select id="selectAll" resultType="brand">
select *
from tb_brand;
</select>-->
<!--
*参数占位符：
1.#{}：替换为？；
传递参数;
2.${}：拼字符串，存在sql注入；
表名、列名不固定;
*特殊字符处理：
1.转义字符;
2.CDATA区:
<![CDATA[
<
]]>
-->
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand where id = #{id};
</select>
<!-- <select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName};
</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>
<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>
</where>
</select>
</mapper>
点击查看代码
package com.itheima.test;
import com.itheima.mapper.BrandMapper;
import com.itheima.pojo.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.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {
@Test
public void testSelectAll() throws IOException {
//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.selectAll();
System.out.println(brands);
//5.释放
sqlSession.close();
}
@Test
public void testSelectById() throws IOException {
int id = 1;
//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.执行方法
Brand brand = brandMapper.selectById(id);
System.out.println(brand);
//5.释放
sqlSession.close();
}
@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();
}
@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.selectByCondition(status, companyName, brandName);
//List<Brand> brands = brandMapper.selectByCondition(brand);
List<Brand> brands = brandMapper.selectByConditionSingle(brand);
System.out.println(brands);
//5.释放
sqlSession.close();
}
}