04—mybatis的关联映射

2023-07-31 11:42:16

mybatis的关联映射
一对一
一对多
多对多

一.一对一（一个人只能有一个身份证号）

1.创建表
创建表tb_card

CREATE TABLE `tb_card` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`code` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

)

INSERT INTO tb_card(CODE)VALUES('150421178702260614');

创建表tb_person

CREATE TABLE `tb_person` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(20) DEFAULT NULL,

`sex` VARCHAR(20) DEFAULT NULL,

`age` INT(11) DEFAULT NULL,

`card_id` INT(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `card_id` (`card_id`),

CONSTRAINT `tb_person_idfk` FOREIGN KEY (`card_id`) REFERENCES `tb_card` (`id`)

)

INSERT INTO tb_person(NAME,sex,age,card_id)VALUES('jack','男',23,1);

2.创建model

Card.java

package org.fkit.domain;

import java.io.Serializable;

public class Card implements Serializable {

    private Integer id;  // 主键id

    private String code; // 身份证编号

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getCode() {

        return code;

    }

    public void setCode(String code) {

        this.code = code;

    }

    @Override

    public String toString() {

        return "Card [id=" + id + ", code=" + code + "]";

    }

}

Person.java

package org.fkit.domain;

import java.io.Serializable;

public class Person implements Serializable {

    private Integer id;  // 主键id

    private String name; // 姓名

    private String sex;  // 性别

    private Integer age; // 年龄

    // 人和身份证是一对一的关系，即一个人只有一个身份证

    private Card card;

    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 getSex() {

        return sex;

    }

    public void setSex(String sex) {

        this.sex = sex;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    public Card getCard() {

        return card;

    }

    public void setCard(Card card) {

        this.card = card;

    }

    @Override

    public String toString() {

        return "Person [id=" + id + ", name=" + name + ", sex=" + sex

                + ", age=" + age + "]";

    }

}

3.Mapper类和xml文件
PersonMapper .java

package org.fkit.mapper;

import org.fkit.domain.Person;

public interface PersonMapper {

    /**

     * 根据id查询Person

     * 方法名和参数必须和XML文件中的<select.../>元素的id属性和parameterType属性一致

     * @param id

     * @return Person对象

     * */

    Person selectPersonById(Integer id);

}

PersonMapper .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">

<!-- namespace指用户自定义的命名空间。 -->

<mapper namespace="org.fkit.mapper.PersonMapper">

<!-- 根据id查询Person，返回resultMap -->

  <select id="selectPersonById" parameterType="int"

      resultMap="personMapper">

      SELECT * from tb_person where id = #{id}

  </select>

 <!-- 映射Peson对象的resultMap -->

    <resultMap type="org.fkit.domain.Person" id="personMapper">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="sex" column="sex"/>

        <result property="age" column="age"/>

                <!-- 下边完成关联信息的映射：association -->

        <association property="card" column="card_id"

        select="org.fkit.mapper.CardMapper.selectCardById"

        javaType="org.fkit.domain.Card"/>

    </resultMap>

</mapper>

CardMapper.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">

<!-- namespace指用户自定义的命名空间。 -->

<mapper namespace="org.fkit.mapper.CardMapper">

    <!-- 根据id查询Card，返回Card对象 -->

  <select id="selectCardById" parameterType="int" resultType="org.fkit.domain.Card">

      SELECT * from tb_card where id = #{id}

  </select>

</mapper>

4.mybatis-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">

  <!--  XML 配置文件包含对 MyBatis 系统的核心设置 -->

<configuration>

    <!-- 指定 MyBatis 所用日志的具体实现 -->

    <settings>

        <setting name="logImpl" value="LOG4J"/>

    </settings>

    <environments default="mysql">

    <!-- 环境配置，即连接的数据库。 -->

    <environment id="mysql">

    <!--  指定事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->

      <transactionManager type="JDBC"/>

      <!--  dataSource指数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->

      <dataSource type="POOLED">

        <property name="driver" value="com.mysql.jdbc.Driver"/>

        <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis"/>

