sql2o JDBCラッパーのガイド

1. 前書き

このチュートリアルでは、https://www.sql2o.org/ [Sql2o]、慣用的なJavaのリレーショナルデータベースアクセス用の小さくて高速なライブラリを見ていきます。
Sql2oはクエリ結果をPOJO(プレーンな古いJavaオブジェクト)にマッピングすることで機能しますが、* Hibernateなどの完全なORMソリューションではありません*。

2. Sql2oセットアップ

  • Sql2oは、プロジェクトの依存関係に簡単に追加できる単一のjarファイルです。*

<dependency>
    <groupId>org.sql2o</groupId>
    <artifactId>sql2o</artifactId>
    <version>1.6.0</version>
</dependency>
この例では、組み込みデータベースであるHSQLも使用します。従うために、それも含めることができます:
<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.4.0</version>
    <scope>test</scope>
</dependency>
Maven Centralは、_https://search.maven.org/search?q = g:org.sql2o%20AND%20a:sql2oの最新バージョンをホストします

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

*接続を確立するには、_Sql2o_クラスのインスタンスから開始します。*
Sql2o sql2o = new Sql2o("jdbc:hsqldb:mem:testDB", "sa", "");
ここでは、接続URL、ユーザー名、およびパスワードをコンストラクターパラメーターとして指定しています。
_Sql2o_オブジェクトはスレッドセーフであり、アプリケーション全体で共有できます。

* 3.1。 DataSource *を使用する

*ほとんどのアプリケーションでは、おそらく接続プールを活用するため、または追加の接続パラメーターを指定するために、生の_DriverManager_接続の代わりに* __ * DataSource * __を使用します。 心配しないで、Sql2oを使用してください。
Sql2o sql2o = new Sql2o(datasource);

* 3.2。 接続の操作*

_Sql2o_オブジェクトを単にインスタンス化しても、データベースへの接続は確立されません。
代わりに、* _ open_メソッドを使用して_Connection_オブジェクトを取得します*(JDBC _Connection_ではないことに注意してください)。 _Connection_は_AutoCloseableであるため、https://www.baeldung.com/java-try-with-resources [try-with-resources]ブロックでラップできます:
try (Connection connection = sql2o.open()) {
    // use the connection
}

4. ステートメントの挿入と更新

それでは、データベースを作成して、そこにデータを入れましょう。 チュートリアル全体を通して、_project:_という単純なテーブルを使用します。
connection.createQuery(
    "create table project "
    + "(id integer identity, name varchar(50), url varchar(100))").executeUpdate();
_executeUpdate_は_Connection_オブジェクトを返すため、複数の呼び出しをチェーンできます。 次に、影響を受ける行の数を知りたい場合は、_getResult:_を使用します
assertEquals(0, connection.getResult());
今見たパターン-_createQuery_および_executeUpdate -_ *すべてのDDL、INSERTおよびUPDATEステートメントに適用します。*

* 4.1。 生成されたキー値の取得*

ただし、場合によっては、*生成されたキー値を取得したい場合があります*これらは、自動的に計算されるキー列の値です(特定のデータベースで自動インクリメントを使用する場合など)。
これは2つのステップで行います。 まず、_createQuery:_に追加のパラメーターを追加します
Query query = connection.createQuery(
    "insert into project (name, url) "
    + "values ('tutorials', 'github.com/eugenp/tutorials')", true);
次に、接続で_getKey_を呼び出します。
assertEquals(0, query.executeUpdate().getKey());
キーが複数ある場合は、代わりに_getKeys_を使用し、配列を返します。
assertEquals(1, query.executeUpdate().getKeys()[0]);

5. データベースからのデータの抽出

それでは、問題の核心である* SELECT * *クエリと、結果セットのJavaオブジェクトへのマッピング*を見てみましょう。
まず、プロジェクトテーブルを表すゲッターとセッターを含むPOJOクラスを定義する必要があります。
public class Project {
    long id;
    private String name;
    private String url;
    //Standard getters and setters
}
次に、以前と同様にクエリを記述します。
Query query = connection.createQuery("select * from project order by id");
ただし、今回は新しいメソッド_executeAndFetch:_を使用します
List<Project> list = query.executeAndFetch(Project.class);
ご覧のとおり、このメソッドはパラメーターとして結果のクラスを取り、Sql2oはデータベースから取得した生の結果セットの行をマップします。

