OA项目之Mybatis多表链接查询
xml文件：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjsxt.mapper.EmployeeMapper">
<select id="selbyemptype" parameterType="int" resultType="Employee">
select * from employee where emptype=#{empType}
</select>
<resultMap type="Employee" id="selAll">
<id property="empId" column="empid"/>
<result property="realName" column="realname"/>
<result property="sex" column="sex"/>
<result property="hireDate" column="hiredate"/>
<result property="phone" column="phone"/>
<collection property="mgr" ofType="Employee">
<id property="empId" column="mgrId"/>
<result property="realName" column="realname"/>
</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="selAll" 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
order by e.empid
</select>
</mapper>
实体类：
package com.bjsxt.entity;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* 员工类
*
* 如何表示员工所属一个部门、一个岗位、一个上级、甚至多个下级的信息呢？
* 在数据库中通过外键来实现：deptno、posid，mgrid
* 在Java类中通过属性关联来实现
*
* private Department dept; //员工所属部门 不仅包含部门的编号，还包含其他信息
private Position position;
private Employee mgr;//上级领导的信息
private List<Employee> empList = new ArrayList<Employee>();//下级的信息，可能多个
* @author Administrator
*
*/
public class Employee {
private String empId;//员工编号
private String password;//密码
private String realName;//真实姓名
private String sex;//性别
private Date birthDate;//出生日期
private Date hireDate;//入职日期
private Date leaveDate;//离职日期
private int onDuty;//是否在职 0-离职 1-在职
private int empType;//员工类型 1.普通员工 2.管理人员 含经理、总监、总裁等 3.管理员
private String phone;//联系方式
private String qq;
private String emerContactPerson;//紧急联系人信息
private String idCard;//身份证号码
/*
private int deptno;
private int posId;
private String mgrId;
*/
private Department dept; //员工所属部门 不仅包含部门的编号，还包含其他信息
private Position position;
private Employee mgr;//上级领导的信息
private List<Employee> empList = new ArrayList<Employee>();//下级的信息，可能多个
public Employee() {
super();
}
public Employee(String empId, String password, String realName, String sex,
Date birthDate, Date hireDate, Date leaveDate, int onDuty,
int empType, String phone, String qq, String emerContactPerson,
String idCard, Department dept, Position position, Employee mgr) {
super();
this.empId = empId;
this.password = password;
this.realName = realName;
this.sex = sex;
this.birthDate = birthDate;
this.hireDate = hireDate;
this.leaveDate = leaveDate;
this.onDuty = onDuty;
this.empType = empType;
this.phone = phone;
this.qq = qq;
this.emerContactPerson = emerContactPerson;
this.idCard = idCard;
this.dept = dept;
this.position = position;
this.mgr = mgr;
}
public Employee(String empId, String password, String realName, String sex,
Date birthDate, Date hireDate, Date leaveDate, int onDuty,
int empType, String phone, String qq, String emerContactPerson,
String idCard, Department dept, Position position, Employee mgr,
List<Employee> empList) {
super();
this.empId = empId;
this.password = password;
this.realName = realName;
this.sex = sex;
this.birthDate = birthDate;
this.hireDate = hireDate;
this.leaveDate = leaveDate;
this.onDuty = onDuty;
this.empType = empType;
this.phone = phone;
this.qq = qq;
this.emerContactPerson = emerContactPerson;
this.idCard = idCard;
this.dept = dept;
this.position = position;
this.mgr = mgr;
this.empList = empList;
}
public String getEmpId() {
return empId;
}
public void setEmpId(String empId) {
this.empId = empId;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthDate() {
return birthDate;
}
public void setBirthDate(Date birthDate) {
this.birthDate = birthDate;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
public Date getLeaveDate() {
return leaveDate;
}
public void setLeaveDate(Date leaveDate) {
this.leaveDate = leaveDate;
}
public int getOnDuty() {
return onDuty;
}
public void setOnDuty(int onDuty) {
this.onDuty = onDuty;
}
public int getEmpType() {
return empType;
}
public void setEmpType(int empType) {
this.empType = empType;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getQq() {
return qq;
}
public void setQq(String qq) {
this.qq = qq;
}
public String getEmerContactPerson() {
return emerContactPerson;
}
public void setEmerContactPerson(String emerContactPerson) {
this.emerContactPerson = emerContactPerson;
}
public String getIdCard() {
return idCard;
}
public void setIdCard(String idCard) {
this.idCard = idCard;
}
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
public Position getPosition() {
return position;
}
public void setPosition(Position position) {
this.position = position;
}
public Employee getMgr() {
return mgr;
}
public void setMgr(Employee mgr) {
this.mgr = mgr;
}
public List<Employee> getEmpList() {
return empList;
}
public void setEmpList(List<Employee> empList) {
this.empList = empList;
}
@Override
public String toString() {
return "Employee [empId=" + empId + ", password=" + password
+ ", realName=" + realName + ", sex=" + sex + ", birthDate="
+ birthDate + ", hireDate=" + hireDate + ", leaveDate="
+ leaveDate + ", onDuty=" + onDuty + ", empType=" + empType
+ ", phone=" + phone + ", qq=" + qq + ", emerContactPerson="
+ emerContactPerson + ", idCard=" + idCard + ", dept=" + dept
+ ", position=" + position + ", mgr=" + mgr + ", empList="
+ empList + "]";
}
}
Mapper接口：
package com.bjsxt.mapper;
import java.util.List;
import com.bjsxt.entity.Employee;
public interface EmployeeMapper {
List<Employee> selbyemptype(int empType);
List<Employee> selAll();
}
service方法：
package com.bjsxt.service.impl;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.bjsxt.dao.EmployeeDao;
import com.bjsxt.dao.impl.EmployeeDaoImpl;
import com.bjsxt.entity.Employee;
import com.bjsxt.mapper.EmployeeMapper;
import com.bjsxt.service.EmployeeService;
import com.bjsxt.util.MyBatisUtil;
public class EmployeeServiceImpl implements EmployeeService{
private EmployeeDao empDao = new EmployeeDaoImpl();
SqlSession session = MyBatisUtil.getSession();
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
@Override
public int add(Employee emp) {
return this.empDao.save(emp);
}
@Override
public List<Employee> findEmpByType(int i) {
List<Employee> selbyemptype = mapper.selbyemptype(i);
session.close();
return selbyemptype;
}
@Override
public List<Employee> selAll() {
List<Employee> selAll = mapper.selAll();
session.close();
return selAll;
}
}
service接口：
package com.bjsxt.service;
import java.util.List;
import com.bjsxt.entity.Employee;
public interface EmployeeService {
/**
* 添加员工
* @param emp
* @return
*/
public int add(Employee emp);
/**
* 查询指定类型的员工
* @param i
* @return
*/
public List<Employee> findEmpByType(int i);
List<Employee> selAll();
}
Servlet实现类：
package com.bjsxt.servlet;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.bjsxt.entity.Department;
import com.bjsxt.entity.Employee;
import com.bjsxt.entity.Position;
import com.bjsxt.service.DepartmentService;
import com.bjsxt.service.EmployeeService;
import com.bjsxt.service.PositionService;
import com.bjsxt.service.impl.DepartmentServiceImpl;
import com.bjsxt.service.impl.EmployeeServiceImpl;
import com.bjsxt.service.impl.PositionServiceImol;
public class EmployeeServlet extends BaseServlet {
public void selinfo(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String empid = request.getParameter("empId");
String deptno = request.getParameter("deptno");
System.out.println("=============="+deptno);
String shireDate = request.getParameter("hireDate");
String onduty = request.getParameter("onDuty");
DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
Date hiredate =null;
try {
hiredate = df.parse(shireDate);
} catch (ParseException e) {
e.printStackTrace();
}
//获取所有的部门信息
DepartmentService ds=new DepartmentServiceImpl();
List<Department> deptList = ds.seAll();
request.setAttribute("deptList", deptList);
EmployeeService es = new EmployeeServiceImpl();
List<Employee> selAll = es.selAll();
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);
}
public void selAll(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//获取所有的部门信息
DepartmentService ds=new DepartmentServiceImpl();
List<Department> deptList = ds.seAll();
request.setAttribute("deptList", deptList);
EmployeeService es = new EmployeeServiceImpl();
List<Employee> selAll = es.selAll();
request.setAttribute("selAll", selAll);
request.getRequestDispatcher("/system/empList.jsp").forward(request, response);
}
public void findsth(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//获取所有的部门信息
DepartmentService ds=new DepartmentServiceImpl();
List<Department> seAll = ds.seAll();
request.setAttribute("seAll", seAll);
//获取所有的岗位信息
PositionService ps=new PositionServiceImol();
List<Position> selpos = ps.selpos();
request.setAttribute("selpos", selpos);
//获取上级员工
EmployeeService empService = new EmployeeServiceImpl();
List<Employee> mgrList = empService.findEmpByType(2);//1 基层员工 2 各级管理人员
request.setAttribute("mgrList",mgrList);
//跳转到system/empAdd.jsp
request.getRequestDispatcher("/system/empAdd.jsp").forward(request, response);
}
public void add(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//获取员工的信息
String empId = request.getParameter("empId");
String password ="123456";
String realName = request.getParameter("realName");
String sex = request.getParameter("sex");
//日期类型的处理
String sbirthDate = request.getParameter("birthDate");
String shireDate = request.getParameter("hireDate");
String sleaveDate = request.getParameter("leaveDate");
Date birthDate= null,hireDate = null,leaveDate = null;
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
birthDate = sdf.parse(sbirthDate);
} catch (ParseException e) {
e.printStackTrace();
}
try {
hireDate = sdf.parse(shireDate);
} catch (ParseException e) {
e.printStackTrace();
}
try {
leaveDate = sdf.parse(sleaveDate);
} catch (ParseException e) {
System.out.println("暂时没有离职日期~");
}
//整数的处理
int onDuty = Integer.parseInt(request.getParameter("onDuty"));
int empType = Integer.parseInt(request.getParameter("empType"));
String phone = request.getParameter("phone");
String qq = request.getParameter("qq");
String emerContactPerson = request.getParameter("emerContactPerson");
String idCard = request.getParameter("idCard");
//对象的处理
int deptno = Integer.parseInt(request.getParameter("deptno"));
Department dept = new Department();
dept.setDeptno(deptno);
int posid = Integer.parseInt(request.getParameter("posid"));
Position position=new Position();
position.setPosid(posid);
String mgrId = request.getParameter("mgrId");
Employee mgr = new Employee();
mgr.setEmpId(mgrId);//!!!
//调用业务层完成添加操作
Employee emp = new Employee(empId, password, realName, sex, birthDate, hireDate, leaveDate, onDuty, empType, phone, qq, emerContactPerson, idCard, dept, position, mgr);
EmployeeService empService = new EmployeeServiceImpl();
int n = empService.add(emp);
//根据结果进行页面跳转
if(n>0){
response.sendRedirect(request.getContextPath()+"/system/empList.jsp");
}
else{
request.setAttribute("error", "添加员工失败");
request.getRequestDispatcher("/system/empAdd.jsp").forward(request, response);
}
}
}
实现效果：
浙公网安备 33010602011771号