sql mapperな mybatis における sql動的生成は、 .xml内に記載する方法をよく見かけますが、 .java 内にも記載できるので、メモ。

以降で様々、記載していますが、ポイントは、『UserMstRepository interfaceにおける @SelectProvider() + org.apache.ibatis.jdbc.SQL 』です。

その他、mybatisの document 等、以下が参考になります。

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>
  </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.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");
        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");

        List<UserMstEntity> userMstEntities =
                userMstService.selectUserIdList("end0tknr", 1);
        System.out.println(userMstEntities);
        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><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.ibatis.annotations.Mapper;
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(Integer kyotenId, String userXmileid);

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

    class UserMstRepositorySqlProvider{

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


        }
    }
}

UserMstService

package jp.end0tknr;

import java.util.List;

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);
    }
}

end0tknr's kipple - web写経開発

太宰府天満宮の狛犬って、妙にカワイイ

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