Connecting to a Specific Schema in JDBC – 在JDBC中连接到一个特定的模式

最后修改: 2021年 9月 7日

中文/混合/英文(键盘快捷键：t)

1. Introduction

1.绪论

In this article, we’ll cover the basics of database schemas, why we need them, and how they are useful. After that, we’ll focus on practical examples of setting schema in JDBC with PostgreSQL as a database.

在这篇文章中，我们将介绍数据库模式的基本知识，为什么我们需要它们，以及它们如何有用。之后，我们将重点介绍在JDBC中以PostgreSQL为数据库设置模式的实际例子。

2. What Is a Database Schema

2.什么是数据库模式

In general, a database schema is a set of rules that regulate a database. It is an additional layer of abstraction around a database. There are two kinds of schemas:

一般来说，数据库模式是一套规范数据库的规则。它是围绕数据库的一个额外的抽象层。有两种模式。

  1. Logical database schema defines rules that apply to the data stored in a database.
  2. Physical database schema defines rules on how data is physically stored on a storage system.

In PostgreSQL, schema refers to the first kind. Schema is a logical namespace that contains database objects such as tables, views, indexes, etc. Each schema belongs to one database, and each database has at least one schema. If not specified otherwise, the default schema in PostgreSQL is public. Every database object we create, without specifying the schema, belongs to the public schema.

在PostgreSQL中，schema指的是第一种。模式是一个逻辑命名空间，包含数据库对象，如表、视图、索引等。每个模式属于一个数据库，每个数据库至少有一个模式。如果没有另外指定，PostgreSQL中的默认模式是公共的。我们创建的每个数据库对象，如果没有指定模式，都属于public模式。

A schema in PostgreSQL allows us to organize tables and views into groups and make them more manageable. This way, we can set up privileges on our database objects on a more granular level. Also, schemas allow us to have multiple users using the same databases simultaneously without interfering.

PostgreSQL中的模式允许我们将表和视图组织成组，使它们更容易管理。这样，我们就可以在一个更细的层次上对我们的数据库对象设置权限。另外，模式还允许我们有多个用户同时使用同一个数据库而不互相干扰。

3. How to Use Schema With PostgreSQL

3.如何在PostgreSQL中使用模式

To access an object of a database schema, we must specify the schema’s name before the name of a given database object that we want to use. For example, to query table product within schema store, we need to use the qualified name of the table:

要访问一个数据库模式的对象，我们必须在我们想要使用的特定数据库对象的名称之前指定模式的名称。例如，要查询模式store中的表product，我们需要使用该表的限定名称。

SELECT * FROM store.product;

The recommendation is to avoid hardcoding schema names to prevent coupling concrete schema to our application. This means that we use database object names directly and let the database system determine which schema to use. PostgreSQL determines where to search for a given table by following a search path.

建议避免对模式名称进行硬编码，以防止将具体模式与我们的应用程序相耦合。这意味着我们直接使用数据库对象名称，让数据库系统决定使用哪种模式。PostgreSQL通过遵循搜索路径来决定在哪里搜索一个给定的表。

3.1. PostgreSQL search_path

3.1 PostgreSQL search_path

The search path is an ordered list of schemas that define the database system’s search for a given database object. If the object is present in any (or multiple) schemas we get the first found occurrence. Otherwise, we get an error. The first schema in the search path is also called the current schema. To preview which schemas are on the search path, we can use the query:

搜索路径是一个有序的模式列表，它定义了数据库系统对给定数据库对象的搜索。如果该对象存在于任何（或多个）模式中，我们会得到第一个发现的出现。否则，我们会得到一个错误。搜索路径中的第一个模式也被称为当前模式。要预览哪些模式在搜索路径上，我们可以使用查询。

SHOW search_path;

Default PostgreSQL configuration will return $user and public schemas. The public schema we already mentioned, the $user schema, is a schema named after the current user, and it might not exist. In that case, the database ignores that schema.

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

To add store schema to the search path, we can execute the query:

为了在搜索路径中添加商店模式，我们可以执行查询。

SET search_path TO store,public;

After this, we can query the product table without specifying the schema. Also, we could remove the public schema from the search path.

在这之后，我们可以在不指定模式的情况下查询产品表。另外，我们还可以从搜索路径中删除公共模式。

Setting the search path as we described above is a configuration on the ROLE level. We can change the search path on the whole database by changing the postgresql.conf file and reloading database instance.

像我们上面描述的那样设置搜索路径是在ROLE级别上的配置。我们可以通过改变postgresql.conf文件和重新加载数据库实例来改变整个数据库的搜索路径。

3.2. JDBC URL

3.2. JDBC URL

We can use JDBC URL to specify all kinds of parameters during connection setup. The usual parameters are database type, address, port, database name, etc. Since Postgres version 9.4. there is added support for specifying the current schema using URL.

我们可以使用JDBC URL来指定连接设置时的各种参数。通常的参数有数据库类型、地址、端口、数据库名称等。自Postgres 9.4.版本以来，增加了对使用URL指定当前模式的支持。。

Before we bring this concept to practice, let’s set up a testing environment. For this, we’ll use the testcontainers library and create the following test setup:

在我们将这个概念付诸实践之前，让我们先建立一个测试环境。为此，我们将使用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')");
}

With @ClassRule, we create an instance of PostgreSQL database container. Next, in the setup method, create a connection to that database and create the required objects.

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

Now when the database is set, let’s connect to the store schema using JDBC URL:

现在，当数据库设置完毕后，让我们使用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"));
}

To change the default schema, we need to specify the currentSchema parameter. If we enter a non-existing schema, PSQLException is thrown during a select query, saying the database object is missing.

要改变默认模式，我们需要指定currentSchema参数。如果我们输入一个不存在的模式，PSQLException就会在select查询时抛出，说数据库对象丢失。

3.3. PGSimpleDataSource

3.3.PGSimpleDataSource

To connect to a database, we can use javax.sql.DataSource implementation from PostgreSQL driver library named PGSimpleDataSource. This concrete implementation has support for setting up a schema:

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

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

While using PGSimpleDataSource, the driver uses public schema as a default if we don’t set schema.

在使用PGSimpleDataSource时，如果我们不设置模式，驱动程序会默认使用公共模式。

3.4. @Table Annotation From javax.persistence Package

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

If we use JPA in our project, we can specify schema on entity level using @Table annotation. This annotation can hold value for schema or defaults to empty a String. Let’s map our product table to the Product entity:

如果我们在项目中使用JPA，我们可以使用@Table注解在实体级别上指定模式。这个注解可以为模式保留值，或者默认为空String。让我们把我们的product表映射到Product实体。

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

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

To verify this behavior, we set up the EntityManager instance to query the product table:

为了验证这一行为，我们设置了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"));
}

As we previously mentioned in section 3, it’s best to avoid coupling schema to the code for various reasons. Because of that, this feature is often overlooked, but it can be advantageous when accessing multiple schemas.

正如我们之前在第3节中提到的，出于各种原因，最好避免将模式与代码耦合。正因为如此，这个功能经常被忽视，但在访问多个模式时，它可能是有利的。

4. Conclusion

4.总结

In this tutorial, first, we covered basic theory about database schemas. After that, we described multiple ways of setting database schema using different approaches and technologies. As usual, all the code samples are available over on GitHub.

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