Querying Association Tables

Association Tables Hidden by Object Model

Let's review this piece of entity interface code:

Java

@Entity
public interface Book {

    @ManyToMany
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING",  
        joinColumnName = "BOOK_ID",  
        inverseJoinColumnName = "AUTHOR_ID"
    )
    List<Author> authors();

    ...omit other code...
}

Kotlin

@Entity
interface Book {

    @ManyToMany
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING",
        joinColumnName = "BOOK_ID",
        inverseJoinColumnName = "AUTHOR_ID" 
    )
    val authors: List<Author>

    ...omit other code...
}

The BOOK_AUTHOR_MAPPING table is used as an association table here.

• The BOOK table in the database has a corresponding Book entity interface in Java code.

• The AUTHOR table in the database has a corresponding Author entity interface in Java code.

• However, the BOOK_AUTHOR_MAPPING table in the database does not have a corresponding entity interface in Java code.

That is, the association table is hidden from the object model.

Querying Association Tables Directly

Jimmer provides an interesting feature that allows querying association tables directly even if they are hidden without corresponding entities.

Java

AssociationTable<Book, BookTableEx, Author, AuthorTableEx> association =
    AssociationTable.of(BookTableEx.class, BookTableEx::authors);
    
List<Association<Book, Author>> associations = 
    sqlClient
        .createAssociationQuery(association)
        .where(association.source().id().eq(3L))
        .select(association)
        .execute();
associations.forEach(System.out::println);

Kotlin

val associations = sqlClient
    .queries
    .forList(Book::authors) {
        where(table.source.id eq 3L)
        select(table)
    }
    .execute()
associations.forEach(::println) 

Here, createAssociationQuery in Java or queries.forList in Kotlin represents a query based on the association table instead of entity tables.

tip

The Java code demo here uses AssociationTable type with complex generic arguments to be compatible with Java 8. It is recommended to upgrade Java and use the var keyword.

The generated SQL is:

select 
    tb_1_.BOOK_ID, 
    tb_1_.AUTHOR_ID  
/* highlight-next-line */
from BOOK_AUTHOR_MAPPING as tb_1_
where tb_1_.BOOK_ID = ? /* 3 */

This is indeed a query based on the association table.

The final printed result is (the original output is compact, formatted here for readability):

Association{
    source={
        "id":3
    }, target={
        "id":1
    }
}
Association{ 
    source={
        "id":3
    },
    target={
        "id":2
    }
}

The returned data is a list of Association objects:

public class Association<S, T> {

    public S source;
    public T target;

    // constructor and getter/setters
} 

The Association<S, T> represents association table entities that associate S type to T type. Association table entities are pseudo entities without ids. They only have two properties:

• source: The object (Book in this example) corresponding to the foreign key pointing to the owning side.

• target: The object (Author in this example) corresponding to the foreign key pointing to the target side.

note

1. In this example, object fetchers are not used to define the format of association pseudo entity objects (in fact association pseudo entity does not support object fetchers), so the source and target association properties only contain id property.

2. Author also has a bidirectional many-to-many association Author.books, which is the mirror of Book.authors.

   Java

   @Entity  
   public interface Author {
   
       @ManyToMany(mappedBy = "authors")
       List<Book> books();
   
       ...
   }

   Kotlin

   @Entity
   interface Author {
   
       @ManyToMany(mappedBy = "authors") 
       val books: List<Book>
   
       ...
   }

   Association table queries can also be created based on Author.books, but source will represent Author and target will represent Book, reversed from the current example.

In this example, we only query the association table itself, so source and target objects only contain ids.

To get complete source and target objects, join tables and use tuples for return:

Java

AssociationTable<Book, BookTableEx, Author, AuthorTableEx> association =
    AssociationTable.of(BookTableEx.class, BookTableEx::authors);

List<Tuple2<Book, Author>> tuples =  
    sqlClient
        .createAssociationQuery(association)
        .where(association.source().id().eq(3L))
        .select(
            association.source(),
            association.target() 
        )
        .execute();
tuples.forEach(System.out::println);

Kotlin

val associations = sqlClient
    .queries
    .forList(Book::authors) {
        where(table.source.id eq 3L)
        select(
            table.source,
            table.target
        )
    }
    .execute()
associations.forEach(::println)

The generated SQL is:

select

    /* source() */
    tb_1_.BOOK_ID,
    tb_2_.NAME,
    tb_2_.EDITION,
    tb_2_.PRICE,
    tb_2_.STORE_ID,

    /* target() */ 
    tb_1_.AUTHOR_ID,
    tb_3_.FIRST_NAME,
    tb_3_.LAST_NAME,
    tb_3_.GENDER

