hibernate根据字段去除重复记录

public List<Employee> getUniqueNameByHqlCorrect(){

List<Object[]> list=employeeDao.createQuery("from Employee e inner join (select max(b.id) as id  from  Employee b group by b.name) c on (c.id=e.id)", new Object[]{}).list();

   for(Object[] rows:list){   
System.out.println(": " + rows[0] + "值: " + rows[1]);     
        }  
   return null;
 }

from Employee e inner join (select max(b.id) as id  from  Employee b group by b.name) c on (c.id=e.id)

这条hql会不会写错,为什么会报.异常

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 57 [from com.dhhc.cs.oa.entity.system.Employee e inner join (select max(b.id) as id  from  com.dhhc.cs.oa.entity.system.Employee b group by b.name) c on c.id=e.id]
 at

 

 

 

以下两种方法可以实现根据字段去除重复记录

 

 

/**
  * hibernate sql根据某一字段的值不重复,用in的方法不健壮，效率低，当in里的id值大于1000个报错
  * @return
  */
 public List<Employee> getUniqueNameByHqlCommon(){
  List<Employee> list=employeeDao.createQuery("from Employee e where e.id in(select max(b.id) from  Employee b group by b.name)", new Object[]{}).list();
  for(Employee entity:list){   
   System.out.println(": " +entity.getName() );     
  }  
  return null;
 }

 

 

 

/**
  * 使用联接,相对于in的用法更合适
  * @return
  */
 public List<Employee> getUniqueNameBySqlCorrect(){
  Session session=this.getSessionFactory().openSession();
  String sql="select name,email from sys_employee e inner join (select max(b.id) as id from  sys_employee b group by b.name) c on (c.id=e.id)";
  List<Object[]> list=session.createSQLQuery(sql).list();
  for(Object[] rows:list){   
            System.out.println(": " + rows[0] + "值: " + rows[1]);     
        } 
  session.close();
  return null;
 }

转自：http://www.myexception.cn/software-architecture-design/665519.html