Connect to MySQL Database

About Mysql

MySQL is an open-source relational database management system ideal for small and large applications. MySQL is free and open-source software under the terms of the GNU General Public License and is also available under various proprietary licenses.

Its name is derived from "My", the name of co-founder Michael Widenius's daughter My and "SQL",

Installing and Configuring MySQL Database

Mysql community edition can be downloaded and installed from the offical website based on your operating system.

It can also be installed from the package manager for your operating system. The Windows system can be installed using chocolatey package manager using the command.

Once installation is successful, it should run as a service with the below default settings.

• Host- Machine name / localhost
• Port - 3306
• User - root
• Password - By default blank

To verify the installation, go to the command prompt and type the below command.

It should take you to the mysql prompt.

To verify host and port, use the below command on the mysql prompt.

The output should display the host and port where mysql is running.

Changing the password for the root user is highly recommended because it comes with the highest privileges. To change the password, run the below command on the mysql prompt.

Above command change root password to "MyNewPass".

Never use the root user from the application to connect to the database.

Configure and Create a New Database

For MySQL to be useful for the application, a database and users with required privileges should be created. An employee management system should have its own database/scheme on the server. To create a database, follow the below steps.

1. Create a User and Password. To create a user and password using the command on mysql prompt.

The above command will create user bob with password start123.

2. Create a Database.

The above command will create the database employee_management into the server. To verify, use the command show databases;.

3. Grant Privileges. Now that we have database employee_management and user bob. We want bob to operate on the employee_management database. Use the below command to grant priviliges.

The above command grants all privileges to bob on the employee_management database.

Verification:

To verify if bob has got the required privileges to log in using bob MySQL -about -p and run the command show databases;

It should display the employee_management database on the console.

Integrate MySQL with Spring Boot

Adding MySQL Dependencies

For the application to connect to the MySQL database, it should have an appropriate driver. Our application needs the below dependencies.

Configure MySQL Using Spring Boot Properties

For the spring boot application to make use of MySQL, we need to configure the required properties in application.properties

Run the application. If you don't see any error, the application to database connection is successful.

Create JPA Entity

Let's create an employee entity with a few attributes.

Above entity will represent an employee table in the database.

We can instruct hibernate to create the employees table for us during startup using the property.

The above command will read the entity and execute the corresponding DDL statement into the database. Queries should be visible on the console.

Repository Class for Spring Boot

Create a repository class to perform basic CRUD operations on the employee table.

JPARepository comes with basic CRUD operations ready-made.

Testing Application

Spring Boot provides the @DataJpaTest annotation to test Spring Data JPA repositories or any other JPA-related components. We can add it to our unit test, which will configure the Spring application context for the data layer. @DataJpaTest connect to h2 database. Therefore we need to add h2 dependency in our pom with scope test.

Having a separate test database different from the actual database is a good design principle. H2 is an excellent choice for unit tests because we don't need any external database server running to run the test case. H2 creates an embedded database on the fly and attaches it to the test case.

EmployeeRepositoryTest