かんがるーさんの日記

最近自分が興味をもったものを調べた時の手順等を書いています。今は Spring Boot をいじっています。

Spring Boot + npm + Geb で入力フォームを作ってテストする ( その１５ )( Flyway のインストール + Spring Security 使用時に H2 Console に接続する + IntelliJ IDEA の Database Tools で in-memory モードの H2 Database に接続する )

概要

記事一覧はこちらです。

Spring Boot + npm + Geb で入力フォームを作ってテストする ( その１４ )( browser-sync –> Tomcat 連携してファイル変更時に自動リロードで反映される環境を構築してみる ) の続きです。

  • 今回の手順で確認できるのは以下の内容です。
    • 本当は Flyway をインストールして H2 Database にテーブルを作成するのをさっと終わらせるだけのつもりでいたのですが、他のことで以外に苦労しました。
    • 以下の内容を記述しています。
      • Flyway のインストールと、Tomcat 起動時の Flyway の動作の確認
      • Spring Security を使用している時に H2 Console に接続する方法
      • IntelliJ IDEA の Database Tools で、Spring Boot の Tomcat から in-memory モードで起動した H2 Database に接続する方法
    • Flyway で H2 Database にテーブルを作成するのは次回に持ち越しです。

参照したサイト・書籍

  1. Flyway
    https://flywaydb.org/

  2. H2 Database Engine
    http://www.h2database.com/html/main.html

  3. Spring Boot Reference Guide - 78. Database initialization - 78.5.1 Execute Flyway database migrations on startup
    https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html#howto-execute-flyway-database-migrations-on-startup

  4. Spring Boot Flyway Sample
    https://github.com/spring-projects/spring-boot/tree/v1.5.6.RELEASE/spring-boot-samples/spring-boot-sample-flyway

  5. SpringBoot：H2DBに接続
    http://web-dev.hatenablog.com/entry/spring-boot/intro/connect-h2db

  6. Spring Boot /h2-console throws 403 with Spring Security 1.5.2
    https://stackoverflow.com/questions/43794721/spring-boot-h2-console-throws-403-with-spring-security-1-5-2

  7. H2 Database Console
    https://springframework.guru/using-the-h2-database-console-in-spring-boot-with-spring-security/

  8. Connect to H2 database using IntelliJ database client
    https://stackoverflow.com/questions/28940912/connect-to-h2-database-using-intellij-database-client

  9. QUERYING THE EMBEDDED H2 DATABASE OF A SPRING BOOT APPLICATION
    https://techdev.de/querying-the-embedded-h2-database-of-a-spring-boot-application/

  10. org.h2.jdbc.JdbcSQLException: Connection is broken: “unexpected status 5505072”
    https://groups.google.com/forum/#!topic/h2-database/qZ5bpboIC6U

目次

  1. Flyway をインストールする
  2. Tomcat を起動してみるが起動せず。。。connection pool の initialSize を 1→2 に変更する
  3. Tocmat 起動時の Flyway の動作を見てみる
  4. IntelliJ IDEA の Database Tools から H2 Database へ接続する。。。が何かおかしい
  5. H2 Console に接続する
  6. in-memory モードで起動している H2 Database に別プロセスから接続するには？
  7. 再び Database Tools から H2 Database へ接続してみる

手順

Flyway をインストールする

Spring IO Platform Reference Guide を見ると org.flywaydb:flyway-core の記述がありますが、バージョンは 3.2.1 でした。Flyway を見ると最新バージョンは 4.2.0 でしたので、バージョン番号を指定して 4.2.0 をインストールします。

build.gradle を以下のように変更します。

dependencies {
    ..........

    // dependency-management-plugin によりバージョン番号が自動で設定されないもの、あるいは最新バージョンを指定したいもの
    compile("com.integralblue:log4jdbc-spring-boot-starter:1.0.1")
    compile("org.flywaydb:flyway-core:4.2.0")
    testCompile("org.dbunit:dbunit:2.5.3")
    testCompile("com.icegreen:greenmail:1.5.5")
    testCompile("org.assertj:assertj-core:3.8.0")
    testCompile("org.spockframework:spock-core:${spockVersion}")
    testCompile("org.spockframework:spock-spring:${spockVersion}")
    testCompile("com.google.code.findbugs:jsr305:3.0.2")

    ..........
}
  • dependencies に compile("org.flywaydb:flyway-core:4.2.0") を追加します。

変更後、Gradle Tool Window の左上にある「Refresh all Gradle projects」ボタンをクリックして更新します。

Tomcat を起動してみるが起動せず。。。connection pool の initialSize を 1→2 に変更する

