1. 序章

この記事では、慣用的なGroovyを使用して、JDBCでリレーショナルデータベースをクエリする方法を見ていきます。

JDBCは比較的低レベルですが、JVM上のほとんどのORMおよびその他の高レベルのデータアクセスライブラリの基盤です。 もちろん、GroovyでJDBCを直接使用することもできます。 ただし、APIはかなり面倒です。

幸いなことに、Groovy標準ライブラリはJDBCに基づいて構築されており、クリーンでシンプルでありながら強力なインターフェイスを提供します。 そこで、GroovySQLモジュールについて説明します。

他のガイドがあるSpringなどのフレームワークを考慮せずに、プレーンなGroovyでJDBCを見ていきます。

2. JDBCとGroovyのセットアップ

依存関係の中にgroovy-sqlモジュールを含める必要があります。

<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy</artifactId>
    <version>2.4.13</version>
</dependency>
<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-sql</artifactId>
    <version>2.4.13</version>
</dependency>

groovy-allを使用している場合は、明示的にリストする必要はありません。

<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-all</artifactId>
    <version>2.4.13</version>
</dependency>

最新バージョンのgroovy、groovy-sqlおよびgroovy-allはMavenCentralで見つけることができます。

3. データベースへの接続

データベースを操作するために最初に行う必要があるのは、データベースに接続することです。

groovy.sql.Sql クラスを紹介しましょう。これは、GroovySQLモジュールを使用したデータベースのすべての操作に使用します。

Sql のインスタンスは、操作するデータベースを表します。

ただし、 Sql のインスタンスは、単一のデータベース接続ではありません。 接続については後で説明しますが、今は気にしないでください。 すべてが魔法のように機能すると仮定しましょう。

3.1. 接続パラメータの指定

この記事全体を通して、HSQLデータベースを使用します。これは、主にテストで使用される軽量のリレーショナルDBです。

データベース接続には、URL、ドライバー、およびアクセス資格情報が必要です。

Map dbConnParams = [
  url: 'jdbc:hsqldb:mem:testDB',
  user: 'sa',
  password: '',
  driver: 'org.hsqldb.jdbc.JDBCDriver']

ここでは、 Map を使用してそれらを指定することを選択しましたが、それが唯一の可能な選択肢ではありません。

次に、Sqlクラスから接続を取得できます。

def sql = Sql.newInstance(dbConnParams)

次のセクションでその使用方法を説明します。

終了したら、関連するリソースを常に解放する必要があります。

sql.close()

3.2. DataSourceを使用する

特にアプリケーションサーバー内で実行されているプログラムでは、データソースを使用してデータベースに接続するのが一般的です。

また、接続をプールしたり、JNDIを使用したりする場合は、データソースが最も自然なオプションです。

GroovyのSqlクラスは、データソースを問題なく受け入れます。

def sql = Sql.newInstance(datasource)

3.3. 自動リソース管理

Sql インスタンスの処理が完了したら、 close()を呼び出すのは面倒です。 結局のところ、マシンは私たちよりもはるかによく覚えています。

Sql を使用すると、コードをクロージャーでラップし、例外の場合でも、制御が終了したときにGroovyに close()を自動的に呼び出させることができます。

Sql.withInstance(dbConnParams) {
    Sql sql -> haveFunWith(sql)
}

4. データベースに対するステートメントの発行

今、私たちは興味深いものに進むことができます。

データベースに対してステートメントを発行する最も簡単で特殊化されていない方法は、executeメソッドです。

sql.execute "create table PROJECT (id integer not null, name varchar(50), url varchar(100))"

理論的には、DDL/DMLステートメントとクエリの両方で機能します。 ただし、上記の単純なフォームでは、クエリ結果を取得する方法は提供されていません。 クエリは後で残します。

execute メソッドにはいくつかのオーバーロードされたバージョンがありますが、ここでも、このメソッドと他のメソッドのより高度なユースケースを後のセクションで見ていきます。

4.1. データの挿入

少量の単純なシナリオでデータを挿入する場合は、前述のexecuteメソッドで十分です。

