SpringBoot整合Mybatis

四、多对多实现

    目标：根据id查询用户选择的运营商

1. 构建项目

    使用上一章内容

2. 创建数据库

1). user表：

CREATE TABLE user( id INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号', name VARCHAR(50) NOT NULL COMMENT '用户名', PRIMARY KEY(id) )ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

2). combo表：

CREATE TABLE combo( id INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号', name VARCHAR(50) NOT NULL COMMENT '运营商名称', PRIMARY KEY(id) )ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

3). user_combo表：

CREATE TABLE user_combo( uid INT(10) NULL COMMENT '用户编号', cid INT(10) NULL COMMENT '运营商编号', price INT(10) NULL COMMENT '价格' )ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

3. 编写代码

1). 实体类Combo

    位于entity包

/** * @author Ray * @date 2018/7/7 0007 * 运营商实体类 */ public class Combo { private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Combo{" + "id=" + id + ", name='" + name + '\'' + '}'; } }

2). 实体类UserCombo

    位于entity包

/** * @author Ray * @date 2018/7/7 0007 * 用户拥有运营商实体 */ public class UserCombo { private int uid; private int cid; private int price; private List<Combo> combos; public List<Combo> getCombos() { return combos; } public void setCombos(List<Combo> combos) { this.combos = combos; } public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public int getCid() { return cid; } public void setCid(int cid) { this.cid = cid; } public int getPrice() { return price; } public void setPrice(int price) { this.price = price; } @Override public String toString() { return "UserCombo{" + "uid=" + uid + ", cid=" + cid + ", price=" + price + ", combos=" + combos + '}'; } }

3). 实体类User

    位于entity包，添加UserCombo集合，添加对应的setter、getter

/** * @author Ray * @date 2018/7/7 0007 * 用户实体类 */ public class User { /** * 编号（主键） */ private int id; /** * 用户名 */ private String name; /** * 新增Phone集合 */ private List<Phone> phones; /** * 新增UserCombo集合 */ private List<UserCombo> userCombos; public List<UserCombo> getUserCombos() { return userCombos; } public void setUserCombos(List<UserCombo> userCombos) { this.userCombos = userCombos; } public List<Phone> getPhones() { return phones; } public void setPhones(List<Phone> phones) { this.phones = phones; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public User() { } public User(String name) { this.name = name; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + '}'; } }

4). 数据操作类UserMapper

    位于mapper包，添加查询方法selectComboById(Integer id)

/** * @author Ray * @date 2018/7/7 0007 * 数据操作层 */ public interface UserMapper { /** * 根据id获取用户信息 */ public User selectById(Integer id); /** * 查询所有用户信息 */ public List<User> list(); /** * 新增用户信息 */ public int insert(User user); /** * 修改用户信息 */ public int update(User user); /** * 删除用户信息 */ public int delete(Integer id); /** * 根据id查询用户的所有电话信息 */ public User selectPhoneById(Integer id); /** * 根据id查询用户选择的运营商 */ public User selectComboById(Integer id); }

5). UserMapper类映射UserMapper.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.ray.mybatis.mapper.UserMapper"> <!-- 新增用户信息 --> <insert id="insert" parameterType="user"> INSERT INTO user(name) VALUES (#{name}) </insert> <!-- 修改用户信息 --> <update id="update" parameterType="user"> UPDATE user SET name=#{name} WHERE id=#{id} </update> <!-- 删除用户信息 --> <delete id="delete"> DELETE FROM user WHERE id=#{id} </delete> <!-- 根据id获取用户信息 --> <select id="selectById" resultType="user" parameterType="int"> SELECT * FROM user WHERE id=#{id} </select> <!-- 查询所有用户信息 --> <select id="list" resultType="user"> SELECT * FROM user </select> <!-- 注意：当多个表的字段名一样的时候，查询需要用别名，否则查询结果不理想 --> <resultMap id="phoneMap" type="user"> <id property="id" column="id"/> <result property="name" column="name"/> <!-- 一对多 【ofType是映射到list集合属性中pojo的类型】 --> <collection property="phones" ofType="phone"> <id property="id" column="pid"/> <result property="name" column="pname"/> <result property="number" column="number"/> <result property="uid" column="uid"/> </collection> </resultMap> <!-- 根据id查询用户的所有电话信息 --> <select id="selectPhoneById" resultMap="phoneMap" parameterType="int"> SELECT u.*, p.id pid,p.name pname,p.number,p.uid FROM user u, phone p WHERE u.id=p.uid and u.id=#{id} </select> <!-- 注意：抽离userMap --> <resultMap id="userMap" type="user"> <id property="id" column="id"/> <result property="name" column="name"/> </resultMap> <!-- 注意：继承userMap --> <resultMap id="comboMap" type="user" extends="userMap"> <!-- 一对多 --> <collection property="userCombos" ofType="userCombo"> <result property="price" column="price"/> <result property="uid" column="uid"/> <result property="cid" column="cid"/> <!-- 一对多 --> <collection property="combos" ofType="combo"> <id property="id" column="cid"/> <result property="name" column="cname"/> </collection> </collection> </resultMap> <!-- 根据id查询用户选择的运营商 --> <select id="selectComboById" parameterType="int" resultMap="comboMap"> SELECT u.*, b.price,b.uid,b.cid, c.id cid,c.name cname FROM user u,user_combo b,combo c WHERE u.id=b.uid AND b.cid=c.id AND u.id=#{id} </select> </mapper>

6). 数据控制类UserController

    位于controller包

/** * @author Ray * @date 2018/7/7 0007 * 数据控制层 */ @RestController public class UserController { @Autowired private UserMapper userMapper; /** * 根据id获取用户信息 * localhost:8080/user/2 */ @GetMapping("/user/{id}") public User selectById(@PathVariable("id")Integer id){ User user = userMapper.selectById(id); return user; } /** * 查询所有用户信息 * localhost:8080/users */ @GetMapping("/users") public List<User> list(){ List<User> users = userMapper.list(); return users; } /** * 新增用户信息 * localhost:8080/user?name=Ray1 */ @PostMapping("/user") public String insert(User user){ int result = userMapper.insert(user); if(result == 1){ return "success"; } return "fail"; } /** * 修改用户信息 * localhost:8080/user/2?name=Ray22 */ @PutMapping("/user/{id}") public String update(User user, @PathVariable("id") Integer id){ int result = userMapper.update(user); if(result == 1){ return "success"; } return "fail"; } /** * 删除用户信息 * localhost:8080/user/8 */ @DeleteMapping("/user/{id}") public String delete(@PathVariable("id") Integer id){ int result = userMapper.delete(id); if(result == 1){ return "success"; } return "fail"; } /** * 根据id查询用户的所有电话信息(一对多) * localhost:8080/user/phone/1 */ @GetMapping("/user/phone/{id}") public User selectPhoneById(@PathVariable("id") Integer id){ User user = userMapper.selectPhoneById(id); return user; } /** * 根据id查询用户选择的运营商（多对多） * localhost:8080/user/combo/1 */ @GetMapping("/user/combo/{id}") public User selectComboById(@PathVariable("id") Integer id){ User user = userMapper.selectComboById(id); return user; } }

4. 测试

    根据id查询用户选择的运营商-GET