[iBatis / MyBatis] SimpleJdbcTemplate 사용 예

.... 생략 ....

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.

 
젠장 짤린다....