MyBatis一对一和一对多案例

---

前言

一、MyBatis一对一关系映射

1.首先创建对象之前的映射关系创建UserBean对象里面存放Role对象

package com.hh.userservice.pojo;

import com.hh.userservice.handle.ExampleTypeHandle;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Component;
import tk.mybatis.mapper.annotation.ColumnType;

import java.io.Serializable;
import java.util.Set;

@Data
@Accessors(chain = true)
@Component
@NoArgsConstructor
@AllArgsConstructor
public class UserBean implements Serializable {
    public static final long serialVersionUID = 1L;
    private String id;
    @ColumnType(jdbcType = JdbcType.VARCHAR, typeHandler = ExampleTypeHandle.class)
    private String username;
    private String password;
    private String email;
    private String telephone;
    private Role role;
}

2.创建Role对象

package com.hh.userservice.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Set;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role implements Serializable {
    public static final Long serialVersionUID=2L;
    private String roleId;
    private String name;
}

3.接下来是xml文件配置UserBean和Role之前的关系

<resultMap id="mapper" type="com.hh.userservice.pojo.UserBean">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="email" property="email"></result>
        <result column="telephone" property="telephone"></result>
        <!-- 切记property里的值是UserBean字段名称-->
        <association property="role" javaType="com.hh.userservice.pojo.Role" autoMapping="true">
            <result column="id" property="roleId"></result>
            <result column="role_name" property="name"></result>
        </association>
    </resultMap>

4.将UserBean和Role的数据同时查出来。

<select id="findUserBeanByUserName" resultMap="mapper">
        select u.*,r.* from u_user u left join role r on u.id = r.id where username = #{username}
</select>

二、MyBatis一对多关系映射

1.首先创建对象之前的映射关系创建UserBean对象里面存放Role对象

package com.hh.userservice.pojo;

import com.hh.userservice.handle.ExampleTypeHandle;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Component;
import tk.mybatis.mapper.annotation.ColumnType;

import java.io.Serializable;
import java.util.Set;

@Data
@Accessors(chain = true)
@Component
@NoArgsConstructor
@AllArgsConstructor
public class UserBean implements Serializable {
    public static final long serialVersionUID = 1L;
    private String id;
    @ColumnType(jdbcType = JdbcType.VARCHAR, typeHandler = ExampleTypeHandle.class)
    private String username;
    private String password;
    private String email;
    private String telephone;
    private List<Role> roles;
}

2.创建Role对象

package com.hh.userservice.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Set;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role implements Serializable {
    public static final Long serialVersionUID=2L;
    private String roleId;
    private String name;
}

3.接下来是xml文件配置UserBean和Role之前的关系

<resultMap id="mapper" type="com.hh.userservice.pojo.UserBean">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="email" property="email"></result>
        <result column="telephone" property="telephone"></result>
        <!-- 切记property里的值是UserBean字段名称-->
         <collection property="roles" ofType="com.hh.userservice.pojo.Role" autoMapping="true">
            <result column="id" property="roleId"></result>
            <result column="role_name" property="name"></result>
        </collection>
</resultMap>

4.将UserBean和Role的数据同时查出来。

<select id="findUserBeanByUserName" resultMap="mapper">
        select u.*,r.* from u_user u left join role r on u.id = r.id where username = #{username}
</select>

三、MyBatis树结构查询同一个对象，一对多关系映射

1.创建UserBean对象，并且映射本身。

package com.hh.userservice.pojo;

import com.hh.userservice.handle.ExampleTypeHandle;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Component;
import tk.mybatis.mapper.annotation.ColumnType;

import java.io.Serializable;
import java.util.Set;

@Data
@Accessors(chain = true)
@Component
@NoArgsConstructor
@AllArgsConstructor
public class UserBean implements Serializable {
    public static final long serialVersionUID = 1L;
    private String id;
    @ColumnType(jdbcType = JdbcType.VARCHAR, typeHandler = ExampleTypeHandle.class)
    private String username;
    private String password;
    private String email;
    private String telephone;
    private List<UserBean> childrenUserBeanList;
}

2.Mapper.xml文件

/***
    * 功能描述:<br>
    * @参数 parentId
    * @描述: 通过参数查询父节点信息
    * @时间:
    * @return
    * @throws
    */
    List<UserBean> findUserBeanByparentId(@Param("parentId") Integer parentId);

    /***
     * 功能描述:<br>
     * @参数 id
     * @描述: 通过参数查询子节点信息
     * @时间:
     * @return
     * @throws
     */
    List<UserBean> findSubUserBeanByparentId(Integer id);

3.接下来是xml文件配置UserBean和UserBean之前的关系

<resultMap id="mapper" type="com.hh.userservice.pojo.UserBean">
        <id column="id" property="id"></id>
        <result column="parent_id" property="parentId"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="email" property="email"></result>
        <result column="telephone" property="telephone"></result>
        <!-- 切记property里的值是UserBean字段名称,将父节点的Id传入到集合中，如果需要传递多个参数的话，需要如下配置。切记：parentId在前，parent_id在后，否则会提示无法找到parentId字段数据等错误信息。-->
         <collection column="{id=id,parentId=parent_id}" property="childrenUserBeanList" ofType="com.hh.userservice.pojo.UserBean" select="findSubUserBeanByParentId">
        </collection>
</resultMap>

<select id="findUserBeanByparentId" resultMap="mapper">
        select u.* from u_user where parent_id = #{parentId}
</select>
<select id="findSubUserBeanByParentId" resultMap="mapper">
        select u.* from u_user where parent_id = #{parentId}
</select>

或者

<resultMap id="mapper" type="com.hh.userservice.pojo.UserBean">
        <id column="id" property="id"></id>
        <result column="parent_id" property="parentId"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="email" property="email"></result>
        <result column="telephone" property="telephone"></result>
        <!-- 切记property里的值是UserBean字段名称,将父节点的Id传入到集合中-->
         <collection column="id" property="childrenUserBeanList" ofType="com.hh.userservice.pojo.UserBean" select="findUserBeanByparentId">
        </collection>
</resultMap>

<select id="findUserBeanByparentId" resultMap="mapper">
        select u.* from u_user where parent_id = #{parentId}
</select>

四、MyBatis避坑，if判断内容格式。

把<if test="takeWay == '1' and workday != null ">
改为<if test='takeWay == "1" and workday != null '>

---

总结

人生物语：很多时候，努力并不是为了得到奖赏，而是为了被认同。当有人说你变了的时候，不过是因为你不用再按他们的方式生活罢了。很多时候，我们视为刻骨铭心的记忆，而别人却早已忘记，与其纠结于心，不如看淡，看轻。