java操作Oracle存储过程,以及生成Excel报表

引子
   在企业应用系统中,经常会要要求生成报表。
   偶公司向来生成报表的任务都交由给数据库组来完成
   不巧,新项目上线 要求鄙人要完成一个展现在浏览器下的报表
   鄙人不太会用什么报表工具,也不是很复杂的报表 只需要按要求抓起数据
   用HTML或者Excel显示
  
  
方案：
   定时触发器 Spring自带的quartz
   Oracle存储过程执行我要抓取的数据
   POI显示成报表形式

目的：
   作为一个开发手记,积累工作经验

   先从数据生成开始,我吧要抓起的数据用新建的表来存放
   代码可能会贴很多

  

create or replace procedure outcall_report_pro
(
 /*前置时间 字符格式*/
 --PREFIX_YMD IN VARCHAR2, -- 前置 处理年月日 2009-06-25
 /*后置时间 字符格式*/
 --POSTPOSITION_YMD IN VARCHAR2
 /*某天的时间*/
 INPUT_YMD IN VARCHAR2 -- 条件 处理年月日
 /*无返回值*/
)

is
     w_RESULT  NUMBER;
           
     /*定义一个游标存放读取的记录*/
    CURSOR cur_read_result
    
    IS
       SELECT
               MAINTABLE.cscalltime
             , MAINTABLE.cscallresult
             , MAINTABLE.cscallrtcause
             , MAINTABLE.csoperatorid
             , MAINTABLE.userid
             , MAINTABLE.ascalltime
             , MAINTABLE.ascallresult
             , MAINTABLE.ascallrtcause
             , MAINTABLE.asoperatorid
             , MAINTABLE.orderid
             , ASALLOT.asallotat
             , CSALLOT.csallotat
       FROM
            (
              select 
                       cstable.calltime as cscalltime
                     , cstable.callresult as cscallresult 
                     , cstable.callrtcause as cscallrtcause
                     , cstable.operatorid as csoperatorid 
                     , cstable.userid
                     , astable.calltime as ascalltime 
                     , astable.callresult as ascallresult 
                     , astable.callrtcause as ascallrtcause 
                     , astable.operatorid as asoperatorid
                     , astable.orderid
              from (
                     select 
                              cst.calltime 
                            , cst.callresult
                            , cst.callrtcause 
                            , cst.operatorid
                            , cst.userid
                            , cst.orderid
                     from 
                            erp2_usercommunicate_log cst 
                     where 
                               cst.callway = 1 
                           and cst.callobject = 1 
                           and to_char(cst.calltime,'yyyy-MM-dd')=INPUT_YMD 
                           order by cst.calltime desc
              ) cstable, 
                  (         
                    select 
                             cst.calltime
                           , cst.callresult 
                           , cst.callrtcause 
                           , cst.operatorid 
                           , cst.userid
                           , cst.orderid
                    from 
                           erp2_usercommunicate_log cst
                    where 
                          (orderid,calltime) 
                          in  
                              ( 
                                select orderid,max(calltime)
                                from 
                                     erp2_usercommunicate_log cst 
                                     where 
                                               cst.callway = 1 
                                           and cst.callobject = 2 
                                           and to_char(cst.calltime,'yyyy-MM-dd')=INPUT_YMD 
                                           group by orderid
                                        )                
                               ) astable 
                 where
                       cstable.orderid = astable.orderid(+)
            ) MAINTABLE,
            (
               select 
                        allot.allottime as asallotat
                      , allot.userid as asuserid
               from erp2_userallot_log allot
               where 
                     (userid, allottime) in 
                     (
                       select userid, min(allottime) 
                       from erp2_userallot_log  
                       where     state=2 
                             and to_char(allottime,'yyyy-MM-dd')=INPUT_YMD 
                             group by userid
                     )
            ) ASALLOT,
            (
              select 
                       allot.allottime as csallotat
                     , allot.userid as csuserid
              from erp2_userallot_log allot
              where 
                    (userid, allottime) in 
                    (
                      select userid, min(allottime) 
                      from erp2_userallot_log  
                      where     state=1
                            and to_char(allottime,'yyyy-MM-dd')=INPUT_YMD
                            group by userid
                    )
            ) CSALLOT
            where 
                     MAINTABLE.userid = ASALLOT.asuserid(+)
                and  MAINTABLE.userid = CSALLOT.csuserid(+)
       ;
       
       

    /* 插入记录函数 */
    FUNCTION fnc_record -- 返回0正常，异常为-1
    (
                p_csallotat IN DATE    
             ,  p_cscalltime IN DATE
             ,  p_cscallresult IN NUMBER
             ,  p_cscallresultcause IN VARCHAR2
             ,  p_asallotat IN DATE
             ,  p_ascalltime IN DATE
             ,  p_ascallresult IN NUMBER
             ,  p_ascallresultcause IN VARCHAR2
             ,  p_userid IN NUMBER
             ,  p_asoperatorid IN NUMBER
             ,  p_csoperatorid IN NUMBER
             ,  p_orderid IN NUMBER
             
    ) RETURN NUMBER
    IS
      BEGIN
      
      INSERT INTO
             ERP2_OUTCALL_REPORT
      (
                ID
             ,  REPORTAT
             ,  CSALLOTAT
             ,  CSCALLAT
             ,  CSCALLRESULT
             ,  CSCALLRESULTCAUSE
             ,  ASCALLAT
             ,  ASCALLRESULT
             ,  ASCALLRESULTCAUSE
             ,  ASALLOTAT
             ,  USERID
             ,  ASID
             ,  CSID
             ,  ORDERID
      )
      values 
      (
                seq_erp2_outcall_report_id.nextval
             ,  sysdate
             ,  p_csallotat
             ,  p_cscalltime
             ,  p_cscallresult
             ,  p_cscallresultcause
             ,  p_ascalltime
             ,  p_ascallresult
             ,  p_ascallresultcause
             ,  p_asallotat
             ,  p_userid
             ,  p_asoperatorid
             ,  p_csoperatorid
             ,  p_orderid
      );
      
      RETURN 0;
      EXCEPTION
          WHEN OTHERS THEN
      RETURN -1;
    END fnc_record;
       
    
    /*执行函数*/
    FUNCTION fnc_excute 
    RETURN NUMBER
    IS
      w_RES       NUMBER := 0; 
      
      BEGIN
      IF w_RES = 0 THEN
            FOR rec IN cur_read_result
             LOOP
                w_RES := fnc_record(rec.csallotat
                        , rec.cscalltime
                        , rec.cscallresult
                        , rec.cscallrtcause
                        , rec.asallotat
                        , rec.ascalltime
                        , rec.ascallresult
                        , rec.ascallrtcause
                        , rec.userid
                        , rec.asoperatorid
                        , rec.csoperatorid
                        , rec.orderid);
                         
              EXIT WHEN w_RES != 0;
              END LOOP;
      END IF;
      
      RETURN w_RES;
      EXCEPTION
          WHEN OTHERS THEN
      RETURN -1;
      
    END fnc_excute;
    
