[Spring] 오라클 DB 연결 - JdbcDaoSupport 상속 / JdbcTemplate

기본 준비

pom.xml파일에 jdbc, dbcp dependency 추가 후 확인

<!-- SpringJDBC -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
			
		<!-- DBCP:Database Connection Pool -->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>

꼭 maven이 잘 임포트 되었는지 확인한다. (Library>Maven Dependency)

 

1. JdbcDaoSupport 상속

BoardDAOSpring

JdbcDaoSupport를 상속 받고 관련 된 함수인 

setSuperDataSource(DataSource dataSource){  }와 getJdbcTemplate()을 사용하여 데이터에 접속한다.

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.hhw.biz.board.BoardVO;


@Repository
public class BoardDAOSpring extends JdbcDaoSupport {

	
	// SQL 명령어 상수 - Mybitis 연계가 쉽게 / 유지보수를 편하게
	private final String BOARD_INSERT = "insert into board (seq, title, writer, content) values ((select nvl(max(seq), 0)+1 from board),?,?,?)";
	private final String BOARD_UPDATE = "update board set title=?, content=? where seq=?";
	private final String BOARD_DELETE = "delete board where seq=?";
	private final String BOARD_GET = "select * from board where seq=?";
	private final String BOARD_LIST = "select * from board order by seq desc";

	public BoardDAOSpring() {
	}
	
	@Autowired
	public void setSuperDataSource(DataSource dataSource){
		super.setDataSource(dataSource);
	}
	
	public void insertBaord(BoardVO vo) {
		System.out.println("Spring JDBC insertBoard 기능처리");
		getJdbcTemplate().update(BOARD_INSERT,vo.getTitle(),vo.getWriter(),vo.getContent());
	}

	public void updateBaord(BoardVO vo) {
		System.out.println("JDBC updateBoard 기능처리");
		getJdbcTemplate().update(BOARD_UPDATE,vo.getTitle(),vo.getContent(),vo.getSeq());
	}

	public void deleteBaord(BoardVO vo) {
		System.out.println("JDBC deleteBoard 기능처리");
		getJdbcTemplate().update(BOARD_DELETE,vo.getSeq());
	}

	public BoardVO getBaord(BoardVO vo) {
		System.out.println("JDBC getBoard 기능처리");
		Object[] args = {vo.getSeq()};
		
		return getJdbcTemplate().queryForObject(BOARD_GET,args, new BoardRowMapper());
	}

	public List<BoardVO> getBaordList(BoardVO vo) {
		System.out.println("JDBC getBoardList 기능처리");
		
		return getJdbcTemplate().query(BOARD_LIST, new BoardRowMapper());
	}

BoardRowMapper.java

Jdbc를 사용하려면 rs값을 반환 받을 수 있게 만드는 RowMapper가 필요하다.

RowMapper<VO>를 구현하는 클래스를 만들어사용해준다.

package com.hhw.biz.board.impl;

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

import org.springframework.jdbc.core.RowMapper;

import com.hhw.biz.board.BoardVO;

/** JdbcTemplate의 메서드 중 queryForObject() /query()메서드에 대한 반환 타입으로 사용 
 * queryForObject() : 검색 결과가 1개일 경우(결과가 없거나, 2개 이상이면 예외처리)
 * query() : 검색 결과가 여러 개일 경우 (검색 결과의 갯수 만큼 mapRow()가 자동 호출 됨 =>List에 저장
 * **/
public class BoardRowMapper implements RowMapper<BoardVO> {

	public BoardRowMapper() {
	}

	/** mapRow()메서드는 SQL 쿼리 실행 후, 스프링 컨테이너가 자동 호출 
	 * ResultSet rs : sql쿼리 실행 결과
	 * int rowNum : sql 실행 결과에 대한 레코드 수
	 *  **/
	@Override
	public BoardVO mapRow(ResultSet rs, int rowNum) throws SQLException {
		BoardVO board = new BoardVO();
		
		board.setSeq(rs.getInt("seq"));
		board.setTitle(rs.getString("title"));
		board.setWriter(rs.getString("writer"));
		board.setContent(rs.getString("content"));
		board.setRegDate(rs.getDate("regdate"));
		board.setCnt(rs.getInt("cnt"));
		
		return board;
	}

}

BoardServiceImpl.java

package com.hhw.biz.board.impl;

import java.util.List;

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

import com.hhw.biz.board.BoardService;
import com.hhw.biz.board.BoardVO;

@Service("boardService")  //service 구현 받은데 붙는 어노테이션
public class BoardServiceImpl implements BoardService {

