Configuring a JPA application with a MySQL Docker Container

I have a simple Jakarta Persistence/JPA application that runs with an embedded HyperSQL database. I wanted to change the application to use MySQL instead of HyperSQL but I didn’t have MySQL setup on my new Windows 10 machine. Multiple people recommended that I use MySQL Docker container instead of installing MySQL directly on my machine. It was a bit painful but I got it to work!

Creating the MySQL Docker Container:

0. If you haven’t yet, you’ll need to install Docker. I installed mine on my Windows 10 machine from here. After the installation, go to your command prompt and verify that Docker is installed by running docker version.

1. In Command Prompt, pull the latest MySQL image by running:

docker pull mysql/mysql-server:latest

Run docker images to verify that the image was pulled. You should see a mysql/mysql-server listed there.

2. Start your MySQL docker container and give it a name. I’m calling mine “mySQLContainer”

docker run --name=mySQLContainer -p 3306:3306 -d mysql/mysql-server:latest

Run docker ps to verify that your container is running. You should see mySQLContainer listed there.

3. Go to your docker container logs and save the randomly generated password (you’ll need it in the next step):

docker logs mySQLContainer

Look for a line that mentions a generated password. Should look something like: [Entrypoint] GENERATED ROOT PASSWORD: %password% and SAVE the value for %password%.

4. Let’s start our MySQL client inside our Docker container:

docker exec -it mySQLContainer mysql -uroot -p

When prompted to enter a password, enter the password you copied in the previous step and press enter.

5. I’m going to change my password to something simpler for now:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Setting up MySQL for the JPA application:

Now that I have a MySQL container, it’s time to create everything my application needs to connect to the database.

1. I will create a database and give it a name. I’m calling mine ‘myDB’:

CREATE DATABASE myDB;

Run SHOW DATABASES to verify the database was created. You should see myDB listed there.

2. I will create a user and password and give them additional privileges (this step caused me a lot of pain which is why I decided to write this blog).

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'user'@'%';
FLUSH PRIVILEGES;

Run SELECT host, user FROM mysql.user; to verify that you have an entry for | % | user | in your table. Otherwise, you’ll get a bunch of Access denied for user errors and spend hours debugging it (ask me how I know).

Configuring the JPA application to use the new MySQL database:

1. In your JPA application’s persistence.xml file, you’ll need to configure your application to point at your new MySQL database. You can clone this repo, if you want to experiment with a simple project.

• JPA 2.2 and earlier configuration example (javax namespace):

<property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/myDB"/>
<property name="javax.persistence.jdbc.user" value="user"/>
<property name="javax.persistence.jdbc.password" value="password"/>

• Jakarta Persistence 3.0 and later configuration example (jakarta namespace):

<property name="jakarta.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="jakarta.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/myDB"/>
<property name="jakarta.persistence.jdbc.user" value="user"/>
<property name="jakarta.persistence.jdbc.password" value="password"/>

2. Add the mysql driver to your dependency list.

• If you’re using Maven, add the following to the pom.xml file:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>

3. Run your application with your new configuration. If you’re using my repo:

• Run the Main class (The application persists a simple Employee entity in the database)
• Verify that the data was persisted successfully by going to your command prompt connected to your mysql client and running:

USE myDB;
SHOW TABLES;

• You should see an Employee table. Then, you can view the persisted data by running:

SELECT * FROM EMPLOYEE;

Output should be:

+----+-----------+-------------+
| ID | FIRSTNAME | LASTNAME    |
+----+-----------+-------------+
|  1 | Dalia     | Abo Sheasha |
+----+-----------+-------------+

I hope this helped!