Contents of this article
preface
Since the launch of Mybatis plus, more and more companies have chosen the Mybatis plus framework to replace the persistence layer framework Mybatis in their own projects. Because Mybatis plus not only has the flexibility of handwritten complex sql statements of Mybatis, but also has the general framework method that Spring Data Jpa automatically provides single table CRUD operation. You only need to customize a Mapper and inherit BaseMapper, which saves a lot of workload for developers to use the persistence layer framework. At the same time, Mybatis plus also provides many general API methods such as chain query and paging query, which can be directly used by developers. The purpose of this paper is to guide novices how to integrate Mybatis plus persistence layer framework in their spring boot project to complete the function of data addition, deletion, modification and query.
1 Introduction to mybatis plus
MyBatis plus (MP for short) is an enhancement tool for MyBatis. On the basis of MyBatis, it only makes enhancements and does not change. It is born to simplify development and improve efficiency.
Vision: our vision is to become the best partner of MyBatis, just like 1P and 2P in soul duel. With the combination of friends and friends, the efficiency will be doubled.
1.1 characteristics
- No invasion: it is only enhanced without change, and its introduction will not affect the existing project, which is as smooth as silk
- Low loss: the basic CURD will be injected automatically upon startup, with basically no loss of performance and direct object-oriented operation
- Powerful crud operation: built in general Mapper and general Service, most CRUD operations of a single table can be realized only through a small number of configurations, and there is a powerful condition constructor to meet various use requirements
- Support Lambda formal call: it is convenient to write various query conditions through Lambda expression, and there is no need to worry about wrong fields
- Support automatic generation of primary key: support up to 4 primary key strategies (including distributed unique ID generator - Sequence), which can be configured freely to perfectly solve the primary key problem
- Support ActiveRecord mode: support ActiveRecord formal calls. Entity classes only need to inherit Model classes to perform powerful CRUD operations
- Support custom global general operations: support global general method injection (Write once, use anywhere)
- Built in code generator: code or Maven plug-in can be used to quickly generate Mapper, Model, Service and Controller layer code, support template engine, and more custom configurations for you to use
- Built in paging plug-in: Based on MyBatis physical paging, developers do not need to care about specific operations. After configuring the plug-in, writing paging is equivalent to ordinary List query
- The paging plug-in supports multiple databases: MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer and other databases
- Built in performance analysis plug-in: it can output SQL statements and their execution time. It is recommended to enable this function during development and testing to quickly find out slow queries
- Built in global interception plug-in: it provides intelligent analysis and blocking of full table delete and update operations, and can also customize interception rules to prevent misoperation
1.2 support database
Any database that can use mybatis for CRUD and supports standard SQL. The specific support is as follows:
- mysql，oracle，db2，h2，hsql，sqlite，postgresql，sqlserver，Phoenix，Gauss ，clickhouse，Sybase，OceanBase，Firebird，cubrid，goldilocks，csiidb
- Dameng database, virtual Valley database, NPC Jincang database, NTU general (Huaku) database, NTU general database, Shentong database, Hangao database
1.3 frame structure
2 quick start
2.1 create a new spring boot project
Open the IDEA, click file - > New - > Project - > and select Spring Initializr. To save too long time for the next project to download dependent packages, first click the setting icon button on the right in the first red box in the figure below to change the ServerUrl parameter value to Ali's address: http://start.aliyun.com (the default address is https://spring.io/ )
Then fill in the project Name and the storage Location of the project on the local computer (the contents in the input boxes on the right of Name and Location in the figure below), and Name the GroupId and artifactId of the project (the contents in the input boxes on the right of Group and Artifact in the figure below). Select 8 for Java version
Then click the Next button to enter the dependency selection control interface. We select the dependencies required by the project. Here, the author selects project dependency modules such as Lombok, Spring Web, Mybatis Plus Framework, MySQL Driver, Apache common Lang and Fastjson
Then click Finish to produce the project skeleton. The program helps us select Spring Boot version 2.3.7.release, and mybatis plus automatically selects version 3.4.2. However, in the process of practice, the author found that the project startup reported a series of errors that the built-in springframeword-XX-5.2.12.RELEASE.jar in Spring Boot 2.3.7.RELEASE could not be opened, so I changed the Spring Boot version to 2.2.7.RELEASE, and the built-in springframework version used 5.2.6.RELEASE, This version of the jar package does not have an error that cannot be opened when the project is started. Then, when I added the @ MapperScan annotation to the startup class of the Spring Boot project, I couldn't find this annotation. I estimated that version 3.4.2 of mybatis plus was incompatible with version 2.2.7.RELEASE of Spring Boot, so I changed the version of mybatis plus to version 3.1.0. After changing, I found that @ MapperScan annotation could be found.
Use spring-boot-2.3.7.RELEASE to start the error log:
java: read D:\mavenRepository\.m2\repository\org\springframework\spring-jdbc\5.2.12.RELEASE\spring-jdbc-5.2.12.RELEASE.jar Error occurred; error in opening zip file java: read D:\mavenRepository\.m2\repository\org\springframework\spring-tx\5.2.12.RELEASE\spring-tx-5.2.12.RELEASE.jar Error occurred; error in opening zip file
After the project was created, the author found that there were many problems of repeated references to jar packages through Maven life cycle and dependency management on the right side of the IDEA development tool, so the label was used to remove the repeated dependencies, but attention should be paid to putting the repeatedly referenced dependencies in some modules under the label separately, In order to avoid the lack of dependent jar packages in the project and the failure of project startup. In fact, if there are repeated references to dependent jar packages in the project, but there is no jar package conflict, the project startup failure error will not be caused, but the dependency management of the project will become bloated.
The following pom.xml file is the content of the pom.xml file after I replaced the spring boot version 2.2.7.release, excluded most of the repeated dependencies, and the project can be started successfully
2.2 complete project startup configuration
Add the configuration items of spring, tomcat server and mybatis pus in the application.properties environment configuration file under the src/main/resources directory of the project. The spring boot project will load these parameters when initializing related beans according to the automatic configuration class during startup
application.propertis
# apply name spring.application.name=mybatis-plus # Application service WEB access port server.port=8080 # Application context path server.servlet.context-path=/mybatis-plus # Activate dev environment spring.profiles.active=dev # Set the time zone to prevent json from serializing the object date parameter 8 hours earlier than the true date spring.jackson.time-zone=GMT+8 # Mybatis plus configuration mybatis-plus.mapper-locations=classpath:com/example/mybatisplus/mapper/*Mapper.xml mybatis-plus.configuration.map-underscore-to-camel-case=true # This configuration allows you to view the detailed log of sql execution mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
In addition, create a new application-dev.properties configuration file, which corresponds to the configuration parameters of the development environment. In this property file, we configure the data source parameters of the development environment. In the future, when there is a test environment and production environment, we can continue to add application-test.properties files and application-prod.properties files to configure the environment configuration parameters corresponding to the test environment and production environment
application-dev.properties
# database driver spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # datasource name spring.datasource.name=hikariDataSource # connection URL spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai # login user spring.datasource.username=root # login password spring.datasource.password=<Your database root User connection password>
Note that two parameters, characterEncoding=UTF-8 and serverTimezone=Asia/Shanghai, must be added to the spring.datasource.url configuration item corresponding to the database connection URL above. The former is to prevent Chinese garbled code in the database, and the latter is to ensure that the time of the date field saved in the database is accurate. The default time will be 8 hours earlier than our Beijing time zone.
Add the @ MapperScan annotation to the startup class of the project, and fill in the package name of the Mapper class of the database provider in the basePackages attribute
package com.example.mybatisplus;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages = {"com.example.mybatisplus.mapper"})
publicclass MybatisPlusApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisPlusApplication.class, args);
}
}
package com.example.mybatisplus.configuration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
publicclass MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
paginationInterceptor.setOverflow(true);
paginationInterceptor.setDialectClazz("com.baomidou.mybatisplus.extension.plugins.pagination.dialects.MySqlDialect");
paginationInterceptor.setSqlParser(new JsqlParserCountOptimize());
return paginationInterceptor;
}
}
The configuration here is slightly different from that of the mybatis plus official website. The official website uses version 3.4.2, while I use version 3.1.0. It mainly introduces a PaginationInterceptor class, PaginationInterceptor, and then sets its database dialect class and sqlParser attribute. If the Mysql database is not used, readers can use the dialect class corresponding to their own database under the com.baidu.mybatisplus.extension.plugins.pagination.conversations package
2.3 analysis of mybatis plus automatic configuration class source code
The purpose of interpreting the source code of mybatis plus automatic configuration class is to help us better understand the working principle of mybatis plus and guide us how to correctly configure the attribute parameters of mybatis plus
The automatic configuration class of mybatis plus is MybatisPlusAutoConfiguration class, which is located in the com.baidu.mybatisplus.autoconfigure package in mybatis plus-boot-starter-3.1.0.jar package
Entering the MybatisPlusAutoConfiguration class, we can see that the methods sqlsessionfactory (datasource, datasource) and sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) marked by the following @ bean annotation help us configure the connected beans that Spring must use when integrating Mybatis: SqlSessionTemplate and SqlSessionTemplate. Both beans will be automatically initialized and injected into the Spring IOC container when the developer does not customize the beans of these two classes
We can see that @ EnableConfigurationProperties({MybatisPlusProperties.class}) and @ autoconfiguraeafter ({DataSourceAutoConfiguration. Class}) annotations are added to the MybatisPlusAutoConfiguration class, The former means that when the project has the property of · MybatisPlusProperties · configuration class, initialize the · MybatisPlusAutoConfiguration · configuration class and the bean s configured below and add them to the Spring IOC container; The latter indicates that the initialization of the data source automatic configuration class DataSourceAutoConfiguration is completed.
Entering the MybatisPlusProperties class, we found that it requires all property parameters related to mybatis plus to be prefixed with mybatis plus
This class parses the key value pair starting with mybatis plus in the environment configuration file and populates its attribute value during initialization
More source code readers can open the project through IDEA and view it.
3. Use mybatis plus to complete the database CRUD function
In order to reduce the length of the article, I only demonstrate the CRUD operation of a single table, which mainly involves the addition, modification, query and paging query of single and multiple data. The continuous table query of querying multiple tables at the same time using Mybatis plus is consistent with the implementation of user-defined mapper.xml in Mybatis. There are many cases on the Internet, so I won't extend the demonstration in this article
3.1 table building
Here I create a new stock in the mysql test database_ The info table represents the commodity information table, which is also needed to facilitate the learning of distributed e-commerce projects. The sql script for creating tables is as follows:
DROPTABLEIFEXISTS`stock_info`;
CREATETABLE`stock_info` (
`id`bigint(20) NOTNULL AUTO_INCREMENT COMMENT'Primary key',
`good_code`varchar(30) NOTNULLCOMMENT'Commodity code',
`good_name`varchar(100) DEFAULTNULLCOMMENT'Trade name',
`count`int(11) DEFAULT'0'COMMENT'Quantity of goods',
`created_date` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'Creation time',
`created_by`varchar(30) NOTNULLDEFAULT'system'COMMENT'Creator',
`last_updated_date` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'Last update time',
`last_updated_by`varchar(30) NOTNULLDEFAULT'system'COMMENT'Last updated by',
`unit_price`int(11) DEFAULT'0'COMMENT'Unit price',
PRIMARY KEY (`id`),
UNIQUEKEY`uk_good_code` (`good_code`)
) ENGINE=InnoDB AUTO_INCREMENT=22DEFAULTCHARSET=utf8mb4;
Readers can also use visual database client tools such as Navicat to create a new table, and then dump the table creation sql script and save it to the folder where the script is stored in the project.
Coding part
The coding of the project is carried out according to the hierarchical mode, which is divided into controller layer (XXController class under controller package), service layer (XXService class under service package) and database access layer (XXMapper class under mapper package)
3.2 create an entity class corresponding to the table
Create a new basic class BaseEntity under the com.example.mybatisplus.pojo package, which mainly contains the fields of creator, creation time, last modified by and last modified time in a table
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
@Data
publicclass BaseEntity implements Serializable {
/**
* Creator
*/
@TableField(value = "created_by", fill = FieldFill.INSERT)
private String createdBy;
/**
* Creation date (with time)
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@TableField(value = "created_date", fill = FieldFill.INSERT)
private Date createdDate;
/**
* Modified by user ID
*/
@TableField(value = "last_updated_by", fill = FieldFill.INSERT_UPDATE)
private String lastUpdatedBy;
/**
* Modification date (with time)
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@TableField(value = "last_updated_date", fill = FieldFill.INSERT_UPDATE)
private Date lastUpdatedDate;
}
Add the date format annotation @ JsonFormat annotation to the date field, and specify the date format in the pattern attribute
Also, create and stock under com.example.mybatisplus.pojo_ The entity class corresponding to the info table is StockInfo class and inherits the BaseEntity class above
package com.example.mybatisplus.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("stock_info")
publicclass StockInfo extends BaseEntity {
/**
* Primary key ID
*/
@TableId(type = IdType.AUTO)
private Long id;
/**
* Commodity code
*/
@TableField(value = "good_code")
private String goodCode;
/**
* Trade name
*/
@TableField(value = "good_name")
private String goodName;
/**
* Inventory quantity
*/
@TableField(value = "count")
private Integer count;
/**
* Unit price of goods, unit: minute
*/
@TableField(value = "unit_price")
private Long unitPrice;
}
For the usage and detailed introduction of @ TableName, @ TableId and @ TableField annotations, readers can read the official documents Annotation section Master, I won't interpret it here. The source code of the project has been submitted to the gitee personal code warehouse. Other interface reference general classes such as ResponseVo, form query parameter encapsulation class, StockParam class and BaseParam class can be viewed through the project gitee address given at the end of the text.
3.3 database access layer code
Create a new StockMapper interface, inherit the BaseMapper interface class, and customize two methods
package com.example.mybatisplus.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mybatisplus.pojo.StockInfo;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
@Repository
publicinterface StockMapper extends BaseMapper<StockInfo> {
/**
* Find inventory quantity by commodity code
* @param goodCode
* @return
*/
Integer findCountByGoodCode(String goodCode);
/**
* Update item inventory quantity
* @param id
* @param count
* @return
*/
Integer updateStockById(@Param("id") Long id, @Param("count") Integer count);
}
Hold down Ctrl and click BaseMapper to enter the BaseMapper class. You can see that this class defines common CRUD methods for single tables, saving developers time to customize CRUD methods
package com.baomidou.mybatisplus.core.mapper;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
// The generic parameter T in BaseMapper is the entity class corresponding to the table
publicinterface BaseMapper<T> {
// Add record method
int insert(T entity);
// Delete method by ID
int deleteById(Serializable id);
// Delete method by column value matching
int deleteByMap(@Param("cm") Map<String, Object> columnMap);
// Delete by query criteria
int delete(@Param("ew") Wrapper<T> wrapper);
// Batch delete based on ID set
int deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);
// Update record by ID
int updateById(@Param("et") T entity);
// Update according to query criteria
int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);
// Query a single record by ID
T selectById(Serializable id);
// Batch query by ID set
List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);
// Multiple records are returned according to the column value matching query
List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);
// Query a single record according to query criteria
T selectOne(@Param("ew") Wrapper<T> queryWrapper);
// Query the quantity that meets the criteria according to the criteria
Integer selectCount(@Param("ew") Wrapper<T> queryWrapper);
//Query multiple records according to query criteria and return entity object collection
List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);
//Query multiple records according to query criteria and return the Map set
List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);
// Query multiple records according to query criteria and return the object collection
List<Object> selectObjs(@Param("ew") Wrapper<T> queryWrapper);
// Paging query with query parameters returns paging objects with entity object sets
IPage<T> selectPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
// Paging query with query parameters returns paging objects with Map sets
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
}
In the src/main/resources directory, we create a new layer by layer to the com/example/muybatisplus/mapper folder, and then create StockMapper.xml under the mapper folder to complete the implementation of custom methods in StockMapper.java. This step is the same as the previous Mybatis handwritten Mapper.xml as a persistence layer framework
StockMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatisplus.mapper.StockMapper">
<select id="findCountByGoodCode" parameterType="java.lang.String" resultType="java.lang.Integer">
select `count` from stock_info
where good_code = #{goodCode, jdbcType=VARCHAR}
</select>
<update id="updateStockById">
update stock_info set `count` = #{count, jdbcType=INTEGER}
where id = #{id, jdbcType=BIGINT}
</update>
</mapper>
3.4 Service layer code
Create a new StockService interface class and integrate the IService interface class
package com.example.mybatisplus.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.mybatisplus.params.StockParam;
import com.example.mybatisplus.pojo.ResponseVo;
import com.example.mybatisplus.pojo.StockInfo;
import java.util.List;
publicinterface StockService extends IService<StockInfo> {
/**
* Single add save
*
* @param stockInfo
* @return
*/
ResponseVo saveOne(StockInfo stockInfo);
/**
* Batch add save
*
* @param stockInfoList
* @return
*/
ResponseVo saveBatch(List<StockInfo> stockInfoList);
/**
* Modify single
*
* @param stockInfo
* @return
*/
ResponseVo updateOne(StockInfo stockInfo);
/**
* Batch modification
* @param stockInfoList
* @return
*/
ResponseVo updateBatch(List<StockInfo> stockInfoList);
/**
* Paging query with multiple conditions
* @param stockParam
* @param pageNo
* @param pageSize
* @return
*/
ResponseVo findPageByCondition(StockParam stockParam, int pageNo, int pageSize);
/**
* Custom find item inventory
* @param goodCode
* @return
*/
ResponseVo findCountByGoodCode(String goodCode);
/**
* Custom inventory
* @param id
* @param count
* @return
*/
ResponseVo updateStockCountById(Long id, Integer count);
}
In the StockService interface class, I define some CRUD abstract methods to access the database, and the return type of the method is uniformly ResponseVo
Then create a new StockServiceImpl class, inherit the ServiceImpl class and implement the StockService interface class
package com.example.mybatisplus.service.impl;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.mybatisplus.mapper.StockMapper;
import com.example.mybatisplus.params.StockParam;
import com.example.mybatisplus.pojo.ResponseVo;
import com.example.mybatisplus.pojo.StockInfo;
import com.example.mybatisplus.service.StockService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
@Slf4j
publicclass StockServiceImpl extends ServiceImpl<StockMapper, StockInfo> implements StockService {
privatestaticfinal String DATE_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
@Override
public ResponseVo saveOne(StockInfo stockInfo) {
log.info("invoke StockServiceImpl#saveOne method start --------");
log.info("stockInfo={}", JSON.toJSON(stockInfo));
setCreatedByAndCreatedByDate(stockInfo);
setLastUpdatedByAndLastUpdatedDate(stockInfo);
boolean result = super.save(stockInfo);
ResponseVo responseVo;
if (result) {
responseVo = ResponseVo.success(result);
} else {
responseVo = ResponseVo.error("Save failed");
}
return responseVo;
}
@Override
public ResponseVo saveBatch(List<StockInfo> stockInfoList) {
log.info("invoke StockServiceImpl#saveBatch method start --------");
log.info("stockInfoList={}", JSON.toJSON(stockInfoList));
for (StockInfo stockInfo : stockInfoList) {
setCreatedByAndCreatedByDate(stockInfo);
setLastUpdatedByAndLastUpdatedDate(stockInfo);
}
boolean result = super.saveBatch(stockInfoList);
ResponseVo responseVo;
if (result) {
responseVo = ResponseVo.success(result);
} else {
responseVo = ResponseVo.error("Batch saving failed");
}
return responseVo;
}
@Override
public ResponseVo updateOne(StockInfo stockInfo) {
log.info("invoke StockServiceImpl#updateOne method start --------");
log.info("stockInfo={}", JSON.toJSON(stockInfo));
setLastUpdatedByAndLastUpdatedDate(stockInfo);
boolean result = super.updateById(stockInfo);
ResponseVo responseVo;
if (result) {
responseVo = ResponseVo.success(result);
} else {
responseVo = ResponseVo.error("Update failed");
}
return responseVo;
}
@Override
public ResponseVo updateBatch(List<StockInfo> stockInfoList) {
log.info("invoke StockServiceImpl#updateBatch method start --------");
log.info("stockInfoList={}", JSON.toJSON(stockInfoList));
for (StockInfo stockInfo : stockInfoList) {
setLastUpdatedByAndLastUpdatedDate(stockInfo);
}
boolean result = super.updateBatchById(stockInfoList);
ResponseVo responseVo;
if (result) {
responseVo = ResponseVo.success(result);
} else {
responseVo = ResponseVo.error("Batch update failed");
}
return responseVo;
}
@Override
public ResponseVo findPageByCondition(StockParam stockParam, int pageNo, int pageSize) {
log.info("invoke StockServiceImpl#findPageByCondition method start --------");
log.info("pageNo={}, pageSize={}", pageNo, pageSize);
if (pageNo <= 0) {
pageNo = 1;
}
if (pageSize <= 10) {
pageSize = 10;
}
if (pageSize > 500) {
pageSize = 500;
}
IPage<StockInfo> pageParam = new Page<>(pageNo, pageSize);
QueryWrapper<StockInfo> queryWrapper = getQueryWrapperByParam(stockParam);
IPage<StockInfo> pageData = super.page(pageParam, queryWrapper);
ResponseVo responseVo = ResponseVo.success(pageData);
return responseVo;
}
@Override
public ResponseVo findCountByGoodCode(String goodCode) {
log.info("invoke StockServiceImpl#findCountByGoodCode method start --------");
log.info("goodCode={}", goodCode);
ResponseVo responseVo;
if (StringUtils.isEmpty(goodCode)) {
responseVo = ResponseVo.error(HttpStatus.BAD_REQUEST.value(), "goodCode cannot be null");
return responseVo;
}
Integer count = this.baseMapper.findCountByGoodCode(goodCode);
if (count == 1) {
responseVo = ResponseVo.success(count);
} else {
responseVo = ResponseVo.error("Failed to update inventory");
}
return responseVo;
}
@Override
public ResponseVo updateStockCountById(Long id, Integer count) {
log.info("invoke StockServiceImpl#updateStockCountById method start --------");
ResponseVo responseVo;
if (id == null || id < 1) {
responseVo = ResponseVo.error(HttpStatus.BAD_REQUEST.value(), "invalid request param of id");
return responseVo;
}
if (count == null || count < 1) {
responseVo = ResponseVo.error(HttpStatus.BAD_REQUEST.value(), "invalid request param of count");
return responseVo;
}
Integer resultCount = this.baseMapper.updateStockById(id, count);
if (resultCount == 1) {
responseVo = ResponseVo.success(resultCount);
} else {
responseVo = ResponseVo.error("Failed to update commodity inventory");
}
return responseVo;
}
/**
* Set creator and creation time
*
* @param stockInfo
*/
private void setCreatedByAndCreatedByDate(StockInfo stockInfo) {
if (StringUtils.isEmpty(stockInfo.getCreatedBy())){
stockInfo.setCreatedBy("system");
}
if (stockInfo.getCreatedDate() == null) {
stockInfo.setCreatedDate(DateUtil.date(System.currentTimeMillis()));
}
}
/**
* Set last modified by and last modified time
*
* @param stockInfo
*/
private void setLastUpdatedByAndLastUpdatedDate(StockInfo stockInfo) {
if (StringUtils.isEmpty(stockInfo.getLastUpdatedBy())){
stockInfo.setLastUpdatedBy("system");
}
if (stockInfo.getLastUpdatedDate() == null) {
stockInfo.setLastUpdatedDate(DateUtil.date(System.currentTimeMillis()));
}
}
/**
* Processing dynamic queries
*
* @param stockParam
* @return queryWrapper
*/
private QueryWrapper<StockInfo> getQueryWrapperByParam(StockParam stockParam) {
QueryWrapper<StockInfo> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "good_code", "good_name", "unit_price", "count",
"created_date", "created_by", "last_updated_by", "last_updated_date");
// Note that using the like query for columns with unique indexes will result in no results
queryWrapper.eq(!StringUtils.isEmpty(stockParam.getGoodCode()), "good_code", stockParam.getGoodCode());
queryWrapper.likeRight(!StringUtils.isEmpty(stockParam.getGoodName()), "good_name", stockParam.getGoodName());
queryWrapper.eq(stockParam.getCount() != null, "count", stockParam.getCount());
queryWrapper.ge(stockParam.getQueryMinUnitPrice() != null,"unit_price", stockParam.getQueryMinUnitPrice());
queryWrapper.le(stockParam.getQueryMaxUnitPrice() != null,"unit_price", stockParam.getQueryMaxUnitPrice());
queryWrapper.eq(StringUtils.isNotBlank(stockParam.getCreatedBy()), "created_by", stockParam.getCreatedBy());
if (!StringUtils.isEmpty(stockParam.getQueryMinCreatedDate())) {
DateTime queryMinCreatedDate = DateUtil.parse(stockParam.getQueryMinCreatedDate(), DATE_TIME_FORMAT);
queryWrapper.ge("created_date", queryMinCreatedDate);
}
if (!StringUtils.isEmpty(stockParam.getQueryMaxCreatedDate())) {
DateTime queryMaxCreatedDate = DateUtil.parse(stockParam.getQueryMaxCreatedDate(), DATE_TIME_FORMAT);
queryWrapper.le("created_date", queryMaxCreatedDate);
}
if (!StringUtils.isEmpty(stockParam.getLastUpdatedBy())) {
queryWrapper.eq(StringUtils.isNotBlank(stockParam.getLastUpdatedBy()), "last_updated_by", stockParam.getLastUpdatedBy());
}
if (!StringUtils.isEmpty(stockParam.getQueryMinUpdateDate())) {
DateTime queryMinUpdateDate = DateUtil.parse(stockParam.getQueryMinUpdateDate(), DATE_TIME_FORMAT);
queryWrapper.ge("last_updated_date", queryMinUpdateDate);
}
if (!StringUtils.isEmpty(stockParam.getQueryMaxUpdateDate())) {
DateTime queryMaxUpdateDate = DateUtil.parse(stockParam.getQueryMaxUpdateDate(), DATE_TIME_FORMAT);
queryWrapper.le("last_updated_date", queryMaxUpdateDate);
}
queryWrapper.orderByAsc("id");
return queryWrapper;
}
}
Entering the ServiceImpl class, we can see that it implements the IService interface class and implements most of the abstract methods in BaseMapper
The first generic parameter in the ServiceImpl class is a custom Mapper class inherited from BaseMapper, and the second generic parameter is an entity class that does not correspond to a table, corresponding to the StockMapper and StockInfo classes in this demonstration project. In this way, the custom Mapper class does not need to be injected into the custom ServiceImpl, but gets the database access proxy object through this.baseMapper.
3.5 Controller layer code
This layer of coding is very simple. You can directly call the injected StockService service class to complete the operation, but you need to add some spring MVC annotations to the classes and methods
package com.example.mybatisplus.controller;
import com.example.mybatisplus.params.StockParam;
import com.example.mybatisplus.pojo.ResponseVo;
import com.example.mybatisplus.pojo.StockInfo;
import com.example.mybatisplus.service.StockService;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
@RestController
@RequestMapping("/stock")
publicclass StockController {
@Resource
private StockService stockService;
/**
* Save single
*
* @param stockInfo
* @return
*/
@PostMapping("/saveOne")
public ResponseVo saveOne(@RequestBody StockInfo stockInfo) {
return stockService.saveOne(stockInfo);
}
/**
* Batch save
* @param stockInfoList
* @return
*/
@PostMapping("/saveBatch")
public ResponseVo saveBatch(@RequestBody List<StockInfo> stockInfoList) {
return stockService.saveBatch(stockInfoList);
}
/**
* Update single
* @param stockInfo
* @return
*/
@PostMapping("/updateOne")
public ResponseVo updateOne(@RequestBody StockInfo stockInfo) {
return stockService.updateOne(stockInfo);
}
/**
* Batch update
*
* @param stockInfoList
* @return
*/
@PostMapping("/updateBatch")
public ResponseVo updateBatch(@RequestBody List<StockInfo> stockInfoList) {
return stockService.updateBatch(stockInfoList);
}
/**
* Paging lookup
*
* @param pageNo Current page
* @param pageSize Records per page
* @param stockParam Inventory query parameter encapsulation object
* @return
*/
@PostMapping("/page/list/{pageNo}/{pageSize}")
public ResponseVo pageListByCondition(@PathVariable("pageNo") int pageNo, @PathVariable("pageSize") int pageSize, @RequestBody StockParam stockParam) {
return stockService.findPageByCondition(stockParam, pageNo, pageSize);
}
/**
* Find the inventory quantity of goods according to the commodity code
*
* @param goodCount
* @return
*/
@GetMapping("/goodCount")
public ResponseVo findGoodCount(@RequestParam("goodCount") String goodCount) {
return stockService.findCountByGoodCode(goodCount);
}
/**
* Modify commodity inventory quantity
*
* @param id
* @param count
* @return
*/
@PostMapping("/update/count")
public ResponseVo updateStockCountById(Long id, Integer count) {
return stockService.updateStockCountById(id, count);
}
}
4 Effect experience
4.1 start up project
After the coding is completed, we start the project on the premise of starting the Mysql service locally. The following log information appears in the console, indicating that the startup is successful:
021-12-04 15:55:19.483 INFO 16832 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2021-12-04 15:55:19.494 INFO 16832 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2021-12-04 15:55:19.494 INFO 16832 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.34]
2021-12-04 15:55:19.723 INFO 16832 --- [ main] o.a.c.c.C.[.[localhost].[/mybatis-plus] : Initializing Spring embedded WebApplicationContext
2021-12-04 15:55:19.723 INFO 16832 --- [ main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 2050 ms
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
_ _ |_ _ _|_. ___ _ | _
| | |\/|_)(_| | |_\ |_)||_|_\
/ |
3.1.0
Registered plugin: 'AbstractSqlParserHandler(sqlParserList=null, sqlParserFilter=null)'
2021-12-04 15:55:20.020 INFO 16832 --- [ main] com.zaxxer.hikari.HikariDataSource : hikariDataSource - Starting...
2021-12-04 15:55:20.195 INFO 16832 --- [ main] com.zaxxer.hikari.HikariDataSource : hikariDataSource - Start completed.
Parsed mapper file: 'file [D:\Mybatis\mybatisplus\target\classes\com\example\mybatisplus\mapper\StockMapper.xml]'
2021-12-04 15:55:20.766 INFO 16832 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2021-12-04 15:55:21.116 INFO 16832 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path '/mybatis-plus'
2021-12-04 15:55:21.120 INFO 16832 --- [ main] c.e.mybatisplus.MybatisPlusApplication : Started MybatisPlusApplication in 4.34 seconds (JVM running for 9.892)
4.2 test interface
After the project is started successfully, we can open postman to test the interface
4.2.1 add a single piece of data to the test
The status code 200 returned from the response result indicates that the interface call is successful. At the same time, we can see that the following sql execution statements are printed on the background console
==> Preparing: INSERT INTO stock_info ( good_code, good_name, count, unit_price, created_by, created_date, last_updated_by, last_updated_date ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ==> Parameters: GalaxyNote20(String), Samsung Noto20(String), 500(Integer), 589900(Long), system(String), 2021-12-04 16:22:31.653(Timestamp), system(String), 2021-12-04 16:22:31.693(Timestamp) <== Updates: 1
4.2.2 adding interfaces in test batch
We call the batch insert interface to insert 5 pieces of data at one time, and the interface returns a status code of 200, indicating that the data is added successfully
Then we can query the database through the client Navicat, and we can see that the data added to the database is added to the database by calling a single add and bulk add interface.
4.2.3 test paging query interface
Finally, we test the paging query effect:
1) First, perform paging query without query parameters. At this time, all the data in the table is queried
A total of 27 pieces of data are found in the interface response information, and 10 pieces of data are displayed on each page, a total of 3 pages
2) Finally, bring query parameters for paging query
During the screenshot, I collapsed the value in the records field. The data in the records field is as follows:
[
{
"createdBy": "system",
"createdDate": "2021-12-04 16:36:50",
"lastUpdatedBy": "system",
"lastUpdatedDate": "2021-12-04 16:36:50",
"id": 23,
"goodCode": "GalaxyNote3",
"goodName": "Samsung Note3",
"count": 500,
"unitPrice": 280000
},
{
"createdBy": "system",
"createdDate": "2021-12-04 16:36:50",
"lastUpdatedBy": "system",
"lastUpdatedDate": "2021-12-04 16:36:50",
"id": 24,
"goodCode": "GalaxyNote4",
"goodName": "Samsung Note4",
"count": 500,
"unitPrice": 300000
},
{
"createdBy": "system",
"createdDate": "2021-12-04 16:36:50",
"lastUpdatedBy": "system",
"lastUpdatedDate": "2021-12-04 16:36:50",
"id": 25,
"goodCode": "GalaxyNote5",
"goodName": "Samsung Note4",
"count": 500,
"unitPrice": 330000
},
{
"createdBy": "system",
"createdDate": "2021-12-04 16:36:50",
"lastUpdatedBy": "system",
"lastUpdatedDate": "2021-12-04 16:36:50",
"id": 26,
"goodCode": "GalaxyNote6",
"goodName": "Samsung Note6",
"count": 500,
"unitPrice": 350000
},
{
"createdBy": "system",
"createdDate": "2021-12-04 16:36:50",
"lastUpdatedBy": "system",
"lastUpdatedDate": "2021-12-04 16:36:50",
"id": 27,
"goodCode": "GalaxyNote7",
"goodName": "Samsung Note7",
"count": 500,
"unitPrice": 380000
}
]
Limited to the length of the article, the test effects of other interfaces are not listed here one by one. Interested readers can clone this project and test the use of mybatis plus to achieve more database operation functions.
5 reference links
[3]Spring boot integration mybatis plus
[4]Implementation of database CRUD with mybatis plus
[5]Mybatis plus paging plug-in
Project gitee address: mybatisplus
This article is the first WeChat official account. I love my articles readers' friends, I hope to scan the code below and add a WeChat concern. Thank you!
