沒看過(guò)先看前兩篇

1. ssm入門
2. ssm框架進(jìn)階-----數(shù)據(jù)庫(kù)增刪改查

正文

一. 查詢雙表

背景介紹：通過(guò)對(duì)科目id的查詢，查詢出所對(duì)應(yīng)科目的成員

1.在test數(shù)據(jù)庫(kù)中新建表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 語(yǔ)句 如圖【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.運(yùn)行測(cè)試

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

二.查詢?nèi)?/p>

背景介紹：通過(guò)對(duì)科目名稱的查詢，查詢出所對(duì)應(yīng)科目的成員

1.在test數(shù)據(jù)庫(kù)中新建表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 語(yǔ)句

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>

注：因?yàn)閟ubject中的name和User中的name重復(fù)需要設(shè)置別名。

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.運(yùn)行測(cè)試

http://localhost:8080/user/findUserBySubjectName?name=語(yǔ)文