2014년 7월 11일 금요일

[Spring JDBC예제]오라클 함수(Function)호출,SimpleJdbcCall로 REF CURSOR리턴하는함수호출,BatchPreparedStatementSetter이용 일괄Insert예제,스프링 JDBC

[Spring JDBC예제]오라클 함수(Function)호출,SimpleJdbcCall로 REF CURSOR리턴하는함수호출,BatchPreparedStatementSetter이용 일괄Insert예제,스프링 JDBC


아래 예제는 BatchPreparedStatementSetter이용 일괄Insert, Oracle Stored function의 ref cursor를 이용하여 EMP테이블의 10번 부서의 사원들의 리스트를 출력하는 예제이니 참조 바랍니다.


1. 오라클 서버측 함수코드

create or replace package types 
as 
type currtype is ref cursor; 
end; 
/ 

create or replace function getEmp(v_deptno in number) return types.currtype 
AS 
emp_cursor currtype; 
sql_string VARCHAR2(500); 
BEGIN 
  sql_string := 'SELECT empno, ename, sal FROM EMP WHERE DEPTNO = '|| v_deptno; 
  OPEN emp_cursor FOR sql_string ; 
  RETURN emp_cursor; 
  CLOSE emp_cursor; 
END; 
/



2. DTO 역할을 하는 Emp.java


package edu.onj.function;

public class Emp {
private String empno;
private String ename;
private String sal;
public Emp() {}
public Emp(String empno, String ename, String sal) {
this.empno = empno;
this.ename = ename;
this.sal = sal;
}
public void setEmpno(String empno) {
this.empno = empno;
}

public void setEname(String ename) {
this.ename = ename;
}

public void setSal(String sal) {
this.sal = sal;
}
public String getEmpno() {
return empno;
}
public String getEname() {
return ename;
}
public String getSal() {
return sal;
}
}



3. DAO단 interface 및 구현클래스

package edu.onj.function;

import java.util.List;

import javax.sql.DataSource;

public interface EmpDao {
public void setDataSource(DataSource ds);
public int[] createEmpList(final List<Emp> emps);
public List<Emp> listEmp(Integer empno);
}



package edu.onj.function;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class EmpDaoImpl implements EmpDao {
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplate = new JdbcTemplate(dataSource);
}

public DataSource getDataSource() {
return dataSource;
}

/*
* 여런건의 EMP 데이터를 BatchPreparedStatementSetter를 이용하여 일괄 인서트
* @see edu.onj.function.EmpDao#createEmpList(java.util.List)
*/
@Override
public int[] createEmpList(final List<Emp> emps) {
String SQL = "insert into emp(empno, ename, sal) values (?, ?, ?)";
BatchPreparedStatementSetter setter = null;
setter = new BatchPreparedStatementSetter() {

@Override
public int getBatchSize() {
return emps.size();
}

@Override
public void setValues(PreparedStatement ps, int index)
throws SQLException {
Emp emp = emps.get(index);
int parameterIndex = 1;
ps.setString(parameterIndex++, emp.getEmpno());
ps.setString(parameterIndex++, emp.getEname());
ps.setString(parameterIndex++, emp.getSal());
}
};
return jdbcTemplate.batchUpdate(SQL, setter);
}

/*
* 오라클 Stored Function을 호출하여 10번 부서 사원리스트 출력(REF CURSOR)
* @see edu.onj.function.EmpDao#listEmp(java.lang.Integer)
* 
* 
* [오라클서버쪽 PL/SQL 함수 코드]
* create or replace package types 
as 
type currtype is ref cursor; 
end; 
/ 
create or replace function getEmp(v_deptno in number) return types.currtype 
AS 
emp_cursor currtype; 
sql_string VARCHAR2(500); 
BEGIN 
 sql_string := 'SELECT empno, ename, sal FROM EMP WHERE DEPTNO = '|| v_deptno; 
 OPEN emp_cursor FOR sql_string ; 
 RETURN emp_cursor; 
 CLOSE emp_cursor; 
END; 
/
* 
* 
* 
*/
@Override
public List<Emp> listEmp(Integer deptno) {
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(this.dataSource)
.withSchemaName("scott")
.withFunctionName("getEmp")
.declareParameters(
                   new SqlOutParameter("emp_cursor", OracleTypes.CURSOR, new EmpMapper()),
                   new SqlParameter("v_deptno", Types.INTEGER))                
               .withoutProcedureColumnMetaDataAccess();

SqlParameterSource params = new MapSqlParameterSource("v_deptno", deptno);

//execute메서드는 Map형태러 리턴한다.
Map<String, Object> resultSet = simpleJdbcCall.execute(params);
return (List<Emp>) resultSet.get("emp_cursor");
}
}



