java 程序调用Oracle Function

本文出自 “古道西风 ” 博客，请务必保留此出处http://hzw2312.blog.51cto.com/2590340/748363

    package com.boxun.crm.util;   
       
    import java.sql.CallableStatement;   
       
    import java.sql.ResultSet;   
    import java.sql.ResultSetMetaData;   
    import java.sql.SQLException;   
    import java.sql.Statement;   
    import java.util.ArrayList;   
    import java.util.HashMap;   
    import java.util.List;   
    import java.util.Map;   
    import org.hibernate.HibernateException;   
    import org.hibernate.Query;   
    import org.hibernate.Session;   
    import org.hibernate.SessionFactory;   
    import org.hibernate.Transaction;   
    import org.hibernate.cfg.Configuration;   
       
    public class DAOUtil {   
           
        private static DAOUtil instance = null;   
        private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";   
        private static final ThreadLocal threadLocal = new ThreadLocal();   
        private static final Configuration cfg = new Configuration();   
        private static SessionFactory sessionFactory = null;   
           
           
        public synchronized static DAOUtil getInstance()   
        {   
            if (instance == null) {   
                instance = new DAOUtil();   
                try {   
                    cfg.configure(CONFIG_FILE_LOCATION);   
                    sessionFactory = cfg.buildSessionFactory();   
                } catch (HibernateException e) {   
                    e.printStackTrace();   
                }   
            }   
            return instance;           
        }   
           
        public Session getSession() {   
            Session session = (Session) threadLocal.get();   
            try {   
                if ((session == null) || (!session.isOpen())) {   
                    session = sessionFactory.openSession();   
                    threadLocal.set(session);   
                }   
            } catch (HibernateException e) {   
                e.printStackTrace();   
            }   
            return session;   
        }   
       
        public void closeSession() {   
            Session session = (Session) threadLocal.get();   
            threadLocal.set(null);   
            if ((session != null) && (session.isOpen())) {   
                session.flush();   
                session.close();   
            }   
        }   
       
        private String getSqlStr(String fname, int size) {   
            StringBuffer sql = new StringBuffer();   
            //String call="{? call pro(?,?,?)}";   
            sql.append("{ ? = call ");   
            sql.append(fname);   
            sql.append("(");   
            for (int i = 0; i < size; i++) {   
                sql.append("?");   
                if (i < size - 1) {   
                    sql.append(",");   
                }   
            }   
            sql.append(") }");   
       
            return sql.toString();   
        }   
       
        private String formatSqlStr(String sql, ArrayList params) {   
            if ((params == null) || (params.size() == 0))   
                return sql;   
            for (int i = 0; i < params.size(); i++) {   
                sql = sql.replaceFirst("//?", String.valueOf(params.get(i)));   
            }   
            return sql;   
        }   
           
        @SuppressWarnings("deprecation")   
        public int executeByFun(Session session, String fname, List params)   
                throws HibernateException, SQLException {   
            String sql = getSqlStr(fname, params.size());   
            int rval = -100;   
            try {   
                if (session.isConnected()) {   
                    CallableStatement call = session.connection().prepareCall(sql);   
                    call.registerOutParameter(1, 4);   
                    int i = 0;   
                    for (int j = 2; i < params.size(); j++) {   
                        call.setObject(j, params.get(i));   
                        i++;   
                    }   
       
                    if (!call.execute()) {   
                        rval = call.getInt(1);   
                    }   
                    call.close();   
                }   
            } catch (Exception ex) {   
                ex.printStackTrace();   
            }   
            return rval;   
        }   
           
        public int executeByFun(String fname, List params)   
                throws HibernateException, SQLException {   
            String sql = getSqlStr(fname, params.size());   
            int rval = -100;   
            try {   
                Session session = DAOUtil.getInstance().getSession();   
                if (session.isConnected()) {   
                    CallableStatement call = session.connection().prepareCall(sql);   
                    call.registerOutParameter(1, 4);   
                    int i = 0;   
                    for (int j = 2; i < params.size(); j++) {   
                        call.setObject(j, params.get(i));   
                        i++;   
                    }   
       
                    if (!call.execute()) {   
                        rval = call.getInt(1);   
                    }   
                    call.close();   
                }   
            } finally {   
                closeSession();   
            }   
            return rval;   
        }   
       
        public double executeByFunDouble(String fname, ArrayList params)   
                throws HibernateException, SQLException {   
            String sql = getSqlStr(fname, params.size());   
            double rval = -100.0D;   
            try {   
                Session session = DAOUtil.getInstance().getSession();   
                if (session.isConnected()) {   
                    CallableStatement call = session.connection().prepareCall(sql);   
                    call.registerOutParameter(1, 8);   
                    int i = 0;   
                    for (int j = 2; i < params.size(); j++) {   
                        call.setObject(j, params.get(i));   
       
                        i++;   
                    }   
       
                    if (!call.execute()) {   
                        rval = call.getDouble(1);   
                    }   
                    call.close();   
                }   
            } finally {   
                closeSession();   
            }   
            return rval;   
        }   
       
        public int executeBySql(String sql, ArrayList params)   
                throws HibernateException, SQLException {   
            Transaction tran = null;   
            try {   
                Session session = DAOUtil.getInstance().getSession();   
                if (session.isConnected()) {   
                    Query query = session.createSQLQuery(sql);   
                    for (int i = 0; i < params.size(); i++)   
                        query.setParameter(i, params.get(i));   
                    tran = session.beginTransaction();   
                    int rval = query.executeUpdate();   
                    tran.commit();   
                    return rval > 0 ? 0 : -100;   
                }   
            } finally {   
                closeSession();   
            }   
            return -100;   
        }   
       
        public double executeBySqlDouble(String sql, ArrayList params)   
                throws HibernateException, SQLException {   
            Transaction tran = null;   
            double rval = 0.0D;   
            List list = null;   
            try {   
                Session session = DAOUtil.getInstance().getSession();   
                if (session.isConnected()) {   
                    Query query = session.createSQLQuery(sql);   
                    for (int i = 0; i < params.size(); i++)   
                        query.setParameter(i, params.get(i));   
                    tran = session.beginTransaction();   
                    list = query.list();   
                    if (list != null && list.size() > 0) {   
                        rval = Double.parseDouble(list.get(0)==null ? 0.0 + ""   
                                : list.get(0) + "");   
                    }   
                    tran.commit();   
                }   
            } finally {   
                closeSession();   
            }   
            return rval;   
        }   
       
        public List getResultByFun(String fname, ArrayList params)   
                throws HibernateException, SQLException {   
            String sql = getSqlStr(fname, params.size());   
            List result = null;   
            try {   
                Session session = DAOUtil.getInstance().getSession();   
                if (session.isConnected()) {   
                    System.out.println(session.connection().prepareCall(sql));   
                    CallableStatement call = session.connection().prepareCall(sql);   
                    call.registerOutParameter(1, -10);   
       
                    for (int i = 0, j = 2; i < params.size(); j++) {   
                        call.setObject(j, params.get(i));   
       
                        i++;   
                    }   
       
                    if (!call.execute()) {   
                        ResultSet rs = (ResultSet) call.getObject(1);   
                        ResultSetMetaData rsmd = rs.getMetaData();   
                        String[] colname = new String[rsmd.getColumnCount()];   
                        for (int j = 1, i = 0; i < colname.length; j++) {   
                            colname[i] = rsmd.getColumnName(j);   
                            i++;   
                        }   
                        result = new ArrayList();   
                        while (rs.next()) {   
                            Map row = new HashMap();   
                            for (int i = 0; i < colname.length; i++)   
                                row.put(colname[i], rs.getObject(colname[i]));   
                            result.add(row);   
                        }   
                        rs.close();   
                    }   
                    call.close();   
                }   
            } finally {   
                closeSession();   
            }   
       
            return result;   
        }   
       
        public String getResultByFunString(String fname, ArrayList params)   
                throws HibernateException, SQLException {   
            String sql = getSqlStr(fname, params.size());   
            String str = "";   
            try {   
                Session session = DAOUtil.getInstance().getSession();   
                if (session.isConnected()) {   
                    CallableStatement call = session.connection().prepareCall(sql);   
                    call.registerOutParameter(1, 12);   
       
                    for (int i = 0, j = 2; i < params.size(); j++) {   
                        call.setObject(j, params.get(i));   
                        i++;   
                    }   
                    if (!call.execute()) {   
                        str = String.valueOf(call.getObject(1));   
                    }   
                    call.close();   
                }   
            } finally {   
                closeSession();   
            }   
       
            return str;   
        }   
       
        public List getResultBySql(String sql, ArrayList params)   
                throws HibernateException, SQLException {   
            List result = null;   
            sql = formatSqlStr(sql, params);   
            try {   
                Session session = DAOUtil.getInstance().getSession();   
                if (session.isConnected()) {   
                    Statement stat = session.connection().createStatement();   
                    ResultSet rs = stat.executeQuery(sql);   
                    ResultSetMetaData rsmd = rs.getMetaData();   
                    String[] colname = new String[rsmd.getColumnCount()];   
                    for (int j = 1, i = 0; i < colname.length; j++) {   
                        colname[i] = rsmd.getColumnName(j);   
                        i++;   
                    }   
       
                    result = new ArrayList();   
                    while (rs.next()) {   
                        Map row = new HashMap();   
                        for (int i = 0; i < colname.length; i++)   
                            row.put(colname[i], rs.getObject(colname[i]));   
                        result.add(row);   
                    }   
                    rs.close();   
                    stat.close();   
                }   
            } finally {   
                closeSession();   
            }   
            return result;   
        }   
    } 

