Spring Projections not returning the State Details

Question

I have a Country and State table for which I have integrated with Spring Data JPA. I have created a function public Page<CountryDetails> getAllCountryDetails in my CountryServiceImpl for getting all the Country and the corresponding State details. The service is working fine and is giving me the below output:

{
  "content": [
    {
      "id": 123,
      "countryName": "USA",
      "countryCode": "USA",
      "countryDetails": "XXXXXXXX",
      "countryZone": "XXXXXXX",
      "states": [
        {
          "id": 23,
          "stateName": "Washington DC",
          "countryCode": "USA",
          "stateCode": "WAS",
          "stateDetails": "XXXXX",
          "stateZone": "YYYYYY"
        },
        {
          "id": 24,
          "stateName": "Some Other States",
          "countryCode": "USA",
          "stateCode": "SOS",
          "stateDetails": "XXXXX",
          "stateZone": "YYYYYY"
        }
      ]
    }
  ],
  "last": false,
  "totalPages": 28,
  "totalElements": 326,
  "size": 12,
  "number": 0,
  "sort": null,
  "numberOfElements": 12,
  "first": true
}

My Complete code is as given below:

CountryRepository.java

@Repository
public interface CountryRepository extends JpaRepository<CountryDetails, Integer> {

    @Query(value = "SELECT country FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}", 
    countQuery = "SELECT COUNT(*) FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}")
    public Page<CountryDetails> findAll(Pageable pageRequest);
}

CountryServiceImpl.java

@Service
public class CountryServiceImpl implements CountryService {

    @Autowired
    private CountryRepository countryRepository;

    @Override
    public Page<CountryDetails> getAllCountryDetails(final int page, final int size) {
        return countryRepository.findAll(new PageRequest(page, size));
    }
}

CountryDetails.java

@Entity
@Table(name = "country", uniqueConstraints = @UniqueConstraint(columnNames = "id"))
public class CountryDetails {

    @Id
    @GeneratedValue
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;
    private String countryName;
    private String countryCode;
    private String countryDetails;
    private String countryZone;

    @JsonManagedReference
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "countryDetails")
    private List<State> states;

    // getters / setters omitted
}

State.java

@Entity
@Table(name = "state", uniqueConstraints = @UniqueConstraint(columnNames = "id"))
public class State {

    @Id
    @GeneratedValue
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;
    private String stateName;
    private String countryCode;
    private String stateCode;
    private String stateDetails;
    private String stateZone;

    @JsonBackReference
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "countryCode", nullable = false, insertable = false, updatable = false, foreignKey = @javax.persistence.ForeignKey(name="none",value = ConstraintMode.NO_CONSTRAINT))
    private CountryDetails countryDetails;

    // getters / setters omitted
}

Now the Problem

Actually what I want the country service to return with minimal information like as shown below

{
  "content": [
    {
      "countryName": "USA",
      "countryCode": "USA",
      "states": [
        {
          "stateCode": "WAS"
        },
        {
          "stateCode": "SOS"
        }
      ]
    }
  ],
  "last": false,
  "totalPages": 28,
  "totalElements": 326,
  "size": 12,
  "number": 0,
  "sort": null,
  "numberOfElements": 12,
  "first": true
}

So for achieving that I have used Projections like as shown below

CountryProjection .java

public interface CountryProjection {
    public String getCountryName();
    public String getCountryCode();
    public List<StateProjection> getStates();
}

StateProjection .java

public interface StateProjection {
    public String getStateCode();
}

CountryServiceImpl.java

@Repository
public interface CountryRepository extends JpaRepository<CountryDetails, Integer> {

    @Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}", 
    countQuery = "SELECT COUNT(*) FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}")
    public Page<CountryProjection> findAll(Pageable pageRequest);
}

But now the service is returning any of the state details like as shown below

