LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@epsilonGtmyon

NPocoの紹介 -機能編- クエリ自動生成

Last updated at Posted at 2020-06-16

NPocoの紹介 -機能編- クエリ自動生成

最初の記事はこちら

今回はクエリ自動生成によるデータ操作をします。

データベースはPostgreSQLを使用しました。

テーブルの用意

今回は以下のような単純なテーブルを用意しました。

emp

列名 型 主キー
emp_id varchar(5) 1
first_name varchar(10)
family_name varchar(10)
dept_code varchar(5)
created_at timestamp
updated_at timestamp

マッピング用のクラスを作成

属性を用いて上記の emp テーブルに対応するクラスを作成します。

Fluent Mappingsによる定義も可能なようです

emp.cs
[TableName("emp")]
[PrimaryKey("emp_id", AutoIncrement = false)]
public class Emp
{
    [Column("emp_id")]
    public string EmpId { get; set; }

    [Column("first_name")]
    public string FirstName { get; set; }

    [Column("family_name")]
    public string FamilyName { get; set; }

    [Column("dept_code")]
    public string DeptCode { get; set; }

    [Column("created_at")]
    public DateTime? CreatedAt { get; set; }

    [Column("updated_at")]
    public DateTime? UpdatedAt { get; set; }

    public override string ToString()
    {
        return $"[EmpId={EmpId}, FirstName={FirstName}, FamilyName={FamilyName}, DeptCode={DeptCode}, CreatedAt={CreatedAt}, UpdatedAt={UpdatedAt}]";
    }
}

TableName 属性

テーブル名に対応する属性です。

PrimaryKey 属性

主キーを表す属性です。少し複雑なので解説します。ソースはこちら

NPocoは複合主キーに対応しており、複合主キーの場合は カンマ区切り文字列 または 文字列の配列を渡します。

//文字列で指定
[PrimaryKey("col1,col2")]

//配列で指定
[PrimaryKey(new string[] { "col1", "col2" })]

またデフォルトではAutoIncrement プロパティがtrue となっているため、主キーの値が自動生成ではない場合はこのプロパティをfalse にしておく必要があります。

シーケンスによる自動採番にも対応しており SequenceName にシーケンス名を渡すことにより、Oracle などのシーケンスによる自動採番にも対応しています。
(PostgreSQL の場合は シーケンスによる自動採番はデフォルトでは対応していないので自分でPostgreSQLDatabaseType を継承した新たなものを用意する必要があります。
SQL Server でシーケンスを使う場合はUseOutputClause をtrue にする必要があります。)

Column 属性

列名に対応する属性です。

その他の属性

最低限上記の属性で足りますが、それ以外の属性についてはこちらを参照してください。

Insertメソッド

Emp insEmp = new Emp
{
    EmpId = "E0001",
    FirstName = "太郎",
    FamilyName = "Nぽこ",
    DeptCode = "001",
    CreatedAt = DateTime.Now,
    UpdatedAt = DateTime.Now,
};
database.Insert(insEmp);

以下のクエリが実行されます。

INSERT INTO "emp" ("emp_id","first_name","family_name","dept_code","created_at","updated_at") VALUES (@p0,@p1,@p2,@p3,@p4,@p5)
         -> @p0 [String] = "E0001"
         -> @p1 [String] = "太郎"
         -> @p2 [String] = "Nぽこ"
         -> @p3 [String] = "001"
         -> @p4 [DateTime] = "2020/06/16 22:25:06"
         -> @p5 [DateTime] = "2020/06/16 22:25:06"

Updateメソッド

列指定なしの場合

//主キー指定で既存データの取得
Emp updEmp = database.SingleOrDefaultById<Emp>("E0001");
updEmp.FirstName = "次郎";
updEmp.UpdatedAt = DateTime.Now;
database.Update(updEmp);

以下のクエリが実行されます。

UPDATE "emp" SET "first_name" = @p0, "family_name" = @p1, "dept_code" = @p2, "created_at" = @p3, "updated_at" = @p4 WHERE "emp_id" = @p5
         -> @p0 [String] = "次郎"
         -> @p1 [String] = "Nぽこ"
         -> @p2 [String] = "001"
         -> @p3 [DateTime] = "2020/06/16 22:25:06"
         -> @p4 [DateTime] = "2020/06/16 22:29:13"
         -> @p5 [String] = "E0001"

