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;
}