In front of the word

• Based on Oracle database, this paper explains how to implement CRUD operation of database based on SpringBoot + MyBatis Plus, including the configuration of key features, such as: paging, primary key sequence, timestamp type field automatic filling, etc

Rely on the version

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.3.4. RELEASE</version>
    <relativePath/>
</parent>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.2.0</version>
</dependency>
Copy the code

Important Configuration Items

spring:
  datasource:
    url: <jdbc url>
    username: <username>
    password: <password>
    
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # Standard output SQL execution log on console
Copy the code

The sample code

Test the database table definition

- the Person table
create table PERSON
(
    ID NUMBER not null
        constraint PERSON_PK
            primary key,
    NAME VARCHAR2(100) not null,
    CREATED_TIME TIMESTAMP(6) not null,
    UPDATED_TIME TIMESTAMP(6))/

Person table primary key sequence
CREATE SEQUENCE PERSON_ID_SEQ
    MINVALUE 1
    NOMAXVALUE
    INCREMENT BY 1
    START WITH 1 NOCACHE;
Copy the code

Paging configuration

import com.baomidou.mybatisplus.core.incrementer.IKeyGenerator;
import com.baomidou.mybatisplus.extension.incrementer.OracleKeyGenerator;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;

@Configuration
public class MybatisPlusConfig {

    /** * Page blocker. */
    @Bean
    public PaginationInterceptor paginationInterceptor(a) {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

    /** * OracleKeyGenerator. */
    @Bean
    public IKeyGenerator keyGenerator(a) {
        return newOracleKeyGenerator(); }}Copy the code

Timestamp type field automatic filling processing class

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;

@Component
public class TimestampMetaObjectHandler implements MetaObjectHandler {

    private static final String CREATED_TIME_FIELD = "createdTime";
    private static final String UPDATED_TIME_FIELD = "updatedTime";

    @Override
    public void insertFill(MetaObject metaObject) {
        this.setInsertFieldValByName(CREATED_TIME_FIELD, new Timestamp(System.currentTimeMillis()), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setUpdateFieldValByName(UPDATED_TIME_FIELD, newTimestamp(System.currentTimeMillis()), metaObject); }}Copy the code

Person entity class

@Data
@Accessors(chain = true)
@TableName(value = "PERSON")
// The default primary key sequence field is of type Long and needs to be explicitly changed
@KeySequence(value = "PERSON_ID_SEQ", clazz = Integer.class)
public class PersonEntity {

    @TableId(value = "ID", type = IdType.INPUT)
    private Integer id;

    @TableField(value = "NAME")
    private String name;

    // Use the fill attribute to define when to execute the field filling logic
    @TableField(value = "CREATED_TIME", fill = FieldFill.INSERT)
    private Timestamp createdTime;

    // Use the fill attribute to define when to execute the field filling logic
    @TableField(value = "UPDATED_TIME", fill = FieldFill.UPDATE)
    private Timestamp updatedTime;
}
Copy the code

PersonMapper interface

• Out of the box basic SQL operations are defined in the parent interface BaseMapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

@Mapper
public interface PersonMapper extends BaseMapper<PersonEntity> {}Copy the code

PersonDAO class

• The PersonMapper interface provides richer encapsulation capabilities
• Out of the box SQL extension operations are defined in the parent class ServiceImpl

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
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;

@Service
public class PersonDAO extends ServiceImpl<PersonMapper.PersonEntity> {

