20分钟搞定 SpringBoot+Mybatis+多数据源

最新推荐文章于 2024-03-14 15:31:56 发布

涉世未深果子狸

最新推荐文章于 2024-03-14 15:31:56 发布

阅读量1.3k

点赞数 1

分类专栏：笔记整理文章标签： spring boot java 后端数据库

本文链接：https://blog.csdn.net/m0_56726104/article/details/125097085

版权

笔记整理专栏收录该内容

10 篇文章 3 订阅

订阅专栏

前言：

这篇博客是希望帮助您在20分钟左右快速集成多数据源，台上一分钟，台下60秒，如果没能帮到您，不喜勿喷。

业务背景：

项目中有个需求，需要在Oracle中查询数据，利用查询出来的数据作为触发参数去执行一段业务流程，这段业务流程执行完毕之后，将业务返回来的数据存储进Mysql中，这个需求就涉及到两个数据源。

解决办法有很多种，比如可以单独写一个脚本先将Oracle的数据导进Mysql等等。不过我使用的是在一个SpringBoot服务中加入两个数据源，这样更简单，也避免了不必要的数据冗余。

环境搭建：

pom:

说明：

1.我使用的是MP（MybatisPlus）, MP的核心思想是在Mybatis的基础上只做增强，不做改变，所以您如果使用的是Mybatis并不影响。

2. ojdbc6（oracle连接驱动包）添加到pom之后，会拉不下来包，可以参考这篇博客手动打包。maven添加Oracle 依赖，将Oracle的依赖jar包打成依赖引入到maven_wx5fa2b09c753b2的技术博客_51CTO博客

        <!-- SpringBoot 2.6.1 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.6.1</version>
        </dependency>

        <!-- oracle连接驱动 -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </dependency>
    
        <!-- MP -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>

        <!-- MySql连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

yml:

spring:
  # 数据源配置
  datasource:
    # 主数据源（mysql）
    db1:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/fs_bank?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password: ********

    # 副数据源（Oracle）
    db2:
      driver-class-name: oracle.jdbc.driver.OracleDriver
      jdbc-url: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:orcl
      username: s03
      password: ********

代码实现：

