preface
Students with Java Web application development experience should be familiar with the three-layer structure design such as Controller/Service/Dao. MyBatis is one of the mainstream ways to implement THE Dao layer, which is used to complete the read and write operations of the database. The Dao layer serves the Service layer and is used to complete business logic operations.
This article focuses on the integrated use of SpringBoot and MyBatis. Considering that the introduction of Controller and Service layers will be complicated to describe, only the Dao layer is involved.
Create projects/modules
Add mybatis module to Maven project SpringBoot for demonstration, mybatis pop.xml:
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>springboot</artifactId>
<groupId>tech.exchange</groupId>
<version>0.1</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>mybatis</artifactId>
</project>
Copy the codeThe Controller calls the Service, and the Service calls the Dao. As mentioned earlier, how do you implement Dao calls without introducing controllers and services?
SpringBoot can be either a Web application or a Console application, similar to Java Main.
SpringBoot Console Application
Demonstrates how to create a SpringBoot command line application.
- Add the dependent
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
Copy the codeNote: The spring-boot-starter dependency needs to be added for Web applications and the spring-boot-starter dependency needs to be added for command-line applications.
- Create the Main
package tech.exchange.springboot.mybatis;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/ * * *@author yurun
*/
@SpringBootApplication
public class Main implements CommandLineRunner {
@Override
public void run(String... args) throws Exception {}public static void main(String[] args) { SpringApplication.run(Main.class, args); }}Copy the codeMain, in this case, is essentially Java Main, with additional annotations and implementation of specific interface methods.
CommandLineRunner
CommandLineRunner is a simple Spring Boot interface with a run method. Spring Boot will automatically call the run method of all beans implementing this interface after the application context has been loaded.
CommandLineRunner is an interface to SpringBoot that has only one run method; After the SpringBoot container is loaded, all Beans that implement the CommandLineRunner interface are automatically called to the Run method.
Main is a special Bean that implements the interface CommandLineRunner. After the SpringBoot container is loaded, the Run method is automatically executed. We can implement Dao calls inside the run method.
SpringBoot integration MyBatis
MyBatis officially provides a SpringBoot integration solution, the process is very simple, add rely on MyBatis -spring-boot-starter can be:
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
Copy the codeSpringBoot and MyBatis integration is complete.
Mysql-connector-java = mysql-connector-java = mysql-connector-java
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the codeCreate a database/table
Create table myTable for demo use:
CREATE TABLE `mytable` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
`col2` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the codeThe primary key ID uses the AUTO_INCREMENT policy.
Configure the data source/connection pool
The interaction between SpringBoot and the database needs to be realized through the DataSource. The DataSource only needs to declare the related properties using the configuration file (application.yml). No additional operations are required. MyBatis also uses the data source to initialize itself.
The data source
Create application. Yml (SRC/main/resources/application. The yml) :
spring: datasource: url: jdbc:mysql://mysql_dev:13306/yurun? useUnicode=true&characterEncoding=UTF-8 username: root password: rootCopy the code-
Url: MySQL connection information, including address, port, database name, and other parameters. You are advised to set useUnicode=true and characterEncoding=UTF-8 to avoid Garbled Characters.
-
Username: indicates the MySQL username.
-
Password: indicates the password of the MySQL database.
The connection pool
Connection pooling caches connections of several MySQL databases to avoid repeated Connection creation and destruction and reduce SQL execution time.
When SpringBoot integrates MyBatis, it automatically integrates the connection pool HikariCP, which is also recommended by SpringBoot. The default connection pool configuration is used here.
CRUD
MyBatis for the database read and write operation is realized by Mapper, Mapper has two forms:
-
Interface + XML
-
Interface + annotation
These two forms are not absolutely good or bad, using interface + annotation, only need to write an interface, the implementation will be more concise, but the flexibility will be relatively weak; With interface + XML, you need to write additional XML files in addition to writing the interface, but with more flexibility.
This paper uses the form of interface + XML, taking reading and writing data table myTable as an example to create interface and XML file.
Create the interface
Interface file: SRC/main/Java/tech/exchange/springboot/mybatis/dao/MyTableMapper Java
package tech.exchange.springboot.mybatis.dao;
import org.apache.ibatis.annotations.Mapper;
/ * * *@author yurun
*/
@Mapper
public interface MyTableMapper {}Copy the codeCreate an XML
XML file: SRC/main/resources/mapper/MyTableMapper. The XML
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="tech.exchange.springboot.mybatis.dao.MyTableMapper">
</mapper>
Copy the codeIt is recommended that the interface file name be the same as the XML file name (optional) for easy search.
XML file is located in the directory of SRC/main/resources/mapper, need to configure the application. The yml, makes the mapper can be SpringBoot scanning is loaded correctly.
mybatis:
mapper-locations:
- classpath:mapper/*.xml
Copy the codeSpringBoot is told to scan and load mapper in the mapper directory on the classpath.
XML Namespace Is used to bind the interface and XML file of Mapper. The two must correspond one by one. The namespace value must be the full class name of the interface.
Creating an entity Class
package tech.exchange.springboot.mybatis.model;
/ * * *@author yurun
*/
public class MyRow {
private int id;
private String col1;
privateString col2; . }Copy the codeThe fields of the entity class MyRow correspond (optionally) to the fields of the database table MyTable, and an instance of MyRow represents a row of MyTable.
Create
- Insert a single line record
XML insert element
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="tech.exchange.springboot.mybatis.dao.MyTableMapper">
<insert id="insertMyRow">
INSERT INTO
mytable (col1, col2)
VALUES
(#{col1}, #{col2})
</insert>
</mapper>
Copy the codeMyBatis XML can declare parameters in the form of #{name}; Name indicates the parameter name.
So, where do these parameters come from? The insert element has an attribute parameterType that specifies the type of the argument, such as:
<insert id="insertMyRow" parameterType="tech.exchange.springboot.mybatis.model.MyRow">.</insert>
Copy the codeAn instance of MyRow is used to pass the parameters required for insert. #{col1} and #{col2} correspond to the fields col1 and col2 of MyRow.
In most cases, we can omit the attribute parameterType, which MyBatis will automatically infer for us.
Interface methods
package tech.exchange.springboot.mybatis.dao;
import org.apache.ibatis.annotations.Mapper;
import tech.exchange.springboot.mybatis.model.MyRow;
import java.util.List;
/ * * *@author yurun
*/
@Mapper
public interface MyTableMapper {
/** * insert a row of records. * *@paramRow Indicates a row *@returnAffects the number of rows */
int insertMyRow(MyRow row);
}
Copy the codeThe interface method name must be the same as the XML insert element ID. The SQL statement in the XML element with the corresponding name is executed when the interface method is called.
#{col1}, #{col2} need to be the same as the col1, col2 name of the entity class MyRow. That is, when the interface method executes, the field values in the MyRow instance object ROW are assigned by name to the parameters in the INSERT statement.
The interface method returns the number of affected rows and the insertion of radio records returns 1.
Note: The interface methods for insert, modify, and delete all return values for affected rows.
Insert a single line record
Add MyTableMapper to Main:
@Autowired
private MyTableMapper mapper;
Copy the codeAutowired indicates that SpringBoot automatically injects instances based on the interface type.
Add the code to insert the record in the Main run method:
MyRow row = new MyRow();
row.setCol1("a");
row.setCol2("b");
int value = mapper.insertMyRow(row);
System.out.println(value);
Copy the codeSince the primary key ID of myTable is incremented, there is no need to set the value of MyRow ID.
Complete code for Main:
package tech.exchange.springboot.mybatis;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tech.exchange.springboot.mybatis.dao.MyTableMapper;
import tech.exchange.springboot.mybatis.model.MyRow;
/ * * *@author yurun
*/
@SpringBootApplication
public class Main implements CommandLineRunner {
@Autowired
private MyTableMapper mapper;
@Override
public void run(String... args) throws Exception {
MyRow row = new MyRow();
row.setCol1("a");
row.setCol2("b");
int value = mapper.insertMyRow(row);
System.out.println(value);
}
public static void main(String[] args) { SpringApplication.run(Main.class, args); }}Copy the code- Inserts a single row record and gets the primary key ID of the inserted record
As mentioned above, the myTable primary key field ID is auto-increment, which is to get the auto-increment of the inserted record ID.
XML insert element
<insert id="insertMyRow" useGeneratedKeys="true" keyProperty="id">
INSERT INTO
mytable (col1, col2)
VALUES
(#{col1}, #{col2})
</insert>
Copy the code-
UseGeneratedKeys is true, which tells MyBatis to use the JDBC getGeneratedKeys method to obtain the value of the auto-increment primary key of the inserted record.
-
The value of keyProperty is id, indicating that MyBatis will obtain the value of the increment primary key, and assign the value to MyRow instance field ID;
Inserts a single row record and gets the primary key ID of the inserted record
// Create a record
MyRow row = new MyRow();
row.setCol1("a");
row.setCol2("b");
/ / insert
int value = mapper.insertMyRow(row);
System.out.println(value);
// Get the primary key ID
System.out.println(row.getId());
Copy the codeAfter the insert method completes execution
mapper.insertMyRow(row)
Copy the codeThat is, the primary key ID can be obtained
row.getId()
Copy the codeThe primary key ID is obtained from the ROW record instance.
- Insert multi-line records
Inserting a multi-row record is similar to inserting a single-row record, but the key is how the INSERT statement expresses the multi-row operation.
XML insert element
<insert id="insertMyRows" useGeneratedKeys="true" keyProperty="id">
INSERT INTO
mytable (col1, col2)
VALUES
<foreach item="item" collection="list" separator=",">
(#{item.col1}, #{item.col2})
</foreach>
</insert>
Copy the codeThe key to inserting multiple rows is to use the foreach element to loop through the SQL statements required for inserting multiple rows:
-
Collection =”list”, indicating that the interface method passes multiple lines through the collection (list), for example: list;
-
Item =”item”, indicating that when processing a row of records, the name (alias) of the row is item, and the value of the record field can be obtained through item. For example, item.col1 is used to obtain the value of col1 of a row.
-
Separator =”,”, indicates that the processing results of multiple lines are connected with a comma; (A1, b1), (a2, b2).
There’s another way to write it:
<insert id="insertMyRows" useGeneratedKeys="true" keyProperty="id">
<foreach item="item" collection="list" separator=";">
INSERT INTO
mytable (col1, col2)
VALUES
(#{item.col1}, #{item.col2})
</foreach>
</insert>
Copy the codeThe first method is to execute a single SQL statement in a single request (MySQL), and the second method is to execute multiple SQL statements in a single request. This method is only used to demonstrate the flexible use of foreach, and is not recommended in batch scenarios.
AllowMultiQueries =true = allowMultiQueries=true = allowMultiQueries=true
Interface methods
/** * Insert multi-line record. * *@paramRows multiple rows *@returnAffects the number of rows */
int insertMyRows(List<MyRow> rows);
Copy the codeInstead of inserting a single row, the interface method takes MyRow (singular); Insert multi-line records with the argument List (plural) to the interface method.
Insert multi-line records
MyRow row1 = new MyRow();
row1.setCol1("a1");
row1.setCol2("b1");
MyRow row2 = new MyRow();
row2.setCol1("a2");
row2.setCol2("b2");
// Multi-line record
List<MyRow> rows = new ArrayList<>();
rows.add(row1);
rows.add(row2);
// insert to return the number of affected rows
int value = mapper.insertMyRows(rows);
System.out.println(value);
rows.forEach(row -> {
// Get the primary key ID of the inserted record
System.out.println(row.getId());
});
Copy the codeSimilar to inserting single row records, after inserting multi-row records, the ID field of each record will be automatically assigned the corresponding primary key ID value by MyBatis.
Read
- Query a single row record
Query a row of records by specifying a record ID.
XML query elements
<select id="selectMyRow" resultType="tech.exchange.springboot.mybatis.model.MyRow">
SELECT
*
FROM
mytable
WHERE
id = #{id}
</select>
Copy the codeThe select element must use a return type resultType attribute query returns the results declarations, here for tech. Exchange. Springboot. Mybatis. Model. MyRow, Indicates that MyBatis will map the queried field value to each field of MyRow instance according to the data table column name; If the table column name is inconsistent with the field name of MyRow, the SELECT statement can redefine the column name using an alias (AS) :
SELECT
id,
col1 AS col1,
col2 AS col2
FROM
mytable
WHERE
id = #{id}
Copy the codeInterface methods
/** * Query a row **@paramId Record ID *@returnRecord * /
MyRow selectMyRow(int id);
Copy the codeMyBatis also supports the use of one or more primitive type variables to pass parameters, note that the names should be consistent.
Querying a Row
int id = 1;
// Query records
MyRow row = mapper.selectMyRow(id);
System.out.println(row.getId());
System.out.println(row.getCol1());
System.out.println(row.getCol2());
Copy the codeAs you can see, the query results are received using the instance variable of MyRow, the return type declared by the INSERT element.
- Query multi-row records
To query all records of the data table mytable to demonstrate the query of multi-row records, you can also use parameters to specify the query conditions, the use of parameters as shown above, no further details.
XML query elements
<select id="selectMyRows" resultType="tech.exchange.springboot.mybatis.model.MyRow">
SELECT
*
FROM
mytable
</select>
Copy the codeMyBatis requires the attribute resultType to declare not the specific collection type (List), but the type of the collection element. It’s still tech. Exchange. Springboot. Mybatis. Model. MyRow.
Interface methods
/** * query multiple rows **@returnMulti-line record */
List<MyRow> selectMyRows(a);
Copy the codeQuery multi-row records
List<MyRow> rows = mapper.selectMyRows();
rows.forEach(System.out::println);
Copy the codeUpdate
Alter table myTABLE alter table mytable alter table mytable
XML modification element
<update id="updateMyRow">
UPDATE
mytable
SET
col1 = #{col1},
col2 = #{col2}
WHERE
id = #{id}
</update>
Copy the codeInterface methods
/** * Modify record **@paramRow Indicates a row *@returnAffects the number of rows */
int updateMyRow(MyRow row);
Copy the codeModify the record
MyRow row = new MyRow();
/ / specified ID
row.setId(1);
// Specify a new field value
row.setCol1("c");
row.setCol2("d");
/ / modify
int value = mapper.updateMyRow(row);
System.out.println(value);
Copy the codeDelete
Alter TABLE myTABLE drop primary key ID;
XML delete element
<delete id="deleteMyRow">
DELETE FROM
mytable
WHERE
id = #{id}
</delete>
Copy the codeInterface methods
/** * Delete record **@paramId Record ID *@returnAffects the number of rows */
int deleteMyRow(int id);
Copy the codeDelete records
int id = 1;
int value = mapper.deleteMyRow(id);
System.out.println(value);
Copy the codesummary
This article through the SpringBoot command line application, demonstrates the whole process of SpringBoot and MyBatis, as well as the basic CRUD example.
After the overall practice, it is found that there are routines to follow in the use of MyBatis. For the read and write operation of a certain data table:
-
Create one or more entity classes for data interaction;
-
Create a MyBatis Mapper to encapsulate data methods. Mapper consists of two parts: Interface (Interface) + XML;
-
Each Method in the Interface of an Element of the (Method) corresponds to the XML (Element, insert/select/update/delete);
-
MyBatis Mapper method call execution, essentially is the execution of SQL statements.
Limited by space, we can only discuss the most basic content of MyBatis to help you get started. Please refer to the official document of MyTatis for details.