С каким столбцом работает DISTINCT в JPA и можно ли его изменить?
Вот пример запроса JPA с использованием DISTINCT:
select DISTINCT c from Customer c
Что не имеет большого смысла - на каком столбце основано отличное? Указано ли это на сущности как аннотация, потому что я не смог ее найти?
Я хотел бы указать столбец, чтобы сделать различие, что-то вроде:
select DISTINCT(c.name) c from Customer c
Я использую MySQL и Hibernate.
Обновление: см. проголосовавший ответ.
В настоящее время я устарел. Только для исторических целей.
Отличие в HQL обычно требуется в Joins, а не в простых примерах, подобных вашим собственным.
См. также Как создать отдельный запрос в HQL
Вы близко.
select DISTINCT(c.name) from Customer c
@Entity
@NamedQuery(name = "Customer.listUniqueNames",
query = "SELECT DISTINCT c.name FROM Customer c")
public class Customer {
...
private String name;
public static List<String> listUniqueNames() {
return = getEntityManager().createNamedQuery(
"Customer.listUniqueNames", String.class)
.getResultList();
}
}
Я согласен с ответом казанаки, и это помогло мне. Я хотел выбрать весь объект, поэтому я использовал
select DISTINCT(c) from Customer c
В моем случае у меня есть отношение "многие ко многим", и я хочу загрузить объекты с коллекциями в один запрос.
Я использовал LEFT JOIN FETCH, и в конце мне пришлось сделать результат разным.
Как я объяснил в этой статье, в зависимости от базового типа запроса JPQL или Criteria API, DISTINCT имеет два значения в JPA.
Для скалярных запросов, которые возвращают скалярную проекцию, например, следующий запрос:
List<Integer> publicationYears = entityManager
.createQuery(
"select distinct year(p.createdOn) " +
"from Post p " +
"order by year(p.createdOn)", Integer.class)
.getResultList();
LOGGER.info("Publication years: {}", publicationYears);
Ключевое слово DISTINCT должно быть передано в базовый оператор SQL, потому что мы хотим, чтобы механизм БД отфильтровывал дубликаты до возврата набора результатов:
SELECT DISTINCT
extract(YEAR FROM p.created_on) AS col_0_0_
FROM
post p
ORDER BY
extract(YEAR FROM p.created_on)
-- Publication years: [2016, 2018]
Для запросов сущностей DISTINCT имеет другое значение.
Без использования DISTINCT, запрос, подобный следующему:
List<Post> posts = entityManager
.createQuery(
"select p " +
"from Post p " +
"left join fetch p.comments " +
"where p.title = :title", Post.class)
.setParameter(
"title",
"High-Performance Java Persistence eBook has been released!"
)
.getResultList();
LOGGER.info(
"Fetched the following Post entity identifiers: {}",
posts.stream().map(Post::getId).collect(Collectors.toList())
);
собирается присоединиться к post_comment post и post_comment следующим образом:
SELECT p.id AS id1_0_0_,
pc.id AS id1_1_1_,
p.created_on AS created_2_0_0_,
p.title AS title3_0_0_,
pc.post_id AS post_id3_1_1_,
pc.review AS review2_1_1_,
pc.post_id AS post_id3_1_0__
FROM post p
LEFT OUTER JOIN
post_comment pc ON p.id=pc.post_id
WHERE
p.title='High-Performance Java Persistence eBook has been released!'
-- Fetched the following Post entity identifiers: [1, 1]
Но родительские post записи дублируются в наборе результатов для каждой ассоциированной post_comment строки. По этой причине в List сущностей Post будут содержаться повторяющиеся ссылки на сущности Post.
Чтобы исключить ссылки на сущности Post, нам нужно использовать DISTINCT:
List<Post> posts = entityManager
.createQuery(
"select distinct p " +
"from Post p " +
"left join fetch p.comments " +
"where p.title = :title", Post.class)
.setParameter(
"title",
"High-Performance Java Persistence eBook has been released!"
)
.getResultList();
LOGGER.info(
"Fetched the following Post entity identifiers: {}",
posts.stream().map(Post::getId).collect(Collectors.toList())
);
Но затем DISTINCT также передается в SQL-запрос, и это совсем не желательно:
SELECT DISTINCT
p.id AS id1_0_0_,
pc.id AS id1_1_1_,
p.created_on AS created_2_0_0_,
p.title AS title3_0_0_,
pc.post_id AS post_id3_1_1_,
pc.review AS review2_1_1_,
pc.post_id AS post_id3_1_0__
FROM post p
LEFT OUTER JOIN
post_comment pc ON p.id=pc.post_id
WHERE
p.title='High-Performance Java Persistence eBook has been released!'
-- Fetched the following Post entity identifiers: [1]
DISTINCT в SQL-запрос, EXECUTION PLAN выполнит дополнительную фазу сортировки, которая добавляет издержки, не принося никакого значения, поскольку комбинации родитель-потомок всегда возвращают уникальные записи из-за дочернего столбца PK:
Unique (cost=23.71..23.72 rows=1 width=1068) (actual time=0.131..0.132 rows=2 loops=1)
-> Sort (cost=23.71..23.71 rows=1 width=1068) (actual time=0.131..0.131 rows=2 loops=1)
Sort Key: p.id, pc.id, p.created_on, pc.post_id, pc.review
Sort Method: quicksort Memory: 25kB
-> Hash Right Join (cost=11.76..23.70 rows=1 width=1068) (actual time=0.054..0.058 rows=2 loops=1)
Hash Cond: (pc.post_id = p.id)
-> Seq Scan on post_comment pc (cost=0.00..11.40 rows=140 width=532) (actual time=0.010..0.010 rows=2 loops=1)
-> Hash (cost=11.75..11.75 rows=1 width=528) (actual time=0.027..0.027 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on post p (cost=0.00..11.75 rows=1 width=528) (actual time=0.017..0.018 rows=1 loops=1)
Filter: ((title)::text = 'High-Performance Java Persistence eBook has been released!'::text)
Rows Removed by Filter: 3
Planning time: 0.227 ms
Execution time: 0.179 ms
Чтобы исключить фазу сортировки из плана выполнения, нам нужно использовать HINT_PASS_DISTINCT_THROUGH запроса HINT_PASS_DISTINCT_THROUGH JPA:
List<Post> posts = entityManager
.createQuery(
"select distinct p " +
"from Post p " +
"left join fetch p.comments " +
"where p.title = :title", Post.class)
.setParameter(
"title",
"High-Performance Java Persistence eBook has been released!"
)
.setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false)
.getResultList();
LOGGER.info(
"Fetched the following Post entity identifiers: {}",
posts.stream().map(Post::getId).collect(Collectors.toList())
);
И теперь SQL-запрос не будет содержать DISTINCT но дубликаты ссылок на сущности Post будут удалены:
SELECT
p.id AS id1_0_0_,
pc.id AS id1_1_1_,
p.created_on AS created_2_0_0_,
p.title AS title3_0_0_,
pc.post_id AS post_id3_1_1_,
pc.review AS review2_1_1_,
pc.post_id AS post_id3_1_0__
FROM post p
LEFT OUTER JOIN
post_comment pc ON p.id=pc.post_id
WHERE
p.title='High-Performance Java Persistence eBook has been released!'
-- Fetched the following Post entity identifiers: [1]
И план выполнения подтвердит, что на этот раз у нас больше нет фазы дополнительной сортировки:
Hash Right Join (cost=11.76..23.70 rows=1 width=1068) (actual time=0.066..0.069 rows=2 loops=1)
Hash Cond: (pc.post_id = p.id)
-> Seq Scan on post_comment pc (cost=0.00..11.40 rows=140 width=532) (actual time=0.011..0.011 rows=2 loops=1)
-> Hash (cost=11.75..11.75 rows=1 width=528) (actual time=0.041..0.041 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on post p (cost=0.00..11.75 rows=1 width=528) (actual time=0.036..0.037 rows=1 loops=1)
Filter: ((title)::text = 'High-Performance Java Persistence eBook has been released!'::text)
Rows Removed by Filter: 3
Planning time: 1.184 ms
Execution time: 0.160 ms
Я бы использовал функцию выражения конструктора JPA. См. Также следующий ответ:
Выражение конструктора JPQL - org.hibernate.hql.ast.QuerySyntaxException: таблица не отображается
Следуя примеру в вопросе, это будет что-то вроде этого.
SELECT DISTINCT new com.mypackage.MyNameType(c.name) from Customer c