我想通过部门的id查询所有员工,部门与员工是多对多关系,上主要代码
Employee.java@Entity
public class Employee implements Serializable{
private Set<Department> departments = new HashSet<Department>();//所属部门
@ManyToMany(cascade=CascadeType.REFRESH, fetch=FetchType.EAGER)
@JoinTable(name="employee_department", joinColumns=@JoinColumn(name="username"),
inverseJoinColumns=@JoinColumn(name="department_id"))
public Set<Department> getDepartments() {
return departments;
}
public void setDepartments(Set<Department> departments) {
this.departments = departments;
}
public void addDepartment(Department department){
if(!this.departments.contains(department)) this.departments.add(department);
}
public void removeDepartment(Department department){
if(this.departments.contains(department)) this.departments.remove(department);
}
}
Department.java@Entity
public class Department implements Serializable{ private Set<Employee> employees = new HashSet<Employee>();//员工
@ManyToMany(mappedBy="departments", cascade=CascadeType.REFRESH)
public Set<Employee> getEmployees() {
return employees;
}
public void setEmployees(Set<Employee> employees) {
this.employees = employees;
}EployeeAction.java public String list(){
StringBuffer hql = new StringBuffer();
hql.append("o.visible=?");
List pr = new ArrayList();
pr.add(true);
if("true".equals(ef.getQuery())) {
if(ef.getDepartment()!=null && !"".equals(ef.getDepartment())) {
System.out.println(ef.getDepartment());
hql.append(" and o.departments in (select d from Department d where id =?)");//问题是这句hql怎么写
pr.add(ef.getDepartment());//ef.getDepartment()接收回来的部门id,初始化问号 }
}
pl = new PageList<Employee>(Integer.valueOf(ReadMessageConnection.getMessageConnection("employeePageSize")), ef.getFirstIndex());
pl.setQueryResult(employeeManager.getScrollData(Employee.class, pl.getFirstindex(), pl
.getMaxresult(), hql.toString(), pr.toArray()));
return "list";
}现在我是打开员工表的,不是打开部门表,我想通过接收回来的部门id在员工表下把所有的员工都显示出来,大神来啊
Employee.java@Entity
public class Employee implements Serializable{
private Set<Department> departments = new HashSet<Department>();//所属部门
@ManyToMany(cascade=CascadeType.REFRESH, fetch=FetchType.EAGER)
@JoinTable(name="employee_department", joinColumns=@JoinColumn(name="username"),
inverseJoinColumns=@JoinColumn(name="department_id"))
public Set<Department> getDepartments() {
return departments;
}
public void setDepartments(Set<Department> departments) {
this.departments = departments;
}
public void addDepartment(Department department){
if(!this.departments.contains(department)) this.departments.add(department);
}
public void removeDepartment(Department department){
if(this.departments.contains(department)) this.departments.remove(department);
}
}
Department.java@Entity
public class Department implements Serializable{ private Set<Employee> employees = new HashSet<Employee>();//员工
@ManyToMany(mappedBy="departments", cascade=CascadeType.REFRESH)
public Set<Employee> getEmployees() {
return employees;
}
public void setEmployees(Set<Employee> employees) {
this.employees = employees;
}EployeeAction.java public String list(){
StringBuffer hql = new StringBuffer();
hql.append("o.visible=?");
List pr = new ArrayList();
pr.add(true);
if("true".equals(ef.getQuery())) {
if(ef.getDepartment()!=null && !"".equals(ef.getDepartment())) {
System.out.println(ef.getDepartment());
hql.append(" and o.departments in (select d from Department d where id =?)");//问题是这句hql怎么写
pr.add(ef.getDepartment());//ef.getDepartment()接收回来的部门id,初始化问号 }
}
pl = new PageList<Employee>(Integer.valueOf(ReadMessageConnection.getMessageConnection("employeePageSize")), ef.getFirstIndex());
pl.setQueryResult(employeeManager.getScrollData(Employee.class, pl.getFirstindex(), pl
.getMaxresult(), hql.toString(), pr.toArray()));
return "list";
}现在我是打开员工表的,不是打开部门表,我想通过接收回来的部门id在员工表下把所有的员工都显示出来,大神来啊
解决方案 »
- java查询数据库中文乱码
- struts2 在配置web.xml文件时出现的异常解决方案汇总
- 菜鸟求解答……
- JBPM4.3中org.hibernate.exception.SQLGrammarException: could not execute query
- compareTo比较器进行排序的问题
- 动态菜单,给个提示吧
- 关于jar包
- TOMCAT 跑ssh2工程 为什么会连续加载3遍
- 如何用servlet在sql server数据库的插入后执行相应动作?
- 一个关于RMI的问题。
- struts2 中的action不执行execute方法,直接跳转INPUT
- 邪了门了,点击登陆后竟然跳转到sousou页面,郁闷 请高手指点
员工和部门是多对多,现在给定一个员工,要得到该员工所属的所有部门下的所有员工?
select e from Departement d join d.employees e where e.id = ?
是这样吗?
select o from Employee o where o.visible=true and o.departments in (select d from Department d where id =?)");
然后就是不知道后面o.departments开始怎么写
实在不行,你就先查出那个人的所有部门,再通过
StringBuilder sb=new StringBuilder();
sb.append("IT");
sb.append("PMC");
sb.append("SMT");
hql.append(" and o.departments in ("+sb.toString()+")");
也是不行
就提示下面的错误
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'in (select department3_.id from Department department3_ where department3_.id='5' at line 1
could not execute query
而且我的id接收回来是53b9d6be-3201-48e2-8ae8-22dea919a6a9,怎变成5呢,这个id是通过uuid生成的
select e from Employee e where exists (select d from Department d join d.employees e where e.visible = true and e.id = ?)
select distinct e from Department d join d.employees e where d.id in (select dd.id from Employee ee join ee.departments dd where ee.id = ?)