How to do related background queries from the page SQL statement |
您所在的位置：网站首页 › resultsetMetadata › How to do related background queries from the page SQL statement |
|
A few days ago, I have just made a page written SQL statement, to the case of the search for the relevant results, give you a look. If you have any deficiencies, please advise! ! ! First, the page: Because the query is not fixed, the table requires self-help generation (this is used to use "jqgrid") If you don't take advantage of "jqgrid" or you can refer to Method 2. method 1. ${sql} Determine Reset //First loaded primary table $(document).ready(function () { $("#jqGrid").jqGrid({ url:"/management/transactionFlow/informationQuery?sql=${sql}", mtype: "GET", datatype: "json", styleUI: 'Bootstrap', colModel: [ {label: '${user}', name: '${user}', autowidth: true, sortable: false}, ], 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); }); //Click the reset button $("#back").click(function () { $("#sql").empty(); });Method 2. Dynamically generate a table, but paging still needs you to write Reference URL: https: //q.cnblogs.com/q/73869/
Code: var table = ""; table += "" + "" + ""; for (var i = 0; i < date.length; i++) { table += "" + date[i] + ""; } table += ""; for (var j = 0; j < department.length; j++) { table += ""; table += "" + department[j] + ""; for (var n = 0; n < date.length; n++) { table += "" + SeriesStr[0].data[n] + ""; } } table += ""; table += ""; $("#Exceltable").html(table); 2nd,controller Query head @RequestMapping(value = "transactionFlow/informationQuery1", method = RequestMethod.GET) public String informationQuery1(@RequestParam(value = "sql", required = false) String sql, Model model) { List 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. The number of query records is prepared for paging @RequestMapping(value = "transactionFlow/informationQuery", method = RequestMethod.GET) @ResponseBody public GridData informationQuery(@RequestParam(value = "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; //Query total number of records String records1 = customerLedgersService.query1(sql); int records = Integer.parseInt(records1); GridData result = new GridData(); //Query Pipeline Details String listTotal = customerLedgersService.query2(sql, page, rows); String query2 = null; List list6 = new ArrayList(); List 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 list = Arrays.asList(value); ListIterator 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; }
Third, DAO layer (using JDBC principle) What needs to be considered is a database connection problem /** * Created by ${yucong} on 2017/7/6. */ @Repository public class QueryDaoImpl implements QueryDao { private static Logger logger = LoggerFactory.getLogger(QueryDaoImpl.class); // Query the head @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 get a connection 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 values = new ArrayList(); ResultSetMetaData rsmd = rs.getMetaData(); LinkedHashMap map = null; while (rs.next()) { // put a record in MAP map = new LinkedHashMap(); for (int i = 0; i < rsmd.getColumnCount(); i++) { String columnLabel = rsmd.getColumnLabel(i + 1); Object value = rs.getObject(i + 1); map.put(columnLabel, value); } values.add(map); } if (values.size() > 0) { for (Map m : values) { Set set = m.keySet(); for (String s : set) { sb.append(s + ";"); } } } return sb.toString(); } catch (Exception e) { e.printStackTrace(); } finally { // 6 Close resources // 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; } / / Query total record number @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 get a connection 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 values = new ArrayList(); ResultSetMetaData rsmd = rs.getMetaData(); LinkedHashMap map = null; while (rs.next()) { // put a record in MAP map = new LinkedHashMap(); for (int i = 0; i < rsmd.getColumnCount(); i++) { String columnLabel = rsmd.getColumnLabel(i + 1); Object value = rs.getObject(i + 1); map.put(columnLabel, value); } values.add(map); } if (values.size() > 0) { for (Map m : values) { Set set = m.keySet(); for (String s : set) { sb.append(s + ";"); } sb.append("|"+index++); } } return String.valueOf(index); } catch (Exception e) { e.printStackTrace(); } finally { // 6 Close resources // 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; } // Query Page Details @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 get a connection 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 values = new ArrayList(); ResultSetMetaData rsmd = rs.getMetaData(); LinkedHashMap map = null; while (rs.next()) { // put a record in MAP map = new LinkedHashMap(); for (int i = 0; i < rsmd.getColumnCount(); i++) { String columnLabel = rsmd.getColumnLabel(i + 1); Object value = rs.getObject(i + 1); map.put(columnLabel, value); } values.add(map); } if (values.size() > 0) { for (Map m : values) { Set set = m.keySet(); for (String s : set) { sb.append(m.get(s) + ";"); } 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; } }
|
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |