SpringBoot integration MyBaits

This is the 23rd day of my participation in the August More Text Challenge

Introduction to the

MyBatis is an excellent persistence layer framework based on Apache open source project iBatis
Mybatis – Spring-boot-starter provides a seamless integration of MyBatis and SpringBoot

Maven is introduced into mybatis – spring – the boot – the starter

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
Copy the code

SpringBoot 2.x requires Mybatis -spring-boot-starter 2.1 or later

Mybatis – Spring-boot-starter provides basic functions
- Automatic DataSource discovery
- Create the SqlSessionFactory using the SqlSessionFactoryBean
- Create and register the SqlSessionTemplate
- Mappers are scanned automatically, registering into the Spring context for easy injection

Basic configuration

Configuring a Data source connection

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo? useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
Copy the code

Configure connection pooling and data source initialization

spring:
  datasource:
    #... (Data source connection configuration)
    # specify connection pool
    type: com.zaxxer.hikari.HikariDataSource
    initialization-mode: always
    continue-on-error: true
    # specify the SQL script to use for initialization
    schema:
      - "classpath:db/schema.sql"
    data:
      - Hikari: minimum-idle: 5 connection-test-query: SELECT 1 FROM DUAL maximum-pool-size: 20 auto-commit: true idle-timeout: 30000 pool-name: DemoHikariCP max-lifetime: 60000 connection-timeout: 30000Copy the code

Common connection pools include DBCP, tomcat-JDBC, CP30, Druid, etc. By default, Hikari is used for SpringBoot

Configure the mapper file path

