struts实战--实现条件查询(利用dbutils)

SIHAIloveYAN

浏览: 119939 次
性别:
来自: 深圳

最近访客更多访客>>

limengna845567

Seven.Q

caoweichen520

liaoke0123

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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语句生成

2）.参数怎样传递?
创建一个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);
		}

	}

分享到：