【多数据源系列】基于SpringCloud实现PostgreSQL外部表读取（附源码）

已于 2023-12-22 20:13:37 修改
阅读量1.4k 收藏 19
点赞数 22
CC 4.0 BY-SA版权
分类专栏： JAVA 文章标签： spring cloud mybatis spring
于 2023-12-22 20:09:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/c18213590220/article/details/135080990

本文将介绍PostgreSQL外部表的核心概念并基于SpringCloud实现外部表数据读取。

  • 如文章中有明显错误或者用词不当的地方，欢迎大家在评论区批评指正，我看到后会及时修改。

  • 如想要和博主进行技术栈方面的讨论和交流可私信我。

1. 前言

        在微服务业务中，当需要获取别的业务模块的数据时，我们通常采用远程调用的形式，但远程调用一多，在拉高系统耦合度的同时，系统运维难度也在提升。其数据调用场景如下图所示：

  由于我做的系统都是基于Postgres数据库的，在机缘巧合下我发现了外部表这个好东西。相比远程调用，外部表的优势如下：

  1. 性能和响应时间： 通过在微服务中使用外部表，可以在本地数据库中存储和缓存数据，从而减少远程调用的需求。本地数据的访问通常比远程调用更快，因此可以提高服务的性能和响应时间。

  2. 降低系统耦合度： 使用外部表可以降低微服务之间的直接依赖性。远程调用可能使得一个微服务依赖于另一个微服务的具体实现，而使用外部表，微服务只需关心自己本地的数据即可，降低了系统的耦合度。

  3. 减少网络开销： 远程调用会引入网络开销，而本地数据访问通常避免了这种开销。通过在本地使用外部表，可以减少对网络资源的依赖，从而提高整体系统的可靠性。

  4. 灵活性： 外部表允许更灵活地选择何时更新本地数据。可以定期同步数据，或者在需要时通过一些事件触发来更新本地数据。这种方式相对于实时远程调用更容易控制和管理。 

        然而，需要注意的是，使用外部表也可能带来一些挑战，例如数据一致性和同步的问题。如果外部数据源发生变化，需要确保本地数据及时更新，以避免脏数据或不一致的情况。因此，在选择使用外部表还是远程调用时，需要根据具体的业务需求和系统特点进行权衡。 

2. 概念介绍

           在 PostgreSQL 中，外部表是一项强大的功能，它允许我们在数据库中访问和查询来自不同数据源的数据，而无需实际地将数据复制到 PostgreSQL 数据库中。为了理解这一机制，我们将深入探讨一些核心概念。

1. 外部数据包装器 (FDW)：

        外部数据包装器是 PostgreSQL 提供的扩展，用于实现对外部数据源的连接和查询。不同的外部数据包装器支持不同的数据源，例如 postgres_fdw 用于连接到其他 PostgreSQL 数据库，而 file_fdw 则用于读取文件系统中的文件。

2. 外部服务器 (Foreign Server)：

        外部服务器是外部数据包装器的一个实例，它定义了如何连接到实际的外部数据源。创建外部服务器时，我们需要指定连接外部数据源所需的参数，如主机地址、端口号、数据库名称等。

CREATE SERVER my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'external_host', dbname 'external_db');

3. 用户映射 (User Mapping)

        用户映射建立了连接到外部服务器的 PostgreSQL 用户和外部数据源中的用户之间的映射关系。这包含了连接所使用的用户名、密码等信息。

CREATE USER MAPPING FOR local_user SERVER my_server OPTIONS (user 'external_user', password 'external_password');

4. 外部表 (Foreign Table)

        外部表是通过外部数据包装器定义的虚拟表，它提供了对外部数据源中数据的访问。外部表的结构和数据来自于外部数据源。

CREATE FOREIGN TABLE my_external_table (
    column1 datatype,
    column2 datatype,
    ...
) SERVER my_server OPTIONS (table_name 'external_table');

5. 查询重写 (Query Rewriting) 

        查询重写是外部数据包装器的一部分，它负责将 PostgreSQL 查询转换为外部数据源能够理解和执行的形式。这包括将条件和投影推送到外部数据源，以减少在 PostgreSQL 中检索的数据量。

6. 外部表列映射 (Column Mapping)

        外部表列映射定义了外部表的列与外部数据源中实际列的映射关系。这确保了在查询时，列的顺序和数据类型能够正确匹配。

IMPORT FOREIGN SCHEMA external_schema FROM SERVER my_server INTO public;

7. 服务器选项 (Server Options) 

        服务器选项是在创建外部服务器时指定的配置参数，用于指定连接到外部数据源的详细信息。

CREATE SERVER my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'external_host', dbname 'external_db');

8. 访问权限控制 (Access Control) 

        外部表同样受到 PostgreSQL 的访问权限控制机制的影响。在创建外部表时，需要确保用户具有足够的权限来访问外部表。

GRANT SELECT ON TABLE my_external_table TO some_role;

3. 开发环境搭建

3.1. 所用版本工具

依赖版本
Spring Boot2.6.3
Spring Cloud Alibaba2021.0.1.0
Spring Cloud 2021.0.1
java1.8
postgres13.12

