1.1对1:
一个设备对应一个区域. 根据设备查询区域信息
表结构:
设备
区域
model
device
public class Device extends Model<Device> {
private static final long serialVersionUID = 1L;
/**
* 序号
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 设备名称
*/
@TableField("device_name")
private String deviceName;
/**
* 设备编码
*/
@TableField("device_number")
private String deviceNumber;
@TableField("area_id")
private Integer areaId;
//设备区域
@TableField(exist = false)
private Area deviceArea;
//种类
@TableField(exist = false)
private List<Category> categories;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDeviceName() {
return deviceName;
}
public void setDeviceName(String deviceName) {
this.deviceName = deviceName;
}
public String getDeviceNumber() {
return deviceNumber;
}
public void setDeviceNumber(String deviceNumber) {
this.deviceNumber = deviceNumber;
}
public Integer getAreaId() {
return areaId;
}
public void setAreaId(Integer areaId) {
this.areaId = areaId;
}
public Area getDeviceArea() {
return deviceArea;
}
public void setDeviceArea(Area deviceArea) {
this.deviceArea = deviceArea;
}
public List<Category> getCategories() {
return categories;
}
public void setCategories(List<Category> categories) {
this.categories = categories;
}
@Override
protected Serializable pkVal() {
return this.id;
}
@Override
public String toString() {
return "Device{" +
", id=" + id +
", deviceName=" + deviceName +
", deviceNumber=" + deviceNumber +
", areaId=" + areaId +
"}";
}
}
area
public class Area extends Model<Area> {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
@TableField("area_name")
private String areaName;
@TableField("area_number")
private String areaNumber;
@TableField(exist = false)
private List<Device> devices;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getAreaName() {
return areaName;
}
public void setAreaName(String areaName) {
this.areaName = areaName;
}
public String getAreaNumber() {
return areaNumber;
}
public void setAreaNumber(String areaNumber) {
this.areaNumber = areaNumber;
}
@Override
protected Serializable pkVal() {
return this.id;
}
public List<Device> getDevices() {
return devices;
}
public void setDevices(List<Device> devices) {
this.devices = devices;
}
@Override
public String toString() {
return "Area{" +
"id=" + id +
", areaName='" + areaName + '\'' +
", areaNumber='" + areaNumber + '\'' +
", devices=" + devices +
'}';
}
}
DeviceMapper.xml
<!--liyang add resultmap 1to1-->
<resultMap id="DeviceResultMap" type="cn.stylefeng.guns.modular.system.model.Device">
<id column="id" property="id" />
<result column="device_name" property="deviceName" />
<result column="device_number" property="deviceNumber" />
<result column="area_id" property="areaId" />
<association property="deviceArea" javaType="cn.stylefeng.guns.modular.system.model.Area">
<id property="id" column="area_id"/>
<result property="areaName" column="area_name"/>
<result property="areaNumber" column="area_number"/>
</association>
</resultMap>
<select id="getDeviceListWithArea" resultMap="DeviceResultMap">
select * from device d, area a where d.area_id = a.id
</select>
DeviceMapper.java
public interface DeviceMapper extends BaseMapper<Device> {
List<Device> getDeviceListWithArea();
}
DeviceService.java
public interface IDeviceService extends IService<Device> {
Device getDeviceWithAreaInfo(int deviceId);
List<Device> getDeviceListWithArea();
List<Device> queryDeviceCategories();
}
@Service
public class DeviceServiceImpl extends ServiceImpl<DeviceMapper, Device> implements IDeviceService {
@Resource
private DeviceMapper deviceMapper;
@Override
public Device getDeviceWithAreaInfo(int deviceId){
return deviceMapper.getDeviceWithAreaInfo(deviceId);
}
@Override
public List<Device> getDeviceListWithArea(){
return deviceMapper.getDeviceListWithArea();
}
@Override
public List<Device> queryDeviceCategories(){
return deviceMapper.queryDeviceCategories();
};
}
controller
@RequestMapping(value = "/list")
@ResponseBody
public Object list(String condition) {
//return deviceService.selectList(null);
//1对1, 查询设备所在区域
List<Device> list = deviceService.getDeviceListWithArea();
//多对多, 通过中间表查询设备种类(可能是多个种类)
List<Device> list1 = deviceService.queryDeviceCategories();
return list;
}
1对1结果:
list的内存模型
2.多对多
多个设备对应多个种类
表结构
device
category
中间表device_category
model
device, 上面一节已经写了
category
public class Category extends Model<Category> {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
@TableField("cate_name")
private String cateName;
@TableField("cate_info")
private String cateInfo;
private List<Device> devices;
public List<Device> getDevices() {
return devices;
}
public void setDevices(List<Device> devices) {
this.devices = devices;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCateName() {
return cateName;
}
public void setCateName(String cateName) {
this.cateName = cateName;
}
public String getCateInfo() {
return cateInfo;
}
public void setCateInfo(String cateInfo) {
this.cateInfo = cateInfo;
}
@Override
protected Serializable pkVal() {
return this.id;
}
@Override
public String toString() {
return "Category{" +
", id=" + id +
", cateName=" + cateName +
", cateInfo=" + cateInfo +
"}";
}
}
DeviceMapper.xml
<!--liyang add resultmap 多to多-->
<resultMap id="DuoResultMap" type="cn.stylefeng.guns.modular.system.model.Device">
<id column="id" property="id" />
<result column="device_name" property="deviceName" />
<result column="device_number" property="deviceNumber" />
<result column="area_id" property="areaId" />
<collection property="categories" ofType="cn.stylefeng.guns.modular.system.model.Category">
<!--<id property="id" column="id"/>-->
<result property="cateName" column="cate_name"/>
<result property="cateInfo" column="cate_info"/>
</collection>
</resultMap>
<select id="queryDeviceCategories" resultMap="DuoResultMap">
select *
from
device d
inner join
device_category m
on
d.id=m.device_id
inner join
category c
on
m.cate_id=c.id
</select>
DeviceMapper.java
public interface DeviceMapper extends BaseMapper<Device> {
List<Device> queryDeviceCategories();
}
service层和controller参照上一节
list1内存模型:
3.1对多
1个区域对应多个device
表结构device. area
跟第一节一样
model也一样
看AreaMapper.xml(3中方式任选)
<?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="cn.stylefeng.guns.modular.system.dao.AreaMapper">
<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="cn.stylefeng.guns.modular.system.model.Area">
<id column="id" property="id" />
<result column="area_name" property="areaName" />
<result column="area_number" property="areaNumber" />
</resultMap>
<!-- 通用查询结果列 -->
<sql id="Base_Column_List">
id, area_name, area_number
</sql>
<!--
案例说明:
区域1下面有两个设备:设备1 设备2
区域1: List<Device> devices;
设备1: Area deviceArea;
设备2: Area deviceArea;
-->
<!--
方案1: 成功
-->
<!--<resultMap id="AreaResultMap" type="cn.stylefeng.guns.modular.system.model.Area">
<id column="id" property="id" />
<result column="area_name" property="areaName" />
<result column="area_number" property="areaNumber" />
<collection property="devices" ofType="cn.stylefeng.guns.modular.system.model.Device">
<result property="deviceName" column="device_name"/>
<result property="deviceNumber" column="device_number"/>
<result property="areaId" column="area_id"/>
</collection>
</resultMap>
<select id="getAreaList" resultMap="AreaResultMap">
select
d.device_name,
d.device_number,
d.area_id,
a.id,
a.area_name,
a.area_number
from area a
left join device d
on d.area_id=a.id
</select>-->
<!--
方案2: 拼接查询(正确)
会返回一个area, devices的size是2, 包含了两个设备
-->
<resultMap id="AreaResultMap" type="cn.stylefeng.guns.modular.system.model.Area">
<id column="id" property="id" />
<result column="area_name" property="areaName" />
<result column="area_number" property="areaNumber" />
<collection property="devices" ofType="cn.stylefeng.guns.modular.system.model.Device"
column="id" select="getDevices">
</collection>
</resultMap>
<select id="getAreaList" resultMap="AreaResultMap">
select * from area
</select>
<select id="getDevices" parameterType="int" resultType="cn.stylefeng.guns.modular.system.model.Device">
SELECT
id id,
device_name deviceName,
device_number deviceNumber,
area_id areaId
FROM device
WHERE area_id=#{id}
</select>
<!--
方案3: 成功
最开始每个区域只能显示一个设备.
经过最终尝试, 找到原因, 原因是Area的id字段, 与device的id字段重复,
如果在collection中加入了<id property="id" column="id"/>, 那么就会只查询到一个设备.
解决方案:
1. 去掉collection中的重复字段id
2. 设计表的时候就将device的id字段取名为d_id. 与这个area的id不重复即可.
-->
<!-- <resultMap id="AreaResultMap" type="cn.stylefeng.guns.modular.system.model.Area">
<id column="id" property="id" />
<result column="area_name" property="areaName" />
<result column="area_number" property="areaNumber" />
<collection property="devices" ofType="cn.stylefeng.guns.modular.system.model.Device">
<!–
<id property="id" column="id"/>
–>
<result property="deviceName" column="device_name"/>
<result property="deviceNumber" column="device_number"/>
<result property="areaId" column="area_id"/>
</collection>
</resultMap>
<select id="getAreaList" resultMap="AreaResultMap">
select * from area a,device d where a.id = d.area_id
</select>-->
</mapper>
AreaMapper.java
public interface AreaMapper extends BaseMapper<Area> {
List<Area> getAreaList();
}
AreaService层
public interface IAreaService extends IService<Area> {
List<Area> getAreaList();
}
@Service
public class AreaServiceImpl extends ServiceImpl<AreaMapper, Area> implements IAreaService {
@Resource
private AreaMapper areaMapper;
@Override
public List<Area> getAreaList(){
return areaMapper.getAreaList();
}
}
areacontroller层
/**
* 获取区域列表
*/
@RequestMapping(value = "/list")
@ResponseBody
public Object list(String condition) {
//return areaService.selectList(null);
List<Area> arealist = areaService.getAreaList();
return arealist;
}
arealist的模型