    public IPage<PersonEntity> getPersonListByPage(Integer pageNumber, Integer pageSize) {
        // pageNumber Counts from 1
        IPage<PersonEntity> page = new Page<>(pageNumber, pageSize);
        LambdaQueryWrapper<PersonEntity> queryWrapper = new QueryWrapper<PersonEntity>().lambda().orderByDesc(PersonEntity::getCreatedTime);
        returnbaseMapper.selectPage(page, queryWrapper); }}Copy the code

SpringBoot Boot class configuration

@SpringBootApplication
@mapperscan ("
      
       ")
      
public class DemoApplication {... }Copy the code

Integration test class

Insert a single record

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
@Slf4j
// SQL needs to be executed in a transaction
@Transactional
// Disable rollback so that you can see the test effect in the database
@Rollback(value = false)
public class PersonDAOTest {

    @Autowired
    private PersonMapper personMapper;
    
    @Autowired
    private PersonDAO personDAO;

    @Test
    public void testInsert(a) {
        PersonEntity personEntity = new PersonEntity().setName("Alex");
        int insert = personMapper.insert(personEntity);
        log.info("insert {} record", insert); // }... }Copy the code

MyBatis Plus debug output:

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@33de9d85]
JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@7e8f97dd] will be managed by Spring
==>  Preparing: SELECT PERSON_ID_SEQ.NEXTVAL FROM DUAL 
==> Parameters: 
<==    Columns: NEXTVAL
<==        Row: 2
<==      Total: 1
==>  Preparing: INSERT INTO PERSON ( ID, NAME, CREATED_TIME ) VALUES ( ?, ?, ? ) 
==> Parameters: 2(Integer), Alex(String), 2021-08-12 11:50:41.309(Timestamp)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@33de9d85]
Copy the code

You can see:

• Before the INSERT SQL is executed, a SELECT operation is performed on primary key sequence fields
• The CREATED_TIME database field is effectively populated

Update existing records

@Test
public void testUpdate(a) {
	PersonEntity personEntity = new PersonEntity().setId(2).setName("AlexLu");
	int update = personMapper.updateById(personEntity);
	log.info("update {} record", update);
}
Copy the code

MyBatis Plus debug output:

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1f591b2e]
JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@464017f5] will be managed by Spring
==>  Preparing: UPDATE PERSON SET UPDATED_TIME=?, NAME=? WHERE ID=? 
==> Parameters: 2021-08-12 11:55:24.762(Timestamp), AlexLu(String), 2(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1f591b2e]
Copy the code

You can see:

• The UPDATED_TIME database field is effectively populated

Batch insert record

@Test
public void testSaveBatch(a) {
    List<PersonEntity> entityList = new ArrayList<>();
    for (int i = 0; i < 2; i++) {
            entityList.add(new PersonEntity().setName("Name" + i));
    }
    boolean result = personDAO.saveBatch(entityList);
    log.info("batch insert success: {}", result);
}
Copy the code

• Investigate the difference between the underlying implementation of batch inserts and single inserts

MyBatis Plus debug output:

JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@2f006edf] will be managed by Spring ==> Preparing: SELECT PERSON_ID_SEQ.NEXTVAL FROM DUAL ==> Parameters: <== Columns: NEXTVAL <== Row: 4 <== Total: 1 ==> Preparing: INSERT INTO PERSON ( ID, NAME, CREATED_TIME ) VALUES ( ? ,? ,?) ==> Parameters: 4(Integer), Name0(String), 2021-08-12 12:02:25.194(Timestamp) ==> Preparing: SELECT PERSON_ID_SEQ.NEXTVAL FROM DUAL ==> Parameters: <== Columns: NEXTVAL <== Row: 5 <== Total: 1 ==> Parameters: 5 (Integer), Name1 (String), the 2021-08-12 12:02:25. 278 (Timestamp)Copy the code

Paging Query Records

@Test
public void testPageQuery(a) {
    IPage<PersonEntity> personListByPage = personDAO.getPersonListByPage(1.2);
    log.info("total: {}", personListByPage.getTotal());
    List<PersonEntity> records = personListByPage.getRecords();
    records.forEach(r -> log.info("record: {}", r));
}
Copy the code

MyBatis Plus debug output:

Creating a new SqlSession Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f006edf] JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@c6db00d] will be managed by Spring JsqlParserCountOptimize sql=SELECT ID,UPDATED_TIME,NAME,CREATED_TIME FROM PERSON ORDER BY CREATED_TIME DESC ==> Preparing: SELECT COUNT(1) FROM PERSON ==> Parameters: <== Columns: COUNT(1) <== Row: 11 ==> Preparing: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT ID,UPDATED_TIME,NAME,CREATED_TIME FROM PERSON ORDER BY CREATED_TIME DESC ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? ==> Parameters: 2(Long), 0(Long) <== Columns: ID, UPDATED_TIME, CREATED_TIME, ROW_ID <== Row: 13, null, Name9, 2021-08-12 12:02:25.848, 1 <== Row: 12, null, Name8, 2021-08-12 12:02:25.777, 2 <== Total: 2 Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f006edf]Copy the code

It can be seen that:

• Oracle uses the ROWNUM keyword for paging queries. The implementation logic is to identify the ROWNUM boundary where the paging record is located and restrict the query

reference

• MyBatis Plus