2014년 7월 26일 토요일

[스프링프레임워크교육학원]spring jdbc,오라클 함수(oracle function)호출(ref cursor) 방법3가지,SimpleJdbcCall ,CallableStatement, StoredProcedure, SqlOutParameter, 스프링프레임워크교육학원추천

[스프링프레임워크교육학원]spring jdbc,오라클 함수(oracle function)호출(ref cursor) 방법3가지,SimpleJdbcCall ,CallableStatement, StoredProcedure, SqlOutParameter, 스프링프레임워크교육학원추천

spring jdbc를 이용하여 오라클 함수를 호출하는 방법은 3가지 정도로 요약된다. 예제를 통해 확인하자.


[오라클 함수가 아래와 같다면]

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; 
/



1. SimpleJdbcCall 이용방법

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;
}
}



-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;
}

@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);
}

@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");
}
}



-매퍼 클래스


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;
}

}




-클라이언트 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();
}

}



2. 기존 JDBC처럼 CallableStatement이용

@Override
public List<Emp> listEmp(final Integer deptno){
CallableStatementCallback<List<Emp>> cb = new CallableStatementCallback<List<Emp>>() {

@Override
public List<Emp> doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.setInt(2, deptno);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);

List<Emp> emps = new ArrayList<Emp>();
EmpMapper mapper = new EmpMapper();
for (int i = 0; rs.next(); i++) {
emps.add(mapper.mapRow(rs, i));
}
 
return emps;
}
};
return jdbcTemplate.execute("{? = call getEmp(?)}", cb);
}


3. Spring Framework의 StoredProcedure를 상속받아서




public class Emp {
private Integer empno;
private String ename;
private Integer sal;
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Integer getSal() {
return sal;
}
public void setSal(Integer sal) {
this.sal = sal;
}
}





import java.util.List;

import javax.sql.DataSource;


public interface EmpDao {
public void setDataSource(DataSource ds);
public List<Emp> listEmp(Integer deptno);
}






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 {
// TODO Auto-generated method stub
Emp emp = new Emp();
emp.setEmpno(rs.getInt("empno"));
emp.setEname(rs.getString("ename"));
emp.setSal(rs.getInt("sal"));
return emp;
}

}





import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import onj.edu.jdbc1.Customer;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class JdbcClient {

public static void main(String[] args) {
// TODO Auto-generated method stub
ApplicationContext ctx = new ClassPathXmlApplicationContext("third2.xml");
EmpJDBCTemplate empJDBCTemplate = (EmpJDBCTemplate)ctx.getBean("empJDBCTemplate");
ArrayList<Emp> list = new ArrayList<Emp>();
list = (ArrayList<Emp>) empJDBCTemplate.listEmp(20);
for (Emp emp:list){
System.out.println("empno --> "+emp.getEmpno()+" ename --> "+emp.getEname()+" sal --> "+emp.getSal() );
}

}

}



import java.sql.Types;
import java.util.*;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class EmpJDBCTemplate extends StoredProcedure implements EmpDao {
 private DataSource dataSource;
 private static final String SQL = "getEmp";

 public EmpJDBCTemplate(DataSource dataSource) {
  // TODO Auto-generated constructor stub
  super(dataSource, SQL);
  setFunction(true);
  declareParameter(new SqlOutParameter("emp_cursor", OracleTypes.CURSOR, new EmpMapper()));
  declareParameter(new SqlParameter("v_deptno", Types.INTEGER));
 }


 @Override
 public List<Emp> listEmp(Integer deptno) {
  Map<String, Object> resultSet = execute(deptno);
  ArrayList<Emp> list = (ArrayList) resultSet.get("emp_cursor"); 
  return list;
 }
}



<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.7:1521:onj</value>
 </property>
 <property name="username">
  <value>scott</value>
 </property>
 <property name="password">
  <value>tiger</value>
 </property>
</bean>
<bean id="empJDBCTemplate" class="onj.practice.third3.EmpJDBCTemplate">
 <constructor-arg ref="dataSource"/>
</bean>
</beans>
오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급
www.oraclejavacommunity.com

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

평일야간(19:00~21:50) 개강
(7/29)안드로이드개발자과정
(7/29)Spring3.X, MyBatis, Hibernate실무과정
(8/04)웹퍼블리싱 마스터
(8/05)JSP,Ajax,jQUERY,Spring,MyBatis,Hibernate속성과정
(8/08)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(8/11)SQL기초에서실무까지
(8/28)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지

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

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

작성자: 시간:

댓글 없음:

댓글 쓰기

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