begin
     
     w_RESULT := 0;   
         IF w_RESULT = 0 THEN  
            w_RESULT := fnc_excute;   
         END IF;   
            
         IF w_RESULT != 0 THEN  
                ROLLBACK;     
         END IF;   
        
     EXCEPTION   
        WHEN OTHERS THEN  
            ROLLBACK;
   
end outcall_report_pro;

   

   然后就是在ssh架构下执行 调用存储过程(没用过的可要看好了 啊)
  

/**
 * 
 * @version 创建时间：2009-6-25 类说明：外呼报表-电话沟通详单查询
 *
 */
@SuppressWarnings({"serial","unused","unchecked"})
public class OutCallReportManageImpl {
	/*
	 * 打印日志
	 */
	Log log = LogFactory.getLog(OutCallReportManageImpl.class);
	
	private StatisticsDao statisticsdao;

	public StatisticsDao getStatisticsdao() {
		return statisticsdao;
	}
	
	public void setStatisticsdao(StatisticsDao statisticsdao) {
		this.statisticsdao = statisticsdao;
	}
	
	//--------------------- excute core method -----------------------
	public void processOutCallReport(){
		 Connection conn = null; 
	     CallableStatement proc = null;
	     
        try {    
        	conn =  statisticsdao.getHibernateTemplate().
        		getSessionFactory().getCurrentSession().connection();
        	
            proc = conn.prepareCall("{ call outcall_report_pro(?) }");   
            proc.setString(1, getTime("yyyy-MM-dd"));
            proc.execute();  
            
            log.info("....Procedure调用成功！");   
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                    if (null != proc) { 
                        proc.close();
                    }   
                    if (null != conn) { 
                        conn.close(); 
                    }
            } catch (Exception ex) {   
            	
            }   
        }   

	}
}

   

   注意这里的类名字定义的了吗？ 为什么为定义成*ManageImpl后缀呢？
    因为鄙人在开发中直接用到spring管理的session
   用普通的POJO的时候 会报不在事务内招不到相应的session
   请看
   

     //.....
     conn =  statisticsdao.getHibernateTemplate().
        		getSessionFactory().getCurrentSession().connection();
    

    和spring 中用aop配置事务的方式
    

   <xml?>
   <aop:config proxy-target-class="true">
      <aop:pointcut id="ServiceOperation" 
      expression="execution(* com.chinadim.erp2..*ManageImpl.*(..))"/>
      <aop:advisor advice-ref="txAdvice" 
      pointcut-ref="ServiceOperation"/>
    </aop:config>
    </xml>
    

    其中执行存储过程的那个方法
     也被定义在事务执行方法内... 省

     下面就是如何在伸手不见五指的 黑夜 来一触即发
      很显然要配置 quartz.xml
  

