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<String> 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();
	}