搭建oracle+mysql双数据源springboot+vue项目配置记录

搭建oracle+mysql双数据源springboot+vue项目配置记录

主要根据项目需要自行搭建简易双数据源框架，以便自行学习实践相关离线开发，话不多说直接开干

1.首先准备所需依赖资源：

springboot框架代码结构（搭建项目略）；
oracle数据库（建库建表过程略）；
mysql数据库（建库建表过程略）；
vue前端（element Admin demo项目直接用即可，前端部分不做记录与介绍）；

2.项目结构截图

3.编辑配置后端pom文件中必须依赖，具体版本可以根据自己的项目调整，其他基础依赖略

		<!-- ↑↑↑↑↑↑↑↑↑↑↑... 依赖省略 ...↑↑↑↑↑↑↑↑↑↑↑ --><!-- ↓↓↓↓↓↓↓↓↓↓ mysql 依赖 ↓↓↓↓↓↓↓↓↓↓--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</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><!-- ↑↑↑↑↑↑↑↑↑↑↑ mysql 依赖 ↑↑↑↑↑↑↑↑↑↑↑--><!--oracle--><dependency><groupId&acle</groupId><artifactId>ojdbc6</artifactId><version>11.2.0.4.0</version></dependency><!-- ↓↓↓↓↓↓↓↓↓↓... 依赖省略 ...↓↓↓↓↓↓↓↓↓↓ -->

4.添加数据源配置文件，配置文件个数根据自己的项目自行添加，每新增一个数据源新增一个配置文件，案例两个数据源因此只有两个配置文件，配置文件内容基本一样根据模板自己改造即可

两个文件分别为：SysDataSourceConfig.java 、YbmsDataSourceConfig.java

1.配置默认系统主数据源文件SysDataSourceConfig.java

package com.jmy.datasource;import org.apache.ibatis.session.SqlSessionFactory;
batis.spring.SqlSessionFactoryBean;
batis.spring.SqlSessionTemplate;
batis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.t.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import t.annotation.Bean;
import t.annotation.Configuration;
import t.annotation.Primary;
import io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** @ProjectName: demo-vue-backend* @Package: com.jmy.datasource* @ClassName: SysDataSourceConfig* @Author: sparkle* @Description: 系统默认数mysql据源配置文件* @Date: 2022/12/15 15:06* @Version: 1.0.0*/@Configuration
@MapperScan(basePackages = "sql.*.*.mapper", sqlSessionTemplateRef = "sysSqlSessionTemplate")
public class SysDataSourceConfig {@Bean@ConfigurationProperties(prefix = "spring.datasource.sysdb")@Primarypublic DataSource sysDataSource() {ate().build();}@Bean@Primarypublic SqlSessionFactory sysSqlSessionFactory(@Qualifier("sysDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/mysql/*/*l"));Object();}@Bean@Primarypublic DataSourceTransactionManager sysTransactionManager(@Qualifier("sysDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Bean@Primarypublic SqlSessionTemplate sysSqlSessionTemplate(@Qualifier("sysSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate(sqlSessionFactory);}
}

2.业务数源配置文件YbmsDataSourceConfig.java

package com.jmy.datasource;import org.apache.ibatis.session.SqlSessionFactory;
batis.spring.SqlSessionFactoryBean;
batis.spring.SqlSessionTemplate;
batis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.t.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import t.annotation.Bean;
import t.annotation.Configuration;
import io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** @ProjectName: demo-vue-backend* @Package: com.jmy.datasource* @ClassName: YbmsDataSourceConfig* @Author: sparkle* @Description: 业务数据源Oracle配置文件* @Date: 2022/12/15 15:06* @Version: 1.0.0*/@Configuration
@MapperScan(basePackages = "acle.*.*.mapper", sqlSessionTemplateRef = "ybmsSqlSessionTemplate")
public class YbmsDataSourceConfig {@Bean@ConfigurationProperties(prefix = "spring.datasource.ybmsdb")public DataSource ybmsDataSource() {ate().build();}@Beanpublic SqlSessionFactory ybmsSqlSessionFactory(@Qualifier("ybmsDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/oracle/*/*l"));Object();}@Beanpublic DataSourceTransactionManager ybmsTransactionManager(@Qualifier("ybmsDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Beanpublic SqlSessionTemplate ybmsSqlSessionTemplate(@Qualifier("ybmsSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate(sqlSessionFactory);}
}

注意：

	一般系统添加如上两个配置文件后再配置好数据库配置即可，项目其他部分一般无需调整，若项目较为复杂根据实际情况自行调整即可，其中需要修改的点分别为：1.basePackages 指定基础包位置2.sqlSessionTemplateRef 指定需要访问的sql模板3.prefix 指定数据源配置信息4.getResources 指定扫描获取数据源sql文件位置5.若项目启动类中已添加扫描sql配置文件的位置，注释掉即可根据项目实际运行情况自行调整，案例如图所示：

