Mybatis complex mapping

Keywords: Java SQL

Variable parameters

The location of method parameters in java supports one syntax

Method name ([type]... Variable name)
The above expression is variable parameter

//Declare a method with variable parameters
public  void sum(int... nums){
    //Parameter... Indicates that the variables specified by this parameter are processed as an array
    //Only one method can appear in this declaration
    // And it must be the last parameter
    //The and output of all elements in nums
    int sum=0;
    for(int i=0;i<nums.length;i++){
void param(){
    //Call the method of variable parameters and directly pass in multiple parameters with matching types
    //There is no limit to 0~ more parameters

Precautions for using variable parameters

When defining a method:

The variable parameter must be the last of all parameters of the method
Method treats variable parameters as an array
Method call:

When calling a method with variable parameters, the number of parameters that can be passed in is unlimited, and the type can match
An unlimited number of parameters means you can not transfer parameters
You can also directly pass in an array with matching type

Mybatis implements dynamic deletion

There is a requirement in our project to delete rows with multiple specified IDS at one time
For example, we want to delete the row with id 2,4,7,11
The sql code is as follows: delete from t_user where id in(?,?,?,?)

However, each deletion is not necessarily 4
So we need to generate the same number of IDS according to the array of IDS to be deleted passed in by the user?
Such requirements can only be realized through dynamic sql generation in xml files
Next, we add a method in the UserMapperPlus interface to correspond to this dynamic deletion
You can use the variable parameters learned above to declare
The code is as follows

//Method for realizing dynamic deletion
Integer deleteByIds(Integer... ids);

Go back to the UserMapperPlus.xml file and write the code corresponding to this deletion

  Delete operation use delete label id Method name in corresponding interface
  Because it is an addition, deletion and modification operation,You do not need to declare the return value type resultType,Default is int
  As for parameter types,Common parameters of the system can automatically identify basic data types,String,Array, etc
collection Specifies the array to traverse  array Represents the array received by the parameter
item Represents a single element in the array during traversal,id Is the name of this element 
separator Specifies the separator that appears at each cycle interval during the cycle
foreach Write in label#{} placeholder. The content in the placeholder is the "id" specified by the item attribute
<delete id="deleteByIds">
    delete from t_user where id in(
    <foreach collection="array" item="id" separator=",">

Test class startup log
private static Logger log=
Test class

UserMapperPlus userMapper;
void deleteIds(){
    int num=userMapper.deleteByIds(2,4,7,11);

Mybatis implements dynamic modification operations

During the development of the project, there may be the following requirements:
Modify user information
The user may specify some information to modify
User name and age may be modified
You may also modify your phone and email,
This requirement may modify any line of the user
If the whole row modification needs to be queried first and then modified, it will consume performance. We need to dynamically generate sql statements that only modify the attributes entered by the user according to the attributes entered by the user

If everything can be modified, the parameter is better of User type
We can judge which attributes of User type objects are assigned and which attributes need to be modified. If they are null, they will not be modified
Declare the methods in the interface first
The code is as follows

//Method for realizing dynamic modification
Integer updateUserInfo(User user);

Add dynamically modified code to the xml file

<!--  Dynamic modification method  -->
<!--  Because the parameter of this method is not a common type provided by the system
      Need to use parameterType To specify User Full class name of -->
<!--  if Required in label test To specify a boolean Type results
       If the result is true Will if The content in the middle of the label is generated in sql Statement
       The specific logic here is:as long as user A property of the object is not empty,
       Generate and modify this property sql sentence-->
<!--  <set>Role of labels
        1.stay<set>Label location generation set keyword
        2.<set>The tag removes the last redundant","complete sql Statement generation
<update id="updateUserInfo"
    update t_user
        <if test="username != null">
        <if test="password != null">
        <if test="age != null">
        <if test="phone != null">
        <if test="email != null">
    where id=#{id}

Test class

void update(){
    User user=new User();
    //user.setUsername("Marshal Tianpeng");
    int num=userMapper.updateUserInfo(user);

Mybatis implements dynamic query

We often encounter this or similar requirements in actual development
There are several situations as follows

1. Query directly without entering criteria
select * from t_user
2. Enter a bar to query
select * from t_user where username like ?
3. Enter two items to query
select * from t_user where username like ? and age=?
4. Enter three entries to query
select * from t_user where username like ? and age=? and phone like ?

In fact, the above query is also a process of dynamically generating sql statements according to the conditions entered by the user
Method of adding dynamic query in UserMapperPlus interface
The code is as follows

List<User> findUsersByParam(
        @Param("username") String username,
        @Param("age") Integer age,
        @Param("phone") String phone);

The xml file code is as follows

<!--  Dynamic query method  -->
<!--  A query must declare a return value type resultType  -->
<!--  Mybatis The type of value returned by an object or collection in a query
                                   Specify the specific entity class type  -->
<!--  <where>Labels and<set>Label similar
       When no condition is where Keywords will not appear
       When conditions are<where>The label is responsible for handling excess and-->
<select id="findUsersByParam"
        resultType="cn.tedu.mybatis.model.User" >
        <if test="username != null">
            username like #{username}
        <if test="age != null">
            and age = #{age}
        <if test="phone != null">
            and phone like #{phone}

Test class

void select(){
    List<User> users=userMapper.findUsersByParam(
    for(User u:users){

Use of parameterType and resultType

resultType is a property that specifies the return value of the current method
All select tags must be specified for both query operations
If you are adding, deleting or modifying, you do not need to specify the default int

parameterType is a property that specifies the parameter type of the current method
Any addition, deletion, modification and query operation may be required
If the parameter type is not the basic data type, it is not String, array or collection
It must be written. For example, the entity class created by our own declaration needs to be written

Mybatis implements Association query

Association relationship in database

There are several relationships between relational database tables
One to many (many to one)
Many to many

Taking one to many as an example, let's explain the solution for processing associated queries in the Mybatis framework
We will t_ The user table is regarded as an employee table
We will design a department table to establish a one to many relationship between the Department and the employee table
Since a department has multiple employees, the Department is one party of one department, and each employee can only belong to one department, the employee is one party of many

To realize such a relationship, we must first establish such a structure in the database
Perform the following operations in sequence

-- establish t_dept Table department table
create table t_dept(
id int primary key auto_increment comment 'department id',
name varchar(50) not null         comment 'Department name'
)default charset=utf8
-- t_user A table needs to be added dept_id List your department
alter table t_user add column dept_id int
 You can do the same: ALTER TABLE t_user ADD COLUMN dept_id INT NOT NULL AFTER email
-- Add 3 rows of data to the Department table to represent 3 departments
insert into t_dept(name) values('java'),('c++'),('linux')
-- take id Less than 10 user of dept_id Assign a value of 1
update t_user set dept_id=1 where id<10
-- take id 10 or more user of dept_id The value assigned is 2
update t_user set dept_id=2 where id>=10

The relationship between the two tables depends on t_ Dept of user table_ ID column
If the value of this column is 1, it means that it belongs to the java department
However, this value can now be modified at will. If it is modified at will, the user's dept cannot be guaranteed_ The value of the ID column can correspond to a real department, which is not possible
To set Dept_ The value of ID column can only be in the existing department, so you need to set foreign key constraints

-- Create foreign key constraints
-- constraint t_user Tabular dept_id Columns can only be from t_dept Tabular id Value in column
alter table t_user
add constraint dept_user foreign key (dept_id)
references t_dept(id)
-- constraint(constraint) references(quote)

The creation of foreign keys will prevent error data when referring to data

Make the data in the database more accurate and reliable

However, because foreign keys bring a lot of maintenance operations, it is more troublesome

Now some companies do not create foreign keys when developing software, which makes it easier to maintain

sql statement for connection query

If we want to query the results as follows

The sql statement of the above query should be written like this
– query user's ID, user's username and dept's name
from t_dept d
join t_user u

What should I do if I want to execute such a connection query and return the results in java
The simplest way is to create a new entity class to complete the connection query
Create a UserVo class with the following code (VO: Value Object)

public class UserVo {
    private Integer id;
    private String username;
    private String deptname;

Create a new interface in the mapper package. The XmlMapper code is as follows

public interface XmlMapper {
    //Method of connection query
    List<UserVo> findUserDept();

This interface should correspond to a new Xml file
The code is as follows

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-// Mapper 3.0//EN"
<mapper namespace="cn.tedu.mybatis.mapper.XmlMapper">
    <select id="findUserDept"
    select,username,name deptname   Alias to correspond pojo Name in
    from t_dept d
    join t_user u

Test code

XmlMapper xmlMapper;
void join(){
    List<UserVo> users=xmlMapper.findUserDept();
    for(UserVo user: users){

Use the Mybatis query to return the associated query results

If you encounter more connection queries in actual development

If different Vo classes are created for each connection query, there will be more Vo classes

Look at the structure of this diagram

The meaning of this figure is that we can create a dept type entity class corresponding to t_dept table

In this entity class, we can declare a collection to hold the information of several user \ employees

The code of Dept class is as follows

public class Dept {
    private Integer id;
    private String name;
    //Declare a collection that holds employee objects
    private List<User> users;

We write an sql statement to query the Department information and the information of all employees contained in the department according to the specified Department id
The code is as follows

    t_dept d
left join  
    t_user u

To implement the above query, we need to add a method in the interface
Continue adding methods to the XmlMapper interface

//How to query employees in related departments
Dept findDeptWithUserById(Integer id);

Add the following code to the XmlMapper.xml file
Note the use of resultMap

<!--  We need a declaration of structure
  This structure is a Dept Structure of type,Which contains Dept Medium id and name
  Also include Dept Saved in List<User>users Information about
  There's more right here User Each column of(include sql Alias column set in statement)
  Make corresponding statements
  So that we can Mybatis Return a Dept Objects can also be Dept Medium User In collection
  Assign value to each employee
<!--  resultMap It is specifically used to set the mapping relationship between each column and each attribute in the object
  It supports mapping columns in a collection in an object
  id yes resultMap Unique identification of,Which query requires which query to be specified
  type Specifies the carrier of the return value of this result,This refers to the employee information Dept object-->
<resultMap id="deptMap" type="cn.tedu.mybatis.model.Dept">
    <!--  One result The label represents a corresponding relationship,
    finger java Which attribute in corresponds to which column in the database
      column Specifies the column name of the database query result,Corresponding database  ,property Specifies the property name in the instance class-->
    <result column="id" property="id" />
    <result column="name" property="name"/>
    <!-- collection It means collection,Represents the current Dept class
                        except id and name There is also a collection
         property Specifies the property name of the collection
         ofType Specifies the generic type of the collection-->
    <collection property="users"
        <!-- Here, you can specify which column corresponds to this query user Which property of the object -->
        <result column="userId" property="id" />
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="age" property="age" />
        <result column="phone" property="phone" />
        <result column="email" property="email" />
<select id="findDeptWithUserById"
    name, userId,
    t_dept d
left join
    t_user u

Test code

void dept(){
    Dept d=xmlMapper.findDeptWithUserById(3);


Query diagram

Posted by Ghost_81st on Sun, 24 Oct 2021 20:59:16 -0700