[SPRING] jdbcTemplate, NamedParameterJdbcTemplate

jdbcTemplate

1. pom.xml 에다 dependency를 추가 해준다

<dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
    </dependencies>

2. jdbcTemplate을 사용해서 db에 값 넣기(update,insert,delete)

0123456789101112

3. RowMapper을 사용해서 db 값 가져오기(select)

public Member selectOne(String id){
        String sql = "select * from member where m_id=?";
        return jdbcTemplate.queryForObject(sql, rowMapper, id);
    }
    
    public List<Member> selectAll(){
        String sql = "select * from member";
        return jdbcTemplate.query(sql, rowMapper);
    }
    
    RowMapper<Member> rowMapper = new RowMapper<Member>() {
        
        @Override
        public Member mapRow(ResultSet rs, int index) throws SQLException {
            Member member = new Member();
            member.setId(rs.getString("m_id"));
            member.setPw(rs.getString("m_pw"));
            member.setName(rs.getString("m_name"));
            member.setEmail(rs.getString("m_email"));
            member.setRegData(rs.getDate("m_reg_date"));
            return member;
        }
    };

jdbcTemplate.query 와  jdbcTemplate.queryForObject 를 유심히 봐야 한다.

4. Dao를 만든 후 bean에 Dao를 추가 및 property를 추가해주면 된다.

    <bean class="MemberDao" id="memberdao">
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

5. 테스트 해보기

 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.GenericXmlApplicationContext;
 
public class MemberDaoTest {
    public static void main(String[] args) {
        ApplicationContext context = new GenericXmlApplicationContext("applicationContext.xml");
        MemberDao memberDao = context.getBean("memberdao",MemberDao.class);
        
        Member member = new Member();
        member.setId("test_id");
        member.setPw("test_pw");
        member.setName("test_name");
        member.setEmail("test_email");
        
        memberDao.insertMember(member);
    }
}
 

DB 화면

NamedParameterJdbcTemplate

1. pom.xml 에다 dependency를 추가 해준다

<dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
    </dependencies>

2. xml 먼저 작성

dataSource -> NamedParameterJdbcTemplate 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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
 
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost/hanbit"></property>
<property name="username" value="root"></property>
<property name="password" value="****"></property>
</bean>
 
<bean class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
</bean>
 
 
 
</beans>
 

3. Dao만들기

0123456

4. Dao를 만든 후 bean에 Dao를 추가 및 property를 추가해주면 된다.

<bean class="dao.deptDao" id="deptDao">
<property name="jdbcTemplate" ref="namedParameterJdbcTemplate"></property>
</bean>
 

5. 테스트 해보기

public class Main {
    public static void main(String[] args) {
        ApplicationContext context = new GenericXmlApplicationContext("applicationContext.xml");
        
        deptDao dao = context.getBean("deptDao",deptDao.class);
        
        Dept d = new Dept();
        d.setDeptno(50);
        d.setDname("TEST1");
        d.setDloc("TEST2");
        dao.insertDept(d);
    }
}
 

DB 화면