프로그래밍/iBatis, MyBatis

[iBatis / MyBatis] SimpleJdbcTemplate 사용 예

꼬렙 2011. 8. 26. 16:24
728x90
.... 생략 ....

private SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);

ㅇ 데이터 갯수 확인 - int queryForInt(String) 
    int count = template.queryForInt(
        "SELECT count(*) FROM article");


ㅇ 데이터를 입력해서 문자열 반환
    String password = template.queryForObject(
        "SELECT password FROM article WHERE article_number=?",
        String.class,
        articleNumber);


ㅇ 리스트 형태 반환
    List<BoardBean> list = null;
    list = template.query(
        "SELECT * FROM article WHERE article_number BETWEEN ? AND ?",
        new BoardBeanRowMapper();
        new Object[] { startRow, endRow }); 

[BoardBeanRowMapper.java]

import java.sql.*;
import org.springframework.jdbc.core.RowMapper;

public class BoardBeanRowMapper implements RowMapper<BoardBean> {

@Override
public BoardBean mapRow(ResultSet rs, int rowNum) throws SQLException {
BoardBean boardBean = new BoardBean();

boardBean.setArticleNumber(rs.getInt("article_number"));
boardBean.setId(rs.getString("id"));
boardBean.setTitle(rs.getString("title"));
boardBean.setContent(rs.getString("content"));
boardBean.setPassword(rs.getString("password"));
boardBean.setHitNumber(rs.getInt("hit_number"));
boardBean.setDepth(rs.getInt("depth"));
String writeDateTemp = String.valueOf(rs.getTimestamp("write_date"));
String date = writeDateTemp.substring(0, 4) + "년 " + writeDateTemp.substring(5, 7) + "월" +
 writeDateTemp.substring(8, 10) + "일 " + writeDateTemp.substring(11, 19);
boardBean.setWriteDate(date);
boardBean.setFileName(rs.getString("file_name"));
return boardBean;
}

}


[BoardBean.java] 
public class BoardBean {
int articleNumber;
String id;
String title;
String content;
String password;
int hitNumber;
int depth;
String writeDate;
String fileName;

        .... Getter / Setter 생략 ....
 
}


ㅇ INSERT
template.update(

    "INSERT INTO article_model2 VALUES (article_model2_sequence.nextval, ?, ?, ?, ?, ?, ?, sysdate, ?)", 
    upload.getId(),
    upload.getTitle(),
    upload.getContent(),
    upload.getPassword(),
    upload.getHitNumber(),
    upload.getDepth(),
    upload.getFile().getOriginalFilename()
);

또는..
 
BeanPropertySqlParameterSource bps = new BeanPropertySqlParameterSource(boardBean);
template.update(
    "INSERT INTO
         article
     VALUES
         (article_seq.NEXTVAL, :id, :title, :content, :password, :hit_number, :depth, SYSDATE, :file_name)",
     bps);


ㅇ UPDATE

template.update(
    "UPDATE article_model2 SET hit_number=hit_number+1 WHERE article_number=?", 
    articleNumber
);


ㅇ DELETE
template.update(
    "DELETE FROM article_model2 WHERE article_number=?", 
    articleNumber
);


이외에도 충분히 여러가지 형태로 오버로딩이 되어 있으므로 상황에 맞게 골라 쓰면 된다


아래는 SimpleJdbcTemplate 클래스의 API이다

Method Summary
 int[] batchUpdate(String sql, List<Object[]> batchArgs) 
          Execute a batch using the supplied SQL statement with the batch of supplied arguments.
 int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) 
          Execute a batch using the supplied SQL statement with the batch of supplied arguments.
 int[] batchUpdate(String sql, Map<String,?>[] batchValues) 
          Executes a batch using the supplied SQL statement with the batch of supplied arguments.
 int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) 
          Execute a batch using the supplied SQL statement with the batch of supplied arguments.
 JdbcOperations getJdbcOperations() 
          Expose the classic Spring JdbcTemplate to allow invocation of less commonly used methods.
 NamedParameterJdbcOperations getNamedParameterJdbcOperations() 
          Expose the Spring NamedParameterJdbcTemplate to allow invocation of less commonly used methods.
<T> List<T>
query(String sql, ParameterizedRowMapper<T> rm, Map<String,?> args) 
          Deprecated. 
