Home > Software design >  How to list column from another table via foreign key in Spring Boot
How to list column from another table via foreign key in Spring Boot

Time:01-31

I'm struggling with this problem. I have table "Cities" which has foreign key to table "Countries" with country_id referenced to country from which is city. In my web application I can list all the data from "Cities" table but I can't find a way to list name of country. This is my service class method.

    public List<City> listAll() {
    List<City> cities = repo.findAll();
    return cities;
}

In "City" entity I have field Country by which I can find in method name of country but I don't know how to return it together with cities.

Addition:

    @GetMapping("/cities")
public String getAllCities(Model model) {
    List<City> listCities = service.listAll();

    model.addAttribute("showListCities", listCities);
    return "cities";
}

City.java:

package com.bookflight.BookFlight.gradovi;

import com.bookflight.BookFlight.drzave.Drzave;
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "cities")
public class City {
    @Id
    @Column(name = "city_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(nullable = false, length = 45, name = "city_name")
    private String city_name;
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "cou_id", referencedColumnName = "cou_id")
    private Country countries;

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public String getcity_name() {
    return city_name;
}

public void setcity_name(String city_name) {
    this.city_name = city_name;
}

public Countries getCountries() {
    return countries;
}

public void setCountries(Country countries) {
    this.countries = countries;
}

}

NOTE: Every variable name here is in my native language so I literally translated it word by word to better understand your solution afterwards.

CodePudding user response:

You have add FetchType to the @ManyToOne annotation arguments:

@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "cou_id", referencedColumnName = "cou_id")
private Country countries;

and her a short description for each fetch type:

FetchType.LAZY will only fire for primary table. If in your code you call any other method that has a parent table dependency then it will fire query to get that table information.

FetchType.EAGER will create join of all table including relevant parent tables directly.

And you can add a method in your city Class to return your country name and this method will be available in your view-layer:

  public String getCountryName(){
    return countries == null ? null : countries.getName();
    //not sure how the country class is implemented
  }
  • Related