使用 Spring 框架调用 DB2 存储过程

db2 CATALOG TCPIP NODE node01 REMOTE zxl01.cn.ibm.com SERVER 50001

db2 CATALOG DB testDB AT NODE node01

DROP TABLE DSW.CUSTOMER
@
CREATE TABLE DSW.CUSTOMER 
(
    CUST_NUM                 VARCHAR(10) NOT NULL,
    CUST_NAME                VARCHAR(35) NOT NULL,
    ADDRESS                  VARCHAR(35) NOT NULL,
    CITY                     VARCHAR(35) NOT NULL,
    COUNTRY                  VARCHAR(3)  NOT NULL,
    ADD_DATE                 TIMESTAMP     NOT NULL
)
@
ALTER TABLE DSW.CUSTOMER 
    ADD CONSTRAINT CUSTOMER_PK
PRIMARY KEY (CUST_NUM)
@

DROP PROCEDURE DSW.S_CUSTOMER
@
CREATE PROCEDURE DSW.S_CUSTOMER (
  OUT poStatus     INTEGER,
  IN  piCust_num      VARCHAR(10)
)
LANGUAGE SQL
SPECIFIC DSW.S_CUSTOMER
RESULT SETS 2

P1: BEGIN NOT ATOMIC
   -------------------------------------------------------------
   -- CONDITION declaration
   -------------------------------------------------------------
   DECLARE sqlReset    CONDITION for sqlstate '80100';

   -- Generic Variables
   DECLARE SQLCODE      INTEGER       DEFAULT 0;
   DECLARE SQLSTATE     CHAR(5)       DEFAULT '00000';
  
   -- Generic handler variables
   DECLARE hSqlcode     INTEGER       DEFAULT 0;
   DECLARE hSqlstate    CHAR(5)       DEFAULT '00000';

   DECLARE v_Cnt INT;

   -- Cursor for returning RS to the client
   DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR (
	   SELECT CUST_NUM, CUST_NAME FROM DSW.CUSTOMER
   );

   DECLARE cursor2 CURSOR WITH RETURN TO CLIENT FOR (
	   SELECT * FROM DSW.CUSTOMER
	   WHERE CUST_NUM = piCust_num
   );
   
   OPEN cursor1;
   
   OPEN cursor2;

   SET poStatus = 2;
   RETURN poStatus;

END P1
@

INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('1','Steven','shang di','BeiJing','CHN')
@
INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('2','David','shang di','BeiJing','CHN')
@
INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('3','Tony','shang di','BeiJing','CHN')
@
INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
VALUES('4','Maggie','shang di','BeiJing','CHN')
@

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application
2.3//EN" 
"http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app id="WebApp">
	<display-name>SpringSproc</display-name>
	<!-- Standard Action Servlet Configuration -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>
			/WEB-INF/applicationContext-service.xml
		</param-value>
	</context-param>
	<listener>
		<listener-class>
		org.springframework.web.context.ContextLoaderListener
		</listener-class>
	</listener>
	<servlet>
		<servlet-name>action</servlet-name>
		<servlet-class>
			org.apache.struts.action.ActionServlet
		</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>action</servlet-name>
		<url-pattern>*.do</url-pattern>
	</servlet-mapping>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
	</welcome-file-list>
</web-app>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
 	<bean id="dataSourceJDBC"
	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName">
			<value>COM.ibm.db2.jdbc.app.DB2Driver</value>
		</property>
		<property name="url">
			<value>jdbc:db2:testDB</value>
		</property>
		<property name="username">
			<value>dsw</value>
		</property>
		<property name="password">
			<value>pass4spring</value>
		</property>
	</bean>
<bean id="SearchImpl" class="com.springsproc.dao.jdbc.SearchDAOImpl">
		<property name="dataSource">
			<ref local="dataSourceJDBC" />
		</property>
		<property name="sprocName">
			<value>DSW.S_CUSTOMER</value>
		</property>
</bean>
</beans>

private static ApplicationContext ctx = null;
public Object getBean(String name) {                  |(1)
		if (ctx == null) {
			ctx = WebApplicationContextUtils
					.getRequiredWebApplicationContext
					(servlet.getServletContext());
		}
		return ctx.getBean(name);
	}
SearchDAO mgr = (SearchDAO) getBean("SearchImpl"); |(2)
Map map = mgr.execute(custNum);                        |(3)

package com.springsproc.dao;

import java.util.List;

public interface SearchDAO {
	public Map execute(String custNum);
}

public Map execute(String custNum) {
		SearchCustomersProcedure sp = new SearchCustomersProcedure(dataSource);
		Map map = new HashMap(1);
		map.put("piCust_num", custNum);
		Map results = sp.execute(map);              |(1)
		return results;
}

private class SearchCustomersProcedure extends StoredProcedure {
	SearchCustomersProcedure(DataSource dataSource) {
		super(dataSource, sprocName);                |(2)   
		declareParameter(new SqlReturnResultSet("resultSet",
				new DemoRowMapper()));      |(3)
		declareParameter(new SqlReturnResultSet("resultSet2",
				new DemoRowMapper2()));     |(4)

		declareParameter(new SqlOutParameter("poGenStatus", Types.INTEGER));  
		|(5)
		declareParameter(new SqlParameter("piCust_num", Types.VARCHAR));  
		|(6)	}
}

private class DemoRowMapper implements RowMapper {          |(7)
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		Customer customer = new Customer();
		customer.setCustNum(rs.getString(1));
		customer.setCustName(rs.getString(2));
		return customer;
	}
}

private class DemoRowMapper2 implements RowMapper {         |(8)
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		Customer customer = new Customer();
		customer.setCustName(rs.getString(1));
		customer.setCustNum(rs.getString(2));
customer.setCustAddress(rs.getString(3));
		customer.setCustCity(rs.getString(4));
		customer.setCustCountry(rs.getString(5));
		return customer;
	}
}

List list = (List)map.get("resultSet");
List list2 = (List)map.get("resultSet2");
String poGenStatus = map.get("poGenStatus").toString();
log.info("poGenStatus is:" + poGenStatus);
                                   
request.setAttribute("customerList",list);  |(1)
request.setAttribute("MyCustomers",list2);  |(2)
                                   
return mapping.findForward("list");

|(1)
<display:table name="customerList" cellspacing="2" cellpadding="2"
    requestURI="" defaultsort="1" id="customers">
    <%-- Table columns --%>
    <display:column property="custNum" sort="true" 
    	headerClass="sortable"
        title="Customer number" headerStyleClass="header"/>
    <display:column property="custName" sort="true"
    	headerClass="sortable"
        title="Customer name" headerStyleClass="header"/>
</display:table>

|(2)
<display:table name="myCustomer" cellspacing="2" cellpadding="2"
    requestURI="" defaultsort="1" id="myCustomer">
</display:table>