OA项目之mybatis动态查询

类似于三个条件，可以全部选择，也可以选择几个条件进行查询

Mapper.xml文件：

<resultMap type="Employee" id="selAll">
		<id property="empId" column="empid"/>
		<id property="hireDate" column="hiredate"/>
		<id property="sex" column="sex"/>
		<id property="phone" column="phone"/>
		<result property="realName" column="realname"/>
			
		<collection property="mgr" ofType="employee">
			<id property="empId" column="mgrid"/>
			<result property="realName" column="name"/>
		</collection>
		
		<collection property="position"  ofType="position">
			<id property="posid" column="posid"/>
			<result property="pname" column="pname"/>	
		</collection>
		
		<collection property="dept" ofType="Department">
			<id property="deptno" column="deptno"/>
			<result property="deptname" column="deptname"/>
		</collection>
	</resultMap>
<select id="selbyd" resultMap="selAll">
	 			select e.empid,e.realname,e.sex,e.hiredate,e.phone,d.deptname,p.pname,e2.realname
					from employee e left join dept d on e.deptno=d.deptno 
								left join position p on e.posid=p.posid
								left join employee e2 on e.mgrid=e2.empid
				<where>
						and e.onduty=#{onduty}
			  			<if test="deptno!=0">
			  				and d.deptno=#{deptno}
			  			</if>
			  			
			  			<if test="empid!=null and empid!=''">
  							<bind name="empid" value="'%'+ empid +'%'"/>
  							and	e.empid like #{empid}
  						</if>	
			  			<if test="hiredate!=null and hiredate!=''">
			  				and e.hiredate &gt;=#{hiredate}
			  			</if>
  				</where>					
	 	</select>

中间用到了模糊查询。

Mapper接口：

List<Employee> selbyd(@Param("empid") String empid,@Param("deptno") int deptno,@Param("hiredate") Date hiredate,@Param("onduty") int onduty);

 

Service方法：

	List<Employee> selbyd(String empid, int deptno, Date hiredate, int onduty);

ServiceImpl:

@Override
	public List<Employee> selbyd(String empid,int deptno, Date hiredate,
			int onduty) {
		List<Employee> selbyd = mapper.selbyd(empid, deptno, hiredate, onduty);	
		session.close();
		return selbyd;
	}

 

Servlet:

public void selinfo(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {	
		String empid = request.getParameter("empId");
		int deptno =Integer.parseInt(request.getParameter("deptno"));	
		String shireDate = request.getParameter("hireDate");
		int onduty =Integer.parseInt(request.getParameter("onDuty"));
		DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
		Date hiredate =null;
		try {
		 hiredate = df.parse(shireDate);
		
		} catch (ParseException e) {
			System.out.println("");
		}
		//获取所有的部门信息
		DepartmentService ds=new DepartmentServiceImpl();
		List<Department> deptList = ds.seAll();
		request.setAttribute("deptList", deptList);
		EmployeeService es = new EmployeeServiceImpl();
		List<Employee> selAll = es.selbyd(empid, deptno, hiredate, onduty);
		request.setAttribute("empid", empid);
		request.setAttribute("deptno", deptno);
		request.setAttribute("onduty", onduty);
		request.setAttribute("hireDate", shireDate);
		request.setAttribute("selAll", selAll);
		request.getRequestDispatcher("/system/empList.jsp").forward(request, response);
		
	}

 

查询所有：

 

条件查询：