Mybatis mapper file tag can write multiple SQL statements? Are there things?

The origin of this blog post, friend interview encountered two questions? Mybatis mapper can write multiple SQL statements to a single tag. The second question is if the above question is true, is there anything in the label?

Four features of database things

ACID atomicity, consistency, isolation, persistence

Answers to questions

Mybatis mapper file can write multiple SQL statements in a single tag

Verify the answer

Create a database table


Create Table

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL.`age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4

Copy the code

2. Build SpringBoot+Mybatis Project

Database configuration:

The default database connection configuration is basically as follows:

url: jdbc:mysql://XXX.XXX.XXX.XXX:XXX/XXXX
Copy the code

Mybatis mapper file can write multiple SQL statements in a tag, will report an exception:

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use 
Copy the code

If we encounter the above exception, we can add parameters to the URL of the database connection, so as to realize the function of executing multiple SQL statements.

url: jdbc:mysql://XXX.XXX.XXX.XXX:XXX/XXXX? allowMultiQueries=true

Copy the code

Third, write MVC three-layer code

MyTest.java


package com.staryea.sfdemo.module.entity;

import lombok.Data;

/ * * *@author: shaofeer
 * <p>
 * @qq: 337081267
 * <p>
 * @CSDN: http://blog.csdn.net/pyfysf
 * <p>
 * @blog: http://wintp.top
 * <p>
 * @email: [email protected]
 * <p>
 * @time: 2020/5/9 * /
@Data
public class MyTest {
    private Integer id;
    private String name;
    private Integer age;
}

Copy the code

MyTestMapper.xml

<?xml version="1.0" encoding="UTF-8" ? >

      
<mapper namespace="com.staryea.sfdemo.module.mapper.MyTestMapper">

    <! SQL > select * from 'SQL';

    <select id="selectBatchSql">
    INSERT INTO  `test` ( `name`, `age`)
    VALUES
      ( 'shaofeer', 10);

    INSERT INTO  `test` (`name`, `age`)
    VALUES
      ( 'pyfysf', '20');

    INSERT INTO  `test` (`name`, `age`)
    VALUES
      ('upuptop', 10);
    </select>
    
    
    <! The following statement is incorrect in order to test for the presence of things -->

    <delete id="deleteBatchSql">
    INSERT INTO  `test` ( `name`, `age`)
    VALUES
      ( 'shaofeer', 10);

    INSERT INTO  `test` (`name`, `age`)
    VALUES
      ( 'pyfysf', 'pyfysf');

    INSERT INTO  `test` (`name`, `age`)
    VALUES
      ('upuptop', 10);
    </delete>
</mapper>

Copy the code

MyTestMapper.java

package com.staryea.sfdemo.module.mapper;

/ * * *@author: shaofeer
 * <p>
 * @qq: 337081267
 * <p>
 * @CSDN: http://blog.csdn.net/pyfysf
 * <p>
 * @blog: http://wintp.top
 * <p>
 * @email: [email protected]
 * <p>
 * @time: 2020/5/9 * /
public interface MyTestMapper {
    void selectBatchSql(a);
    void deleteBatchSql(a);
}
Copy the code

MyTestService.java

package com.staryea.sfdemo.module.service;

/ * * *@author: shaofeer
 * <p>
 * @qq: 337081267
 * <p>
 * @CSDN: http://blog.csdn.net/pyfysf
 * <p>
 * @blog: http://wintp.top
 * <p>
 * @email: [email protected]
 * <p>
 * @time: 2020/5/9 * /
public interface MyTestService {

    void selectBatchSql(a);
    void deleteBatchSql(a);

}

Copy the code

MyTestServiceImpl.java


package com.staryea.sfdemo.module.serviceimpl;

import com.staryea.sfdemo.module.mapper.MyTestMapper;
import com.staryea.sfdemo.module.service.MyTestService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/ * * *@author: shaofeer
 * <p>
 * @qq: 337081267
 * <p>
 * @CSDN: http://blog.csdn.net/pyfysf
 * <p>
 * @blog: http://wintp.top
 * <p>
 * @email: [email protected]
 * <p>
 * @time: 2020/5/9 * /
@Service
public class MyTestServiceImpl implements MyTestService {
    @Autowired
    MyTestMapper mMyTestMapper;


    @Override
    public void selectBatchSql(a) {
        mMyTestMapper.selectBatchSql();

    }

    @Override
    public void deleteBatchSql(a) { mMyTestMapper.deleteBatchSql(); }}Copy the code

MyTestController.java

package com.staryea.sfdemo.module.controller;

import com.staryea.sfdemo.module.service.MyTestService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/ * * *@author: shaofeer
 * <p>
 * @qq: 337081267
 * <p>
 * @CSDN: http://blog.csdn.net/pyfysf
 * <p>
 * @blog: http://wintp.top
 * <p>
 * @email: [email protected]
 * <p>
 * @time: 2020/5/9 * /
@RestController
@RequestMapping("/test")
public class MyTestController {
    @Autowired
    MyTestService mMyTestService;


    @RequestMapping(value = "deleteBatchSql")
    public String deleteBatchSql(a) {
        mMyTestService.deleteBatchSql();
        return "deleteBatchSql";
    }
    @RequestMapping(value = "selectBatchSql")
    public String selectBatchSql(a) {
        mMyTestService.selectBatchSql();
        return "selectBatchSql"; }}Copy the code

4. Start the server

  1. Verify that mapper can execute multiple SQL statements for a single tag

Test by entering the access address of the project in the browser. Browser to access (varies from person to person) : http://localhost:9898/test/selectBatchSql view the success of database insert.

  1. Verify that there is no database transaction when mapper executes multiple SQL for a single tag

Browser to access (varies from person to person) : http://localhost:9898/test/deleteBatchSql we will find that the back-end console to abnormal

### Error updating database. Cause: java.sql.SQLException:
Incorrect integer value: 'pyfysf' for column 'age' at row 1 # # #
Copy the code

If a database transaction exists, then none of the three statements will be successfully inserted into the database.

By checking the database table data, the first statement is successfully executed, the second and third statements are not successfully executed, indicating that mybatis mapper file in a label to execute multiple SQL statements, there is no database things

Fifth, pay attention to

If added to a method in the Service layer@TransactionalAfter the annotation, the tag has something!

    @Transactional
    @Override
    public void deleteBatchSql(a) {
        mMyTestMapper.deleteBatchSql();
    }
Copy the code

Thank you for checking, I hope to help you. If there is any error in the blog, please point out in time, I will immediately change. Thank you very much! Welcome to follow my wechat public account “Fun Learning program” to get more…