使用struts2完成的增、删、改、查的小项目，有分页和多条件查询，MySQL数据库

MySQL数据库语句

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.5.56 : Database - company_manage
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`company_manage` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `company_manage`;

/*Table structure for table `department` */

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (
  `departmentid` int(11) NOT NULL AUTO_INCREMENT,
  `department` varchar(30) NOT NULL,
  PRIMARY KEY (`departmentid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `department` */

insert  into `department`(`departmentid`,`department`) values (1,'行政部'),(2,'市场部'),(3,'财务部'),(4,'研发部');

/*Table structure for table `post` */

DROP TABLE IF EXISTS `post`;

CREATE TABLE `post` (
  `postid` int(11) NOT NULL AUTO_INCREMENT,
  `post` varchar(30) NOT NULL,
  PRIMARY KEY (`postid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `post` */

insert  into `post`(`postid`,`post`) values (1,'管理员'),(2,'经理'),(3,'职员');

/*Table structure for table `users` */

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `userpass` varchar(30) NOT NULL,
  `departmentid` int(11) NOT NULL,
  `postid` int(11) NOT NULL,
  `sex` int(11) NOT NULL,
  `desc` varchar(200) NOT NULL,
  PRIMARY KEY (`userid`),
  KEY `FK_post` (`postid`),
  KEY `FK_department` (`departmentid`),
  CONSTRAINT `FK_department` FOREIGN KEY (`departmentid`) REFERENCES `department` (`departmentid`),
  CONSTRAINT `FK_post` FOREIGN KEY (`postid`) REFERENCES `post` (`postid`)
) ENGINE=InnoDB AUTO_INCREMENT=1025 DEFAULT CHARSET=utf8;

/*Data for the table `users` */

insert  into `users`(`userid`,`username`,`userpass`,`departmentid`,`postid`,`sex`,`desc`) values (1013,'78654','4564',2,1,0,'789'),(1014,'78645','456',1,1,0,'7864'),(1015,'56','',1,1,0,''),(1016,'45477','7777',1,1,0,'777'),(1017,'746786','456456456',1,1,0,'45645'),(1018,'7645','45',1,1,1,'7456'),(1019,'hk','hjk',1,1,0,'454'),(1020,'456111','111',1,1,0,'111'),(1021,'454111','111',1,1,0,'111'),(1022,'1312','1123',1,1,1,'132'),(1023,'test','qqqq',1,1,1,'qqqwe');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

项目关键代码：

多条件查询分页方法

public List<Users> Select_Fenye(int page,int pagesize, String username,
			int postid, int departmentid, int sex) {
		// TODO Auto-generated method stub
		//表示每页之前的记录数
		int pageno=0;
		//计算每页之前记录数
		//当前页数x每页显示的行数
		//从0开始，第一页0x行数为0
		pageno=(page-1) * pagesize;
		
		List<Users> users = new ArrayList<Users>();
		StringBuffer sql=new StringBuffer("SELECT * FROM users where 1=1");
		if(username!=null&&!username.equals("")){
			sql.append(" AND username LIKE '%"+username+"%'");
		}
		if(departmentid!=-1){
			sql.append(" AND departmentid="+departmentid);
		}
		if(postid!=-1){
			sql.append(" AND postid="+postid);
		}
		if(sex!=-1){
			sql.append(" AND sex="+sex);
		}
		//
		sql.append(" LIMIT "+pageno+","+pagesize);
		System.out.println(sql.toString());
		String sql1=sql.toString();
		try {
			rs=super.execQuery(sql1, null);
			while(rs.next()){
				Users user = new Users();
				user.setUserid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setUserpass(rs.getString(3));
				//外键对象
				DepartmentDaoimpl dmi = new DepartmentDao();
				user.setDepartment(dmi.Select_Departmentid_Department(rs.getInt(4)));
				//外键对象
				PostDaoimpl pdi = new PostDao();
				user.setPost(pdi.Select_Postid_Post(rs.getInt(5)));
				user.setSex(rs.getInt(6));
				user.setDesc(rs.getString(7));
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}

jsp页面查询条件

<s:form action="SelectAllPost" method="post">
    			<s:textfield name="Name" label="姓名" value="%{Name}"></s:textfield>
    			<s:radio name="gender" list="#{'1':'男','0':'女',-1:'不限'}"  label="性别" value="%{gender}"></s:radio>
    			<s:select list="depts" listKey="departmentid"
					listValue="department" name="dept" label="部门" headerKey="-1" headerValue="--不限--"></s:select>
				<s:select list="post" listKey="postid"
					listValue="post" name="roles" label="职位" headerKey="-1" headerValue="--不限--"></s:select>
    			<s:submit value="提交"></s:submit><a href="SelectAllPost.action">查询全部</a>
    	 	</s:form>

分页jsp相关

    这里属性用的是bootstrap的属性

<table style="margin:0 auto;">
 	<s:set name="url" value="'&Name='+Name+'&gender='+gender+'&dept='+dept+'&roles='+roles"></s:set>
    <tr class="font1">  
            <td align="right">  
            	<ul class="pagination">
            		<li> 
            			<a href="SelectAllPost?pageNow=1<s:property value='url'/>">首页</a> 
            		</li>
            		<li>
            			<s:if test="page1.hasPre">  
                    		<a href="SelectAllPost?pageNow=<s:property value="page1.pageNow-1"/><s:property value='url'/>">上一页</a>  
                		</s:if>  
                		<s:else>  
                    		<a href="SelectAllPost?pageNow=1<s:property value='url'/>">上一页</a>  
                		</s:else>  
            		</li>
            		<s:iterator  begin="1" end="page1.totalPage" status="L">
            		<s:if test="#L.index+1==page1.pageNow">
            			<li class="active">
            				<a href="SelectAllPost?pageNow=<s:property value='#L.index+1'/><s:property value='url'/>">
            					<s:property value='#L.index+1'/>
            				</a>
            			</li>
            		</s:if>
            		<s:else>
            			<li>
            				<a href="SelectAllPost?pageNow=<s:property value='#L.index+1'/><s:property value='url'/>">
            					<s:property value='#L.index+1'/>
            				</a>
            			</li>
            		</s:else>
            		</s:iterator>  
					<li>
						<s:if test="page1.hasNext">  
                    		<a href="SelectAllPost/SelectAllPost?pageNow=<s:property value="page1.pageNow+1"/><s:property value='url'/>">下一页</a>  
                		</s:if>  
                		<s:else>
                    		<a href="SelectAllPost?pageNow=<s:property value="page1.totalPage"/><s:property value='url'/>">下一页</a>  
                		</s:else> 
					</li>
					<li>
						<a href="SelectAllPost?pageNow=<s:property value="page1.totalPage"/><s:property value='url'/>">尾页</a>  
					</li>
				</ul>
				<br/>
            	共有 <s:property value="page1.totalSize"/> 条记录，
            	当前是  <s:property value="page1.pageNow"/>/ <s:property value="page1.totalPage"/>页!
                
            </td> 
       </table>