mybatis:
  mapper-locations: classpath:mybatis/mappers/*.xml
  type-aliases-package: com.example.demo.model
Copy the code

Package name that is configured to scan mapper interface files on the startup class or other configuration class

@Configuration
@MapperScan(value = {"com.example.demo.mapper"})
public class DataSourceConfig {}Copy the code

Use Java classes instead of YAML files for configuration

Configuring a DataSource DataSource

@Configuration
@MapperScan(value = {"com.example.demo.mapper"})
public class DataSourceConfig {

    @Bean
    public DataSource dataSource(a) {
        HikariDataSource dataSource = new HikariDataSource();
        // Configure the connection properties
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/demo? useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
		// Set connection pool parameters
        dataSource.setMinimumIdle(5);
        dataSource.setConnectionTestQuery("SELECT 1 FROM DUAL");
        dataSource.setMaximumPoolSize(20);
        dataSource.setAutoCommit(true);
        dataSource.setIdleTimeout(30000);
        dataSource.setPoolName("Config-HikariCP");
        dataSource.setMaxLifetime(60000);
        dataSource.setConnectionTimeout(30000);

        returndataSource; }}Copy the code

Initialize the dataSourceInitializer for a data source

@Bean
public DataSourceInitializer dataSourceInitializer(a) {
    // Specify data source initialization Settings
    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
    resourceDatabasePopulator.addScript(new ClassPathResource("/db/schema.sql"));
    resourceDatabasePopulator.addScript(new ClassPathResource("/db/data.sql"));
    resourceDatabasePopulator.setContinueOnError(true);
    DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
    dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
    dataSourceInitializer.setDataSource(dataSource());
    return dataSourceInitializer;
}
Copy the code

Configure the MyBatis mapping file location and typeAlias

@Bean
public SqlSessionFactory sqlSessionFactory(a) throws Exception {
    SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(dataSource());
    sqlSessionFactoryBean.setMapperLocations(
        new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mappers/*.xml")); sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");

    return sqlSessionFactoryBean.getObject();
}
Copy the code

Multiple data sources

Basic configuration implementation

How it works: Use the @mapperscan annotation to bind different sqLSessionFactories, or mapping files, to mapper classes in different packages

The data source and MyBatis configuration section can also be configured using YAML

Using the Java configuration class, the configuration is as follows

@Configuration
// Configure mapper scanning and use different sqlSessionFactory for different packages
@MapperScan(value = {"com.example.demo.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory")
@MapperScan(value = {"com.example.demo.mapper2"}, sqlSessionFactoryRef = "sqlSessionFactory2")
public class DataSourceConfig {

    // Configure the primary data source
    @Bean
    @Primary
    public DataSource dataSource(a) {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/demo? useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("root");

        dataSource.setMinimumIdle(5);
        dataSource.setConnectionTestQuery("SELECT 1 FROM DUAL");
        dataSource.setMaximumPoolSize(20);
        dataSource.setAutoCommit(true);
        dataSource.setIdleTimeout(30000);
        dataSource.setPoolName("Config-HikariCP");
        dataSource.setMaxLifetime(60000);
        dataSource.setConnectionTimeout(30000);

        return dataSource;
    }

    // Configure data source 2
    @Bean
    public DataSource dataSource2(a) {
        // Same as above data source configuration code, specify another database
    }


    // Configure mybatis sqlSessionFactory
    @Bean("sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(a) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mappers/*.xml")); sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");

        return sqlSessionFactoryBean.getObject();
    }

    // Configure mybatis sqlSessionFactory2
    @Bean("sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory2(a) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource2());
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mappers2/*.xml")); sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");

        returnsqlSessionFactoryBean.getObject(); }}Copy the code

After the preceding configuration, the mapper class in the Mapper package uses the primary data source, and the Mapper class in the Mapper2 package uses the data source 2

AOP implements dynamic switching

Principle: inherit AbstractRoutingDataSource, rewrite determineCurrentLookupKey method

Dynamic data source class:

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected DataSource determineTargetDataSource(a) {
        return super.determineTargetDataSource();
    }

    /** * Toggle data */ by setting the data source Key
    @Override
    protected Object determineCurrentLookupKey(a) {
        return DynamicDataSourceContextHolder.getDataSourceKey();
    }

    /** * Sets the default data source **@param defaultDataSource
     */
    public void setDefaultDataSource(Object defaultDataSource) {
        super.setDefaultTargetDataSource(defaultDataSource);
    }

    /** * sets the data source **@param dataSources
     */
    public void setDataSources(Map<Object, Object> dataSources) {
        super.setTargetDataSources(dataSources);
        // Put the data source's key into the data source context's key set, which is used to determine whether the data source is valid when switchingDynamicDataSourceContextHolder.addDataSourceKeys(dataSources.keySet()); }}Copy the code

DynamicDataSourceContextHolder, used to save and retrieve the data source key

public class DynamicDataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();


    /** * The key set of the data source, used to determine whether the data source exists */ when switching
    public static List<Object> dataSourceKeys = new ArrayList<>();

    /** * Switch data source **@param key
     */
    public static void setDataSourceKey(String key) {
        contextHolder.set(key);
    }

    /** * get data source **@return* /
    public static String getDataSourceKey(a) {
        return contextHolder.get();
    }

    /** * reset the data source */
    public static void clearDataSourceKey(a) {
        contextHolder.remove();
    }

    /** * Check whether data source ** is included@paramKey Data source key *@return* /
    public static boolean containDataSourceKey(String key) {
        return dataSourceKeys.contains(key);
    }

    /** * add data source keys **@param keys
     * @return* /
    public static boolean addDataSourceKeys(Collection<? extends Object> keys) {
        returndataSourceKeys.addAll(keys); }}Copy the code

Data source configuration class

@Configuration
@MapperScan(value = {"com.example.demo.mapper"})
public class DataSourceConfig {

    // Configure the primary data source
    @Bean
    public DataSource dataSource(a) {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/demo? useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("root");

        dataSource.setMinimumIdle(5);
        dataSource.setConnectionTestQuery("SELECT 1 FROM DUAL");
        dataSource.setMaximumPoolSize(20);
        dataSource.setAutoCommit(true);
        dataSource.setIdleTimeout(30000);
        dataSource.setPoolName("Config-HikariCP");
        dataSource.setMaxLifetime(60000);
        dataSource.setConnectionTimeout(30000);

        return dataSource;
    }

    // Configure data source 2
    @Bean
    public DataSource dataSource2(a) {
        / /... Data Source Configuration
    }


    // Configure dynamic data sources
    @Bean("dynamicDataSource")
    public DataSource dynamicDataSource(a) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>(2);
        // Register the data source, which can be customized to read from the configuration file
        dataSourceMap.put("master", dataSource());
        dataSourceMap.put("second", dataSource2());
        dynamicDataSource.setDefaultDataSource(dataSource());
        dynamicDataSource.setDataSources(dataSourceMap);
        return dynamicDataSource;
    }


    // Configure mybatis sqlSessionFactory
    @Bean("sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(a) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mappers/*.xml")); sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");

        return sqlSessionFactoryBean.getObject();
    }

    // Configure the transaction manager
    @Bean
    public PlatformTransactionManager transactionManager(a) {
        return newDataSourceTransactionManager(dynamicDataSource()); }}Copy the code

AOP proxy method that switches data source before execution and restores default after execution

@Aspect
// Set Order so that the section executes before @transactional
@Order(-1)
@Component
public class DynamicDataSourceAspect {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    /** * Switch data source **@param point
     * @param dataSource
     */
    @Before("@annotation(dataSource)")
    public void switchDataSource(JoinPoint point, DataSource dataSource) {
        String dataSourceKey = dataSource.value();
        if(! DynamicDataSourceContextHolder.containDataSourceKey(dataSourceKey)) { logger.info("DataSource [{}] doesn't exist, use default", dataSource.value());
        } else {
            // Switch data sources
            DynamicDataSourceContextHolder.setDataSourceKey(dataSource.value());
            logger.info("Switch DataSource to [{}] in Method [{}]", dataSourceKey, point.getSignature()); }}/** * resets the data source **@param point
     * @param dataSource
     */
    @After("@annotation(dataSource)")
    public void resetDataSource(JoinPoint point, DataSource dataSource) {
        // Make the data source the default data source
        DynamicDataSourceContextHolder.clearDataSourceKey();
        logger.info("Reset DataSource to default in Method [{}]", point.getSignature()); }}Copy the code

