Mybatis表关系实现总结

朱雀 2024-04-18 06:52 66阅读 0赞

### 一、一对一 ###

前提：mybatis入门项目搭建完成（[https://blog.csdn.net/chlei\_/article/details/100187907][https_blog.csdn.net_chlei_article_details_100187907]）。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70][]

表格：

![20190903155852892.png][]

![20190903155822828.png][]

实体类：

public class Scores implements Serializable {
        private Integer id;
        private Integer score;
        private Integer sid;
        //从表实体类应该包含一个主表实体类的对象引用
        private Student student;
    
        public Student getStudent() {
            return student;
        }
    
        public void setStudent(Student student) {
            this.student = student;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getScore() {
            return score;
        }
    
        public void setScore(Integer score) {
            this.score = score;
        }
    
        public Integer getSid() {
            return sid;
        }
    
        public void setSid(Integer sid) {
            this.sid = sid;
        }
    
        @Override
        public String toString() {
            return "Scores{" +
                    "id=" + id +
                    ", score=" + score +
                    ", sid=" + sid +
                    '}';
        }
    }

public class Student implements Serializable {
        private Integer id;
        private String name;
        private String sex;
        private Date birthday;
    
        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 Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", birthday=" + birthday +
                    '}';
        }
    }

接口：

public interface ScoresDao {
    
        /**
         * 查询所有分数
         * @return
         */
        List<Scores> findAll();
    }

配置ScoresDao.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.chenlei.dao.ScoresDao">
        
        <resultMap id="StudentScores" type="com.chenlei.domain.Scores">
            <id property="id" column="scId"></id>
            <result property="sid" column="sid"></result>
            <result property="score" column="score"></result>
            
            <association property="student" column="sid" javaType="com.chenlei.domain.Student">
                <id property="id" column="id"></id>
                <result property="name" column="name"></result>
                <result property="sex" column="sex"></result>
                <result property="birthday" column="birthday"></result>
            </association>
        </resultMap>
        
    
        
        <select id="findAll" resultMap="StudentScores">
            select st.*,sc.id as scId ,sc.sid,sc.score	from scores sc ,student st where sc.sid=st.id;
        </select>
    </mapper>

测试类：

@Test
        public void findAll() {
            //执行查询所有对象方法
            List<Scores> scores = scoresDao.findAll();
            for (Scores scorez : scores) {
                System.out.println("-------------");
                System.out.println(scorez);
                System.out.println(scorez.getStudent());
            }
        }

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 1][]

### 二、一对多 ###

实体类：

public class Student implements Serializable {
        private Integer id;
        private String name;
        private String sex;
        private Date birthday;
        //一对多的关系映射：主表实体应该包含从表实体的集合引用
        private List<Scores> scores;
    
        public List<Scores> getScores() {
            return scores;
        }
    
        public void setScores(List<Scores> scores) {
            this.scores = scores;
        }
    
        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 Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", birthday=" + birthday +
                    '}';
        }
    }

public class Scores implements Serializable {
        private Integer id;
        private Integer score;
        private Integer sid;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getScore() {
            return score;
        }
    
        public void setScore(Integer score) {
            this.score = score;
        }
    
        public Integer getSid() {
            return sid;
        }
    
        public void setSid(Integer sid) {
            this.sid = sid;
        }
    
        @Override
        public String toString() {
            return "Scores{" +
                    "id=" + id +
                    ", score=" + score +
                    ", sid=" + sid +
                    '}';
        }
    }

StudentDao.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.chenlei.dao.StudentDao">
        
        <resultMap id="studentScoresMap" type="com.chenlei.domain.Student">
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
            
            <collection property="scores" ofType="com.chenlei.domain.Scores">
                <id property="id" column="scid"></id>
                <result property="sid" column="sid"></result>
                <result property="score" column="score"></result>
            </collection>
    
        </resultMap>
    
        
        <select id="findAll" resultMap="studentScoresMap">
            select * from student st left outer join scores sc on sc.sid = st.id
        </select>
    </mapper>

