JDBC 템플릿 이란?
스프링에서 제공하는 SQL 연산들을 수행할 수 있도록 해주는 jdbc코드용 기본 템플릿이다
JdbcTemplate (Spring Framework 5.3.23 API)
Execute a query for a result object, given static SQL. Uses a JDBC Statement, not a PreparedStatement. If you want to execute a static query with a PreparedStatement, use the overloaded JdbcOperations.queryForObject(String, Class, Object...) method with nu
docs.spring.io
설정 방법:
pom.xml 에 종속성 추가
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.2.18.RELEASE</version>
</dependency>dispatcher-servlet에 의존성 주입
<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<beans:property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
<beans:property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
<beans:property name="username" value="scott"/>
<beans:property name="password" value="tiger"/>
</beans:bean>
사용 예시:
다음과 같이 접은 글을 훨씬 더 효율적으로 더 짧게 바꿔서 사용할 수 있다. (거의 100줄 이상 줄음)
더보기
package com.javalec.dao;
import com.javalec.dto.BoardBean;
import org.springframework.stereotype.Service;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Service
public class BoardDAO {
PreparedStatement pstmt;
ResultSet rs;
Connection conn;
public BoardDAO(){
try{
Context context = new InitialContext();
DataSource ds=(DataSource)context.lookup("java:comp/env/jdbc/Oracle11g");
conn = ds.getConnection();
}catch(Exception e){
e.printStackTrace();
}
}
//게시판 목록 개수 가져오기
public int getListCount(){
int x=0;
try{
pstmt = conn.prepareStatement("select count(*) from board");
rs=pstmt.executeQuery();
if(rs.next()){
x=rs.getInt(1);
}
} catch (Exception e) {
}finally {
if(rs!=null) try {rs.close();}catch(SQLException e) {}
if(pstmt!=null) try {pstmt.close();}catch(SQLException e) {}
}
return x;
}
public List getBoardList(int page,int limit){
String board_list_sql="select * from "+
"(select rownum rnum,BOARD_NUM,BOARD_NAME,BOARD_SUBJECT,"+
"BOARD_CONTENT,BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,"+
"BOARD_RE_SEQ,BOARD_READCOUNT,BOARD_DATE,BOARD_ID,BOARD_PASS from "+
"(select * from board order by BOARD_RE_REF desc,BOARD_RE_SEQ asc)) "+
"where rnum>=? and rnum<=?";
List list = new ArrayList();
int startrow=(page-1)*10+1; //�б� ������ row ��ȣ.
int endrow=startrow+limit-1; //���� ������ row ��ȣ.
try{
pstmt = conn.prepareStatement(board_list_sql);
pstmt.setInt(1, startrow);
pstmt.setInt(2, endrow);
rs = pstmt.executeQuery();
while(rs.next()){
BoardBean board = new BoardBean();
board.setBOARD_NUM(rs.getInt("BOARD_NUM"));
board.setBOARD_NAME(rs.getString("BOARD_NAME"));
board.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));
board.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));
board.setBOARD_FILE(rs.getString("BOARD_FILE"));
board.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));
board.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));
board.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));
board.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));
board.setBOARD_DATE(rs.getDate("BOARD_DATE"));
board.setBOARD_ID(rs.getString("BOARD_ID"));
board.setBOARD_PASS(rs.getString("BOARD_PASS"));
list.add(board);
}
return list;
}catch(Exception ex){
ex.printStackTrace();
}finally{
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
return null;
}
//게시판 등록
public boolean boardInsert(BoardBean board){
int num =0;
String sql="";
int result=0;
try{
pstmt=conn.prepareStatement("select max(board_num) from board");
rs = pstmt.executeQuery();
if(rs.next())
num =rs.getInt(1)+1;
else
num=1;
sql="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,";
sql+="BOARD_CONTENT, BOARD_FILE, BOARD_RE_REF,"+
"BOARD_RE_LEV,BOARD_RE_SEQ,BOARD_READCOUNT,"+
"BOARD_DATE,BOARD_ID) values(?,?,?,?,?,?,?,?,?,?,sysdate,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.setString(2, board.getBOARD_NAME());
pstmt.setString(3, board.getBOARD_PASS());
pstmt.setString(4, board.getBOARD_SUBJECT());
pstmt.setString(5, board.getBOARD_CONTENT());
pstmt.setString(6, board.getBOARD_FILE());
pstmt.setInt(7, num);
pstmt.setInt(8, 0);
pstmt.setInt(9, 0);
pstmt.setInt(10, 0);
pstmt.setString(11, board.getBOARD_ID());
result=pstmt.executeUpdate();
if(result==0)return false;
return true;
}catch(Exception ex){
System.out.println("boardInsert ���� : "+ex);
}finally{
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
return false;
}
//글 조회수 올리기
public void setReadCountUpdate(int num) throws Exception{
String sql="update board set BOARD_READCOUNT = "+
"BOARD_READCOUNT+1 where BOARD_NUM = "+num;
try{
pstmt=conn.prepareStatement(sql);
pstmt.executeUpdate();
}catch(SQLException ex){
System.out.println("setReadCountUpdate ���� : "+ex);
}finally {
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
}
//해당 게시글 내용 가져오기
public BoardBean getDetail(int num) throws Exception{
BoardBean board = null;
try{
pstmt = conn.prepareStatement(
"select * from board where BOARD_NUM = ?");
pstmt.setInt(1, num);
rs= pstmt.executeQuery();
if(rs.next()){
board = new BoardBean();
board.setBOARD_NUM(rs.getInt("BOARD_NUM"));
board.setBOARD_NAME(rs.getString("BOARD_NAME"));
board.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));
board.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));
board.setBOARD_FILE(rs.getString("BOARD_FILE"));
board.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));
board.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));
board.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));
board.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));
board.setBOARD_DATE(rs.getDate("BOARD_DATE"));
board.setBOARD_ID(rs.getString("BOARD_ID"));
board.setBOARD_PASS(rs.getString("BOARD_PASS"));
}
return board;
}catch(Exception ex){
System.out.println("getDetail ���� : " + ex);
}finally{
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
return null;
}
//해당 글번호와 입력한 비밀번호가 일치한지 확인
public boolean isBoardWriter(int num,String pass){
String board_sql="select * from board where BOARD_NUM=?";
try{
pstmt=conn.prepareStatement(board_sql);
pstmt.setInt(1, num);
rs=pstmt.executeQuery();
rs.next();
if(pass==""||pass==null||pass.equals(rs.getString("BOARD_PASS"))){
return true;
}
}catch(SQLException ex){
System.out.println("isBoardWriter ���� : "+ex);
}finally {
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
return false;
}
//글 내용 수정하기 UPDATE
public boolean boardModify(BoardBean modifyboard) throws Exception{
String sql="update board set BOARD_SUBJECT=?,BOARD_CONTENT=? where BOARD_NUM=?";
try{
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, modifyboard.getBOARD_SUBJECT());
pstmt.setString(2, modifyboard.getBOARD_CONTENT());
pstmt.setInt(3, modifyboard.getBOARD_NUM());
pstmt.executeUpdate();
return true;
}catch(Exception ex){
System.out.println("boardModify ���� : " + ex);
}finally{
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
return false;
}
//번호가져와서 게시글삭제
public boolean boardDelete(int num){
String board_delete_sql="delete from board where BOARD_num=?";
int result=0;
try{
pstmt=conn.prepareStatement(board_delete_sql);
pstmt.setInt(1, num);
result=pstmt.executeUpdate();
if(result==0)return false;
return true;
}catch(Exception ex){
System.out.println("boardDelete ���� : "+ex);
}finally{
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
return false;
}
public int boardReply(BoardBean board){
String board_max_sql="select max(board_num) from board";
String sql="";
int num=0;
int result=0;
int re_ref=board.getBOARD_RE_REF();
int re_lev=board.getBOARD_RE_LEV();
int re_seq=board.getBOARD_RE_SEQ();
try{
pstmt=conn.prepareStatement(board_max_sql);
rs = pstmt.executeQuery();
if(rs.next())num =rs.getInt(1)+1;
else num=1;
sql="update board set BOARD_RE_SEQ=BOARD_RE_SEQ+1 where BOARD_RE_REF=? ";
sql+="and BOARD_RE_SEQ>?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,re_ref);
pstmt.setInt(2,re_seq);
result=pstmt.executeUpdate();
re_seq = re_seq + 1;
re_lev = re_lev+1;
sql="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,";
sql+="BOARD_CONTENT, BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,BOARD_RE_SEQ,";
sql+="BOARD_READCOUNT,BOARD_DATE,BOARD_ID) values(?,?,?,?,?,?,?,?,?,?,sysdate,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.setString(2, board.getBOARD_NAME());
pstmt.setString(3, board.getBOARD_PASS());
pstmt.setString(4, board.getBOARD_SUBJECT());
pstmt.setString(5, board.getBOARD_CONTENT());
pstmt.setString(6, ""); //���忡�� ������ ���ε����� ����.
pstmt.setInt(7, re_ref);
pstmt.setInt(8, re_lev);
pstmt.setInt(9, re_seq);
pstmt.setInt(10, 0);
pstmt.setString(11, board.getBOARD_ID());
pstmt.executeUpdate();
return num;
}catch(SQLException ex){
System.out.println("boardReply ���� : "+ex);
}finally{
if(rs!=null) try{rs.close();}catch(SQLException ex){}
if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
}
return 0;
}
}package com.javalec.dao;
import com.javalec.controller.Constant;
import com.javalec.dto.BoardBean;
import org.springframework.jdbc.core.*;
import org.springframework.stereotype.Service;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Service
public class BoardDAO {
JdbcTemplate template;
public BoardDAO(){
this.template= Constant.template;
}
//게시판 목록 개수 가져오기
public int getListCount(){
int x= this.template.queryForObject("select count(*) from board", Integer.class);
return x;
}
public List getBoardList(int page,int limit){
int startrow=(page-1)*10+1; ///�б� ������ row ��ȣ.
int endrow=startrow+limit-1; //���� ������ row ��ȣ.
String board_list_sql="select * from "+
"(select rownum rnum,BOARD_NUM,BOARD_NAME,BOARD_SUBJECT,"+
"BOARD_CONTENT,BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,"+
"BOARD_RE_SEQ,BOARD_READCOUNT,BOARD_DATE,BOARD_ID,BOARD_PASS from "+
"(select * from board order by BOARD_RE_REF desc,BOARD_RE_SEQ asc)) "+
"where rnum>=? and rnum<=?";
return (ArrayList<BoardBean>) template.query(board_list_sql,new Object[]{startrow,limit},new BeanPropertyRowMapper<BoardBean>(BoardBean.class));
}
//게시판 등록
public boolean boardInsert(BoardBean board){
int x=this.template.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
int num= template.queryForObject("select max(board_num) from board", Integer.class);
if(num==0){
num=1;
}else{
num=num+1;
}
String sql="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,";
sql+="BOARD_CONTENT, BOARD_FILE, BOARD_RE_REF,"+
"BOARD_RE_LEV,BOARD_RE_SEQ,BOARD_READCOUNT,"+
"BOARD_DATE,BOARD_ID) values(?,?,?,?,?,?,?,?,?,?,sysdate,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.setString(2, board.getBOARD_NAME());
pstmt.setString(3, board.getBOARD_PASS());
pstmt.setString(4, board.getBOARD_SUBJECT());
pstmt.setString(5, board.getBOARD_CONTENT());
pstmt.setString(6, board.getBOARD_FILE());
pstmt.setInt(7, num);
pstmt.setInt(8, 0);
pstmt.setInt(9, 0);
pstmt.setInt(10, 0);
pstmt.setString(11, board.getBOARD_ID());
return pstmt;
}
});
if(x!=0){
return true;
}else{
return false;
}
}
//글 조회수 올리기
public void setReadCountUpdate(int num) throws Exception{
String sql="update board set BOARD_READCOUNT = "+
"BOARD_READCOUNT+1 where BOARD_NUM = ?";
this.template.update(sql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setInt(1,num);
}
});
}
//해당 게시글 내용 가져오기
public BoardBean getDetail(int num) throws Exception{
String sql = "select * from board where BOARD_NUM = "+num;
return template.queryForObject(sql,new BeanPropertyRowMapper<BoardBean>(BoardBean.class));
}
//해당 글번호와 입력한 비밀번호가 일치한지 확인
public boolean isBoardWriter(int num,String pass){
return this.template.queryForObject("select * from board where BOARD_NUM=?", new Object[]{num}, new RowMapper<Boolean>() {
@Override
public Boolean mapRow(ResultSet resultSet, int i) throws SQLException {
if(pass==""||pass==null||pass.equals(resultSet.getString("BOARD_PASS"))){
return true;
}
return false;
}
});
}
//글 내용 수정하기 UPDATE
public boolean boardModify(BoardBean modifyboard) throws Exception{
String sql="update board set BOARD_SUBJECT=?,BOARD_CONTENT=? where BOARD_NUM=?";
int result=this.template.update(sql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setString(1, modifyboard.getBOARD_SUBJECT());
preparedStatement.setString(2, modifyboard.getBOARD_CONTENT());
preparedStatement.setInt(3, modifyboard.getBOARD_NUM());
}
});
if (result!=0){
return true;
}else{
return false;
}
}
//번호가져와서 게시글삭제
public boolean boardDelete(int num){
String board_delete_sql="delete from board where BOARD_num=?";
int result=this.template.update(board_delete_sql, num);
if (result!=0){
return true;
}else{
return false;
}
}
public int boardReply(BoardBean board){
return this.template.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
String board_max_sql="select max(board_num) from board";
String sql="";
int num=0;
int result=0;
int re_ref=board.getBOARD_RE_REF();
int re_lev=board.getBOARD_RE_LEV();
int re_seq=board.getBOARD_RE_SEQ();
num=template.queryForObject(board_max_sql, Integer.class);
if(num==0){
num=1;
}else{
num=num+1;
}
sql="update board set BOARD_RE_SEQ=BOARD_RE_SEQ+1 where BOARD_RE_REF=? ";
sql+="and BOARD_RE_SEQ>?";
PreparedStatement pstmt=connection.prepareStatement(sql);
pstmt.setInt(1,re_ref);
pstmt.setInt(2,re_seq);
re_seq = re_seq + 1;
re_lev = re_lev+1;
sql="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,";
sql+="BOARD_CONTENT, BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,BOARD_RE_SEQ,";
sql+="BOARD_READCOUNT,BOARD_DATE,BOARD_ID) values(?,?,?,?,?,?,?,?,?,?,sysdate,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.setString(2, board.getBOARD_NAME());
pstmt.setString(3, board.getBOARD_PASS());
pstmt.setString(4, board.getBOARD_SUBJECT());
pstmt.setString(5, board.getBOARD_CONTENT());
pstmt.setString(6, ""); //���忡�� ������ ���ε����� ����.
pstmt.setInt(7, re_ref);
pstmt.setInt(8, re_lev);
pstmt.setInt(9, re_seq);
pstmt.setInt(10, 0);
pstmt.setString(11, board.getBOARD_ID());
return pstmt;
}
});
}
}
느낀 점:
비록 예전에 하던 방법이 더 직관 적이어도 jdbc템플릿을 사용하면 훨씬 더 코드가 간결해지고 편안 해진다는 걸 느꼈다. 다음 프로젝트할 때는 jdbc템플릿을 사용해봐야겠다. 물론 mybatis도 연습해서 사용할 것이다.
'Spring' 카테고리의 다른 글
| [SpringSecurity] CSRF란?Restapi에서 CSRF 해제 가능? (0) | 2023.09.22 |
|---|---|
| Spring mvc2 패턴 (0) | 2022.11.01 |
| [Spring] 스프링 프레임워크 첫걸음~ (1) | 2022.10.26 |
댓글