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