接口：

public interface StudentDao {
        /**
         * 查找所有操作
         * @return
         */
        List<Student> findAll();
    }

测试类：

@Test
        public void findAll() {
            //执行查询所有对象方法
            List<Student> students = studentDao.findAll();
            for (Student student : students) {
                System.out.println(student);
                System.out.println(student.getScores());
            }
        }

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 2][]

### 三、多对多查询 ###

数据库表格：

![20190903195840904.png][]

![20190903195849396.png][]

![20190903195901268.png][]

实体类：

public class Teacher implements Serializable {
        private Integer id;
        private String name;
        private List<Student> students;
    
        public List<Student> getStudents() {
            return students;
        }
    
        public void setStudents(List<Student> students) {
            this.students = students;
        }
    
        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;
        }
    
        @Override
        public String toString() {
            return "Teacher{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    '}';
        }
    }

接口：

public interface TeacherDao {
    
        /**
         * 查询所有分数
         * @return
         */
        List<Teacher> findAll();
    
    }

TeacherDao.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.chenlei.dao.TeacherDao">
        
        <resultMap id="teacherMap" type="com.chenlei.domain.Teacher">
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
            <collection property="students" ofType="com.chenlei.domain.Student">
                <id property="id" column="id"></id>
                <id property="name" column="name"></id>
                <id property="sex" column="sex"></id>
                <id property="birthday" column="birthday"></id>
            </collection>
        </resultMap>
    
        
        <select id="findAll" resultMap="teacherMap">
            select s.*,t.id as tid, t.name as tname from teacher t left outer join student_teacher st on t.id = st.tid left outer join student s on s.id = st.sid
        </select>
    </mapper>

测试类：

@Test
        public void findAll() {
            //执行查询所有对象方法
            List<Teacher> teachers = teacherDao.findAll();
            for (Teacher teacher: teachers) {
                System.out.println("........................");
                System.out.println(teacher);
                System.out.println(teacher.getStudent());
            }
        }

最后在多表查询的时候自己也是碰到空指针异常问题，这里记录提示一下：

![20190903200741108.png][]

大家可以看到如果在teache表中增加一条记录如果没有对应的student值那么这个时候查询，就会报空指针异常了，所以总结就是：参照查询表一定得有对应的值。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 3][]

这个时候查询就来就没问题了。

这里顺带提一下别名，简便我们代码（在SqlMapConfig.xml文件中配置）：

![20190904112216408.png][]

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 4][]

注解实现多表链接：[https://blog.csdn.net/chlei\_/article/details/100538780][https_blog.csdn.net_chlei_article_details_100538780]

[https_blog.csdn.net_chlei_article_details_100187907]: https://blog.csdn.net/chlei_/article/details/100187907
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/2a5a5ab137a8429a92417c330c4c3ef3.png
[20190903155852892.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a0547026902a495d8fa1a60f505fd0d4.png
[20190903155822828.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/101138b984c541168d6c9e35a27bd116.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/36e5d24f14204a3783800e4bfb5d2f33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/34c3bc2175ee4108b38d2f5711e9e004.png
[20190903195840904.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/b6ff4cf2e31b4d25b38226be2ce7a8ca.png
[20190903195849396.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/48685abddf394538b82e876ffbcae18b.png
[20190903195901268.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8825dd50259f46e2b2a6882ba53f7c7b.png
[20190903200741108.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/5670016e87c143df8310cdb477a50f8d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8083e6caf7e7489789981b85458a9e7b.png
[20190904112216408.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/bd4e8621a614494499348f2ebcf7f8bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/c1e8dc49a85444c79b19f05c909846fa.png
[https_blog.csdn.net_chlei_article_details_100538780]: https://blog.csdn.net/chlei_/article/details/100538780