在页面中执行sql查询语句，easyui动态渲染结果

最新推荐文章于 2024-03-24 21:48:05 发布

J练习生

最新推荐文章于 2024-03-24 21:48:05 发布

阅读量424

点赞数 1

分类专栏： JAVA 文章标签： easyui sql 前端

本文链接：https://blog.csdn.net/m0_51458935/article/details/125808352

版权

JAVA 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

该博客介绍了一种在网页中使用EasyUI框架动态渲染SQL查询结果的方法。通过JSP、JavaScript和Java后端配合，实现用户输入SQL语句后执行查询，将查询结果以表格形式展示在页面上。主要涉及AJAX请求、数据网格(DataGrid)的初始化以及Java处理SQL查询返回的数据。

摘要由CSDN通过智能技术生成

在页面中执行sql查询语句，easyui动态渲染结果

在这里插入图片描述

jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <meta charset="UTF-8">
    <jsp:directive.include file="/view/jsp/common.jsp" />
    <title>${title_comm }&nbsp;|&nbsp; 管理</title>
</head>
<body>
<div class="easyui-layout"  style="width:100%;height:100%;">
    <div data-options="region:'north',title:'Sql查询',closedTitle:'Sql查询'" style="height:68px; background: #F4F4F4;" class="searchDiv">
        <form id="searchForm">
            <table class="searchTable">
                <tbody>
                <tr>
                    <td><label>Sql语句:</label><input type="text" name="sql" class="easyui-textbox" style="width:700px;height:26px;"/></td>
                    <td align="left" colspan="2">
                        <div>
                            <a href="javascript:;" class="easyui-linkbutton" iconCls="icon-search" id="SearchBtn">执&emsp;行&emsp;&emsp;</a>&emsp;
                            <a href="javascript:;" class="easyui-linkbutton" iconCls="icon-reload" id="ClearBtn">重&emsp;置&emsp;&emsp;</a>&emsp;
                        </div>
                    </td>
                </tr>
                </tbody>
            </table>
        </form>
    </div>
    <div data-options="region:'center',border:false" style="width: auto; height: auto;">
        <table id="DataGrid" style="height:100%;width:100%">
        </table>
    </div>
</div>
<script type="text/javascript" src="${ctx }/resources/js/SystemManage/SM_DatabaseManage/AM_Query_Sql_MyList.js"></script>
</body>
</html>

$(document).ready(function(){
    //配置datagrid
    $("#DataGrid").datagrid({
        pagination:false,
    });
    /*------查询-------*/
    $('#SearchBtn').click(function(){
        $.ajax({
            url:appPath+'/Achievements/AM_Query_Sql/GetList.do?'+$("#searchForm").serialize(),
            cache:false,
            dataType:'json',
            success:function(data){
                var datalist = null
                var columnlist = null
                if (data.resultStatus=='01') {
                    datalist = data.attr.rows
                    columnlist = data.attr.columns
                    init(columnlist)
                    $("#DataGrid").datagrid('loadData',datalist);
                } else {
                    $.messager.alert('操作提示',data.message,'error');
                }
            }
        });
    });
    /*------重置-------*/
    $('#ClearBtn').click(function(){
        $('#searchForm').form('clear');
    });
})
function init(columnlist){//columnlist: 列头
    $("#DataGrid").datagrid({
        cache : false,
        method : "post",
        columns:[columnlist],
    })
}

java


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.PathResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping(value = "/Achievements/AM_Query_Sql")
public class AM_Query_Sql_Controller {

    @Autowired
    private DataSource dataSource;

    @RequestMapping("/MyList")
    public String MyList(HttpServletRequest request, Model model){
        return "SystemManage/SM_DatabaseManage/AM_Query_Sql_MyList";
    }

    @RequestMapping("/GetList")
    @ResponseBody
    public Object GetList(AM_Query_SqlVO am_query_sqlVO) throws Exception{
        String sql = am_query_sqlVO.getSql();
        JsResult jsResult = new JsResult();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);//执行sql
            List<ColumnData> columnData = getColumnData(rs);//前端展示动态列，需要获取列头信息
            List<Map<String, Object>> list = getList(rs);//获取结果集
            Map<String, Object> map = new HashMap<>();
            map.put("rows",list);
            map.put("columns",columnData);
            jsResult.setAttr(map);
            jsResult.setResultStatus("01");
            jsResult.setMessage("执行成功");
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
            String message = e.getMessage();
            jsResult.setResultStatus("02");
            jsResult.setMessage(message);
        }
        return jsResult;
    }

    private List<ColumnData> getColumnData(ResultSet rs) throws Exception{
        List<ColumnData> list = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();//列数
        for (int i = 1; i <= columnCount; i++) { //遍历每一列
            ColumnData columnData = new ColumnData();
            //获得指定列的列名
            String columnName = metaData.getColumnName(i);
            columnData.setField(columnName);
            columnData.setAlign("center");
            columnData.setSortable(true);
            columnData.setWidth("120");
            columnData.setTitle(columnName);
            list.add(columnData);
        }
        return list;
    }

    private List<Map<String, Object>> getList(ResultSet rs) throws Exception{
        List<Map<String, Object>> list = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();//列数
        while (rs.next()){//遍历每一行，从数据的第一行开始，不包含列头
            Map<String, Object> map = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) { //遍历每一列
                //获得指定列的列名
                String columnName = metaData.getColumnName(i);
                //获得指定列的列值
                Object columnValue = rs.getObject(i);
                map.put(columnName,columnValue);
            }
            list.add(map);
        }
        return list;
    }
}