How to connect your Spring Data application with a PostgreSQL database?
Connecting your Spring Data application to PostgreSQL is a crucial step to effectively manage your database. In this article, we will guide you through the process from defining the data model to configuring the connection in Spring. All this so that you can easily interact with your data through APIs.
What is the data model we will use?
Our data model is composed of five interrelated tables that optimize the management of our database:
- Categories table: it includes the
ID
as primary key, description
and status
. It will be used to categorize products.
- Products Table: Its primary key is
Product ID
and contains name
, category ID
, barcode
, selling price
, quantity in stock
and status
.
- Customers Table: Here we will store customer information such as
name
, last name
, cell phone
, address
and email
.
- Purchases Table: Records transactions with
purchase ID
as primary key, linked to customers by customer ID.
- Table Purchases Products: Uses a primary key composed of
Purchase ID
and Product ID
with additional columns such as quantity
, total
and status
.
How to create the database and load initial data?
To get started, you need to create the database and then run the scripts to define tables and load initial data. Here is how to do it:
- Log in to PostgreSQL: Use PG Admin to connect and enter the configured credentials.
- Create Database: In the PG Admin environment, generate a new database called
PlatziMarket
.
- Execute Scripts:
- Copy and paste the contents of
schema.sql
into PG Admin and run it to create the tabular structures.
- Proceed in the same way for
data.sql
to load initial data.
How to manage automatic sequences?
Streams in PostgreSQL allow to automatically manage primary key values:
- Resets the sequences corresponding to
products
, categories
and purchases
so that each new insertion continues from the last used identifier.
- Check the sequences in PG Admin to make sure they are configured correctly.
How to connect the application to PostgreSQL with IntelliJ IDEA?
To establish your application's connection to the database, follow these steps:
-
Add PostgreSQL Dependency: In build.gradle
, add the PostgreSQL JDBC Driver dependency as runtime only
to be resolved only during execution.
runtimeOnly 'org.postgresql:postgresql'.
-
Configure File Properties: In application-dev.properties
add the necessary settings:
spring.datasource.url=jdbc:postgresql://localhost:5432/PlatziMarketspring.datasource.username=postgresspring.datasource.password=platzi
-
Update Dependencies: Refresh the project to include changes and make sure the dependencies are correctly integrated.
How to verify the successful connection of the application?
Once your application is configured, compile and run your project in Spring Boot. Watch the console:
- Connectivity and Scan: You should see how Spring Data initiates the repository scan and loads the data from the database.
- Initialization Time: The application should start in about 30 seconds, establishing the connection to the server and database successfully.
With this guide, you will not only optimize the operation of Spring Data with PostgreSQL, but you will also be able to explore and manipulate your data efficiently. Go ahead and keep exploring the world of database development!
Want to see more contributions, questions and answers from the community?