Get fields of one entity into another entity using query in Spring Data JPA-CodePudding

I am trying to use Spring Data JPA to achieve a scenario.

I have a query that is the result of some subqueries (Ex: select count(*) from x where id in (subquery...)).

X is an entity with a table on the database. The entity to fill fields of X into is called Persons.

Entity object

@Entity
public class Persons implements Serializable {

    @Id
    private Long count;

    public Long getCount() {
        return count;
    }

    public void setCount(Long count) {
        this.count = count;
    }
}

Do I need to implement something like the following?

Repository

@Repository
public interface PersonssRepository extends JpaRepository<Persons, Long> {

    @Query("<my-query>")
    public long count();
}

Not sure how to achieve this. Any references will be a great help.

CodePudding user response：

You can use Spring JPA Projection for this. You can find a nice tutorial by Baeldung here, to learn how it works.

In your case, Persons is your projection class, so it should not be an entity and should not have its own repository.

The way projections work is through constructors of projection classes (like Persons class). You should create a constructor on Person with the fields you want to get in the query. Your Persons entity should look like this

public class Persons implements Serializable {

    private Long count;

    public Person(Long count) {
        this.count = count;
    }

    public Long getCount() {
        return count;
    }

    public void setCount(Long count) {
        this.count = count;
    }
}

Then, when you want to fetch Persons data from X entity, you can call the constructor of Persons to create Persons object from the fields of X. You will have the following on your XRepository.java:

@Repository
public interface XRepository extends JpaRepository<X, Long> {

    @Query("SELECT new com.your.package.Persons(count(x)) FROM X x WHERE x.id in your_subquery")
    public Persons persons();
}

CodePudding user response：

Maybe you can add the formula annotation in your entity:

    @Formula("(SELECT count(*) FROM person_table)")
    private long count;

But you need a table for your entity. Example:


@Data
@Entity
@Table(name = "person_table")
public class Person implements Serializable {

    private static final long serialVersionUID = -2860883647884316002L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Formula("(SELECT count(*) FROM person_table)")
    private long count;
}