Mybatis 一对多关系

今天药忘吃喽~ 2022-05-17 02:29 239阅读 0赞

学习参考自[luckysky博客][luckysky]

本次例子要完成的是mybatis关系映射中一对多的关系：比如一个学生对应一个专业，一个专业对应多个学生。

需求：1、根据学号查找该学生以及用查询后学生的专业外键值查出该生专业对象

2、根据专业id值查出专业以及所有属于该专业的学生

mybatis的映射实现方法有两种：1、通过一条sql语句的映射和自定义的resultMap结果集得到一个包含专业对象的学生对象结果或一个包含学生集合对象的专业对象结果集。如：select \* from stuinfo s,major m where s.sno=\#\{sno\} and s.majorId=m.id

2、通过两条sql语句的先后执行（其中一条查询的条件为另一个的执行结果）

如：先查询出学生对象select \* from stuinfo where sno=\#\{sno\},再通过外键查出专业：select \* from major where id=\#\{majorId\}

mybatis 中映射的实现方法：

<select id="getStuAndMajor" resultMap="stuAndMajor" parameterType="String">
          select * from stuinfo where sno=#{sno}
    </select>
        
    <resultMap type="Stuinfo" id="stuAndMajor">
          <id property="sno" column="sno"/>
          <result property="sname" column="sname"/>
          <association property="major" javaType="Major" select="getMajor" column="majorId"/>
    </resultMap>
         
    <select id="getMajor" resultType="Major" parameterType="int">
          select * from major where id=#{majorId}
    </select>

数据库stuinfo表：

![70][]

major表：

![70 1][]

其中学生表外键majorId是专业表的主键

1、项目目录结构如下

![70 2][]

2、Major、Stuinfo实体类

package com.onetomany.model;
    
    import java.util.List;
    
    public class Major {
    	
    	private int id;
    	private String majorName;
    	private int personNum;
    	private String turtor;
    	private List<Stuinfo> students;
    	
    	@Override
    	public String toString() {
    		return "Major [id=" + id + ", majorName=" + majorName + ", personNum=" + personNum + ", turtor=" + turtor
    				+ ", students=" + students + "]";
    	}
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getMajorName() {
    		return majorName;
    	}
    	public void setMajorName(String majorName) {
    		this.majorName = majorName;
    	}
    	public int getPersonNum() {
    		return personNum;
    	}
    	public void setPersonNum(int personNum) {
    		this.personNum = personNum;
    	}
    	public String getturtor() {
    		return turtor;
    	}
    	public void setturtor(String turtor) {
    		this.turtor = turtor;
    	}
    	public List<Stuinfo> getStudents() {
    		return students;
    	}
    	public void setStudents(List<Stuinfo> students) {
    		this.students = students;
    	}
    	
    }

package com.onetomany.model;
    
    import java.util.Date;
    
    public class Stuinfo {
    	private String sno;
    	private String sname;
    	private int gender;
    	private Date birthday;
    	private int majorId;
    	private Major major;
    	private int credit;
    	private String remark;
    	public String getSno() {
    		return sno;
    	}
    	public void setSno(String sno) {
    		this.sno = sno;
    	}
    	public String getSname() {
    		return sname;
    	}
    	public void setSname(String sname) {
    		this.sname = sname;
    	}
    	public int getGender() {
    		return gender;
    	}
    	public void setGender(int gender) {
    		this.gender = gender;
    	}
    	public Date getBirthday() {
    		return birthday;
    	}
    	public void setBirthday(Date birthday) {
    		this.birthday = birthday;
    	}
    	public int getMajorId() {
    		return majorId;
    	}
    	public void setMajorId(int majorId) {
    		this.majorId = majorId;
    	}
    	public Major getMajor() {
    		return major;
    	}
    	public void setMajor(Major major) {
    		this.major = major;
    	}
    	public int getCredit() {
    		return credit;
    	}
    	public void setCredit(int credit) {
    		this.credit = credit;
    	}
    	public String getRemark() {
    		return remark;
    	}
    	public void setRemark(String remark) {
    		this.remark = remark;
    	}
    	@Override
    	public String toString() {
    		return "Stuinfo [sno=" + sno + ", sname=" + sname + ", gender=" + gender + ", birthday=" + birthday
    				+ ", majorId=" + majorId + ", major=" + major + ", credit=" + credit + ", remark=" + remark + "]";
    	}
    	
    }

3、mybatis.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>
     
     	<properties resource="mysql.properties"/>
     	
     	<environments default="development">
     		<environment id="development">
     			<transactionManager type="JDBC"/>
     			<dataSource type="POOLED">
     				<property name="driver" value="${driver}"/>
     				<property name="url" value="${url}"/>
     				<property name="username" value="${user}"/>
     				<property name="password" value="${password}"/>
     			</dataSource>
     		</environment>
     	</environments>
     	
     	<mappers>
     		<mapper resource="com/onetomany/mapper/majorMapper.xml"/>
     		<mapper resource="com/onetomany/mapper/StuinfoMapper.xml"/>		
     	</mappers>
     
     </configuration>

