Spring+Jdbc scott用户的调用存储过程

本章使用的是 scott 用户

创建存储过程

create or replace procedure get_dept_name
(
       in_sn in number,
       out_dept_name out varchar2
)is
begin
     select d.dname
     into out_dept_name
     from emp e
     left join dept d
     on d.deptno = e.deptno
     where e.empno = in_sn;
end get_dept_name;

 

调用存储过程

declare
 dname varchar2(30);
begin
 get_dept_name(7698,dname);
 dbms_output.put_line(dname);
end;

 

测试存储过程的效果：

 

EmpDao 业务类

package cn.dao;
public interface EmpDao {
	/**
	 * 调用存储过程，通过员工的ID得到所在部门的名字
	 * @param sn
	 * @return
	 */
	public String getDeptNameByEmpSn(Integer sn);
}

 

package cn.dao.impl;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import cn.dao.EmpDao;
public class EmpDaoImpl extends JdbcDaoSupport implements EmpDao {
	public String getDeptNameByEmpSn(final Integer sn) {
		return super.getJdbcTemplate().execute(new CallableStatementCreator() {
			//实现 createCallableStatement 对象（创建存储过程）
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement cs = conn.prepareCall("{call get_dept_name(?,?)}");
				cs.setInt(1, sn);
				cs.registerOutParameter(2, OracleTypes.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<String>() {
			//实现 doInCallableStatement 对象（调用存储过程）
			public String doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(2);
			}
		});
	}
}

 

applicationContext.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" 
	xmlns:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">
	<!-- 配置数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
		<property name="url" value="jdbc:oracle:thin:@localhost:1521:oracle11" />
		<property name="username" value="scott" />
		<property name="password" value="tiger" />
	</bean>
	<!-- 配置 jdbc 模板并注入 dataSource -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	<!-- 配置 Dao -->
	<bean id="empDao" class="cn.dao.impl.EmpDaoImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
</beans>

 

EmpTest 测试类

package cn.test;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import cn.dao.EmpDao;
public class EmpTest {
	EmpDao empDao;
	static ApplicationContext ctx;
	@BeforeClass  
	public static void init(){
		ctx=new	ClassPathXmlApplicationContext("applicationContext.xml");
	}
	@Before 
	public void setUp(){
		empDao=(EmpDao) ctx.getBean("empDao");
	}

	@After
	public void tearDown(){
		empDao=null;
	}
	@AfterClass
	public static void destory(){
		ctx=null;
	}
	@Test
	//测试 存储过程是否调用成功
	public void testGetDeptName(){
		System.out.println(empDao.getDeptNameByEmpSn(7839));
	}
	
}

 

效果图：