<?xml version="1.0" encoding="UTF-8"?>
<!-- 指定Spring配置文件的Schema信息 -->
<beans xmlns="http://www.springframework.org/schema/beans"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	   xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

<!-- add by sam 2009-06-25 电话沟通详单报表 -->
	<bean id="outCallReport" class="com.chinadim.erp2.datacollection.OutCallReportManageImpl">
		<property name="statisticsdao">
			<ref bean="statisticsdao"/>
		</property>	
	</bean>		
	
<!-- 指定自启动的目标类和目标方法 -->
	<!-- add by sam 2009-06-25 电话详单沟通查询报表 -->	
	<bean id="Target_outCallReport" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
		<!--指定目标类-->
	    <property name="targetObject">
		  	<ref bean="outCallReport"/>
		</property>
		
		<!--指定目标类方法-->
		<property name="targetMethod">
			<value>processOutCallReport</value>
		</property>
	</bean>

<!-- add by sam 2009-06-25 电话详单查询报表工作JOB -->
	<bean id="Trigger_outCallReport" class="org.springframework.scheduling.quartz.CronTriggerBean">
		<property name="jobDetail">
			<ref bean="Target_outCallReport"/>
		</property>
		<property name="cronExpression">
			<value>0 48 15 * * ?</value>
		</property>
	</bean>

	<!-- add by sam 2009-06-25 电话详单查询报表 -->
	<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
		<property name="autoStartup">   
            <value>true</value>   
        </property>   
		<property name="triggers">
			<list>
				<ref local="Trigger_outCallReport"/>
			</list>
		</property>
	</bean>


</beans>

后面是生成报表的部分
细节 省...
用apache POI实现的导出报表

/**
 * 
 * @version 创建时间：2009-5-6 类说明：利用开源组件POI3.0.2动态导出EXCEL文档
 * 
 * @param <T> 应用泛型，代表任意一个符合javabean风格的类
 */
@SuppressWarnings({"serial","unused","unchecked"})
public abstract class ExportExcel<T> {
	
	public void exportExcel(String title, Collection<T> dataset, OutputStream out) {
		exportExcel(title, null, dataset, out, "yyyy-MM-dd");
	}
	
    public void exportExcel(Collection<T> dataset, OutputStream out) {
        exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");
    }

    public void exportExcel(String title, String[] headers, Collection<T> dataset,
		   OutputStream out) {
	     exportExcel(title, headers, dataset, out, "yyyy-MM-dd");
    }

    public void exportExcel(String[] headers, Collection<T> dataset,
         OutputStream out) {
         exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
    }
    