Flyway をインストールしただけの状態で Tomcat が起動するか確認します。

Tomcat を起動すると Caused by: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [main] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:1; busy:1; idle:0; lastwait:30000]. というエラーログが出力されて起動できませんでした。application.properties に spring.datasource.tomcat.initialSize=1 と設定していますが、1 つでは足りないようです。

f:id:ksby:20170813150540p:plain

initialSize の数値を変更して試してみたところ、最低 2 で設定していないと起動できませんでした。application.properties、application-product.properties の設定を以下のように変更します。

spring.datasource.tomcat.initialSize=2
spring.datasource.tomcat.maxActive=2
spring.datasource.tomcat.maxIdle=2
spring.datasource.tomcat.minIdle=2

再度試すと Tomcat が無事起動しました。

f:id:ksby:20170813151035p:plain

Tocmat 起動時の Flyway の動作を見てみる

Log4jdbc Spring Boot Starter により Tomcat 起動時に Flyway で実行される SQL 文がコンソールに出力されていたので、追ってみました。

  1. Connection.new
  2. CALL SCHEMA()
  3. SELECT COUNT(*) FROM INFORMATION_SCHEMA.schemata WHERE schema_name=‘PUBLIC’
  4. SET SCHEMA “PUBLIC”
  5. SELECT COUNT(*) FROM INFORMATION_SCHEMA.schemata WHERE schema_name=‘PUBLIC’
  6. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLEs WHERE TABLE_schema = ‘PUBLIC’ AND TABLE_TYPE = ‘TABLE’
  7. CALL SCHEMA()
  8. CREATE TABLE “PUBLIC”.“schema_version” ( “installed_rank” INT NOT NULL, “version” VARCHAR(50), “description” VARCHAR(200) NOT NULL, “type” VARCHAR(20) NOT NULL, “script” VARCHAR(1000) NOT NULL, “checksum” INT, “installed_by” VARCHAR(100) NOT NULL, “installed_on” TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, “execution_time” INT NOT NULL, “success” BOOLEAN NOT NULL )
  9. ALTER TABLE “PUBLIC”.“schema_version” ADD CONSTRAINT “schema_version_pk” PRIMARY KEY (“installed_rank”)
  10. CREATE INDEX “PUBLIC”.“schema_version_s_idx” ON “PUBLIC”.“schema_version” (“success”)
  11. select * from “PUBLIC”.“schema_version” for update
  12. SELECT “installed_rank”,“version”,“description”,“type”,“script”,“checksum”,“installed_on”,“installed_by”,“execution_time”,“success” FROM “PUBLIC”.“schema_version” WHERE “installed_rank” > -1 ORDER BY “installed_rank”
  13. SET SCHEMA “PUBLIC”
  14. Connection.close()

schema_version というテーブルがないと create table しています。おそらくこのテーブルでどのバージョンの SQL まで実行済なのかを管理しているのでしょう。

“flyway schema_version” で Google で検索すると How Flyway works のページがヒットしました。中の記述を読むと確かに schema_version というテーブルでバージョン管理されていました。

IntelliJ IDEA の Database Tools から H2 Database へ接続する

IntelliJ IDEA の Database Tool Window の左上の「＋」ボタンをクリックして「Data Source」-「H2」を選択します。

f:id:ksby:20170813194830p:plain

「Data Sources and Drivers」ダイアログが表示されますので、以下の値を入力して「OK」ボタンをクリックします。

f:id:ksby:20170813195119p:plain

  • URL に jdbc:h2:mem:bootnpmgebdb を入力します。
  • 「Test Connection」ボタンをクリックして “Successful” が表示されることを確認します。

f:id:ksby:20170813195330p:plain

  • 「Schemas」タブに切り替えて「All schemas」をチェックします。

Database Tool Window に戻ると「bootnpmgebdb」が追加されています。が、Flyway が PUBLIC の下に schema_version テーブルを作成したはずなのに何も表示されていませんね。。。？

f:id:ksby:20170813195555p:plain

H2 Console に接続する

H2 Database には H2 Console という Web アプリケーションがありますので、そちらに接続しても schema_version テーブルが見えないのか確認してみます。

H2 Console はデフォルトでは起動していないので、src/main/resources/application-develop.properties の最後に以下の設定を追加して develop 環境の時だけ起動するようにします。

spring.h2.console.enabled=true

Tomcat を再起動してからブラウザで http://localhost:8080/h2-console にアクセスすると、Basic 認証のダイアログ？が表示されました。これは Spring Security でしょうか？

f:id:ksby:20170813225043p:plain

Web で調べてみると以下の記事が見つかりました。

