java 程序调用Oracle Function .

转载 于 2012-09-11 12:11:43 发布 · 1.6k 阅读 · 0
文章标签：

#function #oracle #java #session #date #string

本文探讨了在特定会议场景下，如何通过算法优化会议资源的分配与利用，包括会议时间、会议室等关键要素，旨在提升会议效率与用户体验。文中详细介绍了算法的设计思路、实现流程及其实验验证过程，为会议管理系统提供了有效的技术支持。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

程序代码——调用Function通用方法：

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 1=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;

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值