Web 開発再入門 #12 ― MyBatis による SQL アクセス処理

# Web 開発再入門 #12 ― MyBatis  による SQL アクセス処理

fmockup

## はじめに

Web アプリケーションのサーバー・サイドを開発します。

MyBatis を使用して、SQL アクセスを実現します。MyBatis は、SQL 文とオブジェクトのマッピングを実現するもので、SQL 文が書ける人なら、学習コストが低いように思います。

なお、Hobernate は使用しませんでした。Hibernate はテーブル構造とオブジェクトのマッピングを実現します。SQL 文を書かずにオブジェクトを操作することで、Hibernate 内部が自動的に SQL 文を発行します。学習コストが高そう（特に、SQL のジョインの実現の仕方に苦労しそう）と思います。いつかは、Hibernate も学習すべきと考えています。

## フォルダー・ファイル構成

D:\\
└ Development\\
&nbsp;&nbsp;&nbsp;&nbsp;└ Workspace\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ fmockup\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ build\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ sql\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ src\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;└ main\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ java\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;└ cn\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ com\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ xxxx\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ fmockup\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ action\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ controller\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ customizer\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ entity\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;└ SessionAuthEntiry.java ← コレ
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ mapper\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;└ SessionAuthMapper.java ← コレ
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ response\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ service\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ util\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ validator\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ validator_order\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ resources\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ mapper\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;└ SessionAuthMapper.xml ← コレ
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ templates\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;│&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ static\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;├ vue-vite\\
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;└ WinSW.NET-nnn\\

## ファイルの文字コード

基本的に Eclipse でファイルを作成するので、あまり意識したことがありません。
多分、Unix 改行（LF）なのだと思います。

## ファイルの編集

