Run MySQL on Docker and use in your Java App
Tags: docker, container, mysql, java
Published: 2019-06-12 17:16:08

---
title: Run MySQL on Docker and use in your Java App
published: true
tags: docker, container, mysql, java
cover_image: https://thepracticaldev.s3.amazonaws.com/i/dcvumk4fgppm40vt6by6.png
---

Running MySQL in Docker is very useful to use in development. In this tutorial, we will see how easy it is to start MySQL in Docker and use it in our Java application with Spring Boot.

##Prepare

First, you need a docker runtime on you machine. If you don't have it, please install following this docker documentation: https://docs.docker.com/install/

##Start Mysql container

To start the container, type the command below in your terminal:
```bash
docker run --name mysql57 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 -d mysql/mysql-server:5.7
```
The command above, pulled image if not found in local repository and run mysql container in detached mode. Configure local port equals to container port and set root password to `1234`.

Check your container running:
```bash
docker ps
CONTAINER ID        IMAGE                 COMMAND                  CREATED             STATUS                   PORTS                               NAMES
380eca8a553c        mysql/mysql-server:5.7    "/entrypoint.sh mysq…"   8 minutes ago       Up 8 minutes (healthy)   0.0.0.0:3306->3306/tcp, 33060/tcp   mysql
```
As we can see, mysql5.7 is running on localhost:3306
At this point, you have a local mysql server without install it.

> **Note:** You will not have external access to the container with root user.

##Create a database
Type:
```bash
docker exec -it mysql57 bash
```
This command access the mysql container and allow to execute commands on database. Inside a container, type:
```bash
mysql -h localhost -u root -p
```
Remember, the password is `1234`. First, we need to create a user for out-of-container access because root access is not allowed:

```bash
CREATE USER 'demo_java' IDENTIFIED BY 'java';
grant all on *.* to 'demo_java'@'%' identified by '1234';
FLUSH PRIVILEGES;

```
To create a database, paste DDL bellow:

```bash
CREATE DATABASE hello_java CHARACTER SET utf8 COLLATE utf8_general_ci;
```
Next, type:
```bash
show databases;
+--------------------+
| Database           |
+--------------------+
| hello_java         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
```

##Create a database automatically
Another way to start a database and automatic create a `hello_java` database and `demo_java` user:
```bash
docker run --name mysql57 -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=1234 \
-e MYSQL_USER=demo_java \
-e MYSQL_PASSWORD=1234 \
-e MYSQL_DATABASE=hello_java \
-d mysql/mysql-server:5.7
```

With the above command, you do not have to enter the database and create a database and a user. Cool, is not it?

##Create Demo App
Go to https://start.spring.io/ and create a demo app with the *Web* and *Jpa* dependencies.

Import application in your IDE and follow steps below:

1 - Edit `application.properties` file:
```
spring.datasource.url=jdbc:mysql://localhost:3306/hello_java
spring.datasource.username=demo_java
spring.datasource.password=1234
spring.jpa.hibernate.ddl-auto=update

```
**spring.jpa.hibernate.ddl-auto=update** creates the `person` table if it doesn't exist.
> **Note:** It's not recommended to use in production.

2 - Edit `pom.xml` file and add mysql connector java
```xml
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
```

3 - Add domain, service and persistence classes:
**Domain**
![](https://thepracticaldev.s3.amazonaws.com/i/uh40cxp2k43lo2a2q9co.png)

**Repository**
![](https://thepracticaldev.s3.amazonaws.com/i/fsfx14mig774ae7szw96.png)

**Service**
![](https://thepracticaldev.s3.amazonaws.com/i/bl2ja92mcu7z9ao38ovy.png)

4 - Change main class to create two new Person:

![](https://thepracticaldev.s3.amazonaws.com/i/3tokyynlkp8qbiiq398i.png)

##Run Demo App
Start demo app just running a main class:

**Application is running and data is persistent in database**

![](https://thepracticaldev.s3.amazonaws.com/i/1o6o3i9u1ikog6rnq8la.png)

##Bonus

To expose your data as a Rest endpoint, just add this dependency:
```xml
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
```
Access http://localhost:8080/persons

And Wow! Your data exposed as json.

![](https://thepracticaldev.s3.amazonaws.com/i/7948avwkyfw19dt4f4wi.png)


##Source code
{% github sandrogiacom/database-demo %}