사용 방법은 무척 간단하다.
pom.xml 등의 학습 지식이 없다면, 이전 글을 참고하면 좋겠다.
[작성 환경]
- IDE: Eclipse 2020-06
- Spring Framework 4.2.4 RELEASES
1. POM.xml 설정
https://mvnrepository.com/artifact/org.springframework/spring-jdbc
사이트에 접속해서 Spring-JDBC의 정보를 찾아서 pom.xml에 입력시켜 준다.
(중략)
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
(중략)
2. 구현 부분
핵심만 밑줄로 표기하였다.
package com.website.example.board;
import java.sql.Date;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import com.website.example.common.MyDataSourceFactory;
import com.website.example.model.FoodMenuVO;
import com.website.example.model.FoodMenuViewVO;
public class BoardDAOSpring {
// Spring Framework - JDBC
private JdbcTemplate jdbcTemplate = null;
private final String FOODMENU_INSERT = "insert into foodmenu_tbl(name, price, store_id, cnt, regidate) values(?, ?, ?, ?, ?)";
private final String BOARD_UPDATE = "update board set subject=?, memo=? where id=?";
private final String FOODMENU_DELETE = "delete foodmenu_tbl where id=?";
private final String BOARD_GET = "select * from board where id=?";
private final String BOARD_LIST = "SELECT * FROM ( " +
"SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM ( " +
"SELECT f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 " +
"where f1.store_id = f2.id order by f1.id desc " +
") Z WHERE ROWNUM <= ? " +
") WHERE RNUM >= ?";
private final String BOARD_FULL_COUNT = "select count(*) from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id";
// 변형을 할 필요가 있음. (태스트용)
public BoardDAOSpring() {
MyDataSourceFactory sourceFactory = new MyDataSourceFactory();
DataSource ds = sourceFactory.getOracleDataSource();
this.jdbcTemplate = new JdbcTemplate(ds);
}
public List<FoodMenuViewVO> getList(){
// 코드 간결하게 작성가능해짐.
System.out.println("Spring JDBC - GetBoardList()");
//return jdbcTemplate.query(BOARD_LIST, new BoardRowMapper());
Object args[] = {10, 1};
return jdbcTemplate.query(BOARD_LIST, args, new FoodMenuViewRowMapper());
// return null;
}
public int getCount() {
int result = 0;
FoodMenuVO vo = jdbcTemplate.queryForObject(BOARD_FULL_COUNT, new FoodMenuViewCntRowMapper());
result = vo.getId();
System.out.println("갯수:" + result);
return result;
}
public void insertTest() {
FoodMenuVO vo = new FoodMenuVO();
// 약 10만 개
// insert 후에 commit 할 것
/*
for(int j = 0; j < 2000 ; j++) {
for ( int i = 0; i < 50; i++) {
vo.setName("하하하하1234" + i);
vo.setPrice(1000);
vo.setStore_id(1);
vo.setCnt(0);
vo.setRegidate(Date.valueOf("2020-01-03"));
vo.setStore_id(1);
jdbcTemplate.update(FOODMENU_INSERT, vo.getName(),
vo.getPrice(), vo.getStore_id(),
vo.getCnt(), vo.getRegidate());
}
}// end of if
*/
vo.setName("야해해");
vo.setPrice(1000);
vo.setStore_id(1);
vo.setCnt(0);
vo.setRegidate(Date.valueOf("2020-01-05"));
vo.setStore_id(1);
Object[] args = {vo.getName(),
vo.getPrice(),
vo.getStore_id(),
vo.getCnt(),
vo.getRegidate()};
jdbcTemplate.update(FOODMENU_INSERT, args);
}
// 글 삭제
public void deleteFoodMenu(FoodMenuVO vo) {
System.out.println("===> Spring JDBC로 deleteBoard() 기능 처리");
jdbcTemplate.update( FOODMENU_DELETE, vo.getId() );
}
}
파일명: BoardDAOSpring.java
3. Mapper 구현
Spring JDBC의 특징은 Mapper 영역을 구현할 수 있다는 점이다.
package com.website.example.board;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.website.example.model.FoodMenuVO;
import com.website.example.model.FoodMenuViewVO;
public class FoodMenuRowMapper implements RowMapper<FoodMenuVO> {
@Override
public FoodMenuVO mapRow(ResultSet rs, int rowNum) throws SQLException {
FoodMenuVO vo = new FoodMenuVO();
vo.setId(rs.getInt(1));
vo.setName(rs.getString(2));
vo.setPrice(rs.getInt(3));
vo.setStore_id(rs.getInt(4));
vo.setCnt(rs.getInt(5));
vo.setRegidate(rs.getDate(6));
return vo;
}
}
파일명: FoodMenuRowMapper.java
package com.website.example.board;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.website.example.model.FoodMenuVO;
public class FoodMenuViewCntRowMapper implements RowMapper<FoodMenuVO> {
@Override
public FoodMenuVO mapRow(ResultSet rs, int rowNum) throws SQLException {
FoodMenuVO vo = new FoodMenuVO();
vo.setId(rs.getInt(1));
// System.out.println(rs.getInt(1));
return vo;
}
}
파일명: FoodMenuViewCntRowMapper.java
Mapper를 두 가지 형태로 두었는데, 상황에 따라서 변형이 가능하다.
* 맺음글(Conclusion)
Spring JDBC에 대해서 살펴보았다.
짧게 적은 이유는 사용 방법이 간단해서 그렇다. Spring JDBC의 트랜젝션을 언급하기 위해서 그렇다.
1. [Spring-Framework] 35. Spring-JDBCTemplate - 트랜젝션 (어노테이션 X), 2020-10-09