18일차 1 - Spring JDBC, Connection Pool

BoardDao를 new방식으로 생성하지 않고, bean으로 만든 후 Dependency Injection처리했으니 이제..

springboard에 Spring JDBC적용해보자.

먼저 dataSource만들고

<?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">
	
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
		<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
		<property name="username" value="oraclejava" />
		<property name="password" value="oraclejava" />
	</bean>

	<bean id="BoardDao" class="board.model.BoardDao" />
	<bean id="UserDao" class="user.model.UserDao" />
	
	<!-- 로그인 폼 -->
	<bean name="/Login.do" class="controller.Login" />

<!-- 생략 -->

 

BoardDao는 JdbcDaoSupport를 상속받게해.

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class BoardDao extends JdbcDaoSupport {

//생략

 

그러면 dataSource를 BoardDao에 주입시킬 수 있어.

<?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">
	
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
		<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
		<property name="username" value="oraclejava" />
		<property name="password" value="oraclejava" />
	</bean>

	<bean id="BoardDao" class="board.model.BoardDao" >
		<property name="dataSource" ref="dataSource"  />
	</bean>
	<bean id="UserDao" class="user.model.UserDao" />
	
	<!-- 로그인 폼 -->
	<bean name="/Login.do" class="controller.Login" />
<!-- 생략 -->

.

.

.

.

그러면 이제 BoardDao에서 Connection, PreparedStatement, ResultSet, getConnection(), dbClose()등은 필요없지.

먼저 getList()부터 변경하자.

	public List<BoardDto> getList(){
		List<BoardDto> list = new ArrayList<BoardDto>();	//upcasting 자식클래스의 인스턴스를 
												//부모클래스의 참조변수로 참조하는것.
		
		String sql = 
				"SELECT seq, title, name, to_char(writeday, 'YYYY/MM/DD') as writeday, readcount " +
				"FROM board " +
				"ORDER BY seq DESC ";
		
		try {
			getConnection();
			st = cn.prepareStatement(sql);	//퀴리 날리고 
			rs = st.executeQuery();	//실행
			
			while (rs.next()) {
				BoardDto boardDto = new BoardDto();
				
				boardDto.setSeq(rs.getInt("seq"));
				boardDto.setTitle(rs.getString("title"));
				boardDto.setName(rs.getString("name"));
				boardDto.setWriteday(rs.getString("writeday"));
				boardDto.setReadcount(rs.getInt("readcount"));
				
				list.add(boardDto);	//list에 붙여..
			} 
		}catch (Exception e){
			e.printStackTrace();
		} finally {
			dbClose();			
		}
		return list;					
	}

	public List<BoardDto> getList(){
		
		//쿼리만들고
		String sql = 
				"SELECT seq, title, name, to_char(writeday, 'YYYY/MM/DD') as writeday, readcount " +
				"FROM board " +
				"ORDER BY seq DESC ";
		
		//Mapping할거 만들고
		RowMapper rowMapper = new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int rownum) throws SQLException {
				
				BoardDto boardDto = new BoardDto();
				
				boardDto.setSeq(rs.getInt("seq"));
				boardDto.setTitle(rs.getString("title"));
				boardDto.setName(rs.getString("name"));
				boardDto.setWriteday(rs.getString("writeday"));
				boardDto.setReadcount(rs.getInt("readcount"));
				
				return boardDto;
			}
			
		};
		
		//실행하면 끝. 알아서 List만들어 리턴해.
		return getJdbcTemplate().query(sql, rowMapper);
	}

.

.

.

.

그럼 PreparedStatement의 setInt, setString같은 처리는 어떻게 할까?

