Access MySQL on a Docker container from a local (host OS) Java program

Introduction

My tech stack feels too legacy and pathetic I want to break away from the legacy uncle and start again as an engineer I started studying. Thank you & # x1f600;

So I decided to study Docker first.

What to build (this goal)

• Diagram

--Software information OS：Windows10 Docker：19.03.12 Program：Java11 MySQL：8.0.21 Adminer：latest WebBrowser: Anything

For the time being, this time from my own Java program on the host OS Challenge from accessing the DB on the container.

Construction work

1. Create Docker container

Prepared by slightly arranging docker-compose.yml on the MySQL explanation page of Docker Hub. DockerHub MySQL Page

# Use root/example as user/password credentials
version: '3.1'

services:

  db:
    image: mysql:8.0.21 #Change
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: hoge #add to
      MYSQL_USER: hoge     #add to
      MYSQL_PASSWORD: hoge #add to
    ports:                 #add to
      - 3306:3306          #add to

  adminer:
    image: adminer:latest
    restart: always
    ports:
      - 8080:8080

The part with # is the arrangement part. I felt that the version of the MySQL image would change if I specified latest, so I specified 8.0.21. This time it is accessed from outside the container, so Add an appropriate DB and set port 3306 to be exposed to the outside.

In the same folder as the above configuration file Start with the docker-compose command.

docker-compose up

Since it is running in the foreground, while watching the log appearing Check if it is up on another console

docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
0c5d267f22ee        mysql:8.0.21        "docker-entrypoint.s…"   26 seconds ago      Up 25 seconds       0.0.0.0:3306->3306/tcp, 33060/tcp   mysql_db_1
258f5885810d        adminer:latest      "entrypoint.sh docke…"   26 seconds ago      Up 25 seconds       0.0.0.0:8080->8080/tcp              mysql_adminer_1

(´ ・ ω ・ ｀) Oh

2. Create tables and data with Adminer

Adminer seems to be a Web client that can operate DB made with PHP. With the MySQL and Adminer containers running http://localhost:8080/ When you access, the following screen will appear Enter the DB and user information described in docker-compose.yml earlier.

Then with a suitable table like this

Insert an appropriate record like this and the DB is complete.

3. Java program creation

First, describe the MySQL driver dependencies in gradle.

apply plugin: 'java'
apply plugin: 'application'
apply plugin: 'eclipse'

repositories {
    mavenCentral()}

dependencies {
	compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.21'
    testImplementation 'junit:junit:4.12'
}

Then create a class that throws a SQL query to MySQL and displays the result.

public class DockerMySQLAccessor {

	public static void main(String[] args) {

		System.out.println("Access MySQL in the Docker container");

		try(Connection con = DriverManager.getConnection(
			    "jdbc:mysql://localhost:3306/hoge?useSSL=false",
			    "hoge", // UserId
			    "hoge"  // Password
			  )) {

			PreparedStatement pstmt = con.prepareStatement("select * from TEST_TBL");
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				System.out.printf("ID:%d, HOGE:%s \n", rs.getInt("ID"), rs.getString("HOGE"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

4. Run

Execute the Java code from Eclipse. You can get the record you just put in! I did it. (´ ・ ω ・ ｀) v

Summary

For the time being, easily set up a virtual DB server using Docker, It could be accessed from a local program.

This time it's a Docker touch The settings are almost full copy and suddenly I used docker-compose In actual operation, do you create a Dockerfile for each container and create an image? Also, I want to run the program on the container and communicate between the containers. This time around here again.

Acknowledgments

It was a great reference for putting Docker in Win10. Thanks <(_ _)> Introduction to Docker by Legacy Engineer