SoFunction
Updated on 2025-04-13

Spring Boot Integration MyBatis Connection Database and Frequently Asked Questions

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. The following details how to integrate MyBatis in Spring Boot projects and connect to the database.

1. Basic configuration

1. Add dependencies

existAdd the following dependencies to:

<!-- Spring Boot Starter Web -->
<dependency>
    <groupId></groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- MyBatis Spring Boot Starter -->
<dependency>
    <groupId></groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version> <!-- Use the latest version -->
</dependency>
<!-- Database Driver，Select according to your database -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!-- Other dependencies that may be needed -->
<dependency>
    <groupId></groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.6</version> <!-- Druid Connection pool -->
</dependency>

2. Configure database connections

existorConfigure database connections:

#Configuration examplespring:
  datasource:
    url: jdbc:mysql://localhost:3306/your_database?useSSL=false&serverTimezone=UTC&characterEncoding=utf8
    username: your_username
    password: your_password
    driver-class-name: 
    type:  # Use Druid to connect to the pool# MyBatis configurationmybatis:
  mapper-locations: classpath:mapper/*.xml  # File Location  type-aliases-package:    # Entity class package  configuration:
    map-underscore-to-camel-case: true      # Turn on Camel Naming Conversion

2. Project structure

A typical project structure is as follows:

src/main/java
└── 
    ├──           # Startup class    ├── config
    │   └──         # MyBatis configuration class (optional)    ├── controller
    │   └──        # Controller    ├── service
    │   ├──           # Service Interface    │   └── impl
    │       └──   # Service Implementation    ├── mapper
    │   └──            #Mapper interface    └── model
        └──                  # Entity Classsrc/main/resources
├──                    # Configuration File└── mapper
    └──                 # SQLMapping files

3. Core component implementation (example)

1. Entity Class

package ;
public class User {
    private Long id;
    private String username;
    private String password;
    private String email;
    // getters and setters
    // toString()
}

2. Mapper interface

package ;
import ;
import .*;
import ;
@Mapper // Important: Identify this is a MyBatis Mapper interfacepublic interface UserMapper {
    @Select("SELECT * FROM user WHERE id = #{id}")
    User findById(Long id);
    @Insert("INSERT INTO user(username, password, email) VALUES(#{username}, #{password}, #{email})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    @Update("UPDATE user SET username=#{username}, password=#{password}, email=#{email} WHERE id=#{id}")
    int update(User user);
    @Delete("DELETE FROM user WHERE id=#{id}")
    int delete(Long id);
    // XML configuration method    List<User> findAll();
}


3. Mapper XML file


src/main/resources/mapper/:


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" 
"/dtd/">
<mapper namespace="">
    <resultMap  type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="email" column="email"/>
    </resultMap>
    <select  resultMap="userResultMap">
        SELECT * FROM user
    </select>
</mapper>


4. Service layer




package ;
import ;
import ;
public interface UserService {
    User findById(Long id);
    List<User> findAll();
    int save(User user);
    int update(User user);
    int delete(Long id);
}


Service layer implementation class:


package ;
import ;
import ;
import ;
import ;
import ;
import ;
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;
    @Override
    public User findById(Long id) {
        return (id);
    }
    @Override
    public List<User> findAll() {
        return ();
    }
    @Override
    public int save(User user) {
        return (user);
    }
    @Override
    public int update(User user) {
        return (user);
    }
    @Override
    public int delete(Long id) {
        return (id);
    }
}


5. Controller layer:


package ;
import ;
import ;
import ;
import .*;
import ;
@RestController
@RequestMapping("/users")
public class UserController {
    @Autowired
    private UserService userService;
    @GetMapping("/{id}")
    public User getUser(@PathVariable Long id) {
        return (id);
    }
    @GetMapping
    public List<User> getAllUsers() {
        return ();
    }
    @PostMapping
    public int createUser(@RequestBody User user) {
        return (user);
    }
    @PutMapping
    public int updateUser(@RequestBody User user) {
        return (user);
    }
    @DeleteMapping("/{id}")
    public int deleteUser(@PathVariable Long id) {
        return (id);
    }
}


4. Advanced features


1. Dynamic SQL


Use dynamic SQL in XML:


<select  parameterType="map" resultMap="userResultMap">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
    </where>
</select>


2. Pagination query


Using the PageHelper plugin:


Add dependencies:


<select  parameterType="map" resultMap="userResultMap">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
    </where>
</select>


Example of usage:


<dependency>
    <groupId></groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.1</version>
</dependency>


3. Multi-data source configuration


Configure multiple data sources:


spring:
  datasource:
    primary:
      url: jdbc:mysql://localhost:3306/db1
      username: root
      password: root
      driver-class-name: 
    secondary:
      url: jdbc:mysql://localhost:3306/db2
      username: root
      password: root
      driver-class-name: 


Create a configuration class:


@Configuration
@MapperScan(basePackages = "", sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "")
    public DataSource primaryDataSource() {
        return ().build();
    }
    @Bean(name = "primarySqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        (dataSource);
        (new PathMatchingResourcePatternResolver().getResources("classpath:mapper/primary/*.xml"));
        return ();
    }
    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager primaryTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}
// Created similarlySecondaryDataSourceConfig


5. Frequently Asked Questions


Mapper interface cannot be injected：


    

  • Make sure there is a startup class@MapperScan("")annotation
    • 

  • Or there is a Mapper interface on@Mapperannotation
    • 



XML file not found：


    

  • examine-locationsIs the configuration correct?
    • 

  • Make sure the XML file is in the correct location in the resources directory
    • 



Hump ​​naming does not take effect：


  • Confirm the configuration-underscore-to-camel-case=true



Connection pool configuration：


  • It is recommended to use Druid connection pool and configure reasonable connection parameters.


Transaction Management：


  • Add on Service method@Transactionalannotation


This is the article about Spring Boot integrating MyBatis database connection and frequently asked questions. For more related Spring Boot MyBatis database connection content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!