getView(int seq)를 변경하며 알아보자.

	public BoardDto getView(int seq){
		
		String sql = 
				"SELECT seq, title, name, writeday, readcount, content " +
				"FROM board " +
				"WHERE seq = ? ";
		
		Object[] values = new Object[]{seq};
		
		RowMapper rowMapper = new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rownum) throws SQLException {
				// TODO Auto-generated method stub
				BoardDto boardDto = new BoardDto();
				
				boardDto.setSeq(rs.getInt("seq"));
				boardDto.setTitle(rs.getString("title"));
				boardDto.setName(rs.getString("name"));
				boardDto.setWriteday(rs.getString("writeday"));
				boardDto.setReadcount(rs.getInt("readcount"));
				boardDto.setContent(rs.getString("content"));	
				boardDto.setContent(rs.getString("content").replaceAll("\r\n", "<br/>"));

				return boardDto;
			}
			
		};		
				
		return (BoardDto) getJdbcTemplate().queryForObject(sql,  values, rowMapper);
	}

queryForObject에 Object 배열로 넘기면 되는구나.

.

.

.

.

executeUpdater의 경우는 어떻게 하는지 insertBoard(BoardDto boardDto)를 통해 알아보자.

	public boolean insertBoard(BoardDto boardDto){
		
		String sql = "insert into board (seq, title, name, password, content) " +
				"values (boardseq.nextval, ?, ?, ?, ?)";	
		
		Object[] values = new Object[] {				
				boardDto.getTitle(),
				boardDto.getName(),
				boardDto.getPassword(),
				boardDto.getContent()				
		}; 
		
		return getJdbcTemplate().update(sql, values) > 0 ? true: false;
	}

getJdbcTemplate().update(sql, values)를 사용하는 구나.

.

.

.

.

나머지를 모두 처리해보자.

package board.model;

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

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class BoardDao extends JdbcDaoSupport {


	public boolean insertBoard(BoardDto boardDto){
		
		String sql = "insert into board (seq, title, name, password, content) " +
				"values (boardseq.nextval, ?, ?, ?, ?)";	
		
		Object[] values = new Object[] {				
				boardDto.getTitle(),
				boardDto.getName(),
				boardDto.getPassword(),
				boardDto.getContent()				
		}; 
		
		return getJdbcTemplate().update(sql, values) > 0 ? true: false;
	}
	
	public List<BoardDto> getList(){
		
		//쿼리만들고
		String sql = 
				"SELECT seq, title, name, to_char(writeday, 'YYYY/MM/DD') as writeday, readcount " +
				"FROM board " +
				"ORDER BY seq DESC ";
		
		//Mapping할거 만들고
		RowMapper rowMapper = new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int rownum) throws SQLException {
				
				BoardDto boardDto = new BoardDto();
				
				boardDto.setSeq(rs.getInt("seq"));
				boardDto.setTitle(rs.getString("title"));
				boardDto.setName(rs.getString("name"));
				boardDto.setWriteday(rs.getString("writeday"));
				boardDto.setReadcount(rs.getInt("readcount"));
				
				return boardDto;
			}
			
		};
		
		//실행하면 끝. 알아서 List만들어 리턴해.
		return getJdbcTemplate().query(sql, rowMapper);
	}
	
	public BoardDto getView(int seq){
		
		String sql = 
				"SELECT seq, title, name, writeday, readcount, content " +
				"FROM board " +
				"WHERE seq = ? ";
		
		Object[] values = new Object[]{seq};
		
		RowMapper rowMapper = new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rownum) throws SQLException {
				// TODO Auto-generated method stub
				BoardDto boardDto = new BoardDto();
				
				boardDto.setSeq(rs.getInt("seq"));
				boardDto.setTitle(rs.getString("title"));
				boardDto.setName(rs.getString("name"));
				boardDto.setWriteday(rs.getString("writeday"));
				boardDto.setReadcount(rs.getInt("readcount"));
				boardDto.setContent(rs.getString("content"));	
				boardDto.setContent(rs.getString("content").replaceAll("\r\n", "<br/>"));

				return boardDto;
			}
			
		};		
				
		return (BoardDto) getJdbcTemplate().queryForObject(sql,  values, rowMapper);
	}
	
	
	public Boolean updateReadCount(int seq) {
	
		String sql = "UPDATE board SET " +
					 "	readcount = readcount+1 " +
					 "WHERE seq = ? ";		
		
		Object[] values = new Object[] { seq };

		return getJdbcTemplate().update(sql, values) > 0 ? true: false;
	}

	public boolean updateBoard(BoardDto boardDto) {

		String sql = "UPDATE board SET " +
					 "	title=?, name=?, content=? " +
					 "WHERE seq=? AND password=? ";		
		
		Object[] values = new Object[] {
				boardDto.getTitle(),
				boardDto.getName(),
				boardDto.getContent(),
				boardDto.getSeq(),
				boardDto.getPassword()
		};

		return getJdbcTemplate().update(sql, values) > 0 ? true: false;
	}

	public Boolean isView(int seq) {
		Boolean result =false;
		String sql = 
				"SELECT count(seq) " +
				"FROM board " +
				"WHERE seq=? ";
		
		Object[] values = new Object[] { seq };
		
		return getJdbcTemplate().queryForInt(sql, values) > 0 ? true: false;
	}

	public boolean deleteBoard(BoardDto boardDto) {

		String sql = "DELETE FROM board " +
					 "WHERE seq=? AND password=? ";	
		
		Object[] values = new Object[] {
				boardDto.getSeq(),
				boardDto.getPassword()
		};

		return getJdbcTemplate().update(sql, values) > 0 ? true: false;
	}	
}

