[spring-batch] springboot3 mybatis 설정 그리고 mapper

환경: springboot3, spring batch5, mybatis

그동안 jpa만 주구장창 사용했어서 올만에 Mybatis 설정이다!

 

1. 디비 정보 등록(application.yml)

2. 빈 등록

@Configuration
@RequiredArgsConstructor
@MapperScan(
    value = {"com.batch.ranking.mapper"},
    annotationClass = LogDataSource.class,
    sqlSessionFactoryRef = "LogDbSqlSessionFactory",
    sqlSessionTemplateRef = "LogDbSqlSessionTemplate")
public class LogDataSourceConfig {

  public static final String SOURCE_DATASOURCE_NAME = "LogDbDataSource";

  @Value("classpath:mybatisConfig.xml")
  private Resource configLocation;

  @Bean(SOURCE_DATASOURCE_NAME)
  public DataSource LogDbDataSource() {
    DataSourceProperty dataSourceProperty = //get them from property

    Properties properties = new Properties();
    properties.setProperty("url", dataSourceProperty.getJdbcUrl());
    properties.setProperty("user", dataSourceProperty.getUsername());
    properties.setProperty("password", dataSourceProperty.getPassword());

    AtomikosDataSourceBean dataSource = new AtomikosDataSourceBean();
    dataSource.setUniqueResourceName(SOURCE_DATASOURCE_NAME);
    dataSource.setXaDataSourceClassName("com.mysql.cj.jdbc.MysqlXADataSource");
    dataSource.setXaProperties(properties);
    dataSource.setMaxPoolSize(connectionPoolProperty.getMaximumPoolSize()); //from property
    dataSource.setMinPoolSize(connectionPoolProperty.getMinimumIdle());

    return dataSource;
  }

//Qualifier is mandatory otherwise it will connect to Primary bean
  @Bean
  public SqlSessionFactory LogDbSqlSessionFactory(
      @Qualifier("LogDbDataSource") DataSource LogDbDataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setConfigLocation(configLocation);
    bean.setDataSource(LogDbDataSource);
    return bean.getObject();
  }

  @Bean
  public SqlSessionTemplate LogDbSqlSessionTemplate(
      @Qualifier("LogDbSqlSessionFactory") SqlSessionFactory LogDbSqlSessionFactory) {
    return new SqlSessionTemplate(LogDbSqlSessionFactory);
  }
}

2-1. mybatis 설정은 자바로 해도 되지만 분리하는 게 가독성이 좋아서 분리하였다.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0/EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <typeAlias type="com.batch.adapter.mybatis.handlers.RankingTypeHandler" alias="RankingTypeHandler" />
    </typeAliases>
</configuration>

2-2. 매퍼 마킹하는 어노테이션

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface LogDataSource {}

3. 사용하는 job에서 부를 경우

  private final SqlSessionFactory LogDbSqlSessionFactory;

  @Bean
  @StepScope
  public MyBatisCursorItemReader<Ranking> DailyRankingReader() {
    return new MyBatisCursorItemReaderBuilder<Ranking>()
        .sqlSessionFactory(LogDbSqlSessionFactory)
        .queryId(
            "com.batch.domain.ranking.mapper.DailyRankMapper.selectDailyTop100")
        .build();
  }

4. 매퍼에 쿼리 작성

@LogDataSource
public interface DailyRankMapper {

  List<Ranking> selectDailyTop100();
}

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.batch.domain.ranking.mapper.DailyRankMapper">
  <resultMap id="sinyutnoriRanking"
    type="com.batch.domain.ranking.model.Ranking">
    <constructor>
      <idArg column="memberid" javaType="java.lang.String" name="memberId"/>
      <arg column="regdate" javaType="java.time.LocalDate" name="registerDate"/>
      <arg column="kind" javaType="com.batch.domain.ranking.type.RankingType" name="RankingType" typeHandler="RankingTypeHandler"/>
      <arg column="gamemoney" javaType="java.lang.Long" name="gameMoney"/>
      <arg column="winrate" javaType="java.lang.Long" name="winRate"/>
      <arg column="matchcnt" javaType="java.lang.Long" name="matchCount"/>
      <arg column="wincnt" javaType="java.lang.Long" name="winCount"/>
      <arg column="defeatcnt" javaType="java.lang.Long" name="defeatCount"/>
      <arg column="ranking" javaType="java.lang.Integer" name="ranking"/>
    </constructor>
  </resultMap>

  <select id="selectDailyTop100" resultMap="Ranking">
    <![CDATA[
    SELECT  memberid
         , regdate
         , kind
         , gamemoney
         , winrate
         , matchcnt
         , wincnt
         , defeatcnt
         , @RNUM := @RNUM + 1 AS ranking
    FROM Table
      ORDER BY (wincnt + defeatcnt + drawcnt) DESC
      , (wincnt / (wincnt + defeatcnt + drawcnt)) * 100 DESC
      , gamemoney
      ) B, (SELECT @RNUM := 0) r
    WHERE @RNUM < 100
    ]]>
  </select>
