mybatis一对多关系表

2021-12-03
3

一、创建JavaProject

二、导入相关jar包


三、编写配置文件mybatis-comfig.xml

  1. 在src目录下新建一个mybatis-comfig.xml文件
  2. 编写数据库配置文件db.properties
    driver=com.mysql.jdbc.Driver
    url=jdbc\:mysql\://localhost\:3306/test?characterEncoding\=utf8
    username=root
    password=123456
    

  3. <?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>
    	<!-- 读取配置文件db.properties中的数据库连接参数 -->
    	<properties resource="db.properties"/>
    	<!-- 配置实体类的别名 -->
    	<typeAliases>
    		<!-- <typeAlias type="com.zscs.model.Company" alias="company"/> -->
    		<package name="com.zscs.model"/>
    	</typeAliases>
    	<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="${username}" />
    				<property name="password" value="${password}" />
    			</dataSource>
    		</environment>
    	</environments>
    	<mappers>
    		<mapper resource="com/zscs/model/ClassesMapper.xml"/>
    		<mapper resource="com/zscs/model/TeacherMapper.xml"/>
    	</mappers>
    </configuration>
四、编写数据表对应的实体类Student,Classes
package com.zscs.model;


public class Student {
    private Integer sId;


    private String sName;
    private Integer classId;


<span style="white-space:pre">	</span>public Integer getClassId() {
<span style="white-space:pre">		</span>return classId;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public void setClassId(Integer classId) {
<span style="white-space:pre">		</span>this.classId = classId;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>private Classes classes;
    
<span style="white-space:pre">	</span>public Classes getClasses() {
<span style="white-space:pre">		</span>return classes;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public void setClasses(Classes classes) {
<span style="white-space:pre">		</span>this.classes = classes;
<span style="white-space:pre">	</span>}


<span style="white-space:pre">	</span>public Integer getsId() {
        return sId;
    }


    public void setsId(Integer sId) {
        this.sId = sId;
    }


    public String getsName() {
        return sName;
    }


    public void setsName(String sName) {
        this.sName = sName == null ? null : sName.trim();
    }
}
</pre><pre name="code" class="java">package com.zscs.model;

import java.util.List;

public class Classes {
    private Integer cId;
    private String cName;
    private Teacher teacher;
    private List<Student> students;
    public Teacher getTeacher() {
		return teacher;
	}

	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}

	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}

	public Integer getcId() {
        return cId;
    }

    public void setcId(Integer cId) {
        this.cId = cId;
    }

    public String getcName() {
        return cName;
    }

    public void setcName(String cName) {
        this.cName = cName == null ? null : cName.trim();
    }
}
五、编写上述实体类对应的映射文件StudentMapper.xml,ClassesMapper.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.zscs.model.StudentMapper" >
  <resultMap id="BaseResultMap" type="com.zscs.model.Student" >
    <id column="s_id" property="sId" jdbcType="INTEGER" />
    <result column="s_name" property="sName" jdbcType="VARCHAR" />
    <result column="class_id" property="classId" jdbcType="INTEGER" />
  </resultMap>
  <select id="getStudentById" parameterType="Integer" resultType="Student" resultMap="BaseResultMap">
  <span style="white-space:pre">	</span>select * from student where s_id=#{id}
  </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.zscs.model.ClassesMapper" >
  <resultMap id="BaseResultMap" type="Classes" >
    <id column="c_id" property="cId" jdbcType="INTEGER" />
    <result column="c_name" property="cName" jdbcType="VARCHAR" />
    <result column="teacher_id" property="teacherId" jdbcType="INTEGER" />
  </resultMap>
  <!-- 方法一:多对多联表查询 -->
  <resultMap type="Classes" id="MyClassMap">
  	<id property="cId" column="c_id"/>
  	<result property="cName" column="c_name"/>
  	<association property="teacher" javaType="Teacher">
  		<id column="t_id" property="tId" jdbcType="INTEGER" />
   	 	<result column="t_name" property="tName" jdbcType="VARCHAR" />
  	</association>
  	<collection property="students" ofType="Student">
  		<id column="s_id" property="sId" />
   	 	<result column="s_name" property="sName" />
    	<result column="class_id" property="classId" />
  	</collection>
  </resultMap>
  <select id="getClassById" parameterType="int" resultMap="MyClassMap">
  	SELECT * FROM class c,student s,teacher t WHERE c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=#{id}
  </select>
  
  <!-- 方法二:嵌套查询:通过执行另外一个 SQL 映射语句来返回预期的复杂类型   -->
  <select id="getStudents" parameterType="int" resultType="Student">
  	select s_id sId,s_name sName,class_id classId from student where class_id=#{id}
  </select>
  <select id="getTeacher" parameterType="int" resultType="Teacher">
  	select t_id tId,t_name tName from teacher where t_id=#{id}
  </select>
  <resultMap type="Classes" id="MyClassMap2">
  	<id property="cId" column="c_id"/>
  	<result property="cName" column="c_name"/>
  	<!-- association联合 -->
  	<association property="teacher" select="getTeacher" column="teacher_id"></association>
  	<!-- collection采集     -->
  	<collection property="students" select="getStudents" column="c_id"></collection>
  </resultMap>
  <select id="getClassById2" parameterType="int" resultMap="MyClassMap2">
  	SELECT * FROM class where c_id=#{id}
  </select>
</mapper>


六、编写测试类
package com.zscs.mytest;


import org.apache.ibatis.session.SqlSession;
import org.junit.Test;


import com.zscs.model.Classes;
import com.zscs.util.MyBatisUtil;


public class MyTest {
<span style="white-space:pre">	</span>@Test
<span style="white-space:pre">	</span>public void testWord(){
<span style="white-space:pre">		</span>SqlSession session = MyBatisUtil.getSession();
<span style="white-space:pre">		</span>String statement="com.zscs.model.ClassesMapper.getClassById2";
<span style="white-space:pre">		</span>//String statement="com.zscs.model.StudentMapper.getStudentById";
<span style="white-space:pre">		</span>Classes classes =session.selectOne(statement, 2);
<span style="white-space:pre">		</span>String students="";
<span style="white-space:pre">		</span>for(int i=0;i<classes.getStudents().size();i++){
<span style="white-space:pre">			</span>students+=classes.getStudents().get(i).getsName()+",";
<span style="white-space:pre">		</span>}
<span style="white-space:pre">		</span>System.out.println(classes.getcName()+"班上有"+classes.getStudents().size()+"个学生他们分别是"+students+"讲课老师是"+classes.getTeacher().gettName());
<span style="white-space:pre">	</span>}
}



评论