关于Mybatis的多对一和一对多查询

时间：2021-06-20 09:37:53

我们在制作web项目的时候常常会遇到点击查询详情信息或者是显示多个对象的相同点，这些都可以假想成一堆多或者多对一的情况。

那我们思考一下，能不能在不点击详情信息的时候不进行详情信息的查询呢？这样不是就可以减少数据库的访问量，降低内存和时间的消耗吗？

我在使用Mybatis框架的时候，学习到了他自带的一对多和多对一查询，需要将sql语句分开再进行异步加载就可以降低时间和内存的消耗了。

一对多（也就是通过该对象的一个特征去抓取含有这个特征的其他对象）：

例子：一个系里面含有的教师（通过系id关联）

教师的bean类（set和get方法自动省略）：

public class TeacherT implements Serializable{
    /**
    * 
    */
    private static final long serialVersionUID = -4767749968286241765L;
    private Integer teacherId;
    private String teacherName;
    private Date teacherBirthday;
    private Integer xiId;
    private Integer sexId;
    private Integer titleId;
    private Integer teacherWage;
    private Integer teacherParty;
    private String teacherPassword;
    private String teacherEmail;
｝
系的bean类（set和get方法自动省略）： 

public class DepartmentT implements Serializable {
    /**
     * 
    */
    private static final long serialVersionUID = 2365509376094808947L;
    private String departmentId;
    private String departmentName;
    private String departmentDirectorId;
｝
然后当我们查询系的信息的时候我们需要通过系id查看教师都有哪些，这时候我们需要新建一个bean类，其中包含系的bean和一个可以存教师的容器，在这里我用的List： 
public class DepartmentExt extends DepartmentT {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1388254472842080452L;
	private List<TeacherT> tlist;

	public List<TeacherT> getTlist() {
		return tlist;
	}

	public void setTlist(List<TeacherT> tlist) {
		this.tlist = tlist;
	}
}
bean基本完成，接下来要配置xml文件，新建一个departmentMapper.xml，含有一个方法查询出系的所有信息（由于赶时间这里用的*不要模仿），新建一个resultMap，继承含有系信息的resultmap，然后在里面配置collection标签，标签的属性property就是上面新建的bean类里面的list，注意这里的属性要用你创建的list的名字，column就是关联属性，要放入department_id，oftype就是该list要存的属性，这里我放入的teacher的bean类（如果没配置简写方法这里需要放入bean类的全路径比如：java.lang.String）,select对应的就是通过departmnent_id查询教师的mapper方法，使用 mapper.方法名 的形式。collection里面的内容放的就是teacher的所有属性。 

，<?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.web.map.DepartmentTMapper" >
<cache/>
  <resultMap id="BaseResultMap" type="departmentT" >
    	<id column="department_id" property="departmentId" jdbcType="CHAR" />
	    <result column="department_name" property="departmentName" jdbcType="CHAR" />
	    <result column="department_director_id" property="departmentDirectorId" jdbcType="CHAR" />
  </resultMap>
  
  
 <resultMap type="departmentExt" id="departmentExtMap" extends="BaseResultMap">
 <collection property="tlist" column="department_id" ofType="teacherT" select="com.web.map.TeacherMapper.selectByDepartmentId">
    <id column="teacher_ID" property="teacherId" jdbcType="INTEGER" />
    <result column="teacher_NAME" property="teacherName" jdbcType="VARCHAR" />
    <result column="teacher_BIRTHDAY" property="teacherBirthday" jdbcType="DATE" />
    <result column="xi_id" property="xiId" jdbcType="INTEGER" />
    <result column="sex_ID" property="sexId" jdbcType="INTEGER" />
    <result column="title_ID" property="titleId" jdbcType="INTEGER" />
    <result column="teacher_WAGE" property="teacherWage" jdbcType="INTEGER" />
    <result column="teacher_PARTY" property="teacherParty" jdbcType="INTEGER" />
    <result column="teacher_PASSWORD" property="teacherPassword" jdbcType="VARCHAR" />
    <result column="teacher_EMAIL" property="teacherEmail" jdbcType="VARCHAR" />
 </collection>
 </resultMap>
 
 <select id="selectDepartment" resultMap="departmentExtMap" >
 select * from department_t
 </select>
</mapper>
departmentMapper.java(接口): 

public interface DepartmentTMapper {
    public List<DepartmentExt> selectDepartment();
}
teacher.xml文件，包含一个selectByDepartment方法，这个方法传入的参数就是上面配置的colum关联的属性。 

<?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只能写一个 -->
<mapper namespace="com.web.map.TeacherMapper">
<resultMap id="BaseResultMap" type="TeacherT" >
    <id column="teacher_ID" property="teacherId" jdbcType="INTEGER" />
    <result column="teacher_NAME" property="teacherName" jdbcType="VARCHAR" />
    <result column="teacher_BIRTHDAY" property="teacherBirthday" jdbcType="DATE" />
    <result column="xi_id" property="xiId" jdbcType="INTEGER" />
    <result column="sex_ID" property="sexId" jdbcType="INTEGER" />
    <result column="title_ID" property="titleId" jdbcType="INTEGER" />
    <result column="teacher_WAGE" property="teacherWage" jdbcType="INTEGER" />
    <result column="teacher_PARTY" property="teacherParty" jdbcType="INTEGER" />
    <result column="teacher_PASSWORD" property="teacherPassword" jdbcType="VARCHAR" />
    <result column="teacher_EMAIL" property="teacherEmail" jdbcType="VARCHAR" />
  </resultMap>
 
