java 程序调用Oracle Function

欢迎技术交流。 QQ：138986722

程序代码——调用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;