Exists using Specification in Spring Data JPA

Suppose you have a table with this structure.

ER.png

If you want to issue a slightly complicated query using Spring Data JPA, use Specification, but if you use exists, the following Entity structure caused a problem. (Setter / getter omitted) By the way, the version of Spring Boot is 1.5.4.RELEASE.

User.java



@Entity
public class User {
    @Id
    private String userId;

    private String name;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private List<Phone> services;
}

Phone.java


@Entity
public class Phone {
    @Id
    private Integer phoneId;

    @ManyToOne
    @JoinColumn(name = "USER_ID")
    private User user;

    private Integer type;

    private String number;
}

Using these Entity, the exists clause to "get User who has at least one Phone"

public interface UserRepository extends CrudRepository<User, Integer>, JpaSpecificationExecutor<User> {

    static Specification<User> existsPhone() {
        return (root, query, cb) -> {
            Subquery<Integer> subquery = query.subquery(Integer.class);
            Root<Phone> subRoot = subquery.from(Phone.class);
            return cb.exists(subquery.select(cb.literal(1)).where(
                    cb.equal(root, subRoot.get("user").get("userId")));
        };
    }

}

Make like

userRepository.findAll(Specifications.where(UserRepository.existsPhone()));

When I executed, a StackOverflowError occurred.

Apparently, it seems that it has been circularly referenced as User → Phone → User → Phone → .... ..

So, if I remove the User reference from Phone as follows, the error disappeared.

User.java



@Entity
public class User {
    @Id
    private String userId;

    private String name;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "userId")
    private List<Phone> services;
}

Phone.java


@Entity
public class Phone {
    @Id
    private Integer phoneId;

    private String userId;

    private Integer type;

    private String number;
}
public interface UserRepository extends CrudRepository<User, Integer>, JpaSpecificationExecutor<User> {

    static Specification<User> existsPhone() {
        return (root, query, cb) -> {
            Subquery<Integer> subquery = query.subquery(Integer.class);
            Root<Phone> subRoot = subquery.from(Phone.class);
            return cb.exists(subquery.select(cb.literal(1)).where(
                    cb.equal(root, subRoot.get("userId")));
        };
    }

}

I don't even refer to User from Phone, so is this okay?

Recommended Posts

Exists using Specification in Spring Data JPA
OR search with Spring Data Jpa Specification
[spring] Let's use Spring Data JPA
Make the where clause variable in Spring Data JPA
[How to install Spring Data Jpa]
Spring Data JPA SQL log output
Spring Data JPA: Write a query in Pure SQL in @Query of Repository
[Java] Get data from DB using singleton service in Spring (Boot)
Null support cache in Spring Data Redis
To write Response data directly in Spring
Spring Data JPA save select-insert is only insert
Data management using volume in Docker (personal memorandum)
Spring Data JPA Entity cross-reference and its notes
Jackson is unable to JSON serialize hibernateLazyInitializer in Spring Data JPA with error
Sample code for DB control by declarative transaction in Spring Boot + Spring Data JPA
Sample code for search using QBE (Query by Example) of Spring Data JPA
Sort by Spring Data JPA (with compound key sort)
Creating a common repository with Spring Data JPA
How to define multiple orm.xml in Spring4, JPA2.1
Spring Boot + Spring Data JPA About multiple table joins
Check the behavior of getOne, findById, and query methods in Spring Boot + Spring Data JPA
A memorandum when trying Spring Data JPA with STS
Automatically generate DDL using [jpa-schema-gradle-plugin] plugin in Spring × Gradle
I tried to get started with Spring Data JPA
Until the use of Spring Data and JPA Part 2
Test field-injected class in Spring boot test without using Spring container
Until the use of Spring Data and JPA Part 1
Inject Logger in Spring
Use Interceptor in Spring
Spring Data JDBC Preview
Microservices in Spring Cloud
Get cookies in Spring
spring data dynamodb trap
Try using Spring JDBC
Spring Boot Introductory Guide I tried [Accessing Data with JPA]
Authenticate 3 or more parameters in JSON format using Spring Security
Create API to send and receive Json data in Spring
Get error information using DefaultErrorAttributes and ErrorAttributeOptions in Spring Boot 2.3
8 things to insert into DB using Spring Boot and JPA
How to control transactions in Spring Boot without using @Transactional
[Spring Data JPA] Custom ID is assigned in a unique sequence at the time of registration.