springboot mysql 多个数据 springboot多数据源mybatis

一、首先是Springboot框架搭建：

1.IDEA左上角File——New——project：

 

2.选择需要的依赖，IDEA帮自动加入pom文件中，这里就算不选，后期手动写到pom文件也是一样的。

3.点击finish，选择新窗口打开就会生成新的项目。项目结构如图，生成的pom文件里面可能需要修改一下版本，如果是springboot3，启动会报错版本不对。我测试用的版本如图。配置好maven，这里就不赘述maven的配置了。

 我修改完的pom文件如下：

<?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.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.springbootmybatis</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <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>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>
        <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>
        <dependency>
            <groupId>cn.easyproject</groupId>
            <artifactId>orai18n</artifactId>
            <version>12.1.0.2.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

 4.配置文件加入内容：（必须要有数据库的配置，不然跑不起来）

#tomcat配置
#端口
server.port=50001

#数据库配置
#数据源配置，包括ip，端口，数据库名等
spring.datasource.url=jdbc:oracle:thin:@//192.168.100.6:1521/rhipdb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
#用户密码
spring.datasource.username=数据库账号
spring.datasource.password=数据库密码
#数据库驱动（Driver）类名
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
#是否输出sql语句
spring.datasource.jpa.show-sql=true
#连接池名称，一般等于这套数据库的配置名
#spring.datasource.pool-name=primary
#连接池最大连接数
spring.datasource.maximum-pool-size=30
#向连接池获取连接的最大等待时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
spring.datasource.connection-timeout=30000
#一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:10分钟，建议设置比数据库超时时长少30秒
spring.datasource.max-lifetime=600000
#一个连接空闲状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
spring.datasource.idle-timeout=600000
#自动提交。值为：true；false
spring.datasource.auto-commit=true

5.然后在启动类里加上如图内容后启动： 

 6.再去浏览器访问测试：

这样就相当于实现了最简单的springboot框架搭建。

二、接下来简单加上mybatis实现查询数据库返回数据

1.按以下目录结构创建文件：

UserController 代码：

package com.springbootmybatis.demo.controller;

import com.springbootmybatis.demo.entity.User;
import com.springbootmybatis.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/User")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("selectById")
    public User selectById(String c_id){
        return userService.selectById(c_id);
    }

    @GetMapping("selectByUsername")
    public User selectByUsername(String c_name){
        return userService.selectByUsername(c_name);
    }

    @GetMapping("selectAll")
    public List<User> selectAll(){
        List<User>  users = userService.selectAll();
        return users;
    }
}

User 实体类代码（根据自己的表结构建）：

package com.springbootmybatis.demo.entity;

public class User {
    private String id;

    private String name;

    private String code;

    private String addr;

    private String phone;

    private String idnumber;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getIdnumber() {
        return idnumber;
    }

    public void setIdnumber(String idnumber) {
        this.idnumber = idnumber;
    }
}

UserMapper 代码：

package com.springbootmybatis.demo.mapper;

import com.springbootmybatis.demo.entity.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {
    User selectById(@Param("c_id") String c_id);

    User selectByUsername(@Param("c_name") String c_name);

    List<User> selectAll();
}

UserServiceImpl代码：

package com.springbootmybatis.demo.service.Impl;

import com.springbootmybatis.demo.entity.User;
import com.springbootmybatis.demo.mapper.UserMapper;
import com.springbootmybatis.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    //调用UserMapper查询数据
    //@Resource或者@Autowired注解都可
    @Resource
    @Autowired
    private UserMapper userMapper;

    @Override
    public User selectById(String c_id) {
        return userMapper.selectById(c_id);
    }

    @Override
    public User selectByUsername(String c_name) {
        return userMapper.selectByUsername(c_name);
    }

    @Override
    public List<User> selectAll() {
        List<User>  users = userMapper.selectAll();
        return users;
    }
}

UserService代码：

package com.springbootmybatis.demo.service;


import com.springbootmybatis.demo.entity.User;

import java.util.List;

public interface UserService {

    User selectById(String c_id);

    User selectByUsername(String c_name);

