MyBatis——数据库多表映射关联查询

MyBatis——数据库多表映射
参考：https://www.cnblogs.com/it-mh/articles/10668923.html

1、实体

(1)User实体
package com.model;
/**
 * @ClassName User
 * @Description 
 * @Author
 * @Date 2019年5月14日下午7:54:58
 */
public class User {
	 private int  id;
	 private String user_name;
	 private String user_pwd;
	 private String create_date;
	 private String user_level;
	 private String user_phone;
	public User() {
		super();
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUser_name() {
		return user_name;
	}
	public void setUser_name(String user_name) {
		this.user_name = user_name;
	}
	public String getUser_pwd() {
		return user_pwd;
	}
	public void setUser_pwd(String user_pwd) {
		this.user_pwd = user_pwd;
	}
	public String getCreate_date() {
		return create_date;
	}
	public void setCreate_date(String create_date) {
		this.create_date = create_date;
	}
	public String getUser_level() {
		return user_level;
	}
	public void setUser_level(String user_level) {
		this.user_level = user_level;
	}
	public String getUser_phone() {
		return user_phone;
	}
	public void setUser_phone(String user_phone) {
		this.user_phone = user_phone;
	}	  
}
(2)Personel实体
package com.model;
public class Personel {
    private Integer id;
    private Integer userId;
    private String picture;
    private String perDes;
    private User user;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getPicture() {
        return picture;
    }

    public void setPicture(String picture) {
        this.picture = picture == null ? null : picture.trim();
    }

    public String getPerDes() {
        return perDes;
    }

    public void setPerDes(String perDes) {
        this.perDes = perDes == null ? null : perDes.trim();
    }

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}
    
}

2、mybaties

