Java, Software Development

Specify Non-default PostgreSQL Schema When Connecting Using JDBC

By default, we use the public schema when we connect to PostgreSQL. Some applications use multiple schemas to store data in the database. Therefore, they need to specify explicitly which PostgreSQL schemas to use. This post shows how to connect to a database schema other than the public schema.

Environment Setup

We will use Docker for Windows, a PostgreSQL Docker image, Java Persistence API (JPA), Sprint Boot, and Java Development Kit (JDK) 8. This post assumes we already installed Docker.

First, we need a PostgreSQL database. We can create it using the PostgreSQL Docker image. Then we create two schemas – public and myapp_schema. The public schema has the pets table, while the myapp_schema schema has the persons table.

Consider the following PostgreSQL table definitions. We can use them to create tables in their respective PostgreSQL schemas.

The persons and pets tables have the following data.

Our Sprint Boot Application

We use the Spring Initlzr to generate the first Spring Boot codes. Then, we modify the codes. For the pom.xml file, we have the following important Maven dependencies.

For the Java source code files, we have the JPA entity, JPA Repository, and main class.

Then, the JPA Repository.

Finally, we have the main class.

Default PostgreSQL Schema in application.properties

To use the myapp_schema schema instead of the public schema, we will fiddle with the application.properties file. First, we start with the usual configuration that uses the default public schema.

The property spring.datasource.url has a JDBC URL that specifies only the PostgreSQL database – turreta. We will change that property’s value a bit later.

When we run our application, we get the following error.

The error indicates that the persons table does not exist (in the public schema).

Specify PostgreSQL Schema

Now, let us change the value on line 4 in the application.properties file to the following.

When we rerun the Spring Boot application, we get the following output.

In summary, we need to use PostgreSQL URL parameter currentSchema to specify explicitly which PostgreSQL schema to use.

Loading

Got comments or suggestions? We disabled the comments on this site to fight off spammers, but you can still contact us via our Facebook page!.


You Might Also Like