Mutable Association Table

Overview

First, the association table is hidden by the object model, with no direct corresponding entity type. This problem has been explained in Query Association Table, so I won't repeat it here.

In addition, in the Save Command section, we introduced the save command. It can compare the existing data structure in the database with the new data structure the user wants to save. If a relation property based on the association table has changed, the association table will be modified.

Admittedly, the Save Command feature is very powerful, and modifying the association table is just a very small part of its capabilities. However, sometimes we just need a simple way to insert or delete data directly from the association table, we don't need the full capabilities of the Save Command.

Jimmer allows developers to directly insert and delete data from the association table in a simpler way.

Insert Association

Java

sqlClient
    .getAssociations(BookProps.AUTHORS)
    .save(12L, 3L); 

Kotlin

sqlClient
    .getAssociations(Book::authors)
    .save(12L, 3L)

The generated SQL is:

insert into
    BOOK_AUTHOR_MAPPING(
        /* highlight-next-line */
        BOOK_ID, AUTHOR_ID  
    )
    values (?, ?)

This example shows how to use the association property Book.authors to operate the association table.

Similarly, we can achieve the same goal through the association property Authors.books:

Java

sqlClient
    .getAssociations(AuthorProps.BOOKS) 
    .save(3L, 12L);

Kotlin

sqlClient
    .getAssociations(Author::books)
    .save(3L, 12L) 

Note that compared to the previous example, the parameter order of the save method is different due to the different association direction.

The generated SQL is:

insert into
    BOOK_AUTHOR_MAPPING(
        /* highlight-next-line */
        AUTHOR_ID, BOOK_ID
    ) 
    values (?, ?)

It's easy to see that the order of the insert statement columns is also different.

Reverse Conversion

For bidirectional associations, reverse can be used to switch between forward and reverse associations:

Java

Associations bookToAuthor = sqlClient
    .getAssociations(BookProps.AUTHORS);
Associations authorToBook = sqlClient
    .getAssociations(AuthorProps.BOOKS);
Associations authorToBook2 =
    bookToAuthor.reverse(); 
Associations bookToAuthor2 =
    authorToBook.reverse();

Kotlin

val bookToAuthor = 
    sqlClient.getAssociations(
        Book::authors
    )
val authorToBook = 
    sqlClient.getAssociations(
        Author::books  
    )
val authorToBook2 =
    bookToAuthor.reverse()
val bookToAuthor2 =
    authorToBook.reverse()

In this code:

• bookToAuthor and bookToAuthor2 are completely equivalent.
• authorToBook and authorToBook2 are completely equivalent.

Bulk Insert

Java

sqlClient
    .getAssociations(BookProps.AUTHORS)
    .saveAll(
        Arrays.asList(
            new Tuple2<>(10L, 1L),
            new Tuple2<>(11L, 1L), 
            new Tuple2<>(12L, 1L),
            new Tuple2<>(10L, 3L),
            new Tuple2<>(11L, 3L),
            new Tuple2<>(12L, 3L)  
        )
    );

Kotlin

sqlClient
    .getAssociations(
        Book::authors  
    )
    .saveAll(
        listOf(
            Tuple2(10L, 1L),
            Tuple2(11L, 1L),
            Tuple2(12L, 1L),
            Tuple2(10L, 3L),
            Tuple2(11L, 3L),
            Tuple2(12L, 3L)
        )
    ) 

Here there are 3 books, two authors, and 6 total combination ways. The saveAll method inserts all 6 combinations into the association table, generating the following SQL:

insert into BOOK_AUTHOR_MAPPING(
    BOOK_ID, AUTHOR_ID
) values
    /* highlight-start */
    (?, ?),
    (?, ?), 
    (?, ?),
    (?, ?),
    (?, ?),
    (?, ?) 
    /* highlight-end */

The above approach is very cumbersome. For 3 * 2 = 6 it is still acceptable, but what about 7 * 9 = 63? Should we really construct 63 tuples?

Jimmer-sql provides a shorthand syntax:

Java

sqlClient
    .getAssociations(BookProps.AUTHORS)
    // Save cartesian product of two id sets  
    .saveAll(
        Arrays.asList(
            10L, 11L, 12L
        ),
        Arrays.asList(
            1L, 3L
        )
    );

Kotlin

sqlClient
    .getAssociations(
        Book::authors
    )
    // Save cartesian product of two id sets
    .saveAll(
        listOf(10L, 11L, 12L),
        listOf(1L, 3L)
    )

The above saveAll method takes two collection parameters and inserts the cartesian product of the two collections into the association table, so its functionality is the same as the previous example.

Check Existence

Inserting id tuples that already exist into the association table will cause errors, as it violates uniqueness constraints.

To solve this problem, existence checks can be performed.

Java

sqlClient
    .getAssociations(BookProps.AUTHORS)
    .saveAllCommand(
        Arrays.asList(
            10L, 11L, 12L
        ), 
        Arrays.asList(
            1L, 3L
        )
    )
    .checkExistence()
    .execute();

Kotlin

sqlClient
    .getAssociations(
        Book::authors
    )
    .saveAll(
        listOf(10L, 11L, 12L),
        listOf(1L, 3L),
        checkExistence = true
    )

Here checkExistence() indicates that it will first check which data to be inserted already exists, and then only insert data that does not exist.

The generated SQL statements are:

