1. 序章

このチュートリアルでは、慣用的なJavaでリレーショナルデータベースにアクセスするための小さくて高速なライブラリであるSql2oを見ていきます。

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は、最新バージョンのsql2oおよびHSQLDBをホストします。

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 ではないことに注意してください)。 ConnectionAutoCloseableであるため、それを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
}

データベーススキーマでは、おそらく同じプロパティを呼び出します作成日。

もちろん、クエリでエイリアスを作成できます。

Query query = connection.createQuery(
    "select name, url, creation_date as creationDate from project");

ただし、面倒であり、select*。を使用することはできません。

もう1つのオプションは、creation_datecreationDateにマップするように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"));

または、 ResultSet に似た、列名から値へのマッピングであるオブジェクトのリストにデータをマッピングすることもできます。

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は、パラメーターをバインドする別の方法を提供します。つまり、をソースとしてPOJOsを使用します。 この手法は、クエリに多くのパラメータがあり、それらがすべて同じエンティティを参照している場合に特に適しています。 それでは、バインドメソッドを紹介しましょう:

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();
    }
}

コミットとロールバックの両方でトランザクションが終了することに注意してください。後続のステートメントはトランザクションなしで実行されるため、ブロックの最後で自動的にロールバックされることはありません。

ただし、トランザクションを終了せずにコミットまたはロールバックすることはできます。

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つの手法(パラメーター化されたクエリとトランザクション)を組み合わせることで、それらをバッチで実行するのは簡単です。

  • まず、クエリを1回だけ作成します
  • 次に、パラメーターをバインドし、クエリのインスタンスごとに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. レイジーフェッチ

逆に、 1つのクエリが多数の結果を返す場合、それらすべてを変換してリストに保存すると、メモリに負荷がかかります。

したがって、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
    }
}

ResultSetIterableAutoCloseableであり、終了時に try-with-resources とともに使用して、基になるResultSetを閉じることを目的としています。

8. 結論

このチュートリアルでは、Sql2oライブラリとその最も一般的な使用パターンの概要を示しました。 詳細については、GitHubのSql20wikiを参照してください。

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