Skip to main content

Non-Structural Mapping

Non-Structural Design Example​

Through the content related to Association Mapping, it is not difficult to find that all associations are based on @JoinColumn and @JoinTable (may be omitted).

Although Jimmer supports fake foreign keys (foreign keys in the developer's mind but without corresponding foreign key constraints in the database), all the association mappings we have discussed so far are based on 3NF design of relational databases.

However, you may be handed over a legacy database containing non-normalized database designs, such as:

create table book(
    id bigint unsigned not null auto_increment primary key,
    // highlight-next-line 
    author_ids varchar(100),
    ...Omit other columns...
) engine=innodb;

Here, author_ids is a string column whose data is the ids of all Author objects associated with the current Book concatenated together with commas, e.g. 1, 5, 100, 206.

Obviously, this is a non-structural design that does not satisfy 3NF of relational databases.

note

For Postgres that supports array types, using arrays would be a better choice, but for better generality of this example, string concatenation is still used here without utilizing database-specific features.

Non-Structural Association​

To support such non-structural designs, Jimmer provides the @org.babyfish.jimmer.sql.JoinSql annotation.

info

@JoinSql must and can only be used together with @ManyToMany.

Next, create an SQL function (MySQL is used as example here):

create function concatenation_string_contains(
	self varchar(100),
	id bigint  
) returns boolean
begin
	if self is null then
		return false;
	end if;
	return self regexp concat('(^|\\,)\\s*', id, '\\s*(\\,|$)'); 
end;

This function assumes that the parameter self is in the format of multiple numbers concatenated by commas, and determines whether it contains the parameter id. For example:

  • concatenation_string_contains('100, 200, 300', 200) returns true

  • concatenation_string_contains('100, 200, 300', 201) returns false

Now we can map the non-structural many-to-many association:

Book.java
@Entity
public interface Book {

    @ManyToMany
    @JoinSql(
        "concatenation_string_contains(" +
        "    %alias.author_ids, " +
        "    %target_alias.id" + 
        ")"
    )
    List<Author> authors();

    ...Omit other code...
}

The SQL expression used in @JsonSql represents the join condition, where:

  • concatenation_string_contains: The SQL function we defined earlier

  • %alias: A special token defined by Jimmer representing the alias of the current table (book table in this example)

  • %target_alias: A special token defined by Jimmer representing the alias of the target table (author table in this example)

caution

ORM automatically generates SQL (which is very important for dynamic queries, especially for ORMs like Jimmer that first proposed the Dynamic Join concept), and users cannot interfere with the aliases of tables in the final SQL.

So using %alias and %target_alias here is the only way.

It is worth mentioning that if you want to define a bidirectional many-to-many association, since Book.authors has already used @JoinSql, the other end Author.books does not need to use @JoinSql again. As before, use mappedBy to indicate that it is the inverse property.

Author.java
@Entity
public interface Author {

    @ManyToMany(mappedBy = "authors")
    List<Book> books();

    ...Omit other code... 
}

Precautions​

warning

Unless legacy database compatibility is required, do not perform non-structural design and map it with @JoinSql, because non-structural many-to-many associations have many limitations: