动态SQL ,即通过Mybatis提供的各种标签对条件做出判断以实现动态拼接SQL语句。这里的条件判断使用的是OGNL表达式。 常用的动态SQL标签有<if><where> <choose/> <foreach>
等。
1.Dao层实现
public interface IStudentDao {
/**
* 通过学生id查找相应学生信息
* @param id 学生id
* @return 学习实体
*/
Student selectStudentById(int id);
/***
* 插入学生数据
* @param student 学生实体
*/
void insertStudent(Student student);
/***
* 通过map 封装多个查询条件
* @param map map
* @return 实体数组.....
*/
List<Student> selectStudentsByMap(Map<String, Object> map);
/****
* 动态查询SQL If 形式
* @param student 学生实体
* @return 实体数组
*/
List<Student> selectStudentIf(Student student);
/***
* 动态查询SQL Where 形式
* @param student 学生实体
* @return 实体数组
*/
List<Student> selectStudentWhere(Student student);
/***
* 动态查询 SQL choose 类似于 switch case 若有第一个条件
* 则添加1 无则添加2
* @param student
* @return
*/
List<Student> selectStudentChoose(Student student);
/***
* 动态查询SQL foreach 查询数组中符合条件的.....
* @param studentIds 学生id数组
* @return 学生实体数组....
*/
List<Student> selectStudentForeachArray(Object[]studentIds);
/***
* 动态SQL
* @param list 和上一样....
* @return
*/
List<Student> selectStudentForeachList(List<Student> list);
}
2.配置文件 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 -->
<mapper namespace="com.evecom.common.IStudentDao">
<select id="selectStudentById" parameterType="int" resultType="Student">
select id,name,score,hobby from student where id = #{id}
</select>
<insert id="insertStudent">
insert into student(id,name,score,hobby)
values(#{id},#{name},#{score},#{hobby})
</insert>
<select id="selectStudentsByMap" resultType="Student">
select id,name,score,hobby from student where name like '%' #{name} '%' and
score > #{score}
</select>
<select id="selectStudentIf" resultType="Student">
select * from student where 1=1
<if test="name != null and name != ''">
and name like '%' #{name} '%'
</if>
<if test="score > 0 ">
and score > #{score}
</if>
</select>
<select id="selectStudentWhere" resultType="Student">
select * from student
<where>
<if test="name != null and name != ''">
name like '%' #{name} '%'
</if>
<if test="score > 0">
and score > #{score}
</if>
</where>
</select>
<select id="selectStudentChoose" resultType="Student">
select * from student
<where>
<choose>
<when test="name != null and name != ''">
name like '%' #{name} '%'
</when>
<when test="hobby != null and hobby != ''">
and hobby like '%' #{hobby} '%'
</when>
<otherwise>
and 1 != 1
</otherwise>
</choose>
</where>
</select>
<select id="selectStudentForeachArray" resultType="Student">
select * from student
<if test="array != null and array.length > 0">
where id in
<foreach collection="array" open="(" close=")" item="myid"
separator=",">
#{myid}
</foreach>
</if>
</select>
<select id="selectStudentForeachList" resultType="Student">
select * from student
<if test="list != null and list.size > 0">
where id in
<foreach collection="list" open="(" close=")" item="stu"
separator=",">
#{stu.id}
</foreach>
</if>
</select>
</mapper>
3.写测试用例。
public class MybatisTest {
private IStudentDao dao;
private SqlSession session;
Logger log = Logger.getLogger(log4jDemo.class);
@Before
public void setup() {
session = MyBatisUtil.getSqlSession();
dao = session.getMapper(IStudentDao.class);
}
@After
public void tearDown() {
if (session != null) {
session.close();
}
}
/*
@Test
public void test02() {
Map<String, Object> map = new HashMap<String, Object>();
map.put("name", "三");
map.put("score", 88.0);
map.put("hobby", "球");
List<Student> students = dao.selectStudentsByMap(map);
for (Student student : students) {
System.out.println(student);
}
}
*/
/*
@Test
public void test03() {
Student student = new Student();
student.setName("三");
student.setScore(89.0);
List<Student> list = dao.selectStudentIf(student);
for (Student student2 : list) {
System.out.println(student2);
}
}
*/
/*
@Test
public void test04() {
Student student = new Student();
student.setName("三");
student.setScore(89.0);
List<Student>list = dao.selectStudentWhere(student);
for (Student student2 : list) {
System.out.println(student2);
}
}
*/
/*
@Test
public void test05() {
Student student = new Student();
// student.setName("三");
student.setHobby("球");
List<Student> students = dao.selectStudentChoose(student);
for (Student student2 : students) {
System.out.println(student2);
}
}
*/
/*
@Test
public void test06() {
Object[] studentIds = new Object[] {3,11};
List<Student> students = dao.selectStudentForeachArray(studentIds);
for (Student student : students) {
System.out.println(student);
}
}
*/
@Test
public void test07() {
List<Student> list = new ArrayList<Student>();
Student student = new Student();
Student student2 = new Student();
// list.add(3);
// list.add(11);
student.setId(3);
student2.setId(11);
list.add(student);
list.add(student2);
List<Student> students = dao.selectStudentForeachList(list);
for (Student student3 : students) {
System.out.println(student3);
}
}
}