（1）User
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dao.UserDao" >
    <resultMap id="BaseResultMap" type="com.model.User" >
	    <id column="id" property="id" jdbcType="INTEGER" />
	    <result column="user_name" property="user_name" jdbcType="VARCHAR" />
	    <result column="user_pwd" property="user_pwd" jdbcType="VARCHAR" />
	    <result column="create_date" property="create_date" jdbcType="VARCHAR" />
	    <result column="user_level" property="user_level" jdbcType="VARCHAR" />
	    <result column="user_phone" property="user_phone" jdbcType="VARCHAR" />
    </resultMap>
  <sql id="Base_Column_List" >
     id,user_name,user_pwd,create_date,user_level,user_phone
  </sql>
  
   <!--添加用户信息 -->
    <insert id="insert" parameterType="com.model.User">
       <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            select LAST_INSERT_ID()
       </selectKey>
	 insert into tbl_user(user_name,user_pwd,user_level,user_phone)
	  values(#{user_name},#{user_pwd},#{user_level},#{user_phone})
    </insert>
    
    <!--  根据id删除用户信息 -->
    <delete id="delete" parameterType="java.lang.Integer">
      <if test="id>0">
         delete   from   tbl_user   where id=#{id}
      </if>
    </delete>
     <!--根据id找到用户信息 -->
    <select id="get" parameterType="com.model.User"  resultType="com.model.User">
	 select <include refid="Base_Column_List" />
	 from tbl_user where 1=1
	   <if test="id>0">
           and id=#{id}
       </if>
       <if test="user_name!=null and user_name!='' ">
           and user_name=#{user_name}
       </if>
      
    </select>
     <!--根据id修改用户信息 -->
    <update id="update" parameterType="com.model.User">
	update tbl_user set user_name=#{user_name},user_pwd=#{user_pwd},user_phone=#{user_phone}
	   <if test="id>0">
         where id=#{id}
       </if>
    </update>
   <!-- 用户登录 -->
  <select id="loginUserInfo" parameterType="com.model.User" resultType="com.model.User">
     select 
     <include refid="Base_Column_List" />
     from  tbl_user
     <if test="user_name!=null and  user_pwd!=null and user_level!=null">
           where user_phone=#{user_name} and user_pwd=#{user_pwd} and user_level=#{user_level}
     </if>
  </select>
   <!-- 根据姓名查询信息 -->
  <select id="queryUserInfoByName"  resultMap="BaseResultMap" resultType="com.model.User">
     select 
     <include refid="Base_Column_List" />
     from  tbl_user where 1=1
     <if test="user_name!='' and user_name!=null ">
          and  user_name  like '%${user_name}%'
     </if>
     <if test="userId!='' and userId>0 ">
          and  id  =#{userId}
     </if>
       limit #{currentPage},#{pageSize}  
  </select>
   <!-- 根据姓名统计用户信息 =》分页-->
   <select id="countUserInfoByName" resultType="int"  >
       select count(*) from  tbl_user   where 1=1
         <if test="user_name!='' and user_name!=null ">
          and  user_name  like '%${user_name}%'
        </if>
        <if test="userId!='' and userId>0 ">
          and  id  =#{userId}
         </if>
   </select>
</mapper>
 （2）(Personel) 关联用户
 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dao.PersonelMapper" >
  <resultMap id="BaseResultMap" type="com.model.Personel" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="user_id" property="userId" jdbcType="INTEGER" />
    <result column="picture" property="picture" jdbcType="VARCHAR" />
    <result column="per_des" property="perDes" jdbcType="VARCHAR" />
    <association property="user" javaType="com.model.User">
        <id column="id" property="id" jdbcType="INTEGER" />
	    <result column="user_name" property="user_name" jdbcType="VARCHAR" />
	    <result column="user_pwd" property="user_pwd" jdbcType="VARCHAR" />
	    <result column="create_date" property="create_date" jdbcType="VARCHAR" />
	    <result column="user_level" property="user_level" jdbcType="VARCHAR" />
	    <result column="user_phone" property="user_phone" jdbcType="VARCHAR" />
    </association>
  </resultMap>
  <!-- 如果数据库的字段名称和实体的属性不一致需要用别名 别名为实体的名称不然映射不到切记 -->
  <sql id="Base_Column_List" >
    t1.id, t1.user_id, t1.picture, t1.per_des,
    t2.id,t2.user_name,t2.user_pwd,t2.user_level,t2.user_phone,t2.create_date
  </sql>
    <!--个人信息查询 关联查询 -->
  <select id="queryPersonel" resultMap="BaseResultMap" parameterType="com.model.Personel" >
    select 
    <include refid="Base_Column_List" />
    from tbl_personel t1,tbl_user t2
    where t1.user_id=t2.id 
    <if test="userName!=null and ''!=userName">
       and   t2.user_name  like '%$userName%'
    </if>
    <if test="pserId!=null">
       and   t2.id =#{pserId}
    </if>
     limit #{currentPage},#{pageSize}  
  </select>
  <!-- 根据姓名统计信息 =》分页-->
  <select id="countQueryPersonel" resultType="int"  >
       select count(*)
    from tbl_personel t1,tbl_user t2
    where t1.user_id=t2.id 
    <if test="userName!=null and ''!=userName">
       and   t2.user_name  like '%$userName%'
    </if>
    <if test="pserId!=null">
       and   t1.id =#{pserId}
    </if>
   </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from tbl_personel
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.model.Personel" >
    insert into tbl_personel (id, user_id, picture, 
      per_des)
    values (#{id,jdbcType=INTEGER}, #{userId,jdbcType=INTEGER}, #{picture,jdbcType=VARCHAR}, 
      #{perDes,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.model.Personel" >
    insert into tbl_personel
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="userId != null" >
        user_id,
      </if>
      <if test="picture != null" >
        picture,
      </if>
      <if test="perDes != null" >
        per_des,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="userId != null" >
        #{userId,jdbcType=INTEGER},
      </if>
      <if test="picture != null" >
        #{picture,jdbcType=VARCHAR},
      </if>
      <if test="perDes != null" >
        #{perDes,jdbcType=LONGVARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.model.Personel" >
    update tbl_personel
    <set >
      <if test="userId != null" >
        user_id = #{userId,jdbcType=INTEGER},
      </if>
      <if test="picture != null" >
        picture = #{picture,jdbcType=VARCHAR},
      </if>
      <if test="perDes != null" >
        per_des = #{perDes,jdbcType=LONGVARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.model.Personel" >
    update tbl_personel
    set user_id = #{userId,jdbcType=INTEGER},
      picture = #{picture,jdbcType=VARCHAR},
      per_des = #{perDes,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.model.Personel" >
    update tbl_personel
    set user_id = #{userId,jdbcType=INTEGER},
      picture = #{picture,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>