기존 JDBC는 여전히 개발자가 작성해야 하는 반복적인 코드가 많습니다.
따라서 복제된 코드는 “템플릿화”되어 개발자에게 제공됩니다.
- Spring Framework는 템플릿 클래스를 제공합니다.
(xxx.템플릿 클래스)
오늘은 JdbcTemplate 클래스에 대해 알아보겠습니다. - 패턴 유형
- MVC 패턴
- 공장 패턴
- 싱글톤 패턴
- 템플릿 메서드 패턴
=> 복잡하거나 반복적인 논리(알고리즘)를 캡슐화하여 쉽게 재사용 가능한 형태로 관리하는 스키마
- pom.xml에 구성 삽입
<!
-- JDBC : MySQL dependency 주입 -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.31</version>
</dependency>
<!
-- 템플릿 패턴 -->
<!
-- 필요한 것 1) DBCP객체 (DataBaseConnectionPool)
기존의 JDBCUtil에서의 역할을 담당하는 객체 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!
-- 필요한 것 2) Spring에서 제공하는 JDBC를 사용하겠다는 설정 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
- XML의 DI 주입
<!
-- Conn 객체를 확보하는 것이 어떤 JDBC에서든지 중요한 포인트!
!
스프링 JDBC에서는 Conn객체를 확보하는 역할을 담당하는 친구가 "DataSource" -->
<!
-- setter 주입밖에 사용하지 못함. -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/나의db" />
<property name="username" value="root" />
<property name="password" value="1234" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>- 구 다오
package com.spring.biz.board;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.spring.biz.common.JDBCUtil;
public class BoardDAO {
private Connection conn;
private PreparedStatement pstmt;
private final String INSERT = "INSERT INTO BOARD (TITLE,WRITER,CONTENT) VALUES(?,?,?)";
private final String UPDATE = "UPDATE BOARD SET TITLE=?, CONTENT=? WHERE BID=?";
private final String DELETE = "DELETE BOARD WHERE WHERE BID=?";
private final String SELECT_ALL = "SELECT * FROM BOARD";
private final String SELECT_ONE = "SELECT * FROM BOARD WHERE BID=?";
public boolean insertBoard(BoardVO bvo) {
conn = JDBCUtil.connect();
try {
pstmt = conn.prepareStatement(INSERT);
pstmt.setString(1, bvo.getTitle());
pstmt.setString(2, bvo.getWriter());
pstmt.setString(3, bvo.getContent());
pstmt.executeUpdate();
System.out.println("INSERT 수행 완료");
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
return true;
}
public boolean updateBoard(BoardVO bvo) {
conn = JDBCUtil.connect();
try {
pstmt = conn.prepareStatement(UPDATE);
pstmt.setString(1, bvo.getTitle());
pstmt.setString(2, bvo.getContent());
pstmt.setInt(3, bvo.getBid());
pstmt.executeUpdate();
System.out.println("UPDATE 수행 완료");
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
return true;
}
public boolean deleteBoard(BoardVO bvo) {
conn = JDBCUtil.connect();
try {
pstmt = conn.prepareStatement(DELETE);
pstmt.setInt(1, bvo.getBid());
pstmt.executeUpdate();
System.out.println("DELETE 수행 완료");
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
return true;
}
public List<BoardVO> selectAll(BoardVO bvo) {
ArrayList<BoardVO> datas = new ArrayList<BoardVO>();
conn = JDBCUtil.connect();
try {
pstmt = conn.prepareStatement(SELECT_ALL);
System.out.println("SELECT_ALL 수행 완료");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
BoardVO data = new BoardVO();
data.setBid(rs.getInt("BID"));
data.setContent(rs.getString("CONTENT"));
data.setTitle(rs.getString("TITLE"));
data.setWriter(rs.getString("WRITER"));
datas.add(data);
}
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
return datas;
}
public BoardVO selectOne(BoardVO bvo) {
conn = JDBCUtil.connect();
BoardVO data=null;
try {
pstmt = conn.prepareStatement(SELECT_ONE);
pstmt.setInt(1, bvo.getBid());
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
data=new BoardVO();
data.setBid(rs.getInt("BID"));
data.setTitle(rs.getString("TITLE"));
data.setContent(rs.getString("CONTENT"));
data.setWriter(rs.getString("WRITER"));
}
System.out.println("SELECT_ONE 수행 완료");
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
return data;
}
}
- DAO 변경
package com.spring.biz.board;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
//메모리 관리를 효율적으로 해줌.
@Repository("boardDAO") // Repository == DAO임을 알 수있게 해줌 컴포넌트랑 똑같은 역할, 컴포넌트를 상속받음
public class BoardDAO2 { // 스프링 JDBC를 활용하는 DAO
// 객체 의존주입
@Autowired
private JdbcTemplate jdbcTemplate;
private final String INSERT = "INSERT INTO BOARD (TITLE,WRITER,CONTENT) VALUES(?,?,?)";
private final String UPDATE = "UPDATE BOARD SET TITLE=?, CONTENT=? WHERE BID=?";
private final String DELETE = "DELETE BOARD WHERE WHERE BID=?";
private final String SELECT_ALL = "SELECT * FROM BOARD";
private final String SELECT_ONE = "SELECT * FROM BOARD WHERE BID=?";
private final String SELECT_W = "SELECT * FROM BOARD WHERE WRITER LIKE CONCAT('%',?,'%')";
public boolean insertBoard(BoardVO bvo) {
System.out.println("BoardDAO 2의 insert()");
jdbcTemplate.update(INSERT, bvo.getTitle(), bvo.getWriter(), bvo.getContent());
// 첫번째 인자는 SQL문 그 뒤로는 pstmt 물음표에 맞춰서 넣어주면됨,
return true;
}
public boolean updateBoard(BoardVO bvo) {
jdbcTemplate.update(UPDATE, bvo.getTitle(), bvo.getContent(), bvo.getBid());
return true;
}
public boolean deleteBoard(BoardVO bvo) {
jdbcTemplate.update(DELETE, bvo.getBid());
return true;
}
public List<BoardVO> selectAll(BoardVO bvo) {
System.out.println("BoardDAO 2의 selectAll()");
try {
Object() args = { bvo.getWriter() };
return jdbcTemplate.query(SELECT_W, args, new BoardRowMapper());
} catch(Exception e) {
return jdbcTemplate.query(SELECT_ALL, new BoardRowMapper());
}
// 아웃풋을 즉, return해줄 값을 대신 만들어줄 BoardRowMapper
}
public BoardVO selectOne(BoardVO bvo) {
Object() args = { bvo.getBid() };
// select류는 위에 처럼 ???를 여러개 넣을 수 없음 따라서 배열에 담아서 넣어주는데, 이때 어떤게 들어올지 모르니 object
return jdbcTemplate.queryForObject(SELECT_ONE, args, new BoardRowMapper());
}
}
class BoardRowMapper implements RowMapper<BoardVO> {
// RowMapper 스프링에서 제공하는 근본 인터페이스 ( 강제성을 띄고 있음 )
@Override
public BoardVO mapRow(ResultSet rs, int rowNum) throws SQLException {
BoardVO data = new BoardVO();
data.setBid(rs.getInt("BID"));
data.setTitle(rs.getString("TITLE"));
data.setContent(rs.getString("CONTENT"));
data.setWriter(rs.getString("WRITER"));
return data;
}
}
100줄이 넘는 원본 DAO를 50줄로 잘라서…
'추천 관련글,
