QueryDSL的多个联接 [英] Multiple Joins with QueryDSL

问题描述

我正在尝试将QueryDSL与Spring Data JPA结合使用.

I'm trying to use QueryDSL with Spring Data JPA.

我的数据库有3个表.

My database has 3 tables.

1)带有帐户信息的帐户表，特别是一个帐号. 2)一个PersonRole表，该表在一个帐户(例如所有者)上具有一个人的角色，并具有相应的帐号和他的个人ID号. 3)一个有几排人的人表.他们的ID号和名字，姓氏等等...

1) An account table with account info and specifically an account number. 2) A PersonRole table which has a person's role on an account (like owner) and the corresponding account number and his person ID number. 3) A person table which has rows of persons. Their ID number and first name, last name etc...

我的实体看起来像这样:

My entities look like this:

@Entity
Account{
//...Other fields ommited
// **
@OneToMany
@JoinColumn(name = "ACCNT_NUMBER")
List<PersonRole> personRoles;**
}

@Entity
PersonRole{
String role;
// ...Other fields ommited
// **
@OneToOne
@JoinColumn(name = "PERSON_ID")
Person person;**
}


@Entity
Person{...}

我想按个人的名字和姓氏筛选我选择的帐户，然后用它填充具有相关人员和人员的合同实体.

I want to filter the accounts that I select by person first name and last name and then use that to populate contract entities which have associated personroles and persons.

我认为我必须创建联接才能执行此操作.我尝试了很多事情，但我不断出错.我已经创建了相应的QClasses.我知道下面的代码是错误的，它不起作用，但是也许您可以看到我是否走在正确的轨道上，并可能会帮助我.任何帮助是极大的赞赏.谢谢！

I assume I have to create joins to do this. I tried a bunch of things but I keep getting errors. I've created the corresponding QClasses. I know the below code is wrong and it doesn't work but perhaps you can see if I'm on the right track and possibly help me. Any help is greatly appreciated. Thank you!

    QAccount account = QAccount.account;
    QPersonRole personRoles = QPersonRole.personRole;
    QPerson person = QPerson.person;

    JPAQuery<Account> query = new JPAQuery<>(entityManager);

    List<Account> accountList = query
            .from(account)
            .innerJoin(acccount.personRoles, personRoles)
            .innerJoin(person)
            .where(person.lastName.eq("John")
                    .and(person.lastName.eq("Doe")))
            .fetch();

推荐答案

仅在需要过滤一对多时才需要连接...仍然可以使用jpql ...，我更喜欢使用单数形式对这个人角色如此:

You only need joins when you want to filter on one to many... jpql can still be leveraged... and I'd prefer to use a singular on the personRole so:

QAccount account = QAccount.account;
QPersonRole personRole = QPersonRole.personRole;

JPAQuery<Account> query = new JPAQuery<>(entityManager);

List<Account> accountList = query
            .from(account)
            .innerJoin(acccount.personRoles, personRole)
            .where(personRole.person.lastName.eq("John")
                    .and(personRole.person.lastName.eq("Doe")))
            .fetch();

请注意，无需再加入Person.

Notice how the join to Person is not necessary.

这篇关于QueryDSL的多个联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！