Чтобы выбрать данные из таблицы использовал такой запрос


после того, как у таблицы появились зависимые - количество повторяемых строк стало равно количеству записей в зависимых таблицах. На мой взгляд какое-то странное поведение. почему так происходит?

пришлось писать так:



хотя всегда считал, что это одно и то же.

Blazkowicz Там тоже .createCriteria(Candidate.class).list() ?

да, конечно.

BlazkowiczКакие конкретно запросы повторяются и какой маппинг на зависимости?.
BlazkowiczПохоже что проблема N+1.
не исключено

Blazkowicz,

Если кандидату(с id==2) добавить 4(например) скилла, то количество кандидатов кандидатов c id==2 в выборке будет равно 4.

Blazkowicz,
HQL
[quote авторHibernate: select candidate0_.id as id1_1_, candidate0_.name as name2_1_, candidate0_.surname as surname3_1_, candidate0_.author_id as author7_1_, candidate0_.creation_date as creation4_1_, candidate0_.phone as phone5_1_, candidate0_.cv_url as cv6_1_ from candidate candidate0_
Hibernate: select user0_.id as id1_0_0_, user0_.name as name2_0_0_, user0_.surname as surname3_0_0_, user0_.password as password4_0_0_ from author user0_ where user0_.id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
[/quote]
CRITERIA
авторHibernate: select this_.id as id1_1_2_, this_.name as name2_1_2_, this_.surname as surname3_1_2_, this_.author_id as author7_1_2_, this_.creation_date as creation4_1_2_, this_.phone as phone5_1_2_, this_.cv_url as cv6_1_2_, user2_.id as id1_0_0_, user2_.name as name2_0_0_, user2_.surname as surname3_0_0_, user2_.password as password4_0_0_, skills3_.candidate_id as candidat1_1_4_, skill4_.id as skill2_2_4_, skill4_.id as id1_9_1_, skill4_.name as name2_9_1_ from candidate this_ left outer join author user2_ on this_.author_id=user2_.id left outer join candidate_skill skills3_ on this_.id=skills3_.candidate_id left outer join skill skill4_ on skills3_.skill_id=skill4_.id

Blazkowicz,
вроде всё правильно написал:
состояние бд:






.createQuery("from Candidate").list();
результат:
автор<list>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>2</id>
<name>erwerwe</name>
<surname>888888888888888888888888888888888888888888888888</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<skills>
<id>1</id>
<name>skill1</name>
</skills>
<skills>
<id>2</id>
<name>skill2</name>
</skills>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>3</id>
<name>John</name>
<surname>Snow</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<date>2013-07-11T10:44:27.397+04:00</date>
<phone>33-33-33</phone>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>4</id>
<name>777</name>
<surname>777</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>5</id>
<name>777</name>
<surname>777</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>6</id>
<name>John</name>
<surname>Snow</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<date>2013-07-16T12:30:42.727+04:00</date>
<phone>33-33-33</phone>
</element>
</list>
.createCriteria(Candidate.class).list();
результат:
автор<list>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>2</id>
<name>erwerwe</name>
<surname>888888888888888888888888888888888888888888888888</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<skills>
<id>2</id>
<name>skill2</name>
</skills>
<skills>
<id>1</id>
<name>skill1</name>
</skills>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>2</id>
<name>erwerwe</name>
<surname>888888888888888888888888888888888888888888888888</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<skills>
<id>2</id>
<name>skill2</name>
</skills>
<skills>
<id>1</id>
<name>skill1</name>
</skills>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>3</id>
<name>John</name>
<surname>Snow</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<date>2013-07-11T10:44:27.397+04:00</date>
<phone>33-33-33</phone>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>4</id>
<name>777</name>
<surname>777</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>5</id>
<name>777</name>
<surname>777</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>6</id>
<name>John</name>
<surname>Snow</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<date>2013-07-16T12:30:42.727+04:00</date>
<phone>33-33-33</phone>
</element>
</list>

P.S. это результат вызова getAll метода

авторEAGER в маппинге это не самая лучшая практика. Потом его не возможно будет поменять на lazy в отдельных случаях.
это временная мера

Blazkowiczredwhite90вроде всё правильно написал:

Тут 14572086 ты пишешь что Criteria приводит к нескольким подзапросам, тогда как в HQL такого нет.
А вот тут: 14572553 - наоборот. HQL - куча запросов, Criteria - один запрос.

перепроверил
это HQL(.createQuery("from Candidate").list();):
авторHibernate: select candidate0_.id as id1_1_, candidate0_.name as name2_1_, candidate0_.surname as surname3_1_, candidate0_.author_id as author7_1_, candidate0_.creation_date as creation4_1_, candidate0_.phone as phone5_1_, candidate0_.cv_url as cv6_1_ from candidate candidate0_
Hibernate: select user0_.id as id1_0_0_, user0_.name as name2_0_0_, user0_.surname as surname3_0_0_, user0_.password as password4_0_0_ from author user0_ where user0_.id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select skills0_.candidate_id as candidat1_1_1_, skills0_.skill_id as skill2_2_1_, skill1_.id as id1_9_0_, skill1_.name as name2_9_0_ from candidate_skill skills0_ inner join skill skill1_ on skills0_.skill_id=skill1_.id where skills0_.candidate_id=?
Hibernate: select vacancies0_.vacancy_id as vacancy1_1_2_, vacancies0_.candidate_id as candidat2_4_2_, vacancy1_.id as id1_12_0_, vacancy1_.author_id as author5_12_0_, vacancy1_.creation_date as creation2_12_0_, vacancy1_.description as descript3_12_0_, vacancy1_.name as name4_12_0_, user2_.id as id1_0_1_, user2_.name as name2_0_1_, user2_.surname as surname3_0_1_, user2_.password as password4_0_1_ from candidate_vacancy vacancies0_ inner join vacancy vacancy1_ on vacancies0_.candidate_id=vacancy1_.id left outer join author user2_ on vacancy1_.author_id=user2_.id where vacancies0_.vacancy_id=?
Hibernate: select vacancies0_.vacancy_id as vacancy1_1_2_, vacancies0_.candidate_id as candidat2_4_2_, vacancy1_.id as id1_12_0_, vacancy1_.author_id as author5_12_0_, vacancy1_.creation_date as creation2_12_0_, vacancy1_.description as descript3_12_0_, vacancy1_.name as name4_12_0_, user2_.id as id1_0_1_, user2_.name as name2_0_1_, user2_.surname as surname3_0_1_, user2_.password as password4_0_1_ from candidate_vacancy vacancies0_ inner join vacancy vacancy1_ on vacancies0_.candidate_id=vacancy1_.id left outer join author user2_ on vacancy1_.author_id=user2_.id where vacancies0_.vacancy_id=?
Hibernate: select vacancies0_.vacancy_id as vacancy1_1_2_, vacancies0_.candidate_id as candidat2_4_2_, vacancy1_.id as id1_12_0_, vacancy1_.author_id as author5_12_0_, vacancy1_.creation_date as creation2_12_0_, vacancy1_.description as descript3_12_0_, vacancy1_.name as name4_12_0_, user2_.id as id1_0_1_, user2_.name as name2_0_1_, user2_.surname as surname3_0_1_, user2_.password as password4_0_1_ from candidate_vacancy vacancies0_ inner join vacancy vacancy1_ on vacancies0_.candidate_id=vacancy1_.id left outer join author user2_ on vacancy1_.author_id=user2_.id where vacancies0_.vacancy_id=?
Hibernate: select vacancies0_.vacancy_id as vacancy1_1_2_, vacancies0_.candidate_id as candidat2_4_2_, vacancy1_.id as id1_12_0_, vacancy1_.author_id as author5_12_0_, vacancy1_.creation_date as creation2_12_0_, vacancy1_.description as descript3_12_0_, vacancy1_.name as name4_12_0_, user2_.id as id1_0_1_, user2_.name as name2_0_1_, user2_.surname as surname3_0_1_, user2_.password as password4_0_1_ from candidate_vacancy vacancies0_ inner join vacancy vacancy1_ on vacancies0_.candidate_id=vacancy1_.id left outer join author user2_ on vacancy1_.author_id=user2_.id where vacancies0_.vacancy_id=?
Hibernate: select vacancies0_.vacancy_id as vacancy1_1_2_, vacancies0_.candidate_id as candidat2_4_2_, vacancy1_.id as id1_12_0_, vacancy1_.author_id as author5_12_0_, vacancy1_.creation_date as creation2_12_0_, vacancy1_.description as descript3_12_0_, vacancy1_.name as name4_12_0_, user2_.id as id1_0_1_, user2_.name as name2_0_1_, user2_.surname as surname3_0_1_, user2_.password as password4_0_1_ from candidate_vacancy vacancies0_ inner join vacancy vacancy1_ on vacancies0_.candidate_id=vacancy1_.id left outer join author user2_ on vacancy1_.author_id=user2_.id where vacancies0_.vacancy_id=?

на выходе(без повторений):
авторThis XML file does not appear to have any style information associated with it. The document tree is shown below.
<list>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>2</id>
<name>erwerwe</name>
<surname>888888888888888888888888888888888888888888888888</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<skills>
<id>2</id>
<name>skill2</name>
</skills>
<skills>
<id>1</id>
<name>skill1</name>
</skills>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>3</id>
<name>John</name>
<surname>Snow</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<date>2013-07-11T10:44:27.397+04:00</date>
<phone>33-33-33</phone>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>4</id>
<name>777</name>
<surname>777</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>5</id>
<name>777</name>
<surname>777</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
</element>
<element xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:type="candidate">
<id>6</id>
<name>John</name>
<surname>Snow</surname>
<author>
<id>1</id>
<name>author777</name>
<surname>author777</surname>
</author>
<date>2013-07-16T12:30:42.727+04:00</date>
<phone>33-33-33</phone>
</element>
</list>

Blazkowiczredwhite90перепроверил

Да, чего тут проверять? Теперь всё понятно. HQL сам по себе JOIN-ы не делает, если их явно не указать в запросе. Поэтому куча select-ов.

получается, что так.

а нельзя CRITERIA сделать без join-ов?

я просто думал, что все эти criteria, sql, hql самодостаточны и всё можно из них получить

без их композиции

Blazkowiczredwhite90а нельзя CRITERIA сделать без join-ов?

Можно.
Criteria.setFetchMode("skills", FetchMode.КАКОЙ_НАДА);
Только возможно Eager в маппинге мешать будет.

спасибо


redwhite90 я просто думал, что все эти criteria, sql, hql самодостаточны и всё можно из них получить
Слова знакомые, а что хотел сказать - не понятно.[/quot]

имелось ввиду, что используя отдельно только sql(либо hql, либо criteria) можно сделать любой запрос к БД. То есть мощность этих инструментов одинаковая, только иногда в hql надо меньше буков писать...