有时候并不想应用所有的条件,只是想从多个选项中选择一个,而使用if标签时,只要test中的表达式为true,就会执行if标签中的条件。
为此,MyBatis提供了choose标签。
if标签中的条件是与(and)的关系,而choose标签中的条件是或(or)的关系。choose标签是按顺序判断其内部when标签中的test条件是否成立,如果有一个成立,则choose结束。当choose中所有when的条件都不成立时,则执行otherwise中的SQL语句。
与Java的switch语句相比,choose相当于switch,when相当于case,otherwise则相当于default。下面通过一个案例来进行演示。
(1)查询条件:
当studen_id有值时,使用studen_id进行查询;
当studen_id没有值时,使用name进行查询;
没有studen_id和name则返回空。
项目情况截图
数据库,建表
create table z_student(
id int not null auto_increment primary key,
name varchar(20) not null,
phone varchar(20) null,
email varchar(50) null,
sex tinyint(4) null comment '0女,1男',
locked tinyint null comment '状态,0正常,1锁定',
gmt_created datetime default CURRENT_TIMESTAMP comment '存入库的时间',
gmt_modified datetime default CURRENT_TIMESTAMP comment '修改时间',
deletes int(11) null
) comment '学生表'
insert into z_student values(null,'tom','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom2','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom3','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom4','130','xx@xx.com',1,0,null,null,0);
Student.java
package com.shrimpking.code04;
import java.util.Date;
/**
* @author user1
*/
public class Student
{
private int id;
private String name;
private String phone;
private String email;
private int sex;
private int locked;
private Date gmtCreated;
private Date gmtModified;
private int deletes;
public Student()
{
}
public Student(int id, String name, String phone, String email, int sex, int locked, Date gmtCreated, Date gmtModified, int deletes)
{
this.id = id;
this.name = name;
this.phone = phone;
this.email = email;
this.sex = sex;
this.locked = locked;
this.gmtCreated = gmtCreated;
this.gmtModified = gmtModified;
this.deletes = deletes;
}
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public String getPhone()
{
return phone;
}
public void setPhone(String phone)
{
this.phone = phone;
}
public String getEmail()
{
return email;
}
public void setEmail(String email)
{
this.email = email;
}
public int getSex()
{
return sex;
}
public void setSex(int sex)
{
this.sex = sex;
}
public int getLocked()
{
return locked;
}
public void setLocked(int locked)
{
this.locked = locked;
}
public Date getGmtCreated()
{
return gmtCreated;
}
public void setGmtCreated(Date gmtCreated)
{
this.gmtCreated = gmtCreated;
}
public Date getGmtModified()
{
return gmtModified;
}
public void setGmtModified(Date gmtModified)
{
this.gmtModified = gmtModified;
}
public int getDeletes()
{
return deletes;
}
public void setDeletes(int deletes)
{
this.deletes = deletes;
}
@Override
public String toString()
{
return "Student{" + "id=" + id + ", name='" + name + '\'' + ", phone='" + phone + '\'' + ", email='" + email + '\'' + ", sex=" + sex + ", locked=" + locked + ", gmtCreated=" + gmtCreated + ", gmtModified=" + gmtModified + ", deletes=" + deletes + '}';
}
}
StudentMapper.java
package com.shrimpking.code04;
public interface StudentMapper
{
public Student selectByIdOrName(Student student);
}
StudentMapper.xml
<?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.shrimpking.code04.StudentMapper">
<sql id="Base_sql">
id,name,phone,email,sex,locked,gmt_created,gmt_modified
</sql>
<select id="selectByIdOrName" resultType="Student" parameterType="Student">
select
<include refid="Base_sql"/>
from z_student
where 1=1
<choose>
<when test="id > 0">
and id = #{id}
</when>
<when test="name != null">
and name = #{name}
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
</select>
</mapper>
mybatis.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 起别名 -->
<typeAliases>
<package name="com.shrimpking.code04"/>
</typeAliases>
<!-- 环境 -->
<environments default="development">
<environment id="development">
<!-- 默认事务管理 -->
<transactionManager type="JDBC"/>
<!-- 默认的数据库连接 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeZone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="mysql123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/shrimpking/code04/StudentMapper.xml"/>
</mappers>
</configuration>
StudentTest.java
package com.shrimpking.code04;
import com.shrimpking.utils.DaoUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
public class StudentTest
{
@Test
public void test() throws IOException
{
SqlSession sqlSession = DaoUtils.getSqlSession("code04/mybatis.xml");
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student stu = new Student();
//
stu.setId(1);
System.out.println("只有id进行查询");
Student student = mapper.selectByIdOrName(stu);
System.out.println(student);
//
stu.setId(0);
stu.setName("虾米");
System.out.println("只有用户名查询");
Student student2 = mapper.selectByIdOrName(stu);
System.out.println(student2);
//
stu.setId(0);
stu.setName(null);
System.out.println("没有id和用户名查询");
Student student3 = mapper.selectByIdOrName(stu);
System.out.println(student3);
sqlSession.close();
}
}
DaoUtils.java
package com.shrimpking.utils;
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 java.io.IOException;
import java.io.Reader;
/**
* @author user1
*/
public class DaoUtils
{
private static Reader reader;
private static SqlSessionFactory sqlSessionFactory;
public static SqlSession getSqlSession(String config) throws IOException
{
try
{
reader = Resources.getResourceAsReader(config);
//
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}
catch (IOException e)
{
e.printStackTrace();
}
finally
{
reader.close();
}
return sqlSessionFactory.openSession();
}
}
运行截图