2項目しか変更していないにも関わらず、set では全ての列を更新していることが確認できます。

Update時に更新対象の列を限定したいときは次に説明する更新対象列指定のUpdateメソッドを使います。
またsnapshot の機能を使うことで、差分更新を自動ですることができます。

列指定ありの場合

第2引数に更新対象列の一覧を渡すことで、列を限定することができます。

Emp updEmp = database.SingleOrDefaultById<Emp>("E0001");
//スナップショットの取得
var snapshot = database.StartSnapshot(updEmp);

updEmp.FirstName = "次郎";
updEmp.UpdatedAt = DateTime.Now;

//更新対象列を指定したアップデート
//UpdatedColumnsメソッドで変更のある列の一覧を取得できる。
database.Update(updEmp, snapshot.UpdatedColumns());

以下のクエリが実行されます。

UPDATE "emp" SET "first_name" = @p0, "updated_at" = @p1 WHERE "emp_id" = @p2
         -> @p0 [String] = "次郎"
         -> @p1 [DateTime] = "2020/06/16 22:35:16"
         -> @p2 [String] = "E0001"

変更対象の2列のみがset で指定されていることが確認できます。

またスナップショットの機能を使っている場合は

database.Update(updEmp, snapshot);

と直接スナップショットを引数で渡すことも可能です。

このほか更新系にはUpdateMany というものも存在しますが、こちらは省略します。

Delete メソッド

Emp delEmp = database.SingleOrDefaultById<Emp>("E0001");
database.Delete(delEmp);

以下のクエリが実行されます。

DELETE FROM "emp" WHERE "emp_id" = @p0
         -> @p0 [String] = "E0001"

主キーによる削除が行われます。

DeleteMany メソッド

単純な条件指定の削除であれば、SQLを書かなくても可能です。

database.DeleteMany<Emp>()
        .Where(x => x.DeptCode == "X01")
        .Where(x => x.FirstName.StartsWith("太"))
        .Execute();

以下のクエリが実行されます。

DELETE  FROM "emp" WHERE ("dept_code" = @p0) AND upper("first_name") like @p1 escape '\'
         -> @p0 [String] = "X01"
         -> @p1 [String] = "太%"

linqのような感じでクエリを組み立てることができます。

SingleById, SingleOrDefaultById メソッド

主キーによる検索を行います。

SingleById は結果が存在しなければ例外をスローします。

database.SingleById<Emp>("E0001");
database.SingleOrDefaultById<Emp>("E0001");

以下のクエリが実行されます。(２つとも同じ)

SELECT "emp_id" AS "EmpId", "first_name" AS "FirstName", "family_name" AS "FamilyName", "dept_code" AS "DeptCode", "created_at" AS "CreatedAt", "updated_at" AS "UpdatedAt" FROM "emp" WHERE "emp_id" = @p0
         -> @p0 [String] = "E0001"

複合主キーの場合はこのように使うそうです。（使ったことないです..）

Query メソッド

このメソッドを使うことで 単純な検索クエリであれば、クエリを書かなくとも検索することが可能です。

database.Query<Emp>()
    .Where(x => x.DeptCode == "001")
    .OrderBy(x => x.EmpId)
    .ToList();

以下のクエリが実行されます。

SELECT "E"."emp_id" as "EmpId", "E"."first_name" as "FirstName", "E"."family_name" as "FamilyName", "E"."dept_code" as "DeptCode", "E"."created_at" as "CreatedAt", "E"."updated_at" as "UpdatedAt"
FROM "emp" "E"
WHERE ("E"."dept_code" = @p0)
ORDER BY "EmpId" ASC
         -> @p0 [String] = "001"

ソートの操作はOrderBy, OrderByDescending, ThenBy, ThenByDescending で可能です。

またLimit によるページング操作も可能です。

終端操作は

  • ToList
  • Single
  • SingleOrDefault
  • First
  • FirstOrDefault

などが可能です。

その他の操作はこちら

終わりに

今回は生のSQLを書かずに操作する方法について紹介しました。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?