この入門で使用するデータベース

初めに

いろいろの学習のために、簡単なデータベースを持っています。このデータベースはたった３つのテーブルからなるものです。その目的は、自分用の知識を集めておく収納庫という位置づけになります。データベースの名称は、"KnowledgeDB"としました。

すでにこのデータベースを使用して、Windows WPF の学習用プログラム、ASP.NET の学習用プログラムを作成してきました。今回は、同等な機能を Java EE で作成してみようと思います。

Java EE にはデータベースを操作する上での仕様が、 JPA として定義されています。 JPA に関しては、別のところにより詳しく書きます。ここでは、一般的な SQL に限定した内容を書く予定です。また SQL に関しては特別に記述しません。さらに、 Microsoft SQL Server Management Studio に関しても記述しません。そうした知識をお持ちという前提にしています。

データベースのスキーマ

データベースのスキーマについて説明します。このデータベースには、以下の３つのテーブルしかありません。簡単ですね。

名前	内容
Major	知識を分類するために使用する大分類を格納するテーブル
Minor	大分類の下位に位置する小分類を格納するテーブル
Knowledge	知識そのものを格納するテーブル

大分類テーブル（Major）

さまざまな知識を整理分類するために、２階層の分類で管理しようと考えました。その第１階層がこの大分類になります。大分類テーブルのスキーマ構造は以下のようにしました。

簡単に説明しますと、 ID フィールドはプライマリーキーとなるフィールドです。 Name フィールドは大分類名用のフィールドです。Comment フィールドは文字通りコメント欄になります。CDate フィールドはこの大分類を登録した日時を、MDate フィールドは最後に変更した日時を意味します。 Stamp フィールドは楽観的排他制御に使用することを想定したフィールドになります。

ここで Stamp フィールドの型が timestamp になっていることに注意してください。 timestamp 型は SQL Server 独自の型です。実はこの型を選択したために、後々 JPA によるデータベースクラスを実装した時に問題となりました。 JPA には楽観的排他制御が仕様として組み込まれています。ところがその楽観的排他制御の対象とできるフィールドは、Java の型としては int, Integer, short, Short, long, Long, java.sql.Timestamp に限定されています。 SQL Server の timestamp 型は Java の型として byte[] すなわち、バイト配列にマッピングされます。つまり楽観的排他制御用のフィールドとして SQL Server の timestamp 型を採用するのはまずい判断でした。ここは、何らかの数値型にしておくのが賢明であったと思います。このデータベースはすでに、 WPF の学習用と ASP.NET の学習用とに利用していることを先に書きました。このどちらの技術も Microsoft 社の技術です。この時点では問題は発生しませんでした。このデータベースを Java と組み合わせて利用した時に初めて問題になりました。

さらに、CDate フィールドはデータベースサーバに自動設定させています。その初期値は、登録時の日時になります。また、 MDate フィールドはトリガーを使用して、これもまたデータベースサーバに設定させています。こうすることで、もれなく正しい値となることがデータベースサーバにより保障されます。SQL Server の独自型である timestamp 型のフィールドは、そのレコードが更新されるたびに、最新の値に自動的に更新されます。

SQL のコードの方が良い方は、こちらをどうぞ。

                 
USE [KnowledgeDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Major](
	[ID] [nvarchar](2) NOT NULL,
	[Name] [nvarchar](20) NOT NULL,
	[Comment] [nvarchar](128) NULL,
	[CDate] [datetime] NOT NULL,
	[MDate] [datetime] NULL,
	[Stamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_Types] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Major] ADD  CONSTRAINT [DF_Types_CDate]  DEFAULT (getdate()) FOR [CDate]
GO

ALTER TABLE [dbo].[Major]  WITH CHECK ADD  CONSTRAINT [FK_Major_Major] FOREIGN KEY([ID])
REFERENCES [dbo].[Major] ([ID])
GO

ALTER TABLE [dbo].[Major] CHECK CONSTRAINT [FK_Major_Major]
GO

小分類テーブル（Minor）

大分類の下位に位置するのが小分類になります。小分類テーブルのスキーマは、以下のようにしました。

このテーブルのプライマリーキーは MID と ID のペアになります。ここで、 MID は大分類テーブルのプライマリキーを参照するフィールドになります。それ以外のフィールドは、大分類テーブルと同じです。単純な構造ですので、理解は易しいと思います。

SQL のコードの方が良い方は、こちらをどうぞ。

                     
USE [KnowledgeDB]
GO

ALTER TABLE [dbo].[Minor] DROP CONSTRAINT [FK_Major_Minor]
GO

ALTER TABLE [dbo].[Minor] DROP CONSTRAINT [DF_Minor_CDate]
GO

