Jfinal 多表关联展示

最新推荐文章于 2021-01-27 02:24:19 发布

Hïgh Spïrïts丶

最新推荐文章于 2021-01-27 02:24:19 发布

阅读量1.4k

点赞数 1

本文链接：https://blog.csdn.net/Lurk_111/article/details/89814136

版权

多表查询展示

用左关联查询出的表怎么在 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">&#xe615;</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());
		}


		}
	}

展示结果：
在这里插入图片描述