{
  "content": [
    {
      "countryName": "USA",
      "countryCode": "USA"
    }
  ],
  "last": false,
  "totalPages": 28,
  "totalElements": 326,
  "size": 12,
  "number": 0,
  "sort": null,
  "numberOfElements": 12,
  "first": true
}

How can we get the minimal state details also like as shown below

{
  "content": [
    {
      "countryName": "USA",
      "countryCode": "USA",
      "states": [
        {
          "stateCode": "WAS"
        },
        {
          "stateCode": "SOS"
        }
      ]
    }
  ],
  "last": false,
  "totalPages": 28,
  "totalElements": 326,
  "size": 12,
  "number": 0,
  "sort": null,
  "numberOfElements": 12,
  "first": true
}

Can anyone please help me on this

You're not using StateProjection in CountryProjection, and shouldn't these interfaces extend Serializable? — Turo, Jun 01 '18 at 06:56
@Turo you mean I am using `State` instead of `StateProjection` with only `stateCode`. even I tried that but states details are not printing out — Alex Man, Jun 01 '18 at 06:59
@Turo Is it issue with `SELECT country.countryName AS countryName, country.countryCode AS countryCode FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}` — Alex Man, Jun 01 '18 at 07:06
Forget that with the Serializable. Once I used just the stupid solution to set the unnessary fields to null and omit nulls per annotation... — Turo, Jun 01 '18 at 07:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172203/discussion-between-turo-and-alex-man). — Turo, Jun 01 '18 at 07:40
can you try by replacing your query with - `@Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode, country.states.stateCode AS stateCode FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}"` — Pradeep, Jun 10 '18 at 18:50
How do you really obtain that json? Show us the VM, I don't see some properties like: `content`, `totalPages`,`numberOfElements`? — NiVeR, Jun 12 '18 at 18:23
Obviously your code `public Page findAll(Pageable pageRequest);` cannot be compiled. You should provide us the code same as you currently have to exploit the bug. In my localhost, the projection work fine and output as you expected — Mạnh Quyết Nguyễn, Jun 13 '18 at 03:23

score 1 · Answer 1 · edited Jul 31 '19 at 00:16

1

Try using JsonIgnore with the fields which you don't want in the return JSON

@JsonIgnore
private String stateDetails;

edited Jul 31 '19 at 00:16

atymic

3,093
1
13
26

answered May 31 '18 at 18:47

Tarun Jain

262
1
8

I can't use `@JsonIgnore` since I need those details in other service's.... – Alex Man Jun 01 '18 at 03:24
In @Query you are not querying for state code form state table, try the join to state table – Tarun Jain Jun 01 '18 at 17:39

score 0 · Answer 2 · edited Jul 30 '19 at 19:59

0

You can use transient keyword with the variable which you don't want within json.

ya otherwise use

@Expose String myString;

edited Jul 30 '19 at 19:59

PowerStat

3,757
8
32
57

answered Jun 10 '18 at 13:54

manish singh

1
1

1

@manishsingh....Thanks for the reply....can you show me with an example – Alex Man Jun 10 '18 at 16:38

NiVeR · Answer 3 · 2018-06-13T09:38:14.783

0

First idea

IMO there is something not right on the way you are doing things. I don't understand why are you defining the query directly like this:

@Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}", 
    countQuery = "SELECT COUNT(*) FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}")

which is basically, creating a projection by select.

On the other hand, you are using Interface-based Projections which is yet again doing the projection, but by exposing the getters of the properties that you want to be projected. As far as I could see, you defined well the hierarchy through interfaces and it should be valid approach.

So what I am asking is, have you tried removing the @Query part all together?

Second idea (in eye of the comment)

Another idea can be to use the join fetch construct in jpql which is used tell to hibernate to load the association eagerly with the query.

@Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode, countryStates FROM Country country join fetch country.states countryStates GROUP BY country.countryId ORDER BY ?#{#pageable}"

edited Jun 13 '18 at 09:38

answered Jun 12 '18 at 18:44

NiVeR

9,644
4
30
35