* 5.1。 列マッピング+

*
  • Sql2oは、名前で列をJavaBeanプロパティにマップします。*大文字と小文字を区別しません。

    ただし、命名規則はJavaデータベースとリレーショナルデータベースで異なります。 プロジェクトに作成日プロパティを追加するとします。
public class Project {
    long id;
    private String name;
    private String url;
    private Date creationDate;
    //Standard getters and setters
}
データベーススキーマでは、おそらく同じプロパティ_creation_date._を呼び出します。
もちろん、クエリでエイリアスを作成できます。
Query query = connection.createQuery(
    "select name, url, creation_date as creationDate from project");
しかし、それは面倒であり、_select * ._を使用する可能性を失います。
別のオプションは、_creation_date_を_creationDate._にマッピングするようにSql2oに指示することです。つまり、クエリをマッピングについて伝えることができます。
connection.createQuery("select * from project")
    .addColumnMapping("creation_date", "creationDate");
これは、少数のクエリで_creationDate_を控えめに使用する場合に便利です。ただし、大規模なプロジェクトで広範囲に使用すると、同じ事実を繰り返し伝えるのが退屈でエラーが発生しやすくなります。
幸いなことに、マッピングをグローバルに指定することもできます:*
Map<String, String> mappings = new HashMap<>();
mappings.put("CREATION_DATE", "creationDate");
sql2o.setDefaultColumnMappings(mappings);
もちろん、これにより_creation_date_のすべてのインスタンスが_creationDate_にマッピングされるため、データの定義全体で名前の一貫性を保つように努めるもう1つの理由です。

* 5.2。 スカラー結果*

クエリから単一のスカラー結果を抽出したい場合があります。 たとえば、レコード数をカウントする必要がある場合。
これらの場合、クラスを定義し、単一の要素を含むことがわかっているリストを反復処理するのはやり過ぎです。 したがって、* Sql2oには_executeScalar_メソッドが含まれます:*
Query query = connection.createQuery(
    "select count(*) from project");
assertEquals(2, query.executeScalar(Integer.class));
ここでは、戻り値の型を_Integer_に指定しています。 ただし、これはオプションであり、基になるJDBCドライバーに決定させることができます。

* 5.3。 複雑な結果*

代わりに、複雑なクエリ(レポートなど)がJavaオブジェクトに簡単にマッピングされない場合があります。 また、単一のクエリでのみ使用するJavaクラスをコーディングしたくないと判断する場合もあります。
したがって、* Sql2oでは、表形式のデータ構造への低レベルの動的マッピングも可能です。* _executeAndFetchTable_メソッドを使用してアクセスできます。
Query query = connection.createQuery(
    "select * from project order by id");
Table table = query.executeAndFetchTable();
次に、マップのリストを抽出できます。
List<Map<String, Object>> list = table.asList();
assertEquals("tutorials", list.get(0).get("name"));
あるいは、_Row_オブジェクトのリストにデータをマッピングできます。これは、__ ResultSet__sに似た、列名から値へのマッピングです。
List<Row> rows = table.rows();
assertEquals("tutorials", rows.get(0).getString("name"));

*6. クエリパラメータのバインド+

*
*多くのSQLクエリは、いくつかのパラメータ化された部分を含む固定構造になっています。*文字列の連結を使用して、部分的に動的なクエリを単純に作成できます。
ただし、Sql2oではパラメーター化されたクエリが許可されているため、次のことが可能です。
  • SQLインジェクション攻撃を回避します

  • データベースに頻繁に使用されるクエリをキャッシュして、
    パフォーマンス

  • 最後に、次のような複雑な型をエンコードする必要はありません。
    日時

    したがって、Sql2oで名前付きパラメーターを使用して、上記のすべてを実現できます。 コロンでパラメーターを導入し、_addParameter_メソッドでバインドします。
Query query = connection.createQuery(
    "insert into project (name, url) values (:name, :url)")
    .addParameter("name", "REST with Spring")
    .addParameter("url", "github.com/eugenp/REST-With-Spring");
assertEquals(1, query.executeUpdate().getResult());

* 6.1。 POJO *からのバインド

