스프링 JDBC 연동을 통한 쇼핑몰: 커넥션 풀

직전 예제에서는 SimpleUrlHandlerMapping 방식으로 각 객체에 대한 주입 설정을 했는데 여기에서는 어노테이션 방식으로 모두 변경한다.

다음 JDBC 부분을 DBCP(데이터베이스 커넥션풀) 방식으로 변경한다.

1. 어노테이션 방식 설정 파일 

servlet-context.xml에서

<annotation-driven />

<context:component-scan base-package="mvc.jdbc" />

이 2개만 있으면 사용 가능하다.

다음 각 클래스에 어노테이션을 붙여준다.

컨트롤러는 @Controller

서비스는 @Service

DAO는 @Repository

DTO는 @Component

각 클래스에서 주입받는 메서드에는 @Autowired만 붙여주면 끝.

1) 설정 파일

<?xml version="1.0" encoding="UTF-8"?>

<beans:beans xmlns="http://www.springframework.org/schema/mvc"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:beans="http://www.springframework.org/schema/beans"

xmlns:context="http://www.springframework.org/schema/context"

xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->

<!-- Enables the Spring MVC @Controller programming model -->

<annotation-driven />

<!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->

<resources mapping="/resources/**" location="/resources/" />

<!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->

<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">

<beans:property name="prefix" value="/WEB-INF/views/jsp/" />

<beans:property name="suffix" value=".jsp" />

</beans:bean>

<context:component-scan base-package="mvc.jdbc" />

</beans:beans>

2) 컨트롤러

package mvc.jdbc.controller;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.servlet.ModelAndView;

import mvc.jdbc.dto.ItemBean;

import mvc.jdbc.service.GoodsItem;

@Controller

public class ShoppingController {

@Autowired

private GoodsItem goodsItem;

public void setGoodsItem(GoodsItem goodsItem) {

this.goodsItem = goodsItem;

}

@RequestMapping("/main.do")

public ModelAndView main() {

List<ItemBean> list = goodsItem.getItemList();

Map<String, Object> model = new HashMap<String, Object>();

model.put("itemList", list);

return new ModelAndView("main", model);

}

@RequestMapping(value="/content.do")

public ModelAndView contentItem(int item_id) {

ModelAndView mav = new ModelAndView();

ItemBean item = goodsItem.getItem_id(item_id);

Map<String, Object> model = new HashMap<String, Object>();

model.put("item", item);

mav.setViewName("content"); // 이렇게 한 뒤 content.jsp 만들어주면 됨. (view page 연결)

mav.addAllObjects(model); // 담아온 값을 전달할 때엔 이렇게 한다.

return mav;

}

}

3) DTO 클래스 (자바빈)

package mvc.jdbc.dto;

import org.springframework.stereotype.Component;

@Component

public class ItemBean {

private int item_id;

private String item_name;

private int item_price;

private String item_des;

private String item_pic;

public int getItem_id() {

return item_id;

}

public void setItem_id(int item_id) {

this.item_id = item_id;

}

public String getItem_name() {

return item_name;

}

public void setItem_name(String item_name) {

this.item_name = item_name;

}

public int getItem_price() {

return item_price;

}

public void setItem_price(int item_price) {

this.item_price = item_price;

}

public String getItem_des() {

return item_des;

}

public void setItem_des(String item_des) {

this.item_des = item_des;

}

public String getItem_pic() {

return item_pic;

}

public void setItem_pic(String item_pic) {

this.item_pic = item_pic;

}

}

4) DAO 클래스

package mvc.jdbc.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.stereotype.Repository;

import mvc.jdbc.dto.ItemBean;

@Repository

public class ItemDaoImpl implements ItemDao {

@Autowired

private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

@Override

public List<ItemBean> findAll() {

String sql = "select * from GoodsItem order by item_id asc";

return jdbcTemplate.query(sql, new RowMapper<ItemBean>(){

// 읽어온 데이터를 입력으로 반환해주며 호출해주는 메서드 

// 인터페이스 RowMapper는 객체를 생성할 수 없어 바로 오버라이딩하여 객체 생성

@Override

public ItemBean mapRow(ResultSet arg0, int arg1) throws SQLException {

ItemBean item = new ItemBean();

item.setItem_id(arg0.getInt("item_id"));

item.setItem_name(arg0.getString("item_name"));

item.setItem_price(arg0.getInt("item_price"));

item.setItem_des(arg0.getString("item_des"));

item.setItem_pic(arg0.getString("item_pic"));

return item;

}

});

}

@Override

public ItemBean findItem_id(int item_id) {

// TODO Auto-generated method stub

String sql = "select * from GoodsItem where item_id = ?";

return jdbcTemplate.queryForObject(sql, new RowMapper<ItemBean>(){

@Override

public ItemBean mapRow(ResultSet arg0, int arg1) throws SQLException {

// TODO Auto-generated method stub

ItemBean item = new ItemBean();

item.setItem_id(arg0.getInt("item_id"));

item.setItem_name(arg0.getString("item_name"));

item.setItem_price(arg0.getInt("item_price"));

item.setItem_des(arg0.getString("item_des"));

item.setItem_pic(arg0.getString("item_pic"));

return item;

}

}, item_id);

}

}

5) 서비스 클래스

package mvc.jdbc.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import mvc.jdbc.dao.ItemDao;

import mvc.jdbc.dto.ItemBean;

@Service

public class GoodsItemImpl implements GoodsItem {

@Autowired // 자바빈 객체 자동 주입

private ItemDao itemDao; 

public void setItemDao(ItemDao itemDao) { 

this.itemDao = itemDao;

}

@Override

public List<ItemBean> getItemList() {

List<ItemBean> list = itemDao.findAll();

return list;

}

@Override

public ItemBean getItem_id(int item_id) {

return itemDao.findItem_id(item_id); 

}

}

스프링에서 커넥션 풀 설정 방법

1. 커넥션 풀 모듈 추가

1) pom.xml에 다음과 같이 모듈 추가

<!-- DBCP 추가 -->

<dependency>

<groupId>commons-dbcp</groupId>

<artifactId>commons-dbcp</artifactId>

<version>20030825.184428</version>

</dependency>

<dependency>

<groupId>commons-pool</groupId>

<artifactId>commons-pool</artifactId>

<version>20030825.183949</version>

</dependency>

<dependency>

<groupId>commons-collections</groupId>

<artifactId>commons-collections</artifactId>

<version>20040616</version>

</dependency>

2) root-context.xml

데이터베이스 설정에서 커넥션 풀 연동 설정

<!-- Data Source : DBCP 커넥션 풀 설정 -->

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

이하는 동일하다가 DBCP용 추가 프로퍼티를 넣어준다.

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

<!-- Root Context: defines shared resources visible to all other web components -->

<!-- Data Source : DBCP 커넥션 풀 설정 -->

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>

<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></property>

<property name="username" value="hr"></property>

<property name="password" value="tiger"></property>

<property name="maxActive" value="50" /> <!-- maxActive: 최대 커넥션 개수 -->

<property name="maxIdle" value="30" /> <!-- maxIdle: 접속 없을 경우 최대 유지 커넥션 개수 -->

<property name="minIdle" value="10" /> <!-- minIdle: 접속 없을 경우 최소 유지 커넥션 개수 -->

<property name="maxWait" value="5" /> <!-- maxWait: 최대 대기 시간(초): 초과시 연결 실패 오류 발생 -->

</bean>

<!-- JdbcTemplate 객체 생성 -->

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

<constructor-arg ref="dataSource" />

</bean>

</beans>

이제 실행하면 전 예제와 마찬가지로 아무 이상없이 실행된다.