mybatis一对多关联查询

1：创建数据库表

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

2：创建实体类 javabean

// Player.javapackage 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.javapackage com.doaoao.bean;import java.util.List;public class Team {    private int id;    private String name;    private List
      
        playerList;    @Override    public String toString() {        return "Team{" +                "id=" + id +                ", name='" + name + '\'' +                ", playerList=" + playerList +                '}';    }　　// 省略getter和setter
      

3：创建TeamMepper.xml映射文件

      
          
               
                
                
                    
                     
                 
            
           
               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}    
      

 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

      
          
               
                
                
                    
                     
                 
            
           
               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}    
      

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 t.id tid,t.name tname,p.id pid,p.name pname        FROM t_team t,t_player p        WHERE t.id = p.id    

2：添加

List
      
        selectPlayers();
      

3：创建测试类

@Test    public void selectPlayer(){        List
      
        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;    @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
      
        selectEmployeeById(int mgr);}
      

3：创建mapper，EmployeeMapper.xml

      
          
               
                
                
            
           
               SELECT id,name,job        FROM t_employee        WHERE mgr = #{id}    
      

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
      
        employeeList = employeeDao.selectEmployeeById(1001);        System.out.println(employeeList);    }}
      

 ...

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

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

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

// 添加下行private Employee leader;// getter setter// toString()

2：修改mapper中得内容

                  
                   
                   
               
                          SELECT id,name,job,mgr            FROM t_employee            WHERE id = #{id}        

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
      
        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
      
        courses;    @Override    public String toString() {        return "Student{" +                "id=" + id +                ", name='" + name + '\'' +                ", age=" + age +                ", score=" + score +                ", courses=" + courses +                '}';    }    // 省略getter setter}
      

4：创建Mapper

      
          
               
                
                
                    
                     
                 
            
           
               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;    
      

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