SQLServerの取扱い
- 基本情報
- データベース接続設定
- データ型マッピング
- 主キーでの自動採番
- ページング検索の条件
- 更新ロックの取得
- プロシージャ
- データベース依存機能
- DBMS独自の利用方法
- DBMS独自の注意点
- Exampleのススメ
- dbflute内部での取扱い
- SQLServer補足資料
基本情報
- 対応バージョン
- SQLServer 2005 以上 (*1)
- JDBCドライバの同梱
- なし
- (推奨)JDBCドライバ
- sqljdbc.jar (or sqljdbc4.jar) (Exampleで利用)
*1: SQLServer 2000 での動作も(ある程度の)考慮はされてはいますが、検証環境は存在しません。
dbfluteモジュール自体が動作するJREが 5.0 の場合は sqljdbc.jar を、6.0 の場合は sqljdbc4.jar を使う必要がありますのでご注意下さい(同梱していない理由の一つ)。
その他ベンダのJDBCドライバに関しては、動作は未確認です。
Windows認証に関しては様々な注意点があります。
データベース接続設定
データベース接続設定(databaseInfoMap.dfprop)について。
接続設定の仕様 @databaseInfoMap.dfprop
map:{
; driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
; url = jdbc:sqlserver://[host]:[port];databaseName=[dbname];
; schema = dbo
; user = [dbuser]
; password = [dbpassword]
}
- catalog はURLから自動判別されるため設定不要
- schema は独自のスキーマを利用しない限りは dbo と指定
- [xxx]の[]は単なる表現上(ドキュメント上)の囲み
以下、実際のExampleプロジェクトでの設定例です。
e.g. dbflute-sqlserver-exampleの場合 {host=localhost,port=1433} @databaseInfoMap.dfprop
map:{
; driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
; url = jdbc:sqlserver://localhost:1433;databaseName=exampledb;
; schema = dbo
; user = exampledb
; password = exampledb
}
データ型マッピング
データベース上のデータ型とプログラム型との(デフォルトの)マッピングについて。
- java.lang.String
- char, varchar, nchar, nvarchar, text
- java.lang.Integer
- int, smallint, tinyint, {numeric(1-9, 0)}
- java.lang.Long
- bigint, {numeric(10-18, 0)}
- java.math.BigDecimal
- smallmoney, float, real, numeric(n, m), money
- java.util.Date
- なし
- java.sql.Time
- なし
- java.sql.Timestamp
- datetime, smalldatetime, timestamp
- java.lang.Boolean
- bit
- byte[]
- binary, varbinary
自動マッピング
numeric に関しては、自動マッピング機能が利用可能です。
未サポートのデータ型
- uniqueidentifier
- 文字列として利用可能 (ex. "FD8C7155-3A0A-DB11-BAC4-001...")
- xml
- XML表現の文字列として利用可能 (ex. "<foo/>")
- sql_variant
- 利用不可 (今後のサポートは未定)
主キーでの自動採番
自動採番(連番)の仕組みとして Identity を利用します。
Identity情報はメタデータから取得できるので、設定なしで利用可能です。
ページング検索の条件
ConditionBeanのPaging
ConditionBeanでは limit 処理として "top n" を利用しています。offset 処理は カーソルのループスルー を利用します。
カーソルのポインタシフトを利用していない理由は、offset 処理でのループスルーは、(limit 処理でのループスルーに比べて)大きなパフォーマンス劣化に繋がりにくい、ということによる割り切りです。 カーソルスキップを利用したい場合は、StatementConfig を利用してResultSetタイプを ScrollInsensitive に設定して下さい。
e.g. ConditionBeanでページング検索 {81-100} @DisplaySql
select top 100 ...
from ...
OutsideSqlのManualPaging
e.g. OutsideSqlのManualPagingでページング検索 {81-100} @OutsideSql
/*IF pmb.isPaging()*/
select *
from (
select plain.*, row_number() over(order by ...) as rn
from (
select ...
-- ELSE select count(*)
/*END*/
from ...
where ...
/*IF pmb.isPaging()*/
order by ...
) plain
) ext
where ext.rn > /*pmb.pageStartIndex*/80
and ext.rn <= /*pmb.pageEndIndex*/100
order by ext.rn asc
/*END*/
- SQLServer2000では row_number() 関数が無いので、autoPaging() を利用すること
TypedParameterBean における ManualPaging の自動判別ロジックは、"row_number()" という文字列が含まれることです。(大文字小文字は区別せず)
更新ロックの取得
ConditionBean の lockForUpdate() では、from [table] with (updlock) を利用します。
e.g. ConditionBeanで更新ロックの取得 (cb.lockForUpdate()) @DisplaySql
select ...
from MEMBER dfloc with (updlock)
where ...
and ...
- 基点テーブルだけに付与されるため、ロックは基点テーブルのみに掛かります。
プロシージャ
ストアドプロシージャ を(dbfluteの機能としての)プロシージャとしてサポートしています。
- INパラメータ
- サポート
- OUTパラメータ
- サポート
- INOUTパラメータ
- サポート
- プロシージャリターン
- サポート
- ResultSetパラメータ
- JDBCドライバにて未サポート ※のようである
- ResultSetリターン
- DBMSにて未サポート ※テーブル値ファンクションはNotParamResult
- NotParamResult
- サポート ※テーブル値ファンクション(@since 0.9.7.6)も含む
- パッケージプロシージャ
- DBMSにて未サポート ※そもそもパッケージの概念なし
- プロシージャシノニム
- DBMSにて未サポート ※そもそもシノニムの概念なし
別スキーマのプロシージャ
別スキーマのプロシージャもオプションで自動生成でき...*未検証
自動生成対象プロパティの有効項目
- Catalog
- 利用不可 (別データベースのプロシージャをサポートしてないため)
- Schema
- サポート
- Name
- サポート
データベース依存機能
データベース依存機能を有効にした場合の利用可能な機能について。
SQLServerに関しては特になし
DBMS独自の利用方法
別スキーマの利用
別スキーマのテーブルを自動生成でき...*未検証
DBMS独自の注意点
NullsFirst/Lastの実現
nulls first/last 構文をサポートしていないため、case when 構文を使って NullsFirst/Last を実現しています。union 句での case when 構文がサポートされないため、UnionQuery と NullsFirst/Last を合わせてることはできません。 (また、パフォーマンス上の懸念が多少あるので、大量データのときには注意して下さい)
CustomizeEntityの対応テーブル
CustomizeEntityのそれぞれのカラムが、元は何のテーブルの何のカラムから派生したものか、通常はJDBCのメタデータから取得できるため、 Sql2Entity 内で自動解決し、JavaDocコメント上などで表示されます。但し、SQLServerに関してはこのメタデータが取得できません。 これによって、機能の利用の仕方が少しだけ変わるものがあります。外だしSQLでの LoadReferrer では、このことによりPKマークで "元は何のテーブルのカラムだったのか" を明示的に指定する必要があります。
SQLServerのWindows認証での注意
SQLServerのWindows認証を利用して接続する場合は、JDBCドライバに対応するWindows認証用のライブラリのセットアップが別途必要となります。 どのようなライブラリが必要なのかは、利用しているJDBCドライバに依存します。例えば、jTDS であれば ntlmauth.dll、Microsoft純正のJDBCドライバであれば sqljdbc_auth.dll をパスが通った場所に置いておく必要があると JIRA で報告されています。
一方で、SQLServerに対してWindows認証で接続した場合に ReplaceSchema が正常に動作しないことが報告されています。 Windows認証を止めてSQLServer認証に変えるか、dbfluteを改変して動作するようにするかなどの回避策が JIRA の課題として議論されています。
Exampleのススメ
SQLServer を使ったExample実装 dbflute-sqlserver-example があります。
dbflute内部での取扱い
dbflute内部でどのようにSQLServerと付き合っているか、特殊なパターンを挙げます。 将来的に同じ状況・同じ方法かどうかは保証されませんので、ここに書かれることに依存した利用はしないようにして下さい。 (dbfluteを深く理解するためのドキュメントと思って下さい)
- SQLServer2000でsysobjectsなどのテーブルの(自動)除外
- SQLServer2000だと、sysobjectsなどの(システム)テーブルが自動生成対象になってしまうため、 dbfluteで明示的に(自動で)除外されるようにしました。
SQLServer補足資料
Identity設定
e.g. Identity設定 {MEMBER_IDにIdentity} @SQL
create table (
MEMBER_ID INTEGER IDENTITY NOT NULL PRIMARY KEY,
...
) ;
