在页面中执行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 } | 管理</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">执 行  </a> 
<a href="javascript:;" class="easyui-linkbutton" iconCls="icon-reload" id="ClearBtn">重 置  </a> 
</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>
js
$(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;
}
}