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>