表单POST请求服务器，通过多条件查询获取数据库数据

一、需求

通过表单几个字段查询获取数据库数据，并在jsp页面展示出来

笔者遇到的主要难点：用户查询的时候，表单字段有可能为空字符串("")或者实际值，MySQL查询语句如何写？

想必很多人都会说（包括我之前的想法），用!=""、if(判断语句，正确执行语句，错误执行语句)之类的方法，但是都会遇到类似的问题：

1.第一种方式，如果实际值(value!="")真的不等于空字符串，那么你想获取符合该条件的数据，应该再添加(and 接入号码=value)，这样带来后果是数据不为空字符串的都会被查询出来。

2.第二种方式同样，当实际值(value!="")，赋予(接入号码=value)这没问题。当(value=="")，那么应该赋予什么值，什么条件能够查询出全部数据，或者说什么语句能把这查询条件去掉。

个人能力有限，通过以上方式都未能实现，有哪位大神想到更好的方法麻烦留言告知一二，谢谢！

二、解决方案

导入相关jar包，下载地址：http://repo1.maven.org/maven2/

实体类：

package com.jekin.base;public class SimInfo {private String accessPhone;private String iccid;private String deviceNumber;private int allFlow;private int useFlow;public SimInfo(String accessPhone, String iccid, String deviceNumber, int allFolw, int useFlow){this.accessPhone = accessPhone;this.iccid = iccid;this.deviceNumber = deviceNumber;this.allFlow = allFolw;this.useFlow = useFlow;}public String getAccessPhone() {return accessPhone;}public void setAccessPhone(String accessPhone) {this.accessPhone = accessPhone;}public String getIccid() {return iccid;}public void setIccid(String iccid) {this.iccid = iccid;}public String getDeviceNumber() {return deviceNumber;}public void setDeviceNumber(String deviceNumber) {this.deviceNumber = deviceNumber;}public int getAllFlow() {return allFlow;}public void setAllFlow(int allFlow) {this.allFlow = allFlow;}public int getUseFlow() {return useFlow;}public void setUseFlow(int useFlow) {this.useFlow = useFlow;}}

jsp页面(这个自己写，哈哈)：

<form class="form-inline" role="form"><div class="form-group form_group"><label for="exampleInputEmail2">接入号码</label><input type="tel" class="form-control" name="phone"></div><div class="form-group form_group"><label for="exampleInputEmail2">ICCID</label><input type="tel" class="form-control" name="iccid"></div><div class="form-group form_group"><label for="exampleInputEmail2">设备号</label><input type="tel" class="form-control" name="number"></div><div class="form-group form_group"><label for="exampleInputEmail2">开始日期</label><input type="date" class="form-control" name="start_time"></div><div class="form-group form_group"><label for="exampleInputEmail2">结束日期</label><input type="date" class="form-control" name="end_time"></div><button type="button" class="btn btn-default form_button">查询</button></form>

<script type="text/javascript" src="../js/jquery-3.1.1.min.js"></script><script type="text/javascript">$("button[type='button']").click(function() {$.ajax({type: 'POST',url: "/IoTSystem/servlet/FindServlet",cache: false,data: "phone=" + $("input[name='phone']").val() + "&iccid=" + $("input[name='iccid']").val() + "&number=" + $("input[name='number']").val() + "&start_time=" + $("input[name='start_time']").val()+ "&end_time=" + $("input[name='end_time']").val(),dataType: 'json',success: function(msg) {$.each(msg, function(key) {$('tbody').before("<tr>" +"<td class='text-center'><input type='checkbox' /></td>" +"<td class='text-center'>" + msg[key].accessPhone + "</td>" +"<td class='text-center'>" + msg[key].iccid + "</td>" +"<td class='text-center'>" + msg[key].deviceNumber + "</td>" +"<td class='text-center'>" + msg[key].allFlow + "</td>" +"<td class='text-center'>" + msg[key].useFlow + "</td>" +"</tr>");});},error: function(xhr, status, statusText) {console.log(statusText);}});});</script>

Servlet类：

package com.jekin.common;import java.io.IOException;public class FindServlet extends HttpServlet {public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {response.setCharacterEncoding("UTF-8");response.setContentType("application/json;character=utf-8");response.setHeader("Cache-Control", "no-cache");Map<String, String> map = new HashMap<String, String>();map.put("接入号码", request.getParameter("phone"));map.put("ICCID", request.getParameter("iccid"));map.put("设备号", request.getParameter("number"));//查询语句StringBuffer stringBuffer = new StringBuffer("SELECT `接入号码`,ICCID,`设备号`,`总流量(G)`,`使用流量(M)` FROM sim_info ");//获取查询字段名Set<String> keys = map.keySet();//判断条件语句是否第一个，是添加where头部，否则添加andboolean isFrist = true;for (String key : keys) {String value = map.get(key);if(!value.equals("") && value != null){if(isFrist){stringBuffer.append("WHERE " + key + " = " + map.get(key));isFrist = false;}else{stringBuffer.append(" AND " + key + " = " + map.get(key));}}}//时间段String start_time_form = request.getParameter("start_time");String end_time_form = request.getParameter("end_time");if(!start_time_form.equals("") && !end_time_form.equals("")){if(isFrist){stringBuffer.append("WHERE `时间` BETWEEN '" + start_time_form + "' AND '" + end_time_form + "'");}else{stringBuffer.append(" AND `时间` BETWEEN '" + start_time_form + "' AND '" + end_time_form + "'");}}MysqlUtil mysqlUtil = new MysqlUtil();List<SimInfo> simInfos = new ArrayList<SimInfo>();try {ResultSet rs = mysqlUtil.execSQL(stringBuffer.toString());while (rs.next()) {String accessPhone = rs.getString("接入号码").trim();String iccid = rs.getString("ICCID").trim();String deviceNumber = rs.getString("设备号").trim();int allFolw = Integer.valueOf(rs.getString("总流量(G)").trim());int useFlow = Integer.valueOf(rs.getString("使用流量(M)").trim());SimInfo simInfo = new SimInfo(accessPhone, iccid, deviceNumber, allFolw, useFlow);simInfos.add(simInfo);}JSONArray jsonArray = JSONArray.fromObject(simInfos);PrintWriter printWriter = response.getWriter();printWriter.write(jsonArray.toString());printWriter.flush();printWriter.close();rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {mysqlUtil.closeSQLConnect();}}}

数据库操作类：

package com.jekin.utils;import java.sql.Connection;public class MysqlUtil {private final String dbDriver = "com.mysql.jdbc.Driver";private final String url = "jdbc:mysql://127.0.0.1:3306/iot";private final String username = "root";private final String password = "admin123";private Connection connection = null;public MysqlUtil() {try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {e.printStackTrace();System.out.println("数据库驱动加载失败");}try {connection = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();System.out.println("数据库连接失败");}}// 执行各种SQL语句的方法public ResultSet execSQL(String sql, Object... args) throws SQLException {PreparedStatement pStatement = connection.prepareStatement(sql);// 为pStement对象设置SQL参数值for (int i = 0; i < args.length; i++) {pStatement.setObject(i + 1, args[i]);}pStatement.execute();// 返回结果集，如果执行的SQL语句不返回结果集，则返回nullreturn pStatement.getResultSet();}public void closeSQLConnect() {if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}}