	@Autowired
	private BoardDAOSpring boardDAO;
	
	public BoardServiceImpl() {
	}

	@Override
	public void insertBaord(BoardVO vo) {
		boardDAO.insertBaord(vo);
	}

	@Override
	public void updateBaord(BoardVO vo) {
		boardDAO.updateBaord(vo);
	}

	@Override
	public void deleteBaord(BoardVO vo) {
		boardDAO.deleteBaord(vo);
	}

	@Override
	public BoardVO getBaord(BoardVO vo) {
		return boardDAO.getBaord(vo);
	}

	@Override
	public List<BoardVO> getBoardList(BoardVO vo) {
		return boardDAO.getBaordList(vo);
	}

}

applicationContext.xml

DataSource를 설정하는 방법에는 직접 xml문서에 경로를 지정하는 방법이 있고

또, .properties파일을 읽어서 경로를 지정하는 방법이 있다.

보통 여러명이 사용해야하는 경우 경로/아이디/비밀번호와 같이 바뀌는 부분들은 하드코드로 작성하지 않고 따로  .properties문서로 관리한다.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="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-4.2.xsd">

	<context:component-scan base-package="com.hhw.biz"></context:component-scan>
	
	<!-- DataSource 설정 -->
<!--  	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
		<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:XE"></property>
		<property name="username" value="hhw"></property>
		<property name="password" value="1234"></property>
	</bean>  -->

	<!-- DataSource Configuration Properties 파일 이용 -->
	<context:property-placeholder location="classpath:config/database.properties" />
	
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>

</beans>

 

JdbcTemplate 임포트

위의 프로젝트에서 DAO쪽과 Context쪽만 수정하면 됨 

BoardDAOSpring

package com.hhw.biz.board.impl;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.hhw.biz.board.BoardVO;

/** JdbcTemplate 사용하기  **/
@Repository
public class BoardDAOSpring {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	// SQL 명령어 상수 - Mybitis 연계가 쉽게 / 유지보수를 편하게
	private final String BOARD_INSERT = "insert into board (seq, title, writer, content) values ((select nvl(max(seq), 0)+1 from board),?,?,?)";
	private final String BOARD_UPDATE = "update board set title=?, content=? where seq=?";
	private final String BOARD_DELETE = "delete board where seq=?";
	private final String BOARD_GET = "select * from board where seq=?";
	private final String BOARD_LIST = "select * from board order by seq desc";

	public BoardDAOSpring() {
	}
	
	/** CRUD 기능 상세 구현 메서드들 **/
	/** 글 등록 **/
	public void insertBaord(BoardVO vo) {
		System.out.println("Spring JDBC insertBoard 기능처리");
		jdbcTemplate.update(BOARD_INSERT,vo.getTitle(),vo.getWriter(),vo.getContent());
	}

	/** 글 수정 **/
	public void updateBaord(BoardVO vo) {
		System.out.println("JDBC updateBoard 기능처리");
		jdbcTemplate.update(BOARD_UPDATE,vo.getTitle(),vo.getContent(),vo.getSeq());
	}

	/** 글 삭제 **/
	public void deleteBaord(BoardVO vo) {
		System.out.println("JDBC deleteBoard 기능처리");
		jdbcTemplate.update(BOARD_DELETE,vo.getSeq());
	}

	/** 글 상세 조회 **/
	public BoardVO getBaord(BoardVO vo) {
		System.out.println("JDBC getBoard 기능처리");
		Object[] args = {vo.getSeq()};
		
		return jdbcTemplate.queryForObject(BOARD_GET,args, new BoardRowMapper());
	}

	/** 글 목록조회 **/
	public List<BoardVO> getBaordList(BoardVO vo) {
		System.out.println("JDBC getBoardList 기능처리");
		
		return jdbcTemplate.query(BOARD_LIST, new BoardRowMapper());
	}

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="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-4.2.xsd">

	<context:component-scan base-package="com.hhw.biz"></context:component-scan>

	<!-- DataSource Configuration Properties 파일 이용 -->
	<context:property-placeholder location="classpath:config/database.properties" />
	
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>
	
	
	<!-- Spring JDBC :JdbcTemplate 메서드 사용을 위한 등록  / JdbcDaoSupport 상속 받을 경우 필요 ㄴㄴ-->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	
</beans>