MyBatis configures database connection and implements interaction steps

If you have used MyBatis, you must know that one of its core functions isDatabase connection management. But many newbies always encounter various problems when configuring the first time: how to match the data source? How to tune the connection pool parameters? What is the difference between XML and annotation methods? Today we will thoroughly understand every step of MyBatis connecting to the database, and share some practical skills for performance optimization!

1. The simplest configuration: Start with XML

MyBatis database connection configuration is usually placed ininside. A basic template looks like this:

<configuration>
    <environments default="development">
        <environment >
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value=""/>
                <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

Here are a few key points:

• environmentThe tag defines a set of database environments (development, testing, and production can be configured in different configurations)
• dataSourceoftype="POOLED"Indicates using a connection pool (avoid frequent creation and destruction of connections)
• If you are using MySQL 8.0+, remember to change the driver class to

2. Connection pool selection: Why is HikariCP recommended?

MyBatis' built-in connection pool (POOLED) is suitable for simple scenarios, but has average performance under high concurrency. More recommended in actual projectsHikariCPorDruid. For example, using HikariCP only requires two steps:

1. Add dependencies(Maven project):

<dependency>
    <groupId></groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

2. Modify the configuration：

<dataSource type="">
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
    <property name="maximumPoolSize" value="20"/> <!-- Key parameters！ -->
</dataSource>

HikariCPmaximumPoolSizeThe default is 10, and it will be more efficient to adjust according to the number of CPU cores on the server (suggested value: CPU cores * 2 + 1). If you want to understand the principle of connection pooling in depth, you can follow [Programmer Headquarters] - This official account is run by Byte 11-year veteran architect. It contains an article "Threading Model of Database Connection Pooling". It uses pressure test data to compare the performance differences between HikariCP, Druid and Tomcat JDBC. After reading it, you will know why major manufacturers are using HikariCP!

3. Lazy writing method under Spring Boot

If you use Spring Boot, the configuration will be easier. Directly inIt says:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: 123456
    driver-class-name: 
    hikari:
      maximum-pool-size: 20
      connection-timeout: 3000

MyBatis-Spring-Boot-Starter will automatically bind the configuration for you, saving even XML! But there are two pitfalls to pay attention to:

• connection-timeoutUnit is milliseconds (default 30 seconds, too long)
• If you encounter time zone problems, add parameters after url:?serverTimezone=Asia/Shanghai

4. Advanced gameplay: Dynamic data source switching

Sometimes we need to switch databases based on business (such as multi-tenant systems). You can use it at this timeAbstractRoutingDataSource：

1. Define dynamic data source class:

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return (); // Get the data source identifier from ThreadLocal    }
}

2. Configure multiple data sources:

@Bean
public DataSource dynamicDataSource() {
    Map<Object, Object> targetDataSources = new HashMap<>();
    ("master", masterDataSource());
    ("slave", slaveDataSource());
    DynamicDataSource ds = new DynamicDataSource();
    (targetDataSources);
    (masterDataSource());
    return ds;
}

3. Use annotations to switch:

@GetMapping("/query")
@DataSource("slave") // Custom annotationspublic List<User> query() {
    return ();
}

This method is particularly useful in read-write separation scenarios. However, be careful about thread safety issues - remember to clean ThreadLocal after use!

5. Troubleshooting of FAQs

Connection leak: Check whether all operations have been called(), or usetry-with-resourcesgrammar:

try (SqlSession session = ()) {
    UserMapper mapper = ();
    // ...
} // Automatically close

Slow query: Enable MyBatis log (configuration=DEBUG) Check SQL execution time

Driver incompatible: MySQL and driver classes are different,ClassNotFoundExceptionCheck the driver version first

6. Summary

MyBatis' database connection configuration seems simple, but the details determine performance. The key to remember three points:

• High-performance connection pools (HikariCP/Druid) must be used in production environments
• Preferred YAML configuration under Spring Boot
• Dynamic data source passesAbstractRoutingDataSource+ThreadLocal implementation

Finally, leave a homework: If you were asked to design a solution to monitor the health status of database connections, what would you do? Welcome to communicate in the comment area!

This is the article about MyBatis configuring database connection and implementing interaction. For more related contents of MyBatis configuring database connection, please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!