Spring에서 오라클 시퀀스를 위한 DataFieldMaxValueIncrementer(자바학원/스프링학원/JAVA학원/Spring학원)
http://ojc.asia/bbs/board.php?bo_table=LecSpring&wr_id=900
ojc.asia
https://www.youtube.com/watch?v=Pn4k4PSewxw&list=PLxU-iZCqT52B7oCYJltUT2-kBzckdr5vp&index=16
https://www.youtube.com/watch?v=2MlbXhykkwU&list=PLxU-iZCqT52B7oCYJltUT2-kBzckdr5vp&index=17
- DataFieldMaxValueIncrementer 인터페이스는 DB 테이블의 칼럼값을 MAX VALUE까지 자동증분 하기 위한 용도로 사용되는 스프링의 인터페이스 이다. 오라클 DB라면 시퀀스를 스프링에서 사용할 수 있게 해 준다.
- 인터페이스의 구조는 다음과 같다.
public interface DataFieldMaxValueIncrementer {
public int nextIntValue() throws DataAccessException;
public long nextLongValue() throws DataAccessException;
public String nextStringValue() throws DataAccessException;
}
- 스프링배치에서는 기본적으로 , Oracle, DB2, Derby, Hsql, Mysql, Postgres, MS-SQL Server, Sybase 등의 주요 DB에 대한 DataFieldMaxValueIncrementer의 구현 클래스가 제공되고 있다.
- 사용절차는 다음과 같다.
1. Spring 설정파일에서 사용할 incrementer에 대해 정의
DB2는 SequenceMaxValueIncrementer, MySQL은 MySQLMaxValueIncrementer, PostgreSQL이라면 PostgreSQLSequenceMaxValueIncrementer, 오라클인 경우 OracleSequenceMaxValueIncrementer를 사용한다.
<bean id="incrementer" class="org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
<property name="incrementerName">
<value>SEQ_SID</value> //오라클에 만들어 놓은 시퀀스 이름
</property>
</bean>
2. Insert SQL문을 실행하는 부분에서 정의된 incrementer 사용
private DataFieldMaxValueIncrementer incrementer;
public void setIncrementer(DataFieldMaxValueIncrementer incrementer) {
this.incrementer = incrementer;
}
….
String strQuery = "INSERT INTO BOARD VALUES ( ?, ?, ?, ?, ?, sysdate) ";
int currentSid =new Integer(incrementer.nextIntValue());
- 사용 예제
[CustomerDao.java]
package onj.edu.jdbc1;
import java.util.List;
import javax.sql.DataSource;
public interface CustomerDAO {
public void setDataSource(DataSource ds);
public void create(Integer id, String name, Integer age);
public Customer getCustomer(Integer id);
public List<Customer> listCustomer();
public void delete(Integer id);
public void update(Integer id, Integer age);
}
[CustomerJDBCTemplate.java]
package onj.edu.jdbc1;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer;
public class CustomerJDBCTemplate implements CustomerDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
private DataFieldMaxValueIncrementer incrementer;
public void setIncrementer(DataFieldMaxValueIncrementer incrementer) {
this.incrementer = incrementer;
}
public void setDataSource(DataSource ds) {
this.dataSource = ds;
this.jdbcTemplate = new JdbcTemplate(ds);
}
public void create(Integer id, String name, Integer age) {
String SQL = "insert into customer(id, name, age) values (?, ?, ?)";
jdbcTemplate.update(SQL, new Integer(incrementer.nextIntValue()), name, age);
System.out.println("<<<Customer insert ok ::: " + name + "," + age);
}
public Customer getCustomer(Integer id) {
String SQL = "select * from customer where id = ?";
Customer customer = jdbcTemplate.queryForObject(SQL, new Object[] { id }, new CustomerMapper());
return customer;
}
public List<Customer> listCustomer() {
String SQL = "select * from customer";
List<Customer> customer = jdbcTemplate.query(SQL, new CustomerMapper());
return customer;
}
public void delete(Integer id) {
String SQL = "delete from customer where id = ?";
jdbcTemplate.update(SQL, id);
System.out.println("<<< customer delete ok ::: id = " + id);
}
public void update(Integer id, Integer age) {
String SQL = "update customer set age = ? where id = ?";
jdbcTemplate.update(SQL, age, id);
System.out.println("<<< customer update ok ::: id = " + id);
}
}
[CustomerMapper.java]
package onj.edu.jdbc1;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class CustomerMapper implements RowMapper<Customer>{
@Override
public Customer mapRow(ResultSet rs, int arg1) throws SQLException {
Customer customer = new Customer();
customer.setId(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setAge(rs.getInt("age"));
return customer;
}
}
[jdbc3.xml]
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.2.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:thin:@192.168.0.27:1521:topcredu</value>
</property>
<property name="username">
<value>scott</value>
</property>
<property name="password">
<value>tiger</value>
</property>
</bean>
<bean id="customerJDBCTemplate" class="onj.edu.jdbc1.CustomerJDBCTemplate">
<property name="dataSource" ref='dataSource'></property>
<property name="incrementer" ref = "incrementer"></property>
</bean>
<bean id="incrementer" class="org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="incrementerName">
<value>SEQ_SID</value> <!-- 오라클에 만들어 놓은 시퀀스 이름 -->
</property>
</bean>
</beans>
[JdbcClient.java]
package onj.edu.jdbc1;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class JdbcClient {
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbc3.xml");
CustomerJDBCTemplate jdbcTemplate = (CustomerJDBCTemplate)ctx.getBean("customerJDBCTemplate");
jdbcTemplate.create(4, "4길동", 4); jdbcTemplate.create(5, "5길동", 5);
jdbcTemplate.create(6, "6길동", 6);
List <Customer> customers = jdbcTemplate.listCustomer();
System.out.println("------------------ all select ------------------");
for(Customer customer:customers){
System.out.println("id ----> "+customer.getId());
System.out.println("name ----> "+customer.getName());
System.out.println("age ----> "+customer.getAge());
}
jdbcTemplate.update(4, 99);
Customer customer = jdbcTemplate.getCustomer(4);
System.out.println("--------- 변경된 4번 데이터 -------------");
System.out.println("id ----> "+customer.getId());
System.out.println("name ----> "+customer.getName());
System.out.println("age ----> "+customer.getAge());
jdbcTemplate.delete(5);
customers = jdbcTemplate.listCustomer();
System.out.println("--------- 5번 삭제 후 ----------");
for(Customer customer1:customers){
System.out.println("id ----> "+customer1.getId());
System.out.println("name ----> "+customer1.getName());
System.out.println("age ----> "+customer1.getAge());
}
}
}
#DataFieldMaxValueIncrementer, #JPA, #JPA교육, #스프링부트, #스프링CRUD예제, #마리아DB, #스프링교육, #스프링동영상, #자바교육, #자바학원, #스프링학원, #스프링JDBC,
DataFieldMaxValueIncrementer, JPA, JPA교육, 스프링부트, 스프링CRUD예제, 마리아DB, 스프링교육, 스프링동영상, 자바교육, 자바학원, 스프링학원, 스프링JDBC
