最近在做在eclipse + maven搭建SSM框架下做一个简单的后台管理系统,因为是第一次搭建SSM项目,在mybatis进行多表连接查询的时候遇到问题,不知道如何进行处理?在网上搜了一下解决方法是,以实现,记录下自己的实现方案。
- 表之间的关系如下图所示:
环境搭建的是eclipse + maven+SpringMVC + Spring + MyBatis,前端用到的是bootstrap框架。
因为是第一次做SSM项目没有用到 mybatis 的自动生成工具 mybatis generator 生成的实体类、mapper 接口、以及 mapper xml 文件。都是自己写的。
Project 类
import java.util.List;
import org.apache.ibatis.type.Alias;
/**
* @author 作者:
* @date 创建时间:2018年10月19日
* @version 1.0
* @author
*/
@Alias("Project")
public class Project {
private String gid ;
private String activity ;
private String activity_end;
private String src ;
private String src2;
private String src3;
private String msgSuc;
private String msgSuc1;
private String jdr;
private String createtime;
private List<Lottery> lotterys;//这个是一个重点与Lottery实体类进行关联
public String getGid() {
return gid;
}
public void setGid(String gid) {
this.gid = gid;
}
public String getActivity() {
return activity;
}
public void setActivity(String activity) {
this.activity = activity;
}
public String getSrc() {
return src;
}
public void setSrc(String src) {
this.src = src;
}
public String getActivity_end() {
return activity_end;
}
public void setActivity_end(String activity_end) {
this.activity_end = activity_end;
}
public String getSrc2() {
return src2;
}
public void setSrc2(String src2) {
this.src2 = src2;
}
public String getSrc3() {
return src3;
}
public void setSrc3(String src3) {
this.src3 = src3;
}
public String getMsgSuc() {
return msgSuc;
}
public void setMsgSuc(String msgSuc) {
this.msgSuc = msgSuc;
}
public String getMsgSuc1() {
return msgSuc1;
}
public void setMsgSuc1(String msgSuc1) {
this.msgSuc1 = msgSuc1;
}
public String getJdr() {
return jdr;
}
public void setJdr(String jdr) {
this.jdr = jdr;
}
public String getCreatetime() {
return createtime;
}
public void setCreatetime(String createtime) {
this.createtime = createtime;
}
public List<Lottery> getLottery() {
return lotterys;
}
public void setLottery(List<Lottery> lotterys) {
this.lotterys = lotterys;
}
@Override
public String toString() {
return "Project [gid=" + gid + ", activity=" + activity + ", activity_end=" + activity_end + ", src=" + src
+ ", src2=" + src2 + ", src3=" + src3 + ", msgSuc=" + msgSuc + ", msgSuc1=" + msgSuc1 + ", jdr=" + jdr
+ ", createtime=" + createtime + "]";
}
public Project(String gid, String activity, String activity_end, String src, String src2, String src3,
String msgSuc, String msgSuc1, String jdr) {
super();
this.gid = gid;
this.activity = activity;
this.activity_end = activity_end;
this.src = src;
this.src2 = src2;
this.src3 = src3;
this.msgSuc = msgSuc;
this.msgSuc1 = msgSuc1;
this.jdr = jdr;
}
public Project() {
super();
}
}
Lottery类
import org.apache.ibatis.type.Alias;
/**
* @author 作者:
* @date 创建时间:2018年10月19日
* @version 1.0
* @author
*/
@Alias("Lottery")
public class Lottery {
private String id;
private String project_id;
private String url;
private String settingId;
private String title;
private String settingTitle;
private String days_newCust;
private String days_oldCust;
private String appid; //appid
private String appSecret;
private String awardType;
private String status;
private String mete;
private String mutex;
private String createtime;/
private String jdr;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getProject_id() {
return project_id;
}
public void setProject_id(String project_id) {
this.project_id = project_id;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getSettingId() {
return settingId;
}
public void setSettingId(String settingId) {
this.settingId = settingId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSettingTitle() {
return settingTitle;
}
public void setSettingTitle(String settingTitle) {
this.settingTitle = settingTitle;
}
public String getDays_newCust() {
return days_newCust;
}
public void setDays_newCust(String days_newCust) {
this.days_newCust = days_newCust;
}
public String getDays_oldCust() {
return days_oldCust;
}
public void setDays_oldCust(String days_oldCust) {
this.days_oldCust = days_oldCust;
}
public String getAppid() {
return appid;
}
public void setAppid(String appid) {
this.appid = appid;
}
public String getAppSecret() {
return appSecret;
}
public void setAppSecret(String appSecret) {
this.appSecret = appSecret;
}
public String getAwardType() {
return awardType;
}
public void setAwardType(String awardType) {
this.awardType = awardType;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String getMete() {
return mete;
}
public void setMete(String mete) {
this.mete = mete;
}
public String getMutex() {
return mutex;
}
public void setMutex(String mutex) {
this.mutex = mutex;
}
public String getCreatetime() {
return createtime;
}
public void setCreatetime(String createtime) {
this.createtime = createtime;
}
public String getJdr() {
return jdr;
}
public void setJdr(String jdr) {
this.jdr = jdr;
}
public Lottery() {
super();
}
public Lottery(String id, String project_id, String url, String settingId, String title, String settingTitle,
String days_newCust, String days_oldCust, String appid, String appSecret, String awardType, String status,
String mete, String mutex, String jdr) {
super();
this.id = id;
this.project_id = project_id;
this.url = url;
this.settingId = settingId;
this.title = title;
this.settingTitle = settingTitle;
this.days_newCust = days_newCust;
this.days_oldCust = days_oldCust;
this.appid = appid;
this.appSecret = appSecret;
this.awardType = awardType;
this.status = status;
this.mete = mete;
this.mutex = mutex;
this.jdr = jdr;
}
}
project 与 lottery 是1:1的关系,在 project zhong 加入一个lottery属性,对应一对一的关系。
然后就是mapper接口与xml文件
mapper :ProjectDao
public interface ProjectDao {
//查询项目信息列表
List<Project> selectAllProject();
}
xml ProjectDao.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.**.mapper.ProjectDao">
<resultMap id="queryForListMap" type="com.**.pojo.Project">
<id column="gid" property="gid" jdbcType="VARCHAR"/>
<result column="activity" property="activity" jdbcType="VARCHAR"/>
<result column="activity_end" property="activity_end" jdbcType="VARCHAR"/>
<result column="msgSuc" property="msgSuc" jdbcType="VARCHAR"/>
<result column="msgSuc1" property="msgSuc1" jdbcType="VARCHAR"/>
<collection property="lotterys" javaType="java.util.List" ofType="com.**.pojo.Lottery">
<id column="r_id" property="id" jdbcType="VARCHAR" />
<result column="r_settingId" property="settingId" jdbcType="VARCHAR" />
<result column="r_title" property="title" jdbcType="VARCHAR" />
<result column="r_settingTitle" property="settingTitle" jdbcType="VARCHAR" />
<result column="r_days_newCust" property="days_newCust" jdbcType="VARCHAR" />
<result column="r_days_oldCust" property="days_oldCust" jdbcType="VARCHAR" />
<result column="r_awardType" property="awardType" jdbcType="VARCHAR" />
<result column="r_mete" property="mete" jdbcType="VARCHAR" />
</collection>
</resultMap>
<!--用户表列名片段-->
<sql id="userColumn">
gid, activity, activity_end, src,src2,src3,msgSuc,msgSuc1,jdr,createtime
</sql>
<select id="selectAllProject" resultMap="queryForListMap">
select p.gid, p.activity, p.activity_end, p.src,p.src2,p.src3,p.msgSuc,p.msgSuc1,p.jdr,
l.id r_id,l.settingId r_settingId, l.title r_title,l.settingTitle r_settingTitle,l.days_newCust r_days_newCust,l.days_oldCust r_days_oldCust,l.appid,l.appSecret,l.awardType r_awardType,l.status,l.mete r_mete,l.mutex
from system_project p left join system_lottery l
on p.gid=l.project_id
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
delete from system_project
where gid = #{gid}
</delete>
</mapper>
service 层代码
public interface ProjectService {
List<Project> selectAllProject();
}
service impl
@Service("projectService")
public class ProjectServiceImpl implements ProjectService {
@Autowired
public ProjectDao projectDao;
@Override
public List<Project> selectAllProject() {
// TODO Auto-generated method stub
return this.projectDao.selectAllProject();
}
}
controler层往前端传的是json
@RequestMapping("/showProjectList")
@ResponseBody
public String GetDepartment()
{
List<Project> list = projectService.selectAllProject();
Gson gson = new Gson();
System.err.println("**********list*********"+gson.toJson(list));
System.err.println("**********list*********"+gson.toJson(list));
System.err.println("**********list*********"+gson.toJson(list));
return gson.toJson(list);
}
传的json是复杂的嵌套类型如下图:
前端用到的了bootstrap框架
<script type="text/javascript">
$(function () {
//1.初始化Table
var oTable = new TableInit();
oTable.Init();
//2.初始化Button的点击事件
operate.operateInit();
});
//table
var TableInit = function () {
var oTableInit = new Object();
//初始化Table
oTableInit.Init = function () {
$('#tb_departments').bootstrapTable({
url: '${pageContext.request.contextPath}/showProjectList', //请求后台的URL(*)
method: 'get', //请求方式(*)
toolbar: '#toolbar', //工具按钮用哪个容器
striped: true, //是否显示行间隔色
cache: false, //是否使用缓存,默认为true,所以一般情况下需要设置一下这个属性(*)
pagination: true, //是否显示分页(*)
paginationLoop: false,
sortable: true, //是否启用排序
sortOrder: "asc", //排序方式
queryParams: oTableInit.queryParams,//传递参数(*)
sidePagination: "client", //分页方式:client客户端分页,server服务端分页(*)
pageNumber:1, //初始化加载第一页,默认第一页
pageSize: 15, //每页的记录行数(*)
pageList: [15], //可供选择的每页的行数(*)
search: true, //是否显示表格搜索,此搜索是客户端搜索,不会进服务端,所以,个人感觉意义不大
strictSearch: false,
showColumns: false, //是否显示所有的列
showRefresh: true, //是否显示刷新按钮
minimumCountColumns: 1, //最少允许的列数
clickToSelect: false, //是否启用点击选中行
uniqueId: "gid", //每一行的唯一标识,一般为主键列
showToggle:false, //是否显示详细视图和列表视图的切换按钮
cardView: false, //是否显示详细视图
detailView: false, //是否显示父子表
undefinedText:'-', //当数据为 undefined 时显示的字符
columns: [{
checkbox: false,
title: '序号',
field: '',
align: 'center',
formatter: function (value, row, index) {
return index + 1;
}
}, {
field: 'lotterys',
title: '名称',
formatter: uidHandle,//自定义方法设置uid跳转链接
sortable:false
}, {
field: 'activity',
title: '活动ID'
}, {
field: 'activity_end',
title: '结束日期'
}, {
field: 'lotterys',
title: '标识',
formatter : function(value,row, index){ //主要配置在这里
return value[0].settingId; }
}, {
field: 'lotterys',
title: '名称',
sortable:false,
formatter : function(value,row, index){ //主要配置在这里
return value[0].title; }
}, {
field: 'lotterys',
title: '天数',
formatter : function(value,row, index){ //主要配置在这里
return value[0].days_newCust; }
}, {
field: 'lotterys',
title: '类型',
formatter : function(value,row, index){ //主要配置在这里
return value[0].awardType; }
}, {
field: 'lotterys',
title: '金额/收益率',
formatter : function(value,row, index){ //主要配置在这里
return value[0].mete; }
}, {
field: 'dataBtn',
title: '操作',
formatter: dataFun
}]
});
};
//条件查询
oTableInit.queryParams = function (params) {
var temp = { //这里的键的名字和控制器的变量名必须一直,这边改动,控制器也需要改成一样的
limit: params.limit, //页面大小
offset: params.offset, //页码
search: params.search,
settingTitle: $("#txt_search_name").val(),//后台请求传的查询参数
activity:$("#txt_search_id").val(),
activity_end:$("txt_search_time").val(),
};
return temp;
};
return oTableInit;
};
function serachUser() {
$("#tb_departments").bootstrapTable('refresh');
}
//button
var operate = {
// 初始化按钮事件
operateInit : function() {
},
}
//自定义图事件
//自定义跳转
var uidHandle = function (value,row,index) {
var html = "<a href='${pageContext.request.contextPath}/goLook?gid=" + row.gid + "'>" + value[0].settingTitle + "</a>";
return html;
}
//自定义按钮
var dataFun = function (value,row,index) {
var html = "<a class='btn btn-default' href=' ${pageContext.request.contextPath}/updateByPrimaryGid?gid=" + row.gid + "'><span class='glyphicon glyphicon-edit' aria-hidden='true'></span></ a><a class='btn btn-default' onclick='confirmdelete()' href='${pageContext.request.contextPath}/deleteByPrimaryKey?gid=" + row.gid + "'><span class='glyphicon glyphicon-remove' aria-hidden='true'></span></ a>";
return html;
}
function confirmdelete(){
if (!confirm("确认要删除?")) { window.event.returnValue = false; }
}
</script>
对于嵌套的json boostrap有处理方法需要如下操作:
{
field: 'lotterys',
title: '类型',
formatter : function(value,row, index){ //主要配置在这里
return value[0].awardType; }
}
最后的实现效果如下: