Mybatis多对一映射查询

小灰灰 2022-05-27 13:43 263阅读 0赞

# Mybatis多对一映射查询 #

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

1.  添加实体类
2.  添加方法接口
3.  在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">
            
            <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="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>