MySQL

Installing MySQL on your machine is tedious. It is easier to run MySQL from a container.

Docker Container

A Docker container is a lightweight environment that contains an application and only the resources that the application needs to run – nothing else.

The Docker Engine allows you to manage and run docker containers. Follow this link to install Docker for your operating system.

After installing Docker, run the command docker version to confirm that it's running.

Running MySQL

Launch the Docker Compose file by following this path in your resources.

The Docker Compose file configures the settings of a container.

1. The Docker container will run an instance of MySQL 8.0.
2. The MySQL instance will contain a database called db.
3. The MySQL credentials are user and password
4. The password for the default root user will also be password.
5. MySQL will run on port 3306.
6. The container will expose port 3306 to the outside.
7. MySQL data will be maintained within a volume that exists on your machine.

From Visual Studio Code, launch a terminal.

Make sure the path points to the folder that contains your docker-compose file.

Run the command docker compose up to start up the docker container.

The MySQL application running inside the container can be accessed on port 3306.

Launch the Spring Boot Project.

From another Visual Studio Code window, launch the starter project.

Add this dependency in order to connect your application to the running instance of MySQL.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

Make sure to add this dependency after Spring Data JPA. Otherwise, you may get a DataSourceConfiguration exception.

Inside your application.properties file, add these properties

spring.datasource.url=jdbc:mysql://localhost:3306/db
spring.datasource.username=user
spring.datasource.password=password
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

1. The Spring Boot application connects to the MySQL instance that's running on port 3306. It connects to the database: db.

2. The Spring Boot application uses the credentials user and password to authenticate.

3. Hibernate drops existing tables before creating new ones. In production, do not include this line because you do not want to destroy previous data during every re-launch. It's fine to keep it during development.

   • Side Note: Spring Data JPA uses the Hibernate implementation.

4. The docker container is running MySQL 8.0. We need to specify a dialect of MySQL8 so that hibernate generates appropriate SQL statements.

Run your application

Congratulations! You connected your Spring Boot application to a MySQL database. Feel free to make requests from Postman to your heart's content.

It's normal if you notice errors being thrown the first time you run the application. At first, ddl-auto = create attempts to drop existing tables from an empty database before creating new ones.

MySQL Workbench

Download the MySQL workbench from MySQL workbench. Once you download the workbench, open it and click the + icon.

Use the following values in order to set up a connection with the currently running instance of MySQL. The Connection Name can be anything.

Enter the password that corresponds to the username. Mac users: you might need to press Store in Keychain first.

Test your connection to ensure that it is successful.

Open the connection, and click on Schemas.

Here you can see the database db, and all of the tables that Spring Boot created.

Feel free to query the saved data:

SELECT * FROM database_name.table_name
/* Example: SELECT * FROM db.student */

On the top toolbar, press the first lightning button to run the query.

Results

Final Task

The docker engine can be heavy on your machine. Close it when you're done with it.

Mac

Windows

Don't worry, you won't lose any data. You can restart the docker engine by clicking the app.

Good Luck!