はじめに
次の記事の続きです。 torutk.hatenablog.jp
データベース利用グラフ表示アプリケーションを作成している過程で生じた問題解決のメモです。
- 外部キーでエンティティ間に関連を持たせる場合
- 自動採番するIDを主キーとするテーブルに新規レコードをインサートし、採番されたIDを取得したい
- 種類を表現する場合
JavaFXにおいて、
- 一つの時系列データ（XYChart.Series<>）を2つのグラフに表示するには
をどう扱ったかをメモします。
外部キーでのエンティティ間に関連
先に作成したスペクトラムテーブルと関連する測定記録テーブルを定義します。 測定記録には、測定日時、測定したスペクトラム、測定条件などを記録します。測定条件には測定に使用した器材、追加の測定条件（アッテネーター、VBW、リファレンスレベル、温度、他）があります。今回は感触をつかむ初版として列を最小限とし、次の設計としました。
- テーブル: Measurements
| 列名 | データ型 | NOT NULL制約 | 備考 |
|---|---|---|---|
| id | bigint | あり | 主キー（自動インクリメント） |
| measurement_time | datetime2(3) | あり | 時刻帯は日本固定 |
| spectrum_id | bigint | あり | 外部キー |
| RBW | real | あり | Resolution Band Width |
このテーブルは今後、がどんどん増えてくる可能性があります。
測定時刻
日時を扱うデータ型には、SQL Serverの場合、datetime、datetime2、smalldatetime、datetimeoffsetとがあります。 どれを選択すればいいかを判断するため、それぞれの内容を調べてみました。
| データ型 | 有効範囲 | 分解能 | ストレージサイズ |
|---|---|---|---|
| datetime | 1753年1月1日から9999年12月31日 | 1/300秒 | 8バイト |
| datetime2 | 0001年1月1日から9999年12月31日 | 100ナノ秒 | 6～8バイト（秒以下の小数部桁数による） |
| smalldatetime | 1900年1月1日から2079年6月6日 | 1分 | 4バイト |
| datetimeoffset | 0001年1月1日から9999年12月31日 | 100ナノ秒 | 8～10バイト（秒以下の小数部桁数による） |
datetimeoffsetは、UTC時刻からのオフセット（例えば日本時間ならUTC+9:00）を持つので、時差を扱う必要があるアプリケーションで選択します。時差は不要（日本時間一択とする、UTC時刻で統一する、等）であれば、測定時刻を比較する際にミリ秒以下の精度を要するか、1753年より古い時刻を扱う必要があるならdatetime2を選択します。 それ以外であれば、datetimeとdatetime2のどちらがいいかは微妙なところです。 smalldatetimeはストレージサイズは小さいですが、60年後には有効範囲が切れるので考えものです。
今回は、日本時刻固定で扱うこととし、精度は秒以下の小数点桁を3桁（ミリ秒）としてdatetime2(3)を採用しました。
スペクトラムテーブルへの参照
エンティティ（テーブル）の関連は、関連先のテーブルの一意となる列の値を関連元の列に格納する外部キーと言われる方法となります。外部キーは、また、制約としてテーブルのメタデータに保管します。
今回、測定記録テーブルからスペクトラムテーブルへの関連を持たせるので、測定記録テーブルの列にスペクトラムテーブルの一意な列（ここでは主キーたるSpectrumsテーブルのid列）の値を格納するため、同じデータ型（bigint）で、名前をspectrum_idとした列を設けます。
測定記録を新規作成しデータベースに登録する際困った事
測定記録に新規登録するレコードを用意するには、外部キーとして参照しているスペクトラムテーブルの関連付けするレコードのid（主キー）が必要です。
通常のユースケースでは、
となります。ここで、スペクトラムテーブルのid列は自動採番（identity）としているので、スペクトラムテーブルへのINSERT時にアプリケーション側ではidが分からず、INSERTしたレコードのidを何とかして取ってくる必要があります。
直前にINSERTしたレコードの自動採番idを取得する方法の調査
SQL ServerのSQLで実現方法を調べたところ、次の3つの方法がありました。
- @@IDENTITY → 使うな！危険！のコメントが付いていることが多い
- IDENT_CURRENT 指定したテーブルのIDENTITYな値を取る（SQL Serverではidentityによる自動インクリメントな列は1個だけ設定可）
- SCOPE_IDENTITY
一方、JDBC のAPIに自動で生成される値を取り出す方法が用意されていました。
次の引数バリエーションのprepareStatementを使用します。
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException
2場面の引数には、Statement.RETURN_GENERATED_KEYS を指定します。 そして、通常通りINSERTのSQLを実行した後に、preparedStatementオブジェクトのgetGeneratedKeys()メソッドを実行しResultSetを戻り値として得ます。 ResultSetのnext()を呼んでカレント行を示し、自動採番のキーの列の型に合わせてResultSetから値を取得します。（int型の列が自動採番ならgetInt(1)のように）
Connection conn;
:
try (var stmt = conn.prepareStatement(
"INSERT INTO Spectrums (name, spectrum, startFrequency, stopFrequency) VALUES (?, ?, ?, ?)",
Statement.RETURN_GENERATED_KEYS
)) {
stmt.setNString(1, spectrum.getName());
stmt.setBytes(2, spectrum.getData());
stmt.setDouble(3, spectrum.getStartFrequency());
stmt.setDouble(4, spectrum.getStopFrequency());
stmt.executeUpdate();
ResultSet generatedKeys = stmt.getGeneratedKeys();
if (generatedKeys.next()) {
spectrum.setId(generatedKeys.getInt(1));
} else {
throw new SQLException("No identity id obtained");
}
}
種類をもたせる
Javaでいえば、enum型を定義したいときがあります。Javaのenumの解説で良く出てくるのがトランプのスイート（スペード、ダイヤ、ハート、クラブ）です。
RDMBSでも、MySQLにはENUM型があり、SQL Server等では代替としてCHECK制約で格納可能な文字列を指定するといったことがあります。しかし、種類の一覧をとりだすといったことが手間であるなどデメリットが多く、ここでは種類の一覧だけ格納する別テーブルを設けます。
種類が文字列で一意なら、種類文字列を1つだけ列に持つテーブルを定義し、主キーを種類文字列にします。 これだと、外部キーが文字列になるので、外部キーを持つテーブルだけをSELECTで閲覧しても意味が分かる列となります。
JavaFXのチャート2つに同一のXYChart.Seriesを貼ったら
折れ線グラフが二つのウィンドウにあって、同一データ（XYChart.Series）をそれぞれに貼ったところ、おかしな挙動となってしまいました。一つのXYChart.Seriesは、一つのChartにしか使用できません。
そこで、中のデータをコピーしたSeriesを作って回避しました。
XYChart.Series<Float, Float> copy(XYChart.Series<Float, Float> series) {
var dupSeries = new XYChart.Series<Float, Float>();
series.getData().stream()
.map(data -> new XYChart.Data<>(data.getXValue(), data.getYValue()))
.collect(Collectors.collectingAndThen(
Collectors.toCollection(FXCollections::observableArrayList),
l -> dupSeries.getData().addAll(l)));
return dupSeries;
}