4、majorMapper.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.onetomany.mapper.InterDaoMapper">
    	
    	<resultMap id="majorMap" type="com.onetomany.model.Major">
    		<id property="id" column="id" javaType="java.lang.Integer"/>
    		<result property="majorName" column="majorName" javaType="java.lang.String"/>
    		<result property="personNum" column="personNum" javaType="java.lang.Integer"/>
    		<result property="turtor" column="turtor" javaType="java.lang.String"/>
    		
    		
    		<collection property="students" ofType="com.onetomany.model.Stuinfo">
    			<id property="sno" column="sno" javaType="java.lang.String"/>
    			<result property="sname" column="sname" javaType="java.lang.String"/>
    			<result property="gender" column="gender" javaType="java.lang.Integer"/>
    			<result property="birthday" column="birthday" javaType="java.util.Date"/>
    			<result property="credit" column="credit" javaType="java.lang.Integer"/>
    			<result property="remark" column="remark" javaType="java.lang.String"/>
    		</collection>
    	</resultMap>
    	
    	<select id="getMajorStudents" parameterType="int" resultMap="majorMap">
    		select * from major m,stuinfo s where m.id=#{id} and s.majorId=#{id}
    	</select>
    	
    </mapper>

5、StuinfoMapper.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.onetomany.mapper.InterDaoMapper">
    
    	
    	<resultMap id="stuMap" type="com.onetomany.model.Stuinfo">
    		<id property="sno" column="sno" javaType="java.lang.String"/>
    		<result property="sname" column="sname" javaType="java.lang.String"/>
    		<result property="gender" column="gender" javaType="java.lang.Integer"/>
    		<result property="majorId" column="majorId" javaType="java.lang.Integer"/>
    		<result property="birthday" column="birthday" javaType="java.util.Date"/>
    		<result property="credit" column="credit" javaType="java.lang.Integer"/>
    		<result property="remark" column="remark" javaType="java.lang.String"/>
    		
    		<association property="major" javaType="com.onetomany.model.Major">
    			<id property="id" column="id" javaType="java.lang.Integer"/>
    			<result property="majorName" column="majorName" javaType="java.lang.String"/>
    			<result property="personNum" column="personNum" javaType="java.lang.Integer"/>
    			<result property="turtor" column="turtor" javaType="java.lang.String"/>
    		</association>
    	</resultMap>
    	
    	
    	<select id="getStuinfo" parameterType="String" resultMap="stuMap">
    		select * from stuinfo s,major m where s.sno=#{sno} and s.majorId=m.id
    	</select>
    
    </mapper>

6、DBtool

package com.onetomany.tool;
    
    import java.io.IOException;
    import java.io.Reader;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    public class DBtool {
    
    	private static SqlSessionFactory sqlSessionFactory;
    	
    	static{
    		
    		try {
    			Reader reader=Resources.getResourceAsReader("mybatis.cfg.xml");
    			sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    	
    	public static SqlSession getSqlSession(){
    		return sqlSessionFactory.openSession();
    	}
    }

7、InterDaoMapper

package com.onetomany.mapper;
    
    import java.util.List;
    
    import com.onetomany.model.Major;
    import com.onetomany.model.Stuinfo;
    
    public interface InterDaoMapper {
    
    	public Stuinfo getStuinfo(String sno);	//通过学号获取学生对象包括其专业对象
    	
    	public Major getMajorStudents(int id);//通过id值获得该专业的所有学生
    }

8、MapperImpl

package com.onetomany.mapper;
    
    import org.apache.ibatis.session.SqlSession;
    
    import com.onetomany.model.Major;
    import com.onetomany.model.Stuinfo;
    import com.onetomany.tool.DBtool;
    
    public class MapperImpl implements InterDaoMapper {
    
    	@Override
    	public Stuinfo getStuinfo(String sno) {
    		SqlSession sqlSession=DBtool.getSqlSession();
    		InterDaoMapper mapper=sqlSession.getMapper(InterDaoMapper.class);
    		Stuinfo stu=mapper.getStuinfo(sno);
    		sqlSession.commit();
    		sqlSession.close();
    		return stu;
    	}
    
    	@Override
    	public Major getMajorStudents(int id) {
    		SqlSession sqlSession=DBtool.getSqlSession();
    		InterDaoMapper mapper=sqlSession.getMapper(InterDaoMapper.class);
    		Major major=mapper.getMajorStudents(id);
    		sqlSession.commit();
    		sqlSession.close();
    		return major;
    	}
    
    }

9、test

package com.onetomany.service;
    
    import com.onetomany.mapper.InterDaoMapper;
    import com.onetomany.mapper.MapperImpl;
    import com.onetomany.model.Major;
    import com.onetomany.model.Stuinfo;
    
    public class Test {
    	public static void main(String[] args) {
    		
    		InterDaoMapper interDaoMapper=new MapperImpl();
    		
    		Stuinfo student=interDaoMapper.getStuinfo("081005");
    		System.out.println(student);
    		
    		Major major=interDaoMapper.getMajorStudents(15);
    		System.out.println(major);
    		
    	}
    }

10、结果

![70 3][]

[luckysky]: https://www.cnblogs.com/aima1013/p/6144861.html
[70]: /images/20220517/dd448242fcb0433c9f6ce631c1cb5ffa.png
[70 1]: /images/20220517/15b0ca97e1de47589b8c8febc8ffa0b6.png
[70 2]: /images/20220517/7ec60bd379e343e396bb34059a0a4c0d.png
[70 3]: /images/20220517/c7326993e0604998a648cd62323276d0.png