Automatic camel case mapping; use of Mybatis-Plus; junit unit testing; Lombok automatically generates get set of entity classes with no parameters and parameters; detailed cases are attached! !

Introduction to MybatisPlus

Official website: https://baomidou.com/

MyBatis-Plus (referred to as MP) is an enhancement tool for MyBatis. Based on MyBatis, it only enhances without making changes. It is created to simplify development and improve efficiency (see the official website for details)

Mybatis automatic camel case mapping

Implementation steps:

1. The database follows the database naming rules, words are separated by underscores, and Java follows camel case naming.

2. Open the configuration in the mybatis configuration file. Pay attention to the order of the settings tags in the configuration file

   <settings>
       <setting name="mapUnderscoreToCamelCase" value="true"/>
   </settings>
   

3. example:

   //javabeans
   public class Staff{<!-- -->
       private int staffId;
       private String staffName;
       private String staffHobby;
   }
   

   -- Fields of the table in the data port
   int staff_id
   varchar(20) staff_name
   varchar(20) staff_hobby
   

   At this time, the query results do not need to be mapped to the result set.

junit unit test

Implementation steps:

1. pom.xml import coordinates (dependency)

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
   </dependency>
   

2. Note:

   • @Before: Run before executing @Test
   • @Test: similar to main method
   • @After: Run after executing @Test

3. example

   InputStream stream = null;
   SqlSession sqlSession = null;
   StaffMapper sMapper = null;
   
   @Before
   public void beforeMethod(){<!-- -->
       try {<!-- -->
           stream = Resources.getResourceAsStream("mybatis-config.xml");
           SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);
           sqlSession = build.openSession(true);
           sMapper = sqlSession.getMapper(StaffMapper.class);
       } catch (IOException e) {<!-- -->
           e.printStackTrace();
       }
   }
   
   @Test
   public void show1(){<!-- -->
       int i = sMapper.insertOne(new Staff("王二狗", "Smoking"));
   }
   
   @After
   public void AfterMethod(){<!-- -->
       try {<!-- -->
           sqlSession.close();
           stream.close();
       } catch (IOException e) {<!-- -->
           e.printStackTrace();
       }
   }
   

lombok

Meaning: Automatically generate the get set of the entity class without parameters or with parameters

Implementation steps:

1. The idea tool installs plug-ins (it only does it once) and intrusive design (if you pull the code into IDEA without lombok, an error will be reported)

   

2. pom.xml import coordinates (dependency)

   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.28</version>
   </dependency>
   

3. Comment

   • @NoArgsConstructor: no-argument constructor

   • @AllArgsConstructor: full parameter constructor

   • @Data: get, set, toString methods

4. example:

   // After adding comments, there is no need to write get, set, toString, no parameters, and parameterized construction methods. If you need a non-full parameter construction method, you can write it separately.
   @NoArgsConstructor
   @AllArgsConstructor
   @Data
   public class Staff{<!-- -->
       private int staffId;
       private String staffName;
       private String staffHobby;
   }
   

Usage of MybatisPlus

Implementation steps:

1. pom.xml import coordinates

   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus</artifactId>
       <version>3.1.1</version>
   </dependency>
   

   Note: After MP coordinates are added, mybatis coordinates are removed

2. Write annotations to configure the mapping between entity classes and relational tables (truncate to clear the tables and primary keys)

   @TableName(value = “Related table name”)=》modification in class
   @TableField(value = “Associated field name”)》Modify in attributes
   exist = “Ignore field”
   @TableId(type=”Specify primary key generation strategy, default snowflake algorithm”)===========》Modify in attributes
   AUTO(0),
   NONE(1),
   INPUT(2),
   ASSIGN_ID(3),
   ASSIGN_UUID(4);

   @NoArgsConstructor
   @AllArgsConstructor
   @Data
   @TableName(value = "staff")
   public class Staff {<!-- -->
       @TableId(value = "staff_id",type = IdType.AUTO)
       private int staffId;
       @TableField(value = "staff_name")
       private String staffName;
       @TableField(value = "staff_hobby")
       private String staffHobby;
   }
   

3. use:

   BaseMapper============》Public data access layer
   IService/ServiceImp==》Public business layer

   public interface StaffMapper extends BaseMapper<Staff> {<!-- -->
   
   }
   

