多条件查询
参考其他博客，完成了类似于知网的多条件查询
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" import="java.sql.*" import="java.util.List" import="java.util.ArrayList" import="net.sf.json.JSONArray"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style type="text/css"> #table{ font-size:20px; } input[type='text'] { font-size:20px; width: 250px; height: 30px; } input[type='button'] { font-size:20px; width: 50px; height: 30px; } </style> </head> <body style="background-color:#EBC79E"> <form action="mychaxun.jsp" method="post"> <table name="tbl" id="tbl"> <p> <tr> <td> <input type="button" id="add" value="+" onclick="insert_row()" > <select id="select1" name="select1" style='width:165px;font-size:17px'> <option value="Inamenum">需求编号</option> <option value="Ixqname">需求名称</option> <option value="Ixqbelong">归口管理</option> <option value="Iszdy">所在地域</option> <option value="Ifrdb">法人代表</option> <option value="Iclient">联系人</option> <option value="name">机构属性</option> <option value="name">技术需求名称</option> <option value="Igjz1">关键字</option> <option value="Izjze">拟投入资金总额</option> <option value="Ipeoprety">技术需求解决方式</option> <option value="Itype">科技活动类型</option> <option value="thi">学科分类</option> <option value="Ilingyu">需求技术所属领域</option> <option value="Iresult">需求状态</option> </select> <input type="text" id="SearchBox1" name="SearchBox1"> <select id="type1" name="TYPE1" style='width:50px;height:30px;fone-size:30px'> <option value="=">精确</option> <option value="like">模糊</option> </select> </td> </tr></p> </table> <div id="SearchButton"> <input type="submit" value="查询" style="width:50px;height:30px;font-size:17px;"> </div> </form> <table border="1" width="900" name="table1" id="table1"> <tr> <td>需求编号</td> <td>需求名称</td> <td>归口管理</td> <td>所在地域</td> <td>法人代表</td> <td>联系人</td> <td>机构属性</td> <td>技术需求名称</td> <td>关键字</td> <td>拟投入资金总额</td> <td>技术需求解决方式</td> <td>科技活动类型</td> <td>学科分类</td> <td>需求技术所属领域</td> <td>需求状态</td> <!-- <td>原因</td> --> <td>操 作 </td> <td>操 作 </td> </tr> <% System.out.println("mychaxun.jsp 界面信息："); System.out.println(""); request.setCharacterEncoding("utf-8"); String select[] = new String[15]; String type[] = new String[15]; String TYPE[] = new String[15]; String SearchBox[] = new String[15]; // 获取查询的条件：名字等 for(int i=0;i<15;i++) { select[i] = request.getParameter("select"+(i+1)); System.out.print(select[i]+" "); } System.out.println(" "); // 获取查询条件：并且等 for(int i=1;i<15;i++) { type[i-1] = request.getParameter("type"+(i+1)); System.out.print(type[i-1]+" "); } System.out.println(" "); // 获取查询条件:精确等 for(int i=0;i<15;i++) { TYPE[i] = request.getParameter("TYPE"+(i+1)); System.out.print(TYPE[i]+" "); } System.out.println(" "); for(int i=0;i<15;i++) { SearchBox[i] = request.getParameter("SearchBox"+(i+1)); System.out.print(SearchBox[i]+" "); } System.out.println(" "); System.out.println(" "); System.out.println("mychaxnu.jsp结束"); System.out.println(" "); %> <jsp:useBean id="db" class="DBBean.Bean" scope="page" /> <% String Inamenum = ""; String Iresult = ""; String Ixqname = ""; String Ixqbelong =""; String Ifrdb=""; String Iszdy=""; String Iclient=""; String Igjz1=""; String Izjze=""; String Ilingyu=""; String Ihzms =""; String Itype=""; String thi=""; String Ixqyyhy=""; String Ipeoprety=""; String sql= "select Inamenum,Ixqname,Iresult,Ixqbelong,Ifrdb,Iszdy,Iclient,Igjz1,Izjze,Ilingyu,Ihzms,Itype,thi,Ixqyyhy,Ipeoprety from xuqiu where " + select[0] + " " + TYPE[0]; if(TYPE[0].equals("=")) { sql += " '"+SearchBox[0]+"'"; }else { sql += " '%"+SearchBox[0]+"%'"; } int length = 0; for(int i=0;type[i]!=null;i++,length++) { } for(int i=0;i<length;i++) { sql = sql +" "; if(type[i].equals("not like")) { sql += " and " + select[i+1]+" "+type[i]+" "; }else{ sql += type[i]+" "+select[i+1]+" "+TYPE[i+1]+" "; } if(TYPE[i+1].equals("=")) { sql += " '"+SearchBox[i+1]+"'"; }else { sql += " '%"+SearchBox[i+1]+"%'"; } } System.out.println(sql); ResultSet rs = db.executeQuery(sql); while (rs.next()) { //System.out.println(rs.getString(1)); //可以输出Inamenum的所有值 比如Iname为图书馆 那么输出图书馆1 图书馆2 图书馆3 Inamenum = rs.getString(1); Iresult = rs.getString(3); Ixqname = rs.getString(2); Ixqbelong=rs.getString(4); Ifrdb=rs.getString(5); Iszdy=rs.getString(6); Iclient=rs.getString(7); Igjz1=rs.getString(8); Izjze=rs.getString(9); Ilingyu=rs.getString(10); Ihzms=rs.getString(11); Itype=rs.getString(12); thi=rs.getString(13); Ixqyyhy=rs.getString(14); Ipeoprety=rs.getString(15); out.println("<tr><td>" + Inamenum + "</td><td>" + Ixqname + "</td>" + "<td>" + Ixqbelong + "</td><td>" + Ifrdb + "</td><td>" + Iszdy + "</td><td>" + Iclient + "</td><td>" + Igjz1 + "</td><td>" + Izjze + "</td><td>" + Ilingyu + "</td><td>" + Ihzms + "</td><td>" + Itype + "</td><td>" + thi + "</td><td>" + Ixqyyhy + "</td><td>" + Ipeoprety + "</td><td>" + Iresult+ // "</td><td>" + xsreason+ "</td><td><a href='del.jsp?Inamenum=" + Inamenum + "'>删除</a> </td><td><a href='saveedit.jsp?Inamenum=" + Inamenum + "'>修改</a></td></tr>"); List list=new ArrayList(); list.add(Inamenum); list.add(Ixqname); list.add(Ixqbelong); list.add(Ifrdb); list.add(Iszdy); list.add(Iclient); list.add(Igjz1); list.add(Izjze); list.add(Ilingyu); list.add(Ihzms); list.add(Itype); list.add(thi); list.add(Ixqyyhy); list.add(Ipeoprety); list.add(Iresult); JSONArray jsonlist=JSONArray.fromObject(list); System.out.println(jsonlist.toString()); } // sql2="select xsreason from xssh where Inamenum="+"'"+Inamenum+"'"; // ResultSet rs2=db.executeQuery(sql2); // while(rs2.next()){ // xsreason=rs2.getString(1); // System.out.println("xqmanage界面的xsreason："+xsreason); // } rs.close(); db.close(); %> </body> <script type="text/javascript"> var i=1; var first = 1; var hidde_me = document.getElementById("add"); function insert_row(){ if(i>=15){ hidde_me.style.visibility="hidden"; return; } i ++; R = tbl.insertRow(); //insertRow() 方法用于在表格中的指定位置插入一个新行。 C = R.insertCell(); //insertCell() 方法用于在 HTML 表的一行的指定位置插入一个空的 <td> 元素。 C.innerHTML = "<select style='width:50px;height:30px;fone-size:30px' id='type"+i+"' name='type"+i+"'>"+ "<option value='and' >并且</option>"+ "<option value='or' >或者</option>"+ "<option value='not like' >不含</option>"+ "</select>"+ " <select style='width:165px;font-size:17px' id='select"+i+"' name='select"+i+"'>"+ "<option value='Inamenum'>需求编号</option>"+ "<option value='Ixqname'>需求名称</option>"+ "<option value='Ixqbelong'>归口管理</option>"+ "<option value='Iszdy'>所在地域</option>"+ "<option value='Ifrdb'>法人代表</option>"+ "<option value='Iclient'>联系人</option>"+ "<option value='name'>机构属性</option>"+ "<option value='name'>技术需求名称</option>"+ "<option value='Igjz1'>关键字</option>"+ "<option value='Izjze'>拟投入资金总额</option>"+ "<option value='Ipeoprety'>技术需求解决方式</option>"+ "<option value='Itype'>科技活动类型</option>"+ "<option value='thi'>学科分类</option>"+ "<option value='Ilingyu'>需求技术所属领域</option>"+ "<option value='Iresult'>需求状态</option>"+ "</select>"+" <input type='text' id='SearchBox"+i+"' name='SearchBox"+i+"' />"+ " <select style='width:50px;height:30px;fone-size:30px' id='TYPE"+i+"' name='TYPE"+i+"'>"+ "<option value='=' >精确</option>"+ "<option value='like' >模糊</option>"+ "</select> "+ "<input type='button' value='-' onclick='deleteRow(this)'>" ; } function deleteRow(obj){ if(i<=1){ return; } // deleteRow() 方法用于从表格删除指定位置的行 是从0开始 tbl.deleteRow(i-1); hidde_me.style.visibility="visible"; i--; } </script> <script type="text/javascript"> function getData(){ return json; } </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <style type="text/css"> #table{ font-size:20px; } input[type='text'] { font-size:20px; width: 250px; height: 30px; } input[type='button'] { font-size:20px; width: 50px; height: 30px; } </style> <script type="text/javascript"> </script> </head> <body style="background-color:#EBC79E"> <form action="mychaxun.jsp" method="post"> <table name="tbl" id="tbl"> <p> <tr> <td> <input type="button" id="add" value="+" onclick="insert_row()" > <select id="select1" name="select1" style='width:165px;font-size:17px'> <option value="Inamenum">需求编号</option> <option value="Ixqname">需求名称</option> <option value="Ixqbelong">归口管理</option> <option value="Iszdy">所在地域</option> <option value="Ifrdb">法人代表</option> <option value="Iclient">联系人</option> <option value="name">机构属性</option> <option value="name">技术需求名称</option> <option value="Igjz1">关键字</option> <option value="Izjze">拟投入资金总额</option> <option value="Ipeoprety">技术需求解决方式</option> <option value="Itype">科技活动类型</option> <option value="thi">学科分类</option> <option value="Ilingyu">需求技术所属领域</option> <option value="Iresult">需求状态</option> </select> <input type="text" id="SearchBox1" name="SearchBox1"> <select id="type1" name="TYPE1" style='width:50px;height:30px;fone-size:30px'> <option value="=">精确</option> <option value="like">模糊</option> </select> </td> </tr></p> </table> <div id="SearchButton"> <input type="submit" value="查询" style="width:50px;height:30px;font-size:17px;"> </div> </form> <table id="table"> <tr> <th>需求编号</th> <th>需求名称</th> <th>归口管理</th> <th>所在地域</th> <th>法人代表</th> <th>联系人</th> <th>机构属性</th> <th>技术需求名称</th> <th>关键字</th> <th>拟投入资金总额</th> <th>技术需求解决方式</th> <th>科技活动类型</th> <th>学科分类</th> <th>需求技术所属领域</th> <th>需求状态</th> </tr> </table> </body> <script type="text/javascript"> var i=1; var first = 1; var hidde_me = document.getElementById("add"); function insert_row(){ if(i>=15){ hidde_me.style.visibility="hidden"; return; } i ++; R = tbl.insertRow(); //insertRow() 方法用于在表格中的指定位置插入一个新行。 C = R.insertCell(); //insertCell() 方法用于在 HTML 表的一行的指定位置插入一个空的 <td> 元素。 C.innerHTML = "<select style='width:50px;height:30px;fone-size:30px' id='type"+i+"' name='type"+i+"'>"+ "<option value='and' >并且</option>"+ "<option value='or' >或者</option>"+ "<option value='not like' >不含</option>"+ "</select>"+ " <select style='width:165px;font-size:17px' id='select"+i+"' name='select"+i+"'>"+ "<option value='Inamenum'>需求编号</option>"+ "<option value='Ixqname'>需求名称</option>"+ "<option value='Ixqbelong'>归口管理</option>"+ "<option value='Iszdy'>所在地域</option>"+ "<option value='Ifrdb'>法人代表</option>"+ "<option value='Iclient'>联系人</option>"+ "<option value='name'>机构属性</option>"+ "<option value='name'>技术需求名称</option>"+ "<option value='Igjz1'>关键字</option>"+ "<option value='Izjze'>拟投入资金总额</option>"+ "<option value='Ipeoprety'>技术需求解决方式</option>"+ "<option value='Itype'>科技活动类型</option>"+ "<option value='thi'>学科分类</option>"+ "<option value='Ilingyu'>需求技术所属领域</option>"+ "<option value='Iresult'>需求状态</option>"+ "</select>"+" <input type='text' id='SearchBox"+i+"' name='SearchBox"+i+"' />"+ " <select style='width:50px;height:30px;fone-size:30px' id='TYPE"+i+"' name='TYPE"+i+"'>"+ "<option value='=' >精确</option>"+ "<option value='like' >模糊</option>"+ "</select> "+ "<input type='button' value='-' onclick='deleteRow(this)'>" ; } function deleteRow(obj){ if(i<=1){ return; } // deleteRow() 方法用于从表格删除指定位置的行 是从0开始 tbl.deleteRow(i-1); hidde_me.style.visibility="visible"; i--; } </script> </html>
