在JDBC中连接到指定schema

1. 引言

在这篇文章中，我们将探讨数据库模式的基本概念，为什么我们需要它们，以及它们如何发挥作用。之后，我们将重点关注使用JDBC与PostgreSQL数据库设置模式的实用示例。

2. 什么是数据库模式

一般来说，数据库模式是一组规范数据库的规则。它是数据库的额外抽象层。有两种类型的模式：

逻辑数据库模式 定义了存储在数据库中的数据适用的规则。
物理数据库模式 定义了数据在存储系统上物理存储的规则。

在PostgreSQL中，模式指的是第一种类型。模式是逻辑命名空间，包含表、视图、索引等数据库对象。每个模式属于一个数据库，每个数据库至少有一个模式。如果没有指定，默认的PostgreSQL模式是public。我们创建的所有对象（未指定模式），默认归属于public模式。

PostgreSQL中的模式允许我们将表和视图组织成组，使其更易于管理。这样，我们可以更细粒度地设置数据库对象的权限。此外，模式允许我们在同一时间让多个用户同时使用同一个数据库，而不会相互干扰。

3. 如何在PostgreSQL中使用模式

要访问数据库模式的对象，我们必须在要使用的特定数据库对象名称前指定模式的名称。例如，要查询名为product的表在s*tore模式下，我们需要使用表的限定名：

SELECT * FROM store.product;

建议避免硬编码模式名称，以防止将具体模式与应用程序耦合。这意味着我们直接使用数据库对象名称，并让数据库系统决定使用哪个模式。PostgreSQL通过搜索路径确定查找给定表的位置。

3.1. PostgreSQL的搜索路径

搜索路径是一个有序的模式列表，定义了数据库系统查找给定数据库对象的方式。如果对象存在于其中任何一个（或多个）模式中，我们将获取第一个找到的实例。否则，我们将收到一个错误。搜索路径中的第一个模式也称为当前模式。要查看搜索路径中的哪些模式，请使用以下查询：

SHOW search_path;

默认的PostgreSQL配置将返回$user和public模式。我们已经提到的public模式，是以当前用户命名的模式，可能不存在。在这种情况下，数据库会忽略该模式。

要将store模式添加到搜索路径中，我们可以执行以下查询：

SET search_path TO store,public;

此后，我们可以不指定模式查询product表。此外，我们还可以从搜索路径中移除public模式。

如前所述设置搜索路径是在ROLE级别的一种配置。我们可以通过更改postgresql.conf文件并重新加载数据库实例来在整个数据库级别更改搜索路径。

3.2. JDBC URL

在连接设置期间，我们可以使用JDBC URL指定各种参数。通常的参数包括数据库类型、地址、端口、数据库名称等。自PostgreSQL 9.4版本开始，它增加了通过URL指定当前模式的支持。

在实践这一概念之前，让我们设置一个测试环境。为此，我们将使用testcontainers库，创建以下测试设置：

@ClassRule
public static PostgresqlTestContainer container = PostgresqlTestContainer.getInstance();

@BeforeClass
public static void setup() throws Exception {
    Properties properties = new Properties();
    properties.setProperty("user", container.getUsername());
    properties.setProperty("password", container.getPassword());
    Connection connection = DriverManager.getConnection(container.getJdbcUrl(), properties);
    connection.createStatement().execute("CREATE SCHEMA store");
    connection.createStatement().execute("CREATE TABLE store.product(id SERIAL PRIMARY KEY, name VARCHAR(20))");
    connection.createStatement().execute("INSERT INTO store.product VALUES(1, 'test product')");
}

使用@ClassRule，我们创建了一个PostgreSQL数据库容器实例。接下来，在setup方法中，连接到该数据库并创建所需的对象。

现在数据库设置好后，我们可以使用JDBC URL连接到store模式：

@Test
public void settingUpSchemaUsingJdbcURL() throws Exception {
    Properties properties = new Properties();
    properties.setProperty("user", container.getUsername());
    properties.setProperty("password", container.getPassword());
    Connection connection = DriverManager.getConnection(container.getJdbcUrl().concat("&" + "currentSchema=store"), properties);

    ResultSet resultSet = connection.createStatement().executeQuery("SELECT * FROM product");
    resultSet.next();

    assertThat(resultSet.getInt(1), equalTo(1));
    assertThat(resultSet.getString(2), equalTo("test product"));
}

要更改默认模式，我们需要指定currentSchema参数。如果我们输入一个不存在的模式，PSQLException会在select查询时抛出，因为找不到数据库对象。

3.3. `PGSimpleDataSource`从PostgreSQL驱动库

为了连接到数据库，我们可以使用来自PostgreSQL驱动库的javax.sql.DataSource实现，名为PGSimpleDataSource。这个具体的实现支持设置模式：

@Test
public void settingUpSchemaUsingPGSimpleDataSource() throws Exception {
    int port = //extracting port from container.getJdbcUrl()
    PGSimpleDataSource ds = new PGSimpleDataSource();
    ds.setServerNames(new String[]{container.getHost()});
    ds.setPortNumbers(new int[]{port});
    ds.setUser(container.getUsername());
    ds.setPassword(container.getPassword());
    ds.setDatabaseName("test");
    ds.setCurrentSchema("store");

    ResultSet resultSet = ds.getConnection().createStatement().executeQuery("SELECT * FROM product");
    resultSet.next();

    assertThat(resultSet.getInt(1), equalTo(1));
    assertThat(resultSet.getString(2), equalTo("test product"));
}

在使用PGSimpleDataSource时，如果没有设置模式，驱动程序将使用public模式作为默认值。

3.4. `@Table`注解来自`javax.persistence`包

如果我们项目中使用JPA，我们可以在实体级别使用@Table注解指定模式。此注解可以为模式指定值，或者默认为空字符串。让我们将product表映射到Product实体：

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

    @Id
    private int id;
    private String name;
    
    // getters and setters
}

为了验证这种行为，我们将设置EntityManager实例以查询product表：

@Test
public void settingUpSchemaUsingTableAnnotation(){
    Map<String,String> props = new HashMap<>();
    props.put("hibernate.connection.url", container.getJdbcUrl());
    props.put("hibernate.connection.user", container.getUsername());
    props.put("hibernate.connection.password", container.getPassword());
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("postgresql_schema_unit", props);
    EntityManager entityManager = emf.createEntityManager();

    Product product = entityManager.find(Product.class, 1);

    assertThat(product.getName(), equalTo("test product"));
}

正如我们在第3部分所述，出于各种原因最好避免将模式与代码耦合。因此，这个功能经常被忽视，但在访问多个模式时可能会有优势。

4. 总结

在这篇教程中，我们首先介绍了数据库模式的基本理论。然后，我们描述了使用不同方法和技术设置数据库模式的多种方式。如往常一样，所有代码示例可在GitHub上找到。

Persistence

REST

Security