まずは Spring Security の Basic 認証を無効にします。src/main/resources/application-develop.properties に以下の設定を追加します。

spring.h2.console.enabled=true
security.basic.enabled=false
  • security.basic.enabled=false を追加します。

次に src/main/java/ksbysample/webapp/bootnpmgeb/config/WebSecurityConfig.java を以下のように変更します。

package ksbysample.webapp.bootnpmgeb.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.security.SecurityProperties;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;

import java.util.regex.Pattern;

/**
 * ???
 */
@Configuration
@Order(SecurityProperties.ACCESS_OVERRIDE_ORDER)
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {

    private static final Pattern DISABLE_CSRF_TOKEN_PATTERN = Pattern.compile("(?i)^(GET|HEAD|TRACE|OPTIONS)$");
    private static final Pattern H2_CONSOLE_URI_PATTERN = Pattern.compile("^/h2-console");

    @Value("${spring.h2.console.enabled:false}")
    private boolean springH2ConsoleEnabled;

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
                // 認証の対象外にしたいURLがある場合には、以下のような記述を追加します
                // 複数URLがある場合はantMatchersメソッドにカンマ区切りで対象URLを複数列挙します
                // .antMatchers("/country/**").permitAll()
                //
                // この Web アプリケーションでは Spring Security を CSRF対策で使用したいだけなので、
                // 全ての URL を認証の対象外にする
                .antMatchers("/**").permitAll()
                .anyRequest().authenticated();

        // spring.h2.console.enabled=true に設定されている場合には H2 Console を表示するために必要な設定を行う
        if (springH2ConsoleEnabled) {
            http.csrf()
                    .requireCsrfProtectionMatcher(request -> {
                        if (DISABLE_CSRF_TOKEN_PATTERN.matcher(request.getMethod()).matches()) {
                            // GET, HEAD, TRACE, OPTIONS は CSRF対策の対象外にする
                            return false;
                        } else if (H2_CONSOLE_URI_PATTERN.matcher(request.getRequestURI()).lookingAt()) {
                            // H2 Console は CSRF対策の対象外にする
                            return false;
                        }
                        return true;
                    });
            http.headers().frameOptions().sameOrigin();
        }
    }

}
  • private static final Pattern DISABLE_CSRF_TOKEN_PATTERN = Pattern.compile("(?i)^(GET|HEAD|TRACE|OPTIONS)$"); を追加します。
  • private Pattern H2_CONSOLE_URI_PATTERN = Pattern.compile("^/h2-console"); を追加します。
  • @Value("${spring.h2.console.enabled:false}") private boolean springH2ConsoleEnabled; を追加します。
  • configure メソッド内に if (springH2ConsoleEnabled) { ... } の処理を追加します。/h2-console の URI に対する CSRF対策を無効にし、X-Frame-Options レスポンスヘッダに DENY ではなく SAMEORIGIN がセットされるようにします。

再び Tomcat を再起動してからブラウザで http://localhost:8080/h2-console にアクセスすると今度はログイン画面が表示されました。

f:id:ksby:20170814003240p:plain

「JDBC URL」に jdbc:h2:mem:bootnpmgebdb を、「User Name」に sa を入力して「Connect」ボタンをクリックします。

H2 Console の画面が表示されました。画面左側を見ると schema_version テーブルが表示されています。

f:id:ksby:20170814003458p:plain

Database Tools で接続しても schema_version テーブルが見えない原因がなんとなく分かりました。おそらく起動しているプロセスが別なので、見ている in-memory データベースが別々になっているからですね。

in-memory モードで起動している H2 Database に別プロセスから接続するには？

そんな方法があるのかな。。。と思いつつ Web で調べてみると、以下の記事が見つかりました。

これらの記事によると TCP サーバを起動して接続すればいいらしいです。試してみます。

src/main/java/ksbysample/webapp/bootnpmgeb/config の下に H2DatabaseConfig.java を新規作成し、以下の内容を記述します。

package ksbysample.webapp.bootnpmgeb.config;

import org.h2.tools.Server;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;

import java.sql.SQLException;

/**
 * Tomcat 内で in-memory モードで起動した H2 Database に外部のプロセスから接続するための
 * TCP サーバを起動する JavaConfig クラス。develop 環境でのみ起動する。
 */
@Configuration
@Profile("develop")
public class H2DatabaseConfig {

    // TCP port for remote connections, default 9092
    @Value("${h2.tcp.port:9092}")
    private String h2TcpPort;

