Mybatis 一对多关联

痛定思痛。 2024-02-18 18:01 62阅读 0赞

一对多关联有两种方法检索：一对多联合查询、一对多分组查询

以school（一）和student（多）为例：

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>     
      <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://127.0.0.1:3306/mybatis1?useUnicode=true&characterEncoding=utf8"/>  
            <property name="username" value="root"/>  
            <property name="password" value="root"/>  
          </dataSource>  
        </environment>  
      </environments>  
      <mappers>  
        <mapper resource="com/dw/dao/userMapper.xml"/>  
        <mapper resource="com/dw/dao/schoolMapper.xml"/>  
        <mapper resource="com/dw/dao/studentMapper.xml"/>  
      </mappers>  
        
    </configuration>

schoolMapper.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.dw.dao.SchoolDao"> 
      <insert id="insertSchool" parameterType="com.dw.domain.School">
        insert into t_school(sc_name) values(#{name});
      </insert>
    
      <select id="selectSchool" parameterType="int" resultType="com.dw.domain.School">
        select sc_id as id,sc_name as name from t_school as sc where sc.sc_id=#{id};
      </select>
     
     
      
      <resultMap type="com.dw.domain.School" id="selectSchool1">
        <id column="sc_id" property="id"/>
        <result column="sc_name" property="name"/>
        <collection property="students" ofType="com.dw.domain.Student">
          <id column="s_id" property="id"/>
          <result column="s_name" property="name"/>
          <result column="s_NO" property="NO"/>
        </collection>
      </resultMap>
      <select id="selectSchoolById" parameterType="int" resultMap="selectSchool1">
          select s.*,sc.* from t_school as sc inner join t_student as s on sc.sc_id = s.sc_id and sc.sc_id = #{id};
      </select>
      
      
      <resultMap type="com.dw.domain.School" id="selectSchool2">
         <id column="sc_id" property="id"/>
         <result column="sc_name" property="name"/>
         <collection property="students" 
         select="com.dw.dao.StudentDao.selectStudentById2"
         column="sc_id" fetchType="lazy">
         </collection>
      </resultMap>
      <select id="selectSchoolById2" parameterType="int" resultMap="selectSchool2">
         select sc.* from t_school as sc where sc.sc_id = #{id}; 
      </select>
    </mapper>

studentMapper.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.dw.dao.StudentDao">
      <insert id="insertStudent" parameterType="com.dw.domain.Student">
        insert into t_student(s_name,s_NO,sc_id) values(#{name},#{NO},#{school.id});
      </insert>
    
    
      <resultMap type="com.dw.domain.Student" id="selectStudent1">
        <id column="s_id" property="id"/>
        <result column="s_name" property="name"/>
        <result column="s_NO" property="NO"/>
        <association property="school" javaType="com.dw.domain.School">
          <id column="sc_id" property="id"/>
          <result column="sc_name" property="name"/>
        </association>
      </resultMap>
      <select id="selectStudentById" parameterType="int" resultMap="selectStudent1">
        select * from t_student as s inner join t_school as sc on sc.sc_id = s.sc_id and s.s_id=#{id};
      </select>
      
      
      <select id="selectStudentById2" parameterType="int" resultType="com.dw.domain.Student">
        select s.s_id as id ,s.s_name as name,s.s_NO as NO from t_student as s where s.sc_id = #{id};
      </select>
    </mapper>

测试：

@Test
      public void insertSchool() {
    	  System.out.println("school");
    	  myInit();
    	  School school = new School();
    	  school.setName("四川大学");
    	  schoolDao.insertSchool(school);
    	  session.commit();
    	  destory();
      }
      
      @Test
      public void insertStudent() {
    	  myInit();
    	  School school = schoolDao.selectSchool(1);
    	  System.out.println(school);
    	  Student student = new Student();
    	  student.setName("王五");
    	  student.setNO("108030009");
    	  student.setSchool(school);
    	  studentDao.insertStudent(student);
    	  session.commit();
    	  destory();
      }
      
      /**多对一查询*/
      @Test
      public void selectStudent() {
    	  myInit();
    	  Student student = studentDao.selectStudentById(1);
    	  System.out.println(student);
    	  destory();
      }
      
      
      /**一对多联合查询*/
      @Test
      public void selectSchool2() {
    	  myInit();
    	  School school = schoolDao.selectSchoolById(1);
    	  System.out.println(school);
    	  destory();
      }
      
      /**一对多分段查询*/
      @Test
      public void selectSchool3() {
    	  myInit();
    	  School school = schoolDao.selectSchoolById2(1);
    	  System.out.println(school);
    	  destory();
      }
      
      @SuppressWarnings("unused")
    private void myInit() {
    	  try {
    			in = Resources.getResourceAsStream("config.xml");
    			sessionFactory = new SqlSessionFactoryBuilder().build(in);
    			session = sessionFactory.openSession();
    			//userDao = session.getMapper(UserDao.class);
    			schoolDao = session.getMapper(SchoolDao.class);
    			studentDao = session.getMapper(StudentDao.class);
    	} catch (Exception e) {
    		e.printStackTrace();
    	}
      }
      
      @SuppressWarnings("unused")
    private void destory() {
    	  if(session !=null) {
    		  session.close();
    	  }
    	  if(in != null) {
    		  try {
    			in.close();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	  }
      }

student.java:

package com.dw.domain;
    
    public class Student {
    	private Integer id;
    	private String name;
    	private String NO;
    	private School school;
    
    	public Integer getId() {
    		return id;
    	}
    
    	public void setId(Integer id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public String getNO() {
    		return NO;
    	}
    
    	public void setNO(String nO) {
    		NO = nO;
    	}
    
    	public School getSchool() {
    		return school;
    	}
    
    	public void setSchool(School school) {
    		this.school = school;
    	}
    
    	@Override
    	public String toString() {
    		return "Student [id=" + id + ", name=" + name + ", NO=" + NO + ", school=" + school + "]";
    	}
    
    	
    }

school.java:

package com.dw.domain;
    
    import java.util.List;
    
    public class School {
    	private Integer id;
    	private String name;
    	private List<Student> students;
    
    	public Integer getId() {
    		return id;
    	}
    
    	public void setId(Integer id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public List<Student> getStudents() {
    		return students;
    	}
    
    	public void setStudents(List<Student> students) {
    		this.students = students;
    	}
    
    	@Override
    	public String toString() {
    		return "School [id=" + id + ", name=" + name + ", students=" + students + "]";
    	}
    
    }