How to perform a background query from a page SQL statement

A few days ago just made a page to write SQL statements, to the background to query the relevant results of the case, to share with you. There is a shortage of places please give us more advice!!!

First, page: Because the query table is not fixed, so the table needs self-help generation (this is the use of "Jqgrid")

If you do not use "Jqgrid" can also refer to Method 2.

Method 1.

<div> <nobr> <div> <form method= "Get" action= "/management/transactionflow/inform AtionQuery1 "> <divclass= "Row" > <divclass= "Col-md-10" > <textarea name= "sql" style= "width:1000px;height:100px" id= "SQL"onfocus= "Cleardefault (this)" placeholder= "Input query statement"onblur= "Adddefault (this)" >${sql}</textarea> </div> </div> <divclass= "Row" > <divclass= "Col-md-2" > <button type= ' Submit 'class= "Btn btn-primary btn-block" id= "sure" >Determine</button> </div> <divclass= "Col-md-2" > <button id= "Back" type= "reset"class= "btn btn-primary btn-block" > Reset</button> </div> <div> <table id= "Jqgrid" ></table> <d IV id= "Jqgridpager" ></div> </div> <script type= "Text/javascript" >//primary table loaded for the first time$ (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:ifTest = "${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);            }); //Click the reset button$ ("#back"). Click (function () {$ ("#sql"). empty ();        }); </script> </content>

Method 2. Generate the table dynamically, but the paging needs to be written by itself.

Reference URL: https://q.cnblogs.com/q/73869/

Code:

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>"; ta ble + = "<td>" + department[j] + "</td>"; for (var n = 0; n < date.length; n++) {table + = "<td>" + Seri Esstr[0].data[n] + "</td>";}} Table + = "</tr>"; table + = "</tbody></table>"; $ ("#Exceltable"). html (table);

Second,Controller

1. Querying a table header

    @RequestMapping (value = "Transactionflow/informationquery1", method = requestmethod.get) public    String  InformationQuery1 (@RequestParam (value = "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. Query the number of records to prepare for paging

@RequestMapping (value = "Transactionflow/informationquery", method =requestmethod.get) @ResponseBody PublicGriddata informationquery (@RequestParam (value = "SQL", required =false) String sql, @RequestParam (value= "Page", required =false) Integer page, @RequestParam (value= "Rows", required =false) (Integer rows) {List list7=NewLinkedlist<>(); String QueryValue=NULL;//Query Total Record CountString RECORDS1 =customerledgersservice.query1 (SQL); intRecords =Integer.parseint (RECORDS1); Griddata result=NewGriddata ();//Query Page DetailsString listtotal =customerledgersservice.query2 (SQL, page, rows); String Query2=NULL; List<String> List6 =NewArraylist<>(); List<String> LIST5 =NewArrayList (); if(Listtotal! =NULL) {string[] keyq3= Listtotal.split ("%");  for(inti = 0, len = keyq3.length; i < Len; i++) {List6.add (keyq3[i]); }             for(intm = 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); }        }        LongTotapage = 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); }        returnresult; }

Third, DAO layer (using the JDBC principle)

There are database connectivity issues to consider

/** * Created by ${yucong} on 2017/7/6. */@Repositorypublic class Querydaoimpl implements Querydao {private static Logger Logger = Loggerfactory.getlogger (Que        Rydaoimpl.class);//Query Header @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 the 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<linkedhashmap<string, object>> values = new arraylist<> ();            ResultSetMetaData RSMD = Rs.getmetadata ();            linkedhashmap<string, object> map = null;                while (Rs.next ()) {//Put a record in map map = new linkedhashmap<string, object> (); 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<string, object> m:values) {Se                    t<string> 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 number of records @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 the 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<linkedhashmap<string, object>> values = new arraylist<> ();            ResultSetMetaData RSMD = Rs.getmetadata ();            linkedhashmap<string, object> map = null;                while (Rs.next ()) {//Put a record in map map = new linkedhashmap<string, object> (); 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<string, object> m:values) {Se                    t<string> 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 Paging 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 getting the 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<linkedhashmap<string, object>> values = new arraylist<> ();            ResultSetMetaData RSMD = Rs.getmetadata ();            linkedhashmap<string, object> map = null;             while (Rs.next ()) {//Put a record in map   Map = new linkedhashmap<string, object> ();                    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<string, object> m:values) {Se                    t<string> 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; }}

How to perform a background query from a page SQL statement