[SpringBoot] MySQL/Maria 연결하기

MySqlRunner.zip

0.09MB

IDE : IntelliJ

JDK : OpenJDK 11

Framework : Spring Boot 2.5.2

Database : MySql 최신버전 ( Oracle 에 라이센스비를 지급해야 하고, 소스공개의 의무가 존재함 )

              Maria 최신버전 ( 소스공개의 의무가 존재함 )

---

Docker 설치

https://hermeslog.tistory.com/497?category=302346 

[Docker] Docker의 설치

Docker에 MySQL 설치

? docker에 mysql 이미지 설치
$>docker pull mysql 

? Docker 컨테이너 생성
? -d : 백그라운드 실행
? -p 포트 포딩 5432번 포트로 들어온 내용은 5432 번 포트로 포딩을 해주겟다는 내용
? -e 환경변수 설정 POSTRGRES_PASSWORD= 내용을 password로 설정
? --name Container 이름을 설정해줍니다.
? docker exec -> run a command on an existing/running container
? docker run -> create a new container from an image, and run the command there
$>docker run -d -p 3306:3306 --name mysql_boot -e MYSQL_ROOT_PASSWORD=1 -e MYSQL_DATABASE=springboot -e MYSQL_USER=hermeswing -e MYSQL_PASSWORD=pass -d mysql

? 생성된 Docker 컨테이너 확인
$>docker ps -a

? Docker 컨테이너 실행
$>docker exec -it mysql_boot bash
? mysql version 확인
#>mysql -v
? mysql 접속 password : 1
#>mysql -u root -p

? Docker 컨테이너 멈춤
$>docker stop mysql_boot

? Docker 컨테이너 삭제
$>docker rm mysql_boot

---

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.mysql</groupId>
    <artifactId>MySqlRunner</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>MySqlRunner</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

package com.mysql.mysqlrunner.runner;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;

@Component
public class MySQLRunner implements ApplicationRunner {
    @Autowired
    DataSource dataSource;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void run( ApplicationArguments args ) {
        try {
            Connection connection = dataSource.getConnection();
            System.out.println( connection.getMetaData().getClass() );
            System.out.println( connection.getMetaData().getURL() );
            System.out.println( connection.getMetaData().getUserName() );

            Statement statement = connection.createStatement();
            String sql = "CREATE TABLE USER(ID INTEGER NOT NULL, NAME VARCHAR(255), PRIMARY KEY(ID))";
            statement.executeUpdate( sql );

            jdbcTemplate.execute( "INSERT INTO USER VALUES (1, 'hermeswing')" );

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

server.port=9090

spring.datasource.hikari.maximum-pool-size=4

spring.datasource.url=jdbc:mysql://localhost:3306/springboot?allowPublicKeyRetrieval=true
spring.datasource.username=hermeswing
spring.datasource.password=pass

---

MySQLRunner 실행결과

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v2.5.2)

2021-07-18 13:38:50.394  INFO 11432 --- [           main] c.m.mysqlrunner.MySqlRunnerApplication   : Starting MySqlRunnerApplication using Java 11 on DESKTOP-HMJ6ST0 with PID 11432 (C:\JetBrains\IdeaProjects\MySqlRunner\target\classes started by hermeswing in C:\JetBrains\IdeaProjects\MySqlRunner)
2021-07-18 13:38:50.404  INFO 11432 --- [           main] c.m.mysqlrunner.MySqlRunnerApplication   : No active profile set, falling back to default profiles: default
2021-07-18 13:38:51.840  INFO 11432 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 9090 (http)
2021-07-18 13:38:51.851  INFO 11432 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2021-07-18 13:38:51.851  INFO 11432 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.48]
2021-07-18 13:38:51.951  INFO 11432 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2021-07-18 13:38:51.951  INFO 11432 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1447 ms
2021-07-18 13:38:52.385  INFO 11432 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 9090 (http) with context path ''
2021-07-18 13:38:52.397  INFO 11432 --- [           main] c.m.mysqlrunner.MySqlRunnerApplication   : Started MySqlRunnerApplication in 2.765 seconds (JVM running for 5.276)
2021-07-18 13:38:52.400  INFO 11432 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-07-18 13:38:52.681  INFO 11432 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
class com.zaxxer.hikari.pool.HikariProxyDatabaseMetaData
jdbc:mysql://localhost:3306/springboot?allowPublicKeyRetrieval=true
hermeswing@172.17.0.1

---

MySQL 접속 및 데이터 확인

root@faf646b731a5:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| springboot         |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use springboot
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_springboot |
+----------------------+
| USER                 |
+----------------------+
1 row in set (0.00 sec)

mysql> drop table USER;
Query OK, 0 rows affected (0.08 sec)

---

Docker에 Maria 설치

 

? docker에 mysql 이미지 설치
$>docker pull mariadb 

? Docker 컨테이너 생성
$>docker run -d -p 3306:3306 --name maria_boot -e MYSQL_ROOT_PASSWORD=1 -e MYSQL_DATABASE=springboot -e MYSQL_USER=hermeswing -e MYSQL_PASSWORD=pass -d mariadb

? 생성된 Docker 컨테이너 확인
$>docker ps -a

? Docker 컨테이너 실행
$>docker exec -it maria_boot bash
#>mysql -u root -p

? Docker 컨테이너 멈춤
$>docker stop maria_boot

? Docker 컨테이너 삭제
$>docker rm maria_boot

MySQL 처럼 allowPublicKeyRetrieval=true 를 붙이지 않아도 됩니다.

server.port=9090

spring.datasource.hikari.maximum-pool-size=4

spring.datasource.url=jdbc:mysql://localhost:3306/springboot
spring.datasource.username=hermeswing
spring.datasource.password=pass

위의 코드를 실행해도 그대로 실행됩니다.

root@6d01bc3192d4:/# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.6.3-MariaDB-1:10.6.3+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use springboot
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [springboot]> show tables;
+----------------------+
| Tables_in_springboot |
+----------------------+
| USER                 |
+----------------------+
1 row in set (0.000 sec)

MariaDB [springboot]> quit
Bye

MariaDB [springboot]>

---

참고 : 백기선님의 스프링 부트 개념과 활용