4. Test code uses MyBatisSqlSessionFactoryBuilder

   @Before
   public void BeforeMethod(){<!-- -->
       try {<!-- -->
           stream = Resources.getResourceAsStream("mybatis-config.xml");
           SqlSessionFactory build = new MybatisSqlSessionFactoryBuilder().build(stream);
           sqlSession = build.openSession(true);
           sMapper = sqlSession.getMapper(StaffMapper.class);
       } catch (IOException e) {<!-- -->
           e.printStackTrace();
       }
   }
   

   Note: You need to use MyBatisSqlSessionFactoryBuilder when using new

5. Add, delete, modify and query in BaseMapper

   public class Test02 {<!-- -->
       InputStream stream =null;
       SqlSession sqlSession =null;
       StaffMapper sMapper = null;
   
       @Before
       public void BeforeMethod(){<!-- -->
           try {<!-- -->
               stream = Resources.getResourceAsStream("mybatis-config.xml");
               SqlSessionFactory build = new MybatisSqlSessionFactoryBuilder().build(stream);
               sqlSession = build.openSession(true);
               sMapper = sqlSession.getMapper(StaffMapper.class);
           } catch (IOException e) {<!-- -->
               e.printStackTrace();
           }
   
       }
       
       @After
       public void AfterMethod(){<!-- -->
           try {<!-- -->
               sqlSession.close();
               stream.close();
           } catch (IOException e) {<!-- -->
               e.printStackTrace();
           }
       }
   
       //Add
       @Test
       public void show1(){<!-- -->
           Staff staff = new Staff("Zhang San", "Look at the beauty");
           int i = sMapper.insert(staff);
           System.out.println("Primary key backfill" + staff.getStaffId());
           System.out.println("Add number of lines" + i);
       }
   
       //Modify by ID
       @Test
       public void show2(){<!-- -->
           Staff staff = new Staff();
           staff.setStaffHobby("Eat, eat, drink");
           staff.setStaffId(5);
           int i = sMapper.updateById(staff);
           System.out.println("Number of lines affected:" + i);
       }
   
       // condition modification
       @Test
       public void show3(){<!-- -->
           // change the data
           Staff staff = new Staff();
           staff.setStaffHobby("Smoking, drinking and perming");
           QueryWrapper<Staff> queryWrapper = new QueryWrapper<>();
           queryWrapper.eq("staff_name","Zhang San");
           int row = sMapper.update(staff, queryWrapper);
           System.out.println("Number of rows affected:" + row);
       }
   
       // ID query
       @Test
       public void show4(){<!-- -->
           Staff staff = sMapper.selectById(5);
           System.out.println(staff);
       }
   
       // Equivalent to in conditional search
       @Test
       public void show5(){<!-- -->
           List<Staff> sList = sMapper.selectBatchIds(Arrays.asList(1, 3, 5));
           sList.forEach(System.out::println);
       }
   
       // count function
       @Test
       public void show6(){<!-- -->
           Integer count = sMapper.selectCount(null);
           System.out.println("Total number of data" + count);
       }
   
       // conditional search or
       @Test
       public void show7(){<!-- -->
           QueryWrapper<Staff> queryWrapper = new QueryWrapper<>();
           queryWrapper.eq("staff_name","Hui Chenyi").or().eq("staff_hobby","Look at the beauty");
           //The following conditions are equivalent to and
           // queryWrapper.eq("staff_name","Hui Chenyi");
           // queryWrapper.eq("staff_hobby","Smoking, drinking and perming");
           List<Staff> slist = sMapper.selectList(queryWrapper);
           slist.forEach(System.out::println);
       }
   
       // paging
       @Test
       public void show8(){<!-- -->
           //1. Define paging rules
           Page<Staff> staffPage = new Page<>();
           staffPage.setSize(3); //Number of records per page
           staffPage.setCurrent(2); //Current page number
   
           IPage<Staff> iPage = sMapper.selectPage(staffPage, null);
           List<Staff> slist = iPage.getRecords();
           slist.forEach(System.out::println);
           System.out.println("Total number of records" + iPage.getTotal());
           System.out.println("Total number of pages" + iPage.getPages());
       }
   
       // delete
       @Test
       public void show9(){<!-- -->
           int row = sMapper.deleteById(6);
           System.out.println("Delete number of rows" + row);
       }
   }
   

   Note:

   1. MP automatically backfills the primary key. After adding it, you can get the primary key by calling the object method.

   2. ConditionQueryWrapper

      QueryWrapper<Staff> queryWrapper = new QueryWrapper<>();
      

      There are many methods in QueryWrapper, such as lt, gt, eq, in, etc.

   3. Use of MP paging:

      Need to be configured in the mybtais configuration file before use

      <!--Plug-in-->
      <plugins>
          <!--mp paging plug-in-->
          <plugin interceptor="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor"></plugin>
      </plugins>
      

      • page.setCurrent(2);The current page number starts from 1
      • Pagination requires configuring plugins
      • MP coordinate version 3.1.1 cannot be used with a higher version

