(改) spring boot for java + mybatis で sql動的生成

先日、mybatisにおける「select」のsql動的生成を上記entryに記載しましたが、 「insert into」も追加し、以下に記載します。

主なポイントは、以下。

• UserMstRepository.java における @SelectProvider() + @InsertProvider()
• UserMstService.java における entityのkey , value の配列変換

その他のポイントは、srcに記載していますので、そちらをご確認下さい。

参考url

• https://mybatis.org/mybatis-3/ja/java-api.html
• https://mybatis.org/mybatis-3/ja/statement-builders.html
• MyBatis で生SQLを叩きたい - Qiita
• Beanの全フィールドを取得したい時に試したこと - Qiita

class 構成

┌@Controller class ┐
│MyController      │
└─┬───────┘
┌@Service class──┐
│UserMstService    │
└─┬───────┘
┌@Mapper interface ┐
│UserMstRepository │
└─┬───────┘
┌@Entity class ──┐┌複合pkey class  ─┐
│UserMstEntity     ├┤UserMstEntityPkey │
└─┬───────┘└─────────┘
┌mysql ──────┐
│DB TBL user_mst   │
└─────────┘

pom.xml

様々、記載していますが、mybatis-spring-boot-starter により 動的sqlも利用できます。

modelmapper-spring を、entity->dto変換用に記載していますが、 今回は使用しませんでした。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.4.5</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>jp.end0tknr</groupId>
  <artifactId>MySpring4</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>MySpring4</name>
  <description>seasar2 to spring</description>
  <properties>
    <java.version>1.8</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.4</version>
    </dependency>
    
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency> <!-- 【JSP & タグ利用】 -->
      <groupId>org.apache.tomcat.embed</groupId>
      <artifactId>tomcat-embed-jasper</artifactId>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>org.modelmapper.extensions</groupId>
      <artifactId>modelmapper-spring</artifactId>
      <version>2.4.2</version>
    </dependency>
    <dependency>
      <groupId>commons-beanutils</groupId>
      <artifactId>commons-beanutils</artifactId>
      <version>1.9.4</version>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.5</version>
    </dependency>
  </dependencies>
  
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <configuration>
          <excludes>
            <exclude>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
            </exclude>
          </excludes>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

application.properties

「mybatis.configuration.map-underscore-to-camel-case=true」により mysqlのsnake caseなcolumn名称が、javaのentityで使用するcamel caseに変換。

# JSP利用の為
spring.mvc.view.prefix= /WEB-INF/view/
spring.mvc.view.suffix= .jsp

# DB接続
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.63.3:3306/bukkenkoutei
spring.datasource.username=xparcadm
spring.datasource.password=ないしょ

# MyBatis
mybatis.mapper-locations:classpath*:/mapper/mysql/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

@Controller と jsp

debug?用に用意しています。

package jp.end0tknr;

import java.sql.Timestamp;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class MyController {

    @Autowired
    UserMstService userMstService;

    @RequestMapping(value="/index1")
    public String index1(Model model) {
        System.out.println("START index1");

        List<UserMstEntity> userMstEntities =
                userMstService.selectUserIdList("end0tknr", 1);

        System.out.println(userMstEntities);
        return "index";
    }

    @RequestMapping(value="/index2")
    public String index2(Model model) {
        System.out.println("START index2");

        UserMstEntity userMstEntity =
                userMstService.findById(1,"end0tknr1");

        System.out.println(userMstEntity);

        return "index";
    }

    @RequestMapping(value="/index3")
    public String index3(Model model) {
        System.out.println("START index3");

        for (int i=5; i<=7; i++){
            String newUserId = "end0tknr" + Integer.toString(i);

            // insert into 対象の entity作成
            UserMstEntity newEntity = new UserMstEntity();

            newEntity.setKyotenId(1);
            newEntity.setUserXmileid(newUserId);
            newEntity.setRegXmileid(newUserId);
            newEntity.setRegName(newUserId);
            newEntity.setLastUpdXmileid(newUserId);
            newEntity.setLastUpdName(newUserId);
            newEntity.setUserName(newUserId);

            //java.sql.Timestamp で datetime型用の値を作成
            Timestamp date = new Timestamp(System.currentTimeMillis());
            //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            //String dateStr = sdf.format(date);
            newEntity.setRegDate(date);
            newEntity.setLastUpdDate(date);

            newEntity.setIsDelete(0);
            newEntity.setVersion(1);
            newEntity.setUserShozokugroup("jp");
            newEntity.setShokumuId("freelance");
            newEntity.setXmashCode("xmash");

            userMstService.insertByEntity(newEntity);
        }

        return "index";
    }
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>

<a href="/index1">INDEX 1</a><br/>
<a href="/index2">INDEX 2</a><br/>
<a href="/index3">INDEX 3</a> (INSERT INTO)<br/>

</body>
</html>

@Entity

package jp.end0tknr;

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.Table;
import javax.persistence.Version;

import lombok.Data;

@Entity
@Table(name = "user_mst")
@IdClass(value=UserMstEntityPkey.class)
@Data
public class UserMstEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    /** kyotenIdプロパティ */
    @Id
    @Column(precision = 10, nullable = false, unique = false)
    public Integer kyotenId;

    /** userXmileidプロパティ */
    @Id
    @Column(length = 50, nullable = false, unique = false)
    public String userXmileid;

    /** isDeleteプロパティ */
    @Column(precision = 10, nullable = false, unique = false)
    public Integer isDelete;

    /** versionプロパティ */
    @Version
    @Column(precision = 10, nullable = false, unique = false)
    public Integer version;

    /** userNameプロパティ */
    @Column(length = 400, nullable = false, unique = false)
    public String userName;

    // 以降、省略
}

