Fetch specific columns dynamically

Question

I have the following User entity:

public class User extends PanacheEntityBase{
    @Id 
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "DataIdGenerator")
    @Column(name = "id")
    public Long id;

    public String name;
    public String location;

    public int age;
}

I also have the following endpoint: '/user', with a 'select' query parameter where you provide the column names you want to receive. It should be possible to select any combination of columns like: /user?select=id,name, /user?select=id,age, /user?select=name,age, /user?select=age,name

Based on the 'select' query I want to use a projection to get the selected columns only. Currently I'm using the query to create the following query fe: /user?select=id,name to SELECT d.id, d.name FROM User d, however I need the DTO to be dynamic based on the columns provided too.

Currently I have the following projection where UserDTO is a class with id and name attributes. This works fine, but if I change any parameter I need a different DTO.

// This variable is dynamically created based on query parameters
String query = 'SELECT d.id, d.name FROM User d'
return User.find(query).project(UserDTO.class).list();

Is it possible to make this projection DTO class more dynamic, so it supports all combinations?

Answer 1

I suspect the Panache API is not flexible enough at the moment to do what you are asking.

But you could use the Hibernate Reactive API without Panache:

@Inject
Mutiny.SessionFactory sf;

public Uni<List<Tuple>> find(String query) {
   return sf.withSession(session ->
       session.createQuery(query, Tuple.class).getResultList()
   );
}

Once you have the Tuple, you can convert it to the type you prefer.

Answer 2

It looks like you already have your solution at the JPA layer. The trick is to serialize only the requested information back from your endpoint.

You can project onto a UserDTO that has all the fields of your entity that you would ever want to expose as nullable values (for example, use the same structure, but use Integer instead of int for the age). I think you would need to create constructors on this DTO for all the combinations of values you would want (each constructor would leave the other values as null).

Then you can annotate your DTO as @JsonInclude(JsonInclude.Include.NON_NULL) to have the Jackson serializer produce only the non-null fields in the response from your endpoint.

And I hope it goes without saying, but I'll say it anyway: Since you are allowing user generated input to modify your query, you should sanitize the inputs for this.