I want to select specific columns specified by user in Spring boot JPA and Angular 7

Question

I have a project in spring boot and angular 7.
Data is displayed in tabular format.
To display all data findAll() method of JPA is good but now the problem is I want to use MyView + filter by which the user can select which columns to be displayed. Also, filters should be applied only to visible columns.
This is my main entitiy

    @Entity
    @Table("user")
    @Getter
    @Setter
    public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private int id;
    
    @Column(name="first_name", nullable = false, length = 500)
    private String firstName;
    
    @Column(name="last_name", length = 2000)
    private String lastName;
    
    @Column(name="imei", length = 20)
    private String imei;

    @Column(name="address", length = 10)
    private String address;

    @Column(name="pincode", length = 10)
    private String pincode;
    
    @Column(name="latitude", length = 20)
    private String latitude;
    
    @Column(name="longitude", length = 20)
    private String longitude;
    
    @Column(name="contact_no", length = 10)
    private String contactNo;
    
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="country_id", nullable = false)
    private Country country;
    
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="state_id", nullable = false)
    private State state;
    
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="city_id", nullable = false)
    private City city;

    @Column(name="is_active", columnDefinition = "tinyint(1) default 1", insertable = false)
    private Boolean isActive;
    
    @Column(name="created_on")
    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdOn;
    
    public User() {
        
    }
}

This is MyView entity

@Entity
@Table("my_view")
@Getter
@Setter
public class MyView {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private int id;
    
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name="user_id", nullable = false)
    @JsonProperty( access = Access.WRITE_ONLY )
    private User user;  
    
    @Column(name="column_name", nullable = false, length = 500)
    private String columnName;
    
    @Column(name="visible", columnDefinition = "tinyint(1) default 1", insertable = false)
    private Boolean visible;
    
    public MyView() {
        
    }
}

Now, suppose I get
first_name, last_name, pincode, imei and contact_no from myview table and filter's are applied from UI like pincode starting with 4220% and last_name is 'furry'.
then how can I create query in JPA.
Note: Column selection will change for each user and accordingly filters will also change.

Answer 1

For dynamic filters you can use Query By Example in simple cases or Specifications in more advanced cases.

Limiting the columns selected is normally done in Spring Data JPA by using a projection interface as return value. But doing that dynamically is not possible and either way you can't combine it with the options for dynamic where clauses.

Therefore the solution is to implement a custom method, get an EntityManager injected, construct your query as required using the Criteria API or String concatenation of a JPQL statement (not recommended due to the risk of SQL injection).