JDBCTemplate

[1] RootConfig 파일에 jdbc 설정하기 

package kr.co.greenart.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.zaxxer.hikari.HikariDataSource;

@Configuration
@ComponentScan("kr.co.song.dao")
@PropertySource("classpath:kr/co/song/config/mysql.properties")
public class RootConfig {
	
	@Value("${jdbc.drivername}")
     private String drivername;
	
	@Value("${jdbc.url}")
	private String url;
			
			@Value("${jdbc.user}")
			private String user;
					
					@Value("${jdbc.password}")
					private String password;
							


	@Bean
	public DataSource hikariCp() {
		HikariDataSource dataSource = new HikariDataSource();
		dataSource.setDriverClassName(drivername);
		dataSource.setJdbcUrl(url);
		dataSource.setUsername(user);
		dataSource.setPassword(password);

		return dataSource;
	}
	@Bean
	@Autowired
	public JdbcTemplate jdbcTemplate(DataSource dataSource) {
		return new JdbcTemplate(dataSource);
		
	}
	
	@Bean
	@Autowired
	public NamedParameterJdbcTemplate namedJdbcTemplate (DataSource dataSource) {
		return new NamedParameterJdbcTemplate(dataSource);
	};
}

@PropertySource("classpath:kr/co/song/config/mysql.properties") 

 PropertySource 어노테이션에 참조하려는 properties 파일의 경로를 넣어주면 된다 

 

 

 

 

 

[2] 같은 패키지 안에 mysql.properties 파일 생성 

jdbc.drivername=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/my_db
jdbc.user=root
jdbc.password=root

 

 

[3] 레포지토리에서 JdbcTemplate 와 NamedJdbcTemplate 사용법 보기 

package kr.co.song.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import kr.co.song.model.Car;

@Repository
public class CarRepositoryImpl implements CarRepository {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private NamedParameterJdbcTemplate namedJdbcTemplate;
// qlsdp 등록 시킨 것 의존성 추가 

	private class CarMapper implements RowMapper<Car> {
		@Override
		public Car mapRow(ResultSet rs, int rowNum) throws SQLException {
			String licensenumber = rs.getString("LICENSE_NUMBER");
			String manufac = rs.getString("MANUFACTUER");
			String type = rs.getString("type");
			String year = rs.getString("year");
			String effi = rs.getString("efficiency");

			return new Car(licensenumber, manufac, type, year, effi);
		}
	}

	public List<Car> getAll() {
		RowMapper<Car> rowMapper = new BeanPropertyRowMapper<Car>(Car.class);
		// return jdbcTemplate.query("select * from Car", new CarMapper());
		// 우리가 만든 클래스에 필드에 대해서 겟터 세터가 있다면 해당하는 컬럼 이름에 대해서 알아서 셋터를 찾아서 값을 넣어준다
		return jdbcTemplate.query("select * from Car", rowMapper);
	}

	public List<Car> getByLicenseNumber(String licenseNumber) {
		return jdbcTemplate.query("select * from Car where license_number = ?", new CarMapper(), licenseNumber);

	}

	public int add(Car car) {
		return namedJdbcTemplate.update(
				"insert into Car values (:license_number, :manufacturer, :type, :year, :efficiency)",
				new BeanPropertySqlParameterSource(car));
	}

	

	@Override
	public int countAll() {
		// 결과 객체 하나만 받아볼때 사용
		return jdbcTemplate.queryForObject("select count(*) from car", Integer.class);
	}

	@Override
	public String getCarTypeByNumber(String licensenumber) {
		// TODO Auto-generated method stub
		return jdbcTemplate.queryForObject("select type from car where license_number = ?", String.class,
				licensenumber);
	}

//	@Override
//	public Map<String, Object> getAvgYearAndEff() {
//		// TODO Auto-generated method stub
//		// 컬림이름이 키값 
//		return jdbcTemplate.queryForMap("select AVG(year) as productionYear"
//				+",AVG(efficienc) as efficiency from car");
//	}

	@Override
	public int updateCarTypeByNumber(String licenseNumber, String type) {
		// TODO Auto-generated method stub
		return jdbcTemplate.update("Update car Set type = ? wherer license_number = ?", type, licenseNumber);
	}

	@Override
	public int delete(String licenseNumber) {
		// TODO Auto-generated method stub
		return jdbcTemplate.update("delete from car where license_number = ?", licenseNumber);
	}

}

 

jdbctemplate을 이용하면 기존에 preparedStatement와 ResultSet 그리고

Connection을 close하거나 소스를 짜는 게 확연하게 줄어드는 것을 확인할 수 있다. 

컬럼명에만 주의하면 될 것 같다.