Spring JDBC

Spring JDBC 설정

Spring에서 JDBC 설정을 하기 위해서는 spring-jdbc 모듈이 필요하다.

pom.xml

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

Oracle 의존성 추가

예제에서 사용할 오라클 데이터베이스를 사용하기 위해서는 ojdbc driver가 필요하다. 이 문서에서는 Oracle 11g Express Edition을 사용하기 위해 ojdbc6을 추가한다.

기본 저장소에서 다운받을 수 없기 때문에 추가 저장소를 설정한다.

<repositories>
	<!-- oracle driver repository -->
	<repository>
		<id>oracle</id>
		<name>Oracle JDBC Driver Repository</name>
		<url>http://maven.jahia.org/maven2</url>
	</repository>
</repositories>

추가 저장소 설정 후 의존성을 dependencies 영역에 추가한다.

<dependency>
	<groupId>com.oracle</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>12.1.0.2</version>
</dependency>

참고

ojdbc8부터 Maven 중앙 저장소에서 다운받을 수 있다.

JDBC Spring Bean 등록

maven 설정을 마쳤다면 Spring 설정파일에 필요한 bean들을 등록한다. DispatcherServlet보다 상위 레벨의 설정이므로 root-context.xml에 작성한다.

DataSource 등록

DataSource의 인스턴스는 데이터베이스의 연결을 담당한다. 연결을 위해 4개의 정보를 설정해야 하며, 설정하는 정보는 다음과 같다.

• driverClassName : 드라이버 클래스의 클래스명

• url : 접속을 시도할 경로. DB/드라이버종류@IP:Port:SID

• username : 접속할 계정 이름 (이 문서에서는 test)

• password : 접속할 계정 비밀번호(이 문서에서는 test)

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="oracle.jdbc.OracleDriver"></property>
	<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></property>
	<property name="username" value="test"></property>
	<property name="password" value="test"></property>
</bean>

JdbcTemplate 등록

spring-jdbc에서는 데이터베이스에 쉽게 명령을 전송할 수 있도록 JdbcTemplate 클래스를 제공한다. JdbcTemplate에는 DataSource 인스턴스를 참조 설정하도록 되어 있다.

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource"></property>
</bean>

Test Case 실행

등록이 정상적으로 이루어 졌는지 테스트케이스를 생성하여 확인한다. 테스트는 spring-bean을 불러오기 위하여 스프링과 연동할 수 있도록 구현한다.

테스트를 위해서는 spring-test와 junit을 추가해야 합니다.

pom.xml

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-test</artifactId>
	<version>${org.springframework-version}</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

Test class

package com.hakademy.spring12;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test01 {

	@Autowired
	private DataSource dataSource;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void dataSourceTest() {
		System.out.println("dataSource = "+dataSource);
	}
	
	@Test
	public void jdbcTemplateTest() {
		System.out.println("jdbcTemplate = "+jdbcTemplate);
	}
	
}

정상적으로 설정되었다면 다음과 같이 출력된다.

dataSource = org.springframework.jdbc.datasource.DriverManagerDataSource@6aa8e115
jdbcTemplate = org.springframework.jdbc.core.JdbcTemplate@609bcfb6

JdbcTemplate을 이용한 명령 수행

JdbcTemplate을 이용하여 데이터베이스에서 다음 작업을 수행할 수 있다.

• Create

• Read

• Update

• Delete

테이블 생성

테스트를 위한 테이블과 시퀀스를 생성한다.

create table student(
no number primary key,
name varchar2(15) not null,
score number
);
create sequence student_seq;

INSERT 코드

JdbcTemplate을 이용하여 데이터베이스 테이블에 데이터를 추가하는 코드를 구현한다.

src/test/java : com.hakademy.spring11.Test02

package com.hakademy.spring12;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test02 {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void insert() {
		String sql = "insert into student values(student_seq.nextval, ?, ?)";
		Object[] param = {"홍길동", 70};
		jdbcTemplate.update(sql, param);
	}
	
}

JdbcTemplate에 DataSource가 설정되어있기 때문에 JdbcTemplate만 사용하도록 설정한다.

@Autowired
private JdbcTemplate jdbcTemplate;

테스트 코드를 실행하기 위해서는 sql 명령과 데이터가 필요하다. sql은 String 형태로 생성하며, 채워야할 데이터는 ?로 처리한다. ? 자리에 들어갈 데이터는 Object[] 형태로 실행 시 배열의 인덱스 순서대로 ? 자리에 차례대로 설정되기 때문에 순서에 주의해야 한다.

