I'm currently struggling with a query that I know it's possible, but due to my poor skills in SQL querys I can't really manage to find the right sentence.

I want to find all Territories from a certain Zone. Each Zone contains several Territories, but a Territory can belong to a Zone. The entities are as they follow:

public class Territory {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    private String name;

    private Long owned_player;

    @OneToMany
    private List<Territory> neighbors;

    private int no_of_armies;

    public Territory(String name) {
        this.no_of_armies = 0;
        this.neighbors = new ArrayList<>();
        this.name = name;
        //this.owned_player = new Player(0);
        this.owned_player = null;
    }

@Entity
public class Zone {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String name;
    private String map;
    //private int control_value;
    @OneToMany
    private List<Territory> territories;

    public Zone(String name, String map) {
        this.territories = new ArrayList<>();
        this.name = name;
        this.map = map;
        //this.control_value = control_value;
    }

The repo where I have to insert the query looks like this:

public interface TerritoryRepository extends JpaRepository<Territory,Long> {

    @Query("????")
    public List<Territory> findAllTerritoriesByZone(String zone);

}

I am trying to find that query that will return all territories that belong to Asia(China,Japan,Taiwan...) or Europe(Spain,France,Germany...) for example.

Thanks for your help and attention!

CodePudding user response：

There were a few issues with your example that would be problematic once you had started to run it, but for now I'll focus on the answer to your specific question.

In your example, the relationship between Zone and Territory is unidirectional. Zone#territories means that if you have a Zone, you can navigate to it's Territories. But since Territory has no reference to Zone, you can not traverse that direction- which is going to be a problem for your question, because that's effectively what you're trying to do.

So the first thing you'll need to do is make the relationship bidirectional.

You can do so by adding a reference to a Zone on your Territory class:

    @ManyToOne
    @JoinColumn(name = "ZONE_ID")
    private Zone zone;

Then update Zone#territories with a mappedBy attribute, so that the JPA implementation can understand that these two fields reference each other.

    @OneToMany(mappedBy = "zone")
    private List<Territory> territories = new ArrayList<>();

With that, you can now reference the Zone from a Territory in your repository. In your repository, it's not clear to me what the zone parameter was supposed to refer to- the name of the zone? If so, you can use this query to find territories with a zone with a specific name:

    // in TerritoryRepository 
    @Query("select t from Territory t join t.zone where t.zone.name = :zone")
    public List<Territory> findAllTerritoriesByZone(String zone);

It's also possible to express this using regular derived queries, in which case you don't need to specify @Query/JPQL at all:

    List<Territory> findAllByZoneName(String zone);

Additional problems with the example:

• Territory needs an @Entity annotation
• Territory#neighbors is expressed as a @OneToMany relationship. This should probably have been a @ManyToMany relationship, as you can have many neighbors who each have many neighbors themselves. I commented it out for the sake of the rest of this answer.
• Your Territory and Zone have no default constructors. JPA requires that entities have a default public/protected constructor so that it can instantiate them reflectively. I added these back in to get the example to work.