JPA Query over a join table

Question

I have 3 tables like:

A                              AB                        B
-------------                  ------------              ---------------
a1                              a1,b1                    b1

AB is a transition table between A and B

With this, my classes have no composition within these two classes to each other. But I want to know that , with a JPQL Query, if any records exist for my element from A table in AB table. Just number or a boolean value is what I need.

Because AB is a transition table, there is no model object for it and I want to know if I can do this with a @Query in my Repository object.

Answer 1

the AB table must be modeled in an entity to be queried in JPQL. So you must model this as an own entity class or an association in your A and or your B entity.

Answer 2

I suggest to use Native query method intead of JPQL (JPA supports Native query too). Let us assume table A is Customer and table B is a Product and AB is a Sale. Here is the query for getting list of products which are ordered by a customer.

entityManager.createNativeQuery("SELECT PRODUCT_ID FROM 
                                     SALE WHERE CUSTOMER_ID = 'C_123'");

Answer 3

Actually, the answer to this situation is simpler than you might think. It's a simple matter of using the right tool for the right job. JPA was not designed for implementing complicated SQL queries, that's what SQL is for! So you need a way to get JPA to access a production-level SQL query;

em.createNativeQuery

So in your case what you want to do is access the AB table looking only for the id field. Once you have retrieved your query, take your id field and look up the Java object using the id field. It's a second search true, but trivial by SQL standards.

Let's assume you are looking for an A object based on the number of times a B object references it. Say you are wanting a semi-complicated (but typical) SQL query to group type A objects based on the number of B objects and in descending order. This would be a typical popularity query that you might want to implement as per project requirements.

Your native SQL query would be as such:

select a_id as id from AB group by a_id order by count(*) desc;

Now what you want to do is tell JPA to expect the id list to comeback in a form that that JPA can accept. You need to put together an extra JPA entity. One that will never be used in the normal fashion of JPA. But JPA needs a way to get the queried objects back to you. You would put together an entity for this search query as such;

@Entity
public class IdSearch {

    @Id
    @Column
    Long id;

    public Long getId() {
            return id;
    }

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

Now you implement a little bit of code to bring the two technologies together;

    @SuppressWarnings("unchecked")
    public List<IdSearch> findMostPopularA() {
            return em.createNativeQuery("select a_id as id from AB group by a_id 
            order by count(*) desc", IdSearch.class).getResultList();
    }

There, that's all you have to do to get JPA to get your query completed successfully. To get at your A objects you would simply cross reference into your the A list using the traditional JPA approach, as such;

List<IdSearch> list = producer.getMostPopularA();
Iterator<IdSearch> it = list.iterator();
while ( it.hasNext() ) {
    IdSearch a = it.next();
    A object = em.find(A.class,a.getId());
    // your in business!

Still, a little more refinement of the above can simplify things a bit further actually given the many many capabilities of the SQL design structure. A slightly more complicated SQL query will an even more direct JPA interface to your actual data;

    @SuppressWarnings("unchecked")
    public List<A> findMostPopularA() {
            return em.createNativeQuery("select * from A, AB 
            where A.id = AB.a_id 
            group by a_id 
            order by count(*) desc", A.class).getResultList();
    }

This removes the need for an interm IdSearch table!

List<A> list = producer.getMostPopularA();
Iterator<A> it = list.iterator();
while ( it.hasNext() ) {
    A a = it.next();
    // your in business!

What may not be clear tot the naked eye is the wonderfully simplified way JPA allows you to make use of complicated SQL structures inside the JPA interface. Imagine if you an SQL as follows;

SELECT array_agg(players), player_teams
FROM (
  SELECT DISTINCT t1.t1player AS players, t1.player_teams
  FROM (
    SELECT
      p.playerid AS t1id,
      concat(p.playerid,':', p.playername, ' ') AS t1player,
      array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
    FROM player p
    LEFT JOIN plays pl ON p.playerid = pl.playerid
    GROUP BY p.playerid, p.playername
  ) t1
INNER JOIN (
  SELECT
    p.playerid AS t2id,
    array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
  FROM player p
  LEFT JOIN plays pl ON p.playerid = pl.playerid
  GROUP BY p.playerid, p.playername
) t2 ON t1.player_teams=t2.player_teams AND t1.t1id <> t2.t2id
) innerQuery
GROUP BY player_teams

The point is that with createNativeQuery interface, you can still retrieve precisely the data you are looking for and straight into the desired object for easy access by Java.

    @SuppressWarnings("unchecked")
    public List<A> findMostPopularA() {
            return em.createNativeQuery("SELECT array_agg(players), player_teams
            FROM (
                  SELECT DISTINCT t1.t1player AS players, t1.player_teams
                  FROM (
                    SELECT
                      p.playerid AS t1id,
                      concat(p.playerid,':', p.playername, ' ') AS t1player,
                      array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
                    FROM player p
                    LEFT JOIN plays pl ON p.playerid = pl.playerid
                    GROUP BY p.playerid, p.playername
                  ) t1
                INNER JOIN (
                  SELECT
                    p.playerid AS t2id,
                    array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
                  FROM player p
                  LEFT JOIN plays pl ON p.playerid = pl.playerid
                  GROUP BY p.playerid, p.playername
                ) t2 ON t1.player_teams=t2.player_teams AND t1.t1id <> t2.t2id
                ) innerQuery
                GROUP BY player_teams
            ", A.class).getResultList();
    }