ただし、列を生成し(シーケンスや自動インクリメントなど)、生成された値を知りたい場合は、 executeInsertという専用のメソッドが存在します。

execute に関しては、利用可能な最も単純なメソッドのオーバーロードを見ていきます。後のセクションでは、より複雑なバリアントを残します。

したがって、自動インクリメントの主キー(HSQLDB用語でのID)を持つテーブルがあるとします。

sql.execute "create table PROJECT (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))"

テーブルに行を挿入し、結果を変数に保存してみましょう。

def ids = sql.executeInsert """
  INSERT INTO PROJECT (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')
"""

executeInsertexecuteとまったく同じように動作しますが、何が返されますか?

戻り値は行列であることがわかります。その行は挿入された行であり(単一のステートメントによって複数の行が挿入される可能性があることに注意してください)、その列は生成された値です。

複雑に聞こえますが、これまでで最も一般的なケースでは、単一の行と単一の生成された値があります。

assertEquals(0, ids[0][0])

後続の挿入は、生成された値1を返します。

ids = sql.executeInsert """
  INSERT INTO PROJECT (NAME, URL)
  VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
"""

assertEquals(1, ids[0][0])

4.2. データの更新と削除

同様に、データの変更と削除のための専用のメソッドが存在します:executeUpdate

繰り返しになりますが、これは execute とは戻り値のみが異なり、最も単純な形式のみを見ていきます。

この場合の戻り値は整数であり、影響を受ける行の数です。

def count = sql.executeUpdate("UPDATE PROJECT SET URL = 'https://' + URL")

assertEquals(2, count)

5. データベースのクエリ

データベースにクエリを実行すると、Groovyが取得され始めます。

JDBC ResultSetクラスを扱うのは必ずしも楽しいことではありません。 私たちにとって幸運なことに、Groovyはそのすべてについて素晴らしい抽象化を提供します。

5.1. クエリ結果の反復

ループはとても古いスタイルですが…私たちは今日、すべて閉鎖に取り組んでいます。

そしてGroovyは私たちの好みに合うようにここにあります:

sql.eachRow("SELECT * FROM PROJECT") { GroovyResultSet rs ->
    haveFunWith(rs)
}

eachRow メソッドは、データベースに対してクエリを発行し、各行に対してクロージャーを呼び出します。

ご覧のとおり、行はGroovyResultSet のインスタンスで表されます。これは、いくつかの機能が追加された、従来のResultSetの拡張です。 それについてもっと知るために読んでください。

5.2. 結果セットへのアクセス

すべてのResultSetメソッドに加えて、GroovyResultSetはいくつかの便利なユーティリティを提供します。

主に、列名に一致する名前付きプロパティを公開します。

sql.eachRow("SELECT * FROM PROJECT") { rs ->
    assertNotNull(rs.name)
    assertNotNull(rs.URL)
}

プロパティ名では大文字と小文字が区別されないことに注意してください。

GroovyResultSet は、ゼロベースのインデックスを使用した列へのアクセスも提供します。

sql.eachRow("SELECT * FROM PROJECT") { rs ->
    assertNotNull(rs[0])
    assertNotNull(rs[1])
    assertNotNull(rs[2])
}

5.3. ページ付け

結果を簡単にページングできます。つまり、オフセットから最大行数までのサブセットのみをロードできます。 これは、たとえばWebアプリケーションでよくある問題です。

eachRow および関連するメソッドには、オフセットと返される行の最大数を受け入れるオーバーロードがあります。

def offset = 1
def maxResults = 1
def rows = sql.rows('SELECT * FROM PROJECT ORDER BY NAME', offset, maxResults)

assertEquals(1, rows.size())
assertEquals('REST with Spring', rows[0].name)

ここで、 rows メソッドは、 eachRow のように行を反復処理するのではなく、行のリストを返します。

6. パラメータ化されたクエリとステートメント

多くの場合、クエリとステートメントはコンパイル時に完全に修正されていません。 それらは通常、パラメータの形で静的部分と動的部分を持っています。