package jp.end0tknr;

import java.io.Serializable;

import lombok.Data;

@Data
public class UserMstEntityPkey implements Serializable {
    public Integer kyotenId;
    public String userXmileid;
}

DB TBL user_mst

mysql> desc user_mst;
+------------------------------+--------------+------+-----+---------+-------+
| Field                        | Type         | Null | Key | Default | Extra |
+------------------------------+--------------+------+-----+---------+-------+
| kyoten_id                    | int          | NO   | PRI | NULL    |       |
| user_xmileid                 | varchar(50)  | NO   | PRI | NULL    |       |
| user_name                    | varchar(400) | NO   |     | NULL    |       |
| is_delete                    | int          | NO   |     | 0       |       |
| reg_xmileid                  | varchar(50)  | NO   |     | NULL    |       |
| reg_name                     | varchar(400) | NO   |     | NULL    |       |
| reg_date                     | datetime     | NO   |     | NULL    |       |
   ＜以降、省略＞
+------------------------------+--------------+------+-----+---------+-------+

UserMstRepository

package jp.end0tknr;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

@Mapper
public interface UserMstRepository {

    @Select("SELECT * FROM user_mst WHERE kyoten_id=#{kyotenId} and user_xmileid=#{userXmileid}")
    UserMstEntity findById(
            @Param("kyotenId") Integer kyotenId,
            @Param("userXmileid") String userXmileid );

    @SelectProvider(
            type=UserMstRepositorySqlProvider.class,
            method="selectUserIdList" )
    List<UserMstEntity> selectUserIdList(
            @Param("userId") String userId,
            @Param("kyotenId") int kyotenId);

    @InsertProvider(
            type=UserMstRepositorySqlProvider.class,
            method="insertByEntity")
    int insertByEntity(
            @Param("table") String table,
            @Param("atriKeys") String[] atriKeys,
            @Param("atriVals") Object[] atriVals);

    class UserMstRepositorySqlProvider{

        public String insertByEntity(
                String table, String[] atriKeys, Object[] atriVals) {

            SQL sql = new SQL() {{
                INSERT_INTO( table );

                for(int i=0; i<atriKeys.length; i++){
                    String atriKey = atriKeys[i];
                    String atriKeySnake = toSnakeStr( atriKey );
                    VALUES(atriKeySnake, "#{atriVals["+i+"]}");
                }
            }};

            String strSql =sql.toString();
            System.out.println(strSql);

            return strSql;
        }

        public String selectUserIdList(String userId, int kyotenId) {
            SQL sql = new SQL() {{
                SELECT("*");
                FROM("user_mst");
                WHERE("user_xmileid LIKE '${userId}%'");
                WHERE("kyoten_id=${kyotenId}");
               WHERE("is_delete=0");
            }};
            return sql.toString();
        }

        // camel -> snake 変換
        public String toSnakeStr(String camel) {
            String snake =
                    StringUtils.join(
                            StringUtils.splitByCharacterTypeCamelCase(camel), "_")
                    .toLowerCase();
            //数字の前には「_」不要
            snake = snake.replaceAll("(_)([0-9])", "$2");

            return snake;
        }

    }
}

UserMstService

package jp.end0tknr;

import java.lang.reflect.InvocationTargetException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserMstService {

    @Autowired
    UserMstRepository userMstRepository;

    public UserMstEntity findById(Integer kyotenId, String userXmileid) {
        return userMstRepository.findById(kyotenId, userXmileid);
    }

    public List<UserMstEntity> selectUserIdList(String userXmileid, Integer kyotenId) {
        return userMstRepository.selectUserIdList(userXmileid, kyotenId);
    }

    // entityのkey , value を配列へ変換し、insert
    public int insertByEntity(UserMstEntity newUserEntity) {

        Iterator iterator = null;
        Map describeEntity = null;

        try {
            describeEntity = PropertyUtils.describe(newUserEntity);
            iterator = describeEntity.keySet().iterator();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }

        String[] atriKeys = new String[describeEntity.size()];
        Object[] atriVals = new Object[describeEntity.size()];

        int i = 0;
        while (iterator.hasNext()) {
            System.out.println(i);
            String atriKey = (String)iterator.next();
            System.out.println(atriKey);
            Object atriVal = describeEntity.get(atriKey);
            System.out.println(atriVal);
            atriKeys[i] = atriKey;
            atriVals[i] = atriVal;
            i++;
        }

        return userMstRepository.insertByEntity("user_mst", atriKeys, atriVals);
    }

}