	public void exportExcel(String[] headers, Collection<T> dataset,
	       OutputStream out, String pattern) {
		exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);
	}

	   /**
	    * 这是一个通用的方法，利用了JAVA的反射机制，
	    * 可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	    * 
	    * @param title
	    *            表格标题名
	    * @param headers
	    *            表格属性列名数组
	    * @param dataset
	    *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	    *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	    * @param out
	    *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	    * @param pattern
	    *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	    */
	   public void exportExcel(String title, String[] headers,
			   Collection<T> dataset, OutputStream out, String pattern) {

	      // 声明一个工作薄
	      HSSFWorkbook workbook = new HSSFWorkbook();
	      
	      // 生成一个表格
	      HSSFSheet sheet = workbook.createSheet(title);
	      
	      // 设置表格默认列宽度为15个字节
	      sheet.setDefaultColumnWidth((short) 15);
	      
	      // 生成一个样式
	      HSSFCellStyle style = workbook.createCellStyle();
	      
	      // 设置这些样式
	      style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	      // 生成一个字体
	      HSSFFont font = workbook.createFont();
	      font.setColor(HSSFColor.VIOLET.index);
	      font.setFontHeightInPoints((short) 12);
	      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	      
	      // 把字体应用到当前的样式
	      style.setFont(font);
	      
	      // 生成并设置另一个样式
	      HSSFCellStyle style2 = workbook.createCellStyle();
	      style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
	      style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	      style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	      style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
	      style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
	      style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	      style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

	      // 生成另一个字体
	      HSSFFont font2 = workbook.createFont();
	      font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

	      // 把字体应用到当前的样式
	      style2.setFont(font2);

	      // 声明一个画图的顶级管理器
	      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

	      // 定义注释的大小和位置,详见文档
	      HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));

	      // 设置注释内容
	      comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));

	      // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
	      comment.setAuthor("leno");

	      //产生表格标题行
	      HSSFRow row = sheet.createRow(0);

	      for (short i = 0; i < headers.length; i++) {
	         HSSFCell cell = row.createCell(i);
	         cell.setCellStyle(style);
	         HSSFRichTextString text = new HSSFRichTextString(headers[i]);
	         cell.setCellValue(text);
	      }

	      //这里可以定制的调用被子类实例的抽象方法
	      createRowsAndCells(dataset, sheet, 
		row, style2, headers.length, pattern);
	      	      
	      try {
	         workbook.write(out);
	      } catch (IOException e) {
	         e.printStackTrace();
	      }
	   }

	   /**
	    * 抽象方法 子类去实现
	    */
	   public abstract void createRowsAndCells(Collection<T> dataset, HSSFSheet sheet, 
			   HSSFRow row, HSSFCellStyle style, int columns, String pattern);
	 
	   /**
	    * 测试主函数
	    * @param args
	    */
	   public static void main(String[] args) {

	   }


}

以上定义的抽象类 定义导出报表的样式 格式颜色等
子类要实现的只是去继承即可....

/**
 * 
 * @version 创建时间：2009-6-25 类说明：
 *
 */
@SuppressWarnings({"serial","unused","unchecked"})
public class ExportExcelOutCallReport extends ExportExcel<OutCallReportWrap>{

