CODEWITHSUNDEEP
spring-boothibernatespring-data-jpa
Matelutex
Matelutex

Reputation: 2220

Spring Boot JPA - find by 2 columns

I don't know how to create a JPA query to get all records from my table:

@Entity
@Table(name = "A")
public class A{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, updatable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "message_id")
    private Message;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;
}

@Entity
@Table(name="message")
@Getter
@Setter
public class Message{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, updatable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "account_id", foreignKey = @ForeignKey(name = "FK_account_to_message"))
    private Account account; //and then search by Account.id
}

SO I have 2 types of objects in A table (sometimes object is created from email, sometimes from a file):

  1. one type without user_id (null) -> then I have to find all A object by searchning by Message -> Account -> Id
  2. second type with user_id -> we can directly get A objects by values in user_id column

I want to get all records for specific user_id -> how to do that in most efficient way? I don't want to invoke 2 methods in repository:

 User user = userService.getEmail();
        List<A> aObjects= Stream.concat(ARepository.findByMessage_Account_Id(user.getId()).orElse(new ArrayList<>()).stream(),
                aRepository.findByUser_Id(user.getId()).orElse(new ArrayList<>()).stream()).collect(Collectors.toList());

Is it possible to create ONE repository method that finds all records for 2 different objects (one with user_id and second without user_id)?

Upvotes: 2

Views: 652

Answers (1)

SternK
SternK

Reputation: 13111

I guess that this query is to complex for using derived from the method name query. As it stated in the documentation:

Although getting a query derived from the method name is quite convenient, one might face the situation in which either the method name parser does not support the keyword one wants to use or the method name would get unnecessarily ugly. So you can either use JPA named queries through a naming convention or rather annotate your query method with @Query.

So, I would suggest just write the following query:

@Query("select aa from A aa left join aa.user u left join aa.message msg left join msg.account acc where (u is null and acc is not null and acc.id = :userId) or (u is not null and u.id = :userId)")
List<A> findByUserOrAccountId(@Param("userId") Long userId);

Upvotes: 1

Related Questions