最近做个项目有很多的报表统计,涉及到几十张表抽取数据,形成一系列统计数据。
举个例子,比如统计一个单位不同职务级别女人数量、少数民族数量、30岁以上人员数量,40以上人员数量等。如:
董事长:0 0 0 1
中层领导:2 2 3 8
职员:20 40 80 200
刚开始想办法拼成了一个超级长的sql来做,但是最后长度超过vachar2的最大长度了,虽然有其他办法解决,虽然最大sql看起来很美好,但是想想后期的优化和维护,还是决定重新来规划。中间的过程就不说了,最终确定先定义一行数据存number型object(类似一维数组),形式如(0,0,1)。然后放到二维数组里。闲言少叙,如下定义:
create or replace
TYPE C_ROW IS OBJECT(
COUNT_SEX NUMBER, --性别女统计
COUNT_NATION NUMBER, --少数民族统计
COUNT_30AGE NUMBER --30岁统计
);
create or replace
TYPE C_ROW_CLM IS TABLE OF C_ROW;
关于以上的语法我就不解释了。 C_ROW_CLM 就是二维数组。
一个简单的存储过程如下:就是往二维数组放了三列。
PROCEDURE COUNT_TEST(C_VALUE OUT C_ROW_CLM) AS
BEGIN
--初始化二维数组,存放格式如:(0,1,2,3),(0,0,2,3)
C_VALUE:=C_ROW_CLM();
C_VALUE.extend;
C_VALUE(1) := C_ROW(0,0,0);
C_VALUE.extend;
C_VALUE(2) := C_ROW(1,2,3);
C_VALUE.extend;
C_VALUE(3) := C_ROW(7,8,9);
NULL;
END COUNT_TEST;
以上存储过程生成而为数据已经有了,我们有一个out输出。现在就看mybatis如何实现了。
目前能查到的文章大部分是关于java如何自定义类型到数据库的举例,官方文档也是,找到一篇文章很不容易,不过最终还是找到了。现在回头看很简单,由于我们自己定义的二维数组java不认识,需要我们自定义类型做数据转换,mybatis提供了TypeHandler接口,只要实现它就可以。里面有几个方法,官方例子主要介绍了setParameter、getResult(ResultSet arg0, int arg1)两个方法,这两个方法共同完成的是java对数据库的操作以及数据类型转换,而我们的存储过程调用是不用他们的,我们用的是getResult(CallableStatement cs, int arg1)这个方法,在这里实现数据类型转换即可。具体事例如下:
package com.broadengate.handler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import com.XX.bean.CountRefMgrPerson;
/**
* 自定义Handler
*/
public class CRMPTypeHandler implements TypeHandler<Object> {
public Object getResult(ResultSet arg0, String arg1) throws SQLException {
// TODO Auto-generated method stub
return null;
}
public Object getResult(ResultSet arg0, int arg1) throws SQLException {
// TODO Auto-generated method stub
return null;
}
/**
* 调用存储过程实现该方法即可。
* @param cs 返回结果
* @param arg1 返回索引
*/
public Object getResult(CallableStatement cs, int arg1)
throws SQLException {
// cs.getConnection();
// 此处是excel y方向数据
String names[] ={"董事长","中层领导","职员"};
// 遍历y方向数据
int i = 0;
// 定义结果集, XXDTO是我们自己定义的bean,用来与统计数量一行数据一一对应。
List< XXDTO > dtos = new ArrayList< XXDTO >();
ResultSet rs = cs.getArray(arg1).getResultSet();
while (rs.next()) {
Datum[] data = ((STRUCT)rs.getObject(2)).getOracleAttributes();
XXDTO dto = new CountRefMgrPerson();
// key_value赋值
dto.setDataValue(names[i]);
i ++;
// 以下操作取得数据库中数据值赋给相应的bean。
dto.setSex(data[0].intValue());
dto.setNation(data[1].intValue());
dto.setDegreeCodeDoctor(data[2].intValue());
// 获得list
dtos.add(dto);
}
return dtos;
}
public void setParameter(PreparedStatement ps, int arg1, Object arg2,
JdbcType arg3) throws SQLException {
// TODO Auto-generated method stub
}
}
package com.xx.bean;
public class CountRefMgrPerson extends BaseBean {
private Integer id;// null
private String dataValue;// 纵列数值( {"董事长","中层领导","职员"};等值 )
private Integer countNation;// 民族(少数民族)数量
private Integer countSex;// 性别(女)数量
<pre name="code" class="html"><span style="white-space:pre"> </span>private Integer count30;// age数量
get、set方法略}
这样我们就完成了数据转换的操作,剩下的工作就是配置mybatis了。为了找到这个方法,憋屈了一天,国内几乎没啥资料,老外的帖子也几乎翻遍了,后来终于在一个老外的回复中找到了答案。不啰嗦了,直接上答案:
<resultMap type="com.broadengate.bean.CountRefMgrPerson" id="clist">
<!-- 职级-->
<result property="sex" column="COUNT_SEX"/>
<!-- 民族-->
<result property="nation" column="COUNT_NATION" />
<!-- 性别-->
<result property="degreeCodeDoctor" column="COUNT_BOSHI"/>
</resultMap>
<!-- 存储过程调用-->
<select id="queryByList" statementType="CALLABLE" parameterType="Object">
{call TEST.COUNT_TEST(
#{result ,mode=OUT, javaType=java.util.List,jdbcType=ARRAY,jdbcTypeName=C_ROW_CLM, resultMap=clist,typeHandler=com.xx.handler.CRMPTypeHandler}
)}
</select>
相信上面的配置,用mybatis的人都知道,这里我只强调两点,一是 typeHandler=com.xx.handler.CRMPTypeHandler 需要指定我们自己的自定义类型,而是必须加 jdbcTypeName,这个name坑爹啊,让我找了很长时间, 其实就是我们out的那个类型C_ROW_CLM。值得一提的是,不要把一维数组和二维数组定义在包体内,调用存储过程是找不到的。
对了顺便放一下,我接受out参数的类。
import java.util.List;
import com.xx.bean.XXDTO;
public class XXModel {
private List< XXDTO > result; // 调用存储过程返回的结果集
public List< XXDTO > getResult() {
return result;
}
public void setResult(List< XXDTO > result) {
this.result = result;
}
}
类里的result与mybatis配置里的返回结果result对应。
ok了。