preface

Mybatis is believed to be familiar. At present, most Internet companies use MyBatis as a persistent layer framework, without any other, because you can directly write SQL statements in XML files to operate the database, flexible. But when we use it, we will also find that there are a lot of add, delete, change and check SQL is the basic operation of each table, if each table to write a set of add, delete, change and check SQL is obviously very time-consuming.

As a result, there is mybatis- Plus framework. As stated on the website, Mybatis – Plus is designed to simplify development.

Mybatis – Plus has the following features:

Just make enhancements without making changes, and introduce it without affecting existing projects, as smooth as silk.
Simple configuration allows you to perform CRUD operations on a single table quickly, saving a lot of time.
Code generation, physical paging, performance analysis, and more.

I. Integration of Mybatis – Plus

SpringBoot2.5.2 is used for the demonstration. First import dependencies:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<! -- Introducing mybatis -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.0</version>
</dependency>
Copy the code

Then configure the database information in the application.properties file:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/user? createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root123456
Mapper. XML file path address
mybatis-plus.mapper-locations=classpath:mapper/*Mapper.xml
Copy the code

Add a scan annotation to the startup class:

@SpringBootApplication
@MapperScan(basePackages = "com.yehongzhi.mydemo.mapper")
public class MydemoApplication {
    public static void main(String[] args) { SpringApplication.run(MydemoApplication.class, args); }}Copy the code

And we’re done, but we’re going to build a table to test it.

CREATE TABLE `user` (
  `id` char(36) NOT NULL DEFAULT ' ' COMMENT 'ID',
  `name` varchar(255) DEFAULT ' ' COMMENT 'name',
  `age` int(3) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Initialize four pieces of data
INSERT INTO `user`. `user` (`id`, `name`, `age`) VALUES ('1345fc0985b111eba0e488d7f66fdab8'.'view Chen'.'20');
INSERT INTO `user`. `user` (`id`, `name`, `age`) VALUES ('d47561e885b011eba0e488d7f66fdab8'.'Yao Daqiu'.'30');
INSERT INTO `user`. `user` (`id`, `name`, `age`) VALUES ('ef2741fe87f011eba0e488d7f66fdab8'.'Stephen Chow'.'60');
INSERT INTO `user`. `user` (`id`, `name`, `age`) VALUES ('ff784f6b85b011eba0e488d7f66fdab8'.'Li Li Sheng'.'33');
Copy the code

After creating the table, create a User entity class corresponding to:

/ / the name of the table
@TableName("user")
public class User {
	/ / the primary key
    @TableId(type = IdType.UUID)
    private String id;
	/ / name
    private String name;
	/ / age
    private Integer age;
    // Getter and setter methods
}
Copy the code

Next, create the UserMapper interface class, and inherit from BaseMapper:

@Repository
public interface UserMapper extends BaseMapper<User> {}Copy the code

Create a usermapper. XML corresponding to UserMapper:


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yehongzhi.mydemo.mapper.UserMapper">

</mapper>
Copy the code

Select * from UserService;

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserMapper userMapper;

    @Override
    public List<User> getList(a) {
        return userMapper.selectList(null); }}Copy the code

Starting the project, the tests are fine:

Second, CRUD operation

After integration, according to the official description of Mybatis – Plus, there is a simple single table CRUD operation function.

All the CRUD operations for a single table are stored in BaseMapper, so when we inherit the BaseMapper class, we get the enhanced features of Mybatis – Plus, including CRUD operations for a single table.

1, insert operation

BaseMapper directly provides an insert() method that passes an entity class.

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserMapper userMapper;
    
    @Override
    public int insertOne(User user) {
        returnuserMapper.insert(user); }}Copy the code

And then on the Controller call, because the User annotation @tableID is attached to the User class, it automatically generates the ID.

@TableName("user")
public class User {
    @TableId(type = IdType.UUID)
    private String id;
    / / to omit...
}
Copy the code

Controller layer code:

@RequestMapping("/insert")
public String insertUser(@RequestParam(name = "name") String name,
                         @RequestParam(name = "age") Integer age) {
    User user = new User();
    user.setName(name);
    user.setAge(age);
    // The @tableId annotation is used in the entity class, and the ID is automatically generated
    int i = userService.insertOne(user);
    return i == 1 ? "success" : "fail";
}
Copy the code

2. Update operation

BaseMapper directly provides an updateById() method that passes an entity class.

@Override
public int updateOne(User user) {
    return userMapper.updateById(user);
}
Copy the code

Controller layer code:

@RequestMapping("/update")
public String updateUser(@RequestParam(name = "id") String id,
                         @RequestParam(name = "name",required = false) String name,
                         @RequestParam(name = "age",required = false) Integer age) {
    User user = new User();
    user.setId(id);
    user.setName(name);
    user.setAge(age);
    int i = userService.updateOne(user);
    return i == 1 ? "success" : "fail";
}
Copy the code

3. Delete

BaseMapper directly provides a deleteById() method that passes primary key values.

@Override
public int deleteOne(String id) {
    return userMapper.deleteById(id);
}
Copy the code

Controller layer code:

@RequestMapping("/delete")
public String deleteUser(@RequestParam(name = "id") String id) {
    int i = userService.deleteOne(id);
    return i == 1 ? "success" : "fail";
}
Copy the code

Delete primary key (); delete primary key ();

@Override
public int deleteBatch(List<String> ids){
    return userMapper.deleteBatchIds(ids);
}
Copy the code

Controller layer code:

@RequestMapping("/deleteBatch")
public String deleteBatchUser(@RequestParam(name = "ids") String ids) {
    List<String> list = Arrays.asList(ids.split(","));
    int i = userService.deleteBatch(list);
    return i == list.size() ? "success" : "fail";
}
Copy the code

Conditional constructor (Wrapper)

Conditional constructors appear in various Mybatis- Plus methods, such as:

Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);

int delete(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
Copy the code

Mybatis-Plus is a powerful tool that can be used to perform a lookup after a WHERE statement. Wrapper is an abstract class and there are many subclasses below, so let’s look at the class diagram to make it look familiar.

There are four commonly used subclass implementations:

QueryWrapper
UpdateWrapper
LambdaQueryWrapper
LambdaUpdateWrapper

QueryWrapper

It is used to generate where conditions. For example, we use name to query user:

public List<User> queryUserByName(String name) {
    SELECT * FROM user WHERE name =?
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //eq() denotes equality
    queryWrapper.eq("name", name);
    return userMapper.selectList(queryWrapper);
}
Copy the code

Let’s look at log printing:

==> Preparing: SELECT id,name,age FROM user WHERE name = ? = = > Parameters: Yao Daqiu (String) < = = the Columns: id, name, age < = = Row: d47561e885b011eba0e488d7f66fdab8 Yao Daqiu,30
<==      Total: 1
Copy the code

If we want to query like, we can write:

public List<User> queryUserLikeName(String name) {
    SELECT * FROM user WHERE name like %#{name}%
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name",name);
    return userMapper.selectList(queryWrapper);
}
Copy the code

If you want to query an age greater than 30, you can do this:

public List<User> queryUserGtByAge(int age) {
    SELECT * FROM user WHERE age >?
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.gt("age", age);
    // small so lt()
    // greater than ge()
    // less than le()
    //范围的话，则使用between()
    return userMapper.selectList(queryWrapper);
}
Copy the code

If you want to query a field that is not empty, you can do this:

public List<User> queryUserByNameNotNull(a) {
    SELECT * FROM user WHERE name IS NOT NULL
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNotNull("name");
    // if a field isNull, use isNull()
    return userMapper.selectList(queryWrapper);
}
Copy the code

If you use IN, you can do this:

public List<User> queryUserByIds(List<String> ids) {
    SELECT * FROM user WHERE name IN ('id1','id2');
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.in("id", ids);
    // Instead, the notIn() method is provided
    return userMapper.selectList(queryWrapper);
}
Copy the code

If you need to sort, you can write:

public List<User> queryUserOrderByAge(a) {
    SELECT * FROM user ORDER BY age ASC
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.orderByAsc("age");
    // Instead, use the orderByDesc() method if the order is descending
    return userMapper.selectList(queryWrapper);
}
Copy the code

If you need a subquery, you can write:

public List<User> queryUserInSql(a) {
    SELECT * FROM user WHERE id IN (SELECT id FROM user WHERE age > 30)
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.inSql("id"."select id from user where age > 30");
    return userMapper.selectList(queryWrapper);
}
Copy the code

Most of the methods are derived from the AbstractWrapper class. In addition, there are many other functions, which are not introduced here. If you are interested, you can explore the conditional constructor on the official website.

UpdateWrapper

UpdateWrapper is a subclass of the AbstractWrapper abstract class, so there are all the above methods for setting conditions. The difference is that UpdateWrapper has set() and setSql() to set the updated values. Here’s an example:

public int updateUserNameById(String id, String name) {
    // equivalent to: UPDATE user SET name =? where id = ?
    UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    userUpdateWrapper.eq("id", id);
    Set name = #{name} set name = #{name}
    userUpdateWrapper.set("name", name);
    return userMapper.update(new User(), userUpdateWrapper);
}
Copy the code

SetSql () is the part of the SQL statement that sets the concatenation after the SET keyword, for example:

public int updateUserBySql(String id, String name) {
    // equivalent to: UPDATE user SET name =? where id = ?
    UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    userUpdateWrapper.setSql("name = '" + name + "'");
    userUpdateWrapper.eq("id", id);
    return userMapper.update(new User(), userUpdateWrapper);
}
Copy the code

SetSql () is pure SQL statement concatenation. We can see that name=’ big D’ after SET, instead of placeholder, risks SQL injection.

==>  Preparing: UPDATE user SET name = 'the big D' WHERE id = ?
==> Parameters: d47561e885b011eba0e488d7f66fdab8(String)
<==    Updates: 1
Copy the code

LambdaQueryWrapper

The advantage of using LambdaQueryWrapper is to eliminate hard coding, such as QueryWrapper when querying name=? , you need to write:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// Use the string "name", which is hard coded
queryWrapper.eq("name", name);
Copy the code

Instead of LambdaQueryWrapper, you could write:

public List<User> lambdaQueryUserByName(String name) {
    SELECT id,name,age FROM user WHERE name =?
    LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda();
    lambdaQueryWrapper.eq(User::getName, name);
    return userMapper.selectList(lambdaQueryWrapper);
}
Copy the code

For another example, using fuzzy query, you can write:

public List<User> lambdaQueryUserLikeName(String name) {
    LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda();
    lambdaQueryWrapper.like(User::getName, name);
    return userMapper.selectList(lambdaQueryWrapper);
}
Copy the code

LambdaUpdateWrapper

Similarly, LambdaUpdateWrapper can also eliminate hard coding:

public int lambdaUpdateUserNameById(String id, String name) {
    // equivalent to: UPDATE user SET name=? WHERE id = ?
    LambdaUpdateWrapper<User> lambdaUpdateWrapper = new UpdateWrapper<User>().lambda();
    lambdaUpdateWrapper.set(User::getName, name);
    lambdaUpdateWrapper.eq(User::getId, id);
    return userMapper.update(new User(), lambdaUpdateWrapper);
}
Copy the code

Paging query

Mybatis- Plus provides a paging plug-in to support paging queries in just a few steps.

First add a configuration class:

@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor(a) {
        return newPaginationInterceptor(); }}Copy the code

Second, define the paging query method in the UserMapper class:

@Repository
public interface UserMapper extends BaseMapper<User> {
	// paging query method
    IPage<User> selectPageByName(Page<User> page, @Param("name") String name);
}
Copy the code

The corresponding usermapper.xml method:

<select id="selectPageByName" parameterType="java.lang.String" resultType="com.yehongzhi.mydemo.model.User">
    select * from `user` where name = #{name}
</select>
Copy the code

Step 3: Call userService:

public IPage<User> selectPageByName(long pageNo, long pageSize, String name) {
    Page<User> page = new Page<>();
    // Set the current page number
    page.setCurrent(pageNo);
    // Set the number of pages to be displayed
    page.setSize(pageSize);
    return userMapper.selectPageByName(page, name);
}
Copy the code

Finally, write a Controller interface test:

@RequestMapping("/queryPage/ByName")
public IPage<User> selectPageByName(@RequestParam("pageNo") long pageNo,
                                    @RequestParam("pageSize") long pageSize,
                                    @RequestParam("name") String name) {
    return userService.selectPageByName(pageNo, pageSize, name);
}
Copy the code

View console logs:

==>  Preparing: SELECT COUNT(1) FROM `user` WHERE name =? <== Columns: COUNT(1)
<==        Row: 1==> Preparing: select * from `user` where name = ? LIMIT ? ,? ==> Parameters: Du (String),0(Long), 10(Long) < = = the Columns: id, name, age < = = Row: d47561e885b011eba0e488d7f66fdab8, johnnie to,30
<==      Total: 1
Copy the code

Paging query successful!

Custom primary key generator

Sometimes in the actual development, it may encounter that the primary key generation strategy provided by Mybatis- Plus cannot meet the requirements, so we need to customize the primary key ID generation strategy. How to set it?

We define a primary key generator as follows:

@Component
public class SnowflakeKeyGenerator implements IdentifierGenerator {
	// create a snowflake ID class
    @Resource
    private SnowflakeIdWorker snowflakeIdWorker;

    @Override
    public Number nextId(Object entity) {
        // Generate a snowflake ID using the snowflake ID generator
        long nextId = snowflakeIdWorker.nextId();
        System.out.println(String.format("Using custom ID generator, generate snowflake ID:%s", nextId));
        returnnextId; }}Copy the code

We then add an annotation attribute to the entity class that needs to use the custom ID generator:

@TableName("user")
public class User {
	// Set the property to idtype.assign_id
    @TableId(type = IdType.ASSIGN_ID)
    private String id;
	
    / / to omit...
}
Copy the code

After testing, we can see that the console prints logs:

conclusion

In addition to the features described above, Mybatis- Plus has many other features, such as: code generators, extensions and more. I won’t go through all of them here, but the CRUDS, conditional constructors, paging queries, and custom primary key policies described above are basically enough for daily development.

Of course, if you think you can not use enough, but also want to understand the framework of the source code implementation, that is no problem. In the next article, I’ll talk about source code analysis of frameworks, so stay tuned,

Thank you very much for reading and I hope this article has been helpful and enlightening to you.

The article continues to update, wechat search “Java technology enthusiasts”, the first time after paying attention to the technical article push, article classification is collected on github: github.com/yehongzhi, you can always find what you are interested in

Please give me a thumbs-up if you think it is useful. Your thumbs-up is the biggest motivation for my creation

I’m a programmer who tries to be remembered. See you next time!!

Ability is limited, if there is any mistake or improper place, please criticize and correct, study together!

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mybatis-Plus, it smells good!

preface

I. Integration of Mybatis – Plus

Second, CRUD operation

1, insert operation

2. Update operation

3. Delete

Conditional constructor (Wrapper)

QueryWrapper

UpdateWrapper

LambdaQueryWrapper

LambdaUpdateWrapper

Paging query

Custom primary key generator

conclusion

Mybatis-Plus, it smells good!

preface

I. Integration of Mybatis – Plus

Second, CRUD operation

1, insert operation

2. Update operation

3. Delete

Conditional constructor (Wrapper)

QueryWrapper

UpdateWrapper

LambdaQueryWrapper

LambdaUpdateWrapper

Paging query

Custom primary key generator

conclusion

Related Posts

Postures for specifying the initialization order of beans in the SpringBoot series

Python 工匠： 异常处理的三个好习惯

Dubbo service invocation

Python 工匠：异常处理的三个好习惯