mybatis_关联查询

mybatis一对多关联查询

1：创建数据库表

-- id：主键
-- name：球队名称
CREATE TABLE `t_team` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

-- id：主键
-- name：球员姓名
-- tid：球员所在球队id
CREATE TABLE `t_player` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `tid` INT NULL,
  PRIMARY KEY (`id`));

2：创建实体类 javabean

// Player.java
package com.doaoao.bean;

public class Player {
    private int id;
    private String name;

    public Player(String name) {
        this.name = name;
    }
    public Player() {
    }

    @Override
    public String toString() {
        return "Player{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
　　// 省略getter setter

}


// Team.java
package com.doaoao.bean;

import java.util.List;

public class Team {
    private int id;
    private String name;
    private List<Player> playerList;

    @Override
    public String toString() {
        return "Team{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", playerList=" + playerList +
                '}';
    }
　　// 省略getter和setter

3：创建TeamMepper.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.doaoao.dao.TeamDao">
    <resultMap id="teamMap" type="Team">
        <id column="teamid" property="id"></id>
        <result column="tname" property="name"></result>

        <collection property="playerList" ofType="Player">
            <id column="pid" property="id" />
            <result column="pname" property="name" />
        </collection>
    </resultMap>

    <select id="selectTeamById" resultMap="teamMap">
        SELECT t.id teamid,t.name tname,p.id pid,p.name pname
        FROM t_team t,t_player p
        where t.id = p.tid and t.id=#{id}
    </select>
</mapper>

 4：创建dao并实现接口

package com.doaoao.dao;

import com.doaoao.bean.Team;

public interface TeamDao {
    Team selectTeamById(int id);
}

5：创建测试类

package com.doaoao.test;

import com.doaoao.bean.Team;
import com.doaoao.dao.TeamDao;
import com.doaoao.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class Test01 {
    private SqlSession sqlSession;
    private TeamDao teamDao;

    @Before
    public void init(){
        sqlSession = MyBatisUtil.getSqlSession();
        teamDao = sqlSession.getMapper(TeamDao.class);
    }

    @After
    public void close(){
        if(sqlSession != null){
            sqlSession.close();
        }
    }

    @Test
    public void selectTeamById(){
        Team team = teamDao.selectTeamById(1);
        System.out.println(team);
    }
}

# 注：还得创建util下的MyBatisUtil，创建db.properties，log4j.properties，mybatis.xml　　和之前类似

# 输出结果

Team{id=1, name='huojian', playerList=[Player{id=1, name='hadeng'}, Player{id=3, name='zhangsan'}]}

...

mybatis一对多关联查询

# 先创建一个一对多关联查询

1：修改之前JavaBean中Player类中的内容

package com.doaoao.bean;

public class Player {
    private int id;
    private String name;
    private Team team;

    @Override
    public String toString() {
        return "Player{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", team=" + team +
                '}';
    }
    // getter和setter省略
}

2：创建mapper，PlayerMapper.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.doaoao.dao.PlayerDao">
    <resultMap id="playerMap" type="Player">
        <id column="pid" property="id" />
        <result column="pname" property="name" />
        <association property="team" javaType="Team">
            <id column="teamid" property="id" />
            <result column="tname" property="name" />
        </association>
    </resultMap>

    <select id="selectPlayerById" resultMap="playerMap">
        SELECT t.id tid,t.name tname,p.id pid,p.name pname
        FROM t_team t,t_player p
        WHERE t.id = p.id and p.id = #{id}
    </select>
</mapper>

3：创建接口

package com.doaoao.dao;

import com.doaoao.bean.Player;

public interface PlayerDao {
    Player selectPlayerById(int id);
}

4：创建测试类

package com.doaoao.test;

import com.doaoao.bean.Player;
import com.doaoao.dao.PlayerDao;
import com.doaoao.dao.TeamDao;
import com.doaoao.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class Test02 {
    private SqlSession sqlSession;
    private PlayerDao playerDao;

    @Before
    public void init(){
        sqlSession = MyBatisUtil.getSqlSession();
        playerDao = sqlSession.getMapper(PlayerDao.class);
    }

    @After
    public void close(){
        if(sqlSession != null){
            sqlSession.close();
        }
    }
    @Test
    public void selectPlayerById(){
        Player player = playerDao.selectPlayerById(1);
        System.out.println(player);
    }
}

# 创建多对一查询

1：添加mapper中的内容