String sql = "insert into student values(student_seq.nextval, ?, ?)";
Object[] param = {"홍길동", 70};

insert, update, delete 모두 JdbcTemplate에서는 .update()로 실행한다.

jdbcTemplate.update(sql, param);

Update

JdbcTemplate을 이용하여 데이터베이스 테이블에 데이터를 수정하는 코드를 구현한다. update의 경우 insert와 큰 차이가 없다.

src/test/java : com.hakademy.spring11.Test03

package com.hakademy.spring12;

import static org.junit.Assert.assertNotEquals;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test03 {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void update() {
		String sql = "update student set score = ? where no = ?";
		Object[] param = {100, 1};
		int result = jdbcTemplate.update(sql, param);
		assertNotEquals(0, result);
	}
}

성공 상황을 단정하기 위해 assertNotEquals()를 이용하여 0이 아닌지 확인한다. 실행된 row의 개수가 0이면 실패, 0보다 클 경우 성공이다.

Delete

JdbcTemplate을 이용하여 데이터베이스 테이블에 데이터를 삭제하는 코드를 구현한다. insert, update와 크게 차이가 없음을 확인할 수 있다.

src/test/java : com.hakademy.spring11.Test04

package com.hakademy.spring12;

import static org.junit.Assert.assertNotEquals;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test04 {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void delete() {
		String sql = "delete student where no = ?";
		Object[] param = {1};
		int result = jdbcTemplate.update(sql, param);
		assertNotEquals(0, result);
	}
	
}

성공 상황을 단정하기 위해 assertNotEquals()를 이용하여 0이 아닌지 확인한다. 실행된 row의 개수가 0이면 실패, 0보다 클 경우 성공이다.

Select

select 구문은 insert, update, delete와 다르게 데이터를 조회하는 것이 목적이기 때문에 다른 명령을 사용한다.

• .query(sql, param, mapper) : 목록 조회

• .query(sql, param, extractor) : 단일 항목 조회(사용자 정의)

• .queryForInt(sql) : int 항목 조회(개수, 합계, ..) - Spring 4.x에서 사용 불가

• .queryForObject(sql, class) : 단일 항목 조회(원시형, String)

DTO 생성

student 테이블을 매핑할 StudentDto 클래스를 생성한다.

src/main/java : com.hakademy.spring12.entity.StudentDto

package com.hakademy.spring12.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data @Builder @NoArgsConstructor @AllArgsConstructor
public class StudentDto {
	private int no;
	private String name;
	private int score;
}

목록 조회

목록을 조회하기 위해서는 RowMapper 형태의 인스턴스가 필요하다. RowMapper란, ResultSet의 row를 StudentDto에 매핑하기 위한 코드를 정의하는 인터페이스이다.

RowMapper는 다음과 같이 생성한다.

RowMapper<형태> mapper = new RowMapper<형태>(){
	public 형태 mapRow(ResultSet rs, int rowNum) throws SQLException {
		... (매핑코드) ...		
	}
};

import를 할 때 반드시 org.springframework.jdbc.core.RowMapper를 선택해야 한다.

생성 위치는 상관없으나, JdbcTemplate에서 사용해야하므로 JdbcTemplate이 선언된 위치에 생성하는 것이 이용하기 편하다.

RowMapper를 이용하여 목록을 조회하는 코드는 다음과 같다.

src/test/java : com.hakademy.spring12.Test05

package com.hakademy.spring12;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import com.hakademy.spring12.entity.StudentDto;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test05 {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void selectList() {
		String sql = "select * from student order by score desc";
		RowMapper<StudentDto> mapper = new RowMapper<StudentDto>() {
			@Override
			public StudentDto mapRow(ResultSet rs, int rowNum) throws SQLException {
				return StudentDto.builder()
								.no(rs.getInt("no"))
								.name(rs.getString("name"))
								.score(rs.getInt("score"))
							.build();
			}
		};		
		
		List<StudentDto> list = jdbcTemplate.query(sql, mapper);
		for(StudentDto student : list) {
			System.out.println(student);
		}
	}
}

데이터가 들어가 있다면 목록이 나오는 것을 확인할 수 있다. 데이터가 없을 경우 Test02 를 실행하여 데이터를 추가한 뒤 실행한다.

검색

검색의 경우는 목록 구문에 ?와 파라미터만 추가하면 된다.

src/test/java : com.hakademy.spring12.Test06