.

.

.

.

이제 Spring에서 제공하는 Connection Pool방식으로 바꿔보자.

dispatcher-servlet.xml의 dataSaource만 수정하면 되네.

<?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">
	
	<!-- 	
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
		<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
		<property name="username" value="oraclejava" />
		<property name="password" value="oraclejava" />
	</bean> 
	-->

	<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName" value="OracleCP" /> <!-- META-INF/context.xml에 만들어 놓은 OracleCP -->
		<property name="resourceRef" value="true" />
	</bean>

	<bean id="BoardDao" class="board.model.BoardDao" >
		<property name="dataSource" ref="dataSource"  />
	</bean>
	<bean id="UserDao" class="user.model.UserDao" />
	
	<!-- 로그인 폼 -->
	<bean name="/Login.do" class="controller.Login" />

	<!-- 로그인 확인 -->
	<bean name="/LoginAction.do" class="controller.LoginAction" />

	<!-- 게시판 리스트 -->
	<bean name="/BoardList.do" class="controller.BoardList" >
		<property ref="BoardDao" name="boardDao"/>
	</bean>

	<!-- 게시판 글쓰기 폼 -->
	<bean name="/BoardInsert.do" class="controller.BoardInsert" />

	<!-- 게시판 글쓰기 데이터를 DB에 저장 -->
	<bean name="/BoardInsertAction.do" class="controller.BoardInsertAction"  >
		<property ref="BoardDao" name="boardDao"/>
	</bean>

	<!-- 게시판 상세보기	 -->
	<bean name="/BoardView.do" class="controller.BoardView"  >
		<property ref="BoardDao" name="boardDao"/>
	</bean>

	<!-- 게시판 글 수정 -->
	<bean name="/BoardUpdate.do" class="controller.BoardUpdate" >
		<property ref="BoardDao" name="boardDao"/>
	</bean>
	<!-- 게시판 글 수정 데이터를 DB에 저장 -->
	<bean name="/BoardUpdateAction.do" class="controller.BoardUpdateAction"  >
		<property ref="BoardDao" name="boardDao"/>
	</bean>

	<!-- 게시판 글 삭제 -->
	<bean name="/BoardDelete.do" class="controller.BoardDelete" />

	<!-- 글 DB에서 삭제 -->
	<bean name="/BoardDeleteAction.do" class="controller.BoardDeleteAction"  >
		<property ref="BoardDao" name="boardDao"/>
	</bean>
	
</beans>

.

.

.

.

잘 동작하는지 확인해봐.