Fluent Mybatis Introduction 4: Many-to-many, dealing with messy relationships

We can’t get around the one-to-one, one-to-many, many-to-many relationships in the underlying table. The best ORM frameworks in the industry have their own answers, which can be summarized in the following ways:

Hibernate and JPA basically shield development from processing the underlying data, just setting up the data cascade at the Model layer. But this setup is also often the beginning of a nightmare.
Mybatis provides simple @one@many annotations and then writes XML mappings to provide cascading processing.
Another option is to not rely on the framework at all and apply your own control.

Because FluentMybatis is based on MyBatis to do packaging and expansion, so here mainly talk about the treatment of MyBatis, and give FluentMybatis liberation scheme.

As in the previous articles, we first set up the business scenario, with the following scenario: boys and girls, men and women normally only have one incumbent (not discussed here), but may have multiple exes; At the same time, they have their own hobbies.

The following three tables can be created:

The data dictionary

CREATE TABLE t_member
(
    id           bigint(21) unsigned auto_increment primary key COMMENT 'primary key id',
    user_name    varchar(45) DEFAULT NULL COMMENT 'name',
    is_girl      tinyint(1)  DEFAULT 0 COMMENT '0: boy; 1: the girl ',
    age          int         DEFAULT NULL COMMENT 'age',
    school       varchar(20) DEFAULT NULL COMMENT 'school',
    gmt_created  datetime    DEFAULT NULL COMMENT 'Creation time',
    gmt_modified datetime    DEFAULT NULL COMMENT 'Update Time',
    is_deleted   tinyint(1)  DEFAULT 0 COMMENT 'Logical deletion or not'
) ENGINE = InnoDB
  CHARACTER SET = utf8 COMMENT = 'Membership List: Information for Girls or boys';

CREATE TABLE t_member_love
(
    id           bigint(21) unsigned auto_increment primary key COMMENT 'primary key id',
    girl_id      bigint(21) NOT NULL COMMENT 'member ';,
    boy_id       bigint(21) NOT NULL COMMENT 'member ';,
    status       varchar(45) DEFAULT NULL COMMENT 'state',
    gmt_created  datetime    DEFAULT NULL COMMENT 'Creation time',
    gmt_modified datetime    DEFAULT NULL COMMENT 'Update Time',
    is_deleted   tinyint(2)  DEFAULT 0 COMMENT 'Logical deletion or not'
) ENGINE = InnoDB
  CHARACTER SET = utf8 COMMENT = 'Member relationship';

CREATE TABLE t_member_favorite
(
    id           bigint(21) unsigned auto_increment primary key COMMENT 'primary key id',
    member_id    bigint(21) NOT NULL COMMENT 'member ';,
    favorite     varchar(45) DEFAULT NULL COMMENT 'Hobbies: movies, mountain climbing, hiking... ',
    gmt_created  datetime    DEFAULT NULL COMMENT 'Creation time',
    gmt_modified datetime    DEFAULT NULL COMMENT 'Update Time',
    is_deleted   tinyint(2)  DEFAULT 0 COMMENT 'Logical deletion or not'
) ENGINE = InnoDB
  CHARACTER SET = utf8 COMMENT = 'Member hobbies';
Copy the code

Add project Maven dependencies

The concrete pom.xml file

Code generation

public class AppEntityGenerator {
    static final String url = "jdbc:mysql://localhost:3306/fluent_mybatis_demo? useSSL=false&useUnicode=true&characterEncoding=utf-8";
    /** * Generate code package path */
    static final String basePackage = "cn.org.fluent.mybatis.many2many.demo";

    public static void main(String[] args) {
        FileGenerator.build(Noting.class);
    }

    @tables (/** database connection info **/ url = url, username = "root", password = "password", /** class parent package path **/ basePack = basePackage, /** Entity code source directory **/ srcDir = "example/many2many_demo/ SRC /main/ Java ", **/ gmtCreated = "gmT_create ", gmtModified =" gmT_modified ", logicDeleted = "is_deleted", **/ tables = @table (value = {"t_member", "t_member_love", "t_member_favorite"}, tablePrefix = "t_") )
    static class Noting {}}Copy the code

This generates three Entity classes: MemberEntity, MemberFavoriteEntity, and MemberLoveEntity.

Relationship analysis

Now let’s see what’s going on here

One to many: A member can have more than one hobby
Many-to-many: a member can have more than one boyfriend or girlfriend (ex + current)
One to one: a member can only have one current boyfriend or girlfriend

Mybatis processing techniques