文字列の連結について考えている場合は、ここで停止してSQLインジェクションについて読んでください。

前のセクションで見たメソッドには、さまざまなシナリオで多くのオーバーロードがあることを前述しました。

SQLクエリとステートメントのパラメータを処理するオーバーロードを紹介しましょう。

6.1. プレースホルダー付きの文字列

プレーンJDBCと同様のスタイルで、位置パラメータを使用できます。

sql.execute(
    'INSERT INTO PROJECT (NAME, URL) VALUES (?, ?)',
    'tutorials', 'github.com/eugenp/tutorials')

または、名前付きパラメーターをマップで使用できます。

sql.execute(
    'INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)',
    [name: 'REST with Spring', url: 'github.com/eugenp/REST-With-Spring'])

これは、 execute executeUpdate rows 、およびeachRowで機能します。 executeInsert もパラメーターをサポートしていますが、その署名は少し異なり、注意が必要です。

6.2. Groovy Strings

プレースホルダー付きのGStringを使用してGroovierスタイルを選択することもできます。

これまで見てきたすべてのメソッドは、GStringsのプレースホルダーを通常の方法で置き換えるわけではありません。 むしろ、それらをJDBCパラメーターとして挿入し、SQL構文が正しく保持されるようにします。引用符を付けたりエスケープしたりする必要がないため、インジェクションのリスクがありません。

これは完全に問題なく、安全で、Groovyです。

def name = 'REST with Spring'
def url = 'github.com/eugenp/REST-With-Spring'
sql.execute "INSERT INTO PROJECT (NAME, URL) VALUES (${name}, ${url})"

7. トランザクションと接続

これまでのところ、非常に重要な懸念事項であるトランザクションについてはスキップしました。

実際、GroovyのSqlが接続を管理する方法についてもまったく話していません。

7.1. 短命の接続

これまでに示した例では、 すべてのクエリまたはステートメントは、新しい専用接続を使用してデータベースに送信されました。 SQL 操作が終了するとすぐに接続を閉じます。

もちろん、接続プールを使用している場合、パフォーマンスへの影響は小さい可能性があります。

それでも、複数のDMLステートメントとクエリを単一のアトミック操作として発行する場合は、トランザクションが必要です。

また、そもそもトランザクションを可能にするには、複数のステートメントとクエリにまたがる接続が必要です。

7.2. キャッシュされた接続を使用したトランザクション

Groovy SQLでは、トランザクションを明示的に作成したりアクセスしたりすることはできません。

代わりに、withTransactionメソッドをクロージャー付きで使用します。

sql.withTransaction {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
    """
}

クロージャ内では、単一のデータベース接続がすべてのクエリとステートメントに使用されます。

さらに、例外のために早期に終了しない限り、クロージャが終了するとトランザクションは自動的にコミットされます。

ただし、 Sql クラスのメソッドを使用して、現在のトランザクションを手動でコミットまたはロールバックすることもできます。

sql.withTransaction {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    sql.commit()
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')
    """
    sql.rollback()
}

7.3. トランザクションなしのキャッシュ接続

最後に、上記のトランザクションセマンティクスなしでデータベース接続を再利用するには、cacheConnectionを使用します。

sql.cacheConnection {
    sql.execute """
        INSERT INTO PROJECT (NAME, URL)
        VALUES ('tutorials', 'github.com/eugenp/tutorials')
    """
    throw new Exception('This does not roll back')
}

8. 結論と参考文献

この記事では、Groovy SQLモジュールと、それがクロージャとGroovy文字列を使用してJDBCを拡張および簡素化する方法について説明しました。

そうすれば、昔ながらのJDBCは、Groovyを散りばめたもので、もう少し現代的に見えると安全に結論付けることができます。

GroovySQLのすべての機能について説明したわけではありません。 たとえば、バッチ処理、ストアドプロシージャ、メタデータなどは省略しています。

詳細については、Groovyのドキュメントを参照してください。

これらすべての例とコードスニペットの実装は、 GitHubプロジェクトにあります。これはMavenプロジェクトであるため、そのままインポートして実行するのは簡単です。