【make it easy】efwのデータベース処理に関する紹介

efwのデータベース処理は、``dbコネクションプール``、``外だしSQL``、``結果レコードの変換処理``、３つの特徴があります。

# dbコネクションプール
tomcat789の場合、myapp/META-INF/context.xmlに以下のように記述すればプール機能を利用できます。サンプルは、postgresqlへの接続で、postgresqlのjdbcドライバーは必要です。ドライバーの置く場所は、myapp/WEB-INF/lib/postgresql-xx.x.x.jarです。
```context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Context>
<Context>
	<Resource
		name			=	"jdbc/efw"
		auth			=	"Container"
		type			=	"javax.sql.DataSource"
		driverClassName	=	"org.postgresql.Driver"
		url				=	"jdbc:postgresql://localhost:5432/helloworld"
		username		=	"postgres"
		password		=	"postgres"
		maxTotal		=	"10"
		maxIdle			=	"10"
		maxWaitMillis	=	"10"
	/>
	<Resource
		name			=	"jdbc/again"
		auth			=	"Container"
		type			=	"javax.sql.DataSource"
		driverClassName	=	"org.postgresql.Driver"
		url				=	"jdbc:postgresql://localhost:5432/helloworld"
		username		=	"postgres"
		password		=	"postgres"
		maxTotal		=	"10"
		maxIdle			=	"10"
		maxWaitMillis	=	"10"
	/>
</Context>
```
上記resourceのname属性の値がefw.propertiesのefw.jdbc.resourceの設定値と一致するもの、つまり「jdbc/efw」はデフォルトDBコネクションです。ほかは追加DBコネクションです。
resourceタグに設定できる属性は、プールの種類によって変わります。tomcat 8から、デフォルトはdbcp2です。その情報は以下です。
<table>
<tr><th>属性</th><th>説明</th></tr>
<tr><td>name</td><td>JNDI接続子名を設定、必須</td></tr>
<tr><td>auth</td><td>Container を設定、固定？</td></tr>
<tr><td>type</td><td>javax.sql.DataSource を設定、固定？（データベースの場合）</td></tr>
<tr><td>factory</td><td>プール種類設定のため、完全修飾Javaクラス名、初期値org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory
※ org.apache.tomcat.jdbc.pool.DataSourceFactory を利用したらもっとよいプールになる一説がある。この場合以下の設定属性を調整する必要。
driverClassName使用するJDBCドライバーの完全修飾Javaクラス名、必須</td></tr>
<tr><td>url</td><td>コネクションを確立するためにJDBCドライバに渡される接続URL、必須</td></tr>
<tr><td>Username</td><td>DB接続ユーザー、必須</td></tr>
<tr><td>Password</td><td>DB接続ユーザーのパスワード、必須</td></tr>
<tr><td>initialSize</td><td>プールの初期化中にプール内に作成される接続の初期数、初期値　0</td></tr>
<tr><td>maxTotal</td><td>このプールから同時に割り当てることができる接続の最大数、初期値 8</td></tr>
<tr><td>minIdle</td><td>このプール内で同時にアイドル状態になる接続の最小数、初期値 0</td></tr>
<tr><td>maxIdle</td><td>このプール内で同時にアイドル状態にできる接続の最大数、初期値 8</td></tr>
<tr><td>maxWaitMillis</td><td>例外をスローする前に、プールが接続が返されるまで待機する最大ミリ秒数 (使用可能な接続がない場合)、初期値 -1</td></tr>
<tr><td>・・・</td><td></td></tr>
</table>

この表からわかることは、デフォルトのプール最大接続数は８ということです。つまりデータベースサーバはいくらよいものでも、プールを設定しないかぎりDBの性能を発揮できません。なので、ちょっと利用人数が多い場面、例えば数十人のセミナーだったら、全員がログインすると、システムダウンになります。

参考
https://tomcat.apache.org/tomcat-8.0-doc/jndi-resources-howto.html
https://atmarkit.itmedia.co.jp/ait/articles/1111/07/news212_2.html

# 外だしSQL
javaの世界にDB処理SQLをプログラムの外に格納することはごく一般的です。例えば``Hibernate``、``iBATIS``、``DBFlute``などが結構よくあります。ソースレビュー・不具合調査の場合、外だしSQLがあればモジュールを跨ってDB処理の整合性が見えやすいです。最初どちらも利用してefwにこの特徴を取込みたかったのですが、サーバサイトjavaScriptのイベント処理なので、DB検索結果をjsonに変換する必要があります。この要件を避けられないため、仕方なく車輪の再発明しました。

今回サンプルの外だしSQLです。

```xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqls>
<sqls>
<sql id="createTbl">
	CREATE TABLE tbl_hello(
		id character varying(10) NOT NULL,	<!-- これはコメント -->
		name character varying(20),			--これもコメント
		birthday date,						//これもコメント
		years integer,
		CONSTRAINT tbl_hello_pkey PRIMARY KEY (id)
	)
</sql>
<sql id="dropTbl">
	--動的パラメータテスト
	DROP TABLE @tbl;
</sql>
<sql id="allFields">
	id,
	name,		--文字テスト
	birthday,	--日付テスト
	years		--数字テスト
</sql>
<sql id="selectAll">
	SELECT
		--includeのテスト
		<include groupId="helloDB" sqlId="allFields"/>
	FROM tbl_hello;
</sql>
<sql id="insertRow">
	INSERT INTO tbl_hello(
		--includeのテスト
		<include groupId="helloDB" sqlId="allFields"/>
	)VALUES (
		:id,
		:name,
		:birthday,
		:years
	);
</sql>
<!-- パラメータと動的パラメータの頭文字のカスタマイズ定義 -->
<sql id="updateName" paramPrefix="#" dynamicPrefix="!">
	UPDATE !tbl
	SET
		name=#name
	WHERE id=#id;
</sql>
<sql id="deleteRow">
	DELETE FROM tbl_hello
	WHERE id=:id;
</sql>
</sqls>
```
パラメータ、動的パラメータ、if文、サブsqlのインクルードなどの特徴があります。詳細は以下のリンクをご参考ください。
https://github.com/efwGrp/efw4.X/blob/master/help/api_sql.md
# dbモジュールを利用するjsイベント
jsイベントにdbモジュールのAPIを利用して、DB操作ができます。以下は検索の例です。これの特徴は、map関数の利用です。DBからのレコードを変換するために扱われます。
```javascript
var helloDB_submit={};
helloDB_submit.paramsFormat={
	mode:null,
};
helloDB_submit.fire=function(params){
	var msg="";
	if (params.mode=="create"){
		db.change("helloDB","createTbl",{});
		var rsLen=db.select("helloDB","selectAll",{}).length;
		msg="レコードのサイズ："+rsLen;
	}else if (params.mode=="insert"){
		var cnt=db.change("helloDB","insertRow",{
			id:"001",
			name:"Efw",
			birthday:new Date("2016/01/01"),
			years:new Date("2016/01/01").getYears()
		});
		var obj=db.select("helloDB","selectAll",{})
		.map({//マップ関数のテスト
			id:"id",
			name:"name",
			birthday:["birthday","yyyy/MM/dd"],
			years:function(data){
				return data.years+"才";
			}
		}).getSingle();
		msg="挿入行数："+cnt+" data="+JSON.stringify(obj);
	}else if (params.mode=="update"){
		var cnt=db.change("helloDB","updateName",{
			id:"001",
			name:"Escco Framework",
			tbl:"tbl_hello"
		});
		var obj=db.select("helloDB","selectAll",{})
		.map({//マップ関数のテスト
			id:"id",
			name:"name",
			birthday:["birthday","yyyy/MM/dd"],
			years:function(data){
				return data.years+"才";
			}
		}).getSingle();
		msg="更新行数："+cnt+" data="+JSON.stringify(obj);
	}else if (params.mode=="delete"){
		var cnt=db.change("helloDB","deleteRow",{
			id:"001"
		});
		var rsLen=db.select("helloDB","selectAll",{}).length;
		msg="削除行数："+cnt+" レコードのサイズ："+rsLen;
	}else if (params.mode=="drop"){
		db.change("helloDB","dropTbl",{tbl:"tbl_hello"});
	}

	return new Result().runat("body").withdata({"#divRet":msg});

}
```
DBから検索されるレコードを画面に利用するため、項目名をリネームしたり日付と数字をフォーマットしたりなどの変換処理がよくあります。それらの処理をmap関数で利用すれば簡単に実装できます。
dbモジュールの参考：
https://github.com/efwGrp/efw4.X
map関数の参考：
https://github.com/efwGrp/efw4.X/blob/master/help/record.map.md

# サンプルイメージ
今回のサンプルは以下のようなボタンの並びです。左から順番にボタンを押せば、``テーブル作成``からデータの``挿入``、``更新``、``削除``、最後に``テーブル削除``する動きを行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3476221/9f6762c5-9d10-6d2e-33e6-80d7508adc43.png)
# jarの入手
```xml
<dependency>
    <groupId>io.github.efwgrp</groupId>
    <artifactId>efw</artifactId>
    <version>4.07.024</version>
</dependency>
```
jdk15以上の場合、関連jarが必要です。
```xml
<dependency>
    <groupId>org.openjdk.nashorn</groupId>
    <artifactId>nashorn-core</artifactId>
    <version>15.6</version>
</dependency>
```
jdbcドライバーは、利用するデータベースに合わせてダウンロードする必要です。以下の資料を参考してください。

<table>
<tr><th>RDB</th><th>JDBC<br>ドライバークラス<br>URL例</th></tr>
<tr><td>PostgreSQL</td><td><a href="https://mvnrepository.com/artifact/org.postgresql/postgresql">postgresql jdbc</a><br>org.postgresql.Driver<br>jdbc:postgresql://localhost:5432/helloworld</td></tr>
<tr><td>MySQL</td><td><a href="https://mvnrepository.com/artifact/mysql/mysql-connector-java">mysql jdbc</a><br>com.mysql.jdbc.Driver<br>jdbc:mysql://localhost/helloworld</td></tr>
<tr><td>Microsoft SQL Server</td><td><a href="https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc">sqlserver jdbc</a><br>com.microsoft.sqlserver.jdbc.SQLServerDriver<br>jdbc:sqlserver://localhost:53847;DatabaseName=helloworld</td></tr>
<tr><td>Oracle</td><td><a href="https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8">oracle jdbc</a><br>oracle.jdbc.driver.OracleDriver<br>jdbc:oracle:thin:@//localhost:1521:helloworld</td></tr>
</table>

今回のサンプルは以下のリンクからダウンロードできます。

https://github.com/efwGrp/qittaSamples