Mybatis provides @one and @many annotations to deal with simple (only primary key and foreign key dependence) one-to-one relationship, and one-to-many relationship specific to the above relationship, MyBatis can only query members’ hobbies, with conditional (not only through foreign key) current boyfriend and girlfriend one-to-one relationship has no way to deal with.

Here I will not specifically expand mybatis configuration syntax, interested readers can directly refer to digging gold inside the article:

Mybatis one-to-many, many-to-one processing

MyBatis complex Sql query (one-to-one, one-to-many)

MyBatis learning summary (4), annotation & many-to-one, one-to-many

MyBatis series 4: One-to-one, one-to-many, many-to-many query and delayed loading (N+1 problem) analysis

Since MyBatis can only deal with simple association relation, Fluent Mybatis does not directly encapsulate the processing mode of MyBatis. Then how does Fluent Mybatis deal with the above association relation? Let’s start with a list of one-to-many hobbies that MyBatis can also handle

One to many hobby list handling

Fluent Mybatis automatically returns the corresponding hobby list according to MemberEntity, which requires the following Settings:

MemberEntity inherits the RichEntity base class
Add method findMyFavorite() to MemberEntity class
Annotate the findMyFavorite method with @refMethod
Add the relationship: in the annotation “memberId = id”, meaning is MemberFavoriteEntity memberId equals MemberEntity. Id

The code snippet below, all of which can be generated from code, is added manually here just for illustration

