[Spring] DB 연동 및 쿼리 작성 (JdbcTemplate) (feat. Connection Pool, 커넥션 풀, DataSource, query())

이번 포스팅은 스프링이 제공하는 JdbcTemplate을 사용해 DB 연동하는 법을 다룹니다.

 

JDBC API를 사용하면 DB연동에 필요한 코드를 작성하게 되는데 이는 매우 반복적입니다.

반복을 줄이기 위해 스프링에서는 JdbcTemplate 클래스를 제공합니다.

이를 사용하면 트랜잭션의 관리도 쉬워집니다. 자세한 내용은 코드를 보면서 이야기하겠습니다.

 

그에 앞서 JdbcTemplate를 사용하기 위해서는 의존 모듈을 추가해야 합니다.

 

https://mvnrepository.com

 

위의 링크에서 검색해 pom.xml 혹은 build.gradle에 넣으시면 되겠습니다.

 

• spring-jdbc: JDBC 연동에 필요한 기능 제공
• tomcat-jdbc: DB 커넥션풀 기능 제공
• mysql-connector-java: MySQL연결에 필요한 JDBC 드라이버를 제공

 

그럼 먼저 DB 테이블을 생성해 보겠습니다.

 

ddl.sql

 

create user 'spring5'@'localhost' identified by 'spring5';

create database spring5fs character set=utf8;

grant all privileges on spring5fs.* to 'spring5'@'localhost';

create table spring5fs.MEMBER (
    ID int auto_increment primary key,
    EMAIL varchar(255),
    PASSWORD varchar(100),
    NAME varchar(100),
    REGDATE datetime,
    unique key (EMAIL) 
) engine=InnoDB character set = utf8;

 

MySQL에 root 사용자로 연결한 뒤 이 코드를 돌립니다. 

이 코드는 spring5라는 계정을 만들고 spring5fs라는 DB를 생성하고 권한 부여 후 MEMBER라는 테이블을 만든 코드입니다.

 

insert into MEMBER (EMAIL, PASSWORD, NAME, REGDATE)
values ('aa@aa.com', '1234', cbk, now())

 

이렇게 데이터도 하나 넣어주겠습니다.

DB를 만들었으니 이제 연동을 해보겠습니다.

 

 

 

반응형

 

 

 

 

AppCtx.java

@Bean(destroyMethod = "close")
	public DataSource dataSource() {
		DataSource ds = new DataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost/spring5fs?characterEncoding=utf8");
		ds.setUsername("spring5");
		ds.setPassword("spring5");
		ds.setInitialSize(2);
		ds.setMaxActive(10);
		ds.setTestWhileIdle(true);
		ds.setMinEvictableIdleTimeMillis(60000 * 3);
		ds.setTimeBetweenEvictionRunsMillis(10 * 1000);
		return ds;
	}

 

위의 예시코드와 같이 DataSource 객체를 생성하고 MySQL과 연동을 해줍니다.

destroyMethod를 close로 설정해 커넥션 풀에 보관된 Connection을 닫습니다.

 

