Spring-6 Eclipse EE [JDBC Template]

by

JDBC Template

  1. JDBC의 반복적인 코드제거와 DB 연동 로직을 JDBC Template 클래스에서 제공하여주는 장점이 있다.
  2. 개발자는 SQL문에 집중
  3. root-context.xml에 저장된 것을 사용

DBCP : 자바 웹 애플리케이션 DB 접속과 관련된 라이브러리

JDBC : Java DataBase Connectivity DB연결 인터페이스, 각 DB Driver를 통해 접속

  1. DB 접속을 위해 JDBC Driver Load
  2. getConnection Method로 DB Connection 객체 가져옴
  3. PreparedStatement 객체 생성
  4. excuteQeury를 실행하여 결과를 가져옴
  5. close

DBCP : DataBase Connection Pool DB와 Connection하고 있는 객체를 관리

  1. WAS(Web Application Server) 실행 시 일정량 DB Connection 객체 생성 후 Pool에 저장
  2. DB Connection 요청 후 Pool에서 가져다 사용 후 반환
  3. DBCP 옵션
    • maxActive :동시 최대 Connection할 수 있는 수
    • maxIdle : Connection Pool에 반납할 때 최대 유지 Connection 수
    • minIdle : 최소 유지할 Conenction 수
    • initialSize : gertConnection() 메소드를 통해 Connection Pool에 채워 넣을 최소 Connection 수

라이브러리 추가

DBCP 라이브러리 추가

sonatype에 접속하여 commons-dbcp 검색

버전 1.4 선택 후 오른쪽 Apache Maven 복사

pom.xml 에 <dependencies> 아래 붙여넣기

<!-- DBCP -->
<dependency>
  <groupId>commons-dbcp</groupId>
  <artifactId>commons-dbcp</artifactId>
  <version>1.4</version>
</dependency>

pom.xml에 spring-orm, spring-jdbc 라이브러리 추가

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

DB Server와 관련된 정보를 따로 만들어 관리한다

/resource/config/database.properties 파일 생성 후 다음 코드 추가

jdbc.driver=org.sqlite.JDBC
jdbc.url=jdbc:sqlite:DB명
jdbc.username=
jdbc.password=

/webapp/WEB-INF/spring/root-context.xm,l에 다음 코드 추가

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="http://www.springframework.org/schema/beans
   https://www.springframework.org/schema/beans/spring-beans.xsd
   http://www.springframework.org/schema/context
   http://www.springframework.org/schema/context/spring-context.xsd">
   <!-- Root Context: defines shared resources visible to all other web components -->
   <!-- Sqlite -->
   <context:property-placeholder location="classpath:config/database.properties"/>
   <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
   <property name="driverClassName" value="${jdbc.driver}"/>
   <property name="url" value="${jdbc.url}"/>
   <property name="username" value="${jdbc.username}"/>
   <property name="password" value="${jdbc.password}"/>
   </bean>
</beans>

properties파일을 place holder를 통해 DataSource의 속성 설정을 BasicDataSource(DataSource Interface 중 하나)를 통해 bean으로 등록한다

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="http://www.springframework.org/schema/beans
   https://www.springframework.org/schema/beans/spring-beans.xsd
   http://www.springframework.org/schema/context
   http://www.springframework.org/schema/context/spring-context.xsd">
   <!-- Root Context: defines shared resources visible to all other web components -->
   <!-- Sqlite -->
   <context:property-placeholder location="classpath:config/database.properties"/>
   <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
   <property name="driverClassName" value="${jdbc.driver}"/>
   <property name="url" value="${jdbc.url}"/>
   <property name="username" value="${jdbc.username}"/>
   <property name="password" value="${jdbc.password}"/>
   </bean>
    <!-- Spring JDBC 설정 -->
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
      <property name="dataSource" ref="dataSource"/>
   </bean>
</beans>

JdbcTemplate Class 생성

@Repository
public class BoardDAOTemplate implements BoardDAO{
	@Autowired
	private JdbcTemplate jdbcTemplate;
	private final static String BOARD_INSERT = "INSERT INTO board(subject,content,writer) VALUES(?,?,?);";
	private final static String BOARD_DELETE = "DELETE FROM board WHERE _id = ?;";
	private final static String BOARD_UPDATE = "UPDATE board SET subject=?, content = ?,writer =? WHERE _id = ?;";
	private final static String BOARD_SELECTE_ONE = "SELECT * FROM board WHERE _id = ?;";
	private final static String BOARD_SELECTE_ALL = "SELECT * FROM board ORDER BY _id DESC;";
	@Override
	public int insert(Board vo) {
		System.out.println("===> Jdbc Template Insert ");
		return jdbcTemplate.update(BOARD_INSERT,vo.getSubject(),vo.getContent(),vo.getWriter());
	}
	@Override
	public int delete(Board vo) {
		System.out.println("===> Jdbc Template ");
		return jdbcTemplate.update(BOARD_DELETE,vo.get_id());
	}
	@Override
	public int update(Board vo) {
		System.out.println("===> Jdbc Template ");
		return jdbcTemplate.update(BOARD_UPDATE,vo.getSubject(),vo.getContent(),vo.getWriter(),vo.get_id());
	}
	@Override
	public Board getOne(Board vo) {
		System.out.println("===> Jdbc Template ");
		Object[]args = {vo.get_id()};
		return jdbcTemplate.queryForObject(BOARD_SELECTE_ONE, args,new BoardRowMapper());
	}
	@Override
	public List<Board> getAll() {
		System.out.println("===> Jdbc Template getAll");
		return jdbcTemplate.query(BOARD_SELECTE_ALL, new BoardRowMapper());
	}
	class BoardRowMapper implements RowMapper<Board>{
		public Board mapRow(ResultSet rs, int rowNum) throws SQLException {
			Board board = new Board();
			board.set_id(rs.getInt("_id"));
			board.setSubject(rs.getString("subject"));
			board.setContent(rs.getString("content"));
			board.setWriter(rs.getString("writer"));
			board.setWdate(rs.getString("wdate"));
			return board;
		}
	}
}

Leave a Comment