百度360必应搜狗淘宝本站头条
nodejsbufferorg.jsoupsetnocountoncv2.bitwise_nottime.strptime
当前位置：网站首页 > 编程字典 > 正文

PostgreSQL带运行时参数的视图（合规体系运行的全景视图模式常常用于）

toyiye 2024-07-03 02:10 24 浏览 0 评论

更多编程技术文章，请查阅IOKKS - 专业编程技术分享平台

有许多情况下，应用程序需要灵活多变，能够在运行时生成动态报告。

本文旨在通过利用 PostgreSQL 数据库支持的临时配置参数，提供一种实现这一目标的方法。

根据 PostgreSQL 文档，在 7.3 版本开始，可以使用 set_config(name, value, is_local) 函数设置配置参数。稍后，可以使用 current_setting(name) 函数读取先前设置的参数值，必要时进行转换并使用。如果前一个函数的第三个参数为 true，则更改的设置仅适用于当前事务。

这正是这里所需要的——提供一个可以作为原子操作的一部分使用的运行时参数值的方法。

设置

示例应用程序构建如下：

  • Java 21
  • Spring Boot 版本 3.1.15
  • PostgreSQL 驱动程序版本 42.6.0。
  • Liquibase 4.20.0
  • Maven 3.6.3

在应用程序级别，Maven 项目配置为使用 Spring Data JPA 和 Liquibase 依赖项。

领域由产品表示，其价格以各种货币表示。为了在不同货币之间进行转换，存在货币汇率。目标是能够以某种货币的汇率读取所有产品及其价格，以及某一天的汇率。

概念验证

为了开始建模，连接到数据库后首先应创建一个新模式。

create schema pgsetting;

有三个实体：Product、Currency 和 CurrencyExchange。

@Entity
@Table(name = "product")
public class Product {

    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "price", nullable = false)
    private Double price;

    @ManyToOne
    @JoinColumn(name = "currency_id")
    private Currency currency;

    ...
}

@Entity
@Table(name = "currency")
public class Currency {

    @Id
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    ...
}


@Entity
@Table(name = "currency_exchange")
public class CurrencyExchange {

    @Id
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "date", nullable = false)
    private LocalDate date;

    @ManyToOne
    @JoinColumn(name = "from_currency_id", nullable = false)
    private Currency from;

    @ManyToOne
    @JoinColumn(name = "to_currency_id", nullable = false)
    private Currency to;

    @Column(name = "value", nullable = false)
    private Double value;

    ...
}

每个实体都有相应的 CrudRepository。

@Repository
public interface ProductRepository extends CrudRepository<Product, Long> { }

@Repository
public interface CurrencyRepository extends CrudRepository<Currency, Long> { }

@Repository
public interface CurrencyExchangeRepository extends CrudRepository<CurrencyExchange, Long> { }

