MyBatis学习总结—实现关联表查询

1、数据库表结构

CREATE TABLE teacher(
    t_id INT PRIMARY KEY AUTO_INCREMENT, 
    t_name VARCHAR(20)
);
CREATE TABLE class(
    c_id INT PRIMARY KEY AUTO_INCREMENT, 
    c_name VARCHAR(20), 
    teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);    

INSERT INTO teacher(t_name) VALUES('teacher1');
INSERT INTO teacher(t_name) VALUES('teacher2');

INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

2、定义实体

　　2.1 TeacherEntity类，TeacherEntity类是teacher表对应的实体类。

package com.test.model;

//Teacher实体类
public class TeacherEntity {
    private int tid;
    private String tname;
    private ClassEntity classEntity;

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    public Integer getTid() {
        return tid;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public String getTname() {
        return tname;
    }

    public void setClassEntity(ClassEntity classEntity) {
        this.classEntity = classEntity;
    }

    public ClassEntity getClassEntity() {
        return classEntity;
    }
}

　　2.2 ClassEntity类，ClassEntity类是class表对应的实体类。

package com.test.model;

//Class实体类
public class ClassEntity {
    private int cid;
    private String cname;
    private int teacherid;

    public void setCid(Integer cid) {
        this.cid = cid;
    }

    public Integer getCid() {
        return cid;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public String getCname() {
        return cname;
    }

    public void setTeacherid(Integer teacherid) {
        this.teacherid = teacherid;
    }

    public Integer getTeacherid() {
        return teacherid;
    }
}

　　这里需要注意的是，在TeacherEntity类里申明ClassEntity实体

3、定义sql映射文件TeacthMapper.xml<?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.test.dao.TeacherMapper" >

    <resultMap type="com.test.model.TeacherEntity" id="resultTeacherList">
　　　　　<!-- 用id属性来映射主键字段 -->
        <id property="tid" column="t_id"/>
        <!-- 用result属性来映射非主键字段 -->  
        <result property="tname" column="t_name"/>

        <association property="classEntity" javaType="com.test.model.ClassEntity">
            <id property="cid" column="c_id"/>
            <id property="cname" column="c_name"/>
            <id property="teacherid" column="teacher_id"/>
        </association>
    </resultMap>

    <select id="getList" resultType="com.test.model.TeacherEntity" resultMap="resultTeacherList">
      select t.t_id,t.t_name,c.c_id,c.c_name,c.teacher_id from teacher t,class c where t.t_id=c.teacher_id
    </select>
</mapper>

 

MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：

　　1、resultMap > type 是TeacherEntity实体类

　　2、resultMap > id属性,要和下面select > resultMap对应

　　3、resultMap 下的id标签对应sql语句查询teacther表的字段

　　4、association > property的值是TeacherEntity里申明的ClassEntity的属性

　　5、association > javaType的值是ClassEntity实体类

　　6、association 下的 id标签是对应sql语句查询class表的字段

　　补充说明：

• property:对象属性的名称
• javaType:对象属性的类型
• column:所对应的外键字段名称
• select:使用另一个查询封装的结果

4、定义TeacthMapper.java查询实现接口

package com.test.dao;

import com.test.model.TeacherEntity;

import java.util.List;

public interface TeacherMapper {
    List<TeacherEntity> getList();
}

 

5、编写单元测试代码

package com.test.controller;

import com.test.dao.TeacherMapper;
import com.test.model.TeacherEntity;
import net.sf.json.JSONArray;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml", "classpath:dispatcher-servlet.xml"})
public class test {
    @Autowired
    TeacherMapper teacherMapper;

    @Test
    public void demo(){

      List<TeacherEntity> teacherList =teacherMapper.getList();
      System.out.println(JSONArray.fromObject(teacherList));
    }
}

 

6、查询结果

[{
    "tname": "teacher1",
    "classEntity": {
        "teacherid": 1,
        "cname": "class_a",
        "cid": 1
    },
    "tid": 1
}, {
    "tname": "teacher2",
    "classEntity": {
        "teacherid": 2,
        "cname": "class_b",
        "cid": 2
    },
    "tid": 2
}]