Spring boot (VI) integrates mybatis to operate MySQL 8
1、 Introduction
1.1 introduction to mybatis
Mybatis is an excellent persistence layer framework, which supports customized SQL, stored procedures and advanced mapping. Mybatis avoids almost all JDBC code and manually setting parameters and getting result sets.
1.2 development history of mybatis
Mybatis was originally an open source project ibatis of Apache. In 2010, the project was migrated from Apache Software Foundation to Google Code, renamed mybatis, and migrated to GitHub in November 2013.
1.3 differences between mybatis and Hibernate
Mybatis and Hibernate are excellent persistence frameworks that support JDBC (Java database connection) and JTA (Java transaction API) transaction processing.
Mybatis benefits
Hibernate benefits
1.4 mybatis integration mode
There are two ways to integrate mybatis:
The XML version is an old-fashioned configuration integration method, with heavy integration of XML files, and all SQL statements are written in XML; The annotated version is relatively simple and does not require XML configuration. It only needs annotations and code to operate the data.
2、 Annotated mybatis integration
development environment
Mybatis spring boot is a version of mybatis officially launched by mybatis to integrate spring boot.
2.1 adding dependencies
Set POM XML file, add the following configuration
<dependency>
<groupId>MysqL</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.12</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
Add MySQL and mybatis support.
2.2 configuring database connections
Set application Properties file, add the following configuration
# MyBatis 配置
spring.datasource.url=jdbc:MysqL://172.16.10.79:3306/mytestdb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.MysqL.cj.jdbc.Driver
mybatis.type-aliases-package=com.hello.springboot.mapper
Mapper file description
Mapper is the core of mybatis, where SQL is stored and where configuration database is mapped.
2.3 setting mapperscan package path
Directly in the boot file springbootapplication Configure @ mapperscan on the Java class, so you can save the trouble of identifying @ mapper on each mapper separately.
@SpringBootApplication
@MapperScan("com.hello.springboot.mapper")
public class SpringbootApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootApplication.class,args);
}
}
2.4 add code
In order to demonstrate the simplicity, we will not do too much hierarchical processing. Here we are divided into entity class, mapper interface and controller class. The controller directly calls mapper interface for data persistence.
User class:
public class User {
private Long id;
private String name;
private int age;
private String pwd;
//省去set、get方法
}
Usermapper interface:
public interface UserMapper {
@Select("select * from user")
@Results({
@Result(property = "name",column = "name")
})
List<User> getAll();
@Select("select * from user where id=#{id}")
User getById(Long id);
@Insert({"insert into user(name,age,pwd) values(#{name},#{age},#{pwd})"})
void install(User user);
@Update({"update user set name=#{name} where id=#{id}"})
void Update(User user);
@Delete("delete from user where id=#{id}")
void delete(Long id);
}
It can be seen that all SQL is written in mapper interface.
Notes in mapper
Controller:
@RestController
@RequestMapping("/")
public class UserController {
@Autowired
private UserMapper userMapper;
@RequestMapping("/")
public ModelAndView index() {
User user = new User();
user.setAge(18);
user.setName("Adam");
user.setPwd("123456");
userMapper.install(user);
ModelAndView modelAndView = new ModelAndView("/index");
modelAndView.addObject("count",userMapper.getAll().size());
return modelAndView;
}
}
So far, the configuration of mybatis project has been completed and you can run debugging.
Annotated GitHub source code download: https://github.com/vipstone/springboot-example/tree/master/springboot-mybatis
3、 Mybatis integration for XML
3.1 adding dependencies
Set POM XML file, add the following configuration
<dependency>
<groupId>MysqL</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.12</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
Add MySQL and mybatis support.
3.2 configuring database connections
Set application Properties file, add the following configuration
# MyBatis 配置
spring.datasource.url=jdbc:MysqL://172.16.10.79:3306/mytestdb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.MysqL.cj.jdbc.Driver
mybatis.type-aliases-package=com.hello.springboot.entity
mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
3.3 setting mapperscan package path
Directly in the boot file springbootapplication Configure @ mapperscan on the Java class, so you can save the trouble of identifying @ mapper on each mapper separately.
@SpringBootApplication
@MapperScan("com.hello.springboot.mapper")
public class SpringbootApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootApplication.class,args);
}
}
3.4 configuration XML file
This example sets up two XML files, mybatis config. Under resource / mybatis XML (configure the basic properties of mybatis) and usermapper.xml (SQL statement for user and data interaction) under resource / mybatis / mapper.
mybatis-config. xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias alias="Integer" type="java.lang.Integer"/>
<typeAlias alias="Long" type="java.lang.Long"/>
<typeAlias alias="HashMap" type="java.util.HashMap"/>
<typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap"/>
<typeAlias alias="ArrayList" type="java.util.ArrayList"/>
<typeAlias alias="LinkedList" type="java.util.LinkedList"/>
</typeAliases>
</configuration>
UserMapper. xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace是命名空间，是mapper接口的全路径-->
<mapper namespace="com.hello.springboot.mapper.UserMapper">
<!--resultMap – 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象-->
<resultMap id="userResultMap" type="com.hello.springboot.entity.User">
<id property="name" column="username"></id>
</resultMap>
<!--sql – 可被其他语句引用的可重用语句块-->
<sql id="colums">
id,username,pwd
</sql>
<select id="findAll" resultMap="userResultMap">
select
<include refid="colums" />
from user
</select>
<select id="findById" resultMap="userResultMap">
select
<include refid="colums" />
from user
where id=#{id}
</select>
<insert id="insert" parameterType="com.hello.springboot.entity.User" >
INSERT INTO
user
(username,pwd)
VALUES
(#{name},#{pwd})
</insert>
<update id="update" parameterType="com.hello.springboot.entity.User" >
UPDATE
users
SET
<if test="username != null">username = #{username},</if>
<if test="pwd != null">pwd = #{pwd},</if>
username = #{username}
WHERE
id = #{id}
</update>
<delete id="delete" parameterType="java.lang.Long" >
DELETE FROM
user
WHERE
id =#{id}
</delete>
</mapper>
The SQL mapping file has few top-level elements (in the order in which they should be defined):
Note: the XML header files of config and mapper in mybatis are different.
Config header file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
Mapper header file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
Mapper XML configuration: http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html
3.5 add code
For the convenience of demonstration, we add three classes for function display, namely entity class user Java, mapper interface usermapper Java and controller class usercontroller Java, use the controller class to directly call the usermapper method for data storage and query.
User. java
package com.hello.springboot.entity;
public class User {
private Long id;
private String name;
private int age;
private String pwd;
//省略set/get方法
}
UserMapper. java
package com.hello.springboot.mapper;
import com.hello.springboot.entity.User;
import java.util.List;
public interface UserMapper {
List<User> findAll();
User findById(Long id);
void insert(User user);
void update(User user);
void delete(Long id);
}
Note: the method name in mapper must be consistent with that in mapper XML, otherwise the executed SQL will not be found.
UserController. java
@RestController
@RequestMapping("/")
public class UserController {
@Resource
private UserMapper userMapper;
@RequestMapping("/")
public ModelAndView index() {
User user = new User();
user.setAge(18);
user.setName("Adam");
user.setPwd("123456");
userMapper.insert(user);
ModelAndView modelAndView = new ModelAndView("/index");
modelAndView.addObject("count",userMapper.findAll().size());
return modelAndView;
}
}
So far, the configuration of mybatis project has been completed and you can run debugging.
Download GitHub source code in XML: https://github.com/vipstone/springboot-example/tree/master/springboot-mybatis-xml
4、 Summary
So far, we have mastered two integration methods of mybatis, annotation integration and XML integration. The annotation version is more in line with programmers' code writing habits and is suitable for simple and fast query; The XML version can flexibly and dynamically adjust SQL, which is more suitable for large-scale project development. The specific choice depends on the development scenario and personal preferences.