    List<User> selectAll();
}

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.springbootmybatis.demo.mapper.UserMapper">

    <resultMap id="userMap" type="com.springbootmybatis.demo.entity.User">
        <id property="id" column="c_id" />
        <result property="name" column="c_name"/>
        <result property="code" column="c_code"/>
        <result property="phone" column="c_tel" />
        <result property="addr" column="c_address"/>
        <result property="idnumber" column="c_sfcard_no"/>
    </resultMap>

    <sql id="FIELDS">
        c_id, c_name, c_code, c_tel,c_address,c_sfcard_no
    </sql>

    <select id="selectById" parameterType="String" resultMap="userMap">
        SELECT
        <include refid="FIELDS" />
        FROM u_pat
        WHERE c_id = #{c_id}
    </select>

    <select id="selectByUsername" parameterType="String" resultMap="userMap">
        SELECT
        <include refid="FIELDS" />
        FROM u_pat
        WHERE c_name = #{c_name}
    </select>

    <select id="selectAll" resultMap="userMap">
        SELECT
        <include refid="FIELDS" />
        FROM u_pat
        where c_id = 'Y66739'
    </select>
</mapper>

mybatis-config.xml配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <!-- 使用驼峰命名法转换字段。 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer"/>
        <typeAlias alias="Long" type="java.lang.Long"/>
        <typeAlias alias="HashMap" type="java.util.HashMap"/>
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap"/>
        <typeAlias alias="ArrayList" type="java.util.ArrayList"/>
        <typeAlias alias="LinkedList" type="java.util.LinkedList"/>
    </typeAliases>

</configuration>

2.然后在启动类加上注解扫描：

@MapperScan("com.springbootmybatis.demo.mapper")

3.配置文件加上mybatis的配置：

 

