[JSP] Spring JDBC

Spring JDBC

- JDBC 프로그래밍에서 반복되는 개발 요소를 스프링 프레임워크가 대신 처리

- 개발자는 필요한 부분만 개발하면 된다!!

JDBC Template

- org.springframework.jdbc.core 에서 가장 중요한 클래스

- select, insert, update, delete 의 작업을 할 수 있음

NamedParameterJdbcTemplate

- 전통적인 JDBC의 "?" 플레이스홀더 대신에 이름있는 파라미터를 제공하기 위해서 사용

DAO (Data Access Object)

- 데이터를 조회하거나 조작하는 기능을 전담하도록 만든 객체

queryForObject

Db로부터 1개의 레코드를 가져와 자바 객체에 저장한다. 오직 1개의 레코드만 반환되는 여부를 검사하는 메소드이므로 하나이상의 레코드가 반환되는 경우 예외처리됨.

그리고 값이 없을경우는 null을 반환

queryForList

DB로부터 1개 이상의 레코드를 가져와 자바 객체의 List를 만드는데 사용

List의 각 엔트리는 해당 열의 컬럼값을 맵으로 나타낸 Map이다

public String getName() {
    return (String) this.jdbcTemplate.queryForObject("select name from mytable", String.class);
}
 
public List<Map<String, Object>> getList() {
    return this.jdbcTemplate.queryForList("select * from mytable");
}

DTO (Data Transfer Object)

- 계층간 데이터 교환을 위한 JavaBean

- 로직을 가지고 있지 않고, 순수한 데이터 객체

ConncectionPool

- DB연결은 비용이 많이 들기때문에 미리 커넥션을 여러개 맺어둠

- 커넥션이 필요하면 커넥션풀에게 빌려서 사용한 후 반납

DataSource

-  커넥션풀을 관리하는 목적으로 사용되는 객체

- DataSource를 이용하여 커넥션을 얻어오고 반납하는 작업을 수행

[ 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.5.0</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>
    <finalName>daoexam</finalName>
    <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>

[ ApplicationConfig ]

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
@ComponentScan(basePackages = { "kr.or.connect.daoexam.dao" })
@Import({DBConfig.class})
public class ApplicationConfig {
    
}

[ DBConfig ]

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";
    private String username = "connectuser";
    private String password = "1108";
    
    @Bean
    public DataSource dataSource() {
            BasicDataSource dataSource = new BasicDataSource();
            dataSource.setDriverClassName(driverClassName);
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            return dataSource;
    }
}
 

[ Role ]

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 ]

package kr.or.connect.daoexam.dao;
 
public class RoleDaoSqls {
    public static final String SELECT = "SELECT role_id, description FROM role WHERE role_id = :role_id";
    public static final String SELECT_ALL = "SELECT role_id, description FROM role order by role_id";
    public static final String UPDATE = "UPDATE role set description = :description WHERE role_id = :role_id";
    public static final String DELETE = "DELETE FROM role WHERE role_id = :rold_id";
}
 

[ RoleDao ]

package kr.or.connect.daoexam.dao;
 
import static kr.or.connect.daoexam.dao.RoleDaoSqls.DELETE;
import static kr.or.connect.daoexam.dao.RoleDaoSqls.SELECT;
import static kr.or.connect.daoexam.dao.RoleDaoSqls.SELECT_ALL;
import static kr.or.connect.daoexam.dao.RoleDaoSqls.UPDATE;
 
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.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, Collections.emptyMap(), rowMapper);
    }
    
    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);
    }
    
    public int delete(Integer id) {
        Map<String, ?> params = Collections.singletonMap("roleId", id);
        return jdbc.update(DELETE, params);
    }
    
    public Role select(Integer id) {
        try {
            Map<String, ?> params = Collections.singletonMap("roleId", id);
            return jdbc.queryForObject(SELECT, params, rowMapper);
        }catch(EmptyResultDataAccessException e) {
            return null;
        }
    }
}