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

2016-12-16 18:57:50来源:CSDN作者:FJeKin人点击

一、需求


通过表单几个字段查询获取数据库数据,并在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头部,否则添加and		boolean 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 block			e.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语句不返回结果集,则返回null		return pStatement.getResultSet();	}	public void closeSQLConnect() {		if (connection != null) {			try {				connection.close();			} catch (SQLException e) {				e.printStackTrace();			}		}	}}















最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台