CODEWITHSUNDEEP
hibernatejpa
Hayi
Hayi

Reputation: 6236

customized DTO and repeatition

I have two table country and city in mysql dababase and mapped as One-to-Many relationship in hibenrate/jpa.

i make a query like this

select new MyDTO(country.id,country.name, city.id, city.name) from country
  inner join city on city.fk_country = country.id
  where .....

the reason why i use my customized DTO is that object city contain other fields (city properties) and i don't want to retrieve them i want just the Id and the name.

the problem is i have duplicated countries in my result 

1,france,1,paris
1,france,2,marseille
1,france,3,lion
....

so how can i make like hibernate put the cities in a collection or something else.

I didn't use hibernate mapping (the entity Country) cause with hibernate/jpa we can't specify what field to retrieve in a collection.

My country entity

@Entity
@Table(name = "country", catalog = "ao")
public class Country implements java.io.Serializable {

    private Integer id;
    private String name;
    private String  .... ;
    private Integer .... ;
    private Boolean .... ;
    ....
    ....
    private Set<City> cities = new HashSet<City>(0);

My City entity

@Entity
@Table(name = "city", catalog = "ao")
public class City implements java.io.Serializable {

    private Integer id;
    private String name;
    //other properties
    private String ...;
    private String ...;
    private Long   ...;
    ...
    ...
    ...
    private Country country;

Upvotes: 1

Views: 629

Answers (2)

solarie
solarie

Reputation: 273

Like this ansnwer from @mohit

Create additional classes for country and city. Transform the flat structure to nested structure of country and cities as shown below:

public class Country {

    Integer idLvl1;
    String  nameLvl1;

    public Country(Integer idLvl1, String  nameLvl1) {
    }

    List<City> cities;

}

public class City {

    Integer idLvl2;
    String  nameLvl2;

    public City(Integer idLvl2, String  nameLvl2) {
    }
}

public class MyDTOConverter {

    public static Collection<Country> covert(List<MyDTO> dtos){
        Map<Integer, Country> countries = new LinkedHashMap<Integer, Country>();
        for (MyDTO myDTO : dtos) {
            //First adding the country if it doesn't exist
            if (!countries.containsKey(myDTO.idLvl1)){
                countries.put(myDTO.idLvl1, new Country(myDTO.idLvl1, myDTO.nameLvl1));
            }

            //Adding city in the existing country.
            countries.get(myDTO.idLvl1).cities.add(new City(myDTO.idLvl2, myDTO.nameLvl2));
        }

        return countries.values();
    }

}

The final Collection of Country will result is the desired JSON.

Upvotes: 1

manish
manish

Reputation: 20135

The JPA query you have provided is functionally equivalent to the following SQL query:

SELECT
  country.id
  , country.name
  , city.id
  , city.name
FROM
  country
INNER JOIN
  city
ON country.id = city.country_id;

With INNER JOIN, the order of the tables does not matter, so this query is the same as

SELECT
  country.id
  , country.name
  , city.id
  , city.name
FROM
  city
INNER JOIN
  country
ON city.country_id = country.id;

This query translates to the following inquiry in natural language

Give me identifiers and names for all cities in my database, along with the identifiers and names for their respective countries.

Given this inquiry and the fact that output of a SELECT query is a collection of tuples, you get the expected result with the country identifier and name repeated for every city in the same country. So, the result is not surprising.

What you are looking for on the other hand is a hierarchical structure:

- Country 1
  - City 1.1
  - City 1.2
  - City 1.3
- Country 2
  - City 2.1
  - City 2.2
  - City 2.3
...

Since the output of a single SELECT query is a table, you should not expect to get this hierarchical output from a single query.

You can achieve what you want by mapping the City table twice. An example follows:

@Entity @Table(name = "city") class City { Long id; String name; @ManyToOne Country country; // Lots of other attributes }
@Entity @Table(name = "city") class CitySummary { Long id; String name; @ManyToOne Country country; }

@Entity @Table(name = "country")
class Country {
  @OneToMany
  private Set<City> cities;

  @OneToMany
  private Set<City> citySummaries;
}

With this you should be able to call country.getCitySummaries() to get just the summarized data for the cities mapped to a country. Alternatively, you can call citySummaryRepository.findAll() to load all the CitySummary objects in one go. Then, calling citySummary.getCountry() will get you the country information. Extra database queries will be fired only once per country. If this also worries you, put the Hibernate-specific @Fetch(FetchMode.JOIN) annotation on Country country; attribute to generate a join query.

Sample code on Github.

Upvotes: 1

Related Questions