Mybatis多对一映射查询

最新推荐文章于 2022-11-03 16:10:48 发布

robin912

最新推荐文章于 2022-11-03 16:10:48 发布

阅读量184

点赞数

分类专栏： java 文章标签： mybatis java spring

本文链接：https://blog.csdn.net/robin912/article/details/80010021

版权

java 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

Mybatis多对一映射查询

数据表多对一需要使用到association标签，在mapper.xml中定义好字段映射．
添加查询需要用到下面几个操作，

添加实体类
添加方法接口
在mapper.xml中定义sql语句

数据库表及数据

use wt;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `uid` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `login_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `worker_id` varchar(16) COLLATE utf8_unicode_ci DEFAULT '',
  `password` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `position` varchar(16) COLLATE utf8_unicode_ci DEFAULT '',
  `department` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `status` int(11) NOT NULL,
  `is_admin` int(11) DEFAULT NULL,
  `reserv2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `reserv1` int(11) DEFAULT NULL,
  `modifytime` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
create unique index idx_user_username on user_info(login_name);

INSERT INTO `user_info` VALUES ('1', 'login_name1', 'name1', '000', '1', '0', '1', 'robin@email.com', 1, 1, '', 0, '2011-08-10 10:02:44');
INSERT INTO `user_info` VALUES ('2', 'login_name2', 'name2', '001', '1', '0', '1', 'robin@email.com', 1, 1, '', 0, '2011-08-10 10:02:44');
INSERT INTO `user_info` VALUES ('3', 'login_name3', 'name3', '002', '1', '0', '2', 'robin@email.com', 1, 1, '', 0, '2011-08-10 10:02:44');


DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `uid` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `department_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `is_active` int(3) NOT NULL DEFAULT 0,
  `modifytime` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into `department` values('1', '软件组', 1, '2018-04-12 12:12:12');
insert into `department` values('2', '导航组', 1, '2018-04-12 12:12:12');
insert into `department` values('3', '算法组', 1, '2018-04-12 12:12:12');
insert into `department` values('4', '机械组', 1, '2018-04-12 12:12:12');

添加实体类

下面的Department是用户归属的部门，属于多对一关系．

package com.example.demo.model;


import java.util.Date;

public class UserInfo2 {
    private String uid;

    private String loginName;

    private String username;

    private String workerId;

    private String password;

    private String position;

    private Department department;

    private String email;

    private Integer status;

    private Integer isAdmin;

    private String reserv2;

    private Integer reserv1;

    private Date modifytime;

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid == null ? null : uid.trim();
    }

    public String getLoginName() {
        return loginName;
    }

    public void setLoginName(String loginName) {
        this.loginName = loginName == null ? null : loginName.trim();
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username == null ? null : username.trim();
    }

    public String getWorkerId() {
        return workerId;
    }

    public void setWorkerId(String workerId) {
        this.workerId = workerId == null ? null : workerId.trim();
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }

    public String getPosition() {
        return position;
    }

    public void setPosition(String position) {
        this.position = position == null ? null : position.trim();
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Integer getIsAdmin() {
        return isAdmin;
    }

    public void setIsAdmin(Integer isAdmin) {
        this.isAdmin = isAdmin;
    }

    public String getReserv2() {
        return reserv2;
    }

    public void setReserv2(String reserv2) {
        this.reserv2 = reserv2 == null ? null : reserv2.trim();
    }

    public Integer getReserv1() {
        return reserv1;
    }

    public void setReserv1(Integer reserv1) {
        this.reserv1 = reserv1;
    }

    public Date getModifytime() {
        return modifytime;
    }

    public void setModifytime(Date modifytime) {
        this.modifytime = modifytime;
    }



    /**
     * @return the department
     */
    public Department getDepartment() {
        return department;
    }

    /**
     * @param department the department to set
     */
    public void setDepartment(Department department) {
        this.department = department;
    }

    public String toString() {
        return "{"+ this.getLoginName() + ","
                + this.getUsername() + ","
                + this.getDepartment().getDepartmentName() +
                "}";
    }
}

方法接口

package com.example.demo.mapper.ext;

import com.example.demo.model.UserInfo2;

public interface UserInfo2Mapper {

    UserInfo2 selectUserInfo2(String uid);

}

mapper.xml数据库查询语句

注意:

名字空间一定要和接口类一致
resultMap中type需要和实体类一致
sql语句中u.*会多出department字段(varchar字符串)，在resultMap中已经注释掉了，这里Mybatis能够自动处理．

<?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.example.demo.mapper.ext.UserInfo2Mapper">
    <resultMap id="BaseResultMap" type="com.example.demo.model.UserInfo2">
        <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Thu Apr 19 10:39:13 CST 2018.
    -->
        <id column="uid" jdbcType="VARCHAR" property="uid" />
        <result column="login_name" jdbcType="VARCHAR" property="loginName" />
        <result column="username" jdbcType="VARCHAR" property="username" />
        <result column="worker_id" jdbcType="VARCHAR" property="workerId" />
        <result column="password" jdbcType="VARCHAR" property="password" />
        <result column="position" jdbcType="VARCHAR" property="position" />
        <!-- <result column="department" jdbcType="VARCHAR" property="department" /> -->
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="status" jdbcType="INTEGER" property="status" />
        <result column="is_admin" jdbcType="INTEGER" property="isAdmin" />
        <result column="reserv2" jdbcType="VARCHAR" property="reserv2" />
        <result column="reserv1" jdbcType="INTEGER" property="reserv1" />
        <result column="modifytime" jdbcType="TIMESTAMP" property="modifytime" />
        <association property="department" javaType="Department">
            <id property="uid" column="uid"/>
            <result column="department_name" property="departmentName"/>
            <result column="is_active" property="isActive"/>
        </association>
    </resultMap>

    <select id="selectUserInfo2" parameterType="java.lang.String" resultMap="BaseResultMap">
        select u.*,d.uid, d.department_name, d.is_active from user_info u, department d where u.department = d.uid and u.uid=#{uid}  
    </select>

</mapper>