最近有个问题一直困扰着，希望各位能给予帮助。
先贴代码：
1
2
3 jdbcType="Object" mode="IN" />
4
5 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.object.entity.ObjectTypeHandler" />
6
7 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.tag.entity.TagsTypeHandler" />
8
9 jdbcType="VARCHAR" mode="OUT" />
10
11 jdbcType="VARCHAR" mode="OUT" />
12
13
14
15 {call
16 PKG_USER.PRC_USER_INDEXVIEW(?,?,?,?,?)}
17
这个ibatis的配置文件.
在网上查了很久，说是用typeHandler和jdbcType来解决解决oracle复合类型，
1 package com.diy.tag.entity;
2
3 import java.sql.CallableStatement;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.List;
9
10 import oracle.sql.Datum;
11 import oracle.sql.STRUCT;
12
13 import com.ibatis.sqlmap.engine.type.TypeHandler;
14
15 public class TagsTypeHandler implements TypeHandler {
16
17 /**
18 * @Description: 这个方法重点
19 * @param cs
20 * @param arg1
21 * @throws SQLException
22 /
23 public java.lang.Object getResult(CallableStatement cs, int arg1)
24 throws SQLException {
25 List list = new ArrayList();
26 ResultSet rs = cs.getArray(arg1).getResultSet();
27 while (rs.next()) {
28 Datum[] data = ((STRUCT)rs.getObject(2)).getOracleAttributes();
29 Tag tag = new Tag();
30 if (data[0] != null) {
31 tag.setTagid(new Long(data[0].getBytes().toString()));
32 }
33 list.add(tag);
34 }
35 return list;
36 }
37
38 @Override
39 public boolean equals(java.lang.Object arg0, String arg1) {
40 // TODO Auto-generated method stub
41 return false;
42 }
43
44 @Override
45 public java.lang.Object getResult(ResultSet arg0, String arg1)
46 throws SQLException {
47 // TODO Auto-generated method stub
48 return null;
49 }
50
51 @Override
52 public java.lang.Object getResult(ResultSet arg0, int arg1)
53 throws SQLException {
54 // TODO Auto-generated method stub
55 return null;
56 }
57
58
59
60 @Override
61 public void setParameter(PreparedStatement arg0, int arg1,
62 java.lang.Object arg2, String arg3) throws SQLException {
63 // TODO Auto-generated method stub
64
65 }
66
67 @Override
68 public java.lang.Object valueOf(String arg0) {
69 // TODO Auto-generated method stub
70 return null;
71 }
72
73 }
这个是java代码，其中一个handler处理类
1 --定义 object表 对象
2 TYPE object_arr IS TABLE OF OBJECT%ROWTYPE INDEX BY BINARY_INTEGER;
3
4 --定义 tag index_by表
5 TYPE table_tag IS TABLE OF TAGS_INFO INDEX BY BINARY_INTEGER;
这个定义的oracle复合类型
1 PROCEDURE PRC_USER_INDEXVIEW(PRM_USERID IN VARCHAR2,
2 PRM_OBJECTS OUT PKG_COMM.OBJECT_ARR,
3 PRM_TAGS OUT PKG_COMM.table_tag,
4 PRM_APPCODE OUT VARCHAR2,
5 PRM_ERRMSG OUT VARCHAR2) IS
6 N_FLAG NUMBER;
7 VAR_FIRSTTAG VARCHAR2(100);
8 VAR_DUSERID VARCHAR2(100);
9 --用户兴趣标签
10 CURSOR CUR_USERTAG IS
11 SELECT C.TAGID, C.NAME
12 FROM USERSDETIAL A, TAGRELATION B, TAG C
13 WHERE A.DUSERSID = B.DUSERSID
14 AND B.TAGID = C.TAGID
15 AND A.DUSERSID = VAR_DUSERID;
16 --公共兴趣标签
17 CURSOR CUR_USERPUB IS
18 SELECT T.
19 FROM (SELECT ROWNUM AS RNUM,
20 COUNT(A.DUSERSID) AS CNUM,
21 B.TAGID,
22 B.NAME
23 FROM TAGRELATION A, TAG B
24 WHERE A.TAGID = B.TAGID
25 GROUP BY A.TAGID) T
26 WHERE RNUM <= 8
27 ORDER BY T.CNUM DESC;
28 --object
29 CURSOR CUR_OBJ(VAR_TAGID VARCHAR2) IS
30 SELECT ROWNUM AS RN, A.*
31 FROM OBJECT A
32 WHERE trim(A.TAGID) = VAR_TAGID
33 AND ROWNUM < 30;
34
35 REC_USERTAG CUR_USERTAG%ROWTYPE;
36 REC_USERPUB CUR_USERPUB%ROWTYPE;
37 REC_OBJ OBJECT%ROWTYPE;
38 BEGIN
39 PRM_APPCODE := PKG_COMM.DEF_OK;
40 PRM_ERRMSG := '';
41
42 IF PRM_USERID IS NULL THEN
43 PRM_APPCODE := PKG_COMM.DEF_ERR;
44 PRM_ERRMSG := '参数未定义';
45 RETURN;
46 END IF;
47 --用户详细ID是否存在
48 SELECT B.DUSERSID
49 INTO VAR_DUSERID
50 FROM USERS A, USERSDETIAL B
51 WHERE A.USERID = B.USERSID
52 AND A.USERID = PRM_USERID;
53 IF VAR_DUSERID IS NULL THEN
54 PRM_APPCODE := PKG_COMM.DEF_ERR;
55 PRM_ERRMSG := '参数无效';
56 RETURN;
57 END IF;
58 --1.判断是否为有效用户
59 SELECT NVL(A.FLAG, 1)
60 INTO N_FLAG
61 FROM USERS A, USERSDETIAL B
62 WHERE A.Userid = B.USERSID
63 AND B.DUSERSID = VAR_DUSERID;
64
65 IF N_FLAG = 1 THEN
66 PRM_APPCODE := PKG_COMM.DEF_ERR;
67 PRM_ERRMSG := '用户已被禁止登录';
68 RETURN;
69 END IF;
70
71 --2.判断用户是否有兴趣tag
72
73 FOR REC_USERTAG IN CUR_USERTAG LOOP
74
75 IF CUR_USERTAG%ROWCOUNT = 0 THEN
76 --获取公共兴趣游标
77 FOR REC_USERPUB IN CUR_USERPUB LOOP
78 IF CUR_USERPUB%ROWCOUNT = 1 THEN
79 VAR_FIRSTTAG := REC_USERPUB.TAGID;
80 END IF;
81 PRM_TAGS(CUR_USERPUB%ROWCOUNT).TAGID := REC_USERPUB.TAGID;
82 PRM_TAGS(CUR_USERPUB%ROWCOUNT).TAGNAME := REC_USERPUB.NAME;
83 END LOOP;
84 ELSIF CUR_USERTAG%ROWCOUNT = 1 THEN
85 VAR_FIRSTTAG := REC_USERTAG.TAGID;
86 END IF;
87 PRM_TAGS(CUR_USERTAG%ROWCOUNT).TAGID := REC_USERTAG.TAGID;
88 PRM_TAGS(CUR_USERTAG%ROWCOUNT).TAGNAME := REC_USERTAG.NAME;
89 END LOOP;
90
91 IF PRM_TAGS.count <> 0 THEN
92 --3. 取出object
93 FOR REC_OBJ IN CUR_OBJ(VAR_FIRSTTAG) LOOP
94 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).OWNERID := REC_OBJ.OWNERID;
95 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).OBJECTID := REC_OBJ.OBJECTID;
96 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).DBUSID := REC_OBJ.DBUSID;
97 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).DUSERSID := REC_OBJ.DUSERSID;
98 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).TAGID := REC_OBJ.TAGID;
99 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).LOVENUM := REC_OBJ.LOVENUM;
100 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).INRUDUCTION := REC_OBJ.INRUDUCTION;
101 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).CATAGROY := REC_OBJ.CATAGROY;
102 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).Imagepath := REC_OBJ.Imagepath;
103
104 END LOOP;
105 END IF;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 PRM_APPCODE := PKG_COMM.DEF_ERR;
110 PRM_ERRMSG := '获取主界面数据失败' || '错误原因：' || PRM_ERRMSG || '-' || SQLERRM ||
111 '错误行数：' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();
112 END;
这个是存储过程的实现,过程没有问题，plsql调试正常！！！
现在我可以确定问题在 1.ibatis xml文件中jdbcType 和typeHandler的配置问题
1
2 5 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.object.entity.ObjectTypeHandler" />
3 6
4 7 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.tag.entity.TagsTypeHandler" />
2.java typeHandler类的返回值
还有一个附带的问题：index by 表不能在数据库中存储，而嵌套表可以存储在数据库中。是不是说index by 表 像java 方法中声明的变量，方法结束，其变量的生命周期就结束了？？？
谢谢！！！
