Brain to Blog

Before JDBC API 3.0

JDBC API 3.0 이전에서는 INSERT 이후에 "select last_insert_id()" 쿼리를 날려서 추가된 AI 값을 가져와야 합니다.

@Override
public int create(User user) throws Exception {
	// query
	String sql = "insert into sample_user (name, age) values (:name, :age)";
	// parameter
	SqlParameterSource paramSource = new BeanPropertySqlParameterSource(user);
	// key holder
	KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
	// execute query
	getNamedParameterJdbcTemplate().update(sql, paramSource, generatedKeyHolder);
	// auto increment
	return generatedKeyHolder.getKey().intValue();
}

sample_mysql_ai.zip

package kr.nerv.dao.impl;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
 
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
 
import kr.nerv.dao.UserInfoDao;
import kr.nerv.vo.UserInfoVo;
 
public class UserInfoDaoJdbc extends NamedParameterJdbcDaoSupport implements UserInfoDao {
    // row mapper
    private UserInfoRowMapper rowMapper = new UserInfoRowMapper();
    // find by id query
    private String findById = "select * from sample_user_info where id = :id";
 
    @Override
    public UserInfoVo findById(Integer id) throws Exception {
        Map param = new HashMap();
        param.put("id", id);
 
        // select 데이터가 없으면 EmptyResultDataAccessException 예외 발생!!
        return (UserInfoVo) getNamedParameterJdbcTemplate().queryForObject(findById, param,
                rowMapper);
    }
 
    private class UserInfoRowMapper implements RowMapper {
        @Override
        public UserInfoVo mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserInfoVo userInfo = new UserInfoVo();
            userInfo.setId(rs.getInt("id"));
            userInfo.setName(rs.getString("name"));
            userInfo.setTel(rs.getString("tel"));
 
            return userInfo;
        }
    }
}

    @Override
    public UserInfoVo findById(Integer id) throws Exception {
        Map param = new HashMap();
        param.put("id", id);
 
        try {
            return (UserInfoVo) getNamedParameterJdbcTemplate().queryForObject(findById, param,
                    rowMapper);
        } catch (EmptyResultDataAccessException e) {
            // EmptyResultDataAccessException 예외 발생시 null 리턴
            return null;
        }
    }