springboot操作mysql数据库(多数据源+sql写在mapper文件)
1.数据库和表情况
数据库:db_test
表:user
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(50) NOT NULL COMMENT '用户名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
数据库:db_test01
表:sys_log
CREATE TABLE `sys_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`oper_name` varchar(32) DEFAULT NULL,
`oper_content` varchar(32) DEFAULT NULL,
`createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
项目结构
2.pom.xml文件
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.18</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>org.hxl</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>mybatis测试</description>
<dependencies>
<!-- SpringWeb核心依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Lombok（简化实体类） -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- 测试依赖（可选） -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- MySQL 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>8.0.33</version> <!-- 适配 MySQL 8.x，5.x 可改用 5.1.49 -->
</dependency>
<!-- MyBatis 整合 Spring Boot 启动器 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.1</version> <!-- 适配 Spring Boot 2.7 的版本 -->
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<!-- 关键：明确指定插件版本为 2.7.18，与 parent 版本一致 -->
<version>2.7.18</version>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
3.配置文件application.yml
spring:
autoconfigure:
exclude: org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
# 配置两个数据源：primary(主业务库)、secondary(日志库)
datasource:
primary:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.14:3306/db_test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: mysql
secondary:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.14:3306/db_test01?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: mysql
# Mybatis全局配置（通用，驼峰转换、日志打印）
mybatis:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
4.各java文件
PrimaryDataSourceConfig.java
package org.hxl.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
* 主库（primary）数据源配置类
* 1. @Primary 表示：这个是主数据源，当有多个同类型Bean时，优先注入这个
* 2. @MapperScan 核心：指定该数据源对应的Mapper包路径 + SqlSessionFactory
*/
@Configuration
@MapperScan(basePackages = "org.hxl.mapper.primary", sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {
// 1. 创建 主库 数据源对象
@Bean("primaryDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
// 2. 创建 主库 SqlSessionFactory，绑定主库数据源
@Bean("primarySqlSessionFactory")
@Primary
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// 加载当前数据源对应的Mapper.xml文件（如果有），路径：resources/mapper/primary
//bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/primary/*.xml"));
return bean.getObject();
}
// 3. 创建 主库 SqlSessionTemplate，绑定主库SqlSessionFactory
@Bean("primarySqlSessionTemplate")
@Primary
public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
SecondaryDataSourceConfig.java
package org.hxl.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
* 日志库（secondary）数据源配置类
* 注意：不能加 @Primary 注解，一个项目中只能有一个@Primary的数据源
*/
@Configuration
@MapperScan(basePackages = "org.hxl.mapper.secondary", sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class SecondaryDataSourceConfig {
// 1. 创建 日志库 数据源对象
@Bean("secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
// 2. 创建 日志库 SqlSessionFactory，绑定日志库数据源
@Bean("secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// 加载当前数据源对应的Mapper.xml文件（如果有），路径：resources/mapper/secondary
//bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/secondary/*.xml"));
return bean.getObject();
}
// 3. 创建 日志库 SqlSessionTemplate，绑定日志库SqlSessionFactory
@Bean("secondarySqlSessionTemplate")
public SqlSessionTemplate secondarySqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
SysLogController.java
package org.hxl.controller;
import org.hxl.entity.SysLog;
import org.hxl.service.LogService;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
/**
* 系统日志模块控制器 - 操作【日志库log_db】
* 数据源自动绑定：mapper在secondary包下 → 自动使用secondary数据源
*/
@RestController
@RequestMapping("/sysLog")
public class SysLogController {
@Resource
private LogService logService;
// 新增操作日志 - POST请求
@PostMapping
public String addLog(@RequestBody SysLog sysLog) {
int result = logService.addLog(sysLog);
return result > 0 ? "日志新增成功" : "日志新增失败";
}
// 查询所有日志 - GET请求
@GetMapping
public List<SysLog> getAllLog() {
return logService.getAllLog();
}
}
UserController.java
package org.hxl.controller;
import org.hxl.entity.User;
import org.hxl.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
/**
* User 控制层，提供 HTTP 接口
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
// 新增用户
@PostMapping
public String addUser(@RequestBody User user) {
int result = userService.addUser(user);
return result > 0 ? "新增成功" : "新增失败";
}
// 查询所有
@GetMapping
public List<User> getAllUsers() {
return userService.getAllUser();
}
}
SysLog.java
package org.hxl.entity;
import lombok.Data;
import java.util.Date;
@Data
public class SysLog {
private Long id;
private String operName;
private String operContent;
private Date createTime;
}
User.java
package org.hxl.entity;
import lombok.Data;
/**
* 用户实体类（用于参数绑定示例）
*/
@Data
public class User {
private Long id;
private String username;
private Integer age;
private String email;
}
UserMapper.java
package org.hxl.mapper.primary;
import org.apache.ibatis.annotations.Select;
import org.hxl.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
@Insert("INSERT INTO user(username,age,email) VALUES(#{username},#{age},#{email})")
int addUser(User user);
@Select("SELECT * FROM user")
List<User> getAllUser();
}
SysLogMapper.java
package org.hxl.mapper.secondary;
import org.apache.ibatis.annotations.Select;
import org.hxl.entity.SysLog;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface SysLogMapper {
@Insert("INSERT INTO sys_log(oper_name,oper_content) VALUES(#{operName},#{operContent})")
int addLog(SysLog sysLog);
@Select("SELECT * FROM sys_log")
List<SysLog> getAllLog();
}
LogService.java
package org.hxl.service;
import org.hxl.entity.SysLog;
import org.hxl.mapper.secondary.SysLogMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class LogService {
@Resource
private SysLogMapper sysLogMapper;
public int addLog(SysLog sysLog){
return sysLogMapper.addLog(sysLog);
}
public List<SysLog> getAllLog(){
return sysLogMapper.getAllLog();
}
}
UserService.java
package org.hxl.service;
import org.hxl.entity.User;
import org.hxl.mapper.primary.UserMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserService {
@Resource
private UserMapper userMapper;
public int addUser(User user){
return userMapper.addUser(user);
}
public List<User> getAllUser(){
return userMapper.getAllUser();
}
}
App.java
package org.hxl;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class App
{
public static void main(String[] args) {
SpringApplication.run(App.class, args);
System.out.println("===== Spring Boot 启动成功 =====");
}
}
6.调用
新增用户
新增操作日志
浙公网安备 33010602011771号