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
- 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.
- 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@Transactional
After 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…