mysql 多对多_Mysql多对多关系的查询

1.创建user表

2.创建gateway表

3.创建user_gateway表

4.创建device表

5.创建gateway_device表

6.创建一个实体类

public class DeviceModule{

private Integer id;

private String device_mac;

private String device_name;

private String device_status;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getDevice_mac() {

return device_mac;

}

public void setDevice_mac(String device_mac) {

this.device_mac = device_mac;

}

public String getDevice_name() {

return device_name;

}

public void setDevice_name(String device_name) {

this.device_name = device_name;

}

public String getDevice_status() {

return device_status;

}

public void setDevice_status(String device_status) {

this.device_status = device_status;

}

}

7.Mapper.java

@Select("SELECT * FROM device "

+ "INNER JOIN gateway_device ON gateway_device.device_id=device.id "

+ "INNER JOIN gateway ON gateway.id=gateway_device.gateway_id "

+ "INNER JOIN user_gateway ON user_gateway.gateway_id=gateway.id "

+ "INNER JOIN user ON user.id=user_gateway.user_id and user.id=(SELECT id FROM user WHERE phone=#{phoneno})")

List getDeviceNameByUserPhone(@Param("phoneno")String phoneno);