Now before we try to build other REST APIs which will help us to expose the CRUD operations to the clients, First, we need to make sure that our database related code is completed and it is available for us to interact with the database.
As of now, we are using an internal H2 database. To use this H2 database, we can define some properties or configurations inside our application.
For the same, we need to go to this resource folder, and under this resources folder – we have application.properties file.
Inside this file, application.properties, we can define all the configurations related to Spring Boot where we can mention the port number which needs to be considered by the spring boot to start the service, we can define database related configurations, we can define message queues related configurations, we can define actuator related configurations.
So there are a good amount of properties or configurations that we can define inside this file, which will make developer life easy.
With the properties that we mentioned inside this file, Spring Boot is going to do a lot of work behind the scenes.
Spring Boot is going to take care of connecting to the database, starting the server at the mentioned port number and enabling certain features of actuator. This way we can achieve many functionality with these properties.
We can define these properties with the format of key and value, which is the default way inside spring boot. Instead, we are going to define all our properties with the YAML format.
YAML is the latest and mostly used format. The reason why I’m using the .yml format instead of .properties is because Yml is very intuitive in nature.
We can easily read the YAML file very easily and the same Yml format is going to be used inside Docker, Kubernetes or any cloud providers that you go to like AWS, GCP, Azure.
Introduction to YAML syntax
YAML works based upon indentation.
When I say indentation, you can see whenever I want to define this server.port as a key inside yaml, I need to first mention server followed by :, post that I should immediately go to a tab space inside my next row.
server:
port: 8080
Like you can see here I have given a tab space. Then only my Yaml is going to consider the key as server.port.
For providing the value, post the :, we need to make sure we are giving a single space followed by what is the value.
server:
port: 8080
spring:
application:
name: accounts
datasource:
url: jdbc:h2:mem:testdb
driverClassName: org.h2.Driver
username: sa
password: ''
h2:
console:
enabled: true
jpa:
database-platform: org.hibernate.dialect.H2Dialect
hibernate:
ddl-auto: update
show-sql: true
We need to maintain the indentation properly. Then only my YAML is going to work.
H2 DB properties
So with the spring.datasource.url, I’m telling the spring boot framework that this is the endpoint details where my application can connect to the internal h2 database.
So jdbc:h2:mem:testdb – this is the value that we need to give.
The driverClassName we need to give org.h2.Driver and username:sa. This is the default username.
I want to go with the default password, which is an empty value.
With the help of this spring.h2.console.enabled – I’m telling to the spring boot framework to enable the h2 console so that I can login into this h2 console from my browser and I can see all the tables and records that are inserted are created into my internal h2 database.
→ spring.jpa.hibernate.ddl-auto: update -> we are telling the spring boot framework, if there are no tables created inside my internal h2 database, please go ahead and create them based upon the SQL instructions that I’m going to provide automatically during the startup. So that’s the purpose here.
We don’t want to create the tables every time manually inside our internal H2 database. We want that process to automatically happen when the server is being started because the h2 database is an internal memory database, whenever you shut down the server, all the data and all the tables that you have created will be wiped off.
That’s why to overcome the challenge of creating the tables and data manually every time, we can provide some SQL scripts. Based upon that, my spring boot framework can automatically create these tables behind the scenes during the startup.
Create Tables – schema.sql file
During our application startup, we want some tables to be created so that we can store that data inside these tables.
For that purpose, Under the resources folder, you need to create a new file with the name schema.sql.
Whenever you want to create the tables, columns, you need to define a file with the name shema.sql.
Whereas if you want to create data with the help of insert scripts or update scripts, you can define a file with the name data.sql.
For now, I just want to create the tables inside my internal H2 database.
schema.sql
CREATE TABLE IF NOT EXISTS `customer` (
`customer_id` int AUTO_INCREMENT PRIMARY KEY,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`mobile_number` varchar(20) NOT NULL,
`created_at` date NOT NULL,
`created_by` varchar(20) NOT NULL,
`updated_at` date DEFAULT NULL,
`updated_by` varchar(20) DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS `accounts` (
`customer_id` int NOT NULL,
`account_number` int AUTO_INCREMENT PRIMARY KEY,
`account_type` varchar(100) NOT NULL,
`branch_address` varchar(200) NOT NULL,
`created_at` date NOT NULL,
`created_by` varchar(20) NOT NULL,
`updated_at` date DEFAULT NULL,
`updated_by` varchar(20) DEFAULT NULL
);
With these things in place, now start the application again.
If you see this message : H2 console available at ‘/h2-console’. Database available at ‘jdbc:h2:mem:testdb’
http://localhost:8080/h2-console
Make sure these values match with the values mentioned in your application.yml file.
Click on ‘Connect’.
Now you can see two tables created for you – ACCOUNTS and CUSTOMER tables
My accounts table has columns like customer_Id, account _Number, account_Type, branch_Address, created_At, created_By, updated_At and updated_By.
Similarly, if I click on this customer, it has columns like customer_Id, name, email, mobile_Number, created_At, created_By, updated_At and updated_By.
So these tables are created based upon the scripts that we have provided inside the schema.sql file.
→ For this customer table, the primary key is going to be the customer_Id column and it is going to be automatically incremented whenever I’m trying to insert a new record into the database.
Now, coming to the accounts table, you can see I’m mentioning the customer_Id column and there is going to be a foreign key link between these customer and accounts table with these customer_Id column and inside these accounts table, we are going to have a column with the name account _Number which will act as a primary key.
With this, we set up the H2 database with the help of this schema.sql file and with the properties mentioned inside the application.yml file.
Bits, Bytes and Brains 