Print log

Print Mybatis log

Using logback as an example, configure logback. XML

<! Additivity specifies whether to add root's appender to logger -->
<logger name="com.example.dao" level="${level}" additivity="true">
	<! Appender -->
    <appender-ref ref="errorAppender" />
    <appender-ref ref="debugAppender" />
    <appender-ref ref="infoAppender" />
</logger>

<! -- Mybatis -->
<! -- Mybatis (mybatis) -- mybatis (Mybatis) -- mybatis (Mybatis) -- mybatis
<logger name="org.apache.ibatis" level="DEBUG" />
<logger name="java.sql" level="DEBUG" />
Copy the code

Print complete SQL

The preceding configuration through the Logback configuration file can only print pre-processed SQL statements, which is not convenient for direct debugging

With the help of MyBatis interceptor, you can obtain MyBatis encapsulated SQL statement object, can further obtain parameters, splicing SQL and print

@Intercepts({ @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class SqlPrintingInterceptor implements Interceptor {

    private static final Logger logger = LoggerFactory.getLogger(SqlPrintingInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // Get the SQL description statement object
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        // Get parameters
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }
        // Get the SQL Id
        String sqlId = mappedStatement.getId();
        // Get BoundSql, the SQL object wrapped by Mybatis
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        // Get the configuration
        Configuration configuration = mappedStatement.getConfiguration();
        // Execute
        long start = System.currentTimeMillis();
        Object returnValue = invocation.proceed();
        long time = System.currentTimeMillis() - start;
        / / print the SQL
        showSql(configuration, boundSql, time, sqlId);
        return returnValue;
    }
    
    /** ** handle characters * in SQL@param configuration
     * @param boundSql
     * @param time
     * @param sqlId
     */
    private static void showSql(Configuration configuration, BoundSql boundSql, long time, String sqlId) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        // Replace Spaces, newlines, TAB indents, etc
        String sql = boundSql.getSql().replaceAll("[\\s]+"."");
        if (parameterMappings.size() > 0&& parameterObject ! =null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\ \"?", getParameterValue(parameterObject));
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\ \"?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\ \"?", getParameterValue(obj));
                    }
                }
            }
        }
        logs(time, sql, sqlId);
    }

    /** * handle different types of arguments **@param obj
     * @return* /
    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + obj + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if(obj ! =null) {
                value = obj.toString();
            } else {
                value = ""; }}return value.replace("$".\ \ "$");
    }

    /** * Prints log *@param time
     * @param sql
     * @param sqlId
     */
    private static void logs(long time, String sql, String sqlId) {
        String sb = "==> Executed [" + sqlId + "] in " + time + "Ms with SQL:" + sql;
        logger.info(sb);
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties0) {}}Copy the code

Introduction to the

Basic configuration

Multiple data sources

Print log

Related Posts

Take 7 days, the most powerful MySQL optimization summary in history, from optimization So Easy!

The interviewer asked about the factory model, did you understand it correctly?

6 ways to quickly count your code execution time