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;
}
}
数据库下载链接:
点击打开链接