mybatis 一对多和多对一关联查询

首先  数据库量表之间字段关系（没有主外键）

studentmajor表的id字段对应student表里major字段

两个实体类

package com.model;import java.util.Date;public class Student {    private Integer sno;    private String sname;    private String ssex;    private Integer sclass;    private StudentMajor studentmajor;        public Student() {        super();    }    public Student(Integer sno, String sname, String ssex, Integer sclass, StudentMajor studentmajor) {        super();        this.sno = sno;        this.sname = sname;        this.ssex = ssex;        this.sclass = sclass;        this.studentmajor = studentmajor;    }    public StudentMajor getStudentmajor() {        return studentmajor;    }    public void setStudentmajor(StudentMajor studentmajor) {        this.studentmajor = studentmajor;    }    public Integer getSno() {        return sno;    }    public void setSno(Integer sno) {        this.sno = sno;    }    public String getSname() {        return sname;    }    public void setSname(String sname) {        this.sname = sname;    }    public String getSsex() {        return ssex;    }    public void setSsex(String ssex) {        this.ssex = ssex;    }    @Override    public String toString() {        return "Student [sno=" + sno + ", sname=" + sname + ", ssex=" + ssex + ", sclass=" + sclass + ", studentmajor="                + studentmajor + "]";    }    public Integer getSclass() {        return sclass;    }    public void setSclass(Integer sclass) {        this.sclass = sclass;    }}

 

package com.model;import java.util.List;public class StudentMajor {    private Integer id;    private String mcode;    private String mname;    private List<Student> students;    public StudentMajor() {        super();    }    public StudentMajor(Integer id, String mcode, String mname, List<Student> students) {        super();        this.id = id;        this.mcode = mcode;        this.mname = mname;        this.students = students;    }    @Override    public String toString() {        return "StudentMajor [id=" + id + ", mcode=" + mcode + ", mname=" + mname + ", students=" + students + "]";    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getMcode() {        return mcode;    }    public void setMcode(String mcode) {        this.mcode = mcode;    }    public String getMname() {        return mname;    }    public void setMname(String mname) {        this.mname = mname;    }    public List<Student> getStudents() {        return students;    }    public void setStudents(List<Student> students) {        this.students = students;    }}

定义两个接口

package com.dao;import java.util.List;import java.util.Map;import com.model.Student;public interface StudentMapper {    /**     * 全表查询     */    public List<Student> selectall();　　/**     * 根据专业查人员,给一对多用     */    public List<Student> selectz(Integer major);}

 

package com.dao;import java.util.List;import com.model.StudentMajor;public interface StudentMajorMapper {    /**     * 全表查询     * @return     */    public List<StudentMajor> selectAll();    /**     * 根据主键查数据,给多对一用     * @param id     * @return     */    public StudentMajor select(Integer id);}

定义两个实体类的映射方法

<?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.dao.StudentMapper">        <!-- 多对一查询  -->    <resultMap type="Student" id="slist">        <!-- 跟一对一一样用association标签,实体类定义的成员,要跟数据库字段名对应上 -->        <association property="studentmajor" column="major"         select="com.dao.StudentMajorMapper.select"/> <!-- 用接口里定义的方法，根据student表中的major字段查出对应数据 -->    </resultMap>    <!-- 查全部 -->    <select id="selectall" resultMap="slist" >        select * from student    </select>    <!-- 根据专业查人员  -->    <select id="selectz" parameterType="Integer" resultType="student">        select * from student s where s.major=#{major}    </select> </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.dao.StudentMajorMapper">          <!-- 一对多查询关联  -->     <resultMap type="StudentMajor" id="slist">         <!-- 实体类属性对应数据库的主键字段，不然主键会查不到 -->         <id property="id" column="id"/>         <!-- 用collection标签 ,也是实体类属性要对应数据库字段-->         <collection property="students" column="id"         select="com.dao.StudentMapper.selectz">         </collection>         </resultMap>          <!-- 全表查询 -->     <select id="selectAll" resultMap="slist">         select * from studentmajor     </select>          <!-- 根据主键查 -->     <select id="select" parameterType="Integer" resultType="StudentMajor">         select * from studentmajor where id=#{id}     </select>   </mapper>

JUnit测试

package com.util;import java.util.List;import org.apache.ibatis.session.SqlSession;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.dao.StudentMajorMapper;import com.dao.StudentMapper;import com.model.Student;import com.model.StudentMajor;public class JJJtest {    private SqlSession ss;    private StudentMapper sm;    private StudentMajorMapper smm;    @Before    public void setUp() throws Exception {        ss=SqlSessionUtil.getSqlSession();        sm=ss.getMapper(StudentMapper.class);        smm=ss.getMapper(StudentMajorMapper.class);            }    @After    public void tearDown() throws Exception {        ss.commit();        ss.close();    }    //一对多查询    public void test() {        List<StudentMajor> list=smm.selectAll();        for(StudentMajor a:list){            System.out.println(a);        }    }    //根据专业查人员，给一对多用    public void selectz(){        List<Student> l=sm.selectz(3);        for(Student a:l){            System.out.println(a);        }    }

    //多对一查询    @Test    public void selectall() {        List<Student> st=sm.selectall();        for(Student tt:st){            System.out.println(tt);        }    }    //根据主键查询，给多对一用    public void select(){        StudentMajor a=smm.select(1);        System.out.println(a);    }}

一对多查询结果

多对一查询结果