sql mapperな mybatis における sql動的生成は、 .xml内に記載する方法をよく見かけますが、 .java 内にも記載できるので、メモ。
以降で様々、記載していますが、ポイントは、 『UserMstRepository interfaceにおける @SelectProvider() + org.apache.ibatis.jdbc.SQL 』です。
その他、mybatisの document 等、以下が参考になります。
- https://mybatis.org/mybatis-3/ja/java-api.html
- https://mybatis.org/mybatis-3/ja/statement-builders.html
- MyBatis で生SQLを叩きたい - 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> </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); } }