        <property name="username" value="root"/>

        <property name="password" value="123456"/>

      </dataSource>

    </environment>

  </environments>

  <!-- mappers告诉了MyBatis去哪里找持久化类的映射文件 -->

  <mappers>

      <mapper resource="org/fkit/mapper/CardMapper.xml"/>

      <mapper resource="org/fkit/mapper/PersonMapper.xml"/>

  </mappers>

</configuration>

log4j.properties

# Global logging configuration

log4j.rootLogger=ERROR, stdout

# MyBatis logging configuration...

log4j.logger.org.fkit.mapper.PersonMapper=DEBUG

log4j.logger.org.fkit.mapper.CardMapper=DEBUG

# Console output...

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

5.测试

package org.fkit.test;

import java.io.IOException;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.fkit.domain.Person;

import org.fkit.mapper.PersonMapper;

public class mytest {

    public static void main(String[] args) throws IOException {

        // 读取mybatis-config.xml文件

        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        // 初始化mybatis，创建SqlSessionFactory类的实例

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        // 创建Session实例

        SqlSession session = sqlSessionFactory.openSession();

        // 获得mapper接口的代理对象

        PersonMapper pm = session.getMapper(PersonMapper.class);

        // 直接调用接口的方法，查询id为1的Peson数据

        Person p = pm.selectPersonById(1);

        // 打印Peson对象

        System.out.println(p);

        // 打印Person对象关联的Card对象

        System.out.println(p.getCard());

        // 提交事务

        session.commit();

        // 关闭Session

        session.close();

    }

}

二.一对多（一个班级有多个学生）

1.创建表

创建表tb_clazz

CREATE TABLE tb_clazz(

id INT PRIMARY KEY AUTO_INCREMENT,

CODE VARCHAR(18),

NAME VARCHAR(18)

);

INSERT INTO tb_clazz(CODE,NAME) VALUES('2017001','Java双语幼儿园中班');

创建表tb_student

CREATE TABLE tb_student(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(18),

sex VARCHAR(18),

age INT,

clazz_id INT,

FOREIGN KEY (clazz_id) REFERENCES tb_clazz(id)

);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('aa','男',23,1);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('bb','女',18,1);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('cc','男',21,1);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('dd','女',20,1);

2.创建model
Clazz.java

package org.fkit.domain;

import java.io.Serializable;

import java.util.List;

public class Clazz implements Serializable {

    private Integer id; // 班级id，主键

    private String code; // 班级编号

    private String name; // 班级名称

    // 班级和学生是一对多的关系，即一个班级可以有多个学生

    private List<Student> students;

    public Clazz() {

        super();

    }

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getCode() {

        return code;

    }

    public void setCode(String code) {

        this.code = code;

    }

    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 "Clazz [id=" + id + ", code=" + code + ", name=" + name + "]";

    }

}

Student.java

package org.fkit.domain;

import java.io.Serializable;

public class Student implements Serializable {

    private Integer id; // 学生id，主键

    private String name; // 姓名

    private String sex;  // 性别

    private Integer age; // 年龄

    // 学生和班级是多对一的关系，即一个学生只属于一个班级

    private Clazz clazz;

    public Student() {

        super();

    }

    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 getSex() {

        return sex;

    }

    public void setSex(String sex) {

        this.sex = sex;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    public Clazz getClazz() {

        return clazz;

    }

    public void setClazz(Clazz clazz) {

        this.clazz = clazz;

    }

    @Override

    public String toString() {

        return "Student [id=" + id + ", name=" + name + ", sex=" + sex

                + ", age=" + age + "]";

    }

}

3.Mapper类和xml文件
ClazzMapper.java

package org.fkit.mapper;

import org.fkit.domain.Clazz;

public interface ClazzMapper {

    // 根据id查询班级信息

    Clazz selectClazzById(Integer id);

}
StudentMapper.java
package org.fkit.mapper;

import org.fkit.domain.Student;

public interface StudentMapper {

    // 根据id查询学生信息