5.java案例代码部分

	由于Oracle与mysql代码部分基本一样，因此只列举mysql代码部分，Oracle代码部分略过（dao层引用数据源时无需指定数据源）

5.1数据查询传输类AnnounceQuery （相当于VO）

package sql.demo.announce.query;import lombok.Data;/*** @Description:* @company: jmy Software Co.* @author: fengwen* @date: 2021/9/29 16:20*/
@Data
public class AnnounceQuery {private Integer imortance;private String title;private String type;private String sort;}

ller部分

package sql.ller;import com.jmymon.JSON;
import sql.ity.Announce;
import sql.demo.announce.query.AnnounceQuery;
import sql.demo.announce.service.AnnounceService;
import bms.dto.YbmsmxDto;
import ity.Ybmsmx;
import bms.service.YbmsmxService;
slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.List;/*** @Description: 信息发布功能模块* @company: jmy Software Co.* @author: sparkle* @date: 2021/9/29 15:58*/
@Slf4j
@CrossOrigin
@RestController
@RequestMapping("/announce")
public class AnnounceController {@Autowiredprivate AnnounceService announceService ;@Autowiredprivate YbmsmxService ybmsmxService ;@RequestMapping("/list")public void getList(AnnounceQuery announceQuery){String jsonStr = JSONSring(announceQuery);log.info("============announceQuery={}", jsonStr);int count = Count(announceQuery);List<Announce> list = List(announceQuery);System.out.println("mysql数据库访问："+list.size());log.info("list============list={}", list);// 为了便于测试可以再次出调用访问Oracle数据源// List<Ybmsmx> list2 = List(new YbmsmxDto());// System.out.println("oracle数据库访问："+list2.size());}
}

5.3service接口层

package sql.demo.announce.service;import sql.ity.Announce;
import sql.demo.announce.query.AnnounceQuery;import java.util.List;/*** @ProjectName: demo-vue-backend* @Package: sql.announce.demo.service* @ClassName: AnnouceService* @Author: sparkle* @Description: demo接口* @Date: 2022/12/15 16:22* @Version: 1.0.0*/public interface AnnounceService {List<Announce> getList(AnnounceQuery announceQuery);int getCount(AnnounceQuery announceQuery);
}

5.4业务实现类AnnounceServiceImpl.java

package sql.demo.announce.service.impl;import sql.ity.Announce;
import sql.demo.announce.mapper.AnnounceMapper;
import sql.demo.announce.query.AnnounceQuery;
import sql.demo.announce.service.AnnounceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;/*** @ProjectName: demo-vue-backend* @ClassName: AnnounceServiceImpl* @Author: sparkle* @Description: 业务实现类* @Date: 2022/12/15 16:28* @Version: 1.0.0*/@Service
public class AnnounceServiceImpl implements AnnounceService {@Autowiredprivate AnnounceMapper announceMapper ;@Overridepublic List<Announce> getList(AnnounceQuery announceQuery) {List(announceQuery);}@Overridepublic int getCount(AnnounceQuery announceQuery) {System.out.println("aaaabbbb"+announceQuery);Count(announceQuery);}
}

5.5 mapper层

package sql.demo.announce.mapper;import sql.demo.announce.query.AnnounceQuery;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;import java.util.List;/*** @Description:* @company: jmy Software Co.* @author: sparkle* @date: 2021/9/29 17:30*/
@Repository
public interface AnnounceMapper {/*** @param announceQuery 参数* @return 返回*/int getCount(AnnounceQuery announceQuery);List<Announce> getList(AnnounceQuery announceQuery);}

5.6 resource文件中的mapping配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis//DTD Mapper 3.0//EN"".dtd">
<mapper namespace="sql.demo.announce.mapper.AnnounceMapper"><select id="getCount" parameterType="sql.demo.announce.query.AnnounceQuery" resultType="java.lang.Integer">select count(1) from t_announce</select><select id="getList" parameterType="sql.demo.announce.query.AnnounceQuery" resultType="sql.ity.Announce">select * from t_announce limit #{startPage}, #{limit}</select></mapper>

注意事项：

	由于数据源不同因此Oracle与mysql的配置文件必须按目录分开，因为在配置类中已经指定了需要访问的数据源配置，如下图所示：

spring:datasource:# 系统默认的mysql数据源，数据库版本：5.7.20, for Win64 (x86_64)sysdb:jdbc-url: ${MYSQL_URL:jdbc:mysql://localhost:3306/vue_db?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8}driver-class-name: sql.cj.jdbc.Driverusername: testmskpassword: 123456# 业务系统oracle数据库，数据库版本：TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Productionybmsdb:jdbc-url:  jdbc:oracle:thin:@localhost:1521/orcldriver-class-name:  oracle.jdbc.driver.OracleDriverusername: testorkpassword: 123123