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

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.

Example of SpringBoot integration with MyBatis Plus

In front of the word

Rely on the version

Important Configuration Items

The sample code

Test the database table definition

Paging configuration

Timestamp type field automatic filling processing class

Person entity class

PersonMapper interface

PersonDAO class

SpringBoot Boot class configuration

Integration test class

Insert a single record

Update existing records

Batch insert record

Paging Query Records

reference

Example of SpringBoot integration with MyBatis Plus

In front of the word

Rely on the version

Important Configuration Items

The sample code

Test the database table definition

Paging configuration

Timestamp type field automatic filling processing class

Person entity class

PersonMapper interface

PersonDAO class

SpringBoot Boot class configuration

Integration test class

Insert a single record

Update existing records

Batch insert record

Paging Query Records

reference

Related Posts

Keep this martial arts secrets, so that you have no worries

Rust Yew builds front-end pages first experience

The Python HTTPX module tutorial is faster than Requests