BOS中调用存储过程实例

最新推荐文章于 2022-08-18 11:49:54 发布

艳阳天_.

最新推荐文章于 2022-08-18 11:49:54 发布

阅读量406

点赞数

分类专栏： # EAS和BOS开发专栏

本文链接：https://blog.csdn.net/MZ199290/article/details/112220486

版权

EAS和BOS开发专栏专栏收录该内容

159 篇文章 17 订阅

订阅专栏

package com.kingdee.eas.custom.xzkingdeemz.utils;

public class OracleConnStr
/*   */ {
/* 4 */   public static String sqluser = "******";
/* 5 */   public static String sqlpwd = "*******";
/* 6 */   public static String sqlurl = "";
/*   */ }

package com.kingdee.eas.custom.xzkingdeemz.utils;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import com.kingdee.bos.Context;
import com.kingdee.bos.event.util.DBUtils;
import com.kingdee.bos.sql.KSqlUtil;

public class OracleConnection {
	private static final String DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
	private static String DATABASE_URL = OracleConnStr.sqlurl;
	private static String DATABASE_USER = OracleConnStr.sqluser;
	private static String DATABASE_PASSWORD = OracleConnStr.sqlpwd;

	public static String xmlPath = System.getProperty("datacenter.config");

	public static String GetPwdByAIS(String ais)
			throws ParserConfigurationException, SAXException, IOException {
		File f = new File(xmlPath);
		System.out.print(xmlPath);
		if (!(f.exists())) {
			f = new File(xmlPath);
			if (!(f.exists())) {
				return "";
			}
		}
		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		DocumentBuilder builder = factory.newDocumentBuilder();
		Document doc = builder.parse(f);
		Element root = doc.getDocumentElement();
		NodeList nl = root.getElementsByTagName("datacenter");
		String pwd = "";
		for (int i = 0; i < nl.getLength(); ++i) {
			Node n = nl.item(i);
			if (!(n.getAttributes().getNamedItem("id").getNodeValue()
					.equals(ais)))
				continue;
			String dbpasswordStr = n.getAttributes().getNamedItem("dbpassword")
					.getNodeValue();

			pwd = KSqlUtil.decodePassword(dbpasswordStr);
		}

		return pwd;
	}

	public static Connection getOracleConn(Context ctx) throws SQLException,
			ParserConfigurationException, SAXException, IOException {
		/* 81 */DatabaseMetaData md = DBUtils.getConnection(ctx).getMetaData();
		/* 82 */String userName = md.getUserName();
		/* 83 */String dbUrl = md.getURL();
		/* 84 */String pwd = GetPwdByAIS(ctx.getAIS());
		/* 85 */Connection dbConnection = DriverManager.getConnection(dbUrl,
				userName, pwd);
		/* 86 */return dbConnection;
	}

	public static Connection getConn() {
		try {
			/* 92 */Class.forName("oracle.jdbc.driver.OracleDriver");

			/* 99 */Connection dbConnection = DriverManager.getConnection(
					DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);

			/* 101 */return dbConnection;
		} catch (SQLException e) {
			/* 104 */e.printStackTrace();
		} catch (ClassNotFoundException e) {
			/* 107 */e.printStackTrace();
		}
		/* 109 */return null;
	}

	public static void closeAll(Connection conn, PreparedStatement pstmt,
			ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		/* 131 */Connection conn = null;
		/* 132 */PreparedStatement ps = null;
		/* 133 */ResultSet rs = null;
		/* 134 */new ArrayList();

		/* 136 */conn = getConn();
		/* 137 */String sql = "select * from ct_bas_customerpolicy where rownum=1 ";
		try {
			/* 139 */ps = conn.prepareStatement(sql);
			/* 140 */rs = ps.executeQuery();
			/* 141 */while (rs.next())
				/* 142 */System.out.println(rs.getString(1));
		} catch (SQLException e) {
			/* 146 */e.printStackTrace();
		}
		/* 148 */closeAll(conn, ps, rs);
	}

}

package com.kingdee.eas.custom.xzkingdeemz.utils;

import java.io.IOException;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.ParserConfigurationException;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import org.xml.sax.SAXException;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;

public class ProcUtils {

