(오라클자바커뮤니티,Spring JDBC, 스프링프레임워크교육학원,데이터베이스,오라클프러시저다루기)BatchPreparedStatementSetter, SimpleJdbcCall 예제

(오라클자바커뮤니티,Spring JDBC, 스프링프레임워크교육학원,데이터베이스,오라클프러시저다루기)BatchPreparedStatementSetter, 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();

}

}

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

평일주간[100%환급과정](8/25)C#4.0,WinForm,ADO.NET
(8/25)안드로이드개발자과정
(8/25)SQL기초에서 Schema Object까지
(8/29)Spring,MyBatis,Hibernate실무과정
(8/29)자바기초JDBC,Servlet/JSP까지
(8/29)PL/SQL,ORACLE HINT,TUNING
(8/25)오라클자바채용확정교육
평일야간[개인80%환급]
(8/21)Spring, MyBatis, Hibernate
(8/21)HTML5,CSS3,Ajax,jQuery마스터
(8/21)C#,Network,ADO.NET,ASP.NET
(8/26)SQL기초에서실무까지
(8/26)안드로이드개발자과정
(8/28)자바JSP,jQuery,Spring,MyBatis
주말주간[개인80%환급]
(8/23)자바웹&스프링,마이바티스
(8/23)Spring, MyBatis, Hibernate
(8/23)SQL기초에서실무까지
(8/23)자바,네트워크,웹&스프링
(8/30)안드로이드개발자과정
(8/30)C#,ASP.NET마스터(8/30)웹퍼블리싱 마스터
주말야간[개인80%환급]
(8/23)SQL기초에서실무까지
(8/23)자바,네트워크,웹&스프링