Springboot using MyBatis annotations for one-to-many and many-to-many queries (2)

Springboot using MyBatis annotations for one-to-many and many-to-many queries

GitHub's full sample project address Kingboy-springboot-data

First, the simulation of business queries

Users in the system have unique address information, each user can have a multi-vehicle car, similar to the following structure

|--user    |--address    |--Carlist        |--car1        |--car2

　　

Second, the corresponding entity classes are as follows

/Omit setter/Getter Public classAddress {PrivateLong ID; PrivateString Province; PrivateString City; Public classCar {PrivateLong ID; PrivateString color; PrivateString name; //User ID    PrivateLong userId;} Public classUser {PrivateLong ID; //address information, and the user is a one-to-one relationship    Privateaddress address; //Address ID    PrivateLong Addressid; //The user owns the car, and the user is a one-to-many relationship    PrivateList<car>cars;}

Third, the corresponding building statement and simulation data are as follows

CREATE TABLE IF not EXISTS ' user ' (' id ' int (one) not null auto_increment, ' nick_name ' varchar () DEFAULT NULL, ' addres s_id ' int (one) default NULL, PRIMARY KEY (' id ')) engine=innodb default Charset=utf8; CREATE TABLE IF not EXISTS ' address ' (' id ' int (one) not null auto_increment, ' province ' varchar () ' DEFAULT NULL ' ' varchar (') default NULL, PRIMARY KEY (' id ')) engine=innodb default Charset=utf8; CREATE TABLE IF not EXISTS ' car ' (' id ' int (one) not null auto_increment, ' color ' varchar () DEFAULT NULL, ' name ' Varch    AR (+) default NULL, ' user_id ' int (one) default NULL, PRIMARY KEY (' id ')) engine=innodb default Charset=utf8;insert into ' User ' VALUES (' 1 ', ' Baby ', ' 1 '), (' 2 ', ' Kingboy ', ' 2 '), (' 3 ', ' Boy ', ' 3 '), (' 4 ', ' Kingbaby ', ' 4 ');    To ' address ' VALUES (' 1 ', ' Beijing ', ' Beijing '), (' 2 ', ' Tianjin ', ' Tianjin '), (' 3 ', ' Anhui ', ' Suzhou '), (' 4 ', ' Guangdong ', ' Guangzhou '); INSERT into ' Car ' VALUES (' 1 ', ' green ', ' Land Rover ', ' 1 '), (' 2 ', ' White ', ' Mercedes ', ' 2 '), (' 3 ', ' Blue ', ' Maserati ', '4 '), (' 4 ', ' Yellow ', ' Lamborghini ', ' 4 '); 

Iv. @One One-to-one mapping

To get the unique address of the user, for example, first we define a query method that queries the address based on the address ID.

 Public Interface addressrepository {    /** *     base Address ID query     addresses *    /@Select ( " SELECT * from ' address ' WHERE id = #{id} " )    Address Findaddressbyid (Long ID);}

Then we define a way to query the user based on the user ID

 Public Interface userrepository {    @Select ("select * from ' user ' where id = #{id}")    user Finduserwithaddress (Long ID);}

The Address property in the user object we queried at this time is empty, and there is no association with address.
So we're going to pass the addressid in user to Addressrepository's method of querying the address,
Then we assign the address object addresses of the query to the user's addressing property, so what do we do?

 Public Interfaceuserrepository {@Select ("SELECT * from ' user ' where id = #{id}") @Results ({@Result ( property="Address", column ="address_id", One= @One (Select="com.kingboy.repository.address.AddressRepository.findAddressById")) }) User finduserwithaddress (Long ID);}

We're going to use the @resutl annotation to configure the returned results,
-Property = "Address", indicating that the returned query result is assigned to the address attribute of user
-column = "address_id" refers to the Address_ in the user table ID as a query parameter for COM.KINGBOY.REPOSITORY.ADDRESS.ADDRESSREPOSITORY.FINDADDRESSBYID
-one means that this is a single query
-@One (select = "Method Full path") represents the method we call

V. @Many one-to-many queries

To get all car cars owned by the user for example, first we define a query method based on the user ID of the car

Public interface Carrepository {    /**     * Query All cars *    /@Select ("SELECT * from ' car ' WHERE user_id = #{use) based on user ID RID} ")    list<car> Findcarbyuserid (Long userId);

Then we define a way to query the user based on the user ID

 Public Interface userrepository {    @Select ("select * from ' user ' where id = #{id}")    user Finduserwithaddress (Long ID);}

The list property in the user object we queried at this time is empty, and there is no association with the car's query method.
So we're going to pass the user ID to Carrepository's method of querying the car,
Then we assign a list of the queried collection object to the user's cars property, so what do we do?

 Public InterfaceUserrepository {/** * Query with car information User =============== demo one-to-many (about many-to-many is actually two a-to-many composition)*/@Select ("SELECT * from ' user ' WHERE id = #{id}") @Results ({@Result ( property="Cars", column ="ID", many= @Many (Select="com.kingboy.repository.car.CarRepository.findCarByUserId")) }) User Getuserwithcar (Long ID);}

-Property =-column =--@Many (Select = "method Full path") represents the method we call, Method parameter UserID is the column value specified above column

Vi. Summary of @One @Many

First we unify the concept: Query address or car method, followed by collectively referred to as the user's subordinate query.

Common:
-whether one-to-one or a-to-many, is implemented by a subordinate query, we need to define this dependent query method.
-The full path of the secondary query method is specified in the main query method through @one, @Many.
-All pass arguments to the attached method through column.

Different points:
-One to the other, then the secondary method returns a separate object
-one-to-many, then the subordinate method returns a collection of objects

Springboot using MyBatis annotations for one-to-many and many-to-many queries (2)