Learn to Connect to a Mysql Database Running on Docker

1.) Introduction

This blog is an extension of  https://czetsuya-tech.blogspot.com/2020/05/jdbc-tutorial-connecting-to-mysql-running-on-docker.html where we discuss how to connect to a MySQL instance using JDBC.

In this article, we will demonstrate how we can run a MySQL instance on docker and connect a standalone Java application on it using JPA.

While this exercise is done using a MySQL instance running on Docker, it can be run in a locally installed MySQL database as well.

2.) Prerequisites

Prior knowledge of existing technologies such as virtualization, eclipse ide is needed to follow this exercise. For those who are not familiar with these technologies, I recommend visiting the reference section below that points to various tutorials I have written related to it.

• Eclipse IDE
• GIT
• MySQL Workbench Community
• Docker

2.1) Running MySQL on Docker

Docker is a virtualization tool that we can use to run an application such as MySQL without actually installing it.

To install Docker, follow the guide in the link in the reference section.

To run docker, open your terminal and execute:

docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=kerri -e MYSQL_DATABASE=catalog mysql

2.2) MySQL Workbench Installation

WorkBench installation is straight-forward for Windows, you just have to download the installer from the link below and connect to your database.

On the other hand, there are some configurations/installations that you need to perform in Debian machines to install it. See the link in the reference section below.

Once the WorkBench is running you can then connect to your database with the password you entered when you run MySQL with docker. In our case, we used 'kerri'.

By default, the database catalog will be created. Double click on it in the Schema tab so that it is selected and then press the "Create a new SQL tab..." button and enter the script in "src/main/resources/1 - schema.sql" to create our product table.

3.) Cloning the Project

You can either clone the project using the Eclipse EGIT plugin or via terminal.

Terminal: git clone https://github.com/czetsuya/jdbc-jpa-code-demonstration.

4.) Before Running the Tests

Note that we need to change the server configuration first.

 - JPA - src/main/resources/META-INF/persistence.xml

4.1) What to Change?

Open the file src/main/resources/META-INF/persistence.xml

1. Change the IP address of the database server in the property javax.persistence.jdbc.url.

2. Make sure to set the correct username and password properties. javax.persistence.jdbc.user and javax.persistence.jdbc.password.

3.) Make sure that the persistence unit defined in persistence.xml file is the one use in the PersistenceManager file.

5.) Running the Tests

There are various tests that are demonstrated in this project.

5.1) JpaMysqlDemo

Did you hear about hibernate? It's an ORM or Object Relational Mapping tool for Java which maps an object-oriented domain to a relational database as is demonstrated here.

In our first example JdbcTemplateMysqlDemo, we are using SQL statements to manipulate a record in the database. By using hibernate we will be using an object instead, which makes it easier.

7.) References

• https://github.com/czetsuya/jdbc-jpa-code-demonstration
• Git Tutorial
• https://czetsuya-tech.blogspot.com/2020/05/how-to-install-mysql-workbench-in-ubuntu.html
• https://czetsuya-tech.blogspot.com/2019/12/learn-java-programming-for-beginners.html
• https://czetsuya-tech.blogspot.com/2019/10/eclipse-plugins-for-java-developer.html
• https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#domain-model
• https://hub.docker.com/_/mysql
• https://www.mysql.com/products/workbench/
• https://docs.docker.com/docker-for-windows/install/