o/rマッパー (というより、sqlマッパー) MyBatis for java

sqlを多く書く為、java用o/rマッパーとして、Doma2 or MyBatis を検討。

先程のエントリの通り、Doma2は諦めた為、MyBatis をお試し。

TODO

DBへの Service→Dao→Entity 経由でのアクセスは理解できました。 が、これまで new()したobjectを利用するsourceを多く書いてきたので、 DIな構成を十分に理解できていない...

環境

項目	内容
OS	windows10
java	C:\Program Files\Java\jdk-10.0.1
IDE	Eclipse Java EE ver.Oxygen(4.7.3a) Build:201804

dir 構成 (主要部のみ抜粋)

[SpringBootMyBatis]$ tree
├─ build.gradle
└─ src
    └─ main
         ├─ java
         │   └─ jp
         │       └─ end0tknr
         │           ├─ controller
         │           │   └─ Controller.java
         │           ├─ domain
         │           │   ├─ UserDao.java
         │           │   ├─ UserEntity.java
         │           │   └─ UserService.java
         │           └── SpringBootMyBatisApplication.java
         └── resources
             ├─ application.yml
             └─ jp
                 └─ end0tknr
                     └─ domain
                         └─ UserDao.xml  

file内容

build.gradle

ubuildscript {
    ext {
        springBootVersion = '2.1.1.RELEASE'
    }
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

group = 'jp.end0tknr'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = 1.8

repositories {
    mavenCentral()
}

dependencies {
    implementation('org.springframework.boot:spring-boot-starter-web')
    implementation('org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.2')
    runtimeOnly('mysql:mysql-connector-java')
    compileOnly('org.projectlombok:lombok')
    testImplementation('org.springframework.boot:spring-boot-starter-test')
}

src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:mysql://192.168.244.101:3306/xing?useSSL=false&zeroDateTimeBehavior=convertToNull
    username: ????????
    password: ????????
    driver-class-name: com.mysql.cj.jdbc.Driver

src/main/java/jp/end0tknr/controller/Controller.java

package jp.end0tknr.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jp.end0tknr.domain.UserDao;
import jp.end0tknr.domain.UserEntity;
import jp.end0tknr.domain.UserService;

@RestController
public class Controller {
    private static final Logger logger =
            LoggerFactory.getLogger(Controller.class);

    @Autowired
    private UserDao userDao;
    @Autowired
    private SqlSession sqlSession;

    @Autowired
    private UserService userService;
    
    //この部分は、MyBatisと無関係です
    @RequestMapping("/")
    public String index() {
        logger.info("start index()");
        return "This is index page !!";
    }

    // DAO経由でレコード取得。SQLはjava内に記載
    @RequestMapping("/dumpuser1")
    public String dumpUser1() {
        UserEntity userEntity = userDao.findByUid("endou021");
        return "dumpUser1() is ..." + userEntity.toString();
        
    }

    // SqlSession経由でレコード取得。SQLは外部XML内に記載
    @RequestMapping("/dumpuser2")
    public String dumpUser2() {
        UserEntity userEntity = 
                sqlSession.selectOne("jp.end0tknr.domain.UserDao.findByUid2","endou021");

        return "dumpUser2() is ..." + userEntity.toString();
    }

    // Service→DAO経由でレコード取得。SQLはjava内に記載
    // 単にobjectをreturnしていますが、自動的にjsonになります
    @RequestMapping("/dumpusers1")
    public List<UserEntity> dumpUsers1() {
        List<UserEntity> userEntities = userService.findAll();

        return userEntities; 
    }

    // 動的SQLパターン
    @RequestMapping("/dumpusers2")
    public List<UserEntity> dumpUsers2() {
        List<UserEntity> userEntities =
                userService.findByUids(Arrays.asList("dada018","undou099"));
        return userEntities; 
    }
}

src/main/java/jp/end0tknr/domain/UserEntity.java

package jp.end0tknr.domain;

import lombok.Data;

@Data
public class UserEntity {
    private String uid;
    private String name;
    private String bushoId;
    
    @Override
    public String toString() {
        return getUid() + "," + getName() + "," + getBushoId();
    }
}

src/main/java/jp/end0tknr/domain/UserDao.java

package jp.end0tknr.domain;

import java.util.ArrayList;
import java.util.List;

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.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jp.end0tknr.controller.Controller;

@Mapper
public interface UserDao {
    static Logger logger =
            LoggerFactory.getLogger(Controller.class);

    @Select("SELECT uid, name, busho_id FROM user WHERE uid = #{uid}")
    UserEntity findByUid(@Param("uid") String uid);

    UserEntity findByUid2(@Param("uid") String uid);

    @Select("SELECT * FROM user order by uid")
    List<UserEntity> findAll();

    // 動的SQL
    @SelectProvider(type = SqlProvider.class, method = "findByUids")
    List<UserEntity> findByUids(@Param("uids") List<String> uids);

    static final class SqlProvider {
        public SqlProvider() {}
        public String findByUids(@Param("uids") List<String> uids) {
            StringBuilder sqlBuilder =
                    new StringBuilder("SELECT uid, name FROM user where uid IN (");
            
            
            ArrayList<String> uidsTmp = new ArrayList<>();
            for (String uid : uids) { 
                uidsTmp.add("'"+ uid + "'"); //TODO sql sanitize
            }
            sqlBuilder.append(String.join(",", uidsTmp) + ")");

            String sqlStr = sqlBuilder.toString();
            logger.info(sqlStr);
            
            return sqlBuilder.toString();
        }
    }    
    
    
}

src/main/java/jp/end0tknr/domain/UserService.java

package jp.end0tknr.domain;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {
    @Autowired
    private UserDao userDao;

    public List<UserEntity> findAll() {
        return userDao.findAll();
    }    
    
    public List<UserEntity> findByUids(List<String> uids) {
        return userDao.findByUids(uids);
    }
}

src/main/resources/jp/end0tknr/domain/UserDao.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="jp.end0tknr.domain.UserDao">
  <select id="findByUid2" resultType="jp.end0tknr.domain.UserEntity">
    SELECT uid, name, busho_id FROM user WHERE uid = #{uid}
  </select>
</mapper>