Spring JDBC에서 DB 접근하는 방법 – SimpleJdbcCall(자바교육/자바학원/스프링교육/스프링학원)
http://ojc.asia/bbs/board.php?bo_table=LecSpring&wr_id=897
ojc.asia
https://www.youtube.com/watch?v=Pn4k4PSewxw&list=PLxU-iZCqT52B7oCYJltUT2-kBzckdr5vp&index=16&t=5s
- DataBase에 SQL로 만들어져 있는 저장함수, 프로시저를 다루기 위한 클래스로 최소한의 구성으로 DB함수, Procedure에 접근할 수 있다.
https://www.youtube.com/watch?v=2MlbXhykkwU&list=PLxU-iZCqT52B7oCYJltUT2-kBzckdr5vp&index=17
오라클 서버쪽 패키지(Package) 및 함수(Function)
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 types.currtype;
sql_string VARCHAR2(500);
BEGIN
sql_string := 'SELECT empno, ename, sal FROM EMP WHERE DEPTNO = :1’ ;
OPEN emp_cursor FOR sql_string USING v_deptno;
RETURN emp_cursor;
CLOSE emp_cursor;
END;
/
New - Project - Spring Legacy Project
Project Name : springjdbc
Simple Projects : Simple Spring Maven
[pom.xml]
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.springframework.samples</groupId>
<artifactId>springjdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<!-- Generic properties -->
<java.version>1.6</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<!-- Spring -->
<spring-framework.version>4.3.9.RELEASE</spring-framework.version>
<!-- Hibernate / JPA -->
<hibernate.version>4.2.1.Final</hibernate.version>
<!-- Logging -->
<logback.version>1.0.13</logback.version>
<slf4j.version>1.7.5</slf4j.version>
<!-- Test -->
<junit.version>4.11</junit.version>
</properties>
<dependencies>
<!-- Spring and Transactions -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<!-- Logging with SLF4J & LogBack -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback.version}</version>
<scope>runtime</scope>
</dependency>
<!-- Hibernate -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>${hibernate.version}</version>
</dependency>
<!-- Test Artifacts -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring-framework.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring-framework.version}</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>codelds</id>
<url>https://code.lds.org/nexus/content/groups/main-repo;
</repository>
</repositories>
</project>
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 서비스를 위한 인터페이스[EmpDao.java]
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 deptno);
}
DAO 서비스를 위한 클래스[EmpDao.java]
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 를 이용하여 일괄 인서트
*/
@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) */
@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");
}
}
매퍼 클래스[EmpMapper.java]
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;
}
}
XML 설정 파일[src/main/resources/jdbc.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.2.xsd ">
<!-- Initialization for data source -->
<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="empDao" class=" edu.onj.function.EmpDaoImpl">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
클라이언트 main 함수[JdbcClient.java]
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 에서 부서코드가 10 인 데이터 추출
List<Emp> emps = empDao.listEmp(10);
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();
}
}
위 예제의 listEmp() 메소드를 기존 자바 JDBC의 CallableStatementCallback을 이용한 방식으로 변경하면 다음과 같다.
@Override
public List<Emp> listEmp2(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);
}
#SimpleJdbcCall, #JPA, #JPA교육, #스프링부트, #스프링CRUD예제, #마리아DB, #스프링교육, #스프링동영상, #자바교육, #자바학원, #스프링학원, #스프링JDBC, SimpleJdbcCall, JPA, JPA교육, 스프링부트, 스프링CRUD예제, 마리아DB, 스프링교육, 스프링동영상, 자바교육, 자바학원, 스프링학원, 스프링JDBC
댓글 없음:
댓글 쓰기