数据源通常在 [application.properties](https://github.com/horatiucd/pg-setting/blob/master/src/main/resources/application.properties) 文件中配置，其中包括记录了用于初始化模式的三个表和它们之间关系的 Liquibase 变更日志文件的路径。

有关详细信息，可以查看应用程序属性和 db/changelog/schema-init.xml 文件。

根变更日志文件为：

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <include file="/db/changelog/schema-init.xml"/>

</databaseChangeLog>

应用程序启动时，变更集按声明顺序执行。到目前为止，一切都很简单，没有什么特别之处——一个简单的 Spring Boot 应用程序，其数据库变更由 Liquibase 管理。

创建动态报告

假设当前应用程序定义了两种货币——RON 和 EUR，以及两种以不同货币记录价格的产品。

货币

+--+----+
|id|name|
+--+----+
|1 |RON |
|2 |EUR |
+--+----+

产品

+--+-------------------+-----+-----------+
|id|name               |price|currency_id|
+--+-------------------+-----+-----------+
|1 |Swatch Moonlight v1|100  |2          |
|2 |Winter Sky         |1000 |1          |
+--+-------------------+-----+-----------+

2023 年 11 月 15 日的货币汇率

+--+----------+----------------+--------------+-----+
|id|date      |from_currency_id|to_currency_id|value|
+--+----------+----------------+--------------+-----+
|1 |2023-11-15|2               |1             |5    |
|2 |2023-11-15|2               |2             |1    |
|3 |2023-11-15|1               |2             |0.2  |
|4 |2023-11-15|1               |1             |1    |
+--+----------+----------------+--------------+-----+

目标结果是一个产品报告，其中所有价格以 EUR 表示，使用 2023 年 11 月 15 日的汇率。这意味着需要转换第二个产品的价格。

为了简化设计，先前设定的目标被分解为更小的部分，然后逐一实现。从概念上讲，应首先获取产品，然后转换它们的价格（如果需要）。

  1. 获取产品。
  2. 使用请求的货币汇率转换价格。

前者很简单。Spring Data Repository 方法很容易允许获取产品——List<Product> findAll()。

后者可以通过查询实现转换。

SELECT p.id,
       p.name,
       p.price * e.value price,       
       e.to_currency_id currency_id,
       e.date
FROM product p
LEFT JOIN currency_exchange e on p.currency_id = e.from_currency_id and
        e.to_currency_id = 2 and
        e.date = '2023-11-15'

为了将两者合并，完成了以下操作：

  • 定义一个视图，用于上述查询——product_view

它在 product-view.sql 文件中定义，并作为可重复的 Liquibase 变更集的幂等操作添加，每当更改时运行。

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <include file="/db/changelog/schema-init.xml"/>

    <changeSet id="repeatable" author="horatiucd" runOnChange="true">
        <sqlFile dbms="postgresql" path="db/changelog/product-view.sql"/>
    </changeSet>

</databaseChangeLog>
  • 定义一个新实体 — ProductView — 作为领域的一部分，并与相应的存储库一起使用。
@Entity
@Immutable
public class ProductView {

    @Id
    private Long id;

    private String name;

    private Double price;

    private LocalDate date;

    @ManyToOne
    @JoinColumn(name = "currency_id")
    private Currency currency;

    ...
}
@Repository
public interface ProductViewRepository extends org.springframework.data.repository.Repository<ProductView, Long> {

    List<ProductView> findAll();
}

现在应用程序能够构建所需的报告，但只能为硬编码的货币和汇率。

为了在运行时传递这两个值，同一事务中执行了以下操作：

  • 将两个参数值设置为配置参数 — SELECT set_config(:name, :value, true)
  • 使用存储库方法获取 ProductView 实体

此外，修改了 product_view，以读取作为当前事务的一部分设置的配置参数，并相应地选择数据。

SELECT p.id,
       p.name,
       p.price * e.value price,
       e.date,
       e.to_currency_id currency_id
FROM product p
LEFT JOIN currency_exchange e on p.currency_id = e.from_currency_id and
        e.to_currency_id = current_setting('pgsetting.CurrencyId')::int and
        e.date = current_setting('pgsetting.CurrencyDate')::date;

current_setting('pgsetting.CurrencyId') 和 current_setting('pgsetting.CurrencyDate') 调用读取先前设置的参数，然后进行转换并使用。

实现需要一些额外的调整。

ProductViewRepository 增加了一个允许设置配置参数的方法。

@Repository
public interface ProductViewRepository extends org.springframework.data.repository.Repository<ProductView, Long> {

    List<ProductView> findAll();

    @Query(value = "SELECT set_config(:name, :value, true)")
    void setConfigParam(String name, String value);
}

最后一个参数始终设置为 true，因此该值仅在当前事务期间保留。

此外，定义了一个 ProductService，用于清楚地标记事务中涉及的所有操作。

@Service
public class ProductService {

    private final ProductViewRepository productViewRepository;

    public ProductService(ProductViewRepository productViewRepository) {
        this.productViewRepository = productViewRepository;
    }

    @Transactional
    public List<ProductView> getProducts(Currency currency, LocalDate date) {
        productViewRepository.setConfigParam("pgsetting.CurrencyId",
                String.valueOf(currency.getId()));

        productViewRepository.setConfigParam("pgsetting.CurrencyDate",
                DateTimeFormatter.ofPattern("yyyy-MM-dd").format(date));

        return productViewRepository.findAll();
    }
}

参数的名称与 product_view 定义中使用的名称相同。

为了验证实现，设置了两个测试。

@SpringBootTest
class Product1Test {

    @Autowired
    private CurrencyRepository currencyRepository;

    @Autowired
    private ProductRepository productRepository;

    @Autowired
    private CurrencyExchangeRepository rateRepository;

    @Autowired
    private ProductService productService;

    private Currency ron, eur;
    private Product watch, painting;
    private CurrencyExchange eurToRon, ronToEur;
    private LocalDate date;

    @BeforeEach
    public void setup() {
        ron = new Currency(1L, "RON");
        eur = new Currency(2L, "EUR");
        currencyRepository.saveAll(List.of(ron, eur));

        watch = new Product(1L, "Swatch Moonlight v1", 100.0d, eur);
        painting = new Product(2L, "Winter Sky", 1000.0d, ron);
        productRepository.saveAll(List.of(watch, painting));

        date = LocalDate.now();
        eurToRon = new CurrencyExchange(1L, date, eur, ron, 5.0d);
        CurrencyExchange eurToEur = new CurrencyExchange(2L, date, eur, eur, 1.0d);
        ronToEur = new CurrencyExchange(3L, date, ron, eur, .2d);
        CurrencyExchange ronToRon = new CurrencyExchange(4L, date, ron, ron, 1.0d);
        rateRepository.saveAll(List.of(eurToRon, eurToEur, ronToEur, ronToRon));
    }
}

前者获取以 EUR 记录价格，使用记录的汇率。

@Test
void prices_in_eur() {
    List<ProductView> products = productService.getProducts(eur, date);
    Assertions.assertEquals(2, products.size());

    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getCurrency().getId().equals(eur.getId())));

    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getDate().equals(date)));

    Assertions.assertEquals(watch.getPrice(),
            products.get(0).getPrice());
    Assertions.assertEquals(painting.getPrice() * ronToEur.getValue(),
            products.get(1).getPrice());
}