DROP TABLE [dbo].[Minor]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Minor](
	[MID] [nvarchar](2) NOT NULL,
	[ID] [nvarchar](2) NOT NULL,
	[Name] [nvarchar](20) NOT NULL,
	[Comment] [nvarchar](128) NULL,
	[CDate] [datetime] NOT NULL,
	[MDate] [datetime] NULL,
	[Stamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_Minor] PRIMARY KEY CLUSTERED 
(
	[MID] ASC,
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Minor] ADD  CONSTRAINT [DF_Minor_CDate]  DEFAULT (getdate()) FOR [CDate]
GO

ALTER TABLE [dbo].[Minor]  WITH CHECK ADD  CONSTRAINT [FK_Major_Minor] FOREIGN KEY([MID])
REFERENCES [dbo].[Major] ([ID])
GO

ALTER TABLE [dbo].[Minor] CHECK CONSTRAINT [FK_Major_Minor]
GO

知識テーブル（Knowledge）

このデータベースの中心となる、知識を格納するためのテーブルです。フィールド名を見ていただければ、それぞれのフィールドの役割は明瞭と思います。知識テーブルのスキーマは以下のようにしました。

簡単に説明します。ID フィールドはこのテーブルのプライマリーキーであり、自動採番させています。 RDate はこの知識の登録日です。 CDate と違って日付だけを持ち、ユーザが直接操作することができる日付になります。 MajarID と MinorID は、小分類テーブルのプライマリーキーを参照しているフィールドです。この知識がどのような分類なのかを意味します。実はこの外部参照キーも JPA から使用する際に問題となりました。これはまた、別のところに記述します。 Title, Keyword, Reference はそれぞれこの知識のタイトル、キーワード、参照を意味します。そして、 Contents は自由に記述可能な本文になります。型は nvarchar(MAX) となります。それ以下のフィールドは大分類と同じです。

SQL のコードの方が良い方は、こちらをどうぞ。

                      
USE [KnowledgeDB]
GO

ALTER TABLE [dbo].[Knowledge] DROP CONSTRAINT [FK_Knowledge_Minor]
GO

ALTER TABLE [dbo].[Knowledge] DROP CONSTRAINT [DF_Knowledge_CDate]
GO

DROP TABLE [dbo].[Knowledge]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Knowledge](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RDate] [datetime] NULL,
	[MajorID] [nvarchar](2) NOT NULL,
	[MinorID] [nvarchar](2) NOT NULL,
	[Title] [nvarchar](128) NOT NULL,
	[KeyWord] [nvarchar](128) NULL,
	[Reference] [nvarchar](128) NULL,
	[Contents] [nvarchar](max) NULL,
	[CDate] [datetime] NOT NULL,
	[MDate] [datetime] NULL,
	[Stamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_Knowledge] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Knowledge] ADD  CONSTRAINT [DF_Knowledge_CDate]  DEFAULT (getdate()) FOR [CDate]
GO

ALTER TABLE [dbo].[Knowledge]  WITH CHECK ADD  CONSTRAINT [FK_Knowledge_Minor] FOREIGN KEY([MajorID], [MinorID])
REFERENCES [dbo].[Minor] ([MID], [ID])
GO

ALTER TABLE [dbo].[Knowledge] CHECK CONSTRAINT [FK_Knowledge_Minor]
GO

データベースのビュー

データベースのビューについて説明します。このデータベースには、以下の２つのビューしかありません。

名前	内容
MinorView	小分類の一覧を表示する際に使用する検索用ビュー
KnowledgeView	知識一覧を表示する際に使用する検索用ビュー

おそらくここで使用しているビュー程度であれば、通常はわざわざビューに登録するまでもないかもしれません。複数のテーブルを join した select 文を用意して、その結果を利用すればよいからです。しかし、 JPA を使用した場合は少し条件が違ってきます。 JPA にも通常の SQL に似た検索用の言語が用意されていて、複数テーブルを join した検索を実行できます。しかしその実行結果が違います。通常のように select 文に複数の読み出したいフィールドを書いた場合、 JPA ではその個数分の Object の配列の集合として返されるのです。今検索条件に該当するレコードが１０件ある場合を例とします。 JPA の検索結果は１０件分の集合となります。そして、その1件1件がさらに Object の配列となっているのです。検索結果を操作するためには、集合の中から必要な順番の要素を取り出します。その要素は Object の配列となっているわけです。そこで Object の配列にインデックスを指定し、希望する Object を取り出し、さらにその Object を適切な型にキャストすることが求められます。厄介ですね。

ここでビューが登場します。ビューを登録すると、そのビューから適合するクラスを 自動的に 作成することができます。そのビューの検索結果は、そのクラスのインスタンスの集合として操作することが可能になるのです。 JPA で複雑な検索をさせるのではなく、積極的にビューを作成して、より簡便に操作できるようにしたほうが良いというのが私の判断です。

小分類検索ビュー（MinorView）

以下に小分類検索用のビューをコードで示します。２つのテーブルを join しているだけです。

                     
SELECT            I.MID, A.Name AS MajorName, I.ID, I.Name, I.Comment, I.CDate, I.MDate, I.Stamp
FROM              dbo.Minor AS I INNER JOIN dbo.Major AS A ON I.MID = A.ID

知識検索ビュー（KnowledgeView）

以下に知識検索用のビューをコードで示します。３つのテーブルを join しているだけです。

                     
 SELECT k.ID, k.RDate, k.MajorID, k.MinorID, k.Title, k.KeyWord, k.Reference, k.Contents, k.CDate, k.MDate, k.Stamp,
       a.Name AS MajorName, i.Name AS MinorName
FROM dbo.Knowledge AS k INNER JOIN
     dbo.Minor AS i ON k.MajorID = i.MID AND k.MinorID = i.ID INNER JOIN dbo.Major AS a ON i.MID = a.ID