Jfinal 多表关联展示
多表查询展示
用左关联查询出的 表 怎么在 jfinal 项目中展示出来。
原理 跟单表查询 基本一样 只是 服务层的 sql语句处需要注意
我们先将 有关表 加入数据库中
前端页面：
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ include file="/common.jsp"%>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<title>简历管理</title>
<link rel="stylesheet" href="${ctx}/static/admin/css/switch.css">
</head>
<body>
<div class="layui-card-body">
<button class="layui-btn layui-btn-sm" id="add">添加</button>
<button class="layui-btn layui-btn-sm" id="search_sidebar"><i class="layui-icon layui-icon-search"></i></button>
<table class="layui-hide" id="jianli_table"></table>
</div>
</body>
</html>
<script src="${ctx}/static/admin/layui/layui.js"></script>
<script src="${ctx}/static/admin/js/common.js"></script>
<script type="text/html" id="search_sidebar_tpl">
<form class="layui-form">
<div class="layui-form-item">
<label class="layui-form-label">姓名</label>
<div class="layui-input-block">
<input type="text" name="name" placeholder="请输入姓名" autocomplete="off" class="layui-input">
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">公司</label>
<div class="layui-input-block">
<input type="text" name="companyname" placeholder="请输入公司名" autocomplete="off" class="layui-input">
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">职位</label>
<div class="layui-input-block">
<input type="text" name="job" placeholder="请输入职位" autocomplete="off" class="layui-input">
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">等级</label>
<div class="layui-input-block">
<select name="lv">
<option value="">初级</option>
<option value="1">中级</option>
<option value="2">高级</option>
<option value="3">资深</option>
</select>
</div>
</div>
<div class="layui-form-item">
<div class="layui-input-block">
<button class="layui-btn" lay-submit lay-filter="serach_form"><i class="layui-icon"></i> 搜索</button>
<button type="reset" class="layui-btn layui-btn-primary">重置</button>
</div>
</div>
</form>
</script>
<script type="text/javascript">
layui.use(['table','layer','jquery','element','sidebar','form','laytpl','laydate'], function(){
var $ = jQuery = layui.$;
var table = layui.table;
var layer = layui.layer;
var laytpl = layui.laytpl;
var element = layui.element;
var sidebar = layui.sidebar;
var form = layui.form;
var laydate=layui.laydate;
var tableIns = table.render({
elem: '#jianli_table',
cellMinWidth: 80,
url: '${ctx}/sys/jl/getList',
height:'full-70',
page: true,
size: 'sm',
cols: [[
{type: 'numbers'},
{field: 'id', title: '序号'},
{field: 'name', title: '姓名'},
{field: 'companyname', title: '公司名'},
{field: 'job', title: '职位'},
{field: 'lv', title: '级别'},
{field: 'money', title: '薪酬'},
]]
});
$("#add").on('click', function() {
parent.layer.open({
type: 2,
area: ['700px', '450px'],
fixed: false, //不固定
maxmin: true,
content: '${ctx}/sys/jl/form',
btn: ['保存', '重置', '关闭'],
yes: function(index, layero){
var iframeWin = layero.find('iframe')[0];//得到iframe页的窗口对象，执行iframe页的方法：
iframeWin.contentWindow.table = tableIns;
layero.find('iframe').contents().find('#submit-btn').click();
},
btn2: function(index, layero){
layero.find('iframe').contents().find('#reset-btn').click();
return false;
},
btn3: function(index, layero){
var index = layer.getFrameIndex(window.name);
layer.close(index); //再执行关闭
}
});
});
var html = laytpl($('#search_sidebar_tpl').html()).render({});
$('#search_sidebar').on('click', function () {
var that = this;
sidebar.render({
elem: that,
content: html,
title: '搜索',
shade: false,
width: '30%', //可以设置百分比和px
done: function () {
form.render();
form.on('submit(serach_form)', function (data) {
table.reload('jianli_table', {
method : 'post',
where: {
name : data.field.name,
companyname : data.field.companyname,
job : data.field.job,
lv : data.field.lv
}
});
return false;
});
}
});
});
});
</script>
Controller层 的代码：
@Clear(IfLoginInterceptor.class)
public class MoneyController extends BaseController {
public void index() {
render("money.jsp");
}
public void getList1() {
String name =this.getHeader("name");
String companyname =this.getHeader("companyname");
String job =this.getHeader("job");
String lv =this.getHeader("lv");
List<Record> pageInfo = Money.dao.getUsers();
this.renderJson(DataGridModelUtils.toDataGridView(pageInfo));
}
public void getList() {
String name =this.getPara("name");
String companyname =this.getPara("companyname");
String job =this.getPara("job");
String lv =this.getPara("lv");
Page<Money> pageInfo=Money.dao.getMoneyPage(getPage(), getRows(), name,companyname,job,lv, getOrderbyStr());
this.renderJson(DataGridModelUtils.toDataGridView(pageInfo));
}
}
Service层：
public class Money extends BaseMoney<Money> {
public static final Money dao = new Money().dao();
public Page<Money> getMoneyPage(int page, int rows, String name,String companyname,String job,String lv, String orderbyStr) {
try{
String select="SELECT t.id,t1.name,t2.companyname,t3.job,t3.lv,t.money ";
StringBuffer sqlExceptSelect=new StringBuffer("FROM money t LEFT JOIN money_username t1 ON t1.nameid=t.nameid LEFT JOIN money_company t2 ON t2.companyid=t.companyid LEFT JOIN money_job t3 ON t3.jobid=t.jobid where 1=1" );
if(CommonUtils.isNotEmpty(name)) {
sqlExceptSelect.append(" and t1.name like '%" +name+ "%'");
}
if(CommonUtils.isNotEmpty(companyname)) {
sqlExceptSelect.append(" and t2.companyname like '%" +companyname+ "%'");
}
if(CommonUtils.isNotEmpty(job)) {
sqlExceptSelect.append(" and t3.job like '%" +job+ "%'");
}
if(CommonUtils.isNotEmpty(lv)) {
sqlExceptSelect.append(" and t3.lv like '%" +lv+ "%'");
}
sqlExceptSelect.append(" "+orderbyStr);
return dao.paginate(page, rows, select, sqlExceptSelect.toString());
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}
public List<Record> getUsers() {
try {
return Db.find("SELECT t.id,t1.name,t2.companyname,t3.job,t3.lv,t.money\n" +
"FROM money t\n" +
"LEFT JOIN money_username t1 ON t1.nameid=t.nameid\n" +
"LEFT JOIN money_company t2 ON t2.companyid=t.companyid\n" +
"LEFT JOIN money_job t3 ON t3.jobid=t.jobid\n" +
"ORDER BY t.id " );
} catch (Exception e) {
throw new RuntimeException("获取用户列表异常："+e.getMessage());
}
}
}
展示结果：