[spring3.0] 스프링3.0게시판 스프링 JDBC로 변경하기

 

1. 필요한 라이브러리 다운

 

Spring JDBC Maven를 복사한다.

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

내 스프링 프레임워크 버전에 맞춘다

 

 

2. Servlet-context.xml 에서 의존성 주입 작성한다.

<!-- jdbcTemplate 사용 -->

<beans:bean id="boardDAO2" class="com.jdbc.dao.BoardDAO2">

	<beans:property name="jdbcTemplate" ref="jdbcTemplate"/>	

</beans:bean>


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

	<beans:constructor-arg ref="dataSource"/>

</beans:bean>

 

 

3. DAO 작성 코드 jdbc로 변경

1. jdbc 사용으로 변경한다.

 

public class BoardDAO2 {

	

	private JdbcTemplate jdbcTemplate;

	

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

		this.jdbcTemplate = jdbcTemplate;

	}

}

 

2. queryForInt

1. int형의 반환값을 받을 때 사용

2. jdbcTemplate.queryForInt(sql문,넘기는 값);

3. StringBuilder sql = new StringBuilder(100);

 - (숫자) : 글자수 지정

 - 원래는 변수.append(“□□□□□”);로 각각 작성하지만, 뒤에 세미콜론(;)을 빼고 .append(“□□□□”)로 작성 가능

   → 마지막에만 세미콜론(;) 작성

public int getMaxNum(){

	int maxNum = 0;
    
	StringBuilder sql = new StringBuilder(100);		

	sql.append("select nvl(max(num),0) from board");

	maxNum = jdbcTemplate.queryForInt(sql.toString());

	return maxNum;

}

 

getDataCount, updateHitCount도 작성

 

 getDataCount

public int getDataCount(String searchKey,String searchValue){

	int result = 0;

	StringBuilder sql = new StringBuilder(200);

	searchValue = "%" + searchValue + "%";

	sql.append("select nvl(count(*),0) from board ")

	.append("where " + searchKey + " like ?");

	result = jdbcTemplate.queryForInt(sql.toString(),searchValue);

	return result;	

}

updateHitCount

public int updateHitCount(int num){

	int result = 0;
	StringBuilder sql = new StringBuilder(200);	
	sql.append("update board set hitCount=hitCount + 1 where num=?");
    
	result = jdbcTemplate.update(sql.toString(),num);

	return result;

		

}

 

update

테이블을 수정할 때 사용

insert, update, delete

jdbcTemplate.update(sql문, 필요한 값)

 - ?에 들어가는 값은 순서대로 작성

 

public void insertData(BoardDTO dto){

	StringBuilder sql = new StringBuilder(200);

	sql.append("insert into board (num,name,pwd,email,subject,content,");

	sql.append("ipAddr,hitCount,created) ");

	sql.append("values(?,?,?,?,?,?,?,0,sysdate)");


	jdbcTemplate.update(sql.toString(),

			dto.getNum(),dto.getName(),dto.getPwd(),dto.getEmail(),

			dto.getSubject(),dto.getContent(),dto.getIpAddr());

}

 

updateHitCount, deleteData, updateData도 작성

 

updateHitCount

public int updateHitCount(int num){

	int result = 0;
	StringBuilder sql = new StringBuilder(200);		

	sql.append("update board set hitCount=hitCount + 1 where num=?");

	result = jdbcTemplate.update(sql.toString(),num);

	return result;

}

deleteData

public void deleteData(int num){

	StringBuilder sql = new StringBuilder(200);
    
	sql.append("delete board where num=?");

	jdbcTemplate.update(sql.toString(),num);

	
}

 

updateData

public void updateData(BoardDTO dto){

	StringBuilder sql = new StringBuilder(200);

	sql.append("update board set name=?, pwd=?, email=?, subject=?,")

	.append("content=? where num=?");

	jdbcTemplate.update(sql.toString(),

			dto.getName(),dto.getPwd(),dto.getEmail(),

			dto.getSubject(),dto.getContent(),dto.getNum());

}

 

4. queryForObject

select문에 사용

값을 읽을 때

 

public List<BoardDTO> getList(int start, int end, String searchKey, String searchValue){

		

	StringBuilder sql = new StringBuilder(500);

	searchValue = "%" + searchValue + "%";

		

	sql.append("select * from (")

	.append("select rownum rnum,data.* from(")

	.append("select num,name,subject,hitCount,")

	.append("to_char(created,'YYYY-MM-DD') created ")

	.append("from board where " + searchKey + " like ? order by num desc) data) ")

	.append("where rnum >= ? and rnum <= ?");

			

	List<BoardDTO> lists =

			jdbcTemplate.query(sql.toString(),

				new Object[] {searchValue,start,end},
				new RowMapper<BoardDTO>() {

					@Override
					public BoardDTO mapRow(ResultSet rs, int rowNum) throws SQLException {

						BoardDTO dto = new BoardDTO();
					

						dto.setNum(rs.getInt("num"));

						dto.setName(rs.getString("name"));

						dto.setSubject(rs.getString("subject"));

						dto.setHitCount(rs.getInt("hitCount"));

						dto.setCreated(rs.getString("created"));

						return dto;

					}	

			});	


	return lists;

				

}