1. Eclipse で、ファイル “SessionAuthMapper.java”、“SessionAuthMapper.mapper”、 “SessionAuthEntity.java” を作成する。
    ```java:SessionAuthMapper.java
    ・・・
    
    package cn.com.xxxx.fmockup.mapper;
    
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    
    import cn.com.xxxx.fmockup.entity.SessionAuthEntity;
    import cn.com.xxxx.fmockup.validator.SessionAuthValidator;
    
    /**
     * Session Auth Mapper
     *   It manipulates the login data.
     */
    
    @Mapper
    public interface SessionAuthMapper {
    
    	SessionAuthEntity searchOneUser(SessionAuthValidator validator);
    	SessionAuthEntity searchOneUserForSession(@Param("userId") String userId);
    	int searchOneUserTranCount(@Param("userId") String userId, @Param("tranName") String tranName);
    
    	int updateUserFailedCount(@Param("userId") String userId);
    	int resetUserFailedCount(@Param("userId") String userId);
    }
    ```
    ```xml:SessionAuthMapper.xml
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
    	PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="cn.com.xxxx.fmockup.mapper.SessionAuthMapper">
    	<select id="searchOneUser" parameterType="cn.com.xxxx.fmockup.validator.SessionAuthValidator" resultType="cn.com.xxxx.fmockup.entity.SessionAuthEntity">
    		SELECT USER_ID, USER_NAME, PASSWORD_AES, FAILED_COUNT, USER_LOCKED_F, DATEDIFF(DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d %T'), LAST_CHANGE_PASSWORD_DATE) AS DURATION_DAYS
    			FROM FMOCKUP.M_USER_LIST
    			WHERE USER_NAME = #{userName}
    				AND DELETED_F = 'N'
    	</select>
    	<select id="searchOneUserForSession" resultType="cn.com.xxxx.fmockup.entity.SessionAuthEntity">
    		SELECT USER_LOCKED_F
    			FROM FMOCKUP.M_USER_LIST
    			WHERE USER_ID = #{userId}
    				AND DELETED_F = 'N'
    	</select>
    	<select id="searchOneUserTranCount" resultType="java.lang.Integer">
    		SELECT COUNT(*)
    			FROM FMOCKUP.M_USER_LIST, FMOCKUP.M_USER_ROLE_LIST, FMOCKUP.M_ROLE_LIST, FMOCKUP.M_ROLE_TRAN_LIST, FMOCKUP.S_TRAN_LIST
    			WHERE M_USER_LIST.USER_ID = #{userId}
    				AND M_USER_LIST.DELETED_F = 'N'
    				AND M_USER_ROLE_LIST.DELETED_F = 'N'
    				AND M_ROLE_LIST.DELETED_F = 'N'
    				AND M_ROLE_TRAN_LIST.DELETED_F = 'N'
    				AND S_TRAN_LIST.DELETED_F = 'N'
    				AND S_TRAN_LIST.TRAN_NAME = #{tranName}
    				AND M_USER_LIST.USER_ID = M_USER_ROLE_LIST.USER_ID
    				AND M_USER_ROLE_LIST.ROLE_ID = M_ROLE_LIST.ROLE_ID
    				AND M_ROLE_LIST.ROLE_ID = M_ROLE_TRAN_LIST.ROLE_ID
    				AND M_ROLE_TRAN_LIST.TRAN_ID = S_TRAN_LIST.TRAN_ID
    	</select>
    	<update id="updateUserFailedCount">
    		UPDATE FMOCKUP.M_USER_LIST
    			SET FAILED_COUNT = FAILED_COUNT + 1,
    				LAST_LOGIN_DATE = DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d %T')
    			WHERE USER_ID = #{userId}
    				AND DELETED_F = 'N'
    	</update>
    	<update id="resetUserFailedCount">
    		UPDATE FMOCKUP.M_USER_LIST
    			SET FAILED_COUNT = 0,
    				LAST_LOGIN_DATE = DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d %T')
    			WHERE USER_ID = #{userId}
    				AND DELETED_F = 'N'
    	</update>
    </mapper>
    ```
    説明：
    #{value} は、動的パラメーターです。SQL インジェクションを防ぐことができます。なお、SQL 文の LIKE において動的パラメーターを使用する場合は、アプリケーション側での事前のサニタイジングが必要になります。
    ${value} は、直接に定数を展開する形になりますので、SQL インジェクションを防ぐために、できるだけ使用しないほうがよいです。
    ```java:SessionAuthEntity.java
    ・・・
    
    package cn.com.xxxx.fmockup.entity;
    
    import lombok.Data;
    
    /**
     * Session Auth Entity
     *   It saves values from database.
     */
    
    @Data
    public class SessionAuthEntity {
    	private String userId;
    	private String userName;
    	private String passwordAes;
    	private int failedCount;
    	private String userLockedF;
    	private int durationDays;
    }
    ```
    説明：
    上記は、データベースの検索結果を格納します。Lombok により、自動的に getter や setter のメソッド関数が生成されたものとされます。

## 注意

* Mybatis XMLに不等号が直接書けないので、CDATAでエスケープする。このとき、局所的にエスケープするほうがよいらしい。
    ```xml:OK（極小的にエスケープ）
    <select id="getHoge" resultType="_int">
      ...省略...
      GROUP BY DATE_FORMAT(col_name, '%Y%m')
      HAVING COUNT(*) <![CDATA[>=]]> 12
    </select>
    ```
    ```xml:NG（全体的にエスケープ）
    <select id="getHoge" resultType="_int">
      <![CDATA[ 
        ...省略...
        GROUP BY DATE_FORMAT(col_name, '%Y%m')
        HAVING COUNT(*) >= 12
      ]]>
    </select>
    ```

## 参考

**MyBatis 全般**

https://qiita.com/fftcy-sttkm/items/57e8596820cff28ec682

https://medium-company.com/spring-boot-mybatis

https://www.codetd.com/ja/article/10796444

**有用な Tips**

https://qiita.com/5zm/items/e1faac61a1d00998fb1f

https://qiita.com/5zm/items/0864d6641c65f976d415

https://qiita.com/ponsuke0531/items/7b778aa54d509ab87939

https://mybatis.org/mybatis-3/ja/sqlmap-xml.html

**MyBatis の SQL 文の書き方いろいろ（XML ファイル、アノテーション、...）**

https://qiita.com/yoshikawaa/items/80e393e863ab08dfcc01

**MyBatis と Hibernate の比較**

https://qiita.com/wanko5296/items/d7829f18d4827cecd4f6

**データベース設計**

https://qiita.com/nishina555/items/a79ece1b54faf7240fac