Mybatis知识点

下面的例子以下面两张表为例:

tb_company
company_id company_name
1 华为
2 小米
3 oppo
tb_user
id username password age c_id
1 张三 123 22 1
2 李四 456 44 3
3 王五 789 33 2
4 赵六 012 21 1
5 陈七 345 33 1

实体类的属性为:

1
2
3
4
5
6
7
8
9
10
11
//User(tb_user)
private Integer id;
private String username;
private String password;
private Integer age;
private Integer cId;
private Company company;
//Company(tb_company)
private String companyId;
private String companyName;
private List<User> UserList;

多对一映射处理

1 级联方式

1
2
3
4
5
6
7
8
9
<resultMap id="UserAndCompanyMethod1" type="Pojo.User">
<result property="cId" column="c_id"></result>
<result property="company.companyId" column="company_id"></result>
<result property="company.companyName" column="company_name"></result>
</resultMap>

<select id="selectUserAndCompanyMethod1" resultMap="UserAndCompanyMethod1">
select * from tb_user left outer join tb_company on company_id = c_id where id = #{id}
</select>

2 association处理映射关系

  • association:处理多对一的映射关系
  • property:需要处理多对的映射关系的属性名
  • javaType:该属性的类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<resultMap id="UserAndCompanyMethod2" type="Pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="age" column="age"></result>
<result property="cId" column="c_id"></result>
<association property="company" javaType="Pojo.Company">
<id property="companyId" column="company_id"></id>
<result property="companyName" column="company_name"></result>
</association>
</resultMap>

<select id="selectUserAndCompanyMethod2" resultMap="UserAndCompanyMethod2">
select * from tb_user left outer join tb_company on tb_company.company_id = tb_user.c_id where tb_user.id = #{id}
</select>

3 分步查询

3.1 查询用户信息

1
2
3
4
5
6
7
8
9
10
11
<resultMap id="UserAndCompanyMethod3" type="Pojo.User">
<result property="cId" column="c_id"></result>
<association property="company"
select="Mapper.CompanyMapper.selectByCompanyId"
column="c_id">
</association>
</resultMap>

<select id="selectUserAndCompanyMethod3" resultMap="UserAndCompanyMethod3">
select * from tb_user where id = #{id}
</select>

3.2 查询公司信息

1
2
3
4
5
6
7
8
<resultMap id="companyMap" type="Pojo.Company">
<id property="companyId" column="company_id"></id>
<result property="companyName" column="company_id"></result>
</resultMap>

<select id="selectByCompanyId" resultMap="companyMap">
select * from tb_company where company_id = #{compamy_id}
</select>

一对多映射处理

1 collection处理映射关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<resultMap id="CompanyAndUserMethod1" type="Pojo.Company">
<id property="companyId" column="company_id"></id>
<result property="companyName" column="company_name"></result>
<collection property="userList" ofType="Pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="age" column="age"></result>
<result property="cId" column="c_id"></result>
</collection>
</resultMap>

<select id="selectCompanyAndUserMethod1" resultMap="CompanyAndUserMethod1">
select * from tb_company left outer join tb_user on company_id = c_id where company_id = # {companyId}
</select>

2 分步查询

2.1 查询公司

1
2
3
4
5
6
7
8
9
10
11
12
<resultMap id="CompanyAndUserMethod2" type="Pojo.Company">
<id property="companyId" column="company_id"></id>
<result property="companyName" column="company_name"></result>
<collection property="userList"
select="Mapper.UserMapper.selectByCId"
column="company_id">
</collection>
</resultMap>

<select id="selectCompanyAndUserMethod2" resultMap="CompanyAndUserMethod2">
select * from tb_company where company_id = #{companyId}
</select>

2.2 查询用户

1
2
3
<select id="selectByCId" resultType="Pojo.User">
select * from tb_user where c_id = #{Cid}
</select>

动态sql

if

  • if标签中通过test属性对传来的数据表达式进行判断,如果结果为ture,则标签中的sql语句就会执行;反之便不会执行。
  • 下面就是一个if标签的简单应用。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="selectByUser" resultType="Pojo.user">
select * from tb_user where 1=1
<if test="id!=null ">
id = #{id}
</if>
<if test="username!=null and username!='' ">
and username = #{username}
</if>
<if test="password!=null and password!='' ">
and password = #{password}
</if>
<if test="cId!=null">
and c_id = #{cId}
</if>
<if test="age!=null">
and age = #{age}
</if>
</select>
  • 在where后面加上1=1恒等式。是为了避免当 if test=”id!=null”不成立时,后面执行sql语句时就会出出现sql语法错误。
1
select * from tb_user where and c_id = ? and age = ?	<!--where多了一个and语法错误-->
1
select * from tb_user where 1=1 and c_id = ? and age = ?	<!--加上后,这样就不会报错-->

where

  • where和if一般结合使用:
  • 若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
  • 若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的and/or去掉
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<select id="selectByUser" resultType="Pojo.user">
select * from tb_user
<where>
<if test="id!=null ">
and id = #{id}
</if>
<if test="username!=null and username!='' ">
and username = #{username}
</if>
<if test="password!=null and password!='' ">
and password = #{password}
</if>
<if test="c_id!=null">
and c_id = #{cId}
</if>
<if test="age!=null ">
and age = #{age}
</if>
</where>
</select>

where标签不能去掉条件后多余的and/or

trim

  • trim用于去掉或添加标签中的内容

  • 常用属性

    • prefix:在trim标签中的内容的前面添加某些内容
    • suffix:在trim标签中的内容的后面添加某些内容
    • prefixOverrides:在trim标签中的内容的前面去掉某些内容
    • suffixOverrides:在trim标签中的内容的后面去掉某些内容
  • 若trim中的标签都不满足条件,则trim标签没有任何效果,也就是只剩下select * from t_emp

choose

  • choose相当于switch
  • when和otherwise相当于case和default
  • when至少要有一个,otherwise至多只有一个
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
<select id="selectByUser" resultType="Pojo.user">
select * from tb_user
<where>
<choose>
<when test="id!=null">
id = #{id}
</when>
<when test="username!=null and username!=''">
username = #{username}
</when>
<when test="password!=null and password !='' ">
password = #{password}
</when>
<when test="cId!=null">
c_id=#{cId}
</when>
<when test="age!=null">
age = #{age}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>

foreach

  • 属性:
    • collection:设置要循环的数组或集合
    • item:表示集合或数组中的每一个数据
    • separator:设置循环体之间的分隔符,分隔符前后默认有一个空格,如,
    • open:设置foreach标签中的内容的开始符
    • close:设置foreach标签中的内容的结束符
1
2
3
4
5
6
<delete id="deleteMoreById">
delete from tb_user where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
1
2
3
4
5
6
<insert id="insertMoreByList">
insert into tb_user(id, username, password, age, c_id) value
<foreach collection="userList" item="user" separator="," >
(#{user.id},#{user.username},#{user.password},#{user.age},#{user.cId})
</foreach>
</insert>

如果有什么,可以通过下面的邮箱和我联系!!!

img