Spring Boot Jpa 配置多个数据源，并读取其中一个表的具体数据

我和ye猫子

已于 2022-08-04 11:14:08 修改

阅读量5k

点赞数 1

分类专栏： jpa Java-数据库文章标签： spring boot 后端 java

于 2022-01-18 15:31:33 首次发布

本文链接：https://blog.csdn.net/u011441473/article/details/122560211

版权

Java-数据库同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

jpa

3 篇文章 0 订阅

订阅专栏

总体简介：

Spring Boot Jpa配置多个数据源（此次两个mysql数据库），访问其中一个库

alime_counsel_assign_log下的assign_data_backflow表，实现查询某个id的数据

一、整体项目目录：

补充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.5.6</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.mock</groupId>
	<artifactId>mockStrategyCenter</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>mockStrategyCenter</name>
	<description>mockStrategyCenter</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.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<!--日主输出模块通常spring-boot自带依赖不需要手动添加-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-logging</artifactId>
		</dependency>

		<!--JPA模块，支持Spring实现的上层操作，默认包含JDBC模块   数据持久化数据库访问jar包-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<!--mysql模块，主要包含连接数据库的驱动-->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<!--Lombok是用来简化Java代码的编写，那么就需要IDE和编译器（Maven）支持Lombok,因此ideal需要安装插件，maven和jdk需要满足你一定的版本 -->
		<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.12</version>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>

		<!--<dependency>
			<groupId>org.jacoco</groupId>
			<artifactId>jacoco-maven-plugin</artifactId>
			<version>0.8.3</version>
		</dependency>-->

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>

			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-surefire-plugin</artifactId>
				<version>2.5</version>
				<configuration>
				<includes>
					<include>**/*Test.java</include>
					<include>**/*Tests.java</include>
				</includes>
				</configuration>
			</plugin>

			<!-- Jacoco插件用来生成代码覆盖率-->
			<plugin>
				<groupId>org.jacoco</groupId>
				<artifactId>jacoco-maven-plugin</artifactId>
				<version>0.8.3</version>
				<configuration>
					<skip>false</skip>
				</configuration>
				<executions>
					<execution>
						<id>pre-test</id>
						<goals>
							<goal>prepare-agent</goal>
						</goals>
					</execution>
					<execution>
						<id>post-test</id>
						<phase>test</phase>
						<goals>
							<goal>report</goal>
						</goals>

						<configuration>
							<!-- 指定从哪里对其jacoco的exec文件，默认路径为jacoco.exec-->
							<!--<dataFile>target/jacoco.exec</dataFile>-->
							<!-- 指定dataFile路径中文件被解析后输出地址-->
							<outputDirectory>target/${project.artifactId}/jacoco-ut</outputDirectory>
						</configuration>
					</execution>
				</executions>
			</plugin>

		</plugins>
	</build>

</project>

二、数据库配置及数据源初始化

1）、main/resources目录下application.yml配置文件配置数据库相关信息：

定义了项目的各种配置文件，包括多个数据源的链接信息

#1.内容格式比较： .properties文件，通过.来连接，通过=来赋值，结构上，没有分层的感觉，但比较直接。 .yml文件，通过：来分层，结构上，有比较明显的层次感，最后key赋值的：后需要留一个空格
#.执行顺序 如果工程中同时存在application.properties文件和 application.yml文件，yml文件会先加载，而后加载的properties文件会覆盖yml文件。所以建议工程中，只使用其中一种类型的文件即可。
#使用jdbc-url属性代替之前配置中的url属性 否则会报错使用jdbc-url属性代替之前配置中的url属性
server:
  port: 8080
  #编码格式
  tomcat:
    uri-encoding: utf-8

#数据库相关配置
# 多数据源配置
#primary
spring:
  #应用名称
  application:
    name: mock-strategy-center
  datasource:
    primary:
      jdbc-url: jdbc:mysql://11.164.61.168:3306/alime_counsel_assign_log
      username: alibbpoc
      password: alibbpoc
      driver-class-name: com.mysql.cj.jdbc.Driver

    #secondary
    secondary:
      jdbc-url: jdbc:mysql://11.164.61.168:3306/alime_counsel
      username: alibbpoc
      password: alibbpoc
      driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    hibernate:
      primary-dialect: org.hibernate.dialect.MySQL57Dialect
      secondary-dialect: org.hibernate.dialect.MySQL57Dialect
      hbm2ddl: update
    open-in-view: true
    show-sql: true

2）、读取多数据源配置的几个类DataSourceConfig、PrimaryConfig、SecondaryConfig

DataSourceConfig类，从application.yml配置文件中读取主数据源和第二数据源的链接信息PrimaryConfig类和SecondaryConfig 指定jpa接口的Repository，实体entity，读取到的数据源配置从哪里取

