Before building a system, designing its database tables and creating class diagrams (data models) are also very important steps.

In a complex system, there are various relationships among the data, which also causes problems for us to design the data model. Today I will share the relationship between the tables in the database and the actual operation scheme.

1, an overview of the

In fact, the relationship between data is nothing more than this:

  • One to one
  • One to many (many to one is the same thing)
  • Many to many

In fact, one-to-one is the best to understand and operate, in the implementation of a certain degree of difficulty is many-to-many.

So today I’m going to focus on sharing how we actually implement these relationships and how we do it in the SSM framework. Sample code repositories are presented at the end of this article.

Today I’m going to explain how this relationship works, using characters, guilds, and weapons in Princess Link Re:Dive as an example.

In the Princess Link game, there are many characters, each character belongs to a guild (organization), one character needs to equip some weapons, and each character has her own special weapons.

Here you can see:

  • There is a one-to-one relationship between a character and an exclusive weapon. A character can only have one exclusive weapon, and one exclusive weapon can only belong to one character
  • Guilds and roles are one-to-many. A role can belong to only one guild, and a guild can have multiple roles
  • Weapons and characters have a many-to-many relationship, one weapon can be used by multiple characters, and one character can be equipped with multiple weapons

One-to-one relationships are easy, so today I’m going to focus on one-to-many and many-to-many relationships.

2. Design of database tables

Based on the above information, let’s design the database table step by step.

The first is to establish three tables: character, guild and weapon.

Select * from guild table where primary key id = 1 and primary key ID = 1;

In other words, one-to-many relationship, only need to add a field in the “many” table, the primary key field of the table “one”, as the foreign key of the “many” table, to achieve the one-to-many relationship construction and connection, it can be said that this is very simple.

Then we implement many-to-many relationships between characters and weapons. Many-to-many requires an additional table to be created between the two tables to represent the relationship between the two tables. This table usually has no primary key, but only foreign key, whose foreign key is the primary key of the two “many” tables:

It can be seen that the weapon_character table is the weapons-role relationship table, which is used to build the many-to-many bridge between weapons and roles.

At this point, our database table design is complete!

In addition to creating the table, we will also initialize some sample data:

Drop table if exists' character '; create table `character` ( `id` int not null, `name` varchar(16) not null unique, `type` varchar(8) not null, `guild_id` int not null, primary key (`id`) ) engine = InnoDB default charset = utf8mb4; -- Drop table if exists' guild '; create table `guild` ( `id` int not null, `name` varchar(16) not null unique, primary key (`id`) ) engine = InnoDB default charset = utf8mb4; -- Weapon drop table if exists' weapon '; create table `weapon` ( `id` int not null, `name` varchar(16) not null unique, primary key (`id`) ) engine = InnoDB default charset = utf8mb4; Drop table if exists' weapon_character '; create table `weapon_character` ( `character_id` int not null, `weapon_id` int not null ) engine = InnoDB default charset = utf8mb4; Insert into 'guild' values (0, 'guild '), (1,' Guild '), (2, 'Guild '); Insert into ` character ` values (0, 'mirror China', 'defender mage output, 0), (1,' meimei ', 'defender physical output, 0), (2,' not play when ', 'vanguard physical auxiliary, 0), (3,' omiya ', 'vanguard physical tanks', 1), (4,' she ', 'defender mage auxiliary', 1), (5, 'the leah,' defender mage output, 1), (6, step 'true', 'defender mage auxiliary, 2), (7,' true jean, 'vanguard physical output, 2), (8. 'Xiangcheng ',' Defender Mage assist ', 2); Insert into ` weapon ` values (0, 'deep rod sacrifice month), (1,' fine ice her bow), (2, 'celestial star field staff), (3,' the dominator of water up the sword god '), (4, 'fireworks peony ornamental hairpin), (5,' destroy of ghost god '). (6, 'Sea Dragon hair ornament '), (7,' Arashi Storm guard '); insert into `weapon_character` values (0, 0), (0, 1), (1, 3), (1, 4), (2, 6), (2, 7), (3, 5), (3, 6), (4, 1), (4, 2), (5, 0), (5, 1), (6, 1), (6, 2), (7, 3), (7, 4), (8, 1), (8, 2);Copy the code

