SpringBoot 多数据源配置


首先 mybatis generator 是支持多数据源生成 dao 层的代码的(也就是那些 mapper.xml文件、实体类文件等等),我们需要针对我们不同数据源里面的表,分别进行配置,才能生成对应库表的 dao 层代码。要知道 mybatis generator 的配置文件里面是可以配置多个context的,一个context就是一个数据源。

我们先知道怎么配置单一数据源,下面是最简单的方式了,我们单一数据源的时候可以直接按照下面的写法在 配置 文件里面配置

#spring mysql
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/demo?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=
#mybatis
mybatis.type-aliases-package=com.server.entity
mybatis.mapper-locations=classpath:/mapper/**.xml

上面基本就够了。

知道了单一数据源怎么配置,接下来就是开始配置多数据源了。一般我们不推荐在一个项目中访问多个数据源,因为一般一个项目独占一个数据库,除非有一个数据源是只读的可能我们为了取数据方便回允许使用多数据源。

spring.datasource.demo.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.demo.jdbcUrl=jdbc:mysql://localhost:3306/demo?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.demo.password=root
spring.datasource.demo.username=
spring.datasource.study.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.study.jdbcUrl=jdbc:mysql://localhost:3306/study?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.study.password=root
spring.datasource.study.username=

上面虽然看上去是一样的,注意一个是demo表一个是study表。我们需要取名称。另外注意相对于单一数据库. jdbcUrl 那一行的配置不一样了。如果按照但数据库的配置名字去配置,大概率会报下面的错误

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'h2Console' defined in class path resource [org/springframework/boot/autoconfigure/h2/H2ConsoleAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.boot.web.servlet.ServletRegistrationBean]: Factory method 'h2Console' threw exception; nested exception is java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
 at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:656)
 at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:636)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
 at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:207)
 at org.springframework.boot.web.servlet.ServletContextInitializerBeans.getOrderedBeansOfType(ServletContextInitializerBeans.java:211)
 at org.springframework.boot.web.servlet.ServletContextInitializerBeans.getOrderedBeansOfType(ServletContextInitializerBeans.java:202)
 at org.springframework.boot.web.servlet.ServletContextInitializerBeans.addServletContextInitializerBeans(ServletContextInitializerBeans.java:96)
 at org.springframework.boot.web.servlet.ServletContextInitializerBeans.<init>(ServletContextInitializerBeans.java:85)
 at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.getServletContextInitializerBeans(ServletWebServerApplicationContext.java:253)
 at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.selfInitialize(ServletWebServerApplicationContext.java:227)
 at org.springframework.boot.web.embedded.tomcat.TomcatStarter.onStartup(TomcatStarter.java:53)
 at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5135)
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
 at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)
 at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
 at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
 at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909)
 at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:841)
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
 at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)
 at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
 at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
 at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909)
 at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:262)
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
 at org.apache.catalina.core.StandardService.startInternal(StandardService.java:421)
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
 at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:930)
 at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
 at org.apache.catalina.startup.Tomcat.start(Tomcat.java:459)
 at org.springframework.boot.web.embedded.tomcat.TomcatWebServer.initialize(TomcatWebServer.java:107)
 ... 18 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.boot.web.servlet.ServletRegistrationBean]: Factory method 'h2Console' threw exception; nested exception is java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
 at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185)
 at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:651)
 ... 58 common frames omitted
Caused by: java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
 at com.zaxxer.hikari.HikariConfig.validate(HikariConfig.java:954)
 at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:109)
 at org.springframework.boot.autoconfigure.h2.H2ConsoleAutoConfiguration.lambda$h2Console$0(H2ConsoleAutoConfiguration.java:73)
 at org.springframework.beans.factory.ObjectProvider.ifAvailable(ObjectProvider.java:93)
 at org.springframework.boot.autoconfigure.h2.H2ConsoleAutoConfiguration.h2Console(H2ConsoleAutoConfiguration.java:72)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154)
 ... 59 common frames omitted

上面那个报错,只需要填写 jdbcUrl 就行了,别写错了。

接下来我们还需要在代码里面指定用哪个mapper的时候该访问哪个数据源。比如 demoMapper 就读写 demo 库 里面的 某个 表。如果用 studyMapper 的时候就用 访问 study 库里面的某个表。

自动生成dao层代码以及配置类目录结构大致如下

├── config
│   ├── datasource
│   │   ├── Datasource1Config.java
│   │   └── Datasource2Config.java
├── controller
│   ├── DemoController.java
│   └── LoginController.java
├── dal
│   ├── mapper
│   │   ├── demo
│   │   │   └── AccountMapper.java
│   │   └── study
│   │       └── ChangeLogMapper.java
│   └── model
│       ├── demo
│       │   ├── Account.java
│       │   └── AccountExample.java
│       └── study
│           ├── ChangeLog.java
│           └── ChangeLogExample.java

其中 Datasource1Config 大致长下面这个样子

@Configuration
@MapperScan(basePackages = "com.server.dal.mapper.demo", sqlSessionFactoryRef = "datasource1SqlSessionFactory")
public class Datasource1Config {
    @Bean
    @Primary
    @Qualifier("datasource1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.demo")
    public DataSource datasource1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean
    public SqlSessionFactory datasource1SqlSessionFactory(@Qualifier("datasource1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/demo/*.xml"));
        return sessionFactory.getObject();
    }
}

Datasource2Config 其实也类似

@Configuration
@MapperScan(basePackages = "com.server.dal.mapper.study", sqlSessionFactoryRef = "datasource2SqlSessionFactory")
public class Datasource2Config {
    @Bean
    @Qualifier("datasource2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.study")
    public DataSource datasource2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public SqlSessionFactory datasource2SqlSessionFactory(@Qualifier("datasource2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/study/*.xml"));
        return sessionFactory.getObject();
    }
}

主要是一个注解 @Primary. 如果没有这个注解,就会包下面这个错误

Description:

Method h2Console in org.springframework.boot.autoconfigure.h2.H2ConsoleAutoConfiguration required a single bean, but 2 were found:
 - datasource1DataSource: defined by method 'datasource1DataSource' in class path resource [com/server/config/datasource/Datasource1Config.class]
 - datasource2DataSource: defined by method 'datasource2DataSource' in class path resource [com/server/config/datasource/Datasource2Config.class]


Action:

Consider marking one of the beans as @Primary, updating the consumer to accept multiple beans, or using @Qualifier to identify the bean that should be consumed

还要注意,加上下面这一句

sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/study/*.xml"));

因为如果不加,可能会报下面这个错误

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.server.dal.mapper.demo.AccountMapper.insertSelective
 at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:235)
 at org.apache.ibatis.binding.MapperMethod.<init>(MapperMethod.java:53)
 at org.apache.ibatis.binding.MapperProxy.lambda$cachedInvoker$0(MapperProxy.java:108)
 at java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1660)
 at org.apache.ibatis.util.MapUtil.computeIfAbsent(MapUtil.java:35)
 at org.apache.ibatis.binding.MapperProxy.cachedInvoker(MapperProxy.java:95)
 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
 at com.sun.proxy.$Proxy113.insertSelective(Unknown Source)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
 at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
 at com.sun.proxy.$Proxy114.insertSelective(Unknown Source)

原因是 mapper 不知道该绑定到哪个 xml 文件了.


评论