java 程序调用Oracle Function

舒畅 · 发表于 2018-9-26 08:28:30

　　程序代码——调用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 = 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, rs.getObject(colname));
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 = 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, rs.getObject(colname));
result.add(row);
}
rs.close();
stat.close();
}
} finally {
closeSession();
}
return result;
}
}

　　

　　应用程序调用Function：
　　

/**添加参数**/

ArrayList params = new ArrayList();

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 list_Sel_RoomInfo=daoSql.find(sql, params); //原程序代码

List list_Sel_RoomInfo = new ArrayList();;

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();

for(int i=0;i

账号		自动登录	找回密码
密码			立即注册

Centos6.5×64安装配置openmeetings3.0.3详

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

[经验分享] java 程序调用Oracle Function

浏览过的版块

扫码加入运维网微信交流群