IT실전교육,탑크리에듀교육센터: spring jdbc,오라클 함수(oracle function)호출(ref cursor) 방법3가지,SimpleJdbcCall ,CallableStatement, StoredProcedure, SqlOutParameter spring jdbc를 이용하여 오라클 함수를 호출하는 방법은 3가지 정도...

2014년 8월 4일 월요일

spring jdbc,오라클 함수(oracle function)호출(ref cursor) 방법3가지,SimpleJdbcCall ,CallableStatement, StoredProcedure, SqlOutParameter spring jdbc를 이용하여 오라클 함수를 호출하는 방법은 3가지 정도...

spring jdbc,오라클 함수(oracle function)호출(ref cursor) 방법3가지,SimpleJdbcCall ,CallableStatement, StoredProcedure, SqlOutParameter

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

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

create or replace package types

type currtype is ref cursor;

end;

create or replace function getEmp(v_deptno in number) return types.currtype

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

}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.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.7:1521:onj</value>
</property>
<property name="username">
  <value>scott</value>
</property>
<property name="password">
  <value>tiger</value>
</property>
</bean>

[출처] 오라클자바커뮤니티 - http://www.oraclejavacommunity.co.kr/bbs/board.php?bo_table=LecSpring&wr_id=417

평일주간[100%환급과정]
(8/08)Spring,MyBatis,Hibernate실무과정
(8/08)C#4.0,WinForm,ADO.NET
(8/11)SQL기초에서 Schema Object까지
(8/11)채용예정교육
(8/18)자바기초JDBC,Servlet/JSP까지
(8/18)안드로이드개발자과정
(8/18)PL/SQL,ORACLE HINT,TUNING
평일야간[개인80%환급]
(8/06)JSP,jQUERY,Spring,MyBatis
(8/11)SQL기초에서실무까지
(8/11)안드로이드개발자과정
(8/12)Spring, MyBatis, Hibernate
(8/13)C#,Network,ADO.NET,ASP.NET
(8/13)HTML5,CSS3,Ajax,jQuery마스터
(8/28)자바JSP,jQuery,Spring,MyBatis
주말주간[개인80%환급]
(8/09)SQL기초에서실무까지
(8/09)안드로이드개발자과정
(8/09)자바JSP,Ajax,jQuery,Spring,MyBatis(8/16)C#,ASP.NET마스터 (8/16)웹퍼블리싱 마스터
(8/16)Spring, MyBatis, Hibernate
(8/23)JAVA,Network&WEB&Framework
주말야간[개인80%환급]
(8/09)SQL기초에서실무까지
(8/23)JAVA,Network&WEB&Framework

2014년 8월 4일 월요일

spring jdbc,오라클 함수(oracle function)호출(ref cursor) 방법3가지,SimpleJdbcCall ,CallableStatement, StoredProcedure, SqlOutParameter spring jdbc를 이용하여 오라클 함수를 호출하는 방법은 3가지 정도...

댓글 없음:

댓글 쓰기