Need a sample for JSON output. I have about 30 fields in the object. Of these, I need to bring 5. For example, only the name, surname and address of the user.

I want to get [{"name": "Vasya", "surname": "Pupkin", "address": "Main str 9"}]

I do this sample:

@Query(value = "SELECT user.name, user.surname, user.address FROM User user") List<Task> findAll();

It deduces to me: [["Vasya", "Pupkin", "Main str 9"]] i.e. without field names.

Parse it, without the names of the fields, of course it is not possible. How to be? @JsonIgnore is not an option.

  • one
    Who and where leads? [{"Vasya","Pupkin","Main str 9"}] is not a valid JSON at all. - enzo
  • @enzo Sorry, corrected. Not exactly rewritten. But the essence of the issue does not change. - Tom Wally
  • Fast forward to waiting for lb food.) W - Nick Volynkin ♦

1 answer 1

Extract fields by number. You know in what order they go, once you set it yourself (by your request, of course):

 List<Object[]> users = em.createQuery(...).getResultList(); for (Object[] u : users) { System.out.println(u[0]); // name ... }

Another option is to use the NEW operation and the constructor.
You define for your projection (a sample of a part of the fields is often called a projection) a class with a constructor, whose parameters correspond to the fields of the selection:

 package abc; public class UserPr { private String name; // поля ... public UserPr(String name, String surname, ...) // конструктор с полями как в запросе public String getName() { return name; } ... }

(It is not necessary to make private fields and getters to them. You can simply do with public fields)

Now operation NEW
JPQL query will look like this:

 String query = "SELECT NEW abcUserPr(user.name, user.surname, user.address) FROM User user"

The full class name abcUserPr

As a result of such a request, we get the List<UserPr> :

 List<UserPr> users = em.createQuery(query).getResultList(); for (UserPr u : users) { System.out.println(u.getName()); ... }

In this simple way, you can merge query results into all classes with a suitable constructor.
You can even use the same User if you make the necessary constructor for it! Only the resulting objects will not be considered Entity, with all the consequences.

I will not paint in detail, I will only mention.
With reference to native queries exactly. About JPQL I do not remember.
Sometimes for such classes you have to tweak a little mapping.
The @SqlResultSetMapping allows @SqlResultSetMapping to specify how each field from the sample will be translated into the class field (more precisely, the constructor parameter).

How instead of println get your JSON hope yourself figure it out.

  • Thank you very much! Just yesterday found in the documentation. For those who work with the spring framework: @Query (value = "SELECT new NameSurnameAndAdress (user.name, user.surname, user.address) FROM User user") List <User> someMethod (); And take into account that at least spring, without heterosers and seters, gave me an error of serialization. - Tom Wally