INTRODUCTION TO THE PERSISTENCE LAYER
As you may remember, when we analyzed the layers of our web application, next to the presentation layer that handles requests and creates HTML, XML, JSON responses in response to the client, there is also the persistence layer whose purpose is to interface the DBMS to our application.
The database can be either relational or NOSQL type such as MongoDB. In this course we will use SQL Server, then a relational database. The choice fell on this DBMS (there will also be the MySQL part) because Windows natively supports it, for Linux the version for this database engine has been available for a few years already, on macOS as in my case I will use a Docker container and Azure Data Studio to interface with the database. I already explained to you when I talked about installing components on macOS how to have SQL Server on this OS as well. Going back to the schema, the service layer is an intermediate layer that takes care of the Business Logic of the application and talks to the presentation layer. In this section we will also have a chance to talk about the domain layer, that is, those classes that are responsible for mapping the database tables to the application.
THE SAMPLE DATABASE
The AlphaShop sample database comes from large retail, has been depowered, and most of the tables have been removed. However, it remains an effective aid to understanding the course. We will focus our attention on the ITEMS table, BARCODE then the CUSTOMERS table and CARDS, PROMO and DETTPROMO when we discuss the Spring Boot.
DATA SOURCE CONFIGURATION IN SPRING MVC
Let’s edit the file pom.xml, you will remember that this file downloads the project dependencies. Now we need to enter the dependencies for the JDBC file that allows Java programs to access the database. We also need to download the driver for SQL Server.
JDBC DRIVER CONFIGURATION
Once we have installed the dependencies we need to configure the JDBC driver, we need to tell it where it should go to look for the database and what User Id and Password to log in with. We create a new Java class JdbcConfig.java, as with any configuration class we need to include the notation @Configuration. Another important notation is the @EnableTransactionManagement, with which we tell Spring MVC to enable transactions. The @ComponentScan notation allows us to find all the configuration classes, while the @PropertySource gives us access to a configuration file called application.properties and located in the src/main/resource package. In the file the code is explained step by step.
THE APPLICATION.PROPERTIES FILE
Represents the configuration of the JDBC driver then read from the newly created configuration class.
THE DOMAIN LAYER OF THE WEBAPP
The domain layer represents those class structures that map the various tables in the database. We create a new package com.xantrix.webapp.domain and create the Articles class within the package. This class will represent the ARTICLES table of our data source. When we are dealing with a domain class we always have to implement serialization.
ASSIGN THE CORRECT PERMISSIONS TO AN SQL SERVER USER
Normally, the user sa (Super Administrator) is never used to access the database. It is appropriate to create a new login to be associated with the creation of a new user with the right privileges. This is for obvious security reasons.
SQL SERVER DATABASE VISUALIZATION
The following video shows how to open the AlphaShop database on macOS.
Leave A Comment