应用程序调用Function：

    /**添加参数**/   
    ArrayList<Object> params = new ArrayList<Object>();   
    if((null !=wer_1 && ""!=wer_1)&&(null !=wer_2 && ""!=wer_2))   
    {       
        /**  
         * 古道西风  
         * 2011-05-23  
         */   
        params.add(tbmeetmgr.getStoptime()); //会议开会日期   
        params.add(tbmeetmgr.getStarttime()); //会议开始时间   
        params.add(tbmeetmgr.getEndtime());//会议结束时间   
        params.add(ecId);//集团编号   
        if(tbmeetmgr.getXunhuairiqi() == null){   
            params.add(0); //循环日期   
        }else{   
            params.add(tbmeetmgr.getXunhuairiqi()); //循环日期   
        }   
        if(tbmeetmgr.getXunhuaimoshi() == null){   
            params.add(0); //循环模式   
        }else{   
            params.add(tbmeetmgr.getXunhuaimoshi()); //循环模式   
        }   
        params.add(tbmeetmgr.getHuiyimoshi()); //会议模式   
    }   
       
    //List<Object> list_Sel_RoomInfo=daoSql.find(sql, params); //原程序代码   
       
    List<Object> list_Sel_RoomInfo = new ArrayList<Object>();;   
    try {   
        list_Sel_RoomInfo = com.boxun.crm.util.DAOUtil.getInstance().getResultByFun   
                ("FUN_tbmeetmgrInfo.pro_sel_tbmeetmgrinfo", params);   
    } catch (HibernateException e) {   
        e.printStackTrace();   
    } catch (SQLException e) {   
        e.printStackTrace();   
    }   
    if(null !=list_Sel_RoomInfo && list_Sel_RoomInfo.size()>0)   
    {   
        list_room_info=new ArrayList<Tbmeetroom>();   
        for(int i=0;i<list_Sel_RoomInfo.size();i++)   
        {   
             Map map=(HashMap)list_Sel_RoomInfo.get(i);   
             Tbmeetroom tbmeetroom=new Tbmeetroom();   
             tbmeetroom.setId(new Long(map.get("ID").toString()));   
             tbmeetroom.setName(new String(map.get("NAME").toString()));   
             list_room_info.add(tbmeetroom);   
        }   
        return list_room_info;             
    }  

