多条件查询----补发周一内容（六级让我忽略了JAVA）
周一测试多条件查询
要求仿照知网高级查询页面重构期中考试多条件查询功能，可以根据志愿者姓名、性别、民族、政治面目、服务类别、注册时间六种条件实现模糊查询，输出结果以列表形式显示，显示姓名、性别，民族、政治面目基本信息，点击列表中的姓名，跳转到志愿者个人详细信息页面。
难点在于sql语句的拼接和动态添加删除文本框和下拉框。 为了简便我选择用table表格，因为它删除时可以简单的用其函数删除任意一行，动态添加我是用字符串拼接，将html代码用innetHTML方法写入。因为不知道条件框有几个，所以得获取条件框的个数，而且sql语句也得拼接。条件有三个，与或非，查询内容，精确或者模糊，所以在拼接时需要注意，如果在与或非条件，如果是与或，则直接在sql语句后拼接，但如果是非（not like） 所以需要在查询内容后拼接，比如你要查姓名非性别，就得写出 “select * from 表名 where name = ... and sex not like ...”而不是 “select * from 表名 where name 。 ...not like sex ...”，这样是sql语句的错误。在精确查询是，如果是精确则为=，如果是模糊则为not like。 具体看代码
查询界面：
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <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"> <% Object message = request.getAttribute("message"); if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <% }%> <form action="Servlet1?method=search" method="post"> <table name="tbl" id="tbl"> <tr> <td> <input type="button" value="+" onclick="insert_row()"> <input type="button" value="-" onclick="deleteRow(this)"> <select id="select1" name="select1" style='width:120px;font-size:17px'> <option value="name">姓名</option> <option value="sex">性别</option> <option value="minzu">民族</option> <option value="zhengzhi">政治面目</option> <option value="fuwu">服务类别</option> <option value="time">注册时间</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> </table> <div id="SearchButton"> <input type="submit" value="查询" style="width:50px;height:30px;font-size:17px;"> </div> </form> <table id="table"> <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> </tr> <c:forEach items="${list}" var = "volunteer"> <tr> <td><a href="Servlet1?method=searchAtName&name=${volunteer.name}">${volunteer.name}</a></td><td></td><td></td><td></td> <td>${volunteer.sex}</td><td></td><td></td><td></td> <td>${volunteer.minzu}</td><td></td><td></td><td></td> <td>${volunteer.zhengzhi}</td><td></td><td></td><td></td> </tr> </c:forEach> </table> </body> <script type="text/javascript"> var i=1; var first = 1; function insert_row(){ if(i>=6){ 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:120px;font-size:17px' id='select"+i+"' name='select"+i+"'>"+ "<option value='name' >姓名</option>"+ "<option value='sex' >性别</option>"+ "<option value='minzu'>民族</option>"+ "<option value='zhengzhi'>政治面目</option>"+ "<option value='fuwu'>服务类别</option>"+ "<option value='time'>注册时间</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>"; } function deleteRow(obj){ if(i<=1){ return; } // deleteRow() 方法用于从表格删除指定位置的行 是从0开始 tbl.deleteRow(i-1); i--; } </script> </html>
Servlet层！！！！！重点：
package Servlet; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import Bin.Volunteer; import Dao.Dao; /** * Servlet implementation class Servlet1 */ @WebServlet("/Servlet1") public class Servlet1 extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if(method.equals("search")) { search(req,resp); }else if(method.equals("searchAtName")) { searchAtName(req,resp); } } // 通过多条件查询 private void search(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); String select[] = new String[6]; String type[] = new String[5]; String TYPE[] = new String[6]; String SearchBox[] = new String[6]; // 获取查询的条件：名字等 for(int i=0;i<6;i++) { select[i] = request.getParameter("select"+(i+1)); } // 获取查询条件：并且等 for(int i=1;i<6;i++) { type[i-1] = request.getParameter("type"+(i+1)); } // 获取查询条件:精确等 for(int i=0;i<6;i++) { TYPE[i] = request.getParameter("TYPE"+(i+1)); } // 获取写的内容 for(int i=0;i<6;i++) { SearchBox[i] = request.getParameter("SearchBox"+(i+1)); } String sql= "select * from volunteer 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); List<Volunteer> list = Dao.Search(sql); if(list.size()!=0) { request.setAttribute("list", list); request.getRequestDispatcher("search1.jsp").forward(request, response); }else { request.setAttribute("message", "不存在符号该条件的志愿者"); request.getRequestDispatcher("search1.jsp").forward(request, response); } } // 通过单条件查询 private void searchAtName(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{ request.setCharacterEncoding("utf-8"); String name = request.getParameter("name"); Volunteer volunteer = Dao.searchAtName(name); if(volunteer!=null) { request.setAttribute("volunteer", volunteer); request.getRequestDispatcher("searchResult.jsp").forward(request, response); } } }
DBUtil层
package DBUtil; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class Util { // 获取连接 // 获取连接方法 public static Connection getConnection() { String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/tree?useSSL=false&useUnicode=true&characterEncoding=utf8"; String username = "root"; String password = "a3685371"; Connection con = null; try { Class.forName(driver); con = DriverManager.getConnection(url,username,password); }catch(Exception e) { throw new RuntimeException(e); } return con; } // 释放资源 public static void release(Connection con,PreparedStatement ps) { if(ps!=null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(con!=null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
Dao层
package Dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.junit.Test; import Bin.Tree; import Bin.Volunteer; import DBUtil.Util; public class Dao { // 通过多条件查询 public static List<Volunteer> Search(String SQL){ Connection con = null; PreparedStatement ps = null; ResultSet rs = null; List<Volunteer> list = new ArrayList<Volunteer>(); try { // 获取连接 con = Util.getConnection(); // 编写sql语句 String sql = SQL; // 执行sql语句 ps = con.prepareStatement(sql); // 执行查询操作 rs = ps.executeQuery(); while(rs.next()) { Volunteer volunteer = new Volunteer(); volunteer.setName(rs.getString(1)); volunteer.setSex(rs.getString(2)); volunteer.setMinzu(rs.getString(3)); volunteer.setZhengzhi(rs.getString(4)); list.add(volunteer); } return list; } catch (SQLException e) { e.printStackTrace(); } try { if(rs!=null) { rs.close(); } Util.release(con, ps); } catch (SQLException e) { e.printStackTrace(); } return null; } // 通过名字查询 public static Volunteer searchAtName(String name) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { // 获取连接 con = Util.getConnection(); // 编写sql语句 String sql = "select * from volunteer where name = ?"; // 执行sql语句 ps = con.prepareStatement(sql); // 设置参数 ps.setString(1, name); // 执行查询操作 rs = ps.executeQuery(); while(rs.next()) { Volunteer volunteer = new Volunteer(); volunteer.setName(rs.getString(1)); volunteer.setSex(rs.getString(2)); volunteer.setMinzu(rs.getString(3)); volunteer.setZhengzhi(rs.getString(4)); volunteer.setFuwu(rs.getString(5)); volunteer.setTime(rs.getString(6)); return volunteer; } } catch (SQLException e) { e.printStackTrace(); } try { if(rs!=null) { rs.close(); } Util.release(con, ps); } catch (SQLException e) { e.printStackTrace(); } return null; } }
数据库：
运行结果：
总结：关键还是在于sql语句的拼接和动态添加条件。由于六级的到来，让我对JAVA有点忽略了，现在赶快补上。
如果有大佬发现错误或者更好的方法，请留言支持，向您学习！！！！
