
版权声明:本文为博主原创文章,未经博主允许不得转载。
domino是文档型数据库,相比关系数据库在权限控制方便有一定的优势,但是在数据分析、统计方面在关系数据库面前则是它的瓶颈。故在实施数据分析、统计方面的应用与关系数据库集成则成为了必然。
本文的项目背景是实现流程效率统计,通过此应用来监控流程各个环节的办理情况,超过办理时限的环节通过绩效扣分的方式来进行管控,达到有效提高流程的办理效率。
流程审批平台:domino
数据存储:Oracle
数据分析、统计:tomcat+servlet
这里主要讲domino怎样往oracle进行增、删、改动作。
【jar包】
ojdbc14.jar (网上很多,找起来也方便,部署在\jvm\lib\ext目录下,要重启domino)
【Java脚本库】
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- public class HurrylogClass {
- Connection conn = null;
- Statement stmt = null;
- ResultSet rset = null;
- String sDriver = "";
- String sURL = "";
- String sName = "";
- String sPasswd = "";
- /*
- * 构造函数
- */
- public HurrylogClass() throws Exception{
- }
- public void fnSetSDriver(String sDriver){
- this.sDriver = sDriver;
- }
- public void fnSetSURL(String sURL){
- this.sURL = sURL;
- }
- public void fnSetSName(String sName){
- this.sName = sName;
- }
- public void fnSetSPasswd(String sPasswd){
- this.sPasswd = sPasswd;
- }
- /*
- * @初始化
- */
- public void fnInit() throws Exception{
- try{
- conn = fnInitConnetion();
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- /*
- * @创建与Oracle数据库连接
- */
- public Connection fnInitConnetion() throws Exception{
- // Connection conn = null;
- /*Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection(
- "jdbc:oracle:thin:@10.3.255.175:1521:orcl", "hurrylog",
- "hurrylog");*/
- Class.forName(this.sDriver);
- conn = DriverManager.getConnection(this.sURL, this.sName,this.sPasswd);
- return conn;
- }
- /*
- * @创建Statement对象
- */
- public Statement fnCreateStatement() throws Exception{
- try{
- // conn = fnInitConnetion();
- if(conn!=null && conn.isClosed()==false){
- stmt = conn.createStatement();
- }
- }catch(Exception e){
- e.printStackTrace();
- }
- return stmt;
- }
- /*
- * @执行Oracle查询,返回查询结果
- */
- public ResultSet fnExcuteQuery(String sQuery) throws Exception{
- try{
- // conn = fnInitConnetion();
- if(stmt!=null){
- // stmt = conn.createStatement();
- rset = stmt.executeQuery(sQuery);
- }
- }catch(Exception e){
- e.printStackTrace();
- }
- return rset;
- }
- /*
- * @执行Oracle数据更新、删除
- */
- public int fnExcuteUpdate(String sQuery) throws Exception{
- int bReturn=0;
- try{
- // conn = fnInitConnetion();
- if(stmt!=null){
- // stmt = conn.createStatement();
- bReturn = stmt.executeUpdate(sQuery);
- }
- }catch(Exception e){
- e.printStackTrace();
- }
- return bReturn;
- }
- /*
- * @关闭conn、stmt、rset对象
- */
- public void fnCloseObject() throws Exception{
- try {
- if(rset!=null){
- rset.close();
- }
- if(stmt!=null){
- stmt.close();
- }
- if(conn!=null){
- conn.close();
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
【oracel测试连接代码(参考)】
- import lotus.domino.*;
- import java.io.PrintWriter;
- import java.sql.Connection;
- public class JavaAgent extends AgentBase {
- Connection conn = null;
- HurrylogClass hurry = null;
- Session ss = null;
- AgentContext agt = null;
- Database db = null;
- Document proDoc = null;
- PrintWriter pw = null;
- String sDriver = "";
- String sURL = "";
- String sName = "";
- String sPasswd = "";
- public void NotesMain() {
- try {
- pw = getAgentOutput();
- ss = getSession();
- agt = ss.getAgentContext();
- db = agt.getCurrentDatabase();
- proDoc = db.getProfileDocument("ConfigProfile",null);
- if(proDoc!=null){
- sDriver = proDoc.getItemValueString("jdbcDriver");
- sURL = proDoc.getItemValueString("jdbcURL");
- sName = proDoc.getItemValueString("jdbcUsername");
- sPasswd = proDoc.getItemValueString("jdbcPassword");
- // System.out.println("sDriver=>"+sDriver+"<");
- // System.out.println("sURL=>"+sURL+"<");
- // System.out.println("sName=>"+sName+"<");
- // System.out.println("sPasswd=>"+sPasswd+"<");
- // 初始化jdbc类
- hurry = new HurrylogClass();
- // 初始化jdbc参数
- hurry.fnSetSDriver(sDriver);
- hurry.fnSetSURL(sURL);
- hurry.fnSetSName(sName);
- hurry.fnSetSPasswd(sPasswd);
- // 创建jdbc连接
- conn = hurry.fnInitConnetion();
- if(conn!=null){
- pw.println("<script>alert('oracle连接成功!');history.back();</script>");
- }else{
- pw.println("<script>alert('oracle连接失败,请检查配置!');</script>");
- }
- }else{
- pw.println("<script>alert('请进行JDBC参数配置!');</script>");
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- try {
- if(pw!=null){
- pw.close();
- }
- hurry.fnCloseObject();
- if(conn!=null){
- conn.close();
- }
- if(proDoc!=null){
- proDoc.recycle();
- }
- if(db!=null){
- db.recycle();
- }
- if(agt!=null){
- agt.recycle();
- }
- if(ss!=null){
- ss.recycle();
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
- }
【sql查询代码(参考)】
- public boolean fnCheckOracleData() throws Exception{
- boolean bReturn=false;
- // HurrylogClass hurry = new HurrylogClass();
- ResultSet rs = null;
- Database appDb = null;
- Document appDoc = null;
- Document logDoc = null;
- // Database orgDb = null;
- // View orgView = null;
- // Document orgDoc = null;
- Vector oDept = null;
- String sDeptName = "";
- int iCount = 0;
- long rsCount = 0;
- String sQuery = "";
- String sHurrylogUNID = "";
- String sAppDocUNID = "";
- String sAppDbpath = "";
- String sTitle = "";
- String sCurUser = "";
- String sCurDept = "";
- String sInTime = "";
- String sIsOverTime = "";
- try {
- //遍历oracle数据前,清除未处理日志,重新生成
- undealView.getAllEntries().removeAll(true);
- // 获取当前年份
- Calendar cal = Calendar.getInstance();
- int year = cal.get(Calendar.YEAR);
- // year = 2013;
- if(hurrylogDb.isOpen()){
- sQuery = "select DOCID,V_DOCID,V_PATH,V_TITLE,V_CURRENT_USER,V_DEPARTMENT,V_IN_TIME,ISOVERTIME from t_hurrylog t where t.v_in_time >= to_date('"+year+"-01-01', 'yyyy-mm-dd') and t.v_in_time < to_date('"+(year+1)+"-01-01', 'yyyy-mm-dd')";
- // System.out.println("sQuery=="+sQuery);
- rs = hurry.fnExcuteQuery(sQuery);
- // 循环oracle数据
- while(rs.next()){
- rsCount = rsCount + 1;
- // System.out.println("执行第" + rsCount + "条数据");
- sHurrylogUNID = rs.getString(1);
- sAppDocUNID = rs.getString(2);
- sAppDbpath = rs.getString(3);
- sTitle = rs.getString(4);
- sCurUser = rs.getString(5);
- sCurDept = rs.getString(6);
- sInTime = rs.getString(7);
- sIsOverTime = rs.getString(8);
- }
- System.out.println("执行完成" + rsCount + "条数据");
- //执行结束,返回true
- bReturn = true;
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally{
- // 关闭oracle连接,domino相关对象
- try{
- // hurry.fnCloseObject();
- if(rs!=null){
- rs.close();
- }
- if(appDoc!=null){
- appDoc.recycle();
- }
- if(logDoc!=null){
- logDoc.recycle();
- }
- if(appDb!=null){
- appDb.recycle();
- }
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- return bReturn;
- }
【sql删除代码(参考)】
- /*
- * 处理oracle数据
- */
- public boolean fnDealOracleData() throws Exception{
- boolean bReturn=false;
- // 初始化jdbc访问类
- // HurrylogClass hurry = new HurrylogClass();
- // View undealView = null;
- Document logDoc = null;
- Document tmpDoc = null;
- Vector oDept = null;
- String sHashurry = "";
- String sType = "";
- String sHurrylogUNID = "";
- String sUsername = "";
- String sCurDept = "";
- String sOrgDept = "";
- String sQuery = "";
- int iMark = 0;
- int iQueryReturn = 0;
- long rsCount = 0;
- try{
- undealView = curDb.getView("undealView");
- logDoc = undealView.getFirstDocument();
- while(logDoc!=null){
- rsCount++;
- // System.out.println("执行第" + rsCount + "条数据");
- iQueryReturn = 0;
- tmpDoc = undealView.getNextDocument(logDoc);
- sHashurry = logDoc.getItemValueString("hasHurry");
- sType = logDoc.getItemValueString("type");
- sHurrylogUNID = logDoc.getItemValueString("docid");
- // 1:应用库文档不存在
- if(sType.equals("1")||sType.equals("4")||sType.equals("5")){
- // 删除oracle数据
- sQuery = "delete from t_hurrylog t where t.docid='" + sHurrylogUNID + "'";
- iQueryReturn = hurry.fnExcuteUpdate(sQuery);
- if(iQueryReturn==1){
- sHashurry = logDoc.getItemValueString("hasHurry");
- if(sHashurry.equals("1")){
- // 删除hurrylog数据
- hurrylogDoc = fnGetDocumentByUNID(hurrylogDb,sHurrylogUNID);
- if(hurrylogDoc!=null){
- hurrylogDoc.remove(true);
- }
- }
- // 日志标记为已处理
- logDoc.replaceItemValue("isDone", "1");
- logDoc.save(true);
- }else{
- System.out.println(sHurrylogUNID+"删除失败");
- }
- }
- // 文档指向下一条
- logDoc = tmpDoc;
- }
- System.out.println("执行完成" + rsCount + "条数据");
- // 执行成功返回 true
- bReturn = true;
- }catch(Exception e){
- e.printStackTrace();
- }finally{
- try{
- if (logDoc != null) {
- logDoc.recycle();
- }
- if (tmpDoc != null) {
- tmpDoc.recycle();
- }
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- return bReturn;
- }
【sql更新代码(参考)】
- import java.io.PrintWriter;
- import lotus.domino.*;
- public class JavaAgent extends AgentBase {
- Session ss = null;
- AgentContext agentContext = null;
- Database curDb = null;
- Document proDoc = null;
- Database hurrylogDb = null;
- Document hurrylogDoc = null;
- Document curDoc = null;
- PrintWriter pw = null;
- // 初始化jdbc访问类
- HurrylogClass hurry = null;
- // 待增加服务器配置项
- String sOAServer = "";
- String sHurrylogDbpath = "";
- String sOrgDbpath = "";
- String sDriver = "";
- String sURL = "";
- String sName = "";
- String sPasswd = "";
- String sQuery = "";
- String sCurDept = "";
- String sHurrylogUNID = "";
- public void NotesMain() {
- try {
- pw = getAgentOutput();
- ss = getSession();
- agentContext = ss.getAgentContext();
- curDb = agentContext.getCurrentDatabase();
- curDoc = agentContext.getDocumentContext();
- proDoc = curDb.getProfileDocument("ConfigProfile",null);
- if(proDoc!=null){
- sOAServer = proDoc.getItemValueString("OAServer");
- sHurrylogDbpath = proDoc.getItemValueString("hurrylogDbpath");
- sOrgDbpath = proDoc.getItemValueString("orgDbpath");
- sDriver = proDoc.getItemValueString("jdbcDriver");
- sURL = proDoc.getItemValueString("jdbcURL");
- sName = proDoc.getItemValueString("jdbcUsername");
- sPasswd = proDoc.getItemValueString("jdbcPassword");
- }else{
- System.out.println("没有参数配置文档!");
- }
- // 公文效率监督库
- // hurrylogDb = ss.getDatabase(sOAServer,"chtweboa/system/hurrylog.nsf");
- hurrylogDb = ss.getDatabase(sOAServer,sHurrylogDbpath);
- if(hurrylogDb.isOpen()==false){
- hurrylogDb.open();
- }
- // 定义jdbc访问类
- hurry = new HurrylogClass();
- // 连接参数赋值
- hurry.fnSetSDriver(sDriver);
- hurry.fnSetSURL(sURL);
- hurry.fnSetSName(sName);
- hurry.fnSetSPasswd(sPasswd);
- // 初始化jdbc类
- hurry.fnInit();
- // 创建Statement
- hurry.fnCreateStatement();
- sCurDept = curDoc.getItemValueString("v_realdept");
- sHurrylogUNID = curDoc.getItemValueString("DOCID");
- sQuery = "update t_hurrylog set V_DEPARTMENT='"+sCurDept+"' where DOCID='"+sHurrylogUNID+"'";
- int iReturn = hurry.fnExcuteUpdate(sQuery);
- if(iReturn==1){
- // 更新hurrylog数据
- hurrylogDoc = fnGetDocumentByUNID(hurrylogDb,sHurrylogUNID);
- if(hurrylogDoc!=null){
- hurrylogDoc.replaceItemValue("v_department", sCurDept);
- hurrylogDoc.save(true);
- }
- curDoc.replaceItemValue("isDone", "1");
- curDoc.save(true);
- pw.println("<script>alert('更新成功!');window.close();</script>");
- }else{
- pw.println("<script>alert('更新失败!');history.back();</script>");
- }
- // (Your code goes here)
- } catch(Exception e) {
- e.printStackTrace();
- }finally{
- try{
- if(pw!=null){
- pw.close();
- }
- // 关闭jdbc相关对象
- hurry.fnCloseObject();
- // 关闭domino相关对象
- if (curDoc != null) {
- curDoc.recycle();
- }
- if(curDb!=null){
- curDb.recycle();
- }
- if(proDoc!=null){
- proDoc.recycle();
- }
- if(hurrylogDoc!=null){
- hurrylogDoc.recycle();
- }
- if(hurrylogDb!=null){
- hurrylogDb.recycle();
- }
- if(agentContext!=null){
- agentContext.recycle();
- }
- if(ss!=null){
- ss.recycle();
- }
- System.gc();
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- }
- /*
- * 通过UNID获取数据库文档文档
- */
- public Document fnGetDocumentByUNID(Database appDb,String sUNID) throws Exception{
- Document doc = null;
- try{
- // System.out.println("数据库是否存在==" + (appDb!=null));
- if(appDb!=null && !sUNID.equals("")){
- doc = appDb.getDocumentByUNID(sUNID);
- // System.out.println("文档是否存在==" + (doc!=null));
- }
- }catch(Exception e){
- // e.printStackTrace();
- }
- return doc;
- }
- }