 <select id="selectByDepartmentId" resultMap="BaseResultMap" parameterType="java.lang.String">
 	select * from teacher_t where department_id = #{id}
 </select>
</mapper>
teahcerMapper.java: 

public interface TeacherMapper {
	public List<TeacherT> selectByDepartmentId(String id);
}
写到这基本上算是写完了，现在写一下Servlet验证一下： 

public void teachersByDepartment(){
		SqlSession session = ColinSqlSessionFactory.getSqlSessionFactory().openSession();
		DepartmentTMapper departmentMapper = session.getMapper(DepartmentTMapper.class);
		List<DepartmentExt> list = departmentMapper.selectDepartment();
		for(DepartmentExt de : list){
			System.out.println("========"+de.getDepartmentName());
			for(TeacherT t:de.getTlist()){
				if(de.getTlist()!=null)
				System.out.println(t.getTeacherName());
			}
		}
	}

可以看到，只需要调用DepartmentMapper里的抽象方法就可以获取所有信息了

多对一（多个老师对应一个系）：

首先bean前两个类型是一样的，我们需要新建一个扩展的bean在继承teacherbean的同时具有departMent对象属性

public class TeacherandDepartment extends TeacherT {
	/**
	 * 
	 */
	private static final long serialVersionUID = -7128097219788502461L;
	public DepartmentT departmentT;

	public DepartmentT getDepartmentT() {
		return departmentT;
	}

	public void setDepartmentT(DepartmentT departmentT) {
		this.departmentT = departmentT;
	}
}
配置teacherMapper.xml文件，在新写的resultmap可以看出继承了之前的teacher属性并在此基础上添加了 association标签property对应的是新的bean类里面放置的department对象属性，javatype对应的属性类型，column对应的关联属性，也就是department_id，select对应的departmentMapper里面的查询方法 
<?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只能写一个 -->
<mapper namespace="com.web.map.TeacherMapper">
<resultMap id="BaseResultMap" type="TeacherT" >
    <id column="teacher_ID" property="teacherId" jdbcType="INTEGER" />
    <result column="teacher_NAME" property="teacherName" jdbcType="VARCHAR" />
    <result column="teacher_BIRTHDAY" property="teacherBirthday" jdbcType="DATE" />
    <result column="xi_id" property="xiId" jdbcType="INTEGER" />
    <result column="sex_ID" property="sexId" jdbcType="INTEGER" />
    <result column="title_ID" property="titleId" jdbcType="INTEGER" />
    <result column="teacher_WAGE" property="teacherWage" jdbcType="INTEGER" />
    <result column="teacher_PARTY" property="teacherParty" jdbcType="INTEGER" />
    <result column="teacher_PASSWORD" property="teacherPassword" jdbcType="VARCHAR" />
    <result column="teacher_EMAIL" property="teacherEmail" jdbcType="VARCHAR" />
  </resultMap>
 <resultMap type="TeacherandDepartment" id="teacherdepartmentmap" extends="BaseResultMap">
 	<association property="departmentT" javaType="departmentT" column="department_id" select="com.web.map.DepartmentTMapper.selectByTeacherTb">
 		<id column="department_id" property="departmentId" jdbcType="CHAR" />
	    <result column="department_name" property="departmentName" jdbcType="CHAR" />
	    <result column="department_director_id" property="departmentDirectorId" jdbcType="CHAR" />
 	</association>
 </resultMap>
 
 <select id="selectteacher" resultMap="teacherdepartmentmap">
 	select * from teacher_t
 </select>
 
</mapper>
departmentmapper.xml同样paramenterType参数类型就是column关联的类型，我这个表在创建的时候id是string类型的 

<?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.web.map.DepartmentTMapper" >
<cache/>
  <resultMap id="BaseResultMap" type="departmentT" >
    	<id column="department_id" property="departmentId" jdbcType="CHAR" />
	    <result column="department_name" property="departmentName" jdbcType="CHAR" />
	    <result column="department_director_id" property="departmentDirectorId" jdbcType="CHAR" />
  </resultMap>
  

 <select id="selectByTeacherTb" resultMap="BaseResultMap" parameterType="java.lang.String">
 select * from department_t where department_id = #{id}
 </select>

</mapper>

teacherMapper.java 

public interface TeacherMapper {
	
	public List<TeacherandDepartment> selectteacher();
	
}
departmentMapper.java 

public interface DepartmentTMapper {
	public List<DepartmentT> selectByTeacherTb();
    
}
ok到这里就基本完成了，Servlet测试 

public void teacherAndDepartment(){
		SqlSession session = ColinSqlSessionFactory.getSqlSessionFactory().openSession();
		TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
		List<TeacherandDepartment> list = studentMapper.selectteacher();
		for(TeacherandDepartment t : list){
			System.out.println(t.getTeacherName());
			System.out.println(t.getDepartmentT().getDepartmentName());
		}
	}
可以看到结果都出来了

标签：

相关文章