Sql2oは、パラメーターをバインドする別の方法を提供します。つまり、* POJO *をソースとして使用します。 この手法は、クエリに多くのパラメータがあり、それらがすべて同じエンティティを参照する場合に特に適しています。 そこで、* _ bind_メソッドを紹介しましょう:*
Project project = new Project();
project.setName("REST with Spring");
project.setUrl("github.com/eugenp/REST-With-Spring");
connection.createQuery(
    "insert into project (name, url) values (:name, :url)")
    .bind(project)
    .executeUpdate();
assertEquals(1, connection.getResult());

7. トランザクションとバッチクエリ

*トランザクションを使用すると、複数のSQLステートメントをアトミックな単一の操作として発行できます。*つまり、成功するか失敗するかは一括で、中間結果はありません。 実際、トランザクションはリレーショナルデータベースの重要な機能の1つです。
トランザクションを開くには、これまで使用していた_open_メソッドの代わりに_beginTransaction_メソッドを使用します。
try (Connection connection = sql2o.beginTransaction()) {
    // here, the transaction is active
}
実行がブロックを離れるとき、* Sql2oはトランザクションがまだアクティブな場合、自動的にトランザクションをロールバックします*

* 7.1。 手動コミットとロールバック*

ただし、*適切なメソッドでトランザクションを明示的にコミットまたはロールバックできます:*
try (Connection connection = sql2o.beginTransaction()) {
    boolean transactionSuccessful = false;
    // perform some operations
    if(transactionSuccessful) {
        connection.commit();
    } else {
        connection.rollback();
    }
}
  • _commit_と_rollback_の両方がトランザクションを終了します。*後続のステートメントはトランザクションなしで実行されるため、ブロックの終わりで自動的にロールバックされません。

    ただし、トランザクションを終了せずにコミットまたはロールバックできます。
try (Connection connection = sql2o.beginTransaction()) {
    List list = connection.createQuery("select * from project")
        .executeAndFetchTable()
        .asList();
    assertEquals(0, list.size());
    // insert or update some data
    connection.rollback(false);
    // perform some other insert or update queries
}
// implicit rollback
try (Connection connection = sql2o.beginTransaction()) {
    List list = connection.createQuery("select * from project")
        .executeAndFetchTable()
        .asList();
    assertEquals(0, list.size());
}

* 7.2。 バッチ操作*

同じステートメントを異なるパラメーターで何度も発行する必要がある場合、それらをバッチで実行するとパフォーマンスが大幅に向上します。
幸いなことに、これまで説明してきた2つの手法(パラメーター化されたクエリとトランザクション)を組み合わせることで、バッチで実行するのは簡単です。
  • 最初に、クエリを一度だけ作成します

  • 次に、パラメーターをバインドし、インスタンスごとに_addToBatch_を呼び出します
    クエリの

  • 最後に、_executeBatch:_を呼び出します

try (Connection connection = sql2o.beginTransaction()) {
    Query query = connection.createQuery(
        "insert into project (name, url) " +
        "values (:name, :url)");
    for (int i = 0; i < 1000; i++) {
        query.addParameter("name", "tutorials" + i);
        query.addParameter("url", "https://github.com/eugenp/tutorials" + i);
        query.addToBatch();
    }
    query.executeBatch();
    connection.commit();
}
try (Connection connection = sql2o.beginTransaction()) {
    assertEquals(
        1000L,
        connection.createQuery("select count(*) from project").executeScalar());
}

* 7.3。 遅延フェッチ*

逆に、*単一のクエリが非常に多くの結果を返す場合、それらすべてを変換してリストに保存すると、メモリが大量に消費されます。*
そのため、Sql2oは、行が返され、一度に1つずつマップされる遅延モードをサポートしています。
Query query = connection.createQuery("select * from project");
try (ResultSetIterable<Project> projects = query.executeAndFetchLazy(Project.class)) {
    for(Project p : projects) {
        // do something with the project
    }
}
_ResultSetIterable_は_AutoCloseable_であり、_try-with-resources_とともに使用して、終了時に基礎となる_ResultSet_を閉じることに注意してください。

8. 結論

このチュートリアルでは、Sql2oライブラリとその最も一般的な使用パターンの概要を示しました。 詳細については、https://github.com/aaberg/sql2o/wiki [GitHubのSql20 wiki]を参照してください。
また、これらすべての例とコードスニペットの実装は、Mavenプロジェクトであるhttps://github.com/eugenp/tutorials/tree/master/persistence-modules/persistence-libraries[GitHubプロジェクト]にあります。そのままインポートして実行するのが簡単なはずです。