package com.hakademy.spring12;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import com.hakademy.spring12.entity.StudentDto;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test06 {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void searchList() {
		String sql = "select * from student where name like '%'||?||'%' order by score desc";
		Object[] param = {"홍"};
		RowMapper<StudentDto> mapper = new RowMapper<StudentDto>() {
			@Override
			public StudentDto mapRow(ResultSet rs, int rowNum) throws SQLException {
				return StudentDto.builder()
								.no(rs.getInt("no"))
								.name(rs.getString("name"))
								.score(rs.getInt("score"))
							.build();
			}
		};		
		
		List<StudentDto> list = jdbcTemplate.query(sql, param, mapper);
		for(StudentDto student : list) {
			System.out.println(student);
		}
	}
}

목록과의 차이점만 비교해보면 다음과 같다.

목록과 검색의 차이

SQL 구문 변경

like 검색을 이용할 경우 유사한 이름을 조회할 수 있으며, 아닐 경우 =로 비교한다.

String sql = "select * from student where name like '%'||?||'%' order by score desc";

홀더에 설정할 파라미터 추가

파라미터를 설정하는 방식은 insert, update, delete와 동일하게 Object[]로 설정한다.

Object[] param = {"홍"};

실행 구문 변경

실행 구문에 파라미터가 추가되도록 변경하였다.

List<StudentDto> list = jdbcTemplate.query(sql, param, mapper);

단일 항목 조회

단일 항목을 조회하기 위해서는 단일 항목을 매핑할 수 있도록 ResultSetExtractor 클래스의 객체가 필요하다. RowMapper와 달리 ResultSetExtractor는 항목이 존재하지 않을 경우에 대한 코드를 구현해야 한다.

ResultSetExtractor는 다음과 같이 생성한다.

ResultSetExtractor<형태> extractor = new ResultSetExtractor<형태>() {
	public 형태 extractData(ResultSet rs) throws SQLException, DataAccessException {
		...(매핑 코드)...
	}
};

extractData() 내부에는 다음과 같은 형태로 코드를 구현한다.

if(rs.next()){//데이터가 있으면
	...(매핑 코드)...
}
else{//데이터가 없으면
	...(매핑 코드 또는 null 반환)...
}

StudentDto에 대한 ResultSetExtractor는 다음과 같이 생성한다.

ResultSetExtractor<StudentDto> extractor = new ResultSetExtractor<StudentDto>() {
	public StudentDto extractData(ResultSet rs) throws SQLException, DataAccessException {
		if(rs.next()){
			return StudentDto.builder().
						.no(rs.getInt("no"))
						.name(rs.getString("name"))
						.score(rs.getInt("score"))
					.build();
		}
		else{
			return null;
		}
	}
};

전체적인 테스트 코드는 다음과 같다.

src/test/java : com.hakademy.spring12.Test07

package com.hakademy.spring12;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import com.hakademy.spring12.entity.StudentDto;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test07 {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void selectOne() {
		String sql = "select * from student where no = ?";
		Object[] param = {1};
		ResultSetExtractor<StudentDto> extractor = new ResultSetExtractor<StudentDto>() {
			public StudentDto extractData(ResultSet rs) throws SQLException, DataAccessException {
				if(rs.next())
					return StudentDto.builder()
						.no(rs.getInt("no"))
						.name(rs.getString("name"))
						.score(rs.getInt("score"))
					.build();
				else
					return null;
			}
		};
		
		StudentDto student = jdbcTemplate.query(sql, param, extractor);
		System.out.println(student);
	}
}

Count 조회

다음 구문을 수행하면 결과는 단 한개의 숫자로 나온다.

select count(*) from student

count 뿐 아니라 sum, avg, max, min 등 다양한 항목들이 존재하며, 이 때는 RowMapper나 ResultSetExtractor가 없어도 구현할 수 있다.

src/test/java : com.hakademy.spring12.Test08

package com.hakademy.spring12;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import com.hakademy.spring12.entity.StudentDto;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = {
	"file:src/main/webapp/WEB-INF/spring/root-context.xml",
	"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"
})
public class Test08 {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void selectCount() {
		String sql = "select count(*) from student";
		int count = jdbcTemplate.queryForObject(sql, Integer.class);
		System.out.println(count);
	}
}

원시형 또는 String 한 개만 조회할 경우 queryForObject() 명령을 이용하며 sql 명령과 결과물의 자료형을 전달하며 호출한다.

주의할 점은 count는 null이 나오지 않지만 max, min 등은 null이 나올 수 있기 때문에 다음과 같이 작성해야 오류를 방지할 수 있다.

Integer result = jdbcTemplate.queryForObject(sql, Integer.class);
int count = result == null? 0 : result;