Connection conn = null;
try {
	conn = dataSource.getConnection();
        ...

 

그럼 위와 같이 DataSource를 사용해 DB Connection을 구할 수 있습니다.

DB 연동에 사용할 DataSource를 스프링 빈으로 등록하고, DB 연동 기능을 구현한 빈 객체는 DataSource를 주입받아 사용합니다.

 

커넥션 풀은 커넥션을 생성하고 유지합니다. 이런 커넥션 풀에 커넥션을 요청하면 해당 커넥션은 활성(active) 상태가 되고, 커넥션을 다시 커넥션 풀에 반환하면 유휴(idle) 상태가 됩니다.

 

 

 

Connection conn = null;
try {
	conn = dataSource.getConnection(); //풀에서 구함
        ...
finally {
	...
    try {
    	conn.close(); // 풀에 반환
        ...

 

 

 

예시 코드처럼 dataSource.getConnection()을 하게 되면 active, conn.close()를 하게 되면 idle 상태가 됩니다.

이때 close를 하면 커넥션을 끊는 것 같지만 사실은 반환하고 유휴상태를 유지합니다.

 

 

 

 

반응형

 

 

 

 

Tomcat JDBC 모듈의 DataSource 클래스는 커넥션을 몇 개 만들지 지정할 수 있는 메소드를 제공합니다.

 

예를 들어 위의 AppCtx.java 코드처럼 setMaxActive()는 활성 상태가 가능한 최대 커넥션 개수를 지정합니다.

만약 setMaxActive(40)이라고 한다면, 동시에 커넥션 풀에서 가져올 수 있는 커넥션 개수가 40개라는 뜻입니다.

활성상태 커넥션이 40개인데 커넥션 풀에 다시 커넥션을 요청하면 다른 커넥션이 반환될 때까지 대기합니다.

 

이 대기시간 역시 setMaxWait()을 통해 지정할 수 있습니다.

대기 시간 내에 풀에 반환된 커넥션이 있으면 해당 커넥션을 구하고, 없으면 익셉션이 발생합니다.

 

그럼 커넥션 풀을 사용하는 이유는 무엇일까요? 바로 성능 때문입니다.

매번 새로운 컨넥션을 생성하면 그때마다 연결 시간이 소모됩니다.

커넥션 풀을 사용하면 미리 커넥션을 생성했다가 필요할 때 꺼내 쓰기 때문에 커넥션을 구하는 시간을 절약할 수 있습니다.

그래서 커넥션 풀을 초기화할 때 최소 수준의 커넥션을 미리 생성하는 것이 좋습니다. 이는 setInitialSIze()를 통해 지정할 수 있습니다.

 

커넥션 풀에 생성된 커넥션은 지속적으로 재사용됩니다. 하지만 한 커넥션이 영원히 유지되는 것은 아닙니다.

DBMS 설정에 따라 일정 시간 내에 쿼리를 실행하지 않으면 연결을 끊기도 합니다.

그렇다면 DBMS와 커넥션의 연결은 끊겼는데 커넥션은 여전히 풀 안에 남아있는 상태가 되고 이때 이 커넥션을 불러오면 익셉션이 발생하게 됩니다.

이를 방지하기 위해서는 커넥션 풀의 커넥션이 유효한지 주기적으로 검사해야 합니다. 

 

 

ds.setTestWhileIdle(true); //유휴 커넥션 검사
ds.setMinEvictableIdleTimeMillis(60000 * 3); //최소 유휴 시간 3분
ds.setTimeBetweenEvictionRunsMillis(10 * 1000); //10초 주기

 

 

위의 AppCtx.java의 코드입니다.

setTestWhileIdle()을 사용해 유휴 커넥션을 검사할 수 있고,

setMinEvictableIdleTimeMillis()을 최소 유휴시간을 지정하고,

setTimeBetweenEvictionRunsMillis()를 사용해 검사주기를 설정할 수 있습니다.

 

 

 

반응형

 

 

 

 

그럼 JdbcTemplate을 이용해 쿼리를 실행해 보겠습니다.

 

MemberDao.java

 

public class MemberDao {

	private JdbcTemplate jdbcTemplate;

	public MemberDao(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
}

 

JdbcTemplate을 사용하기 위해 객체를 생성해 줬습니다. 그리고 생성자 방식으로 의존성을 주입해 줬습니다.

 

 

AppCtx.java

@Bean
public MemberDao memberDao() {
	return new MemberDao(dataSource());
}

 

그리고 설정파일에서 빈 설정을 추가합니다. 

 

이제 쿼리를 작성해 보도록 하겠습니다.

 

MemberDao.java 

 

public Member selectByEmail(String email) {
	List<Member> results = jdbcTemplate.query(
		"select * from MEMBER where EMAIL = ?",
		new RowMapper<Member>() {
		@Override
		public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
			Member member = new Member(
				rs.getString("EMAIL"),
				rs.getString("PASSWORD"),
				rs.getString("NAME"),
				rs.getTimestamp("REGDATE").toLocalDateTime());
				member.setId(rs.getLong("ID"));
				return member;
			}
		}, email);

	return results.isEmpty() ? null : results.get(0);
}

 

 

JdbTemplate 클래스는 query() 메서드를 제공합니다.

이를 통해 sql 파라미터로 전달받은 쿼리를 실행하고, RowMapper를 이용해 ResultSet의 결과를 자바 객체로 변환합니다.

RowMapper의 MapRow() 메소드는 SQL 실행 결과로 구한 ResultSet의 한 행의 데이터를 읽어와 자바 객체로 변환하는 매퍼 기능을 구현합니다.

 

이 코드는 주어진 MEMBER 테이블에서 주어진 이메일을 가진 사람의 정보를 조회하는 테이블입니다.

위의 코드에서는 query() 메소드로 쿼리를 실행한 후, RowMapper객체에 ResultSet을 통해 읽어온 데이터를 Member 객체로 변환해 전달하고 있습니다.

만약 동일한 RowMapper가 여러 번 사용된다면 이를 따로 클래스로 빼서 구현해 코드 중복을 줄일 수 있습니다.

 

 

MemberRowMapper.java

public class MemberRowMapper implements RowMapper<Member> {

	@Override
	public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
		Member member = new Member(
				rs.getString("EMAIL"),
				rs.getString("PASSWORD"),
				rs.getString("NAME"),
				rs.getTimestamp("REGDATE").toLocalDateTime());
		member.setId(rs.getLong("ID"));
		return member;
	}

}

 

Return 값이 동일하다면 여러 클래스에 여러 쿼리를 실행해도 new MemberRowMapper()라고만 작성해 주면 코드 중복 없이 코드를 작성할 수 있습니다.

 

 

 

 

반응형

 

 

 

 

쿼리의 결과가 1행인 경우에는 queryForObject() 메소드를 사용할 수 있습니다. 예시를 들어 설명해 보겠습니다.

 

 

MemberDao.java

public int count() {
	Integer count = jdbcTemplate.queryForObject(
			"select count(*) from MEMBER", Integer.class);
	return count;
}

 

 

이 코드는 MEMBER 테이블의 컬럼 개수를 count 하는 쿼리입니다. 결과는 1행이 나오겠죠?

이때는 List 타입으로 받는 것보다 Integer 타입으로 받으면 더 변수를 편리하게 이용할 수 있습니다.

이를 처리하기 위한 메소드가  queryForObject() 입니다.

꼭 Integer에 국한되는 것은 아니며 기본자료형의 래퍼클래스이면 다 사용할 수 있습니다.

 

그럼 JdbcTemplate를 이용한 변경 쿼리를 실행해 보겠습니다.

Insert, Update, Delete 쿼리는 전부 update() 메소드를 사용합니다. 아래의 코드는 예시코드입니다.

 

 

MemberDao.java

public void update(Member member) {
	jdbcTemplate.update(
			"update MEMBER set NAME = ?, PASSWORD = ? where EMAIL = ?",
			member.getName(), member.getPassword(), member.getEmail());
}

 

 

지금까지 작성한 코드는 쿼리에서 사용할 값을 인자로 사용했습니다. ex) member.getName()

PreparedStatement의 set 메소드를 사용해 직접 인덱스 파라미터의 값을 설정해야 하는 경우도 있습니다.

이때, PreparedStatementCreator를 인자로 받는 메소드를 이용해서 직접 PreparedStatement를 생성하고 설정해야 합니다.

 

MemberDao.java

 

public void insert(Member member) {
	KeyHolder keyHolder = new GeneratedKeyHolder();
	jdbcTemplate.update(new PreparedStatementCreator() {
		@Override
		public PreparedStatement createPreparedStatement(Connection con)
				throws SQLException {
			// 파라미터로 전달받은 Connection을 이용해서 PreparedStatement 생성
			PreparedStatement pstmt = con.prepareStatement(
					"insert into MEMBER (EMAIL, PASSWORD, NAME, REGDATE) " +
					"values (?, ?, ?, ?)",
					new String[] { "ID" });
			// 인덱스 파라미터 값 설정
			pstmt.setString(1, member.getEmail());
			pstmt.setString(2, member.getPassword());
			pstmt.setString(3, member.getName());
			pstmt.setTimestamp(4,
					Timestamp.valueOf(member.getRegisterDateTime()));
			// 생성한 PreparedStatement 객체 리턴
			return pstmt;
		}
	}, keyHolder);
	Number keyValue = keyHolder.getKey();
	member.setId(keyValue.longValue());
}

 

이미 있는 값을 가져와서 사용하는 것이 아니라 동적으로 전달받은 값을 쿼리에 사용하기 위해 PreparedStatement를 사용해 구현했습니다. 이때 PreparedStatementCreator 인터페이스를 구현해 Connection을 이용해 PreparedStatement객체를 생성하고 값을 설정해 객체를 리턴합니다.

 

위의 코드처럼 insert를 실행하는 경우 보통 MySQL에서 ID 같은 값을 AUTO_INCREMENT를 통해 행이 추가되면 자동으로 값을 늘려줍니다. 이런 경우는 java에서 쿼리를 작성할 때 그 컬럼 값은 지정하지 않아도 됩니다.

그런데 쿼리 실행 후 그 값을 알고 싶다면 어떻게 해야 할까요?

JdbcTemplate의 KeyHolder를 사용하면 자동으로 생성된 키값을 구할 수 있습니다.

 

위의 코드에서 GeneratedKeyHolder 객체를 생성해 줍니다.  

update() 메소드는 PreparedStatement를 실행한 후 자동 생성된 키값을 keyHolder에 보관합니다.

이때 보관한 값을 getKey()를 통해 구할 수 있습니다.

 

 

 

 

반응형

 

 

 

 

+)

 

 

DB를 연동하는 과정에서 자주 발생하는 익셉션과 그 원인을 소개합니다.

 

access denied for user 'root'@'localhost' (using password yes)

원인: MySQL 서버에 연결할 권한이 없는 경우.

해결법: 계정의 이름과 패스워드를 잘 입력했는지 확인해 보시길 바랍니다.

 

CannotGetJdbcConnectionException: Failed to obtain JDBC Connection

원인: DB를 실행하지 않았거나 방화벽에 막혀있어 DB에 연결할 수 없다는 메시지입니다.

해결법: 로컬에 설치된 DBMS를 실행했는지 확인해 보세요.

 

BadSqlGrammarException

원인: 잘못된 쿼리를 사용했기 때문에 발생한 익셉션입니다.

해결법: 쿼리 문법을 고치면 해결됩니다. 이때 주로 유의해야 할 것은 쿼리에서 줄 바꿈을 할 때 공백문자를 넣는 것입니다. (그게 문법임)

 

BadSqlGrammarException이 발생하는 이유는 MySQL에서 SyntaxErrorException(문법 에러)가 발생했기 때문입니다.

JDBC API를 사용하면 SyntaxErrorException -> SQLException -> DataAccessException으로 변환해서 발생합니다.

JdbcTemplate는 DataAccessException를 상속받은 BadSqlGrammarException로 변환합니다.

 

그렇다면 스프링은 왜 SQLException -> DataAccessException 이런 변환작업을 거치는 것일까요?

이유는 익셉션을 편하게 처리하게 하기 위해서입니다.

DB연동을 하는 방법은 JDBC 이외에도 하이버네이트, Mybatis, JPA 등 너무 다양합니다. 

이에 따라 각각 다른 익셉션을 스프링이 제공하는 익셉션으로 변환처리 함으로써 동일한 코드로 익셉션을 처리할 수 있게 하기 위한 것입니다.

 

JDBC를 직접 사용하는 경우는 무조건 try-catch문을 통해 SQLException을 처리해줘야 했지만 스프링에서는 DataAccessException은 RuntimeException이라 필요한 경우에만 에러처리를 하면 된다는 장점이 있습니다.

 

 

 

 

 

출처: 초보 웹 개발자를 위한 스프링 5 프로그래밍 입문