    /**
     * TCP connection to connect with SQL clients to the embedded h2 database.
     * Connect to "jdbc:h2:tcp://localhost:9092/mem:testdb", username "sa", password empty.
     */
    @Bean
    public Server h2TcpServer() throws SQLException {
        return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", h2TcpPort).start();
    }

}
  • TCP サーバだけあればよいので、Web サーバは記述していません。
  • develop 環境でのみ起動します。

Tomcat を再起動します。

H2 Console から接続を試みてみます。「JDBC URL」に jdbc:h2:tcp://localhost:9092/mem:bootnpmgebdb と入力して「Connect」ボタンをクリックすると、

f:id:ksby:20170814072125p:plain

接続できました。scheme_version テーブルも表示されています。

f:id:ksby:20170814072245p:plain

再び Database Tools から H2 Database へ接続してみる

Database Tool Window から「Data Sources and Drivers」ダイアログを表示して、以下のように設定しますが。。。

f:id:ksby:20170815001348p:plain

  • 「URL」の右側のドロップダウンリストで「Remote」を選択した後、jdbc:h2:tcp://localhost:9092/mem:bootnpmgebdb を入力します。
  • 「User」に sa を入力します。
  • 「Test Connection」ボタンをクリックして “Successful” のメッセージが表示されることを確認します。

「Test Connection」は成功しているのに、ダイアログの下に「Error: [90067][90067]接続が壊れています: “unexpected status 256"」のメッセージが表示されます。「Database」のところに mem:bootnpmgebdb ではなく mem としか表示されていないからでしょうか。。。

「Schemas」タブをクリックすると何もスキーマが表示されていません。

f:id:ksby:20170815001746p:plain

「Data Sources and Drivers」ダイアログの設定をしばらく見ていたら「URL」の右側のドロップダウンリストに「URL only」という選択肢があることに気づきました。

f:id:ksby:20170815002414p:plain

「URL only」で設定すると今度は「URL」のところに赤縦線は表示されず「Test Connection」も成功しているのですが、「接続が壊れています」のメッセージが相変わらず表示されています。

f:id:ksby:20170815002557p:plain

原因がまるで分かりません。。。

Web でエラーメッセージからいろいろ調べてみたら以下のやり取りを見つけました。サーバとクライアントで H2 のバージョンが違うからではないか？、と書かれていますね。

確認してみます。Project Tool Window の「External Libraries」でサーバのバージョンを見ると 1.4.195 でした。

f:id:ksby:20170815003730p:plain

Database Tool Window から「Data Sources and Drivers」ダイアログを表示して H2 のクライアントのバージョンを見ると latest と表示されていますが、この表示だと使われているのは 1.4.192 でしょう。

f:id:ksby:20170815004126p:plain

確かに違いました。H2 のサーバのバージョンを 1.4.192 に変更して問題が解消されるか試してみます。build.gradle を以下のように変更します。

dependencies {
    ..........
    compile("org.codehaus.janino:janino")
    testCompile("org.springframework.boot:spring-boot-starter-test")
    ..........

    // dependency-management-plugin によりバージョン番号が自動で設定されないもの、あるいは最新バージョンを指定したいもの
    compile("com.integralblue:log4jdbc-spring-boot-starter:1.0.1")
    compile("org.flywaydb:flyway-core:4.2.0")
    compile("com.h2database:h2:1.4.192")
    testCompile("org.dbunit:dbunit:2.5.3")
    ..........
}
  • compile("com.h2database:h2") を下に移動して compile("com.h2database:h2:1.4.192") に変更します。

変更後、Gradle Tool Window の左上にある「Refresh all Gradle projects」ボタンをクリックして更新します。

Tomcat を再起動した後、「Data Sources and Drivers」ダイアログを表示すると今度は「接続が壊れています」のメッセージが表示されていません。「Test Connection」も成功します。

f:id:ksby:20170815005353p:plain

「Schemas」タブをクリックするとスキーマ一覧も表示されています。「All schemas」をチェックします。

f:id:ksby:20170815071508p:plain

「OK」ボタンをクリックしてダイアログを閉じた後 Database Tool Window を見ると、今度は schema_version テーブルが表示されていました。接続できたようです。

f:id:ksby:20170815012438p:plain

履歴

2017/08/16
初版発行。
2017/08/17
* WebSecurityConfigurerAdapter クラスに追加した H2_CONSOLE_URI_PATTERN 定数に static final が抜けていたので追加した。
* H2DatabaseConfig クラスにクラスコメントが抜けていたので追加した。
2017/08/23
* WebSecurityConfig.java で http.csrf().requireCsrfProtectionMatcher(...) を設定する場合、GET メソッド等で CSRF対策が実行されないようにする設定も追加する必要があったが、入れるのを忘れていたので追加した。