JDBC를 직접 이용한 쿼리 jdbcTemplate 이용한 처리의 비교

JDBC 직접 연결 처리

package app.messages;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Component;

@Component
public class MessageRepository {
	
	private final static Log logger = LogFactory.getLog(MessageRepository.class);
	private DataSource dataSource;
	
	public MessageRepository(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public Message saveMessage(Message message) {
		Connection c = DataSourceUtils.getConnection(dataSource);
		
		String insertSql = "INSERT INTO messages(id,text,created_date) values(null, ?, ? )";
		try {
			PreparedStatement ps = c.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
			ps.setNString(1, message.getText());
			ps.setTimestamp(2, new Timestamp(message.getCreatedDate().getTime()));
			int rowAffected = ps.executeUpdate();
			if( rowAffected > 0 ) {
				ResultSet result = ps.getGeneratedKeys();
				if(result.next()) {
					int id= result.getInt(1);
					return new Message(id, message.getText(), message.getCreatedDate());
				}else {
					logger.error("Failed to retrieve id, No row in result set");
					return null;
				}
			}else {
				return null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Failed to save message", e);
			try {
				c.close();
			}catch (SQLException ee) {
				logger.error("Failed to close connection", ee);
			}
		} finally {
			DataSourceUtils.releaseConnection(c, dataSource);
		}
		return null;
	}
	

}

Connection 리소스 관련 finally 처리 등 반복되는 부분이 많음

 

JDBC Template 이용

 

package app.messages;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Component;

@Component
public class MessageRepository {
	
	private final static Log logger = LogFactory.getLog(MessageRepository.class);
	private NamedParameterJdbcTemplate jdbcTemplate;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	}
	
	public Message saveMessage(Message message) {
		GeneratedKeyHolder holder = new GeneratedKeyHolder();
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("text", message.getText());
		params.addValue("createdDate", message.getCreatedDate());
		
		String insertSql = "INSERT INTO messages(id,text,created_date) values(null, :text, :createdDate )";
		try {
			this.jdbcTemplate.update(insertSql, params,holder);
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			logger.error("Failed to save message", e);
			return null;
		}
		
		return new Message(holder.getKey().intValue(), message.getText(), message.getCreatedDate());
	}
	

}

좀더 짧아지고 깔끔해짐