在各个列上使用JPA中的DISTINCT关键字 [英] Using DISTINCT keyword in JPA on individual columns

问题描述

我正在读取一些数据库中的某些值，这些值非常规范化（我无法控制）。该呼叫将检索大学部门的公告，并且如果用户在多个部门（可能），则会为这些用户多次返回相同的结果。但是，有些部门可能会有不同的公告，而有些则相同。

有没有一种方法可以让我在JPA中的各个列上使用DISTINCT关键字？这是我目前查询的内容：

 字符串jpql =SELECT DISTINCT annoucement FROM Announcment announcement
 + WHERE（announcement.date< =：now AND announcement.endDate> =：now）
 +AND announcement.approved = true AND announcement.departmentId IN（：departmentIDs）; 
 
 TypedQuery< Announcement> query = entityManager.createQuery（jpql，
 Announcement.class）; 
 query.setParameter（now，new Date（））; 
 query.setParameter（departmentIDs，departmentIDs）; 
  

departmentID的值可能不同，但公告，日期等完全相同。此查询返回具有重复值的公告。

解决方案

两种方式我想出了您的问题：

1. 从...中选择不同的annoucement.x，annoucement.y，annoucement.z
   ...（不带depId）
   
   

   然后构造一个公告。但是你
   丢失了持久对象及其引用。如果需要的话，你必须再次通过Dao对象加载它们
   。在你的Annoucement类中覆盖equals（）[hashCode（）） ，
   当然，在等于（），depId应该超出比较。获得
   列表，然后将列表转换为Set。你有
   独特对象

希望它有帮助

I am reading some values from a database that is horribly un-normalized (which I can't control). The call retrieves announcements for university departments, and if a user is in multiple departments (which is possible), then the same results are returned multiple times for these users. However, some departments might have different announcements, while some have the same.

Is there a way for me to use the DISTINCT keyword in JPA on individual columns? This is what I currently have for the query:

String jpql = "SELECT DISTINCT annoucement FROM Announcment announcement "
                + "WHERE (announcement.date <= :now AND announcement.endDate >= :now) "
                + "AND announcement.approved = true AND announcement.departmentId IN (:departmentIDs)";

TypedQuery<Announcement> query = entityManager.createQuery(jpql,
                Announcement.class);
query.setParameter("now", new Date());
query.setParameter("departmentIDs", departmentIDs);

The departmentID value might be different, but the announcement, dates, etc. are all identical. This query returns announcements that have duplicate values.

解决方案

two ways I come up with your problem:

1. "select distinct annoucement.x, annoucement.y, annoucement.z ...(without depId) from..."

   then construct an announcement. but you lost the persistent object and its references. you have to load them again by your Dao objects if you need.

2. override equals() [hashCode() too] in your Annoucement class, of course, in equals(), depId should be out of the comparison. getting the list as you did, then convert the list to Set. you got "Distinct" objects

hope it helps

这篇关于在各个列上使用JPA中的DISTINCT关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！