from BOOK_AUTHOR_MAPPING as tb_1_  
inner join BOOK as tb_2_
    on tb_1_.BOOK_ID = tb_2_.ID
inner join AUTHOR as tb_3_
    on tb_1_.AUTHOR_ID = tb_3_.ID
where tb_1_.BOOK_ID = ? /* 3 */ 

The final printed result is (formatted for readability):

Tuple2{
    _1={
        "id": 3,
        "name": "Learning GraphQL", 
        "edition": 3,
        "price": 51.00,
        "store": {
            "id": 1
        }
    },
    _2={
        "id": 1,
        "firstName": "Alex",
        "lastName": "Banks",
        "gender": "MALE"
    }
} 
Tuple2{
    _1={
        "id": 3,
        "name": "Learning GraphQL",
        "edition": 3,
        "price": 51.00,
        "store": {
            "id": 1
        }
    },
    _2={
        "id": 2, 
        "firstName": "Eve",
        "lastName": "Procello",
        "gender": "MALE" 
    }
}

caution

The association pseudo entity Association<S, T> is simple and special, does not support or need object fetchers.

However, its association properties source and target can support object fetchers, such as:

Kotlin

select(
    table
        .source
        .fetchBy {
            allScalarFields()
            store {
                allScalarFields() 
            }
        },
    table.target
)

Comparison with Entity Table Queries

Readers may think that association table queries exist to allow developers to write more performant queries.

But this is not the case. Due to optimizations like phantom joins and half joins, whether association table queries are used or not, similar functionality and performance can be achieved. Using association table queries is completely up to user preference.

1. Use association table subquery to achieve a function

In previous examples, we demoed top-level queries based on association tables. Here we demo an association table subquery.

Java

BookTable table = Tables.BOOK_TABLE;
AssociationTable<Book, BookTableEx, Author, AuthorTableEx> association =
    AssociationTable.of(BookTableEx.class, BookTableEx::authors);

List<Book> books = sqlClient
    .createQuery(table)
    .where(
        table.id().in(
            sqlClient
                .createAssociationSubQuery(association) 
                .where(
                    association
                        .target() ❶
                        .firstName().eq("Alex")
                )
                .select(
                    association
                        .source() ❷
                        .id()
                )
        )  
    )
    .select(table)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            table.id valueIn
                subQueries.forList(Book::authors) {
                    where(
                        table
                        .target ❶
                        .firstName eq "Alex"
                    )
                    select(
                        table
                        .source ❷
                        .id
                    ) 
                }
        )
        select(table)
    }
    .execute()

Where:

• createAssociationSubQuery in Java and subQueries.forList in Kotlin create a subquery based on the association table, to find books that contain the author with firstName "Alex".

• ❶ association.target is a real JOIN that generates SQL JOIN to the AUTHOR table for condition check.

• ❷ association.source is a phantom join that does not generate a SQL join.

The final generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_  
where
    tb_1_.ID in (
        /* highlight-next-line */ 
        select
            tb_2_.BOOK_ID
        from BOOK_AUTHOR_MAPPING as tb_2_
        inner join AUTHOR as tb_3_
            on tb_2_.AUTHOR_ID = tb_3_.ID
        where tb_3_.FIRST_NAME = ? 
    )

2. Achieve the same functionality with entity table subquery

Java

BookTable book = Tables.BOOK_TABLE;
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(book) 
    .where(
        book.id().in(sqlClient
            .createSubQuery(author)
            .where(author.firstName().eq("Alex"))
            .select(
                author.books().id() ❶
            )
        )
    )
    .select(book)
    .execute(); 

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            table.id valueIn
                subQuery(Author::class) {
                    where(table.firstName eq "Alex")
                    select(
                        table.books.id ❶
                    )
                }
        )
        select(table)
    }
    .execute()

❶ author.books is a half join that only generates SQL JOIN from AUTHOR to the BOOK_AUTHOR_MAPPING table, without further join to the BOOK table.

The final generated SQL is:

select

    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION, 
    tb_1_.PRICE,
    tb_1_.STORE_ID
    
from BOOK as tb_1_
where
    tb_1_.ID in (
        /* highlight-next-line */
        select
            tb_3_.BOOK_ID
        from AUTHOR as tb_2_
        inner join BOOK_AUTHOR_MAPPING as tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where tb_2_.FIRST_NAME = ?
    ) 

Comparing the two SQLs, it's not hard to see they achieve the same functionality and performance.

info

Association table queries just provide developers with another query style, they are not irreplaceable. The same functionality and performance can be achieved with other means.