学習用ORマッパー「EzJDBC」の作成 その３

JAVA


昨日の続きです。

学習用ORマッパー「EzJDBC」の作成 その１（2008-12-08 - T.RYoken がんばる日記 / TryGun blog）
学習用ORマッパとー「EzJDBC」の作成 その２（2008-12-09 - T.RYoken がんばる日記 / TryGun blog）



SQL自働生成 FAQ


Ｑ．JAVAのバージョンは？
Ａ．現段階では「1.4」以上で稼動します。


Ｑ．対象データの件数を取得は可能ですか？
Ａ．可能です。下記のように記述します。

CUSTOMERテーブルの件数を取得する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。

long count = new EzJDBC().from(_CUSTOMER).count();
limit,offsetメソッド,またはorderByメソッドが指定されていてもcount時は、無効となります。


Ｑ．取得データのTRIM(空白文字削除)の指定は可能ですか？
Ａ．可能です。下記のように記述します。

右の空白文字を削除する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。

List result = new EzJDBC().from(_CUSTOMER)

　　　　　　　　　　　　　.rtrim()

　　　　　　　　　　　　　.find(Customer.class);
左右の空白を削除する場合は、trim()、左空白を削除する場合は、ltrim()、削除しない場合は、ntrim()


Ｑ．１件検索(参照結果がListではなくEntity)は可能ですか？
Ａ．可能です。下記のように記述します。

CUSTOMERテーブルのユニークキーを指定した場合の例



// EzJDBCインスタンスを生成し、結果を取得します。

Customer customer = (Customer)new EzJDBC().from(_CUSTOMER)

　　　　　　　　　　　　　　　　　　　　　.where(_CUSTOMER.ID.eq(1))

　　　　　　　　　　　　　　　　　　　　　.findSingle(Customer.class);
参照結果が複数件あった場合は、最初の１レコード目のデータを返します。


Ｑ．複数テーブルの結合は可能ですか？
Ａ．可能です。下記のように記述します。

CUSTOMER - ORDER - ORDER_ITEM を結合する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。	

List result = new EzJDBC().from(_CUSTOMER)

　　　　　　　　　　　　　.join(_CUSTOMER._ORDER)