    Student selectStudentById(Integer id);

}

ClazzMapper.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">

<!-- namespace指用户自定义的命名空间。 -->

<mapper namespace="org.fkit.mapper.ClazzMapper">

    <!-- 根据id查询班级信息，返回resultMap -->

      <select id="selectClazzById" parameterType="int" resultMap="clazzResultMap">

          SELECT * FROM tb_clazz  WHERE id = #{id}

      </select>

       <!-- 映射Clazz对象的resultMap -->

    <resultMap type="org.fkit.domain.Clazz" id="clazzResultMap">

        <id property="id" column="id"/>

        <result property="code" column="code"/>

        <result property="name" column="name"/>

        <!-- 一对多关联映射:collection fetchType="lazy"表示懒加载  -->

        <collection property="students" javaType="ArrayList"

      column="id" ofType="org.fkit.domain.Student"

      select="org.fkit.mapper.StudentMapper.selectStudentByClazzId"

      fetchType="lazy">

          <id property="id" column="id"/>

          <result property="name" column="name"/>

          <result property="sex" column="sex"/>

          <result property="age" column="age"/>

        </collection>

    </resultMap>

</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">

<!-- namespace指用户自定义的命名空间。 -->

<mapper namespace="org.fkit.mapper.StudentMapper">

  <!-- 根据id查询学生信息，多表连接，返回resultMap -->

  <select id="selectStudentById" parameterType="int" resultMap="studentResultMap">

      SELECT * FROM tb_clazz c,tb_student s

      WHERE c.id = s.clazz_id

       AND s.id = #{id}

  </select>

  <!-- 根据班级id查询学生信息，返回resultMap -->

  <select id="selectStudentByClazzId" parameterType="int"

  resultMap="studentResultMap">

      SELECT * FROM tb_student WHERE clazz_id = #{id}

  </select>

   <!-- 映射Student对象的resultMap -->

    <resultMap type="org.fkit.domain.Student" id="studentResultMap">

        <id property="id" column="id"/>

          <result property="name" column="name"/>

          <result property="sex" column="sex"/>

          <result property="age" column="age"/>

        <!-- 多对一关联映射:association   -->

        <association property="clazz" javaType="org.fkit.domain.Clazz">

            <id property="id" column="id"/>

            <result property="code" column="code"/>

            <result property="name" column="name"/>

        </association>

    </resultMap>

</mapper>

4.mybatis-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">

  <!--  XML 配置文件包含对 MyBatis 系统的核心设置 -->

<configuration>

    <!-- 指定 MyBatis 所用日志的具体实现 -->

    <settings>

        <setting name="logImpl" value="LOG4J"/>

        <!-- 要使延迟加载生效必须配置下面两个属性 -->

        <setting name="lazyLoadingEnabled" value="true"/>

        <setting name="aggressiveLazyLoading" value="false"/>

    </settings>

    <environments default="mysql">

    <!-- 环境配置，即连接的数据库。 -->

    <environment id="mysql">

    <!--  指定事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->

      <transactionManager type="JDBC"/>

      <!--  dataSource指数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->

      <dataSource type="POOLED">

        <property name="driver" value="com.mysql.jdbc.Driver"/>

        <property name="url" value="jdbc:mysql://127.0.0.1:3306/mytestdb"/>

        <property name="username" value="root"/>

        <property name="password" value="root"/>

      </dataSource>

    </environment>

  </environments>

  <!-- mappers告诉了MyBatis去哪里找持久化类的映射文件 -->

  <mappers>

      <mapper resource="org/fkit/mapper/ClazzMapper.xml"/>

      <mapper resource="org/fkit/mapper/StudentMapper.xml"/>

  </mappers>

</configuration>

log4j.properties

# Global logging configuration

log4j.rootLogger=ERROR, stdout

# MyBatis logging configuration...

log4j.logger.org.fkit.mapper.ClazzMapper=DEBUG

log4j.logger.org.fkit.mapper.StudentMapper=DEBUG

# Console output...

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

5.测试

package org.fkit.test;

import java.io.InputStream;

import java.util.List;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.fkit.domain.Clazz;

import org.fkit.domain.Student;

import org.fkit.mapper.ClazzMapper;

import org.fkit.mapper.StudentMapper;

public class OneToManyTest {

