[Spring Boot Real Warfare Series] - No.3 Spring boot integration Mybatis

I. What is Mybatis?

Mybatis is an excellent java-based persistence framework, which encapsulates jdbc internally, so that developers only need to pay attention to SQL statement s themselves, and do not need to spend energy to deal with the complicated process of loading driver, creating connection, creating state and so on. At the same time, mybatis provides dynamic SQL based on XML or annotation, which enables us to control and optimize SQL.

2. Springboot integrates Mybatis in two ways:

A. Spring Boot introduces automatic configuration, which makes it easier for developers to use. When we introduce mybatis-spring-boot-starter, spring boot configures mybatis components directly by default so that we can use mybatis directly. The method is as follows:

1. Introducing dependencies into pom.xml: (mainly Ali's druid database connection pool and mybatis-spring-boot-starter)

<?xml version="1.0" encoding="UTF-8"?>
<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">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.springboot.yanming</groupId>
	<artifactId>mybatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>mybatis</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.8.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.1.1</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.29</version>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

2. In application.yml (or. properties format), set the database and mybatis parameters:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springboot
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.springboot.yanming.mybatis.model

In the Mybatis section, we declare the path of the. xml file corresponding to the Mapper class (the mapper folder under the resources root path), and the POJO location of mybatis.

So far, the configuration part of mybatis is over. We just need to write POJO,Mapper and Dao.

3. Write User's Mapper class:

package com.springboot.yanming.mybatis.mapper;

import com.springboot.yanming.mybatis.model.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * @Author: YanMing
 * @Description:
 * @Date: Created in 12:20 2017/11/27
 */
@Mapper
public interface UserMapper {

    //@Select("SELECT username,password,sex FROM user WHERE username = #{username}")
    User findUserByName(@Param("username")String username);

    void insertUser(User user);
}

The Mapper class is the main tool we use to access databases. We can use annotations like @Select to write dynamic SQL directly on methods, or we can write a UserMapper.xml for Mapper to declare statements in an XML file. (idea has a Mybatis plug-in that highlights the way Mapper does not add statement, while alt+enter generates Mapper.xml. You can also add the following UserMapper.xml directly to the resources/mapper mentioned above.

4. Write UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.yanming.mybatis.mapper.UserMapper">
    <sql id="ALL_COLUMN">
        username,password,sex
    </sql>
    <insert id="insertUser" parameterType="com.springboot.yanming.mybatis.model.User">
        INSERT INTO user(username, password, sex) VALUES (
        #{username},
        #{password},
        #{sex}
        )
    </insert>
    <select id="findUserByName" resultType="com.springboot.yanming.mybatis.model.User">
        SELECT
        <include refid="ALL_COLUMN"></include>
        FROM user WHERE username = #{username};
    </select>
</mapper>

At this point, our Mapper is declared Bean and can be injected directly into Dao using @Autowire. The Dao part is easy to write and code directly.

5. writing DAO

package com.springboot.yanming.mybatis.dao;

import com.springboot.yanming.mybatis.model.User;
import org.springframework.stereotype.Repository;

/**
 * @Author: YanMing
 * @Description:
 * @Date: Created in 12:24 2017/11/27
 */

public interface UserDao {

    User findUserByName(String username);

    void insertUser(User user);
}

package com.springboot.yanming.mybatis.dao.impl;

import com.springboot.yanming.mybatis.dao.UserDao;
import com.springboot.yanming.mybatis.mapper.UserMapper;
import com.springboot.yanming.mybatis.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

/**
 * @Author: YanMing
 * @Description:
 * @Date: Created in 19:15 2017/11/27
 */
@Repository
public class UserDaoImpl implements UserDao{
    @Autowired
    UserMapper userMapper;

    @Override
    public User findUserByName(String username) {
        return userMapper.findUserByName(username);
    }

    @Override
    public void insertUser(User user) {
        userMapper.insertUser(user);
    }
}

6. Write Spirngboot Junit test cases

package com.springboot.yanming.mybatis.mapper;

import com.springboot.yanming.mybatis.MybatisApplication;
import com.springboot.yanming.mybatis.dao.UserDao;
import com.springboot.yanming.mybatis.model.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import static org.junit.Assert.*;

/**
 * @Author: YanMing
 * @Description:
 * @Date: Created in 12:28 2017/11/27
 */
@RunWith(SpringJUnit4ClassRunner.class)
//@WebAppConfiguration
@SpringBootTest(classes = MybatisApplication.class)
@SpringBootConfiguration
public class UserMapperTest {
    @Autowired
    UserDao userDao;

    @Test
    public void insertAUser(){
        User user = new User();
        user.setSex("man");
        user.setUsername("yanming");
        user.setPassword("123456");
        userDao.insertUser(user);
        User res = userDao.findUserByName("xiaoming");
        System.out.println(res.getPassword());
    }
}

Note that since my findUserByName() return value is User, there was an error calling this method when there were multiple users with the same name in the database. You can change the return value of this method to List < User > and leave the rest unchanged.

At the same time, we can also customize Mybatis Config to configure Mybatis. There are two required classes for customizing Mybatis:

DataSourceConfig.class: Configure the database (druid is used here)

Mybatis Config. class: Configure Mybatis sqlSessionFactory, etc.

1. Dependencies required in pom. XML (mainly mybatis,mybatis-spring, spring-boot-starter-jdbc and Druid)

<?xml version="1.0" encoding="UTF-8"?>
<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">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.springboot.yanming</groupId>
	<artifactId>mybatisconfig</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>mybatisconfig</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.8.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.1</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.3.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.18</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
			<version>1.5.8.RELEASE</version>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

2. Configure database-related information in application.yml:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springboot
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

3. DataSourceConfig.class

package com.springboot.yanming.mybatisconfig.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * Created by arnold.zhu on 6/13/2017.
 */
@Configuration
public class DataSourceConfig {


    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.filters}")
    private String filters;


    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", username);
        reg.addInitParameter("loginPassword", password);
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

    @Bean
    public DataSource druidDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
           e.printStackTrace();
        }
        return datasource;
    }

}

