소스코드 다운받기

21-1 Spring JDBC (JdbcTemplate) 이용한 반복코드 줄이기

  • 드라이버 로드, 커넥션 생성 & DB 연결, SQL 실행, DB 연결 해제 부분은 매번 같은 동작을 반복한다
  • JDBC Template을 이용해서 이러한 작업들을 간단하게 처리 할 수 있다

21-2 Spring빈을 이용한 코드 간소화

  • Datasource빈이 들어있는 JdbcTemplate빈을 이용하면 DB관련 클래스들을 생성하고 조립할 수 있다

Dependency 추가(pom.xml)

<dependencies>
	<!-- 생략 -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>4.1.4.RELEASE</version>
	</dependency>
</dependencies>

BController에 JdbcTemplate 추가 & Setter 만들기 & Autowired

public class BController {
	BCommand command;

	public JdbcTemplate template;

	@Autowired // autoscan. 빈이 만들어질때 바로 setting
	public void setTemplate(JdbcTemplate template){
		this.template = template;
		// template을 전역적으로 사용하기 위해 Constant 파일을 만듬
		Constant.template = this.template;
	}

	//생략
}

Constant.java 파일 만들기

public class Constant {
	public static JdbcTemplate template;
}

스프링 설정파일에 Spring빈 추가(servlet-context.xml)

  • dataSource 빈 추가
  • template 빈 추가 - dataSource를 포함하도록
<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
	<beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
	<beans:property name="username" value="jonny" />
	<beans:property name="password" value="1234" />
</beans:bean>

<beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
	<beans:property name="dataSource" ref="dataSource" />

</beans:bean>

21-3 JDBC를 이용한 리스트 목록 만들기

엄청 짧아졌다!!

BDao의 list메서드 수정하기

public ArrayList<BDto> list() {
	String query = "select bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent from mvc_board order by bGroup desc, bStep asc";
	return (ArrayList<BDto>) template.query(query, new BeanPropertyRowMapper<BDto>(BDto.class));
}
  • 드라이버 로드, 커넥션 생성 & DB 연결, SQL 실행, DB 연결 해제 부분은 JDBC 템플릿이 알아서 해준다

21-4 Insert, Update, Delte 처리하기

BDao의 각 메서드 변경


public class BDao {

	JdbcTemplate template;
	
	public BDao() {
		this.template = Constant.template;
	}
	
	public void write(final String bName, final String bTitle, final String bContent) {
		
		this.template.update(new PreparedStatementCreator() {
			
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				String query = "insert into mvc_board (bId, bName, bTitle, bContent, bHit, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, 0, mvc_board_seq.currval, 0, 0 )";
				PreparedStatement pstmt = con.prepareStatement(query);
				pstmt.setString(1, bName);
				pstmt.setString(2, bTitle);
				pstmt.setString(3, bContent);
				return pstmt;
			}
		});
	}
	
	// 중복이지만 위치 참고하려고 넣었다
	public ArrayList<BDto> list() {
		
		String query = "select bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent from mvc_board order by bGroup desc, bStep asc";
		return (ArrayList<BDto>) template.query(query, new BeanPropertyRowMapper<BDto>(BDto.class));
		
	}
	
	public BDto contentView(String strID) {
		upHit(strID);
		String query = "select * from mvc_board where bId = " + strID;
		return template.queryForObject(query, new BeanPropertyRowMapper<BDto>(BDto.class));
	}
	
	public void modify(final String bId, final String bName, final String bTitle, final String bContent) {
		String query = "update mvc_board set bName = ?, bTitle = ?, bContent = ? where bId = ?";
		this.template.update(query, new PreparedStatementSetter() {

			// 아직 setString, setInt부분은 조금 불편하네?
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, bName);
				ps.setString(2, bTitle);
				ps.setString(3, bContent);
				ps.setInt(4, Integer.parseInt(bId));
			}
		});
	}
	
	public void delete(final String bId) {
		String query = "delete from mvc_board where bId = ?";
		
		this.template.update(query, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, bId);
			}
		});
		
	}
	
	public BDto reply_view(String str) {
		String query = "select * from mvc_board where bId = " + str;
		return template.queryForObject(query, new BeanPropertyRowMapper<BDto>(BDto.class));
	}
	
	public void reply(final String bId, final String bName, final String bTitle, final String bContent, final String bGroup, final String bStep, final String bIndent) {
		replyShape(bGroup, bStep);
		String query = "insert into mvc_board (bId, bName, bTitle, bContent, bGroup, bStep, bIndent) values (mvc_board_seq.nextval, ?, ?, ?, ?, ?, ?)";
		this.template.update(query, new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, bName);
				ps.setString(2, bTitle);
				ps.setString(3, bContent);
				ps.setInt(4, Integer.parseInt(bGroup));
				ps.setInt(5, Integer.parseInt(bStep) + 1);
				ps.setInt(6, Integer.parseInt(bIndent) + 1);
			}
		});
	}
	
	private void replyShape( final String strGroup, final String strStep) {
		String query = "update mvc_board set bStep = bStep + 1 where bGroup = ? and bStep > ?";
		this.template.update(query, new PreparedStatementSetter() {
	
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, strGroup);
				ps.setString(2, strStep);
			}
		});
	}
	
	private void upHit(final String bId) {
		String query = "update mvc_board set bHit = bHit + 1 where bId = ?";
		this.template.update(query, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				// TODO Auto-generated method stub
				ps.setInt(1, Integer.parseInt(bId));
			}
		});
	}

참고자료