java mysql数据去重_java使用MySQL和HQL数据去重

思路

先查询出满足某种条件的数据的最小ID，然后删除最小ID以外的数据就实现了去重

实例

查询最小ID的重复数据

select * from oms_relation_model orm

where orm.fd_id=

(

select min(t.fd_id) from oms_relation_model t

where orm.fd_ekp_id=t.fd_ekp_id and orm.fd_ekp_id=t.fd_ekp_id

)

;

删除操作

delete from oms_relation_model s

where s.fd_id not in (

select orm.fd_id from oms_relation_model orm

where orm.fd_id=

(

select min(t.fd_id) from oms_relation_model t

where orm.fd_ekp_id=t.fd_ekp_id and orm.fd_ekp_id=t.fd_ekp_id

)

);

HQL

/**

* 不重复的id列表

*

* @return

*/

private List getUniqueIds() {

Session session = getBaseDao().getHibernateSession();

String sql = "select fdId from OmsRelationModel orm"

+

" where orm.fdId in" +

" (select min(fdId) from OmsRelationModel t where orm.fdEkpId=t.fdEkpId and orm.fdAppPkId=t.fdAppPkId)";

Query query = session.createQuery(sql);

List result = query.list();

logger.info("查询重复数据,list=>" + JSONUtils.valueToString(result));

return result;

}

/**

* 删除重复的

*

* @param idList

*/

private void delSame(List idList) {

Session session = getBaseDao().getHibernateSession();

String sql = "delete from OmsRelationModel where fdId not in (:ids)";

Query query = session.createQuery(sql);

query.setParameterList("ids", idList);

int result = query.executeUpdate();

logger.info("查询重复数据,list=>" + JSONUtils.valueToString(result));

session.flush();

session.clear();

}