    public static void main(String[] args) throws Exception {

        // 读取mybatis-config.xml文件

        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        // 初始化mybatis，创建SqlSessionFactory类的实例

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()

                .build(inputStream);

        // 创建Session实例

        SqlSession session = sqlSessionFactory.openSession();

        OneToManyTest t = new OneToManyTest();

        t.testSelectClazzById(session);

//        t.testSelectStudentById(session);

        // 提交事务

        session.commit();

        // 关闭Session

        session.close();

    }

    // 测试一对多，查询班级Clazz（一）的时候级联查询学生Student（多）

    public void testSelectClazzById(SqlSession session){

        // 获得ClazzMapper接口的代理对象

        ClazzMapper cm = session.getMapper(ClazzMapper.class);

        // 调用selectClazzById方法

        Clazz clazz = cm.selectClazzById(1);

        // 查看查询到的clazz对象信息

        System.out.println(clazz.getId() + " "+ clazz.getCode() + " "+clazz.getName());

        // 查看clazz对象关联的学生信息

        List<Student> students = clazz.getStudents();

        for(Student stu : students){

            System.out.println(stu);

        }

    }

    // 测试多对一，查询学生Student（多）的时候级联查询 班级Clazz（一）

    public void testSelectStudentById(SqlSession session){

        // 获得StudentMapper接口的代理对象

        StudentMapper sm = session.getMapper(StudentMapper.class);

        // 调用selectStudentById方法

        Student stu = sm.selectStudentById(1);

        // 查看查询到的Student对象信息

        System.out.println(stu);

        // 查看Student对象关联的班级信息

        System.out.println(stu.getClazz());

    }

}

三.多对多（学生与课程的关系，学生可以学多门课程，课程同样也可以有多个学生）

1.创建表

CREATE TABLE `t_student` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `student_name` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

)

INSERT INTO t_student (student_name)VALUES ('小米');

INSERT INTO t_student (student_name)VALUES ('小王');

INSERT INTO t_student (student_name)VALUES ('小李');

CREATE TABLE `t_courses` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `courses_name` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

)

INSERT INTO t_courses (courses_name)VALUES ('java');

INSERT INTO t_courses (courses_name)VALUES ('c#');

INSERT INTO t_courses (courses_name)VALUES ('php');

INSERT INTO t_courses (courses_name)VALUES ('vb');

INSERT INTO t_courses (courses_name)VALUES ('javascript');

CREATE TABLE `t_stu_cou` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `stu_id` int(11) DEFAULT NULL,

  `cou_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `stu_id` (`stu_id`),

  KEY `cou_id` (`cou_id`),

  CONSTRAINT `t_stu_cou_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `t_student` (`id`),

  CONSTRAINT `t_stu_cou_ibfk_2` FOREIGN KEY (`cou_id`) REFERENCES `t_courses` (`id`)

)  

INSERT INTO t_stu_cou (stu_id,cou_id)VALUES (1,1);

INSERT INTO t_stu_cou (stu_id,cou_id)VALUES (1,2);

INSERT INTO t_stu_cou (stu_id,cou_id)VALUES (1,3);

INSERT INTO t_stu_cou (stu_id,cou_id)VALUES (2,2);

INSERT INTO t_stu_cou (stu_id,cou_id)VALUES (2,3);

INSERT INTO t_stu_cou (stu_id,cou_id)VALUES (2,4);

2.创建model
CoursesBean.java

package org.fkit.domain;

import java.io.Serializable;

import java.util.List;

public class CoursesBean implements Serializable{

    private static final long serialVersionUID = 1L;

    private Integer id;

    private String name;

   // 使用 List<StudentBean>集合，是说明学习这门课程的所有学生

    private List<StudentBean> student;

    public CoursesBean() {

        super();

    }

    public CoursesBean(Integer id, String name, List<StudentBean> student) {

        super();

        this.id = id;

        this.name = name;

        this.student = student;

    }

    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<StudentBean> getStudent() {

        return student;

    }