public class MemberEntity extends RichEntity implements IEntity {
    // ...
    /** * My hobby list **@return* /
    @RefMethod("memberId=id")
    public List<MemberFavoriteEntity> findMyFavorite(a) {
        return super.loadCache("findMyFavorite", MemberEntity.class); }}Copy the code

Select * from member-sources where Refs = Refs; select * from member-sources where Refs = Refs

/** ** Refs: * O - query, updater factory class singleton references * O - Apply all Mapper Bean references * O-Entity associated object lazy load query implementation **@author powered by FluentMybatis
 */
public abstract class Refs extends EntityRefQuery {
  public List<MemberFavoriteEntity> findMyFavoriteOfMemberEntity(MemberEntity entity) {
    return memberFavoriteMapper.listEntity(newMemberFavoriteQuery() .where.memberId().eq(entity.getId()) .end()); }}Copy the code

In this class to automatically generate a method: findMyFavoriteOfMemberEntity, into is MemberEntity, the parameter is a List, inside the implementation according to the member’s id of all the interests of the members.

Increase the Spring Bean

Create a new class: AllRelationQuery (name whatever you like and your business), inherit Refs, and add AllRelationQuery to Spring management.

@Service
public class AllRelationQuery extends Refs {}Copy the code

Old school. Write a test to verify it

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class FindMemberFavoriteTest {
    @Autowired
    private MemberMapper memberMapper;

    @Before
    public void setup(a) {
        // Omit the data preparation part
    }

    @Test
    public void findMyFavorite(a) {
        MemberEntity member = memberMapper.findById(1L);
        List<MemberFavoriteEntity> favorites = member.findMyFavorite();
        System.out.println("Hobbies:"+ favorites.size()); }}Copy the code

View the console log output

DEBUG - ==> Preparing: SELECT id, ... , user_name FROM t_member WHERE id = ? DEBUG - ==> Parameters: 1(Long) DEBUG - &lt; == Total: 1 DEBUG - ==> Preparing: SELECT id, ... , member_id FROM t_member_favorite WHERE member_id = ? DEBUG - ==> Parameters: 1(Long) DEBUG - &lt; == Total: 2 Hobby: 2Copy the code

As the log shows, Fluent Mybatis returns the list of hobbies as expected.

Add a little spice to a one-to-many relationship

Is_deleted =0 (deleted=0) =0 (deleted=0) =0 (deleted=0) =0 (deleted=0)

With Mybatis we shrug our shoulders, throw our hands in the air and say: Fluent Mybatis supports this kind of scenario very well. We just give the @ RefMethod annotation value add some conditions, MemberFavoriteEntity. MemberId = MemberEntity. The id and the logic of Favorite delete logo and Member list, concrete are defined as follows:

public class MemberEntity extends RichEntity implements IEntity {
    @RefMethod("memberId=id && isDeleted=isDeleted")
    public List<MemberFavoriteEntity> findMyFavorite(a) {
        return super.loadCache("findMyFavorite", MemberEntity.class); }}Copy the code

Recompile the project and observe the Refs code

public abstract class Refs extends EntityRefQuery {
    public List<MemberFavoriteEntity> findMyFavoriteOfMemberEntity(MemberEntity entity) {
        return memberFavoriteMapper.listEntity(newMemberFavoriteQuery() .where.isDeleted().eq(entity.getIsDeleted()) .and.memberId().eq(entity.getId()) .end()); }}Copy the code

A logical delete condition is attached to the query condition

Run the test, look at the log

DEBUG - ==> Preparing: SELECT id, ... , user_name FROM t_member WHERE id = ? DEBUG - ==> Parameters: 1(Long) DEBUG - &lt; == Total: 1 DEBUG - ==> Preparing: SELECT id, ... , member_id FROM t_member_favorite WHERE is_deleted = ? AND member_id = ? DEBUG - ==> Parameters: false(Boolean), 1(Long) DEBUG - &lt; == Total: 2 Hobby: 2Copy the code

FluentMybatis easily handles one-to-many relationships with multiple condition associations, which is not limited to logical deletions in business but also extends to deployment environment identification (deploy_ENV), tenant relationships, and state-related relationships that are only used in your business.

Fluent Mybatis for many-to-many relationship processing

Fluent Mybatis can easily handle one-to-one, one-to-many simple and multi-condition correlation, but the many-to-many does not provide automatic code generation processing means. Because many-to-many, it essentially involves 3 tables, A table, B table, AB associated table. However, Fluent Mybatis still provides semi-automatic support for such scenarios. For example, we need MemberEntity to return a list of all ex-lovers.

Define the method in MemberEntity: exFriends()

public class MemberEntity extends RichEntity implements IEntity {
    /** * List of ex-boyfriends/girlfriends **@return* /
    @RefMethod
    public List<MemberEntity> findExFriends(a) {
        return super.loadCache("findExFriends", MemberEntity.class); }}Copy the code

Unlike the automated one-to-many relationship above, no query criteria are set on @refMethod, so we recompile the project. We observe the Refs, besides just findMyFavoriteOfMemberEntity method, is more than an abstract method: findExFriendsOfMemberEntity

public abstract class Refs extends EntityRefQuery {
    /** * Returns the list of ex friends */
    public abstract List<MemberEntity> findExFriendsOfMemberEntity(MemberEntity entity);
}
Copy the code

Before we start coding, let’s analyze the messy boyfriend/girlfriend relationship

In the member table, we use a gender field is_girl to distinguish between male and female. In the relationship table, there are two foreign keys girl_id and boy_id to identify a couple of lovers. SQL > select * from member where member = member;

select * from t_member
where is_deleted=0
  and id in (select boy_id from t_memeber_love
              where status = 'previous'
                and girl_id = ? - girl id
                and is_deleted = 0
)  
Copy the code

Select * from member where member = male; select * from member where member = male;

select * from t_member
where is_deleted=0
  and id in (select girl_id from t_memeber_love
              where status = 'previous'
                and  boy_id= ? Id - boy
                and is_deleted = 0
)  
Copy the code

Query the former male (female) friends list function

In general, in order to implement the branch query above, you need to configure such a tag code branch in the MYbatis XML file, or implement if(…) in Java code. {}else{} branch of code logic. Let’s look at fluent Mybatis how to achieve the above query? We can on just defined Refs subclass implementation findExFriendsOfMemberEntity own logic.

@Service
public class AllRelationQuery extends Refs {
    @Override
    public List<MemberEntity> findExFriendsOfMemberEntity(MemberEntity entity) {
        MemberQuery query = new MemberQuery()
            .where.isDeleted().isFalse()
            .and.id().in(MemberLoveQuery.class, q -> q
                .select(entity.getIsGirl() ? boyId.column : girlId.column)
                .where.status().eq("Former") .and.isDeleted().isFalse() .and.girlId().eq(entity.getId(), o -> entity.getIsGirl()) .and.boyId().eq(entity.getId(), o -> ! entity.getIsGirl()) .end()) .end();returnmemberMapper.listEntity(query); }}Copy the code

Write the test and look at log

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class FindExFriendsTest {
    @Autowired
    private MemberMapper memberMapper;

    @Test
    public void findExBoyFriends(a) {
        MemberEntity member = memberMapper.findById(1L);
        System.out.println("Is it a girl?"+ member.getIsGirl()); List<MemberEntity> boyFriends = member.findExFriends(); System.out.println(boyFriends); }}Copy the code

Console log

DEBUG - ==>  Preparing: SELECT id, ..., user_name FROM t_member WHERE id = ?  
DEBUG - ==> Parameters: 1(Long) 
DEBUG - <==      Total: 1 
是否女孩:true
DEBUG - ==>  Preparing: SELECT id, ..., user_name FROM t_member 
    WHERE is_deleted = ? 
    AND id IN (SELECT boy_id 
        FROM t_member_love 
        WHERE status = ? 
        AND is_deleted = ? 
        AND girl_id = ?)  
DEBUG - ==> Parameters: false(Boolean), 前任(String), false(Boolean), 1(Long) 
DEBUG - <==      Total: 1 
[MemberEntity(id=2, gmtModified=Sun Nov 08 12:31:57 CST 2020, isDeleted=false, age=null, gmtCreated=null, isGirl=false, school=null, userName=mike)]
Copy the code

As shown in the log, when querying the list of ex-boyfriends, the condition will switch branches according to whether the Member is a girl, which is also the powerful part of fluent Mybatis dynamic condition.

Code generation Settings

So far, we have basically explained how to use Fluent Mybatis to realize one-to-one, one-to-many, many-to-many and complex logic implementation under these relationships. However, methods in Entity are manually added during the demonstration. If the code is generated again, Entity code will be covered. You need to do manual identification.

In fact, all of the Entity code shown above can be set to code generation so that you don’t have to worry about code coverage. The full code generation is shown below, and the various Settings are explained below.

public class AppEntityFullGenerator {
    static final String url = "jdbc:mysql://localhost:3306/fluent_mybatis_demo? useSSL=false&useUnicode=true&characterEncoding=utf-8";
    /** * Generate code package path */
    static final String basePackage = "cn.org.fluent.mybatis.many2many.demo";