1. Check which data to be inserted already exists:

   select
       BOOK_ID, AUTHOR_ID
   from BOOK_AUTHOR_MAPPING
   where
       (BOOK_ID, AUTHOR_ID) in(
           (?, ?),
           (?, ?), 
           (?, ?)
       )

2. Using the results of the previous query, the data that really needs to be inserted can be calculated. Skip this step if no data needs to be inserted; otherwise, execute this step.

   Here, assuming it is determined that only two rows of data really need to be inserted, the generated SQL is:

   insert into BOOK_AUTHOR_MAPPING(
       BOOK_ID, AUTHOR_ID  
   ) values
       (?, ?),
       (?, ?)

info

Some databases support UPSERT (such as Postgres' insert into ... on conflict ...), which will be supported before Jimmer-1.0.0

Delete Association

Java

sqlClient
    .getAssociations(BookProps.AUTHORS)
    .delete(12L, 3L);

Kotlin

sqlClient
    .getAssociations(
        Book::authors
    )
    .delete(12L, 3L)

The generated SQL is:

delete from BOOK_AUTHOR_MAPPING
where
    (BOOK_ID, AUTHOR_ID) in (
        (?, ?) 
    )

Bulk Delete

Similar to bulk insert, there are two ways to bulk delete:

Java

sqlClient
    .getAssociations(
        BookTableEx.claBookProps.AUTHORS)
    .deleteAll(
        Arrays.asList(
            new Tuple2<>(10L, 1L),
            new Tuple2<>(11L, 1L),
            new Tuple2<>(12L, 1L),
            new Tuple2<>(10L, 3L),
            new Tuple2<>(11L, 3L),
            new Tuple2<>(12L, 3L), 
        )
    );

Kotlin

sqlClient
    .getAssociations(
        Book::authors
    )
    .deleteAll(
        listOf(
            Tuple2(10L, 1L),
            Tuple2(11L, 1L),
            Tuple2(12L, 1L),
            Tuple2(10L, 3L),
            Tuple2(11L, 3L),
            Tuple2(12L, 3L)
        )
    )

Or:

Java

sqlClient
    .getAssociations(BookProps.AUTHORS)
    .deleteAll(
        Arrays.asList(10L, 11L, 12L),
        Arrays.asList(1L, 3L)
    ); 

Kotlin

sqlClient
    .getAssociations(
        Book::authors
    )
    .deleteAll(
        listOf(10L, 11L, 12L),
        listOf(1L, 3L) 
    )

The generated SQL is:

delete from BOOK_AUTHOR_MAPPING  
where
    (BOOK_ID, AUTHOR_ID) in (
        (?, ?),
        (?, ?)
    )

Notes

caution

Please do not abuse this feature. This feature is only suitable for simple insertion and deletion of association tables. It is recommended to only use it when facing the simplest incremental operations.

Let's look at an example. If comparison with existing data in the database is needed, this feature is obviously not the optimal choice, and the code will be very cumbersome, as follows:

Java

Inappropriate Usage Example

public void setAuthors(long bookId, Collection<Long> authorIds) {

    // Get old author ids
    AssociationTable<Book, BookTableEx, Author, AuthorTableEx> table =
                AssociationTable.of(BookTableEx.class, BookTableEx::authors);    
    List<Long> oldAuthorIds = sqlClient
            .createAssociationQuery(table)
            .where(table.source().id().eq(bookId))
            .select(table.target().id())
            .execute();

    // Delete `oldAuthorIds - authorIds`
    List<Long> detachedAuthorIds = new ArrayList<>(oldAuthorIds);
    detachedAuthorIds.removeAll(authorIds);
    sqlClient.getAssociations(BookProps.AUTHORS).deleteAll(
            Collections.singleton(bookId),
            detachedAuthorIds
    );

    // Insert `authorIds - oldAuthorIds`
    List<Long> attachedAuthorIds = new ArrayList<>(authorIds);
    attachedAuthorIds.removeAll(oldAuthorIds);
    sqlClient.getAssociations(BookProps.AUTHORS).saveAll(
            Collections.singleton(bookId),
            attachedAuthorIds
    );
}

Kotlin

Inappropriate Usage Example

fun setAuthors(bookId: Long, authorIds: Collection<Long>) {
    // Get old author ids
    val oldAuthorIds = sqlClient.queries.forList(Book::authors) {
        where(table.source.id eq bookId)
        select(table.target.id)
    }.execute().toSet()

    // Delete `oldAuthorIds - authorIds`
    sqlClient
        .getAssociations(Book::authors)
        .deleteAll(listOf(bookId), oldAuthorIds - authorIds)

    // Insert `authorIds - oldAuthorIds` 
    sqlClient
        .getAssociations(Book::authors)
        .saveAll(listOf(bookId), authorIds - oldAuthorIds)
}

It is obvious that dealing with it this way is very cumbersome.

tip

When the business scenario is no longer simple addition or deletion, Save Command is a better choice, as follows:

Java

Better Usage Example

public void setAuthors(long bookId, Collection<Long> authorIds) {
    sqlClient.save(
        Immutables.createBook(book -> {
            book.setId(bookId);
            book.setAuthorIds(authorIds);
        })
    );
}

Kotlin

Better Usage Example

fun setAuthors(bookId: Long, authorIds: Collection<Long>) {
    sqlClient.save(
        Book {
            id = bookId
            authorIds = authorIds
        }
    )
}