【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です。その情報は以下です。

属性	説明
name	JNDI接続子名を設定、必須
auth	Container を設定、固定？
type	javax.sql.DataSource を設定、固定？（データベースの場合）
factory	プール種類設定のため、完全修飾Javaクラス名、初期値org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory ※ org.apache.tomcat.jdbc.pool.DataSourceFactory を利用したらもっとよいプールになる一説がある。この場合以下の設定属性を調整する必要。 driverClassName使用するJDBCドライバーの完全修飾Javaクラス名、必須
url	コネクションを確立するためにJDBCドライバに渡される接続URL、必須
Username	DB接続ユーザー、必須
Password	DB接続ユーザーのパスワード、必須
initialSize	プールの初期化中にプール内に作成される接続の初期数、初期値　0
maxTotal	このプールから同時に割り当てることができる接続の最大数、初期値 8
minIdle	このプール内で同時にアイドル状態になる接続の最小数、初期値 0
maxIdle	このプール内で同時にアイドル状態にできる接続の最大数、初期値 8
maxWaitMillis	例外をスローする前に、プールが接続が返されるまで待機する最大ミリ秒数 (使用可能な接続がない場合)、初期値 -1
・・・

この表からわかることは、デフォルトのプール最大接続数は８ということです。つまりデータベースサーバはいくらよいものでも、プールを設定しないかぎり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 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からのレコードを変換するために扱われます。

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

サンプルイメージ

今回のサンプルは以下のようなボタンの並びです。左から順番にボタンを押せば、テーブル作成からデータの挿入、更新、削除、最後にテーブル削除する動きを行います。

jarの入手

<dependency>
    <groupId>io.github.efwgrp</groupId>
    <artifactId>efw</artifactId>
    <version>4.07.000</version>
</dependency>

jdk15以上の場合、関連jarが必要です。

<dependency>
    <groupId>org.openjdk.nashorn</groupId>
    <artifactId>nashorn-core</artifactId>
    <version>15.4</version>
</dependency>

jdbcドライバーは、利用するデータベースに合わせてダウンロードする必要です。以下の資料を参考してください。

RDB	JDBC ドライバークラス URL例
PostgreSQL	postgresql jdbc org.postgresql.Driver jdbc:postgresql://localhost:5432/helloworld
MySQL	mysql jdbc com.mysql.jdbc.Driver jdbc:mysql://localhost/helloworld
Microsoft SQL Server	sqlserver jdbc com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc:sqlserver://localhost:53847;DatabaseName=helloworld
Oracle	oracle jdbc oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@//localhost:1521:helloworld

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