Ok, here are a few simple query examples.

Query which guild “miyako” belongs to:

Select * from 'guild' where 'guild' = 'guild_id' where 'guild' = 'guild_id '; select * from' guild 'where' guild '=' guild_id 'where' guild '=' guild_id ';Copy the code

Results:

Query all members of the guild in “Little Sugar”

select `character`.* from `character` left join `guild` on `character`.guild_id = `guild`.id where `guild`.name = 'Little sweetie ';Copy the code

Results:

Query the weapons used by the character “Mirror Hua”

select `weapon`.* from `weapon` left join `weapon_character` on `weapon`.id = `weapon_character`.weapon_id left join Id where 'character_id' = 'character_id ';Copy the code

Results:

Query which character uses the weapon “Ice Princess’s bow”

select `character`.* from `character` left join `weapon_character` on `character`.id = `weapon_character`.character_id Weapon_id = 'weapon'. Id WHERE 'weapon'. Name = 'weapon ';Copy the code

Results:

It can be seen that the one-to-one and one-to-many relationships between tables are established in the above way, and flexible two-way queries can be realized, mainly through the left connection, to achieve a variety of relational queries.

One-to-one relationships are even easier, with the primary key of either as the foreign key of the other.

Normally, SSM development is by ID query, and will specify the query field in select and alias, here for convenience, by name query.Copy the code

3. Cascading in the SSM

So how should this relationship be realized in MyBatis? This is usually done by cascading.

I’m going to design the class diagram first, because there’s a lot of correlation going on here, so I’m going to design a class that’s a little bit different than the simple case. That is, database table fields and class attributes no longer correspond exactly one-to-one.

Let’s make the class diagram as follows:

It was noted that the properties in purple were somewhat different from the above database table structure, and there was no design of the “weapons-character” class, where properties can be complex data structures, but database table fields are not.

In other words, when we design the database tables as Java classes, we need to do some “transformations” in such interrelated data:

In the one-to-many case, we need to design the primary key field representing “one” in the “many” table to be a “one” attribute in the “many” class:

Therefore, in the many-to-many case, there is no need to design the class of its relational table, just add a List or Set type attribute to the two corresponding classes to indicate that they are related to each other:

Now that you’ve designed the class, you need to write MyBatis Mapper XML, where the real difficulty lies.

For ease of sight, the following sample XML file I post only the resultMap node and select node.

Our DAO layer only writes query methods, so we don’t post DAO class code here.

Start with the simplest, guild Mapper XML file:

<resultMap id="guildResultMap" type="com.example.relationmapping.dataobject.Guild">
   <id column="id" property="id"/>
   <result column="name" property="name"/>
</resultMap>

<select id="getById" resultMap="guildResultMap">
   select *
   from `guild`
   where id = #{id}
</select>
Copy the code

Without further ado, write the character’s:

<resultMap id="characterResultMap" type="com.example.relationmapping.dataobject.Character">
   <id column="id" property="id"/>
   <result column="name" property="name"/>
   <result column="type" property="type"/>
   <association property="guild" select="com.example.relationmapping.dao.GuildDAO.getById" column="guild_id" fetchType="lazy"/>
   <collection property="weapons" ofType="com.example.relationmapping.dataobject.Weapon" fetchType="lazy">
      <id column="weapon_id" property="id"/>
      <result column="weapon_name" property="name"/>
   </collection>
</resultMap>

<select id="getByName" resultMap="characterResultMap">
   select `character`.*, `weapon`.id as weapon_id, `weapon`.name as weapon_name
   from `character`
          left join `weapon_character` on `character`.id = `weapon_character`.character_id
          left join `weapon` on `weapon_character`.weapon_id = `weapon`.id
   where `character`.name = #{name}
</select>
Copy the code

Here we focus on association and Collection nodes in resultMap. Let’s look at them one by one:

Association represents an association of a complex type. It is generally used in a resultMap of “many” in a one-to-many, and represents the attribute of “one” (guild) in the middle of the class (role) of “many”. The meanings of the attributes above are:

  • propertyRepresents the name of the property in the class corresponding to this complex type field
  • selectRepresents the database table method to query this field, filling in the fully qualified name of the method in the DAO
  • columnTo query parameters, use<select>The value of a field in the node statement is used as a parameter. Enter the method specified by SELECT for query. Multiple parameters are separated by commas (,)
  • fetchTypeGenerally set tolazyLazy query, that is, when the table is queried but the complex field is not used, the field will not be queried to improve performance and solve the N+1 problem

So how does the association work? Let’s see.

As we know, the resultMap function is to map the database table to our Java class. After retrieving the record, the value in the record is assigned to the corresponding attribute of the corresponding class.

In the example above, the role table has id, name, type, and guild_id, so the < SELECT > node will fetch these raw data first.

Association, where the parameter (Column) is set to guild_ID and the query method (SELECT) is set to getById in GuildDAO. MyBatis will put out in the record guild_id field value as a parameter to use getById method for query, check a Guild as an example, the instance is assigned to the Character of Guild. As you can see, there are actually two queries.

Guild_id = guild_id; guild_id = guild_id; guild_id = guild_id;

Collection indicates that the field type is a collection, and many-to-many classes use collection to represent their collection properties in a resultMap. The meanings of the above attributes:

  • propertyRepresents the name of the property in the corresponding class of this collection, similar to the above
  • ofTypeRepresents the types of elements in this collection
  • fetchTypeSame as above

In Collection, we define the mapping of the collection element classes.

So how does collection work?

First, let’s execute the statement in the < SELECT > node to see what results we get:

We should only query for one character, but there are multiple results and the character information is repeated, so how do we merge the weapon information into one set? Clearly, collection works.

In the above resultMap, except that the ID, type, and name of the role table are assigned to the corresponding attribute of the role instance, guild_id is converted to the corresponding complex attribute through association. The remaining fields, weapon_id and weapon_name, are clearly included in the Weapon class instance because the mapping to the Weapon class is defined in the Collection. MyBatis then generates multiple weapon instances and places each record value in them. These weapon instances form a collection assigned to the used weapon field in the character class.

Consider the < SELECT > node, also through the join of multiple tables, implement associative query. In other words, the collection here is a cascade based on an associative query.

Weapon table id = weapon_id; weapon table name = weapon_name; weapon table id = weapon_id; weapon table name = weapon_name; If you do not distinguish between them, a resultMap mapping error will occur (because the resultMap contains the mapping of both the character table and the weapon table).

Weapon classes also have character lists, so why not include them in the collection? Because it's not necessary, and it's an infinite loop.Copy the code

Ok, so that completes the XML writing of the characters, and in turn, the weapons are pretty much the same, so I won’t post the code here.

Finally we Test Test, write two query methods in Test and call:

/** @param name Character name */ private void getCharacterInfo(String name) {Character XCW = characterDAO.getByName(name); System.out.println(" xcw.getName() "); System.out.println(" role type: "+ xcw.getType()); System.out.println(" guild: "+ xcw.getGuild().getName()); System.out.println(" Character weapon: "); for (Weapon weapon : xcw.getWeapons()) { System.out.println(" - " + weapon.getName()); }} @param name Weapon name */ private void getWeaponInfo(String name) {Weapon Weapon = weaponDAO.getByName(name); System.out.println(" weapon name: "+ weapon.getName()); System.out.println(" Use this weapon as a role: "); for (Character character : weapon.getCharacters()) { System.out.println(" - " + character.getName() + " " + character.getType() + " " + character.getGuild().getName()); }} @contextloads () {characterInfo (); GetWeaponInfo (" water Lord god makes sword "); }Copy the code

Results:

4,

The database is abstract, and so is the back-end data modeling. It is important to understand the relationships between the data models and how they relate to each other. Here, especially the cascading of MyBatis is still difficult, so it is necessary to roughly understand its principle, the corresponding meaning of each parameter, and associated query.

Sample repository address