We have already introduced two kinds of in Spring Boot How to access a relational database ：
- Use spring-boot-starter-jdbc
- Use spring-boot-starter-data-jpa
although Spring Data JPA Popular abroad , But at home MyBatis Of the world . therefore , Today we are going to talk about how to do this Spring Boot The integration of MyBatis Complete the operation of adding, deleting, modifying and querying the relational database .
Integrate MyBatis
First step ： newly build Spring Boot project , stay pom.xml Introduction in MyBatis Of Starter as well as MySQL Connector rely on , As follows ：
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
About mybatis-spring-boot-starter We need to pay attention to ：
2.1.xThe version applies to ：MyBatis 3.5+、Java 8+、Spring Boot 2.1+2.0.xThe version applies to ：MyBatis 3.5+、Java 8+、Spring Boot 2.0/2.11.3.xThe version applies to ：MyBatis 3.4+、Java 6+、Spring Boot 1.5
among , What is still being maintained is 2.1.x Version and 1.3.x edition .
The second step ： It’s the same as the use of jdbc Module and jpa The module connects to the database the same , stay application.properties Middle configuration mysql Connection configuration
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
Of course , You can also use no default data source , If you want to use Druid As a database connection pool , You can see 《Spring Boot 2.x： Use domestic database connection pool Druid》 One article .
The third step ：Mysql Create a table for testing , such as ：User surface , It includes id(BIGINT)、age(INT)、name(VARCHAR) Field .
The specific creation commands are as follows ：
CREATE TABLE `User` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
Step four ： establish User Mapping object of table User：
@Data
@NoArgsConstructor
public class User {
private Long id;
private String name;
private Integer age;
public User(String name, Integer age) {
this.name = name;
this.age = age;
}
}
Step five ： establish User Table operation interface ：UserMapper. Define two data operations in the interface , An insert , A query , For subsequent unit test verification .
@Mapper
public interface UserMapper {
@Select("SELECT * FROM USER WHERE NAME = #{name}")
User findByName(@Param("name") String name);
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insert(@Param("name") String name, @Param("age") Integer age);
}
Step six ： establish Spring Boot The main class
@SpringBootApplication
public class Chapter35Application {
public static void main(String[] args) {
SpringApplication.run(Chapter35Application.class, args);
}
}
Step seven ： Create unit tests . The specific test logic is as follows ：
- Insert a name=AAA,age=20 The record of , And then according to name=AAA Inquire about , And decide age Is it 20
- End of test rollback data , Ensure that the data environment of each test unit run is independent
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class Chapter35ApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
@Rollback
public void test() throws Exception {
userMapper.insert("AAA", 20);
User u = userMapper.findByName("AAA");
Assert.assertEquals(20, u.getAge().intValue());
}
}
Note configuration description
The following simultaneous interpreting is implemented by several different ways of reference , Let’s learn MyBatis Some of the common notes in .
Use @Param
In the previous integration example, we have used the simplest parameter passing method , as follows ：
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insert(@Param("name") String name, @Param("age") Integer age);
It’s a good way to understand ,@Param As defined in name Corresponding SQL Medium #{name},age Corresponding SQL Medium #{age}.
Use Map
The following code , adopt Map<String, Object> Object as a container for passing parameters ：
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})")
int insertByMap(Map<String, Object> map);
about Insert Parameters required in statement , All we need to do is map Fill in the content with the same name , See the following code for details ：
Map<String, Object> map = new HashMap<>();
map.put("name", "CCC");
map.put("age", 40);
userMapper.insertByMap(map);
Use object
except Map object , We can also directly use ordinary Java Object as the parameter of query condition , For example, we can use User object :
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insertByUser(User user);
In this sentence #{name}、#{age} They correspond to each other User Object name and age attribute .
Additions and deletions
MyBatis Different annotations are provided for different database operations to configure , In the previous example, I demonstrated @Insert, The following for User Table to do a basic set of additions and deletions as an example ：
public interface UserMapper {
@Select("SELECT * FROM user WHERE name = #{name}")
User findByName(@Param("name") String name);
@Insert("INSERT INTO user(name, age) VALUES(#{name}, #{age})")
int insert(@Param("name") String name, @Param("age") Integer age);
@Update("UPDATE user SET age=#{age} WHERE name=#{name}")
void update(User user);
@Delete("DELETE FROM user WHERE id =#{id}")
void delete(Long id);
}
After completing a set of addition, deletion, modification and search , Let’s try the following unit tests to verify the correctness of the above operations ：
@Transactional
@RunWith(SpringRunner.class)
@SpringBootTest
public class ApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
@Rollback
public void testUserMapper() throws Exception {
// insert A piece of data , and select Come out and verify
userMapper.insert("AAA", 20);
User u = userMapper.findByName("AAA");
Assert.assertEquals(20, u.getAge().intValue());
// update A piece of data , and select Come out and verify
u.setAge(30);
userMapper.update(u);
u = userMapper.findByName("AAA");
Assert.assertEquals(30, u.getAge().intValue());
// Delete this data , and select verification
userMapper.delete(u.getId());
u = userMapper.findByName("AAA");
Assert.assertEquals(null, u);
}
}
Return result binding
For increment 、 Delete 、 The change of operation is relatively small . And for “ check ” operation , We often need to associate multiple tables , Summary calculation and other operations , Then the result of the query is often no longer a simple entity object , It is often necessary to return a wrapper class different from the database entity , So for this kind of situation , You can go through @Results and @Result Annotations to bind , As follows ：
@Results({
@Result(property = "name", column = "name"),
@Result(property = "age", column = "age")
})
@Select("SELECT name, age FROM user")
List<User> findAll();
In the above code ,@Result Medium property Properties corresponding to User Member name in object ,column Corresponding SELECT The name of the field . In this configuration, it is not found intentionally id attribute , Only right User Corresponding to name and age Object is configured for mapping , This can be verified by the following unit tests id by null, Other attributes are not null：
@Test
@Rollback
public void testUserMapper() throws Exception {
List<User> userList = userMapper.findAll();
for(User user : userList) {
Assert.assertEquals(null, user.getId());
Assert.assertNotEquals(null, user.getName());
}
}
This article mainly introduces several most commonly used ways , More use of other annotations can See documentation , Next we will show you how to use XML To configure SQL The traditional way of using .
More free tutorials in this series 「 Click to enter the summary Directory 」
Code example
For an example of this article, see the following in the warehouse chapter3-5 Catalog ：
- Github：https://github.com/dyc87112/SpringBoot-Learning/
- Gitee：https://gitee.com/didispace/SpringBoot-Learning/