<T> List<T>
query(String sql, ParameterizedRowMapper<T> rm, Object... args) 
          Deprecated. 
<T> List<T>
query(String sql, ParameterizedRowMapper<T> rm, SqlParameterSource args) 
          Deprecated. 
<T> List<T>
query(String sql, RowMapper<T> rm, Map<String,?> args) 
          Query for a List of Objects of type T using the supplied RowMapper to the query results to the object.
<T> List<T>
query(String sql, RowMapper<T> rm, Object... args) 
          Query for a List of Objects of type T using the supplied RowMapper to the query results to the object.
<T> List<T>
query(String sql, RowMapper<T> rm, SqlParameterSource args) 
          Query for a List of Objects of type T using the supplied RowMapper to the query results to the object.
 int queryForInt(String sql, Map<String,?> args) 
          Query for an int passing in a SQL query using the named parameter support provided by the NamedParameterJdbcTemplateand a map containing the arguments.
 int queryForInt(String sql, Object... args) 
          Query for an int passing in a SQL query using the standard '?' placeholders for parameters and a variable number of arguments.
 int queryForInt(String sql, SqlParameterSource args) 
          Query for an int passing in a SQL query using the named parameter support provided by the NamedParameterJdbcTemplateand a SqlParameterSource containing the arguments.
 List<Map<String,Object>> queryForList(String sql, Map<String,?> args) 
          Execute the supplied query with the supplied arguments.
 List<Map<String,Object>> queryForList(String sql, Object... args) 
          Execute the supplied query with the (optional) supplied arguments.
 List<Map<String,Object>> queryForList(String sql, SqlParameterSource args) 
          Execute the supplied query with the supplied arguments.
 long queryForLong(String sql, Map<String,?> args) 
          Query for an long passing in a SQL query using the named parameter support provided by theNamedParameterJdbcTemplate and a map containing the arguments.
 long queryForLong(String sql, Object... args) 
          Query for an long passing in a SQL query using the standard '?' placeholders for parameters and a variable number of arguments.
 long queryForLong(String sql, SqlParameterSource args) 
          Query for an long passing in a SQL query using the named parameter support provided by theNamedParameterJdbcTemplate and a SqlParameterSource containing the arguments.
 Map<String,Object> queryForMap(String sql, Map<String,?> args) 
          Execute the supplied query with the supplied arguments.
 Map<String,Object> queryForMap(String sql, Object... args) 
          Execute the supplied query with the (optional) supplied arguments.
 Map<String,Object> queryForMap(String sql, SqlParameterSource args) 
          Execute the supplied query with the supplied arguments.
<T> T
queryForObject(String sql, Class<T> requiredType, Map<String,?> args) 
          Query for an object of type T identified by the supplied @Class.
<T> T
queryForObject(String sql, Class<T> requiredType, Object... args) 
          Query for an object of type T identified by the supplied @Class.
<T> T
queryForObject(String sql, Class<T> requiredType, SqlParameterSource args) 
          Query for an object of type T identified by the supplied @Class.
<T> T
queryForObject(String sql, ParameterizedRowMapper<T> rm, Map<String,?> args) 
          Deprecated. 
<T> T
queryForObject(String sql, ParameterizedRowMapper<T> rm, Object... args) 
          Deprecated. 
<T> T
queryForObject(String sql, ParameterizedRowMapper<T> rm, SqlParameterSource args) 
          Deprecated. 
<T> T
queryForObject(String sql, RowMapper<T> rm, Map<String,?> args) 
          Query for an object of type T using the supplied RowMapper to the query results to the object.
<T> T
queryForObject(String sql, RowMapper<T> rm, Object... args) 
          Query for an object of type T using the supplied RowMapper to the query results to the object.
<T> T
queryForObject(String sql, RowMapper<T> rm, SqlParameterSource args) 
          Query for an object of type T using the supplied RowMapper to the query results to the object.
 int update(String sql, Map<String,?> args) 
          Execute the supplied SQL statement with (optional) supplied arguments.
 int update(String sql, Object... args) 
          Execute the supplied SQL statement with supplied arguments.
 int update(String sql, SqlParameterSource args) 
          Execute the supplied SQL statement with supplied arguments.
 
젠장 짤린다....