DataSourceConfig类

package com.mock.mockstrategycenter.dataSource;

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

@Configuration
public class DataSourceConfig {

    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

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

}

PrimaryConfig

package com.mock.mockstrategycenter.dataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.mock.mockstrategycenter.repository.alimeCounselAssignLogRepository"}//设置Repository所在位置
)
public class PrimaryConfig {

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Resource
    private Properties jpaProperties;

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean entityManagerFactory = builder
                .dataSource(primaryDataSource)
                .packages("com.mock.mockstrategycenter.entity.alimeCounselAssignLogEntity") //设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .build();
        entityManagerFactory.setJpaProperties(jpaProperties);
        return entityManagerFactory;
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

SecondaryConfig

package com.mock.mockstrategycenter.dataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= { "com.mock.mockstrategycenter.repository.alimeCounselRepository" }) //设置Repository所在位置
public class SecondaryConfig {

    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    @Resource
    private Properties jpaProperties;

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean entityManagerFactory = builder
                .dataSource(secondaryDataSource)
                .packages("com.mock.mockstrategycenter.entity.alimeCounselEntity") //设置实体类所在位置
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
        entityManagerFactory.setJpaProperties(jpaProperties);
        return entityManagerFactory;
    }


    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }

}

三、数据库访问实现相关：

1）、AssignDataBackflow实体类，定义表assign_data_backflow的字段

package com.mock.mockstrategycenter.entity.alimeCounselAssignLogEntity;


import lombok.Data;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "assign_data_backflow")
@Data
public class AssignDataBackflow {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name ="gmt_create")
    private Date gmt_create;

    @Column(name="tenant_id")
    private Integer tenant_id;

    @Column(name="session_id")
    private String session_id;

    //@Column(name="content") 符合命名格式可以不用加Column备注
    private String content;

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


    public Integer getId() {
        return id;
    }
}

2）、AssignDataBackflowRepository接口

package com.mock.mockstrategycenter.repository.alimeCounselAssignLogRepository;

import com.mock.mockstrategycenter.entity.alimeCounselAssignLogEntity.AssignDataBackflow;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 *
 * @Query
 * @Modifying 等注释可以实现自定义语句
 */
public interface AssignDataBackflowRepository extends JpaRepository<AssignDataBackflow,Integer> {

    /*@Modifying
    @Query(value = "INSERT INTO `alime_counsel_assign_log`.`assign_data_backflow`(`tenant_id`, `session_id`, `content`) VALUES (:tenant_id, :session_id, '|n|r|99|62|84||34|92|18|9|106.5|h|47|h|z|11|91||e|36|e|s||g|||f||')", nativeQuery=true)
    void insert(@Param("tenant_id")Integer tenantId,@Param("session_id")String sessionId);*/

}

3）、service层封装业务逻辑访问数据库ModeTrainingService

ModeTrainingService：

package com.mock.mockstrategycenter.service;

import com.mock.mockstrategycenter.repository.alimeCounselAssignLogRepository.AssignDataBackflowRepository;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service
public class ModeTrainingService {
    // 注入数据访问层接口对象
    @Resource
    private AssignDataBackflowRepository assignDataBackflowRepository;

    public String findData(){
         Object  data=assignDataBackflowRepository.findById(new Integer(2008));
         return data.toString();
    }
}

通过MockHttpResponse将接口暴露

具体源码：

package com.mock.mockstrategycenter.controller;

import com.mock.mockstrategycenter.service.ModeTrainingService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
public class MockHttpResponse {
    @Autowired
    ModeTrainingService modeTrainingService;
    @RequestMapping(value = "/",produces="text/html;charset=UTF-8",method= RequestMethod.GET)
    @ResponseBody
    public String helloWord(@RequestParam Integer id){
        //访问执行时间
        long beforeTime = System.currentTimeMillis();
       String ob= modeTrainingService.findData();
        long afterTime = System.currentTimeMillis();

        return "Hello"+id+"query ["+ob +"] use time: "+(afterTime-beforeTime);
    }

    @RequestMapping(value = "/value",produces="application/json;charset=UTF-8",method= RequestMethod.GET)
    @ResponseBody
    public String dataValue(@RequestParam Integer id){
        String name="test"+id;
        return "{\"name\":\""+name+"\"}";
    }

    /**
     * 传入什么返回什么
     * @param thing
     * @return
     */
    @RequestMapping(value = "/youSelf",produces="application/json;charset=UTF-8",method= RequestMethod.GET)
    @ResponseBody
    public String dataValue(@RequestParam String thing){
        return "{\"value\":\""+thing+"\"}";
    }
}