MP condition query

@Test
public void show1(){<!-- -->
    //Conditional query
    LambdaQueryWrapper<Staff> la = new LambdaQueryWrapper<>();
    la.gt(Staff::getStaffAge,10);
    List<Staff> slist = sMapper.selectList(la);
    slist.forEach(System.out::println);
}

LambdaQueryWrapper is also an object of splicing conditions, and differs from QueryWrapper in the form of parameters

MP fuzzy dynamic query

// Fuzzy dynamic query
@Test
public void show2(){<!-- -->
    //Data sent by the front end
    Integer num1 = null;
    Integer num2 = 30;
    // Query conditions
    LambdaQueryWrapper<Staff> la = new LambdaQueryWrapper<>();
    if(num1 != null){<!-- -->
        la.gt(Staff::getStaffAge,num1);
    }
    if(num2 !=null){<!-- -->
        la.lt(Staff::getStaffAge,num2);
    }

    List<Staff> slist = sMapper.selectList(la);
    slist.forEach(System.out::println);
}


//Writing method two:
@Test
public void show3(){<!-- -->
    Integer num1 = null;
    Integer num2 = 30;

    LambdaQueryWrapper<Staff> la = new LambdaQueryWrapper<>();
    la.gt(num1!=null,Staff::getStaffAge,num1);
    la.lt(num2 !=null, Staff::getStaffAge,num2);
    List<Staff> slist = sMapper.selectList(la);
    slist.forEach(System.out::println);
}

LambdaQueryWrapper splices the condition method overload. In the second writing method, LambdaQueryWrapper passes three parameters. The first parameter is the condition. If it is true, it will be executed later.

MP field query

@Test
public void show4(){<!-- -->
    LambdaQueryWrapper<Staff> la = new LambdaQueryWrapper<Staff>();
    la.select(Staff::getStaffName, Staff::getStaffHobby);
    List<Staff> slist = sMapper.selectList(la);
    slist.forEach(System.out::println);
}

The select method of LambdaQueryWrapper writes the field you want to find. The written field has data, and the remaining attributes are null or 0.

MP special query

@Test
public void show5(){
    QueryWrapper<Staff> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("count(*) as num1, max(staff_age) as num2, min(staff_age) as num3");
    List> slist = sMapper.selectMaps(queryWrapper);
    slist.forEach(System.out::println);
}

Select method of LambdaQueryWrapper

1. count(*), number of statistical data
2. max(staff_age), the maximum statistical age
3. min(staff_age), minimum statistical age

The query results are stored in the Map collection in the form of key-value pairs. The key is the alias after “as”, and the value is the query result. The above query results are as follows:

{num1=4, num3=10, num2=30}

MP group query

@Test
public void show6(){
    QueryWrapper<Staff> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("count(*) as num,staff_age");
    queryWrapper.groupBy("staff_age");
    List> slist = sMapper.selectMaps(queryWrapper);
    System.out.println(slist);
}

The groupBy method of LambdaQueryWrapper first writes the conditions through the select method, and then determines the grouping conditions through the groupBy method. The results are as follows

[
     {num=1, staff_age=10},
     {num=2, staff_age=20},
     {num=1, staff_age=30}
 ]

• num is the result of count(*), staff_age is the grouping field, num and staff_age are two key-value pairs, stored in Map

• There are three groups in total, that is, three Maps. The three Maps are stored in List, so there is another layer of List wrapped around the Map.

MP tombstone

Physical deletion: Business data is discarded from the database and a delete operation is performed.

Logical deletion: Set the availability status field for the data. When deleting, set the status field to the unavailable status. The data is retained in the database and an update operation is performed.

Implementation steps:

1. Modify the database table and add the deleted column. For example, 0 represents normal and 1 represents deleted. You can set the default value to 0Normal.

2. Add attributes and annotations to entity classes

   @TableLogic(value="0",delval="1")
    private Integer deleted;
   //value is the value of normal data, delval is the value of deleted data
   

   Tombstone

   @Test
   public void show7() {<!-- -->
       mapper.deleteById(6);
   }
   

   Note:

   1. The tombstoned data will not be displayed in the full search in MP, but you will see such data in Mybatis. To implement the tombstone full search in Mybtatis, you need to add the condition where deleted = 0, so that the tombstoned data will not be displayed.