gpt4 book ai didi

java - JPA Criteria Join OneToMany 表 where 子句不起作用

转载 作者:搜寻专家 更新时间:2023-11-01 02:38:09 25 4
gpt4 key购买 nike

我有两个表。

CREATE TABLE public.question
(
id SERIAL PRIMARY KEY
, day VARCHAR(2) NOT NULL
, month VARCHAR(2) NOT NULL
, year VARCHAR(4) NOT NULL
);

CREATE TABLE public.question_translation
(
id SERIAL PRIMARY KEY
, question_id INT REFERENCES public.question(id) NOT NULL
, question_text TEXT NOT NULL
, language VARCHAR(2) NOT NULL
);

现在我想创建条件来检索问题。像这样的 SQL:

SELECT * FROM question q LEFT JOIN question_translation qt ON q.id = qt.question_id WHERE qt.language = 'en'

在使用 JPA Criterias 的 java 中,它看起来像这样:

@Override
public Collection<Question> findByMonthYear(String month, String year, String locale) {
EntityManager em = sessionFactory.createEntityManager();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Question> criteriaQuery = builder.createQuery(Question.class);

List<Predicate> predicates = new ArrayList<>();

Root<Question> questionRoot = criteriaQuery.from(Question.class);
ListJoin<Question, QuestionTranslation> questionTranslationJoinRoot = questionRoot.join(Question_.questionTranslation, JoinType.LEFT);

predicates.add(builder.equal(questionRoot.get(Question_.month), month));
predicates.add(builder.equal(questionRoot.get(Question_.year), year));
predicates.add(builder.equal(questionTranslationJoinRoot.get(QuestionTranslation_.language), locale));

criteriaQuery.select(questionRoot).where(predicates.toArray(new Predicate[]{}));

TypedQuery<Question> query = em.createQuery(criteriaQuery);

String queryString = query.unwrap(Query.class).getQueryString();

return query.getResultList();
}

我使用 ListJoin 是因为在元模型 Question_.class 中我得到了这一行:

public static volatile ListAttribute<Question, QuestionTranslation> questionTranslation;

但是这个返回给我的问题类带有 List QuestionTranslation 和两个条目,其中语言字段等于 ende 值。但我指定 where 子句只返回一个条目,其中语言等于 en 值。我的代码有什么问题?

更新#1:

我有第二种情况。

多一张表:

CREATE TABLE public.user_answer
(
uuid VARCHAR(36) PRIMARY KEY
, user_uuid VARCHAR(36) REFERENCES public.users(uuid) NOT NULL
, question_id INT REFERENCES public.question(id) NOT NULL
, answer TEXT NOT NULL
);

我想像这样制作 SQL:

SELECT * FROM user_answer ua LEFT JOIN question q on ua.question_id = q.id LEFT JOIN question_translation qt ON q.id = qt.question_id WHERE qt.language = 'en' AND ua.user_uuid = '00000000 -user-0000-0000-000000000001' AND q.month = '01' AND q.day = '01' AND q.year = '2016';

在使用 JPA Criterias 的 java 中,它看起来像这样:

@Override
public UserAnswer findByDayMonthYear(String day, String month, String year, User user, String locale) {
EntityManager em = sessionFactory.createEntityManager();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<UserAnswer> criteriaQuery = builder.createQuery(UserAnswer.class);

List<Predicate> predicates = new ArrayList<>();

Root<UserAnswer> userAnswerRoot = criteriaQuery.from(UserAnswer.class);
Join<UserAnswer, Question> questionJoin = userAnswerRoot.join(UserAnswer_.question);
ListJoin<Question, QuestionTranslation> questionTranslatJoin = questionJoin.join(Question_.questionTranslation);

predicates.add(builder.equal(builder.treat(questionJoin, Question.class).get(Question_.day), day));
predicates.add(builder.equal(builder.treat(questionJoin, Question.class).get(Question_.month), month));
predicates.add(builder.equal(builder.treat(questionJoin, Question.class).get(Question_.year), year));
predicates.add(builder.equal(builder.treat(questionTranslatJoin, QuestionTranslation.class).get(QuestionTranslation_.language), locale));
predicates.add(builder.equal(userAnswerRoot.get(UserAnswer_.user), user));

criteriaQuery.select(userAnswerRoot).where(predicates.toArray(new Predicate[]{}));

TypedQuery<UserAnswer> query = em.createQuery(criteriaQuery);

String queryString = query.unwrap(Query.class).getQueryString();

return query.getSingleResult();
}

在这种情况下,Question 的列表包含两个 QuestionTranlsation 项目,语言为 ende,但我只需要一个 QuestionTranlsation 条目,其中语言等于 en

在这种情况下我必须做什么?

最佳答案

这可以通过 JPA 2.1 功能 JOIN ON

参见 How to do JOIN ON query using Criteria API

关于java - JPA Criteria Join OneToMany 表 where 子句不起作用,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41379226/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com