Spring Boot_MyBatis if문(동적 쿼리 생성) 사용하기

MyBatis if문 (동적 쿼리 생성) 사용하기

 

AccountController.java

@Controller
@RequestParam("/account")
public class AccountController {
 
    @Autowired
    private AccountService accountService;
    @Autowired
    private HttpSession httpSession;
    
    @GetMapping("/detail/{id}")
    public String detail(@PathVariable Integer id, @RequestParam(name = "type"
        , defaultValue = "all", required = false, Model, model) {
        
        User principal = (User)session.getAttribute(Define.PRINCIPAL);
        if(principal == null) {
            throw new CustomRestfullException("로그인 먼저 해주세요.", HttpStatus.UNAUTHORIZED);
        }
        
        // 로깅 
        System.out.println("type : " + type);
        
        // 서비스 호출
        Account account = accountService.readAccount(id);
        List<Account> accountList = accountService.readHistoryListByAccount(type, id);
        
        model.addAttribute(Define.PRINCIPAL, principal);
        model.addAttribute("account", account);
        model.addAttribute("historyList", historyList);
        
        return "/account/detail";
    }
 
}

 

 

history.xml

<mapper namespace="com.tenco.bank.repository.interfaces.HistoryRepository>
 
    <select id="findByIdHistoryType" resultType="com.tenco.bank.dto.response.HistoryDto">
        
        <if test="type == 'deposit'">
            select h.id, h.amount, h.d_balance as balance, h.created_at,
                ifnull(wa.number, 'ATM') as sender,
                da.number as receiver
            from history_tb as h
            left join account_tb as da
            on h.d_account_id = da.id
            left join account_tb as wa
            on h.w_account_id = wa.id
            where h.d_account_id = #{id};
        </if>
        
        <if test="type == 'withdraw'">
            select h.id, h.amount, h.w_balance as balance, h.created_at, 
                ifnull(da.number, 'ATM') as receiver,
                wa.number as sender
            from history_tb as h
            left join account_tb as wa
            on h.w_account_id = wa.id
            left join account_tb as da
            on h.d_account_id = da.id
            where h.w_account_id = #{id};
        </if>
        
        <if test="type == 'all'">
            select h.id, h.amount, case when h.w_account_id = #{id} then (h.w_balance)
                when h.d_account_id=#{id} then (h.d_balance) end as balance,
                ifnull(wa.number, 'ATM') as sender,
                ifnull(da.number, 'ATM') as receiver,
                h.created_at
            from history_tb as h
            left join account_tb as da
            on h.d_account_id = da.id
            left join account_tb as wa 
            on h.w_account_id = wa.id
            where h.d_account_id = #{id} or h.w_account_id = #{id};
        </if>
        
    </select>
 
</mapper>

 

 

HistoryRepository.java

@Mapper // MyBatis 의존성 주입
public interface HistoryRepository {
    
    // @Param 잊지마 !!!!!!!!!!!!!!!!!!!
    // !!!!!! 매개변수 갯수가 2개 이상이면 반드시 파라미터 이름을 명시해줘야 함 !!!!!!!
    public List<HistoryDto> findByIdHistoryType(@Param("type") String type, @Param("id") Integer id);
}

 

 

AccountService.java

@Service
public class AccountService {
 
    @Autowired 
    private AccountRepository accountRepository;
    @Autowired
    private HistoryRepository historyRepository;
    
    // 단일 계좌 검색 기능
    @Transactional
    public Account readAccount(Integer id) {
        Account accountEntity = accountRepository.findById(id);
        
        if(accountEntity == null) {
            throw new CustomRestfullException("해당 계좌를 찾을 수 없습니다.", HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }
    
    // 단일 계좌 거래 내역 검색
    // @Param type = [all, deposit, withdraw]
    // @Param id(account_id)
    // @return 입금, 출금, 입출금 거래내역 (3가지)
    @Transactional
    public List<HistoryDto> readHistoryListByAccount(String type, Integer id) {
        
        List<HistoryDto> historyDtos = historyRepository.findByIdHistoryType(type, id);
        
        historyDtos.forEach(e -> {
            System.out.println(e);
        });
        
        // 거래내역이 없을 수도 있으니 예외처리 안 함.
        return historyDtos;
    }
 
}