Spring JDBC 실습4

INSERT, UPDATE

• Directory 구조
  

1. RoleDaoSqls.java 수정 - UPDATE 쿼리 추가

package kr.or.connect.daoexam.dao;

public class RoleDaoSqls {
	public static final String SELECT_ALL = "SELECT role_id, description FROM role order by role_id";
	public static final String UPDATE = "UPDATE role set description = :description where role_id = :roleId";
}

2. RoleDao.java 수정 - 메서드 추가

package kr.or.connect.daoexam.dao;

import static kr.or.connect.daoexam.dao.RoleDaoSqls.*;

import java.util.Collections;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import kr.or.connect.daoexam.dto.Role;

@Repository
public class RoleDao {
	private NamedParameterJdbcTemplate jdbc;
	private SimpleJdbcInsert insertAction;
	
	private RowMapper<Role> rowMapper = BeanPropertyRowMapper.newInstance(Role.class);
	
	public RoleDao(DataSource dataSource) {
		this.jdbc = new NamedParameterJdbcTemplate(dataSource);
		this.insertAction = new SimpleJdbcInsert(dataSource).withTableName("role");
	}

	public List<Role> selectAll(){
		return jdbc.query(SELECT_ALL, Collections.emptyMap(), rowMapper);
	}
	
	/*
	 * BeanPropertySqlParameterSource 으로 role 객체의 값을 Map으로 바꿔준다.
	 * BeanPropertySqlParameterSource는 DBMS 표기법(role_id)와 JAVA 카멜표기법(roleId)이름을 자동으로 맞춰 준다.
	 * */
	public int insert(Role role) {
		SqlParameterSource params = new BeanPropertySqlParameterSource(role);
		return insertAction.execute(params);
	}
	
	public int update(Role role) {
		SqlParameterSource params = new BeanPropertySqlParameterSource(role);
		return jdbc.update(UPDATE,  params);
	}
}

3. JDBCTest.java 작성

package kr.or.connect.daoexam.main;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import kr.or.connect.daoexam.config.ApplicationConfig;
import kr.or.connect.daoexam.dao.RoleDao;
import kr.or.connect.daoexam.dto.Role;

public class JDBCTest {
    public static void main(String[] args) {
	ApplicationContext ac = new AnnotationConfigApplicationContext(ApplicationConfig.class);
		
	RoleDao roleDao = ac.getBean(RoleDao.class);
		
	Role role = new Role();
      
    	// 이번 실습에서는 PK값을 직접 넣어서 한다.
	role.setRoleId(500);
	role.setDescription("CEO");
		
	int count = roleDao.insert(role);
	System.out.println(count + "건 입력하였습니다.");
		
       	// 이번 실습에서는 PK값을 직접 넣어서 한다.
	role.setRoleId(301);
	role.setDescription("PROGRAMMER");
		
	int count2 = roleDao.update(role);
	System.out.println(count2 + "건 수정하였습니다.");	
    }
}

결과