[Spring Boot] How to create custom repository

Process

1.0 Create Repository  interface

package tw.com.abc.analysis.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import tw.com.abc.analysis.entity.Goal;
import tw.com.abc.analysis.entity.GoalId;
import tw.com.abc.analysis.repository.custom.GoalRepositoryCustom;

import java.util.List;

@Repository
public interface GoalRepository extends CrudRepository<Goal, GoalId> {
}

1.1 Create Repository Custom interface

package tw.com.abc.analysis.repository.custom;

public interface GoalRepositoryCustom {
}

1.2 Create Repository Custom Imple. class

package tw.com.abc.analysis.repository.impl;

public class GoalRepositoryImpl implements GoalRepositoryCustom {
}

1.3 Repository class extends Repository Custom interface

package tw.com.abc.analysis.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import tw.com.abc.analysis.entity.Goal;
import tw.com.abc.analysis.entity.GoalId;
import tw.com.abc.analysis.repository.custom.GoalRepositoryCustom;

import java.util.List;

@Repository
public interface GoalRepository extends CrudRepository<Goal, GoalId>, GoalRepositoryCustom {
}

1.4 Write SQL statement and save in SQL file

1.5 Create Mapper Class

package tw.com.abc.analysis.vo.rowmapper;

import org.springframework.jdbc.core.RowMapper;
import tw.com.abc.analysis.vo.GoalVo;

import java.sql.ResultSet;
import java.sql.SQLException;

public class GoalVoRowMapper implements RowMapper<GoalVo> {

    @Override
    public GoalVo mapRow(ResultSet rs, int rowNum) throws SQLException {
        GoalVo vo = new GoalVo();
        vo.setItemId(rs.getString("itemId"));
        vo.setName(rs.getString("name"));
        vo.setYear(rs.getString("year"));
        vo.setMonth(rs.getString("month"));
        vo.setGoal(rs.getBigDecimal("goal"));
        return vo;
    }
}

1.6 Declare custom method in custom interface

package tw.com.abc.analysis.repository.custom;

import tw.com.abc.analysis.vo.GoalVo;

import java.io.IOException;
import java.util.List;

public interface GoalRepositoryCustom {

    List<GoalVo> findByYear(String year) throws IOException;

}

1.7 Implement custom class method

package tw.com.abc.analysis.repository.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import tw.com.abc.analysis.repository.custom.GoalRepositoryCustom;
import tw.com.abc.analysis.util.SqlFileLoader;
import tw.com.abc.analysis.vo.GoalVo;
import tw.com.abc.analysis.vo.rowmapper.GoalVoRowMapper;

import java.io.IOException;
import java.util.List;

public class GoalRepositoryImpl implements GoalRepositoryCustom {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    private SqlFileLoader sqlFileLoader;

    @Override
    public List<GoalVo> findByYear(String year) throws IOException {
        String sql = sqlFileLoader.getSqlFile("find_by_year.sql");

        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("year", year);

       return jdbcTemplate.query(sql, parameters, new GoalVoRowMapper());
    }
}