	@Override
	public void createRowsAndCells(Collection<OutCallReportWrap> dataset,
			HSSFSheet sheet, HSSFRow row, HSSFCellStyle style, int columns,
			String pattern) {
		
		int index = 0;
		for (OutCallReportWrap oe:dataset){
			index ++;
			//创建行
			row = sheet.createRow(index);
			for (int i=0; i < columns; i++){
				//创建列 并设置样式
				HSSFCell cell = row.createCell((short)i);
				cell.setCellStyle(style);
				
				switch (i) {
					case 0: //第一列:省份
						String province = oe.getOutCallReportEntity().getUserObject().getProvince();
						cell.setCellValue(new HSSFRichTextString(province));
						break;
					case 1: //第二列:城市
						String city = oe.getOutCallReportEntity().getUserObject().getCity();
						cell.setCellValue(new HSSFRichTextString(city));
						break;
					case 2: //第三列:导入时间
						if (null != oe.getOutCallReportEntity().getUserObject().getImporttime()){
							String importTime = getTime(oe.getOutCallReportEntity().
									getUserObject().getImporttime(), "yyyy-MM-dd");
							cell.setCellValue(new HSSFRichTextString(importTime));
						}
						break;
					case 3: //第四列:电话号码
						String mobile = oe.getOutCallReportEntity().getUserObject().getMobile();
						cell.setCellValue(new HSSFRichTextString(mobile));
						break;
					case 4: //第五列:下单分配时间
						if (null != oe.getOutCallReportEntity().getCsAllotAt()){
							String csAllotTime = getTime(oe.getOutCallReportEntity().getCsAllotAt(),"yyyy-MM-dd");
							cell.setCellValue(new HSSFRichTextString(csAllotTime));
						}
						break;
					case 5: //第六列:下单拨打时间
						if (null != oe.getOutCallReportEntity().getCsCallAt()){
							String csCallTime = getTime(oe.getOutCallReportEntity().getCsCallAt(),"yyyy-MM-dd");
							cell.setCellValue(new HSSFRichTextString(csCallTime));
						}
						break;
					case 6: //第七列:下单拨打结果
						String csCallResult = UsercommunicateLogEntity.
							getContactResultTreeMapForCallResult(false).get(oe.getOutCallReportEntity().getCsCallResult());
						cell.setCellValue(new HSSFRichTextString(csCallResult));
						break;
					case 7: //第八列:下单拨打结果原因
						String csCallResultCause = oe.getOutCallReportEntity().getCsCallResultCause();
						cell.setCellValue(new HSSFRichTextString(csCallResultCause));
						break;
					case 8: //第九列:下单坐席编号
						String csSeatingNo = oe.getOutCallReportEntity().getCsOperator().getSeatingNo();
						cell.setCellValue(new HSSFRichTextString(csSeatingNo));
						break;
					case 9: //审单分配时间
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String asAllotTime = getTime(oe.getOutCallReportEntity().getAsAllotAt(),"yyyy-MM-dd");
							cell.setCellValue(new HSSFRichTextString(asAllotTime));
						}
						break;
					case 10: //审单拨打时间
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String asCallTime = getTime(oe.getOutCallReportEntity().getAsCallAt(),"yyyy-MM-dd");
							cell.setCellValue(new HSSFRichTextString(asCallTime));
						}
						break;
					case 11: //审单拨打结果
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String asCallResult = UsercommunicateLogEntity.
							getContactResultTreeMapForCallResult(false).get(oe.getOutCallReportEntity().getAsCallResult());
							cell.setCellValue(new HSSFRichTextString(asCallResult));
						}
						break;
					case 12: //审单拨打结果原因
						if (null != oe.getOutCallReportEntity().getOrderObject()){
								
							String asCallResultCause = oe.getOutCallReportEntity().getAsCallResultCause();
							cell.setCellValue(new HSSFRichTextString(asCallResultCause));
						}
						break;
					case 13: //审单坐席编号
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String asSeatingNo = oe.getOutCallReportEntity().getAsOperator().getSeatingNo();
							cell.setCellValue(new HSSFRichTextString(asSeatingNo));
						}
						break;	
					case 14: //订单编号
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String orderNo = oe.getOutCallReportEntity().getOrderObject().getOrderNo();
							cell.setCellValue(new HSSFRichTextString(orderNo));
						}
						break;	
						
					case 15: //订单状态
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String orderState = OrdersEntity.getContactResultTreeMapForOrderState(false).
								get(oe.getOutCallReportEntity().getOrderObject().getOrderState());
							cell.setCellValue(new HSSFRichTextString(orderState));
						}
						break;	
						
					case 16: //套餐名称
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String packageNames = oe.getPackageNames();
							cell.setCellValue(new HSSFRichTextString(packageNames));
						}
						break;	
					case 17: //订单备注
						if (null != oe.getOutCallReportEntity().getOrderObject()){
							String orderRemark = oe.getOutCallReportEntity().getOrderObject().getRemark();
							cell.setCellValue(new HSSFRichTextString(orderRemark));
						}
						break;	
				}
			}
		}
	}

}

  贴了很多代码 开始遇到过些错误
  都慢慢解决了
 
  个人感觉 在不熟悉用报表工具开发的时候 写写存储过程还是蛮方便的抓起数据
  在效益上也有保障
 
  // -----------------------
  下一步 J2EE报表开发 -->
  朋友 一起努力吧