Oracle Packages(相当于java中的接口):

    CREATE OR REPLACE PACKAGE FUN_tbmeetmgrInfo IS   
           FUNCTION pro_Sel_tbmeetmgrInfo(v_stoptime varchar2,   
                                          v_strtime varchar2,   
                                          v_endtime varchar2,   
                                          v_ecid number,   
                                          v_xunhuairiqi varchar2,   
                                          v_xunhuaimoshi number,   
                                          v_huiyimoshi number)   
                                          return sys_refcursor;   
    END FUN_tbmeetmgrInfo; 

Oracle Package bodies(相当于java中的实现类):

    CREATE OR REPLACE PACKAGE BODY FUN_tbmeetmgrInfo IS   
           FUNCTION pro_Sel_tbmeetmgrInfo(v_stoptime varchar2,   
                                          v_strtime varchar2,   
                                          v_endtime varchar2,   
                                          v_ecid number,   
                                          v_xunhuairiqi varchar2,   
                                          v_xunhuaimoshi number,   
                                          v_huiyimoshi number)   
                                          return sys_refcursor IS   
                                   TYPE c_time IS REF CURSOR;  --创建游离标记   
              vrec c_time;   
              varInt number;   
              strOverSql varchar2(30);     
              v_months number;   
              out_room sys_refcursor;   
      begin   
        delete timeinfo where 11=1;    
        commit;   
         
        --v_Result.extend;  --赋值前、必须加上   
        --一次性会议   
        if v_huiyimoshi = 1 then   
           open out_room for select id, name from Tbmeetroom  where id in (select id from Tbmeetroom where status = 0    
           and id not in    
           (select huiyishi from tbmeetmgrinfo where to_date(v_strtime,'hh24:mi') between to_date(starttime,'hh24:mi')    
           and to_date(endtime,'hh24:mi')  and to_date(stoptime,'yyyy-MM-dd') = to_date(v_stoptime,'yyyy-MM-dd')    
           and ecid = v_ecid or to_date(v_endtime,'hh24:mi') between to_date(starttime,'hh24:mi')    
           and to_date(endtime,'hh24:mi')  and to_date(stoptime,'yyyy-MM-dd') = to_date(v_stoptime,'yyyy-MM-dd') and ecid = v_ecid )    
           and id not in    
           (select meetroomid from Tbmeetroomlock where to_date(v_stoptime||' '||v_strtime,'yyyy-mm-dd hh24:mi')     
           between to_date(lockstarttime,'yyyy-mm-dd hh24:mi') and to_date(lockendtime,'yyyy-mm-dd hh24:mi')    
           or to_date(v_stoptime||' '||v_endtime,'yyyy-mm-dd hh24:mi')  between to_date(lockstarttime,'yyyy-mm-dd hh24:mi')    
           and to_date(lockendtime,'yyyy-mm-dd hh24:mi')));   
        end if;   
           
       ------------------------------------------------------------------------------------------   
        if v_huiyimoshi = 2 then   
          --单周会议   
          if v_xunhuaimoshi = 1 then   
                select ceil(( to_date(v_stoptime,'yyyy-mm-dd') - next_day(to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')-1, ceil(substr(v_xunhuairiqi,3)))+1  )/7) into varInt from dual;   
                      OPEN vrec for SELECT to_char(next_day(to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')-1,   
                      ceil(substr(v_xunhuairiqi,3)))+1+(rownum-1)*7 , 'yyyy-MM-dd')   
                      from dual connect by rownum<=varInt ;     
                        LOOP     
                          FETCH vrec INTO strOverSql;     
                          exit when vrec%notfound;   
                          dbms_output.put_line('StopTime'||strOverSql);   
                           insert into timeInfo values(strOverSql);   
                           commit;   
                        end loop;   
                        
          end if;   
             
          --每月   
          if v_xunhuaimoshi = 3 then   
          select ceil(months_between(to_date(v_stoptime,'yyyy-mm-dd'),   
                           to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))) into varInt from dual;   
               OPEN vrec for SELECT to_char(add_months(to_date((substr(to_char(sysdate,'yyyy-mm-dd'),1,8))||(substr(v_xunhuairiqi,3,4)),'yyyy-mm-dd'),+(rownum-1)), 'yyyy-mm-dd')   
                  from dual connect by rownum<= ceil(varInt) ;   
                     LOOP     
                      FETCH vrec INTO strOverSql;     
                      exit when vrec%notfound;   
                       dbms_output.put_line('StopTime'||strOverSql);   
                           insert into timeInfo values(strOverSql);   
                           commit;   
                     end loop;               
                    
          end if;   
             
             
          --每季度   
          if v_xunhuaimoshi = 4 then   
          select ceil((to_date(v_stoptime,'yyyy-mm-dd')-to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'))/90)   
                     into varInt from dual;   
          v_months := gettimebytime(ceil(substr(substr(v_xunhuairiqi,3,5),0,1)));   
          dbms_output.put_line('v_months:'||v_months);   
               OPEN vrec for SELECT to_char(add_months(to_date((substr(to_char(sysdate,'yyyy-mm-dd'),1,5))||v_months||'-'||substr(v_xunhuairiqi,5,7),'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd')   
                  from dual connect by rownum<= ceil(varInt);   
                     LOOP     
                      FETCH vrec INTO strOverSql;    
                      exit when vrec%notfound;   
                       dbms_output.put_line('StopTime'||strOverSql);   
                           insert into timeInfo values(strOverSql);   
                           commit;   
                     end loop;               
                    
          end if;   
          -----------------------------------------------------------------------------------------------   
                         
                         
           open out_room for select id, name from Tbmeetroom  where id in (select id from Tbmeetroom where status = 0    
           and id not in    
           (select huiyishi from tbmeetmgrinfo,timeinfo where to_date(v_strtime,'hh24:mi') between to_date(starttime,'hh24:mi')    
           and to_date(endtime,'hh24:mi')  and to_date(stoptime,'yyyy-MM-dd') = to_date(timeinfo.t_stoptime,'yyyy-MM-dd')    
           and ecid = v_ecid or to_date(v_endtime,'hh24:mi') between to_date(starttime,'hh24:mi')    
           and to_date(endtime,'hh24:mi')  and to_date(stoptime,'yyyy-MM-dd') = to_date(timeinfo.t_stoptime,'yyyy-MM-dd') and ecid = v_ecid )    
           and id not in    
           (select meetroomid from Tbmeetroomlock,timeinfo where to_date(timeinfo.t_stoptime||' '||v_strtime,'yyyy-mm-dd hh24:mi')     
           between to_date(lockstarttime,'yyyy-mm-dd hh24:mi') and to_date(lockendtime,'yyyy-mm-dd hh24:mi')    
           or to_date(timeinfo.t_stoptime||' '||v_endtime,'yyyy-mm-dd hh24:mi')  between to_date(lockstarttime,'yyyy-mm-dd hh24:mi')    
           and to_date(lockendtime,'yyyy-mm-dd hh24:mi')));   
                        
             
          ------------------------------------------------------------------------------------------   
             
         end if;    
         RETURN out_room;   
         END pro_Sel_tbmeetmgrInfo;   
                                             
    END FUN_tbmeetmgrInfo;