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

fmockup

はじめに

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

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

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

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

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

ファイルの文字コード

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

ファイルの編集

Eclipse で、ファイル “SessionAuthMapper.java”、“SessionAuthMapper.mapper”、 “SessionAuthEntity.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);
}

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 インジェクションを防ぐために、できるだけ使用しないほうがよいです。

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でエスケープする。このとき、局所的にエスケープするほうがよいらしい。

OK（極小的にエスケープ）

<select id="getHoge" resultType="_int">
  ...省略...
  GROUP BY DATE_FORMAT(col_name, '%Y%m')
  HAVING COUNT(*) <![CDATA[>=]]> 12
</select>

NG（全体的にエスケープ）

<select id="getHoge" resultType="_int">
  <![CDATA[ 
    ...省略...
    GROUP BY DATE_FORMAT(col_name, '%Y%m')
    HAVING COUNT(*) >= 12
  ]]>
</select>

参考

MyBatis 全般

有用な Tips

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

MyBatis と Hibernate の比較

データベース設計