    public static void main(String[] args) {
        FileGenerator.build(Noting.class);
    }

    @tables (/** database connection info **/ url = url, username = "root", password = "password", /** class parent package path **/ basePack = basePackage, /** Entity code source directory **/ srcDir = "example/many2many_demo/ SRC /main/ Java ", **/ gmtCreated = "gmT_create ", gmtModified =" gmT_modified ", logicDeleted = "is_deleted", **/ tables = @table (value = {"t_member", "t_member_love", "t_member_favorite"}, tablePrefix = "t_"), relations = { @Relation(method = "findMyFavorite", source = "t_member", target = "t_member_favorite", type = RelationType.OneWay_0_N , where = "id=member_id && is_deleted=is_deleted"), @Relation(method = "findExFriends", source = "t_member", target = "t_member", type = RelationType.OneWay_0_N), @Relation(method = "findCurrFriend", source = "t_member", target = "t_member", type = RelationType.OneWay_0_1) } )
    static class Noting {}}Copy the code

Compared with the previous generation code, the complete generation code has more relations attribute on the annotation. Relation attribute is used to generate association relation on the Entity code, and three @ relation are defined here

FindMyFavorite: One-to-many relationship, type= relationtype. OneWay_0_N

Find member’s hobbies

The Entity corresponding to the source table that defines the method, in this case MemberEntity
The target table is the returned Entity of the associated query, here is MemberFavoriteEntity, and because the relationship is * trust-to-many, when generating the code, the return value List
Where sets association conditions and can have more than one condition

The value is “ID =member_id && is_deleted=is_deleted”, indicating

where t_member.id = t_member_favorite.id
and t_member.is_deleted = t_member_favorite.is_deleted
Copy the code

The corresponding MemberEntity generated code is the annotation value of @RefMethod on the findMyFavorite method

@RefMethod("isDeleted = isDeleted && memberId = id")
Copy the code

findExFriends:

Look for lists of ex-boyfriends and girlfriends

In a one-to-many relationship, the source and destination tables are the same
The lack of a WHERE condition means that no assignment is made on @refMethod, and compilation does not automatically generate code, requiring manual implementation of the logic

findCurrFriend

Find your current boyfriend or girlfriend

In a one-to-one relationship, the source table and target table are the same
No WHERE condition is set and the logic needs to be implemented manually

conclusion

Fluent Mybatis has more functional advantages than MyBatis in dealing with association relations, and it is easy for technicians to track the code implementation logic.

If you feel fluent Mybatis is good, welcome to help forward and like, so that the good tool can benefit everyone

Article Sample code

Fluent Mybatis article series

Fluent MyBatis introduction a

FluentMybatis Introduction ii

Introduction 3: Complex Query & Table Query

Fluent Mybatis, native Mybatis, Mybatis Plus three function comparison

Fluent Mybatis document and source code

Fluent Mybatis documentation & examples

Fluent Mybatis Gitee

Fluent Mybatis GitHub

Fluent Mybatis Introduction 4: Many-to-many, dealing with messy relationships

Relationship analysis

Mybatis processing techniques

One to many hobby list handling

Add a little spice to a one-to-many relationship

Fluent Mybatis for many-to-many relationship processing

Code generation Settings

conclusion

Related Posts

Quick-cocos2d-x learning series 11 MVC examples

Spring loops rely on parsing

How to use annotations elegant record operation logs | sprout new writing open source 01