4. MybatisConfig. class (configuring information in application.yml in our A method in the class)

package com.springboot.yanming.mybatisconfig.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import javax.sql.DataSource;


/**
 * @Author: YanMing
 * @Description:
 * @Date: Created in 14:29 2017/11/27
 */
@Configuration
@EnableTransactionManagement
public class MyBatisConfig implements TransactionManagementConfigurer {

    @Autowired
    DataSource druidDataSource;

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean() {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(druidDataSource);
        bean.setTypeAliasesPackage("com.springboot.yanming.mybatisconfig.entity");

        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath:mapping/*Mapper.xml"));
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(druidDataSource);
    }
}

The following sections on UserMapper, XML file and DAO are omitted, just like in Method A.

Run our test case:

package com.springboot.yanming.mybatisconfig.dao;

import com.springboot.yanming.mybatisconfig.MybatisconfigApplication;
import com.springboot.yanming.mybatisconfig.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

import static org.junit.Assert.*;

/**
 * @Author: YanMing
 * @Description:
 * @Date: Created in 19:06 2017/11/27
 */
@RunWith(SpringJUnit4ClassRunner.class)
//@WebAppConfiguration
@SpringBootTest(classes = MybatisconfigApplication.class)
@SpringBootConfiguration
public class UserDaoTest {
    @Autowired
    UserDao userDao;

    @Test
    public void testUserDao(){

        List<User> users = userDao.findUserByName("yanming");
        System.out.println(users.size()+" user named yanming");

    }
}

The result is that it doesn't work. Because when we customize Mybatis configuration, we also have to specify the path of Mapper to the Application. To solve this problem, we can

1. Note @MapperScan directly on Application ("com.springboot.yanming.mybatisconfig.mapper")

2. Configure MyBatis Mapper Scanner Config, and configure the path to scan Mapper

package com.springboot.yanming.mybatisconfig.config;

import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
//@AutoConfigureAfter(MyBatisConfig.class)
public class MapperScannerConfig {

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.springboot.yanming.mybatisconfig.mapper");
        return mapperScannerConfigurer;
    }

}

While I was studying, I found that a lot of information mentioned that because mapperScanner Configurer was executed earlier than MyBatis Config, sqlSession Factory could not be injected. But I did not report any errors through experiments, so I commented @AutoConfigureAfter (MyBatis Config. class).

The inappropriateness of P.S. article is also expected to be corrected.

Text github source address

Posted by Tokunbo on Tue, 18 Dec 2018 21:42:04 -0800

Programmer Group

[Spring Boot Real Warfare Series] - No.3 Spring boot integration Mybatis

Hot Keywords