DataSourceConfiguration：

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 {

    @Bean("primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    @Primary
    public DataSource primaryDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean("secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource secondaryDataSource(){
        return DataSourceBuilder.create().build();
    }


}

PrimaryConfig：

import com.baomidou.mybatisplus.autoconfigure.SpringBootVFS;
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 org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;


@Configuration
@MapperScan(
        basePackages = "com.xxx.xxx.xxx.mapper.db1",
        sqlSessionFactoryRef = "sqlSessionFactoryPrimary",
        sqlSessionTemplateRef ="sqlSessionTemplatePrimary"
)
public class PrimaryConfig {

    private final DataSource primaryDataSource;

    public PrimaryConfig(@Qualifier("primaryDataSource")DataSource primaryDataSource){
        this.primaryDataSource = primaryDataSource;
    }

    @Bean("sqlSessionFactoryPrimary")
    @Primary
    public SqlSessionFactory sqlSessionFactoryPrimary() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(primaryDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db1/*.xml"));
        bean.setVfs(SpringBootVFS.class);
        bean.setTypeAliasesPackage("com/xxx/xxx/xxx/domain/entity/db1");
        return bean.getObject();
    }

    @Bean("sqlSessionTemplatePrimary")
    @Primary
    public SqlSessionTemplate sqlSessionTemplatePrimary() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryPrimary());
    }
}

SecondaryConfig:

import com.baomidou.mybatisplus.autoconfigure.SpringBootVFS;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;


@Configuration
@MapperScan(
        basePackages = "com.xxx.xxx.xxx.mapper.db2",
        sqlSessionFactoryRef = "sessionFactorySecondary",
        sqlSessionTemplateRef = "sqlSessionTemplateSecondary"
)
public class SecondaryConfig {
    private final DataSource secondaryDataSource;

    public SecondaryConfig(@Qualifier("secondaryDataSource")DataSource secondaryDataSource){
        this.secondaryDataSource = secondaryDataSource;
    }

    @Bean("sessionFactorySecondary")
    public SqlSessionFactory sessionFactorySecondary() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(secondaryDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db2/*.xml"));
        bean.setVfs(SpringBootVFS.class);
        bean.setTypeAliasesPackage("com/xxx/xxx/xxx/domain/entity/db2");
        return bean.getObject();
    }

    @Bean("sqlSessionTemplateSecondary")
    public SqlSessionTemplate sqlSessionTemplateSecondary() throws Exception {
        return new SqlSessionTemplate(sessionFactorySecondary());
    }
}

到此，SpringBoot的多数据源就配置完成了，然后实体类，XXXMapper接口，XXXMapper.xml文件等等按照平时的开发流程，配置到相应的位置就可以。

这里贴一套DB2（Oralce）数据源的，DB1（Mysql）也是一样的逻辑，配置好后，我们正常调用Mapper中的接口就行。

实体类：

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.util.Date;


@Data
@EqualsAndHashCode(callSuper = false)
public class DfcPunishSheetInfo implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 数据库自增序列
     */
    @TableId("GUID")
    private Long guid;

    /**
     * 处罚决定书编号
     */
    @TableField("PUNISHSHEETNO")
    private String punishSheetNo;

    /**
     * 处理日期
     */
    @TableField("DEALDATE")
    private Date dealDate;

    /**
     * 缴款日期
     */
    @TableField("PAYDATE")
    private Date payDate;
}

Mapper：


import com.xxx.xxx.xxx.domain.entity.db2.DfcPunishSheetInfo;
import org.apache.ibatis.annotations.Mapper;


@Mapper
public interface DfcPunishSheetInfoMapper {
    DfcPunishSheetInfo selectByGuid(Long guid);
}

Mapper.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.xxx.xxx.xxx.mapper.db2.DfcPunishSheetInfoMapper">

    <!-- 开启二级缓存 -->
    <!--<cache type="org.mybatis.caches.ehcache.LoggingEhcache"/>-->

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.xxx.xxx.xxx.domain.entity.db2.DfcPunishSheetInfo">
        <id column="GUID" property="guid"/>
        <result column="PUNISHSHEETNO" property="punishSheetNo"/>
        <result column="DEALDATE" property="dealDate"/>
        <result column="PAYDATE" property="payDate"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        GUID, PUNISHSHEETNO, DEALDATE, PAYDATE
    </sql>

    <select id="selectByGuid" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from DSF_PUNISHSHEETINFO where GUID = #{guid}
    </select>



</mapper>

测试类：

import com.xxx.xxx.xxx.domain.entity.db2.DfcPunishSheetInfo;
import com.xxx.xxx.xxx.swap.util.JacksonUtil;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class DfcPunishSheetInfoMapperTest {
    @Autowired
    private DfcPunishSheetInfoMapper dfcPunishSheetInfoMapper;
    @Test
    void test01(){
        DfcPunishSheetInfo info = dfcPunishSheetInfoMapper.selectByGuid(544957876L);
        System.out.println(JacksonUtil.writeValueToString(info));
    }

}

运行结果：

{
    "guid":544957876,
    "punishSheetNo":"652325100184036",
    "dealDate":1422934800000,
    "payDate":null
}

如果运行过程出现以下错误：

java.lang.AbstractMethodError: Method oracle/jdbc/driver/OracleResultSetImpl.getObject(Ljava/lang/String;Ljava/lang/Class;)Ljava/lang/Object; is abstract
 
	at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java)
	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.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:69)
	at com.sun.proxy.$Proxy93.getObject(Unknown Source)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:38)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:28)
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:81)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:521)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:402)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:67)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
	at com.sun.proxy.$Proxy71.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:158)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:76)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61)
	at com.sun.proxy.$Proxy74.selectList(Unknown Source)
	at com.hzsh.module.user.RetrieveTest.selectByWrapper8(RetrieveTest.java:176)
	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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

网上都说oracle 驱动版本不对，都是扯淡的，我们还是要追源码解决！

问题分析，因为新功能后来加的，新建的实体类，其他的查询映射都是OK的，所以依赖包是没问题的。

关键看报错位置：Method oracle/jdbc/driver/OracleResultSetImpl.getObject(Ljava/lang/String;Ljava/lang/Class;)Ljava/lang/Object; is abstract

追源码过程不贴了，最后发现：

oracle 表中字段是DATE类型，实体类用的LocalDateTime 在做字段映射时出现异常，

解决办法：

修改实体类LocalDateTime类型为Date类型