首页 > 代码库 > 如何从页面SQL语句进行相关后台查询
如何从页面SQL语句进行相关后台查询
前几天刚刚做了一个页面书写sql语句，到后台查询相关结果的案例，给大家分享一下。有不足之处请大家多指教！！！
一、页面：因为查询的表不固定，所以表格需要自助生成（这是利用了“jqGrid”）
若是不利用“jqGrid”也可以参考方法2.
方法1.
<div>
<nobr>
<div>
<form method="get" action="/management/transactionFlow/informationQuery1">
<div class="row">
<div class="col-md-10">
<textarea name="sql" STYLE="width: 1000px;height: 100px" id="sql"
onfocus="ClearDefault(this)" placeholder="输入查询语句"
onblur="AddDefault(this)">${sql}</textarea>
</div>
</div>
<div class="row">
<div class="col-md-2">
<button type=‘submit‘ class="btn btn-primary btn-block" id="sure">确定
</button>
</div>
<div class="col-md-2">
<button id="back" type="reset" class="btn btn-primary btn-block">重置
</button>
</div>
<div>
<table id="jqGrid"></table>
<div id="jqGridPager"></div>
</div>
<script type="text/javascript">
//第一次加载的主表
$(document).ready(function () {
$("#jqGrid").jqGrid({
url:"/management/transactionFlow/informationQuery?sql=${sql}",
mtype: "GET",
datatype: "json",
styleUI: ‘Bootstrap‘,
colModel: [
<c:forEach items="${list3}" var="user" varStatus="status">
{label: ‘${user}‘, name: ‘${user}‘, autowidth: true, sortable: false},
<%--<c:if test = "${status.last}">--%>
<%--{label: ‘${user}‘, name:‘${user}‘, autowidth: true, sortable: false}--%>
<%--</c:if>--%>
</c:forEach>
],
viewrecords: true,
height: 400,
shrinkToFit: false,
rowNum: 10,
autowidth: true,
pager: "#jqGridPager",
page: 1
}).trigger("reloadGrid");
return false;
var len = $("#jqGrid").getGridParam("width");
// $("#jqGrid").setGridParam().hideCol("id");
$("#jqGrid").setGridWidth(len);
});
//点击重置按钮
$("#back").click(function () {
$("#sql").empty();
});
</script>
</content>
方法2.动态生成表格，但是分页还需自己在写一下
参考网址：https://q.cnblogs.com/q/73869/
代码：
var table = "<table id=\"date\" name=\"TableName\" class=‘table table-striped table-bordered table-hover‘ >";
table += "<thead><tr><th>" + "" + "</th>";
for (var i = 0; i < date.length; i++) {
table += "<th >" + date[i] + "</th>";
}
table += "</tr></thead><tbody>";
for (var j = 0; j < department.length; j++) {
table += "<tr>";
table += "<td>" + department[j] + "</td>";
for (var n = 0; n < date.length; n++) {
table += "<td>" + SeriesStr[0].data[n] + "</td>";
}
}
table += "</tr>";
table += "</tbody></table>";
$("#Exceltable").html(table);
二、controller
1.查询表头
@RequestMapping(value = "http://www.mamicode.com/transactionFlow/informationQuery1", method = RequestMethod.GET)
public String informationQuery1(@RequestParam(value = "http://www.mamicode.com/sql", required = false) String sql, Model model) {
List<String> list3 = new LinkedList<>();
String query1 = customerLedgersService.query(sql);
Map map = new HashMap();
if (query1 != null) {
String[] key = query1.split(";");
for (int i = 0, len = key.length; i < len; i++) {
list3.add(key[i]);
}
model.addAttribute("sql", sql);
model.addAttribute("list3", list3);
return "/complement/informationQuery";
} else {
model.addAttribute("sql", sql);
return "/complement/informationQuery";
}
}
2.查询记录数为分页做准备
@RequestMapping(value = "http://www.mamicode.com/transactionFlow/informationQuery", method = RequestMethod.GET) @ResponseBody public GridData informationQuery(@RequestParam(value = "http://www.mamicode.com/sql", required = false) String sql, @RequestParam(value = "page", required = false) Integer page, @RequestParam(value = "rows", required = false) Integer rows ) { List list7 = new LinkedList<>(); String queryValue = null; //查询总计录数 String records1 = customerLedgersService.query1(sql); int records = Integer.parseInt(records1); GridData result = new GridData(); //查询分页详情 String listTotal = customerLedgersService.query2(sql, page, rows); String query2 = null; List<String> list6 = new ArrayList<>(); List<String> list5 = new ArrayList(); if (listTotal != null) { String[] keyq3 = listTotal.split("%"); for (int i = 0, len = keyq3.length; i < len; i++) { list6.add(keyq3[i]); } for (int m = 0; m < list6.size(); m++) { queryValue = list6.get(m); String[] value = queryValue.split(";"); List<String> list = Arrays.asList(value); ListIterator<String> li = list.listIterator(); while (li.hasNext()) { Object obj = li.next(); if (obj.equals("0E-8")) { li.set("0.00000000"); } if (obj.equals("null")) { li.set(""); } } list7.add(list); } } long totaPage = records % rows == 0 ? records / rows : records / rows + 1; result.setPage(page); result.setRecords(records); result.setTotal((int) totaPage); if (list7 != null) { result.getRows().addAll(list7); } return result; }
三、dao层（利用了JDBC原理）
需要考虑的是数据库连接问题
/**
* Created by ${yucong} on 2017/7/6.
*/
@Repository
public class QueryDaoImpl implements QueryDao {
private static Logger logger = LoggerFactory.getLogger(QueryDaoImpl.class);
//查询表头
@Override
public String selectSql(String sql) {
DataSource ds = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer();
int index = 1;
//1 获得连接
try {
if(sql == null){
return null;
}
InitialContext initialContext = new InitialContext();
ds = (DataSource) initialContext.lookup("jdbc/account-structure");
if (ds != null) {
conn = ds.getConnection();
}
if(!sql.contains(";")){
sql=sql+";";
}
if(!sql.contains("limit") && !sql.contains("LIMIT")){
if(sql.contains(";")){
sql = sql.replaceAll(";", "");
}
sql = sql + " limit 0, 1;";
}
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
List<LinkedHashMap<String, Object>> values = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
LinkedHashMap<String, Object> map = null;
while (rs.next()) {
//把一条记录放入Map中
map = new LinkedHashMap<String, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
Object value = http://www.mamicode.com/rs.getObject(i + 1);";");
}
}
}
return sb.toString();
} catch (Exception e) {
e.printStackTrace();
} finally {
//6关闭资源
// JDBCUtils.close(conn, pstmt, rs);
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
try {
pstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
rs.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
return null;
}
//查询总记录数
@Override
public String selectSql1(String sql) {
DataSource ds = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer();
int index = 0;
//1 获得连接
try {
if(sql == null){
return null;
}
InitialContext initialContext = new InitialContext();
ds = (DataSource) initialContext.lookup("jdbc/account-structure");
if (ds != null) {
conn = ds.getConnection();
}
if(!sql.contains(";")){
sql=sql+";";
}
if(!sql.contains("limit") && !sql.contains("LIMIT")){
if(sql.contains(";")){
sql = sql.replaceAll(";", "");
}
sql = sql + " limit 0, 5000;";
}
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
List<LinkedHashMap<String, Object>> values = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
LinkedHashMap<String, Object> map = null;
while (rs.next()) {
//把一条记录放入Map中
map = new LinkedHashMap<String, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
Object value = http://www.mamicode.com/rs.getObject(i + 1);";");
}
sb.append("|"+index++);
}
}
return String.valueOf(index);
} catch (Exception e) {
e.printStackTrace();
} finally {
//6关闭资源
// JDBCUtils.close(conn, pstmt, rs);
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
try {
pstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
rs.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
return null;
}
//查询分页详情
@Override
public String selectSq2(String sql, Integer page, Integer rows) {
DataSource ds = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer();
StringBuffer sb1 = new StringBuffer();
int index = 1;
//1 获得连接
try {
InitialContext initialContext = new InitialContext();
ds = (DataSource) initialContext.lookup("jdbc/account-structure");
if (ds != null) {
conn = ds.getConnection();
}
String sql1=null;
StringBuffer sql2=null;
if(!sql.contains(";")){
sql=sql+";";
}
sql1=sql.substring(0,sql.length()-1);
sql2=sb1.append(sql1+" "+"limit"+" "+"?"+","+"?;");
pstmt = conn.prepareStatement(sql2.toString());
int startIndex = (page - 1) * rows;
pstmt.setInt(1,startIndex);
pstmt.setInt(2,rows);
rs = pstmt.executeQuery();
List<LinkedHashMap<String, Object>> values = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
LinkedHashMap<String, Object> map = null;
while (rs.next()) {
//把一条记录放入Map中
map = new LinkedHashMap<String, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
Object value = http://www.mamicode.com/rs.getObject(i + 1);";");
}
sb.append("%");
}
}
return sb.toString();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
try {
pstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
rs.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
return null;
}
}
如何从页面SQL语句进行相关后台查询
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。