调用时，product_view 为：

+--+-------------------+-----+-----------+----------+
|id|name               |price|currency_id|date      |
+--+-------------------+-----+-----------+----------+
|1 |Swatch Moonlight v1|100  |2          |2023-11-15|
|2 |Winter Sky         |200  |2          |2023-11-15|
+--+-------------------+-----+-----------+----------+

后者使用相同的汇率，获取以 RON 价格的产品。

@Test
void prices_in_ron() {
    List<ProductView> products = productService.getProducts(ron, date);
    Assertions.assertEquals(2, products.size());

    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getCurrency().getId().equals(ron.getId())));

    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getDate().equals(date)));

    Assertions.assertEquals(watch.getPrice() * eurToRon.getValue(),
            products.get(0).getPrice());
    Assertions.assertEquals(painting.getPrice(),
            products.get(1).getPrice());
}

调用时，product_view 为：

+--+-------------------+-----+-----------+----------+
|id|name               |price|currency_id|date      |
+--+-------------------+-----+-----------+----------+
|1 |Swatch Moonlight v1|500  |1          |2023-11-15|
|2 |Winter Sky         |1000 |1          |2023-11-15|
+--+-------------------+-----+-----------+----------+

示例代码

可在 此处 找到。

相关推荐

为何越来越多的编程语言使用JSON（为什么编程）

JSON是JavascriptObjectNotation的缩写，意思是Javascript对象表示法，是一种易于人类阅读和对编程友好的文本数据传递方法，是JavaScript语言规范定义的一个子...

何时在数据库中使用 JSON（数据库用json格式存储）

在本文中，您将了解何时应考虑将JSON数据类型添加到表中以及何时应避免使用它们。每天?分享?最新?软件?开发?，Devops，敏捷?，测试?以及?项目?管理?最新?，最热门?的?文章?，每天?花?...

MySQL 从零开始:05 数据类型（mysql数据类型有哪些,并举例）

前面的讲解中已经接触到了表的创建，表的创建是对字段的声明，比如：上述语句声明了字段的名称、类型、所占空间、默认值和是否可以为空等信息。其中的int、varchar、char和decimal都...

JSON对象花样进阶（json格式对象）

一、引言在现代Web开发中，JSON（JavaScriptObjectNotation）已经成为数据交换的标准格式。无论是从前端向后端发送数据，还是从后端接收数据，JSON都是不可或缺的一部分。...

深入理解 JSON 和 Form-data（json和formdata提交区别）

在讨论现代网络开发与API设计的语境下，理解客户端和服务器间如何有效且可靠地交换数据变得尤为关键。这里，特别值得关注的是两种主流数据格式：...

JSON 语法（json 语法 priority）

JSON语法是JavaScript语法的子集。JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔花括号保存对象方括号保存数组JS...

JSON语法详解（json的语法规则）

JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔大括号保存对象中括号保存数组注意：json的key是字符串,且必须是双引号，不能是单引号...

MySQL JSON数据类型操作（mysql的json）

概述mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库，在处理json这种非结构化的数据...

JSON的数据模式（json数据格式示例）

像XML模式一样，JSON数据格式也有Schema，这是一个基于JSON格式的规范。JSON模式也以JSON格式编写。它用于验证JSON数据。JSON模式示例以下代码显示了基本的JSON模式。{"...

前端学习——JSON格式详解（后端json格式）

JSON(JavaScriptObjectNotation)是一种轻量级的数据交换格式。易于人阅读和编写。同时也易于机器解析和生成。它基于JavaScriptProgrammingLa...

什么是 JSON:详解 JSON 及其优势（什么叫json）

现在程序员还有谁不知道JSON吗？无论对于前端还是后端，JSON都是一种常见的数据格式。那么JSON到底是什么呢？JSON的定义...

PostgreSQL JSON 类型:处理结构化数据

PostgreSQL提供JSON类型，以存储结构化数据。JSON是一种开放的数据格式，可用于存储各种类型的值。什么是JSON类型？JSON类型表示JSON(JavaScriptO...

JavaScript:JSON、三种包装类（javascript 包）

JOSN:我们希望可以将一个对象在不同的语言中进行传递，以达到通信的目的，最佳方式就是将一个对象转换为字符串的形式JSON（JavaScriptObjectNotation）-JS的对象表示法...

Python数据分析 只要1分钟 教你玩转JSON 全程干货

Json简介：Json，全名JavaScriptObjectNotation,JSON(JavaScriptObjectNotation(记号、标记))是一种轻量级的数据交换格式。它基于J...

比较一下JSON与XML两种数据格式?（json和xml哪个好）

JSON（JavaScriptObjectNotation）和XML（eXtensibleMarkupLanguage）是在日常开发中比较常用的两种数据格式，它们主要的作用就是用来进行数据的传...

取消回复欢迎 你 发表评论:

请填写验证码
一周热门
最近发表
标签列表