Edited at

【DBFlute】外だしSQLを作成する。

外だしSQLの構文以外について記載していく。


事前準備


  • SQLの配置パスはsourceに合わせる。
    合わせない場合バッチ自体は正常終了するがoutsideSql()呼び出し時に型不一致のコンパイルエラーが発生する。

  • (ex)

    src/main/java/com.xxx.dbflute.exbhv

    src/resources/java/com.xxx.dbflute.exbhv ★ここに配置


SQLファイル名

-- 先頭を必ず「テーブル名 + Bhv」にする。


SQLファイルのテンプレート部分


DepartmentBhv_selectDepartmentByKey.sql

/*

[df:title]
部署取得SQL

[df:description]
部署を取得する。
*/

-- #df:entity#

-- !df:pmb!
-- !!AutoDetect!!

SELECT
D.*
FROM
DEPARTMENT D
WHERE
D.ID = /*pmb.id*/'20'



  • [df:title], [df:description]は何でも書いてよいが、別のSQLと内容が重複しているとエラーになる。

  • 「#df:entity#」で外だしSQL用のEntityクラスを作成する。

  • 「!df:pmb!」「!!AutoDetect!!」で条件を指定するためのPmbクラスを作成する。


クラス名


  • エンティティクラスはsqlファイルの_select以降の名前 (ex.DepartmentByKey)

  • Pmbクラスはsqlファイルの_select以降 + Pmbの名前 (ex.DepartmentByKeyPmb)


外だしSQLの作成高速化


databaseInfoMap.dfprop

    #; url      = jdbc:h2:file:C:/H2DB/sampledb2

; url = jdbc:h2:tcp://localhost/C:/H2DB/sampledb2


  • 組み込みモード(上)の場合、H2 Consoleを立ち上げていると「Sql2Entity実行時」接続エラーとなり、いったんConsoleを閉じないといけないため
    サーバモード(下)の書き方にするとよい。


参考


Sql2Entity実行ログ

[df-sql2entity] templates

[df-sql2entity] 2019-05-26 10:59:56,728 INFO - +------------------------------------------+
[df-sql2entity] 2019-05-26 10:59:56,728 INFO - | |
[df-sql2entity] 2019-05-26 10:59:56,728 INFO - | Sql2Entity |
[df-sql2entity] 2019-05-26 10:59:56,728 INFO - | |
[df-sql2entity] 2019-05-26 10:59:56,728 INFO - +------------------------------------------+
[df-sql2entity] 2019-05-26 10:59:56,754 INFO - ...Preparing data source:
[df-sql2entity] 2019-05-26 10:59:56,754 INFO - driver = org.h2.Driver
[df-sql2entity] 2019-05-26 10:59:56,754 INFO - url = jdbc:h2:tcp://localhost/C:/H2DB/sampledb2
[df-sql2entity] 2019-05-26 10:59:56,754 INFO - user = sa
[df-sql2entity] 2019-05-26 10:59:56,757 INFO -
[df-sql2entity] 2019-05-26 10:59:56,757 INFO - ...Using Java control: om/ControlSql2EntityJava.vm
[df-sql2entity] 2019-05-26 10:59:56,768 INFO - ...Resolving XML by database.dtd in same package
[df-sql2entity] 2019-05-26 10:59:56,861 INFO - ...Firing: DepartmentBhv_selectDepartmentByKey.sql
[df-sql2entity] 2019-05-26 10:59:56,867 INFO - ...Connecting to database by data source:
[df-sql2entity] 2019-05-26 10:59:56,957 INFO - product = H2 1.4.193 (2016-10-31)
[df-sql2entity] 2019-05-26 10:59:56,957 INFO - driver = H2 JDBC Driver 1.4.193 (2016-10-31) for JDBC 4.0
[df-sql2entity] 2019-05-26 10:59:56,968 INFO - SQL:
[df-sql2entity] -- #df:entity#
[df-sql2entity]
[df-sql2entity] -- !df:pmb!
[df-sql2entity] -- !!AutoDetect!!
[df-sql2entity]
[df-sql2entity] SELECT
[df-sql2entity] *
[df-sql2entity] FROM
[df-sql2entity] DEPARTMENT D
[df-sql2entity] WHERE
[df-sql2entity] D.ID = /*pmb.id*/'20'
[df-sql2entity] 2019-05-26 10:59:57,025 INFO - -> success=1 failure=0
[df-sql2entity] 2019-05-26 10:59:57,025 INFO - {Fired SQL}: success=1 failure=0 (in 1 files)
[df-sql2entity] 2019-05-26 10:59:57,050 INFO -
[df-sql2entity] 2019-05-26 10:59:57,050 INFO - ...Preparing generation of customize-entities and parameter-beans
[df-sql2entity] 2019-05-26 10:59:57,050 INFO - * * * * * * * * * *
[df-sql2entity] 2019-05-26 10:59:57,050 INFO - * CustomizeEntity *
[df-sql2entity] 2019-05-26 10:59:57,050 INFO - * * * * * * * * * *
[df-sql2entity] 2019-05-26 10:59:57,057 INFO -
[df-sql2entity] DepartmentByKey
[df-sql2entity] ID CHAR(30) related to DEPARTMENT.ID
[df-sql2entity] NAME CHAR(60) related to DEPARTMENT.NAME
[df-sql2entity] START_DATE DATE(10) related to DEPARTMENT.START_DATE
[df-sql2entity] END_DATE DATE(10) related to DEPARTMENT.END_DATE
[df-sql2entity] REGIST_DATE TIMESTAMP(26, 6) related to DEPARTMENT.REGIST_DATE
[df-sql2entity] 2019-05-26 10:59:57,057 INFO - * * * * * * * * *
[df-sql2entity] 2019-05-26 10:59:57,057 INFO - * ParameterBean *
[df-sql2entity] 2019-05-26 10:59:57,057 INFO - * * * * * * * * *
[df-sql2entity] 2019-05-26 10:59:57,057 INFO -
[df-sql2entity] DepartmentByKeyPmb
[df-sql2entity] String id
[df-sql2entity] 2019-05-26 10:59:57,068 INFO - generator.parse("om/ControlSql2EntityJava.vm", ctx);
[df-sql2entity] 2019-05-26 10:59:57,170 INFO - ...Setting up sql2EntityOutputDirectory: ../src/main/java
[df-sql2entity] 2019-05-26 10:59:57,170 INFO - public void deleteOldCustomizeClass() {
[df-sql2entity] 2019-05-26 10:59:57,178 INFO - }
[df-sql2entity] 2019-05-26 10:59:57,178 INFO - public void parseCustomizeEntity() {
[df-sql2entity] 2019-05-26 10:59:57,228 INFO - parse('DepartmentByKey')
[df-sql2entity] 2019-05-26 10:59:57,228 INFO - }
[df-sql2entity] 2019-05-26 10:59:57,228 INFO - public void parseParameterBean() {
[df-sql2entity] 2019-05-26 10:59:57,238 INFO - parse('DepartmentByKeyPmb');
[df-sql2entity] 2019-05-26 10:59:57,250 INFO - }
[df-sql2entity] 2019-05-26 10:59:57,267 INFO -
[df-sql2entity] /- - - - - - - - - - - - - - - - - - - - - - - -
[df-sql2entity] Target SQL files: 1
[df-sql2entity]
[df-sql2entity] DepartmentBhv_selectDepartmentByKey.sql