系列文章目录
前言
本系列文章主要记录项目过程中重点的项目技术
一、目标
由于一些数据表是用来存储历史数据的，但是页面展示的时候有需要显示相关的名称之类的，这个时候需要我们进行表的联查。但有时候一张表可能对应两张表的内容，这个时候我们需要用union来实现查询结果的拼接，这个时候需要自定义SQL语句，由于查询结果是自定义的，所以我们这个时候一般需要自定义实体对象用来接收数据。
二、实现目标
1.确定返回的属性
1.根据可能需要的页面展示的内容，编写SQL语句，以及对应需要返回的属性
2.编写自定义SQL语句
1.在mapping文件夹中找到对应的.xml文件，将1中的SQL文件作为内容，放入方法中，并根据查询条件添加相关的查询内容
<!-- 查询数据-->
<select id="findDisplayData" resultType="com.jeesite.modules.lbst.entity.DisplayLocation">
SELECT
tu.NAME as tname,
tcw.cname AS people,
tcw.type as type,
lo.distance,
tcw.phone as phone,
lo.update_date,
tcw.office_code as officeCode,
tcw.office_name as officeName
FROM
lbst_location lo
LEFT JOIN lbst_tunnel tu ON lo.tunnel_id = tu.id
LEFT JOIN (
SELECT
l.type,
l.id,
l.office_code,
l.office_name,
c.car_code AS cname,
c.car_phone AS phone
FROM
lbst_tracker l
INNER JOIN lbst_tracker_worker tw ON l.id = tw.tracker_id
INNER JOIN lbst_car c ON tw.binding_code = c.id UNION
SELECT
l.type,
l.id,
l.office_code,
l.office_name,
w.NAME AS cname,
w.phone AS phone
FROM
lbst_tracker l
INNER JOIN lbst_tracker_worker tw ON l.id = tw.tracker_id
INNER JOIN lbst_worker w ON tw.binding_code = w.id
) AS tcw ON lo.tracker_id = tcw.id
<where>
<if test="tname !=null and tname !=''">
AND tu.name like CONCAT('%',#{tname},'%')
</if>
<if test="people !=null and people !=''">
AND tcw.cname like CONCAT('%',#{people},'%')
</if>
<if test="type !=null and type !=''">
AND tcw.type = #{type}
</if>
<if test="distance !=null and distance !=''">
AND lo.distance = #{distance}
</if>
<if test="phone !=null and phone !=''">
AND tcw.phone =#{phone}
</if>
<if test="officeCode !=null and officeCode !=''">
AND tcw.office_code = #{officeCode}
</if>
</where>
<if test="page != null and page.orderBy != null and page.orderBy != ''">
ORDER BY ${page.orderBy} DESC
</if>
</select>
3.根据属性创建entity
根据1中的返回属性，创建entity对象
package com.jeesite.modules.lbst.entity;
import java.util.Date;
import com.jeesite.common.entity.DataEntity;
public class DisplayLocation extends DataEntity<DisplayLocation>{
/**
*
*/
private static final long serialVersionUID = 1L;
private String tname;
private String people;//用来表示员工姓名/车辆内部编号
private String type ;//标签类型
private Double distance;//距离洞口的距离
private String phone;//手机号
private Date updateDate;//定位时间
private String officeCode;//机构编码
private String officeName;
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public String getPeople() {
return people;
}
public void setPeople(String people) {
this.people = people;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public Double getDistance() {
return distance;
}
public void setDistance(Double distance) {
this.distance = distance;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getOfficeCode() {
return officeCode;
}
public void setOfficeCode(String officeCode) {
this.officeCode = officeCode;
}
public Date getUpdateDate() {
return updateDate;
}
public void setUpdateDate(Date updateDate) {
this.updateDate = updateDate;
}
public String getOfficeName() {
return officeName;
}
public void setOfficeName(String officeName) {
this.officeName = officeName;
}
}
4.service层代码
/**
* 分页查询隧道中车辆/人员的位置信息
* @param displayLocation
* @return
*/
@Transactional(readOnly = false)
public Page<DisplayLocation> findDisplayData(DisplayLocation displayLocation){
Page<DisplayLocation> page = (Page<DisplayLocation>) displayLocation.getPage();
//设置排序的条件
page.setOrderBy("lo.update_date");
page.setList(dao.findDisplayData(displayLocation));
return page;
}
5.control层代码
/**
* 位置信息列表页面
*/
@RequiresPermissions("user")
@RequestMapping(value = "locationListPage")
public String locationListPage(HttpServletRequest request, HttpServletResponse response, Model model) {
return "modules/lbst/locationListPage";
}
/**
* 查询位置列表信息
*/
@RequiresPermissions("user")
@RequestMapping(value = "listDisplayData")
@ResponseBody
public Page<DisplayLocation> listDisplayData(DisplayLocation location,
HttpServletRequest request, HttpServletResponse response){
location.setPage(new Page<DisplayLocation>(request, response));
// 通过当前登陆用户来获得机构code和名称
Office office = EmpUtils.getOffice();
//根据当前用户所属机构进行过滤
location.setOfficeCode(office.getOfficeCode());
Page<DisplayLocation> page = locationService.findDisplayData(location);
return page;
}
6.HTML代码
<% layout('/layouts/default.html', {title: '位置信息表管理', libs: ['dataGrid']}){ %>
<div class="main-content">
<div class="box box-main">
<div class="box-header">
<div class="box-title">
<i class="fa icon-notebook"></i> ${text('位置信息表管理')}
</div>
<div class="box-tools pull-right">
<a href="#" class="btn btn-default" id="btnSearch" title="${text('查询')}"><i class="fa fa-filter"></i> ${text('查询')}</a>
<a href="#" class="btn btn-default" id="btnSetting" title="${text('设置')}"><i class="fa fa-navicon"></i></a>
</div>
</div>
<div class="box-body">
<#form:form id="searchForm" model="${location.displayLocation}" action="${ctx}/lbst/location/listDisplayData" method="post" class="form-inline hide"
data-page-no="${parameter.pageNo}" data-page-size="${parameter.pageSize}" data-order-by="${parameter.orderBy}">
<div class="form-group">
<label class="control-label">${text('隧道名称')}：</label>
<div class="control-inline">
<#form:input path="tname" maxlength="64" class="form-control width-120"/>
</div>
</div>
<div class="form-group">
<label class="control-label">${text('工人名称/车辆ID')}：</label>
<div class="control-inline">
<#form:input path="people" maxlength="64" class="form-control width-120"/>
</div>
</div>
<div class="form-group">
<label class="control-label">${text('标签类型')}：</label>
<div class="control-inline width-120">
<#form:select path="type" dictType="lbst_tag_type" blankOption="true" class="form-control"/>
</div>
</div>
<div class="form-group">
<label class="control-label">${text('距离洞口的位置')}：</label>
<div class="control-inline">
<#form:input path="distance" maxlength="10" type="number" class="form-control number width-120"/>
</div>
</div>
<div class="form-group">
<label class="control-label">${text('手机号码')}：</label>
<div class="control-inline">
<#form:input path="phone" maxlength="11" type="number" class="form-control number width-120"/>
</div>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary btn-sm">${text('查询')}</button>
<button type="reset" class="btn btn-default btn-sm">${text('重置')}</button>
</div>
</#form:form>
<table id="dataGrid"></table>
<div id="dataGridPage"></div>
</div>
</div>
</div>
<% } %>
<script>
// 初始化DataGrid对象
$('#dataGrid').dataGrid({
searchForm: $("#searchForm"),
columnModel: [
{header:'${text("机构名称")}', name:'officeName', index:'displayLocation.office_name', width:200, align:"center"},
{header:'${text("隧道名称")}', name:'tname', index:'displayLocation.tname', width:200, align:"center"},
{header:'${text("工人名称/车辆ID")}', name:'people', index:'displayLocation.people', width:200, align:"center"},
{header:'${text("标签类型")}', name:'type', index:'displayLocation.type', width:100, align:"center", formatter: function(val, obj, row, act){
return js.getDictLabel(${@DictUtils.getDictListJson('lbst_tag_type')}, val, '${text("未知")}', true);
}},
{header:'${text("距离洞口的位置")}', name:'distance', index:'displayLocation.distance', width:150, align:"center", formatter: function(val, obj, row, act){
return js.formatNumber(val, 2, false, ''); // 数值类型格式化 (原始数值, 小数位数, 是否千分位, 默认值，金额情况下设置0.00);
}},
{header:'${text("手机号码")}', name:'phone', index:'displayLocation.phone', width:100, align:"center"},
{header:'${text("定位时间")}', name:'updateDate', index:'displayLocation.update_date', width:150, align:"center"}
],
// 加载成功后执行事件
ajaxSuccess: function(data){
}
});
</script>
7.注意事项
1.因为页面跳转以及数据的返回都在location路径中，所以HTML页面中的form标签的model对应的值必须是location相关的，例如上面的location.displayLocation
2.DisplayLocation对象必须作为Location对象的一个属性存在，否则页面数据不能正常显示
所以，Location中必须要有DisplayLocation对象属性
/**
* Copyright (c) 2013-Now http://jeesite.com All rights reserved.
*/
package com.jeesite.modules.lbst.entity;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import org.hibernate.validator.constraints.Length;
import com.jeesite.common.entity.DataEntity;
import com.jeesite.common.mybatis.annotation.Column;
import com.jeesite.common.mybatis.annotation.JoinTable;
import com.jeesite.common.mybatis.annotation.Table;
import com.jeesite.common.mybatis.annotation.JoinTable.Type;
/**
* 位置信息表Entity
* @author feiyu
* @version 2020-09-18
*/
@Table(name="lbst_location", alias="a", columns={
@Column(name="id", attrName="id", label="编号", isPK=true),
@Column(name="tracker_id", attrName="trackerId", label="标签ID"),
@Column(name="station_id", attrName="stationId", label="基站ID"),
@Column(name="tunnel_id", attrName="tunnelId", label="隧道ID"),
@Column(name="distance", attrName="distance", label="距离洞口的位置"),
@Column(name="sequence_number", attrName="sequenceNumber", label="顺序号"),
@Column(includeEntity=DataEntity.class),
},joinTable = {
@JoinTable(type=Type.LEFT_JOIN, entity=Station.class, alias="o",
on="o.id = a.station_id",
columns={@Column(includeEntity=Station.class)}),
@JoinTable(type=Type.LEFT_JOIN, entity=Tracker.class, alias="c",
on="c.id = a.tracker_id",
columns={@Column(includeEntity=Tracker.class)}),
@JoinTable(type=Type.LEFT_JOIN, entity=Tunnel.class, alias="d",
on="d.id = a.tunnel_id",
columns={@Column(includeEntity=Tunnel.class)}),
}, extWhereKeys="dsf",orderBy="a.update_date DESC"
)
public class Location extends DataEntity<Location> {
private static final long serialVersionUID = 1L;
private String trackerId; // 标签ID
private String stationId; // 基站ID
private String tunnelId; // 隧道ID
private Double distance; // 距离洞口的位置
private Integer sequenceNumber; // 顺序号
private Tunnel tunnel;
private Station station;
private Tracker tracker;
private DisplayLocation displayLocation;
private String tname;
private String people;
private String type;
private String phone;
private String officeCode;
private String officeName;
public Location() {
this(null);
}
public Location(String id){
super(id);
}
@NotBlank(message="标签ID不能为空")
@Length(min=0, max=64, message="标签ID长度不能超过 64 个字符")
public String getTrackerId() {
return trackerId;
}
public void setTrackerId(String trackerId) {
this.trackerId = trackerId;
}
@NotBlank(message="基站ID不能为空")
@Length(min=0, max=64, message="基站ID长度不能超过 64 个字符")
public String getStationId() {
return stationId;
}
public void setStationId(String stationId) {
this.stationId = stationId;
}
@NotNull(message="距离不能为空")
public Double getDistance() {
return distance;
}
public void setDistance(Double distance) {
this.distance = distance;
}
@NotNull(message="顺序号不能为空")
public Integer getSequenceNumber() {
return sequenceNumber;
}
public void setSequenceNumber(Integer sequenceNumber) {
this.sequenceNumber = sequenceNumber;
}
@NotBlank(message="隧道ID不能为空")
@Length(min=0, max=64, message="隧道ID长度不能超过 64 个字符")
public String getTunnelId() {
return tunnelId;
}
public void setTunnelId(String tunnelId) {
this.tunnelId = tunnelId;
}
public Tunnel getTunnel() {
return tunnel;
}
public void setTunnel(Tunnel tunnel) {
this.tunnel = tunnel;
}
public Station getStation() {
return station;
}
public void setStation(Station station) {
this.station = station;
}
public Tracker getTracker() {
return tracker;
}
public void setTracker(Tracker tracker) {
this.tracker = tracker;
}
public DisplayLocation getDisplayLocation() {
return displayLocation;
}
public void setDisplayLocation(DisplayLocation displayLocation) {
this.displayLocation = displayLocation;
}
public String getPeople() {
return people;
}
public void setPeople(String people) {
this.people = people;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getOfficeCode() {
return officeCode;
}
public void setOfficeCode(String officeCode) {
this.officeCode = officeCode;
}
public String getOfficeName() {
return officeName;
}
public void setOfficeName(String officeName) {
this.officeName = officeName;
}
}
总结
至此，本文章结束
1112
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
