(原创)Ibatis2调用数据库存储过程的相关示例
第一种方式，传入一个参数(非自定义java类型)，返回单个值：
?
oracle代码：
???
CREATE OR REPLACE PROCEDURE testPro(bidObjectId? NUMBER,projectId OUT NUMBER)
AS
BEGIN
?????? SELECT bo.project_id INTO projectId FROM bm_t_bid_object bo WHERE bo.bid_object_id = bidObjectId;
? END;
??
Ibatis配置：
???? <parameterMap id="projectIdProc" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
??????? <parameter property="result" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
??? </parameterMap>
?
<procedure id="getProjectIdProc" parameterMap="projectIdProc">
??????? { call testPro(?,?) }
</procedure>
?
Java调用代码：
???? public Long getProjectIdProc(Map map) throws Exception {
??????? this.getBaseDao().getSqlMapClientTemplate().queryForObject("getProjectIdProc",map);
??????? Long pId = Long.valueOf(map.get("result").toString());???????
??????? return pId;
??? }
?
Junit测试代码：基于org.springframework.test.AbstractDependencyInjectionSpringContextTests测试
????? public void testGetProjectIdProc() {
??????? HashMap parameters = new HashMap();
??????? parameters.put("bidObjectId","900000000000000003");
??????? Object o = null;
??????? try {
??????????? o = projectMgr.getProjectIdProc(parameters);
??????? } catch (Exception e) {
??????????? e.printStackTrace();
??????? }
??????? System.out.println(o);
??? }
?
第二种方式：?传入单个参数，返回游标结果集
?
oracle代码：
?
CREATE OR REPLACE PACKAGE myPage
AS
? TYPE myrctype IS REF CURSOR;
? PROCEDURE testListPro(projectId NUMBER,return_cursor OUT myrctype);
END;
?
CREATE OR REPLACE PACKAGE BODY myPage
AS
? PROCEDURE testListPro(projectId NUMBER,return_cursor OUT myrctype)
??? IS
??? sqlStr VARCHAR(200);
??? BEGIN
????? sqlStr := 'SELECT * FROM bm_t_bid_object bo WHERE bo.project_id =:p_id';
????? OPEN return_cursor FOR sqlStr USING projectId;
??? END testListPro;
END myPage;
?
Ibatis配置：
?
<resultMap id="bidObjectBasic" column="BID_OBJECT_ID"/>
??????? <result property="objectNo" column="OBJECT_NO"/>
??????? <result property="objectName" column="OBJECT_NAME"/>
??? </resultMap>
?
??? <parameterMap id="bidObjectsProc" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
??????? <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="bidObjectBasic"/>
??? </parameterMap>
?
??? <procedure id="getBidObjectsProc" parameterMap="bidObjectsProc">
??????? { call MYPAGE.testListPro(?,?) }
??? </procedure>
?
Java调用代码：
?
public List getBidObjectsProc(Map map) throws Exception {
??????? this.getBaseDao().getSqlMapClientTemplate().queryForList("getBidObjectsProc",map);
??????? List list = (ArrayList) map.get("result");
??????? return list;
??? }
?
Junit测试代码:
public void testGetBidObjectsProc() {
??????? HashMap parameters = new HashMap();
??????? parameters.put("projectId","120000000000000004");
??????? List list = null;
??????? try {
??????????? list = projectMgr.getBidObjectsProc(parameters);
??????? } catch (Exception e) {
??????????? e.printStackTrace();
??????? }
??????? System.out.println("list.size() = " + list.size());
??? }
?
?
第三种方式，传入一个List<Project> projects参数，返回游标
?
oracle代码：
?
CREATE OR REPLACE TYPE OPROJECT IS OBJECT
(
?????? pid NUMBER(18),
?????? pname NVARCHAR2(200),
?????? pno NVARCHAR2(100)
)
?
CREATE OR REPLACE TYPE MY_OPROJECT_TYPE AS TABLE OF OPROJECT
?
CREATE OR REPLACE PACKAGE mypageforList
AS
? TYPE myrctype IS REF CURSOR;
? PROCEDURE GETBIDOBJECTSBYPROJECTS(MY_OPROJECT IN MY_OPROJECT_TYPE,return_cursor OUT myrctype);
END mypageforList;
?
CREATE OR REPLACE PACKAGE BODY MYPAGEFORLIST AS
? PROCEDURE GETBIDOBJECTSBYPROJECTS(MY_OPROJECT?? IN MY_OPROJECT_TYPE,
??????????????????????????????????? RETURN_CURSOR OUT MYRCTYPE) IS
??? PROJECTID_STRS VARCHAR2(200) := '';
??? sqlstr VARCHAR2(200);
? BEGIN???
??? FOR I IN 1 .. MY_OPROJECT.COUNT() LOOP
????? PROJECTID_STRS := CONCAT(PROJECTID_STRS,CONCAT(to_char(MY_OPROJECT(I).PID),','));??????
??? END LOOP;?
??????? PROJECTID_STRS := SUBSTR(PROJECTID_STRS,1,(LENGTH(PROJECTID_STRS)-1));
???? sqlstr := 'SELECT * FROM BM_T_BID_OBJECT BO
?????? WHERE BO.PROJECT_ID IN ('||PROJECTID_STRS||')';
??? --OPEN return_cursor FOR SELECT * FROM bm_t_bid_object bo WHERE bo.project_id = MY_OPROJECT(2).pid;
??? OPEN RETURN_CURSOR FOR sqlstr;
? END GETBIDOBJECTSBYPROJECTS;
END MYPAGEFORLIST;
?
Ibatis配置：
?
<parameterMap id="bidObjectsByProjectListProc" jdbcType="ARRAY" javaType="java.util.List" mode="IN" typeHandler="ProjectListHandler"/>
??????? <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="bidObjectBasic"/>
??? </parameterMap>
??? <procedure id="getBidObjectsByProjectListProc" parameterMap="bidObjectsByProjectListProc">
??????? { call mypageforList.GETBIDOBJECTSBYPROJECTS(?,?) }
??? </procedure>
?
java代码：
?
public class ProjectListHandler implements TypeHandlerCallback {
??? @Override
??? public void setParameter(ParameterSetter parameterSetter, Object o) throws SQLException {
??????? Connection conn = null;
??????? try {
??????????? if (null != o) {
??????????????? List<Project> list = (ArrayList<Project>) o;
??????????????? System.out.println("传入的java参数list长度为： = " + list.size());
??????????????? Class c = Class.forName("oracle.jdbc.OracleDriver");
??????????????? conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "powerbid_test", "powerbid_test");
??????????????? System.out.println("conn = " + conn);
??????????????? ARRAY array = getArray(conn, "OPROJECT", "MY_OPROJECT_TYPE", list);
??????????????? parameterSetter.setArray(array);
??????????? }
??????? } catch (ClassNotFoundException e) {
??????????? e.printStackTrace();
??????? } catch (Exception e) {
??????????? e.printStackTrace();
??????? } finally {
??????????? if (null != conn) {
??????????????? conn.close();
??????????? }
??????? }
??? }
??? @Override
??? public Object getResult(ResultGetter resultGetter) throws SQLException {
???????//todo 如果返回的数据库类型要转换成某种特定的java类型，实现这个方法
??????? return null;
??? }
??? @Override
??? public Object valueOf(String s) {
??????? return null;
??? }
??? private ARRAY getArray(Connection con, String OracleObj,
?????????????????????????? String Oraclelist, List listData) throws Exception {
??????? ARRAY array = null;
??????? if (listData != null && listData.size() > 0) {
??????????? ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, con);
??????????? StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
??????????? STRUCT[] structs = new STRUCT[listData.size()];
??????????? for (int i = 0; i < listData.size(); i++) {
??????????????? Object[] result = {((Project) listData.get(i)).getId().toString(),
??????????????????????? ((Project) listData.get(i)).getProjectName(),
??????????????????????? ((Project) listData.get(i)).getProjectNo()};
??????????????? structs[i] = new STRUCT(structdesc, con, result);
??????????? }
??????????? array = new ARRAY(desc, con, structs);
??????? }
??????? return array;
??? }
}
?
public List getBidObjectsByProjectListProc(Map map) throws Exception {
??????? this.getBaseDao().getSqlMapClientTemplate().queryForList("getBidObjectsByProjectListProc",map);
??????? List list = (ArrayList) map.get("result");
??????? return list;
}
?
?
Junit测试代码：
?
public void testGetBidObjectsByProjectListProc() {
??????? List<Project> listinput = new ArrayList<Project>();
??????? Project p1 = new Project(900000000000000007l);
??????? Project p2 = new Project(900000000000000001l);
??????? Project p3 = new Project(900000000000000005l);
??????? listinput.add(p1);
??????? listinput.add(p2);
??????? listinput.add(p3);
??????? HashMap parameters = new HashMap();
??????? parameters.put("projects", listinput);
??????? List list = null;
??????? try {
??????????? list = projectMgr.getBidObjectsByProjectListProc(parameters);
??????? } catch (Exception e) {
??????????? e.printStackTrace();
??????? }
??????? System.out.println("list.size() = " + list.size());
??? }
?
?
今天项目中遇到才去研究，现贴如此，希望对有同样需求的同行有所帮助。
<property name="basePDao">