mybatis框架mysql查询多对一

多对一查询的两种做法：

在入门的基础上进行，有看不懂的点击这里：mybatis入门案例

Lombok插件：
@Date注释可自动生成get、set跟toString等方法，使程序更加简便，只需在全局配置文件pom.xml中加上配置：

<dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>
    </dependencies>

数据库mybatis，表student，teacher：
student.sql：

SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for student

---

DROP TABLE IF EXISTS student;
CREATE TABLE student (
id char(255) NOT NULL DEFAULT ‘’,
name char(255) DEFAULT NULL,
tid char(255) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

– Records of student

---

INSERT INTO student VALUES (‘1’, ‘1’, ‘1’);
INSERT INTO student VALUES (‘2’, ‘2’, ‘1’);
INSERT INTO student VALUES (‘3’, ‘3’, ‘1’);
INSERT INTO student VALUES (‘4’, ‘4’, ‘1’);
INSERT INTO student VALUES (‘5’, ‘5’, ‘1’);

teacher.sql:

SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for teacher

---

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
id char(255) CHARACTER SET utf8 NOT NULL DEFAULT ‘’,
name char(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

– Records of teacher

---

INSERT INTO teacher VALUES (‘1’, ‘teacher’);

创建一个新项目或新模块：

Teacher.java：

package com.feng.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}

Student.java：

package com.feng.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

StudentMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.feng.dao.StudentMapper">
    <select id="getStudent2" resultMap="Student2">
        select s.id sid,s.name sname,t.name tname
            from student s,teacher t
            where s.tid = t.id;
    </select>
    <resultMap id="Student2" type="Student">
        <result column="sid" property="id"/>
        <result column="sname" property="name"/>
        <association property="teacher" javaType="Teacher">
            <result column="tname" property="name"/>
        </association>
    </resultMap>


    <select id="getStudent" resultMap="StudentTeacher">
    select * from student
</select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，我们需要单独处理 对象： association 集合： collection -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>
</mapper>

TeacherMapper.xml（只需要加上mapper标签）:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.feng.dao.TeacherMapper">

</mapper>

StudentMapper接口：

package com.feng.dao;

import com.feng.pojo.Student;
import java.util.List;

public interface StudentMapper {
    //查询所有学生
    public List<Student> getStudent();
}

mybatis-config.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--配置-->
<configuration>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <!--给实体类起别名-->
    <typeAliases>
        <package name="com.feng.pojo"/>
    </typeAliases>

    <!--环境-->
    <environments default="development">
        <environment id="development">
            <!--事务管理器-->
            <transactionManager type="JDBC"/>
            <!--数据源-->
            <dataSource type="POOLED">
                <!--属性-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <!--<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>-->
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>
    <!--映射-->
    <mappers>
        <mapper class="com.feng.dao.StudentMapper"/>
        <mapper class="com.feng.dao.TeacherMapper"/>
    </mappers>
</configuration>

测试类MyTest:

import com.feng.dao.StudentMapper;
import com.feng.pojo.Student;
import com.feng.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.testng.annotations.Test;

import java.util.List;

public class MyTest {

    @Test
    public void getStudent(){
        SqlSession sqlSession = MybatisUtils.getSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> list = mapper.getStudent();
        for (Student student : list) {
            System.out.println(student);
        }
        sqlSession.close();
    }

    @Test
    public void getStudent2(){
        SqlSession sqlSession = MybatisUtils.getSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> list = mapper.getStudent();;
        for (Student student : list) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

两种都能查出来：