</mapper>

5. enum으로 바로 꺼내고 싶다면 type handler 작성

public class RankingTypeHandler extends BaseTypeHandler<RankingType> {

  @Override
  public void setNonNullParameter(
      PreparedStatement ps, int i, RankingType parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setInt(i, parameter.getCode());
  }

  @Override
  public RankingType getNullableResult(ResultSet rs, String columnName)
      throws SQLException {
    return RankingType.findByCode(rs.getInt(columnName)).orElse(null);
  }

  @Override
  public RankingType getNullableResult(ResultSet rs, int columnIndex)
      throws SQLException {
    return RankingType.findByCode(rs.getInt(columnIndex)).orElse(null);
  }

  @Override
  public RankingType getNullableResult(CallableStatement cs, int columnIndex)
      throws SQLException {
    return RankingType.findByCode(cs.getInt(columnIndex)).orElse(null);
  }
}

---

 

여기서 궁금한 사항

Mapper interface를 직접 사용하지 않은데도(자바 로직에서) 클래스가 필요한 것인가?

reader/writer를 보면 아래와 같이 직접 db factory를 연결했으니 interface는 필요 없는 게 아니냐는 문의를 주셔서 좀 더 알아본다.

  @Bean
  @StepScope
  public MyBatisCursorItemReader<SinyutnoriRanking> dailyRankingMatchCntReader(
      @Qualifier(HangameLogDataSourceConfig.SESSION_FACTORY) SqlSessionFactory logDb) {
    return new MyBatisCursorItemReaderBuilder<SinyutnoriRanking>()
        .sqlSessionFactory(logDb)
        .queryId(LOG_MAPPER + "selectDailyTop1000UsersByMatchCnt")
        .build();
  }

 

설정이 대략 이런식으로 연결되어 있다고 할 때...

@Configuration
@RequiredArgsConstructor
@MapperScan(
    value = {"com.batch.domain.mapper.gamemapper.*"},
    annotationClass = DataSource.class,         //mapper interface에 해당 어노테이션을 달아야 
    sqlSessionFactoryRef = DataSourceConfig.SESSION_FACTORY,
    sqlSessionTemplateRef = "DbSqlSessionTemplate")
public class DataSourceConfig {

@DataSource
public interface GameMapper {

<mapper namespace="com.batch.domain.mapper.gamemapper.GameMapper">

 

1. interface 삭제 가능? No

:  GameMapper삭제하고 (Config에 annotationClass 주석하니; 하건 안하건 둘 다) 에러 발생

Caused by: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for com.batch.domain.mapper.gamemapper.GameMapper.insertRank

xml의 namespace가 인터페이스와 연결되어 있어야 쿼리 주입이 가능

2. 직접적인 함수의 호출이 없으므로 함수는 삭제 가능? Yes

xml안에는 <select>, <insert> 등 여러 쿼리가 있지만 직접 호출하지 않으므로 interface에 연결하는 함수는 없어도 된다.

@DataSource
public interface GameMapper {

//  List<Ranking> selectDailyTop1000UsersByMatchCnt();
}

@Bean
@StepScope
public MyBatisCursorItemReader<Ranking> dailyRankingMatchCntReader(
    @Qualifier(DataSourceConfig.SESSION_FACTORY) SqlSessionFactory logDb) {
  return new MyBatisCursorItemReaderBuilder<Ranking>()
      .sqlSessionFactory(logDb)
      .queryId(LOG_MAPPER + "selectDailyTop1000UsersByMatchCnt")
      .build();
}

select 함수가 직접적으로 선언되지 않아도 작동한다.

작동은 되지만 나중에 관리차원에서 헷갈릴까 봐 지울지 말지 약간 걱정은 된다..