</select>
<select id="selectStudentByName" resultType="StudentBean">
select * from student where name like '%' #{name} '%'
</select>
<select id="selectStuByMap1" resultType="StudentBean">
<!-- 查询的参数是Map #{map的key} 使用的数据是key对应的value-->
select * from student where name like '%' #{name} '%' and age>#{age}
</select>
<select id="selectStuByMap2" resultType="StudentBean">
<!-- 查询的参数是Map #{map的key} 使用的数据是key对应的value
map的key是student，value是student对象-->
select * from student where name like '%' #{name} '%' and age > #{student.age}
</select>
<select id="selectStuByParameters1" resultType="StudentBean">
select * from student where name like '%' #{0} '%' and age > #{1}
</select>
<select id="selectStuByParameters2" resultType="StudentBean">
select * from student where name like '%' #{0} '%' and age > #{1.age}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
动态参数
/**
* 动态参数
*/
//mybatis 动态参数类似域jstl《c:》
//if拼接 sql语句要跟上 where 1 =1
List<StudentBean> selectStudentByIf(StudentBean student);
<select id="selectStudentByIf" resultType="StudentBean">
select * from student where 1=1
<if test="name!=null and name!=''">
and name like '%' #{name} '%'
</if>
<if test="age>0">
and age > #{age}
</if>
</select>
//不生成 1= 1 提高效率 自动在sql语句拼接的时候加上where 关键字
List<StudentBean> selectStudentByWhere(StudentBean student);
<select id="selectStudentByWhere" resultType="StudentBean">
select * from student
<where>
<if test="name!=null and name!=''">
and name like '%' #{name} '%'
</if>
<if test="age>0">
and age > #{age}
</if>
</where>
</select>
//多选一
List<StudentBean> selectStudentByChoose(StudentBean student);
<select id="selectStudentByChoose" resultType="StudentBean">
select * from student
<choose>
<when test="name!=null and name!=''">
where name like '%' #{name} '%'
</when>
<when test="age>0">
where age > #{age}
</when>
<otherwise>
where 1 = 2
</otherwise>
</choose>
</select>
List<StudentBean> selectStudentByForeachArray(int[] ids);
<select id="selectStudentByForeachArray" resultType="StudentBean">
select * from student
<if test="array!=null and array.length>0">
where id in
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
</select>
List<StudentBean> selectStudentByForeachList(List<Integer> ids);
<select id="selectStudentByForeachList" resultType="StudentBean">
select * from student
<if test="list!=null and list.size>0">
where id in
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
</select>
List<StudentBean> selectStudentByForeachStudent(List<StudentBean> students);
<select id="selectStudentByForeachStudent" resultType="StudentBean">
select * from student
<if test="list!=null and list.size>0">
where id in
<foreach collection="list" item="student" open="(" close=")" separator=",">
#{student.id}
</foreach>
</if>
</select>
List<StudentBean> selectStudentBySqlFragement();
<select id="selectStudentBySqlFragement" resultType="StudentBean">
select <include refid="fragement" /> student
</select>
<sql id="fragement"> * from</sql>
//统计一张表的总数据条数 分页的总条数
int selectStudentCount();
<select id="selectStudentCount" resultType="int">
select count(*) from student
</select>
<!-- 动态参数 -->
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
---------------------