    public void setStudent(List<StudentBean> student) {

        this.student = student;

    }

    @Override

    public String toString() {

        return "CoursesBean [id=" + id + ", name=" + name + ", student="

                + student + "]";

    }

}

StudentBean.java

package org.fkit.domain;

import java.io.Serializable;

import java.util.List;

public class StudentBean implements Serializable{

    private static final long serialVersionUID = 1L;

    private Integer id;

    private String name;

    private List<CoursesBean> courses;

    public StudentBean() {

        super();

        // TODO Auto-generated constructor stub

    }

    public StudentBean(Integer id, String name, List<CoursesBean> courses) {

        super();

        this.id = id;

        this.name = name;

        this.courses = courses;

    }

    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<CoursesBean> getCourses() {

        return courses;

    }

    public void setCourses(List<CoursesBean> courses) {

        this.courses = courses;

    }

    @Override

    public String toString() {

        return "StudentBean [id=" + id + ", name=" + name + ", courses="

                + courses + "]";

    }

}

3.Mapper类和xml文件

CoursesMapper.java

package org.fkit.mapper;

import org.fkit.domain.CoursesBean;

public interface CoursesMapper {

    /**

     * 根据id查询课程

     * @param id

     * @return

     */

    public CoursesBean findCouById(int id);

    /**

     * 要求查课时，将选课的学生一并查出

     * @param id

     * @return

     */

    public CoursesBean findCouAndStu(int id);

}

CoursesMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.fkit.mapper.CoursesMapper">

 <resultMap type="CoursesBean" id="coursesMap">

   <!--   在默认情况下，mybatis会自动在TypeAliasRegistry初始化的时候挂在很多jdk常用类，

                          所以javaType="java.lang.Integer"可以写成javaType="Integer"-->

    <id property="id" column="id" javaType="java.lang.Integer"/>

    <result property="name" column="courses_name" javaType="java.lang.String"/>

 </resultMap>

 <resultMap type="CoursesBean" id="couAndStu">

    <id property="id" column="id" javaType="java.lang.Integer"/>

    <result property="name" column="courses_name" javaType="java.lang.String"/>

    <!-- 对于一个属性的类型是一个集合，就使用collection

                      对于一个属性的类型是一个类，就使用association   -->

    <collection property="student" column="id" select="findStudentByCourses"></collection>

 </resultMap>

 <select id="findCouById" resultMap="coursesMap">

  select * from t_courses where id=#{id}

 </select>

 <!-- 有学生表，课程表这两张表都没有外键，我们就要使用第三张关联表。我们就要根据课程表的fk_cou_id，把学生的id值得到。

               对于多对多那么这个学生的id值就不可能是一个值 。在数据库里就要使用in-->

 <select id="findStudentByCourses" resultMap="org.fkit.mapper.StudentMapper.studentMap">

    select * from t_student where id in (select stu_id from t_stu_cou where cou_id=#{id})

 </select>

 <select id="findCouAndStu" resultMap="couAndStu">

 select * from t_courses where id=#{id}

 </select>

</mapper>

StudentMapper.java

package org.fkit.mapper;

import org.fkit.domain.StudentBean;

public interface StudentMapper {

    /**

     * 根据id值查询学生信息

     * @param id

     * @return

     */

    public StudentBean findStuById(int id);

    /**

     * 要求查询学生时，将学生选择的课程查出

     * @param id

     * @return

     */

    public StudentBean findStuAndCou(int id);

}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.fkit.mapper.StudentMapper">

<resultMap type="StudentBean" id="studentMap">

    <id property="id" column="id" javaType="java.lang.Integer"/>

    <result property="name" column="student_name" javaType="java.lang.String"/>

</resultMap>

<resultMap type="StudentBean" id="studentAndCourses">

     <id property="id" column="id" javaType="java.lang.Integer"/>

     <result property="name" column="student_name" javaType="java.lang.String"/>

     <collection property="courses" column="id" select="findCoursesByStudent"></collection>

</resultMap>

    <select id="findStuById" resultMap="studentMap">

        select * from t_student where id = #{id}

    </select>

    <select id="findStuAndCou" resultMap="studentAndCourses">

        select * from t_student where id = #{id}

    </select>

    <select id="findCoursesByStudent" resultMap="org.fkit.mapper.CoursesMapper.coursesMap">

        select * from t_courses where id in (select cou_id from t_stu_cou where stu_id = #{id})

    </select>

</mapper>

4.mybatis-config.xml文件

log4j.properties

# Global logging configuration

log4j.rootLogger=ERROR, stdout

# MyBatis logging configuration...

log4j.logger.org.fkit.mapper.PersonMapper=DEBUG

log4j.logger.org.fkit.mapper.CardMapper=DEBUG

# Console output...

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

db.properties

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://127.0.0.1:3306/mybatis

username=root

password=123456

mybatis-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">

<!-- XML 配置文件包含对 MyBatis 系统的核心设置 -->

<configuration>

    <properties resource="db.properties" />

    <!-- 指定 MyBatis 所用日志的具体实现 -->

    <settings>

        <setting name="logImpl" value="LOG4J" />

    </settings>

    <!-- 为JAVA Bean起类别名 -->

    <typeAliases>

        <package name="org.fkit.domain" />

    </typeAliases>

    <environments default="mysql">

        <!-- 环境配置，即连接的数据库。 -->

        <environment id="mysql">

            <!-- 指定事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->

            <transactionManager type="JDBC" />

            <!-- dataSource指数据源配置，POOLED是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告诉了MyBatis去哪里找持久化类的映射文件 -->

    <mappers>

        <mapper resource="org/fkit/mapper/CoursesMapper.xml" />

        <mapper resource="org/fkit/mapper/StudentMapper.xml" />

    </mappers>

</configuration>

5.测试

package org.fkit.test;

import java.io.IOException;

import java.io.InputStream;

import java.util.List;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.fkit.domain.CoursesBean;

import org.fkit.domain.StudentBean;

import org.fkit.mapper.CoursesMapper;

import org.fkit.mapper.StudentMapper;

public class ManyToManyTest {

    public static void main(String[] args) throws IOException {

        // 读取mybatis-config.xml文件

        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        // 初始化mybatis，创建SqlSessionFactory类的实例

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()

                .build(inputStream);

        // 创建Session实例

        SqlSession session = sqlSessionFactory.openSession();

        ManyToManyTest t = new ManyToManyTest();

        // 根据用户id查询用户，测试一对多

        //t.testSelectCoursesById(session);

        // 根据订单id查询订单，测试多对多

        t.testSelectStudentById(session);

        // 提交事务

        session.commit();

        // 关闭Session

        session.close();

    }

    public void testSelectStudentById(SqlSession session){

        // 获得UserMapper接口的代理对象

        StudentMapper sm = session.getMapper(StudentMapper.class);

        // 调用selectUserById方法

        StudentBean stu = sm.findStuAndCou(1);

        System.out.println(stu);

        // 查看查询到的user对象信息

        System.out.println(stu.getId() + " " + stu.getName());

        // 查看user对象关联的订单信息

        List<CoursesBean> CoursesList = stu.getCourses();

        for(CoursesBean item : CoursesList){

            System.out.println(item);

        }

    }

    // 测试多对多，查询订单Order（多）的时候级联查询订单的商品Article（多）

    public void testSelectCoursesById(SqlSession session){

        // 获得OrderMapper接口的代理对象

        CoursesMapper cm = session.getMapper(CoursesMapper.class);

        // 调用selectOrderById方法

        CoursesBean course = cm.findCouAndStu(2);

        System.out.println(course);

        // 查看查询到的order对象信息

        System.out.println(course.getId() + " " + course.getName());

        // 查看order对象关联的商品信息

        List<StudentBean> studentlist = course.getStudent();

        for(StudentBean stu : studentlist){

            System.out.println(stu);

        }

    }

}
上一篇：mybatis 一对一关联映射实例


下一篇：Mybatis的关联映射