　　　　　　　　　　　　　.join(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　　　　　.find(Customer.class);
　または


// EzJDBCインスタンスを生成し、結果を取得します。

List result = new EzJDBC().from(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　　　　　.find(Customer.class);


CUSTOMER – ORDER - ORDER_ITEM および CUSTOMER – ADDRESS を結合する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。

List result = new EzJDBC().from(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　　　　　.join(_CUSTOMER._ADDRESS)

　　　　　　　　　　　　　.find(Customer.class);
結合するテーブルのネストや数に制限はありません。好きなだけJOINして下さい。


Ｑ．抽出条件に結合先テーブルの項目を指定できるの？
Ａ．可能です。下記のように記述します。

CUSTOMER - ORDER - ORDER_ITEM を結合する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。	      

List result = new EzJDBC().from(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　　　.where(_CUSTOMER._ORDER.ORDER_NO.eq("AAA"))

　　　　　　　　　　　.find(Customer.class);
または


// EzJDBCインスタンスを生成し、結果を取得します。	      

List result = new EzJDBC().from(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　　　.where(_CUSTOMER._ORDER.ORDER_NO.eq("AAA"))

　　　　　　　　　　　.and(_CUSTOMER._ORDER._ORDER_ITEM.SEQ.gt(100))

　　　　　　　　　　　.find(Customer.class);
andやorの指定に制限はありません。また比較条件式も全て利用可能です。
ソート(ORDER BY)の指定も同様にできます。


Ｑ．抽出条件に「条件１ and (条件２ or 条件３)」の指定はできるの？
Ａ．可能です。下記のように記述します。

CUSTOMER - ORDER - ORDER_ITEM を結合する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。	

List result = new EzJDBC().from(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　.where(_CUSTOMER._ORDER.ORDER_NO.eq("AAA"))

　　　　　　　　　　.and(_CUSTOMER._ORDER._ORDER_ITEM.SEQ.gt(100)

　　　　　　　　　　　　.or(_CUSTOMER._ORDER._ORDER_ITEM.SEQ.lt(10))

　　　　　　　　　　)

　　　　　　　　　.find(Customer.class);
andやorのネスト数に制限はありません。


Ｑ．比較値の値によって、抽出条件に含んだり外したり出来るの？
Ａ．可能です。下記のように記述します。

比較値(ID)がnullの場合は、抽出条件から除外する場合の例



// 比較値IDがnullかをチェックし、結果をconditionにセットします。	

boolean condition = (id != null);

// EzJDBCインスタンスを生成し、結果を取得します。	

List result = new EzJDBC().from(_CUSTOMER)

　　　　　　　　　　　.where(_CUSTOMER.NAME.likeBegin("T"))

　　　　　　　　　　　　.and(_CUSTOMER.ID.SEQ.eq(id, condition))

　　　　　　　　　　　.find(Customer.class);
または


// EzJDBCを生成します

EzJDBC jdbc = new EzJDBC();

// EzJDBCにWHERE条件を設定します。

jdbc.from(_CUSTOMER).where(_CUSTOMER.NAME.likeBegin("T"));

// 比較値IDがnullかをチェックし、null以外の場合は条件として設定します。

if(id != null) {　　　　　   

　　jdbc.and(_CUSTOMER._ID.SEQ.eq(id));　　　　　

}

//結果を取得します。

List result = jdbc.find(Customer.class);
上記方法は、流れるようなインターフェースではありません。これも可能です。


Ｑ．ページング(何件目から何件のみ取得)は指定出来るの？
Ａ．可能です。下記のように記述します。

CUSTOMER - ORDER - ORDER_ITEM を結合する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。

List result = new EzJDBC().from(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　　　　　.where(_CUSTOMER._ORDER.ORDER_NO.eq("AAA"))

　　　　　　　　　　　　　.limit(10)         // 取得件数

　　　　　　　　　　　　　.offset(101)       // カーソル位置		　　　　　　　　

　　　　　　　　　　　　　.find(Customer.class);


Ｑ．イテレーター(1レコードづつ取得する)機能はあるの？
Ａ．あります。下記のように記述します。

CUSTOMER - ORDER - ORDER_ITEM を結合する場合の例



// EzJDBCインスタンスを生成し、結果を取得します。

JDBCIterator iterator = new EzJDBC()

　　　　　　　　　　.from(_CUSTOMER._ORDER._ORDER_ITEM)

　　　　　　　　　　.where(_CUSTOMER._ORDER.ORDER_NO.eq("AAA"))

　　　　　　　　　　.iterator (Customer.class);

// 一件づつデータ(CUSTOMER)を取得します。

while(iterator.hasNext()) {

　　　// CUSTOMERを一件取得します。

　　　Customer customer = (Customer)iterator.next();

}

// JDBCIteratorをクローズします。

iterator.end();


Ｑ．トランザクション管理は可能ですか？
Ａ．可能です。下記のように記述します。

トランザクションを管理する場合の例



// トランザクションを開始します。

EzTx.begin();

try {

　　　// EzJDBCインスタンスを生成し、結果を取得します。

　　　List result = new EzJDBC().from(_CUSTOMER).find(Customer.class);

} catch(Exception e) {

　　　// ロールバックします。               

　　　EzTx.rollback();

} finally {

　　　// トランザクションを終了します。               

　　　EzTx.end();          

}
EzTx.end()でコミットも内部で行われます。明示的にコミットする場合は、EzTx.commit();


-- 追記 --
Ｑ．SAVEPOINTの設定は可能ですか？
Ａ．可能です。下記のように記述します。

SAVEPOINTを設定する場合の例



// EzJDBCを生成します。

EzJDBC jdbc = new EzJDBC();

// トランザクションを開始します。

EzTx.begin();

try {

　　　// テーブルに登録するCustomerを生成します。

　　　Customer customer = new Customer();

　　　//　CUSTOMER.IDをセットします。

　　　customer.id = 1;

　　　// データの登録を行います。

　　　int result = jdbc.insert(customer);

　　　// SAVEPOINTを設定します。

　　　EzTx.savepoint("FIRST");
　　　//　CUSTOMER.IDをセットします。

　　　customer.id = 2;

　　　// データの登録を行います。

　　　result = jdbc.insert(customer);

　　　// SAVEPOINTを設定します。

　　　EzTx.savepoint("SECOND");
　　　//　CUSTOMER.IDをセットします。

　　　customer.id = 3;

　　　// データの登録を行います。

　　　result = jdbc.insert(customer);

　　　// SAVEPOINTを設定します。

　　　EzTx.savepoint("THIRD");
　　　// SAVEPOINT[SECOND]まで、ROLLBACKします。

　　　EzTx.rollback("SECOND");

} catch(Exception e) {

　　　// ロールバックします。               

　　　EzTx.rollback();

} finally {

　　　// トランザクションを終了します。               

　　　EzTx.end();          

}


【注意】全ての機能は組み合わせて利用することが可能です




SQLファイル


EzJDBCは、SQLファイルの利用が可能です。
SQLファイルの仕様は、Seasar(S2Dao, S2JDBC)とほぼ同様の仕様にしました。理由は、単純明快！Seasarの「2WaySQL」の仕様が、とてもよく出来ていたためです。ドキュメントも「Seasar」のSQLファイルの説明を元にしています。Seasarと異なる箇所には「☆」が、記述してあります。



この機能により、Seasar(S2Dao,S2JDBC)に慣れた方でも、安心して使ってもらえると思います。
SQLファイルとは、SQLをソースコードに記述するのではなく、 ファイルに記述したものです。
単純なSQLだと自働生成機能で、記述したほうが、 めんどうくさくなくて楽(わざわざファイルを作る必要がない)ですが、 複雑なSQLは、ファイルに記述したほうがメンテナンスしやすくなります。
SQLファイルは、クラスパス上にあるならどこにおいてもかまいません。


何のパラメータもない単純なSQLファイルは次のようになります。



SELECT * FROM CUSTOMER 

WHERE 

PKGS >= 1000 

AND PKGS  <= 2000
1000の部分を pkgsMin というパラメータで置き換えるには、 次のように置き換えたいリテラルの左にSQLコメントでパラメータ名を埋め込みます。 リテラルを文字列として直接置き換えるのではなく、 PreparedStatment を使ったバインド変数に置き換えるので、SQLインジェクション対策も問題ありません。



変数コメントは「 /*型：フィールド名*/リテラル」の形式で記述します。
/*型：フィールド名*/の型は、EzJDBCから指定するフィールドの型をあらわします。
また型は省略可能で、省略した場合は「String」フィールドとして扱われます。
☆Seasarに「型：」の指定は存在しません。



SELECT * FROM CUSTOMER

WHERE 

PKGS >= /*int:pkgsMin*/1000 

AND PKGS <= 2000
同様に2000の部分も pkgsMax というパラメータで置き換えます。


SELECT * FROM CUSTOMER 

WHERE 

PKGS >= /*int:pkgsMin*/1000 

AND PKGS <= /*int:pkgsMax*/2000
in を置き換える場合は次のようにします。 ()で囲まれている部分を置き換えます。 idのパラメータの型は、配列になります。
☆Seasarでは、配列の他にListも利用可能です


SELECT * FROM CUSTOMER 

WHERE 

PKGS IN /*int[]:pkgs*/(1, 2)
like を置き換える場合は次のようにします。 '(シングルクオート)で囲まれている部分を置き換えます。 ワイルドカードを使いたい場合は、パラメータの値に埋め込んでください。 


SELECT * FROM CUSTOMER 

WHERE 

ID LIKE /*ID*/'S%'
検索条件の入力画面などによくあるパターンで、何か条件が入力されていれば、 検索条件に追加し、入力されていなければ条件には追加しないということを実装してみましょう。 このような場合は、IFコメントとENDコメントを組み合わせます。


IFコメントは「 /*IF(型：条件)*/.../*END*/」の形式で記述します。/*型：条件*/の型は、EzJDBCから指定するフィールドの型をあらわします。
また型は省略可能で、省略した場合は「String」フィールドとして扱われます。 IFコメント内で型を指定したフィールドは、その後は型の宣言をする必要はありません。
☆Seasarでは、/*IF … */の形式で記述します。括弧は付きません。



SELECT * FROM CUSTOMER 

WHERE 

/*IF(int:pkgsMin > 0)*/

　　PKGS >= /*pkgsMin*/1000 

/*END*/ 

/*IF(int:pkgsMax > 0)*/

　　AND PKGS <= /*pkgsMax*/2000 

/*END*/

IFコメントの内容が true なら、 IFコメントとENDコメントで囲んでいる内容が出力されます。
上記のように記述すると、pkgsMinが1以上で、pkgsMaxが0のときには、下記のように正しいSQLになります。 



SELECT * FROM CUSTOMER 

WHERE 

PKGS >= ?
しかしpkgsMinが0でpkgsMaxが1以上のときは、次のような不正な(andがwhereの直後にある)SQLになります。


SELECT * FROM CUSTOMER 

WHERE 

AND PKGS <= ?
また、pkgsMinとpkgsMaxが0の場合も、 次のような不正な(whereだけがある)SQLになります。 


SELECT * FROM CUSTOMER 

WHERE
この問題に対応するためには、where句の部分を次のように、 BEGINコメントとENDコメントで囲みます。


SELECT * FROM CUSTOMER 

/*BEGIN*/ 

WHERE       

　　　/*IF(int:pkgsMin> 0*/          

　　　　　　PKGS >= /*pkgsMin*/1000      

　　　/*END*/      

　　　/*IF(int:pkgsMax > 0)*/          

　　　　　　AND PKGS <= /*pkgsMax*/2000      

　　　/*END*/ 

/*END*/
このようにすると、pkgsMinが0以下でpkgsMaxが1以上のときは、 pkgsMaxの条件は、BEGINコメントとENDコメントで囲まれた最初の条件なので、 and の部分が自動的に削除されて次のようになります。


SELECT * FROM CUSTOMER 

WHERE 

PKGS <= ?
また、pkgsMinとpkgsMaxが0の場合は、 BEGINコメントとENDコメントで囲まれた部分に1つも条件に一致するものがないので、 BEGINコメントとENDコメントで囲まれた部分がカットされて次のようになります。 


SELECT * FROM CUSTOMER
ELSEコメントは、IFコメントとENDコメントの間に行コメントとして埋め込みます。
ELSEコメントは「/*ELSE ... */」の形式で記述します。
☆Seasarでは、ELSEコメントは「--」で表現します。


/*IF(ID != null)*/    

　　　ID = /*ID*/100 

/*ELSE    

　　　ID IS null 

＊/ 

/*END*/
ELSEコメント中に、IFコメントを記述(ネスト)することも可能です。


/*IF(ID != null)*/    

　　　ID = /*ID*/100

/*ELSE    

　　　ID IS null    

　　　/*IF(author != null)*/       

　　　　　　AND AUTHOR = /*author*/'TAKAHASHI'    

　　　/*END*/ 

＊/ 

/*END*/
SQL等の説明コメントは「--」で記述します。--コメントは実行時には無視されます。
☆Seasarでは、/* ･･･*/形式で記述します。(/*の後に1BLANK記述) 


SELECT * FROM CUSTOMER 

/*BEGIN*/ 

WHERE　　

　　　-- 指定されたpkgsMinが1以上の場合のみ抽出条件になります。    

　　　/*IF(int:pkgsMin > 0*/        

　　　　　　PKGS >= /*pkgsMin*/1000    

　　　/*END*/　　

　　　-- 指定されたpkgsMax が1以上の場合のみ抽出条件になります。    

　　　/*IF(pkgsMax > 0)*/        

　　　　　　AND PKGS <= /*pkgsMax*/2000    

　　　/*END*/ 

/*END*/



SQLファイルを利用したEzJDBCの実装例


SQLファイルの仕様については、「SQLファイル」の説明で、ご理解いただけたかと思います。
今度は、SQLファイルを利用した場合のEzJDBCの記述方法について説明したいと思います。


利用するSQLファイルは下記の通り。


ファイル名「jp.co.xxx.sample.SqlFile.sql」



SELECT * FROM  CUSTOMER CUSTOMER LEFT JOIN  

ORDER ORDER ON (CUSTOMER.ID = ORDER.CUSTOMER_ID) LEFT JOIN  

ORDER_ITEM ORDER_ITEM ON (ORDER.ID = ORDER_ITEM.ID) 

　　AND (ORDER.ORDER_NO =ORDER_ITEM.ORDER_NO)  

/*BEGIN*/

　　　WHERE	

　　　/*IF(ID > 0)*/		

　　　　　　CUSTOMER.ID!=/*id*/1 	

　　　/*END*/	

　　　/*BEGIN*/AND (		

　　　　　　/*IF(String[]:orderNo != null)*/			

　　　　　　　　　ORDER.ORDER_NO IN /*orderNo*/('A', 'B', 'C')		

　　　　　　/*END*/		

　　　　　　/*IF(seq != null)*/			

　　　　　　　　　OR ORDER_ITEM.SEQ>/*seq*/'1'		

　　　　　　/*END*/	

　　　)/*END*/	 

/*END*/    

ORDER BY    

　　CUSTOMER.ID ASC,

　　ORDER.ORDER_NO DESC
SQLファイルを利用したEzJDBCの記述方法は以下の通りです。

利用方法１ ： 変数コメントを、EzJDBCに直接指定する方法



// IDのパラメータ値

int value1 = 100;

// ORDER番号のパラメータ値

String[] value2 = new String[]{"1-1","1-2","1-5"};

// 明細SEQのパラメータ値

String value3 = "10";

// EzJDBCよりSQL結果を取得します。

List result = new EzJDBC()		

　　　　　.from("jp.co.xxx.sample.SqlFile.sql", false)　// SQLファイルPATHを指定

　　　　　.set("id", value1)　　　　　　　　　　　　　　　　　// idにセットする値

　　　　　.set("orderNo", value2)　　　　　　　　　　　　　// orderNoにセットする値		

　　　　　.set("seq",value3)　　　　　　　　　　　　　　　　　// seqにセットする値		

　　　　　.find(Customer.class);


利用方法２ ： 変数コメントに対応したBeanを、EzJDBCに指定する方法



// パラメータBeanを生成します(SQLファイル名と同名のBeanであることに注目)。

SqlFile sqlFile = new SqlFile();                 

sqlFile.id = 100;　　　　　　　　　　　　　　　　　　　// IDのパラメータ値

sqlFile.orderNo = new String[]{"1-1","1-2","1-5"};　　// ORDER番号のパラメータ値

sqlFile.seq = "10";　　　　　　　　　　　　　　　　　　// 明細SEQのパラメータ値			

// EzJDBCよりSQL結果を取得します。

List result = new EzJDBC()		

　　　　　　　.from(sqlFile)　　　　　　　　　　　　　// パラメータBeanを指定		

　　　　　　　.find(Customer.class);
SqlFile.javaの内容は以下の通りです(パッケージがSQLファイルと同じにする事)。


package jp.co.xxx.sample; 
public class SqlFile implements EzParam {
　　　/** idのパラメータ値 */	

　　　public int id;		
　　　/** ORDER番号のパラメータ値 */	

　　　public String[] orderNo;		
　　　/** 明細SEQのパラメータ値 */	

　　　public String seq; 
}
パラメータBeanは「SQLファイルエディター」で編集後に保存をかけると自動生成される予定。


利用方法２の方はパラメータBean自働生成機能が出来上がればタイプセーフになるというメリットがあります。


利用方法１、２を実行した結果発行されたSQL文



SELECT

　　　...

FROM

　　　CUSTOMER CUSTOMER LEFT JOIN ORDER ORDER ON (CUSTOMER.ID = ORDER. CUSTOMER_ID) LEFT JOIN    ORDER_ITEM  ORDER_ITEM ON (ORDER.ID = ORDER_ITEM.ID) AND (ORDER.ORDER_NO = ORDER_ITEM.ORDER_NO)

WHERE

　　　CUSTOMER.ID != 100 AND

　　　(ORDER.ORDER_NO IN ('1-1', '1-2', '1-5') OR ORDER_ITEM.SEQ> '10')

ORDER BY

　　　CUSTOMER.ID ASC,

　　　ORDER.ORDER_NO DESC


SQL自動生成機能を利用したEzJDBCの実装例


下記のSQLファイルで記述したようなことを、SQL自動生成で記述する場合の説明をいたします。



SELECT * FROM  CUSTOMER CUSTOMER LEFT JOIN  

ORDER ORDER ON (CUSTOMER.ID = ORDER.CUSTOMER_ID) LEFT JOIN  

ORDER_ITEM ORDER_ITEM ON (ORDER.ID = ORDER_ITEM.ID) 

　　AND (ORDER.ORDER_NO =ORDER_ITEM.ORDER_NO)  

/*BEGIN*/

　　　WHERE	

　　　/*IF(ID > 0)*/		

　　　　　　CUSTOMER.ID!=/*id*/1 	

　　　/*END*/	

　　　/*BEGIN*/AND (		

　　　　　　/*IF(String[]:orderNo != null)*/			

　　　　　　　　　ORDER.ORDER_NO IN /*orderNo*/('A', 'B', 'C')		

　　　　　　/*END*/		

　　　　　　/*IF(seq != null)*/			

　　　　　　　　　OR ORDER_ITEM.SEQ>/*seq*/'1'		

　　　　　　/*END*/	

　　　)/*END*/	 

/*END*/    

ORDER BY    

　　CUSTOMER.ID ASC,

　　ORDER.ORDER_NO DESC

SQL自動生成版実装方法



//	IDにセットする値のnullチェック

boolean c1 = (value1 > 0);

//	ORDER_NOにセットする値のnullチェック

boolean c2 = (value2 != null);

//	SEQにセットする値のnullチェック

boolean c3 = (value3 != null);
//	メインテーブルとなるCUSTOMERテーブル情報を保持するCustomer.class 

CUSTOMER _CUSTOMER = new CUSTOMER();

// EzJDBCよりSQL結果を取得します。

List result =  new EzJDBC()

　　　　　　.from(_CUSTOMER._ORDER._ORDER_ITEM)		

　　　　　　.where(_CUSTOMER.ID.ne(value1, c1))		  

　　　　　　　.and(_CUSTOMER._ORDER.ORDER_NO.in(value2, c2)

　　　　　　　　　　.or(_CUSTOMER._ORDER._ORDER_ITEM.SEQ.gt(value3, c3)))

　　　　　　.orderBy()

　　　　　　　.asc(_CUSTOMER.ID)		  

　　　　　　　.desc(_CUSTOMER._ORDER.ORDER_NO);		

　　　　　　.find(Customer.class);
boolean型の「c1, c2, c3」がfalseの場合は、それぞれの抽出条件の対象外になります。


今回の例に出したSQLぐらいであれば、SQL自動生成バージョンの方が楽ですね　





最後に…

　　つづく…