[SpringBoot] H2 Database 연결하기

H2Runner.zip

0.10MB

내용은 간단합니다.

IDE : IntelliJ

JDK : OpenJDK 11

Framework : Spring Boot 2.5.2

---

<?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.h2</groupId>
    <artifactId>H2Runner</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>H2Runner</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-security</artifactId>
        </dependency>
        <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>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-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.h2.h2runner;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class H2RunnerApplication {

    public static void main( String[] args ) {
        SpringApplication.run( H2RunnerApplication.class, args );
    }

}

package com.h2.h2runner.runner;

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

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

@Component
public class H2Runner implements ApplicationRunner {

    @Autowired
    DataSource dataSource;

    @Override
    public void run( ApplicationArguments args ) {
        try {
            Connection connection = dataSource.getConnection();
            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 );


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

#application.properties

server.port=9090
spring.h2.console.enabled=true

 

정상적으로 Spring Boot 가 정상적으로 시작되었습니다.

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

2021-07-17 12:39:31.557  INFO 14876 --- [           main] com.h2.h2runner.H2RunnerApplication      : Starting H2RunnerApplication using Java 11 on DESKTOP-HMJ6ST0 with PID 14876 (C:\JetBrains\IdeaProjects\H2Runner\target\classes started by hermeswing in C:\JetBrains\IdeaProjects\H2Runner)
2021-07-17 12:39:31.562  INFO 14876 --- [           main] com.h2.h2runner.H2RunnerApplication      : No active profile set, falling back to default profiles: default
2021-07-17 12:39:32.811  INFO 14876 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 9090 (http)
2021-07-17 12:39:32.821  INFO 14876 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2021-07-17 12:39:32.821  INFO 14876 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.48]
2021-07-17 12:39:32.921  INFO 14876 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2021-07-17 12:39:32.921  INFO 14876 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1285 ms
2021-07-17 12:39:32.951  INFO 14876 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-07-17 12:39:33.082  INFO 14876 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2021-07-17 12:39:33.087  INFO 14876 --- [           main] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:9d4f4e79-019d-4657-979d-206a8ca618a2'
2021-07-17 12:39:33.461  INFO 14876 --- [           main] .s.s.UserDetailsServiceAutoConfiguration : 

Using generated security password: 0e47fb0e-d426-4f95-a40c-94c73fd8a969

2021-07-17 12:39:33.571  INFO 14876 --- [           main] o.s.s.web.DefaultSecurityFilterChain     : Will secure any request with [org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter@40bf4386, org.springframework.security.web.context.SecurityContextPersistenceFilter@2233cac0, org.springframework.security.web.header.HeaderWriterFilter@12417468, org.springframework.security.web.csrf.CsrfFilter@2519026b, org.springframework.security.web.authentication.logout.LogoutFilter@6d6f6860, org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter@4b87074a, org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter@2a4f8009, org.springframework.security.web.authentication.ui.DefaultLogoutPageGeneratingFilter@14d25b6e, org.springframework.security.web.authentication.www.BasicAuthenticationFilter@56826a75, org.springframework.security.web.savedrequest.RequestCacheAwareFilter@787e4357, org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter@8e25d3f, org.springframework.security.web.authentication.AnonymousAuthenticationFilter@51c65a43, org.springframework.security.web.session.SessionManagementFilter@d325518, org.springframework.security.web.access.ExceptionTranslationFilter@359066bc, org.springframework.security.web.access.intercept.FilterSecurityInterceptor@5934ca1e]
2021-07-17 12:39:33.645  INFO 14876 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 9090 (http) with context path ''
2021-07-17 12:39:33.657  INFO 14876 --- [           main] com.h2.h2runner.H2RunnerApplication      : Started H2RunnerApplication in 2.762 seconds (JVM running for 5.079)
jdbc:h2:mem:9d4f4e79-019d-4657-979d-206a8ca618a2
SA
2021-07-17 12:43:18.915  INFO 14876 --- [nio-9090-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2021-07-17 12:43:18.916  INFO 14876 --- [nio-9090-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2021-07-17 12:43:18.917  INFO 14876 --- [nio-9090-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 1 ms
2021-07-17 12:43:20.313  WARN 14876 --- [nio-9090-exec-1] o.a.c.util.SessionIdGeneratorBase        : Creation of SecureRandom instance for session ID generation using [SHA1PRNG] took [1,377] milliseconds.

http://localhost:9090/h2-console 접속

 

정상적으로 접속 되길 기대했습니다. ㅠ.ㅠ

---

그런데 결과는 아래와 같습니다.

http://localhost:9090/login

이런 화면이 뜨면서 접속이 안됩니다.

---

원인은 Spring-Security 입니다.

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

Project를 생성하면서 Spring Security가 추가되어 있었습니다.

Spring Security는 H2의 접근을 막을 수 있다고 합니다.

그래서 "/h2-console" 의 모든 경로를 Spring Security에서 무시할 수 있도록 해줘야 합니다.

package com.h2.h2runner.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.builders.WebSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;

@Configuration
@EnableWebSecurity
public class WebSecurityConfiguration extends WebSecurityConfigurerAdapter { // ...
    @Override
    public void configure( WebSecurity web ) throws Exception {
        web.ignoring().antMatchers( "/h2-console/**" );
    }

    @Override
    protected void configure( HttpSecurity httpSecurity ) throws Exception {
        httpSecurity.authorizeRequests() // 권한요청 처리 설정 메서드
            .antMatchers( "/h2-console/**" ).permitAll() // 누구나 h2-console 접속 허용
            .and() // ...
            .csrf().ignoringAntMatchers( "/h2-console/**" ).disable(); // GET메소드는 문제가 없는데 POST메소드만 안되서 CSRF 비활성화 시킴
    }
}

---

서버를 재시작하고, http://localhost:9090/h2-console 접속했습니다.

URL : jdbc:h2:mem:testdb
Driver Class : org.h2.Driver
User Name : sa
Password : "" (없어요.)

기대를 했는데 아래와 같이 접속이 안됩니다.

---

최신 H2 는 보안상의 이유로 기본DB를 생성하지 않는다고 합니다.

application.properties 파일에 아래의 내용을 추가해야 합니다.

server.port=9090
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

USER 테이블도 확인해볼 수 있습니다.

---

H2 Console 에 접속이 안된다면

1. application.properties 에 아래 구문이 추가되어 있는지 확인해보세요.

spring.h2.console.enabled=true

2. Spring Security 가 포함되어 있지 않은지 확인해보세요.

3. 보안상의 이유로 기본 DB를 생성하지 않는다고 합니다.

4. AWS EC2 환경에서는 접속이 안될 수 있는 모양입니다.

- 아래 참고 URL 을 확인해주세요.

---

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

https://moonsiri.tistory.com/33

[springboot] h2-console 접속이 안될 경우 문제 해결

https://galid1.tistory.com/611

SpringBoot - SpringBoot H2 연결방법(H2 웹콘솔)