NamedParameter 를 사용해서 쿼리를 하려고 하였다.
하지만 쿼리 결과를 custom class의 리스트 형태로 받으려고 하니 제대로 되지 않았다.
그래서 NamedParameterJdbcTemplate 를 사용하지 않고
JdbcTemplate를 사용해서 쿼리를 날렸다.
그래서 sql문에 ?를 사용해서 퀴리를 만들고 사용했다.
(NamedParameter를 쓴다면 ? 대신 :이름 을 사용했을 것)
InitializingBean 을 사용해서 xml에 설정한 dataSource의 세팅을 확인했다.
RowMapper를 구현한 MyMapper 클래스를 작성해서 내가 원하는 형태의 출력을 만들었다.
------------------------- 자바 소스 -------------------------
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.BeanCreationException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.my.dao.MyDao;
public class SelectByDate implements InitializingBean{
private JdbcTemplate jdbcTemplate;
private String sql = "SELECT * from MY_CNT_TB WHERE TM >= ? AND TM <= ?";
@Autowired
private DataSource dataSource;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void afterPropertiesSet() throws Exception {
// TODO Auto-generated method stub
if(dataSource==null) {
throw new BeanCreationException("Must set dataSource!");
}
}
public List<MyDao> findFromByDate(int startTime, int endTime) {
Object[] args = new Object[] {startTime,endTime};
return jdbcTemplate.query(sql, args, new MyMapper());
}
private static final class MyMapper implements RowMapper<MyDao> {
@Override
public MyDao mapRow(ResultSet rs, int rowNum) throws SQLException {
// TODO Auto-generated method stub
MyDao tempMy = new MyDao();
tempMy.setNumber(rs.getInt("NM_ID"));
tempMy.setTime(rs.getInt("TM"));
tempMy.setVal1(rs.getInt("VAL1_CNT"));
tempMy.setVal2(rs.getInt("VAL2_CNT"));
tempMy.setVal3(rs.getInt("VAL3_CNT"));
return tempMy;
}
}
}
------------------------- xml 설정파일 -------------------------
<?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 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<context:property-placeholder location="./jdbc.properties" />
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value>${jdbc.driver}</value>
</property>
<property name="url">
<value>${jdbc.url}</value>
</property>
<property name="username">
<value>${jdbc.user}</value>
</property>
<property name="password">
<value>${jdbc.password}</value>
</property>
</bean>
</beans>
jdbc.properties 파일
# Placeholders jdbc*
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/user
# use this one for a separate server process so you can inspect the results
# (or add it to system properties with -D to override at run time).
schema=
schema.script=classpath:/org/springframework/batch/core/schema-mysql.sql
jdbc.user=user
jdbc.password=
------------------------- JUnit 테스트 파일 -------------------------
import java.util.List;
import javax.sql.DataSource;
import junit.framework.TestCase;
import org.apache.log4j.Logger;
import org.junit.Before;
import org.junit.Test;
import org.junit.Assert;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.support.GenericXmlApplicationContext;
import org.springframework.util.StopWatch;
import com.my.dao.MyDao;
import com.my.SelectByDate;
@Configuration(value = "./jdbc-config.xml")
public class testJdbc extends TestCase {
private final static Logger logger = Logger
.getLogger(testJdbc.class);
SelectByDate test1;
@Autowired
private DataSource dataSource;
@Before
public void setUp() {
GenericXmlApplicationContext ctx = new GenericXmlApplicationContext();
ctx.load("./jdbc-config.xml");
ctx.refresh();
dataSource = (DataSource) ctx.getBean("dataSource");
}
@Test
public void testJdbcTask() throws Exception {
StopWatch sw = new StopWatch();
test1 = new SelectByDate();
List<MyDao> result = null;
test1.setDataSource(dataSource);
sw.start();
result = test1.findFromByDate(20130728, 20130801);
assertEquals(108, result.size());
//108은 내가 직접 쿼리를 돌려 얻은 예상 값
logger.info(result.size());
sw.stop();
logger.info(">>> TIME ELAPSED:" + sw.prettyPrint());
}
}
참조 :
http://stackoverflow.com/questions/16359316/namedparameterjdbctemplate-vs-jdbctemplate
http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html#queryForObject(java.lang.String, org.springframework.jdbc.core.namedparam.SqlParameterSource, org.springframework.jdbc.core.RowMapper)
http://pirrip.tistory.com/entry/Spring-JDBC-Template-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0
http://julingks.tistory.com/entry/Spring-JDBC-support
http://loopypapa.tistory.com/entry/SQL-SELECT-%EC%A1%B0%EA%B1%B4
