MyBatis的增删改查操作


</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

---------------------

posted @ 2019-06-19 19:33  水至清明  阅读(161)  评论(0编辑  收藏  举报