ssm框架进阶-----数据库多表查询

没看过先看前两篇

1. ssm入门
2. ssm框架进阶-----数据库增删改查

正文

一. 查询双表

背景介绍：通过对科目id的查询，查询出所对应科目的成员

1.在test数据库中新建表sub_user

02

2.在model中建立SubjectModel和SubjectModelOne

03
04

public class SubjectModel {
    private UserModel userModel;

    public UserModel getUserModel() {
        return userModel;
    }

    public void setUserModel(UserModel userModel) {
        this.userModel = userModel;
    }
}

public class SubjectModelOne {

    private List<UserModel> userModels;

    public List<UserModel> getUserModels() {
        return userModels;
    }

    public void setUserModels(List<UserModel> userModels) {
        this.userModels = userModels;
    }
}

3.在mapper的UserDao中编写sql 语句 如图【05】

05

注：共有两种写法，任选一种即可

    <resultMap id="SubjectUserMap" type="SubjectModel">
        <association property="userModel" javaType="UserModel"
                     select="getUserById" column="id">
            <result column="id" property="id"/>
            <result property="name" column="name"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>

    <resultMap id="SubjectUserMap2" type="SubjectModelOne">
        <collection property="userModels" ofType="UserModel"
        >
            <result column="id" property="id"/>
            <result property="name" column="name"/>
            <result column="age" property="age"/>
        </collection>

    </resultMap>

    <!--方式1-->
    <select id="findUserBySubjectId" resultMap="SubjectUserMap">
        SELECT * FROM sub_user WHERE subject_id=#{id}
    </select>

    <!--方式2-->
    <select id="findUserBySubjectId2" resultMap="SubjectUserMap2">
        SELECT * FROM sub_user s , user u WHERE u.id=s.user_id AND s.subject_id=#{id}
    </select>

4.在dao的UserDao中编写 如图【06】

06

     List<SubjectModel> findUserBySubjectId(int id);
    SubjectModelOne findUserBySubjectId2(int id);

5.在service的UserService中编写 如图【07】

07

    public List<SubjectModel> findUserBySubjectId(int id) {
        return userDao.findUserBySubjectId(id);
    }

    public SubjectModelOne findUserBySubjectId2(int id) {
        return userDao.findUserBySubjectId2(id);
    }

6.在controller的UserController中编写 如图【08】

08

    @RequestMapping(value = "findUserBySubjectId")
    public List<SubjectModel> findUserBySubjectId(int id) {
        return userService.findUserBySubjectId(id);
    }

    @RequestMapping(value = "findUserBySubjectId2")
    public SubjectModelOne findUserBySubjectId2(int id) {
        return userService.findUserBySubjectId2(id);
    }

7.运行测试

http://localhost:8080/user/findUserBySubjectId?id=1
http://localhost:8080/user/findUserBySubjectId2?id=1

二.查询三表

背景介绍：通过对科目名称的查询，查询出所对应科目的成员

1.在test数据库中新建表subject

01

2.在model中建立SubjectUserModel

02

   private String su_name;

    private List<SubjectModel> subjectModels;

    public String getName() {
        return su_name;
    }

    public void setName(String su_name) {
        this.su_name = su_name;
    }

    public List<SubjectModel> getSubjectModels() {
        return subjectModels;
    }

    public void setSubjectModels(List<SubjectModel> subjectModels) {
        this.subjectModels = subjectModels;
    }

3.在mapper的UserDao中编写sql 语句

03

    <resultMap id="SubjectUserNameMap" type="SubjectUserModel">
        <result column="name" property="su_name"/>
        <collection property="subjectModels" ofType="SubjectModel">
            <association property="userModel" javaType="UserModel">
                <result column="id" property="id"/>
                <result property="name" column="uname"/>
                <result column="age" property="age"/>
            </association>
        </collection>
    </resultMap>


    <select id="findUserBySubjectName" resultMap="SubjectUserNameMap">
        SELECT s.name,u.id,u.name uname,u.age FROM subject s , sub_user su, user u WHERE su.subject_id=s.id AND u.id=su.user_id AND s.name=#{name}
    </select>

注：因为subject中的name和User中的name重复需要设置别名。

4.在dao的UserDao中编写

04

    SubjectUserModel findUserBySubjectName(String name);

5.在service的UserService中编写

05

    public SubjectUserModel findUserBySubjectName(String name) {
        return userDao.findUserBySubjectName(name);
    }

6.在controller的UserController中编写

06

    @RequestMapping(value = "findUserBySubjectName")
    public SubjectUserModel findUserBySubjectName(String name) {
        return userService.findUserBySubjectName(name);
    }

7.运行测试

http://localhost:8080/user/findUserBySubjectName?name=语文