    <select id="selectPlayers" resultMap="playerMap">
        SELECT t.id tid,t.name tname,p.id pid,p.name pname
        FROM t_team t,t_player p
        WHERE t.id = p.id
    </select>

2：添加

List<Player> selectPlayers();

3：创建测试类

    @Test
    public void selectPlayer(){
        List<Player> players = playerDao.selectPlayers();
        System.out.println(players);
    }

 ...

mybatis自关联查询（一对多的关系）

例如：给出一个员工编号，得出其下级所有员工

1：创建数据库中相应的表

-- 建表语句
CREATE TABLE `t_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- id ：主键
-- name ：员工姓名
-- job ：工作岗位
-- mgr ：该员工直接上级领导的id


-- 初始化数据
INSERT INTO `t_employee` VALUES (1001,'king','董事长',NULL),(1002,'jack','技术总监',1001),(1003,'paul','财务总监',1001),(1004,'tom','销售总监',1001),(1005,'tomas','技术一部经理',1002),(1006,'linda','技术二部经理',1002),(1007,'lucy','会计',1003),(1008,'lily','出纳',1003),(1009,'terry','销售一部经理',1004),(1010,'emma','销售二部经理',1004),(1011,'may','软件工程师',1005),(1012,'bella','软件工程师',1005),(1013,'kelly','软件工程师',1006);

表中的数据

2：创建JavaBena

package com.doaoao.bean;

import java.util.List;

public class Employee {
    private int id;
    private String name;
    private String job;

    private List<Employee> employee;

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", job='" + job + '\'' +
                ", employee=" + employee +
                '}';
    }

    // getter和setter省略
}

3： 创建Dao

package com.doaoao.dao;

import com.doaoao.bean.Employee;

import java.util.List;

public interface EmployeeDao {
    List<Employee> selectEmployeeById(int mgr);
}

3：创建mapper，EmployeeMapper.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.doaoao.dao.EmployeeDao">
    <resultMap id="employeeMap" type="Employee">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <collection property="employee" ofType="Employee" select="selectEmployeeById" column="id" />
    </resultMap>

    <select id="selectEmployeeById" resultMap="employeeMap">
        SELECT id,name,job
        FROM t_employee
        WHERE mgr = #{id}
    </select>
</mapper>

4：创建测试类

package com.doaoao.test;

import com.doaoao.bean.Employee;
import com.doaoao.dao.EmployeeDao;
import com.doaoao.dao.PlayerDao;
import com.doaoao.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

public class Employee_test {
    private SqlSession sqlSession;
    private EmployeeDao employeeDao;

    @Before
    public void init(){
        sqlSession = MyBatisUtil.getSqlSession();
        employeeDao = sqlSession.getMapper(EmployeeDao.class);
    }

    @After
    public void close(){
        if(sqlSession != null){
            sqlSession.close();
        }
    }

    @Test
    public void selectEmployeeById(){
        List<Employee> employeeList = employeeDao.selectEmployeeById(1001);
        System.out.println(employeeList);
    }
}

 ...

mybatis自关联查询（多对一的关系） 

例如：给出一个员工编号，给出其所有上级领导编号

1：修改实体类Employee.java中得内容

// 添加下行
private Employee leader;

// getter setter
// toString()

2：修改mapper中得内容

        <resultMap id="LeaderMap" type="Employee">
            <id column="id" property="id" />
            <result column="name" property="name" />
            <collection property="leader" ofType="Employee" select="selectLeaderById" column="mgr" />
        </resultMap>

        <select id="selectLeaderById" resultMap="LeaderMap">
            SELECT id,name,job,mgr
            FROM t_employee
            WHERE id = #{id}
        </select>

3： 修改接口Dao中得内容

package com.doaoao.dao;

import com.doaoao.bean.Employee;

public interface EmployeeDao {
    Employee selectLeaderById(int mgr);
}

4：添加测试类

    @Test
    public void selectLeaderById(){
        Employee employee = employeeDao.selectLeaderById(1011);
        System.out.println(employee);
    }

 5：输出内容

Employee{id=1011, name='may', job='软件工程师', employee=null, leader=Employee{id=1005, name='tomas', job='技术一部经理', employee=null, leader=Employee{id=1002, name='jack', job='技术总监', employee=null, leader=Employee{id=1001, name='king', job='董事长', employee=null, leader=null}}}}

...

mybatis多对多关联查询

例如：学生和课程之间得关系，一个学生可以选择多门课程，一门课程可以被多个学生选择

1：创建数据库表

-- 创建课程表
CREATE TABLE `t_course` (
  `id` INT NOT NULL,
  `name` VARCHAR(20) NULL,
  PRIMARY KEY (`id`));

-- 创建选课表
CREATE TABLE `learnmybatis`.`t_student_course` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sid` INT NULL,
  `cid` INT NULL,
  PRIMARY KEY (`id`));

-- 创建学生表
CREATE TABLE `learnmybatis`.`t_student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NULL,
  `age` INT NULL,
  `score` DOUBLE NULL,
  PRIMARY KEY (`id`));

2：初始化数据

-- 初始化课程数据
INSERT INTO `learnmybatis`.`t_course` (`id`, `name`) VALUES ('1001', 'AngularJS');
INSERT INTO `learnmybatis`.`t_course` (`id`, `name`) VALUES ('1002', 'Vue.js');
INSERT INTO `learnmybatis`.`t_course` (`id`, `name`) VALUES ('1003', 'JQuery');
INSERT INTO `learnmybatis`.`t_course` (`id`, `name`) VALUES ('1004', 'Ajax');

-- 初始化选课表数据
INSERT INTO `learnmybatis`.`t_student_course` (`sid`, `cid`) VALUES ('1', '1001');
INSERT INTO `learnmybatis`.`t_student_course` (`sid`, `cid`) VALUES ('1', '1002');
INSERT INTO `learnmybatis`.`t_student_course` (`sid`, `cid`) VALUES ('1', '1003');
INSERT INTO `learnmybatis`.`t_student_course` (`sid`, `cid`) VALUES ('3', '1001');
INSERT INTO `learnmybatis`.`t_student_course` (`sid`, `cid`) VALUES ('3', '1002');
INSERT INTO `learnmybatis`.`t_student_course` (`sid`, `cid`) VALUES ('4', '1001');

3：创建实例化类

package com.doaoao.bean;

import java.util.List;

public class Course {
    private int id;
    private String name;
    private List<Student> students;

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", students=" + students +
                '}';
    }
　　　　// 省略getter setter
}

package com.doaoao.bean;

import java.util.List;

public class Student {
    private int id;

    private String name;

    private int age;

    private double score;

    private List<Course> courses;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", score=" + score +
                ", courses=" + courses +
                '}';
    }
    // 省略getter setter
}

4：创建Mapper

<?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.doaoao.dao.CourseDao">

    <resultMap id="courseMapper" type="Course">

        <id property="id" column="cid"/>
        <result property="name" column="cname"/>
        <collection property="students" ofType="Student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>


    <select id="selectCourseStudent" resultMap="courseMapper">
        SELECT
            c.id cid, c.name cname, s.id sid, s.name sname
        FROM
            t_course c,
            t_student s,
            t_student_course sc
        WHERE
            c.id = #{id}
            AND s.id = sc.sid
            AND c.id = sc.cid;
    </select>
</mapper>

5：创建Dao接口

package com.doaoao.dao;

import com.doaoao.bean.Course;

public interface CourseDao {
    Course selectCourseStudent(int id);
}

6：创建测试类

    @Test
    public void selectCourseStudent(){
        Course course = courseDao.selectCourseStudent(1001);
        System.out.println(course);
    }

 ...

 本笔记参考自：小猴子老师教程 http://www.monkey1024.com