4. 매퍼 클래스


package edu.onj.function;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class EmpMapper implements RowMapper<Emp> {

@Override
public Emp mapRow(ResultSet rs, int rowNum) throws SQLException {
Emp emp = new Emp();
emp.setEmpno(rs.getString("empno"));
emp.setEname(rs.getString("ename"));
emp.setSal(rs.getString("sal"));
return emp;
}

}




5. 클라이언트 main함수


package edu.onj.function;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.support.GenericXmlApplicationContext;

public class JdbcClient {

public static void main(String[] args) {
GenericXmlApplicationContext ctx = new GenericXmlApplicationContext();
ctx.load("jdbc.xml");
ctx.refresh();
EmpDaoImpl empDao = (EmpDaoImpl)ctx.getBean("empDao");
//1. EMP TABLE의 empno가 1001인 데이터 추출
List<Emp> emps = empDao.listEmp(1001);
System.out.println("------------------ all select ------------------");
for (Emp emp : emps) {
System.out.println("empno-->" + emp.getEmpno());
System.out.println("ename-->" + emp.getEname());
System.out.println("sal  -->" + emp.getSal());
}

emps.clear();
//2. BatchPreparedStatementSetter를 이용하여 List에 있는것을 일괄 Insert
emps.add(new Emp("9701", "1001길동", "10010"));
emps.add(new Emp("9702", "1002길동", "10020"));
emps.add(new Emp("9703", "1003길동", "10030"));
emps.add(new Emp("9704", "1004길동", "10040"));
emps.add(new Emp("9705", "1005길동", "10050"));
empDao.createEmpList(emps);
//3.spring JDBC SimpleJdbcCall을 이용한 오라클 함수(ref cursor리턴)호출을 통한 10번 부서 EMP 추출
ArrayList<Emp> emps2 = (ArrayList<Emp>)empDao.listEmp(new Integer(10));
for(Emp e: emps2) {
System.out.println(e.getEmpno() + ":" + e.getEname() + ":" + e.getSal());
}
ctx.close();
}

}






[결과]


SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/C:/java2/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/C:/Documents%20and%20Settings/Administrator/.m2/repository/ch/qos/logback/logback-classic/1.0.13/logback-classic-1.0.13.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
INFO : org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [jdbc.xml]
INFO : org.springframework.context.support.GenericXmlApplicationContext - Refreshing org.springframework.context.support.GenericXmlApplicationContext@1791620: startup date [Thu Jul 10 12:32:25 KST 2014]; root of context hierarchy
INFO : org.springframework.beans.factory.support.DefaultListableBeanFactory - Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@99175d: defining beans [dataSource,empDao]; root of factory hierarchy
------------------ all select ------------------
7782:CLARK:2450
7839:KING:5000
7934:MILLER:1300
7990:Onj1:null
8885:Onj1:null
8886:Onj2:null
8887:Onj3:null
8888:Onj3:null
1711:짱윤탱:null
1722:최일웅:null
1733:짱문탱:null
435:Onj1:null
442:Onj2:null
343:Onj3:null
721:Onj85:null
722:Onj82:null
723:Onj84:null
737:Onj85:null
738:Onj82:null
739:Onj84:null
7411:Onj85:null
7412:Onj82:null
7413:Onj84:null
1717:Onj1:null
2727:Onj2:null
3737:Onj3:null
1101:song:null
1102:song:null
1103:song:null
8717:Onj1:null
8727:Onj2:null
8737:Onj3:null
8747:Onj3:null
1104:song:null
7031:choi1:null
7030:choi1:null
4352:Onj1:null
1559:Onj2:null
1558:Onj3:null
9191:kth1:null
123:Onj1:null
456:Onj2:null
789:Onj3:null
9717:Onj1:null
9727:Onj2:null
9737:Onj3:null
9747:Onj3:null
5236:kth3:null
7717:Onj1:null
7727:Onj2:null
7737:Onj3:null
7747:Onj3:null
1523:kth1:null
4423:kth2:null
7771:Onj1:null
7772:Onj2:null
7773:Onj3:null
9900:Onj1:null
9990:Onj1:null
11:jang:null
22:moon:null
33:gyu:null
9995:Onj1:null
9996:Onj2:null
9997:Onj3:null
3995:Onj1:null
3996:Onj2:null
3997:Onj3:null
2997:Onj3:null
173:Onj1:null
213:Onj2:null
313:Onj3:null
333:Onj3:null
727:Onj85:null
728:Onj82:null
729:Onj84:null
7404:Onj85:null
7405:Onj82:null
7406:Onj84:null
1:변경된이름:null
2:권재현:null
3:강성진:null
8771:Onj1:null
8772:Onj2:null
8773:Onj3:null
8881:Onj1:null
8882:Onj2:null
8883:Onj3:null
8884:Onj3:null
724:Onj85:null
725:Onj82:null
726:Onj84:null
714:Onj85:null
731:Onj85:null
732:Onj82:null
733:Onj84:null
7401:Onj85:null
7402:Onj82:null
7403:Onj84:null
7407:Onj85:null
7408:Onj82:null
7409:Onj84:null
911:Onj1:null
922:Onj2:null
923:Onj3:null
9564:moonsun83:null
6165:moongyu:null
5424:saerom:null
890:saeroms:null
9911:Onj1:null
9922:Onj2:null
9923:Onj3:null
111:Onj1:null
222:Onj2:null
7421:Onj85:null
7422:Onj82:null
7423:Onj84:null
7022:choi1:null
7021:choi1:null
7020:choi3:null
7019:choi5:null
7027:choi1:null
7026:choi1:null
7029:choi3:null
4100:MI61:null
4101:MI62:null
4102:MI63:null
7032:choi1:null
7033:choi1:null
1105:song:null
1106:song:null
1107:song:null
INFO : org.springframework.context.support.GenericXmlApplicationContext - Closing org.springframework.context.support.GenericXmlApplicationContext@1791620: startup date [Thu Jul 10 12:32:25 KST 2014]; root of context hierarchy
INFO : org.springframework.beans.factory.support.DefaultListableBeanFactory - Destroying singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@99175d: defining beans [dataSource,empDao]; root of factory hierarchy

오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급
www.oraclejavacommunity.com

평일주간(9:30~18:10) 개강
(7/14)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍
(7/14)[기업100%환급]SQL기초에서 Schema Object까지
(7/14)[기업100%환급]안드로이드개발자과정
(7/21)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(7/21)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(7/21)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(7/21)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정

평일야간(19:00~21:50) 개강
(7/15)SQL기초에서실무까지
(7/15)안드로이드개발자과정
(7/16)Spring3.X, MyBatis, Hibernate실무과정
(7/17)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(7/21)웹퍼블리싱 마스터
(7/22)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(8/05)MyBatis3.X, Hibernate4.X ORM실무과정

주말(10:00~17:50) 개강
(7/12)SQL초보에서 Schema Object까지
(7/12)개발자를위한PLSQL,SQL튜닝,힌트
(7/19)C#,ASP.NET마스터
(7/19)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지)
(7/19)Spring3.X, MyBatis, Hibernate실무과정
(7/19)웹퍼블리싱 마스터
(7/19)안드로이드개발자과정
(8/02)MyBatis3.X, Hibernate4.X ORM실무과정
(8/09)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지

주말저녁(18:30~22:20) 개강
(8/02)JAVA,Network&WEB&Framework
(8/09)SQL기초에서실무까지
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)