	/**
	 * p自营政策导入
	 */
	public void UpdateSelfPolicyByPerson(Context ctx, Map map)
			throws BOSException {
		Connection conn = null;

		conn = getConnection(ctx);
		try {
			CallableStatement cs = conn
					.prepareCall("{call Proc_UpdatePPByObject(?,?,?,?,?,?,?,?)}");

			List customerPolicyList = null;
			String customerPolicyTy = "";
			String ProposerID = "";
			String PersonID = "";
			String billNo = "";
			String PolicyCustomerType = "";
			String OrgID = "";
			java.util.Date effectDate = null;
			java.util.Date startDate = null;
			String CustomerPolicyType = "";
			if (map.get("customerPolicyList") != null) {
				customerPolicyList = (List) map.get("customerPolicyList");
			}
			if (map.get("PersonID") != null) {
				PersonID = map.get("PersonID").toString();
			}
			if (map.get("OrgID") != null) {
				OrgID = map.get("OrgID").toString();
			}
			if (map.get("ProposerID") != null) {
				ProposerID = map.get("ProposerID").toString();
			}
			if (map.get("effectDate") != null) {
				effectDate = (java.sql.Date) map.get("effectDate");
			}
			if (map.get("PolicyCustomerType") != null) {
				PolicyCustomerType = map.get("PolicyCustomerType").toString();
			}
			if (map.get("CustomerPolicyType") != null) {
				CustomerPolicyType = map.get("CustomerPolicyType").toString();
			}
			if (map.get("startDate") != null) {
				startDate = (java.sql.Date) map.get("startDate");
			}

			Array oracleList = getselfpolicyListArray(conn,
					"POLICYCUSTOMER_ORACLE_LIST",
					(ArrayList) customerPolicyList);

			cs.setArray(1, oracleList);
			cs.setString(2, PersonID);
			cs.setString(3, OrgID);
			cs.setString(4, ProposerID);
			cs.setDate(5, (java.sql.Date) effectDate);
			cs.setString(6, PolicyCustomerType);
			cs.setString(7, CustomerPolicyType);
			cs.setDate(8, (java.sql.Date) startDate);

			cs.execute();

			cs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 目标终端导入
	 * 
	 * @param ctx
	 * @return
	 */

	public String UpdateTargetTerminalByPerson(Context ctx, Map map)
			throws BOSException {
		String msg = "";

		Connection conn = null;

		conn = getConnection(ctx);
		try {
			CallableStatement cs = conn
					.prepareCall("{call Proc_UpdateTTByObject(?,?,?,?,?,?,?,?,?,?)}");
			List targetTerminalList = null;
			List ywydList = null;
			String personID = null;
			String orgID = "";
			String proposerID = "";
			String manageTerminalType = "";
			String otcProductList = "";
			String otcProductSHList = "";
			java.util.Date effectDate = null;
			int DisplayProductFlag = 0;
			if (map.get("targetTerminalList") != null) {
				targetTerminalList = (List) map.get("targetTerminalList");
			}
			if (map.get("ywydList") != null) {
				ywydList = (List) map.get("ywydList");
			}
			if (map.get("personID") != null) {
				personID = map.get("personID").toString();
			}
			if (map.get("orgID") != null) {
				orgID = map.get("orgID").toString();
			}
			if (map.get("proposerID") != null) {
				proposerID = map.get("proposerID").toString();
			}
			if (map.get("effectDate") != null) {
				effectDate = (java.util.Date) map.get("effectDate");
			}
			if (map.get("manageTerminalType") != null) {
				manageTerminalType = map.get("manageTerminalType").toString();
			}
			if (map.get("otcProductList") != null) {
				otcProductList = map.get("otcProductList").toString();
			}
			if (map.get("otcProductSHList") != null) {
				otcProductSHList = map.get("otcProductSHList").toString();
			}
			if (map.get("displayProductFlag") != null)
				DisplayProductFlag = ((Boolean) map.get("displayProductFlag"))
						.booleanValue() ? 1 : 0;
			else {
				DisplayProductFlag = 1;
			}

			Array oracleList = getCommonOracleArray(conn,
					"TARGETTERMINALORACLEOBJECT", "TARGETTERMINAL_ORACLE_LIST",
					(ArrayList) targetTerminalList);

			Array ywydOracleList = getCommonOracleArray(conn,
					"YWYDORACLEOBJECT", "YWYD_ORACLE_LIST",
					(ArrayList) ywydList);

			cs.setArray(1, oracleList);
			cs.setString(2, personID);
			cs.setString(3, orgID);
			cs.setString(4, proposerID);
			cs.setString(5, manageTerminalType);
			cs.setString(6, otcProductList);
			cs.setString(7, otcProductList);
			cs.setDate(8, (java.sql.Date) effectDate);
			cs.setInt(9, DisplayProductFlag);
			cs.setArray(10, ywydOracleList);

			cs.execute();

			cs.close();
			msg = "导入成功！";
		} catch (Exception e) {
			e.printStackTrace();
			msg = "导入失败！";
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return msg;
	}

	/**
	 * 处方进销存的导入
	 * 
	 * @param con
	 * @param oracleObjectName
	 * @param Oraclelist
	 * @param objlist
	 * @return
	 * @throws Exception
	 */

	public String  ImportInventoryData(Context ctx, Map map)
			throws BOSException {
		String result = "";
		Connection conn = getConnection(ctx);
		List list = (List) map.get("entryList");
		String billNo = map.get("billNo") == null ? "" : map.get("billNo")
				.toString();
		String title = map.get("title") == null ? "" : map.get("title")
				.toString();
		String bizDate = map.get("bizDate") == null ? "" : map.get("bizDate")
				.toString();
		String author = map.get("author") == null ? "" : map.get("author")
				.toString();
		String position = map.get("position") == null ? "" : map
				.get("position").toString();
		String dept = map.get("dept") == null ? "" : map.get("dept").toString();
		String manager = map.get("manager") == null ? "" : map.get("manager")
				.toString();
		String createAuthor = map.get("createAuthor") == null ? "" : map.get(
				"createAuthor").toString();
		String rxsources = map.get("rxsources") == null ? "" : map.get(
		"rxsources").toString();
		try {
			Array arrayList = getListArray(conn, "INVENTORYDATA_ORACLE_LIST",
					"INVENTORYDATAINFO", (ArrayList) list);
			CallableStatement cs = conn
					.prepareCall("{call Proc_ImportInventoryData(?,?,?,?,?,?,?,?,?,?,?)}");

			cs.setArray(1, arrayList);
			cs.setString(2, billNo);
			cs.setString(3, title);
			cs.setString(4, bizDate);
			cs.setString(5, author);
			cs.setString(6, position);
			cs.setString(7, dept);
			cs.setString(8, manager);
			cs.setString(9, createAuthor);
			cs.setString(10, rxsources);
			cs.registerOutParameter(11, 12);

			cs.execute();
			result = cs.getString(11);

			cs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	private static ARRAY getListArray(Connection con, String oraclelist,
			String oracleInfo, ArrayList objlist) throws Exception {
		ARRAY list = null;

		if ((objlist != null) && (objlist.size() > 0)) {
			StructDescriptor structdesc = new StructDescriptor(oracleInfo, con);
			STRUCT[] structs = new STRUCT[objlist.size()];
			Object[] result = new Object[0];

			for (int i = 0; i < objlist.size(); i++) {
				List subitemList = (List) objlist.get(i);
				result = new Object[subitemList.size()];
				for (int j = 0; j < subitemList.size(); j++) {
					result[j] = new String(subitemList.get(j).toString());
				}
				structs[i] = new STRUCT(structdesc, con, result);
			}

			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(oraclelist,
					con);
			list = new ARRAY(desc, con, structs);
		} else {
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(oraclelist,
					con);
			STRUCT[] structs = new STRUCT[0];
			list = new ARRAY(desc, con, structs);
		}

		return list;
	}

	private static ARRAY getCommonOracleArray(Connection con,
			String oracleObjectName, String Oraclelist, ArrayList objlist)
			throws Exception {
		ARRAY list = null;

		if ((objlist != null) && (objlist.size() > 0)) {
			StructDescriptor structdesc = new StructDescriptor(
					oracleObjectName, con);
			ResultSetMetaData md = structdesc.getMetaData();
			int mdCount = md.getColumnCount();
			STRUCT[] structs = new STRUCT[objlist.size()];
			Object[] result = new Object[0];

			for (int i = 0; i < objlist.size(); i++) {
				result = new Object[mdCount];
				for (int j = 0; j < mdCount; j++) {
					result[j] = new String(((List) objlist.get(i)).get(j)
							.toString());
				}
				structs[i] = new STRUCT(structdesc, con, result);
			}

			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
					con);
			list = new ARRAY(desc, con, structs);
		} else {
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
					con);
			STRUCT[] structs = new STRUCT[0];
			list = new ARRAY(desc, con, structs);
		}

		return list;
	}

	public Connection getConnection(Context ctx) {
		Connection conn = null;
		try {
			conn = OracleConnection.getOracleConn(ctx);
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (ParserConfigurationException e1) {
			e1.printStackTrace();
		} catch (SAXException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		return conn;
	}

	private static ARRAY getselfpolicyListArray(Connection con,
			String Oraclelist, ArrayList objlist) throws Exception {
		ARRAY list = null;

		if ((objlist != null) && (objlist.size() > 0)) {
			StructDescriptor structdesc = new StructDescriptor(
					"POLICYCUSTOMERORACLEOBJECT", con);
			STRUCT[] structs = new STRUCT[objlist.size()];
			Object[] result = new Object[0];

			for (int i = 0; i < objlist.size(); i++) {
				result = new Object[6];
				result[0] = new String(((List) objlist.get(i)).get(0)
						.toString());
				result[1] = new String(((List) objlist.get(i)).get(1)
						.toString());
				result[2] = new String(((List) objlist.get(i)).get(2)
						.toString());
				result[3] = new String(((List) objlist.get(i)).get(3)
						.toString());
				result[4] = new String(((List) objlist.get(i)).get(4)
						.toString());
				result[5] = new String(((List) objlist.get(i)).get(5)
						.toString());
				structs[i] = new STRUCT(structdesc, con, result);
			}

			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
					con);
			list = new ARRAY(desc, con, structs);
		} else {
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
					con);
			STRUCT[] structs = new STRUCT[0];
			list = new ARRAY(desc, con, structs);
		}

		return list;
	}

}

艳阳天_.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
BOS中调用存储过程实例

package com.kingdee.eas.custom.xzkingdeemz.utils;public class OracleConnStr/* */ {/* 4 */ public static String sqluser = "******";/* 5 */ public static String sqlpwd = "*******";/* 6 */ public static String sqlurl = "";/* */ }package.
复制链接

扫一扫