# mybatis 配置内容
# 配置 MyBatis 配置文件路径
mybatis.config-location=classpath:mybatis-config.xml
# 配置 Mapper XML 地址
mybatis.mapper-locations=classpath:mapper/*.xml
# 配置数据库实体包路径
mybatis.type-aliases-package=com.springbootmybatis.demo.entity

 4.接下来就可以启动项目测试了。在浏览器访问：

 三、接下来配置多数据源：

第二步里面加的mybatis只有一个数据源，扫描内容也是在启动类用注解指定，现在配置多数据源。

1.首先增加mysql数据库连接配置，多数据源配置与单个数据源配置不同点在于，spring.datasource之后多了一个数据源名称primary/secondary用来区分不同的数据源；多数据源配置以后使用新的mybatis配置文件，就把第二步加mybatis时配置的内容删除（mybatis-config.xml配置文件删除、启动类加的@MapperScan("com.springbootmybatis.demo.mapper")也删除）。

此时的配置文件：

#tomcat配置
#端口
server.port=50001

#数据库配置
#数据源配置，包括ip，端口，数据库名等
spring.datasource.primary.jdbc-url=jdbc:oracle:thin:@//192.168.100.6:1521/rhipdb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
#用户密码
spring.datasource.primary.username=数据库账号
spring.datasource.primary.password=数据库密码
#数据库驱动（Driver）类名
spring.datasource.primary.driver-class-name=oracle.jdbc.driver.OracleDriver
#是否输出sql语句
spring.datasource.primary.jpa.show-sql=true
#连接池名称，一般等于这套数据库的配置名
spring.datasource.primary.pool-name=primary
#连接池最大连接数
spring.datasource.primary.maximum-pool-size=30
#向连接池获取连接的最大等待时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
spring.datasource.primary.connection-timeout=30000
#一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:10分钟，建议设置比数据库超时时长少30秒
spring.datasource.primary.max-lifetime=600000
#一个连接空闲状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
spring.datasource.primary.idle-timeout=600000
#自动提交。值为：true；false
spring.datasource.primary.auto-commit=true

spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/test
spring.datasource.secondary.username=root
spring.datasource.secondary.password=mysql123
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
#连接池名称，一般等于这套数据库的配置名
spring.datasource.secondary.pool-name=secondary
#连接池最大连接数
spring.datasource.secondary.maximum-pool-size=30
#向连接池获取连接的最大等待时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
spring.datasource.secondary.connection-timeout=30000
#一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒
spring.datasource.secondary.max-lifetime=1800000
#一个连接空闲状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
spring.datasource.secondary.idle-timeout=600000
#自动提交。值为：true；false
spring.datasource.secondary.auto-commit=true

增加配置文件：

package com.springbootmybatis.demo.config;

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 javax.sql.DataSource;

@Configuration
public class DataSourceConfiguration {
    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
}

 通过@ConfigurationProperties就可以知道这两个数据源分别加载了spring.datasource.primary.*和spring.datasource.secondary.*的配置。@Primary注解指定了主数据源，当不指定数据源时，就会使用该主数据源。

3.增加mybatis配置：

配置类上使用@MapperScan注解来指定当前数据源下定义的实体和mapper的包路径，还注入了sqlSessionFactory和sqlSessionTemplate，通过@Qualifier注解指定了对应的数据源，其名字对应在DataSourceConfiguration配置类中的数据源定义的函数名。

PrimaryConfig 代码：

package com.springbootmybatis.demo.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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan(
        basePackages = "com.springbootmybatis.demo.mapper.primaryMapper",
        sqlSessionFactoryRef = "sqlSessionFactoryPrimary",
        sqlSessionTemplateRef = "sqlSessionTemplatePrimary")
public class PrimaryConfig {
    private DataSource primaryDataSource;
    public PrimaryConfig(@Qualifier("primaryDataSource") DataSource primaryDataSource) {
        this.primaryDataSource = primaryDataSource;
    }
    @Bean
    public SqlSessionFactory sqlSessionFactoryPrimary() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(primaryDataSource);
        return bean.getObject();
    }
    @Bean
    public SqlSessionTemplate sqlSessionTemplatePrimary() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryPrimary());
    }
}

SecondaryConfig 代码：

package com.springbootmybatis.demo.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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan(
        basePackages = "com.springbootmybatis.demo.mapper.secondaryMapper",
        sqlSessionFactoryRef = "sqlSessionFactorySecondary",
        sqlSessionTemplateRef = "sqlSessionTemplateSecondary")
public class SecondaryConfig {
    private DataSource secondaryDataSource;
    public SecondaryConfig(@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
        this.secondaryDataSource = secondaryDataSource;
    }
    @Bean
    public SqlSessionFactory sqlSessionFactorySecondary() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(secondaryDataSource);
        return bean.getObject();
    }
    @Bean
    public SqlSessionTemplate sqlSessionTemplateSecondary() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactorySecondary());
    }
}

4.然后修改项目结构，创建文件夹路径，增加对新数据库的查询代码，如图：

 此时的UserController代码：

package com.springbootmybatis.demo.controller;

import com.springbootmybatis.demo.entity.MysqlUser;
import com.springbootmybatis.demo.entity.User;
import com.springbootmybatis.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/User")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("selectById")
    public User selectById(String c_id){
        return userService.selectById(c_id);
    }

    @GetMapping("selectByUsername")
    public User selectByUsername(String c_name){
        return userService.selectByUsername(c_name);
    }

    @GetMapping("selectAll")
    public List<User> selectAll(){
        List<User>  users = userService.selectAll();
        return users;
    }

    @GetMapping("selectByUsernameMysql")
    public MysqlUser selectByUsernameMysql(String name){
        return userService.selectByUsernameMysql(name);
    }

}

MysqlUser实体类代码（根据自己的表数据结构创建）：

package com.springbootmybatis.demo.entity;

import java.sql.Date;

public class MysqlUser {
    private int id;
    private String name;
    private Date appointmentTime;
    private int no;
    private int isPass;
    private String isAppointment;
    private Date operationTime;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getAppointmentTime() {
        return appointmentTime;
    }

    public void setAppointmentTime(Date appointmentTime) {
        this.appointmentTime = appointmentTime;
    }

    public int getNo() {
        return no;
    }

    public void setNo(int no) {
        this.no = no;
    }

    public int getIsPass() {
        return isPass;
    }

    public void setIsPass(int isPass) {
        this.isPass = isPass;
    }

    public String getIsAppointment() {
        return isAppointment;
    }

    public void setIsAppointment(String isAppointment) {
        this.isAppointment = isAppointment;
    }

    public Date getOperationTime() {
        return operationTime;
    }

    public void setOperationTime(Date operationTime) {
        this.operationTime = operationTime;
    }
}

此时的UserService代码：

package com.springbootmybatis.demo.service;


import com.springbootmybatis.demo.entity.MysqlUser;
import com.springbootmybatis.demo.entity.User;

import java.util.List;

public interface UserService {

    User selectById(String c_id);

    User selectByUsername(String c_name);

    List<User> selectAll();

    MysqlUser selectByUsernameMysql(String name);
}

此时的UserServiceImpl代码：

package com.springbootmybatis.demo.service.Impl;

import com.springbootmybatis.demo.entity.MysqlUser;
import com.springbootmybatis.demo.entity.User;
import com.springbootmybatis.demo.mapper.primaryMapper.UserMapper;
import com.springbootmybatis.demo.mapper.secondaryMapper.MysqlUserMapper;
import com.springbootmybatis.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    //调用UserMapper查询数据
    //@Resource或者@Autowired注解都可
    @Resource
    @Autowired
    private UserMapper userMapper;

    @Resource
    @Autowired
    private MysqlUserMapper mysqlUserMapper;

    @Override
    public User selectById(String c_id) {
        return userMapper.selectById(c_id);
    }

    @Override
    public User selectByUsername(String c_name) {
        return userMapper.selectByUsername(c_name);
    }

    @Override
    public List<User> selectAll() {
        List<User>  users = userMapper.selectAll();
        return users;
    }

    @Override
    public MysqlUser selectByUsernameMysql(String name) {
        return mysqlUserMapper.selectByUsername(name);
    }
}

MysqlUserMapper代码：

package com.springbootmybatis.demo.mapper.secondaryMapper;

import com.springbootmybatis.demo.entity.MysqlUser;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface MysqlUserMapper {

    MysqlUser selectById(@Param("id") String id);

    MysqlUser selectByUsername(@Param("name") String name);

    List<MysqlUser> selectAll();
}

MysqlUserMapper.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.springbootmybatis.demo.mapper.secondaryMapper.MysqlUserMapper">

    <resultMap id="mysqlUserMap" type="com.springbootmybatis.demo.entity.MysqlUser">
        <id property="id" column="id" />
        <result property="name" column="name"/>
        <result property="appointmentTime" column="appointment_time"/>
        <result property="no" column="no" />
        <result property="isPass" column="is_pass"/>
        <result property="isAppointment" column="is_appointment"/>
        <result property="operationTime" column="operation_time"/>
    </resultMap>

    <sql id="FIELDS">
        id, name, appointment_time, no,is_pass,is_appointment,operation_time
    </sql>

    <select id="selectById" parameterType="String" resultMap="mysqlUserMap">
        SELECT
        <include refid="FIELDS" />
        FROM test_queue
        WHERE id = #{id}
    </select>

    <select id="selectByUsername" parameterType="String" resultMap="mysqlUserMap">
        SELECT
        <include refid="FIELDS" />
        FROM test_queue
        WHERE name = #{name}
    </select>
    <select id="selectAll" resultMap="mysqlUserMap">
        SELECT
        <include refid="FIELDS" />
        FROM test_queue
    </select>
</mapper>

5.然后就可以运行测试了：

请求第二步的接口：

请求新数据源的接口：

 说明搭建成功。

6.注意事项

在创建文件夹路径和移动文件时，最好是到文件夹里去操作，因为在IDEA里面有时候看到的文件夹路径和你想的不一样，比如以下两种路径在IDEA里看是一样的

然后在移动原来的文件时，注意包的路径变化。

如果访问接口查询时报错，绑定不到数据源：

 基本上就是路径的问题了。本文也是从网上查文章资料整合而来，方便自己记录学习，mapper的路径不一定按我的来，理解了配置思路以后是可以灵活修改的。