3.2. pom文件编写

3.2.1. 父模块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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.xfc</groupId>
    <artifactId>xfc-fdw-cloud</artifactId>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>xfc-gateway</module>
        <module>xfc-foreign-table</module>
    </modules>
    <packaging>pom</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.3</version>
        <relativePath/>
    </parent>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <spring-cloud.version>2021.0.1</spring-cloud.version>
        <spring-cloud-alibaba.version>2021.0.1.0</spring-cloud-alibaba.version>
        <mybatis-plus.version>3.3.1</mybatis-plus.version>
    </properties>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>${mybatis-plus.version}</version>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-generator</artifactId>
                <version>${mybatis-plus.version}</version>
            </dependency>
            <!-- springCloud -->
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba.cloud</groupId>
                <artifactId>spring-cloud-alibaba-dependencies</artifactId>
                <version>${spring-cloud-alibaba.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>

    </dependencyManagement>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                    <encoding>${project.build.sourceEncoding}</encoding>
                </configuration>
            </plugin>
            <!--springboot 打包插件 ,不然报错 Unable to find main class
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            -->
        </plugins>
        <resources>
            <resource>
                <!--编译时，默认情况下不会将    mapper.xml文件编译进去，
                src/main/java 资源文件的路径，
                **/*.xml 需要编译打包的文件类型是xml文件，
                -->
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
    </build>

</project>
3.2.2. 网关模块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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>xfc-fdw-cloud</artifactId>
        <groupId>com.xfc</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>xfc-gateway</artifactId>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-gateway</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-bootstrap</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
    <build>
        <finalName>geoscene-back-gateway</finalName>
        <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>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>
3.2.3. 外部表模块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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>xfc-fdw-cloud</artifactId>
        <groupId>com.xfc</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>xfc-foreign-table</artifactId>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-bootstrap</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
    <build>
        <finalName>geoscene-back-gateway</finalName>
        <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>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

4. 源码简介

        我写了一个基于Spring Cloud读取外部表的demo，其链接如下：https://download.youkuaiyun.com/download/c18213590220/88653032?spm=1001.2014.3001.5503

4.1. 项目结构

本项目结构主要由父模块，网关微服务及外部表微服务构成，其接口访问流程如下图所示。      

        由上图看出，比起远程调用，外部表接口从请求到返回只经过了一个微服务。

4.2. 新增外部表

        我简单说sql文件夹里面的目录结构，foreign里面是构建外部表的语句，original里面是原始表的语句（外部表基于原始表抽取而来）。

        在navicat中执行外部表构建操作

4.3. 访问外部表对应接口

        启动网关微服务及外部表微服务，访问接口。
 

6. 结语

        以上就是本章的全部内容，多数据源系列现在更新了AbstractRoutingDataSource 及外部表两篇文章，想看多数据源其他技术栈的同学可以在评论区留言，我看到以后会（酌情）更新的~~。

7.参考链接

PostgreSQL-外部表_小宝大人的技术博客_51CTO博客

SpringBoot3+JPA+MySQL实现多数据源的读写分离(基于EntityManagerFactory)
没刮胡子的程序员专栏
03-21 2571
在Spring Boot3中配置多个数据源并实现自动切换EntityManager，这里我编写了一个RoutingEntityManagerFactory和AOP（面向切面编程）的方式来实现。 这里我配置了两个数据源：primary和secondary，其中primary主数据源用来写入数据，secondary从数据源用来读取数据。
【万字长文】SpringBoot整合Atomikos实现多数据源分布式事务（提供Gitee源码）
黄团团的个人博客
08-04 2437
在最近的实际开发的过程中，遇到了在多数据源的情况下要保证原子性的问题，这个问题当时遇到了也是思考了一段时间，后来通过搜集大量资料与学习，最后是采用了分布式事务来解决这个问题，在讲解之前，在我往期的博客提前搭好了一个SpringBoot整合MyBatis搭建MySQL多数据源的教程，本篇博客我是在原有的这个项目的基础之上进行的改造，主要新增了几个配置项进行实现的，大家可以先简单的看一下这篇文章！
springcloud微服务搭建多数据源（mysql,oracle,postgres,等等）管理模块，支持通过注解方式切换不同类型的数据库
君临天下tjm的博客
04-29 2101
同一套微服务管理系统，业务完全一样，但不同的客户可能要求使用自己熟悉的数据库，比如，mysql，oracle，postgres，还有一些国产数据库。如果能够将数据库模块独立出来，兼容各家的数据库操作，在发布系统之前，只需要通过注解切换到定制的数据库类型，那么就会方便很多。
SpringCloud的JPA接入PostgreSql 教程
之城的专栏
06-24 1682
项目目录结构 父build.gradle文件如下 spring-cloud-dependencies 和 spring-cloud-alibaba-dependencies 之间有版本对应关系的。 并不是可以随意搭配的。 具体版本对应关系参考： 版本关系 本想使用WebFlux模块的，奈何openfeign 不支持。 buildscript { ext { springBootVersion = '2.1.13.RELEASE' springBootMa
PostgreSQL/Hologres 外部表系统表 pg_foreign_table 详解
最新发布
qq_41081716的博客
06-21 304
ftrelid oid NOT NULL, -- 外部表在pg_class中的OIDftserver oid NOT NULL, -- 外部服务器在pg_foreign_server中的OIDftoptions text[] -- 外部表特定的选项，以"option=value"格式存储。
Spring Cloud初探之nacos-2.3.0适配postgresql数据库(一)
Neo.Yang的专栏
04-04 1669
修改nacos 2.3.0源码适配postgresql数据库。
Spring Cloud SpringCloud 自学课程3 SpringCloud Data JPA + PostgreSQL
优快云 正在崛起的新人---小麦
04-18 1072
实例代码：https://github.com/michaelzhanghe/springcloud-03-licensingservice.git------------------------------Windows10--------------------------------&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;projec...
PostgreSQL映射外部表、外部模式(postgres_fdw拓展的使用)
m0_71559454的博客
09-05 1162
本文章主要介绍PostgreSQL数据库postgres_fdw拓展的使用，包括映射外部表、外部模式
《【多数据源系列】基于SpringCloud实现PostgreSQL外部表读取（附源码）》demo
12-22
《【多数据源系列】基于SpringCloud实现PostgreSQL外部表读取（附源码）》demo，项目解压后即可启动，里面包含外部表sql和原始表sql，建议先看我写的文章熟悉一下里面外部表相关原理和概念以及文章里面关于该demo的...
SpringCloud与PostgreSQL集成：外部表读取示例（附源码）
资源摘要信息:"本资源是一个基于Spring Cloud框架实现的PostgreSQL外部表读取的示例项目，涵盖了多数据源处理的高级应用。通过该项目，开发者可以学习到如何在Spring Cloud环境中集成PostgreSQL数据库，并实现对外部...
基于spring boot + vue 的宿舍管理系统源码.zip
05-23
Spring Boot与这些数据库的集成非常方便，通过配置文件即可轻松配置数据源。此外，ORM（对象关系映射）框架如Hibernate或MyBatis可能被用来简化数据库操作。 项目结构通常会遵循MVC（模型-视图-控制器）设计模式，...
SpringBoot连接PostgreSql三层架构实现增改删
10-23
SpringBoot配置Mybaits连接PostgreSql三层架构实现增改删。结构代码规范齐全，上传的是整个工作空间。
microservices:使用Spring Boot，Spring Cloud Config，Spring Security，Eureka，Hystrix，Zuul，PostgreSql的微服务
03-15
这是一个微服务体系结构，我正在设计该体系以学习微服务的所有概念以及用于设计具有多个服务的健壮体系结构的不同技术。 为了使这种体系结构接近行业标准，我在数据库中创建了一些帐户和资产，这些帐户和资产将用于将数据加载到内存中。 启动服务的步骤： 启动spring-cloud-config-server从本地/远程存储库加载所有配置文件。 启动所有服务将订阅到的发现服务器。 启动用户服务，该服务将具有数据库连接以提取所有用户特定的信息。 启动帐户服务，该服务将具有数据库连接以提取所有特定于帐户的信息。 帐户服务将对用户服务进行API调用，以提取用户帐户映射列表 启动sprinc-cloud-api-gateway服务，它将充当所有服务的单个入口点。 用于构建此体系结构的技术：Java 11，Spring引导，Spring安全性，Eureka Discovery服务器/客户端，Hyst
springboot对接postgresql框架开发
11-29
使用springboot框架对接pgsql实现开发逻辑，主要用于对数据库的数据接口进行开发，
Spring连接PostgreSQL
alpha105的博客
06-07 1338
在IDEA中将PostgreSQL数据库连接到maven项目
Spring Cloud Alibaba Dubbo Nacos 整合Postgresql
花花世界芸芸众生的博客
03-05 1217
Spring Cloud Alibaba Dubbo Nacos 整合Postgresql Nacos 2.2.0支持postgresql数据库
seata和postgresql和springcloud整合
qq_19520877的博客
08-05 1848
第一步导入坐标 <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> <!--druid--> <dependency>
五分钟带你玩转spring cloud alibaba（三）定制postgresql版本的nacos
小鲍侃java
06-18 611
链接：https://pan.baidu.com/s/1VdzqtBYOx7TyBXMgwrVSqw 提取码：r8tg
Spring Cloud多数据源配置与使用
脚踏实地,实事求是。
01-11 3396
在实际的应用开发中，常常需要同时连接多个数据库来满足不同业务需求。Spring Cloud提供了便捷的多数据源配置和使用方式，使得开发人员能够轻松地管理多个数据库连接。在配置文件中定义多个数据源的信息。创建数据源配置类，将配置文件中的数据源信息注入到DataSource对象中。使用JdbcTemplate注入不同的数据源，并在业务逻辑中进行数据库操作。整个过程实现了多数据源的灵活配置和使用，使得系统更加灵活、可维护，并能够满足不同业务场景的需求。
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

后端小肥肠

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值