[부스트코스 웹] Spring JDBC 실습

June·2021년 1월 7일

부스트코스

목록 보기

20/23

실습

1. pom.xml 변경

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>kr.or.connect</groupId>
	<artifactId>daoexam</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>daoexam</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<spring.version>4.3.5.RELEASE</spring.version>
	</properties>

	<dependencies>
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- basic data source -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.1.1</version>
		</dependency>


		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.45</version>
		</dependency>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>3.8.1</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.6.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

2. config 파일 생성

ApplicationContext는 Spring이 제공해준다. ApplicationContext에 어떤 파일들을 읽어들이면되는지 알려주는 ApplicationConfig 파일을 생성해야 한다.

ApplicationConfig.java

package kr.or.connect.daoexam.config;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;

@Configuration
@Import({DBConfig.class})
public class ApplicationConfig {

}

DBConfig.java

package kr.or.connect.daoexam.config;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
public class DBConfig {
	private String driverClassName = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/connectdb?useUnicode=true&characterEncoding=utf8&useSSL=false";

    private String username = "connectuser";
    private String password = "connect123!@#";

    @Bean
    public DataSource dataSource() {
    	BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;

    }
}

3. DataSourceTest 파일 생성

package kr.or.connect.daoexam.main;

import java.sql.Connection;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import kr.or.connect.daoexam.config.ApplicationConfig;

public class DataSourceTest {

	public static void main(String[] args) {
		ApplicationContext ac = new AnnotationConfigApplicationContext(ApplicationConfig.class);
		DataSource ds = ac.getBean(DataSource.class);
		Connection conn = null;
		try {
			conn = ds.getConnection();
			if(conn != null)
				System.out.println("접속 성공^^");
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) {
				try {
					conn.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

}

Config를 이용해서 ApplicationContext 객체를 만든다. 이 객체는 컨테이며 import를 통해서 DataSource도 관리하고 있다. 따라서 getBean을 통해서 DataSource 객체를 만들 수 있다. 이 DataSource 객체에서 Connection 객체를 가져올 수 있다.

Role.java (DTO 생성)

package kr.or.connect.daoexam.dto;

public class Role {
	private int roleId;
	private String description;
	
	public int getRoleId() {
		return roleId;
	}
	public void setRoleId(int roleId) {
		this.roleId = roleId;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	@Override
	public String toString() {
		return "Role [roleId=" + roleId + ", description=" + description + "]";
	}
	
}

RoleDaoSqls.java

package kr.or.connect.daoexam.dao;

public class RoleDaoSqls {
	public static final String SELECT_ALL = "SELECT role_id, description FROM role order by role_id";
}

RoleDao.java

package kr.or.connect.daoexam.dao;

import static kr.or.connect.daoexam.dao.RoleDaoSqls.*;

import java.util.Collections;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import kr.or.connect.daoexam.dto.Role;
@Repository
public class RoleDao {
	private NamedParameterJdbcTemplate jdbc;
	private SimpleJdbcInsert insertAction;
	private RowMapper<Role> rowMapper = BeanPropertyRowMapper.newInstance(Role.class);

	public RoleDao(DataSource dataSource) {
		this.jdbc = new NamedParameterJdbcTemplate(dataSource);
		this.insertAction = new SimpleJdbcInsert(dataSource)
                .withTableName("role");
	}
	
	public List<Role> selectAll() {
		return jdbc.query(SELECT_ALL, EmptySqlParameterSource.INSTANCE, rowMapper);
	}
}

ApplicationConfig.java에 추가

@ComponentScan(basePackages = { "kr.or.connect.daoexam.dao" })

SelectAllTest.java

package kr.or.connect.daoexam.main;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import kr.or.connect.daoexam.config.ApplicationConfig;
import kr.or.connect.daoexam.dao.RoleDao;
import kr.or.connect.daoexam.dto.Role;

public class SelectAllTest {

	public static void main(String[] args) {
		ApplicationContext ac = new AnnotationConfigApplicationContext(ApplicationConfig.class); 
		
		RoleDao roleDao =ac.getBean(RoleDao.class);

		List<Role> list = roleDao.selectAll();
		
		for(Role role: list) {
			System.out.println(role);
		}

	}

}

실습 2

Update & Insert 기능 추가하기

RoleDaoSqls.java

public static final String UPDATE = "UPDATE role SET description = :description WHERE ROLE_ID = :roleId";

RoleDao.java

	public int insert(Role role) {
		SqlParameterSource params = new BeanPropertySqlParameterSource(role);
		return insertAction.execute(params);
	}

	public int update(Role role) {
		SqlParameterSource params = new BeanPropertySqlParameterSource(role);
		return jdbc.update(UPDATE, params);
	}

JDBCTest.java

		RoleDao roleDao = ac.getBean(RoleDao.class);
		Role role = new Role();
		role.setRoleId(201);
		role.setDescription("PROGRAMMER");
		
		int count = roleDao.insert(role);
		System.out.println(count + "건 입력하였습니다.");
			
		int count = roleDao.update(role);
		System.out.println(count +  " 건 수정하였습니다.");

실습 3

SELECT & DELETE 기능 추가하기

RoleDaoSqls.java

	public static final String SELECT_BY_ROLE_ID = "SELECT role_id, description FROM role WHERE role_id = :roldId";
	public static final String DELETE_BY_ROLE_ID = "DELETE FROM role WHERE rold_id = :roldId";

RoleDao.java

	public int deleteById(Integer id) {
		Map<String, ?> params = Collections.singletonMap("roleId", id);
		return jdbc.update(DELETE_BY_ROLE_ID, params);
	}
	
	public Role selectById(Integer id) {
		try {
			Map<String, ?> params = Collections.singletonMap("roldId", id);
			return jdbc.queryForObject(SELECT_BY_ROLE_ID, params, rowMapper);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

JDBCTest.java 추가

Role resultRole = roleDao.selectById(201);
System.out.println(resultRole);
		
int deleteCount = roleDao.deleteById(500);
System.out.println(deleteCount + "건 삭제하였습니다.");
	
Role resultRole2 = roleDao.selectById(500);
System.out.println(resultRole2);

생각해보기

JdbcTemplate을 이용하지 않고 NamedParameterJdbcTemplate를 이용하여 DAO를 작성한 이유는 무엇이라고 생각하나요?

JdbcTemplate을 사용하면 자바에 존재하는 쿼리문과 DB 연산으로 부터 발생하는 값 객체의 파라미터를 관리하기 매우 힘들어진다. 대표적인 예로, Java는 클래스의 인스턴스 변수에 camelCase를 주로 사용하고 sql은 Column명으로 snake case를 주로 사용하기 때문에 서로 통신하기 위해서 Parsing 하는 작업이 필요 하고 이러한 작업들을 NamedParameterJdbcTemplate이 지원해준다. 만약 JdbcTemplate을 직접 사용한다면 NamedParameterJdbcTemplate가 재공해주는 Parsing 작업들을 개발자가 직접해줘야만 하고 이는 Type Safe하지 못할 뿐더러 많은 중복된 코드를 발생시킨다. 이러한 중복된 코드는 모든 에러의 근원이며 이를 없애기 위해서 JdbcTemplate 대신 NamedParameterJdbcTemplate를 사용할 것을 권장한다.

June

이전 포스트

[부스트코스 웹] DAO와 DTO 개념

다음 포스트

[부스트코스 웹] Spring JDBC 실습