内容简介:struts实战--实现条件查询(利用dbutils)
struts实现条件查询---利用dbutils
一.在list.jsp页面修改查询组件
是否上传简历
<s:select list="#{'1':'有','2':'无'}" name="isUpload" id="isUpload" headerKey="0" headerValue="--请选择--"></s:select>
<%@ page language="java" pageEncoding="UTF-8"%> <%@taglib uri="/struts-tags" prefix="s"%> <HTML> <HEAD> <meta http-equiv="Content-Language" content="zh-cn"> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <link href="${pageContext.request.contextPath}/css/Style.css" rel="stylesheet" type="text/css" /> <script language="javascript" src="${pageContext.request.contextPath}/js/public.js"></script> <script type="text/javascript" src="${pageContext.request.contextPath }/jquery/jquery-1.4.2.js"></script> <script type="text/javascript"> function addUser(){ window.location.href = "${pageContext.request.contextPath}/user/add.jsp"; } $(function(){ // 为删除链接 加确认效果 $(".delLink").click(function(event){ var isConfirm = window.confirm("想好了吗?"); if(!isConfirm){ // 阻止提交 event.preventDefault(); } }); }); </script> </HEAD> <body> <br> <s:form action="user_list" namespace="/" method="post" theme="simple" id="Form1" name="Form1"> <table cellSpacing="1" cellPadding="0" width="100%" align="center" bgColor="#f5fafe" border="0"> <TBODY> <tr> <td class="ta_01" align="center" bgColor="#afd1f3"> <strong>查 询 条 件</strong> </td> </tr> <tr> <td> <table cellpadding="0" cellspacing="0" border="0" width="100%"> <tr> <td height="22" align="center" bgColor="#f5fafe" class="ta_01"> 用户姓名 </td> <td class="ta_01" bgColor="#ffffff"> <s:textfield name="userName" size="15" id="Form1_userName" cssClass="bg"/> </td> <td height="22" align="center" bgColor="#f5fafe" class="ta_01"> 性别: </td> <td class="ta_01" bgColor="#ffffff"> <s:select list="{'男','女'}" name="sex" id="sex" headerKey="" headerValue="--选择性别--"></s:select> </td> </tr> <tr> <td height="22" align="center" bgColor="#f5fafe" class="ta_01"> 学历: </td> <td class="ta_01" bgColor="#ffffff"> <s:select list="{'博士','硕士','研究生','本科','专科','高中'}" name="education" id="education" headerKey="" headerValue="--选择学历--"></s:select> </td> <td height="22" align="center" bgColor="#f5fafe" class="ta_01"> 是否上传简历 </td> <td class="ta_01" bgColor="#ffffff"> <s:select list="#{'1':'有','2':'无' }" name="isUpload" id="isUpload" headerKey="" headerValue="--请选择--"></s:select> </td> </tr> <tr> <td width="100" height="22" align="center" bgColor="#f5fafe" class="ta_01"> </td> <td class="ta_01" bgColor="#ffffff"> <font face="宋体" color="red"> </font> </td> <td align="right" bgColor="#ffffff" class="ta_01"><br><br></td> <td align="right" bgColor="#ffffff" class="ta_01"> <button type="submit" id="search" name="search" value="查询" class="button_view"> 查询 </button> <input type="reset" name="reset" value="重置" class="button_view"/> </td> </tr> </table> </td> </tr> <tr> <td class="ta_01" align="center" bgColor="#afd1f3"> <strong>用 户 列 表</strong> </TD> </tr> <tr> <td class="ta_01" align="right"> <button type="button" id="add" name="add" value="添加" class="button_add" onclick="addUser()"> 添加 </button> </td> </tr> <tr> <td class="ta_01" align="center" bgColor="#f5fafe"> <table cellspacing="0" cellpadding="1" rules="all" bordercolor="gray" border="1" id="DataGrid1" style="BORDER-RIGHT: gray 1px solid; BORDER-TOP: gray 1px solid; BORDER-LEFT: gray 1px solid; WIDTH: 100%; WORD-BREAK: break-all; BORDER-BOTTOM: gray 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #f5fafe; WORD-WRAP: break-word"> <tr style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; HEIGHT: 25px; BACKGROUND-COLOR: #afd1f3"> <td align="center" width="18%"> 登录名 </td> <td align="center" width="17%"> 用户姓名 </td> <td align="center" width="8%"> 性别 </td> <td align="center" width="23%"> 联系电话 </td> <td width="11%" align="center"> 学历 </td> <td width="7%" align="center"> 编辑 </td> <td width="7%" align="center"> 查看 </td> <td width="7%" align="center"> 删除 </td> </tr> <s:iterator value="users" var="user"> <tr onmouseover="this.style.backgroundColor = 'white'" onmouseout="this.style.backgroundColor = '#F5FAFE';"> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="18%"> <!-- user会push到root 同时 保存contextMap --> <s:property value="logonName"/> <s:property value="#user.logonName"/> </td> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="17%"> <s:property value="#user.userName"/> </td> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="8%"> <s:property value="#user.sex"/> </td> <td style="CURSOR: hand; HEIGHT: 22px" align="center" width="23%"> <s:property value="#user.telephone"/> </td> <td style="CURSOR: hand; HEIGHT: 22px" align="center"> <s:property value="#user.education"/> </td> <td align="center" style="HEIGHT: 22px"> <s:a action="user_editview" namespace="/"> <s:param name="userID" value="#user.userID"></s:param> <img src="${pageContext.request.contextPath}/images/i_edit.gif" border="0" style="CURSOR: hand"> </s:a> </td> <td align="center" style="HEIGHT: 22px"> <s:a action="user_view" namespace="/"> <s:param name="userID" value="#user.userID"></s:param> <img src="${pageContext.request.contextPath}/images/button_view.gif" border="0" style="CURSOR: hand"> </s:a> </td> <td align="center" style="HEIGHT: 22px"> <s:a action="user_delete" namespace="/" cssClass="delLink"> <s:param name="userID" value="#user.userID"></s:param> <img src="${pageContext.request.contextPath}/images/i_del.gif" width="16" height="16" border="0" style="CURSOR: hand"> </s:a> </td> </tr> </s:iterator> </table> </td> </tr> </TBODY> </table> </s:form> </body> </HTML>
二.添加校验
三.完成条件查询操作
1、是否上传简历,怎样在action中获取?
需要在User中添加一个属性 String isUpload
2、 在dao中怎样根据条件查询?
在这里dao层我们使用的是dbutils,所以进行条件查询的时候需要用到QueryRunner类,而他的查询方法queryRunner.query(sql,
new BeanListHandler<User>(User.class), argList.toArray());需要 sql 语句,和所有参数的这两个参数,所以我们需要解决这两个问题。
1).sql语句生成
创建一个List<Object>,在每一次判断时,直接将参数添加到集合中,
最后将集合转换成Object[],做为参数传递到query方法中。
String sql = "select * from s_user where 1=1 "; List<Object> params=new ArrayList<Object>(); String username = user.getUserName(); if (username != null && username.trim().length() > 0) { sql += " and userName like ?"; params.add("%"+username+"%"); } String sex = user.getSex(); if (sex != null && sex.trim().length() > 0) { sql += " and sex=?"; params.add(sex); } String education = user.getEducation(); if (education != null && education.trim().length() > 0) { sql += " and education=?"; params.add(education); } String isupload = user.getIsUpload(); if ("1".equals(isupload)) { sql += " and filename is not null"; } else if ("2".equals(isupload)) { sql += " and filename is null"; } QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); return runner.query(sql, new BeanListHandler<User>(User.class),params.toArray());
/** * 条件查询 * * @param user * @return */ public List<User> findByCondition(User user) { // 根据用户姓名、性别、学历、是否上传简历 组合查询 String sql = "select * from s_user where 1=1 "; List<String> argList = new ArrayList<String>(); // 参数列表 if (user.getUserName() != null && user.getUserName().trim().length() > 0) { sql += "and userName like ? "; argList.add("%" + user.getUserName() + "%"); } if (user.getSex() != null && user.getSex().trim().length() > 0) { sql += "and sex = ? "; argList.add(user.getSex()); } if (user.getEducation() != null && user.getEducation().trim().length() > 0) { sql += "and education = ? "; argList.add(user.getEducation()); } if (user.getIsUpload() != null && user.getIsUpload().trim().length() > 0) { if (user.getIsUpload().equals("1")) { // 上传简历 sql += "and filename is not null"; } else if (user.getIsUpload().equals("2")) { // 没有上传简历 sql += "and filename is null"; } } try { List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class), argList.toArray()); return users; } catch (SQLException e) { e.printStackTrace(); throw new MySQLException(e); } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- elasticsearch学习笔记高级篇(三)——在案例中实战基于bool组合多个filter条件搜索
- SQL where条件和jion on条件的详解及区别
- Python 条件语句
- React 行内条件渲染
- Golang: 条件和循环
- 漫谈条件随机场
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。