Thanks for the reply.... actually I cant remove @Query since I have a search query also to be searched against multiple fields(I have not shown that in the current SO example).... – Alex Man Jun 13 '18 at 02:45
@AlexMan How do you expect a viable solution when you don't present the problem as it is? – NiVeR Jun 13 '18 at 07:09
I tried that...and got one exception ```Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list``` – Alex Man Jun 14 '18 at 04:27
Ok, it seems that the association is already configured as eager by something else. You can try 2 more things: 1. Remove just `fetch ` from the query or 2 : remove the join clause completely but add `country. states` to the select. – NiVeR Jun 14 '18 at 06:14
you mean like this `@Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode, countryStates FROM Country country country.states countryStates GROUP BY country.countryId ORDER BY ?#{#pageable}"` – Alex Man Jun 14 '18 at 10:39
@AlexMan no. I was thinking about `@Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode, countryStates as states FROM Country country join country.states countryStates GROUP BY country.countryId ORDER BY ?#{#pageable}"` or the other option like: `@Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode, country.countryStates as states FROM Country country GROUP BY country.countryId ORDER BY ?#{#pageable}"` – NiVeR Jun 14 '18 at 10:56
the first query worked `@Query(value = "SELECT country.countryName AS countryName, country.countryCode AS countryCode, countryStates as states FROM Country country join country.states countryStates GROUP BY country.countryId ORDER BY ?#{#pageable}"`.................but it is returning only one record for states – Alex Man Jun 14 '18 at 11:07
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/173137/discussion-between-niver-and-alex-man). – NiVeR Jun 14 '18 at 11:14

score 0 · Answer 4 · answered Jun 12 '18 at 18:52

0

You can check the documentation of annotation :

@JsonIgnoreProperties("fieldname")

This annotation needs to be applied on your POJO classes in your case Country*, State* mentioning the comma separated list of fields you don't need to be part of the response.

You can try this approach instead of changing to the implementation on projection style.

@JsonIgnoreProperties({ "id","countryDetails","countryZone"})
public class CountryDetails

@JsonIgnoreProperties({ "id","stateName","countryCode","stateDetails","stateZone"})
public class State

answered Jun 12 '18 at 18:52

Rizwan

2,369
22
27

Thanks for replying... where I should apply the json ignore annotations...onto CountryDetails and State model clases....actualy If i do that then it will be always ignoring those fields....there are other services which I do require the ignored values – Alex Man Jun 13 '18 at 02:32
Yes, you are correct they would be ignored while returning the response. As in your case if you have to handle the same Object structure to behave differently in different API calls then you have to come up with the custom Mapper Utility to check the request call and derive the necessary properties to be included in the response. Look for below project with such customization. https://github.com/monitorjbl/json-view – Rizwan Jun 13 '18 at 08:30

score 0 · Answer 5 · answered Oct 16 '19 at 13:39

You can make your CountryService return a DTO instead of the entity with only the fields you need.

Service

@Service
public class CountryServiceImpl implements CountryService {

    @Autowired
    private CountryRepository countryRepository;

    @Override
    public Page<CountryDetailsDto> getAllCountryDetails(final int page, final int size) {
        return countryRepository.findAll(new PageRequest(page, size))
                .map(c -> {
                    CountryDetailsDTO dto = new CountryDetailsDTO();
                    dto.setCountryCode(c.getCountryCode());
                    dto.setCountryName(c.getCountryName());

                    dto.setStates(c.getStates().stream().map(s -> {
                        StateDto stateDto = new StateDto();
                        stateDto.setStateCode(s.getStateCode());

                        return stateDto;
                    }).collect(Collectors.toSet()));

                    return dto;
                });
    }
}

DTO

public class CountryDetailsDTO {

    private String countryName;

    private String countryCode;

    private Set<StateDto> states;
}

public class StateDto {

    private String stateCode;
}

Spring Projections not returning the State Details

Now the Problem

5 Answers5

First idea

Second idea (in eye of the comment)

Service

DTO