[make it easy] Introduction to Database Processing

EFW database processing has three characteristics: db connection pool, external SQL, and result record conversion processing.

# DB Connection Pool
In the case of tomcat789, you can use the pool function by writing the following in myapp/META-INF/context.xml. The sample connects to postgresql, and the postgresql jdbc driver is required. The location of the driver is myapp/WEB-INF/lib/postgresql-xx.x.x.jar.
```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>
```
The one whose name attribute value of the above resource matches the setting value of efw.jdbc.resource in efw.properties, that is, "jdbc/efw" is the default DB connection. Others are additional DB connections.
The attributes that can be set in the resource tag vary depending on the pool type. Starting with tomcat 8, the default is dbcp2. The information is below.
<table>
<tr><th>Attr</th><th>Explanation</th></tr>
<tr><td>name</td><td>Set JNDI connector name, required</td></tr>
<tr><td>auth</td><td>Set Container. Fixed?</td></tr>
<tr><td>type</td><td>Set javax.sql.DataSource. Fixed? (for database)</td></tr>
<tr><td>factory</td><td>Fully qualified Java class name, initial value for pool type setting. 
org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory
※ There is a theory that using org.apache.tomcat.jdbc.pool.DataSourceFactory will result in a better pool. In this case, you need to adjust the following configuration attributes.
driverClassName Fully qualified Java class name of the JDBC driver to use, required.</td></tr>
<tr><td>url</td><td>Connection URL passed to JDBC driver to establish connection, required.</td></tr>
<tr><td>Username</td><td>DB connection user, required.</td></tr>
<tr><td>Password</td><td>DB connection user password, required.</td></tr>
<tr><td>initialSize</td><td>Initial number of connections created in the pool during pool initialization, initial value 0.</td></tr>
<tr><td>maxTotal</td><td>Maximum number of connections that can be allocated simultaneously from this pool, initial value 8.</td></tr>
<tr><td>minIdle</td><td>こMinimum number of concurrently idle connections in the pool, initial value 0.</td></tr>
<tr><td>maxIdle</td><td>Maximum number of connections that can be idle simultaneously in this pool, initial value 8.</td></tr>
<tr><td>maxWaitMillis</td><td>Maximum number of milliseconds that the pool will wait for a connection to be returned (if no connections are available) before throwing an exception, initial value -1.</td></tr>
<tr><td>・・・</td><td></td></tr>
</table>

As you can see from this table, the default maximum number of pool connections is only 8. In other words, no matter how good the database server is, unless you set up a pool, it will not be able to demonstrate its DB performance. Furthermore, if there are a large number of users, such as a seminar with a few dozen people, the system will go down the moment you are asked to "everyone please log in."

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

# External SQL
In the Java world, it is very common to store DB processing SQL outside of the program. For example, Hibernate, iBATIS, DBFlute are quite common. For source reviews and bug investigations, if you have external SQL, it is easier to see the consistency of DB processing across modules. First, I would like to use either of these to incorporate this feature into efw, but since it is event processing for the server site JavaScript, I need to convert the DB search results to json. Unable to avoid this requirement, we had no choice but to reinvent the wheel.

This is the outside SQL of the sample.

```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>
```
Features include parameters, dynamic parameters, if statements, and sub-SQL inclusion. Please refer to the link below for details.
https://github.com/efwGrp/efw4.X/blob/master/help/api_sql.md
# Js Event using DB Module
You can perform DB operations using the db module API for js events. Below is an example search. The key feature here is the use of the map function. It's for converting records from 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});

}
```
In order to use the records retrieved from the DB on the screen, conversion processes such as renaming item names and formatting dates and numbers are often performed. These processes can be easily implemented using the map function.

DB Module Reference:
https://github.com/efwGrp/efw4.X
Map Function Reference:
https://github.com/efwGrp/efw4.X/blob/master/help/record.map.md

# Sample Image
In this sample, the buttons are arranged as shown below. By pressing the buttons in order, you can create a table, insert/update/delete data, and finally delete the table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3476221/9f6762c5-9d10-6d2e-33e6-80d7508adc43.png)
# Get Jar
```xml
<dependency>
    <groupId>io.github.efwgrp</groupId>
    <artifactId>efw</artifactId>
    <version>4.07.000</version>
</dependency>
```
jdk15以上の場合、関連jarが必要です。
```xml
<dependency>
    <groupId>org.openjdk.nashorn</groupId>
    <artifactId>nashorn-core</artifactId>
    <version>15.4</version>
</dependency>
```
You need to download the jdbc driver according to the database you are using. Please refer to the following materials.

<table>
<tr><th>RDB</th><th>JDBC<br>Driver class<br>URL example</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>

This sample can be downloaded from the link below.

https://github.com/efwGrp/qittaSamples