[SpringBoot] Spring JDBC 사용하여 데이터베이스 연결하기 [JDBC Template, Groovy 활용]

---

[SpringBoot] Service 생성 및 의존성 주입 [생성자 주입을 권장하는 이유]

---

- Spring JDBC 사용하여 데이터베이스 연결하기

1. Spring JDBC 의존성 주입

// database
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
runtimeOnly 'com.mysql:mysql-connector-j'

 

JDBC 관련 의존성 주입

---

2. MySQL Test DB 연동

[MySQL] MySQL Test DB 다운로드 방법 [MySQL wolrd DB 다운로드]

---

world의 city 데이터를 사용할 예정

application.properties에 MySQL jdbc 정보를 입력한다.

 

- Access denied for user 해결방법

[MySQL] Access denied for user 'user'@'localhost' [user 계정에 접근 권한이 없습니다]

---

City 테이블의 model 생성

City 테이블의 Repository 생성

---

3. Groovy 적용

클래스 내에 Multiline String을 선언하기 위해, Groovy를 사용한다.

#build.gradle에 Grrovy plugins와 dependencies 추가
plugins {
    id 'java'
    id 'org.springframework.boot' version '2.7.6'
    id 'io.spring.dependency-management' version '1.0.15.RELEASE'
    id 'groovy' # plugins 추가
}

dependencies {
	implementation('org.codehaus.groovy:groovy') # groovy 추가
}

---

STS에서 Groovy plugin 설치

다 설치해주면 된다.

CitySql.groovy 파일 생성 후, 멀티스트링으로 SQL 문을 작성한다.

---

4. Mapper 생성 및 Groovy 적용

- CityRowMapper

package kr.co.yermi.firstboot.info.repository;

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

import org.springframework.jdbc.core.RowMapper;

import kr.co.yermi.firstboot.info.model.City;

public class CityRowMapper implements RowMapper<City> {

	@Override
	public City mapRow(ResultSet rs, int rowNum) throws SQLException {
		City city = new City();
		city.setId(rs.getInt("ID"));
		city.setName(rs.getString("Name"));
		city.setCountryCode(rs.getString("countrycode"));
		city.setDistrict(rs.getString("district"));
		city.setPopulation(rs.getInt("population"));
		return city;
	}

}

---

- CityRepository

package kr.co.yermi.firstboot.info.repository;

import java.util.List;

import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import org.springframework.stereotype.Repository;

import kr.co.yermi.firstboot.info.model.City;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Repository
public class CityRepository {
	
	private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private final CityRowMapper cityRowMapper;
	
	public CityRepository(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
		this.cityRowMapper = new CityRowMapper();
	}

	public List<City> findList(){
		log.debug("query : {}", CitySql.SELECT);
		
		return namedParameterJdbcTemplate.query(CitySql.SELECT, EmptySqlParameterSource.INSTANCE, this.cityRowMapper);
	}
}

---

- InfoService

package kr.co.yermi.firstboot.info;

import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Service;

import kr.co.yermi.firstboot.info.model.City;
import kr.co.yermi.firstboot.info.model.Project;
import kr.co.yermi.firstboot.info.repository.CityRepository;

@Service
public class InfoService {
	
	private final CityRepository cityRepository;
	
	// spring 4.2 이상은 @Autowired 생략 가능
	public InfoService(CityRepository cityRepository) {
		this.cityRepository = cityRepository;
	}
	
	public Project getProjectInfo() {
		Project project = new Project();
		project.projectName = "firstboot";
		project.author = "yermi";
		project.createDate = new Date();
		
		return project;
	}
	
	public List<City> getCityList() {
		return this.cityRepository.findList();
	}
}

---

- InfoController

package kr.co.yermi.firstboot.info;

import java.util.List;

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

import kr.co.yermi.firstboot.info.model.City;
import kr.co.yermi.firstboot.info.model.Project;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@RestController
public class InfoController {
	
    private InfoService infoService;
	
    @Autowired
	public InfoController(InfoService infoService) {
		this.infoService = infoService;
	}
	
	@GetMapping("/cityList")
	public List<City> cityList() {
		log.debug("/cityList start");
		return infoService.getCityList();
	}
	
	@GetMapping("/info")
	public Project projectInfo() {
		log.debug("/info start");
		Project project = infoService.getProjectInfo();
		return project;
	}
}

---

cityList 실행 결과

---

- 참고자료

[SpringBoot] RestApi 만들기 (5.1) MySQL + JDBC Template

---