用户输入条件时,是否所有条件都会填写?
查询-多条件-动态条件查询
SQL语句会随着用户的输入或外部条件的变化而变化,我们称为动态SQL
<select id="selectByCondition" resultMap=""brandResultMap">
select *
from tb_brandwhere
status = #{status}
and company_name like #{companyName)
and brand_name like #{brandName}
</select>
MyBatis对动态SQL有很强大的支撑:
if
choose (when,otherwise)
trim (where,set)
foreach
在BrandMapper.java的接口文件中添加
List<Brand> selectByChangedCondition1(Map map);
在BrandMapper.xml文件中添加
<select id="selectByChangedCondition1" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status != null">
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>
在MyBatisTest.java文件中添加
@Test
public void testselectByChangedCondition1()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.selectByChangedCondition1(map);
System.out.println(brands);
//5.释放资源
sqlSession.close();
}
小结
if:用于判断参数是否有值,使用test属性进行条件判断
存在的问题:第一个条件不需要逻辑运算符
解决方案:
1)使用恒等式让所有条件格式都一样
2) <where>标签替换where关键字
查询-单条件-动态条件查询
从多个条件中选择一个
choose (when, otherwise):选拯,类似于Java中的 switch 语句
在BrandMapper.java的接口文件中添加
/*
*单条件动态查询
* */
List<Brand> selectByConditionSingle(Brand brand);
在BrandMapper.xml文件中添加
<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 !='' "><!--相当于case-->
company_name like #{companyName}
</when>
<when test="brandName != null and brandName !='' "><!--相当于case-->
brand_name like #{brandName}
</when>
</choose>
</where>
</select>
在MyBatisTest.java文件中添加
@Test
public void testselectByConditionSingle()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.selectByConditionSingle(brand);
System.out.println(brands);
//5.释放资源
sqlSession.close();
}