当前位置：Java -> PostgreSQL视图中的运行时参数

PostgreSQL视图中的运行时参数

许多情况下，应用程序被要求具有足够的灵活性和多功能性，以便能够运行动态报告，其输入在运行时提供。

本文旨在通过利用 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 文件中进行配置，此文件还包含指向 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          |
+--+-------------------+-----+-----------+

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日的汇率。这意味着需要转换第二个产品的价格。

获取产品。
使用所请求日期的汇率将价格转换为所请求货币。

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));
    }
}

前者使用记录的汇率，获取以欧元计价的产品价格。

@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|
+--+-------------------+-----+-----------+----------+

后者使用相同的汇率，获取以罗马尼亚列伊计价的产品价格。

@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|
+--+-------------------+-----+-----------+----------+

示例代码

在这里可用。

推荐阅读： ChatGpt会对生活产生哪些影响

本文链接： PostgreSQL视图中的运行时参数

LTS JDK 21 特性如何从Spring Boot应用程序验证数据库连接处理Javax迁移到Jakarta的最佳实践一个解决企业开发痛点的PDF框架使用Spring WebFlux拥抱响应式编程 Marco Codes Live: Gavin King 和 Hibernate 6.3 [视频] 探索IBM App Connect Enterprise 12.0.12.0 Java 并行 GC 调优探索 IBM App 连接企业 12.0.9.0 释放Spring Cloud的微服务之力

PostgreSQL视图中的运行时参数

设置

概念验证

创建动态报告

货币

产品

11月15日的货币汇率

示例代码

相关文章：

热门文章