Dbutil
package com.cnten.wf.util;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import com.cnten.wf.flowdefine.Flow;
import com.cnten.wf.flowdefine.Step;
import com.cnten.wf.instance.DynamicStep;
import com.cnten.wf.instance.FlowInstance;
import com.cnten.wf.instance.History;
import com.cnten.wf.instance.StepState;
import com.cnten.wf.instance.Task;
public class Dbutil {
public void dbSaveTask(Task task) throws SQLException {
task.TaskID = GetNewSequence("SEQWF_STEP_TASK_TB");
String strSql = "insert into WF_STEP_TASK_TB(TASK_ID,FLOW_ENTITY_ID,STEP_ID,FLOW_ID,BUSINESS_ENTITY_ID,TASK_NAME,EXECUTOR,TASK_URL,EXE_STATE,CREATEDATE ) values(?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = null;
Connection conn = null;
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(strSql);
pstmt.setInt(1, Integer.valueOf(task.TaskID));
pstmt.setInt(2, Integer.valueOf(task.FlowInstanceID));
pstmt.setInt(3, Integer.valueOf(task.stepID));
pstmt.setInt(4, Integer.valueOf(task.FlowID));
pstmt.setInt(5, Integer.valueOf(task.BusinessEntityID));
pstmt.setString(6, DBFormat.Format(task.TaskName, 1));
pstmt.setString(7, task.UserID );
pstmt.setString(8, DBFormat.Format(task.Url, 1));
pstmt.setInt(9, task.TaskState.getValue());
Timestamp timp = new Timestamp(new Date().getTime());
pstmt.setTimestamp(10,timp);
pstmt.execute();
conn.close();
}
public String dbGetFlowID(String formCode) {
//String strSql = "select PROC_CODE from SY_FUNC_WF_PROC where FUNC_CODE='"
//+ formCode + "'";
String strSql = "select FLOW_ID from WF_FLOW_TB where BO_ID='"
+ formCode + "'";
String procCode = null;
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);
while (rs.next()) {
procCode = rs.getString(1);
}
Dbutil.close(rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return procCode;
}
public FlowInstance dbCreateFlowInstance(FlowInstance f) {
f.InstanceID = GetNewSequence("SEQWF_FLOW_ENTITY_TB");
String strSql = "insert into WF_FLOW_ENTITY_TB( FLOW_ENTITY_ID, FLOW_ENTITY_STATE, FLOW_ID, BUSINESS_ENTITY_ID, FLOW_ENTITY_NAME,CREATOR,CREATE_TIME ,BO_ID) values(?,?,?,?,?,?,?,? )";// ����ҪBO_ID
// "insert into WF_FLOW_ENTITY_TB( FLOW_ENTITY_ID, FLOW_ENTITY_STATE, FLOW_ID, BUSINESS_ENTITY_ID, FLOW_ENTITY_NAME,CREATOR,BO_ID) values({0},{1},{2},{3},'{4}','{5}',{6})";
PreparedStatement prest = null;
Connection conn = Dbutil.getConnection();
try {
prest = conn.prepareStatement(strSql);
prest.setInt(1, Integer.valueOf(f.InstanceID));
prest.setInt(2, f.ExeState.getValue());
prest.setInt(3, Integer.valueOf(f.FlowID));
prest.setString(4, f.BusinessEntityID);//prest.setInt(4, Integer.valueOf(f.BusinessEntityID));
prest.setString(5, f.FlowEntityName);
prest.setString(6, f.Creator );
Timestamp timp = new Timestamp(new Date().getTime());
prest.setTimestamp(7,timp);
prest.setString(8, "1");
prest.execute();
prest.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(conn);
}
return f;
}
public String dbUpdateFlowInstance(FlowInstance flowInstance) {
String strSql = "update WF_FLOW_ENTITY_TB set FLOW_ENTITY_STATE="
+ flowInstance.ExeState.getValue() + " where FLOW_ENTITY_ID="
+ flowInstance.InstanceID;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stmt.execute(strSql);
} catch (SQLException e) {
e.printStackTrace();
return "�쳣";
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return null;
}
public List<HashMap<String, Object>> dbGetFlowSteps(String flowID) {
String strSql = "select step.step_id,step.step_name,step.step_type,step.step_usercount,step.step_performtype,step.step_deptfilter,step.step_rejecttype,step.step_controls,STEP_BRANCHMERGE from wf_step_tb step where step.flow_id='"
+ flowID + "' order by step.step_id";
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);
HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return stepLists;
}
public List<HashMap<String, Object>> dbGetFlowStepLines(String flowID) {
String strSql = "select router.router_id,router.router_name,router.router_condition,router.router_nextstep,router.step_id from wf_router_tb router where exists(select 1 from wf_step_tb step where step.flow_id='"
+ flowID + "' and router.step_id =step.step_id )";
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);
HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
return stepLists;
}
public HashMap<String, Object> dbFillFlowInfo(Flow flow) {
String strSQL = "select FLOW_ID,BO_ID,FLOW_NAME,FLOW_TYPE,FLOW_FORM,FLOW_CHART,FLOW_COMMENT,FLOW_CREATER,FLOW_CREATETIME,FLOW_VERSION,FLOW_TABLE,FLOW_TABLE_PK,FORM_OBJECT_PROVIDER,FLOW_USER_PROVIDER,FLOW_SENDMSG_PROVIDER from WF_FLOW_TB where FLOW_ID="
+ flow.FlowID;
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
HashMap<String, Object> mapObject = new HashMap<String, Object>();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSQL);
HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
if (stepLists.size() > 0) {
mapObject = stepLists.get(0);
}
return mapObject;
}
public HashMap<String, Object> dbGetFormObject(String tableName,
String pkFieldName, String pkValue) {
String strSQL = "select * from " + tableName + " where " + pkFieldName
+ "=" + pkValue;
ResultSet rs;
Statement stmt = null;
Connection conn = Dbutil.getConnection();
HashMap<String, Object> mapObject = new HashMap<String, Object>();
List<HashMap<String, Object>> stepLists = new ArrayList<HashMap<String, Object>>();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSQL);
HashMap<String, Object> map;
ResultSetMetaData rsmd;
while (rs.next()) {
map = new HashMap<String, Object>();
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
stepLists.add(map);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Dbutil.close(stmt);
Dbutil.close(conn);
}
if (stepLists.size() > 0) {
mapObject = stepLists.get(0);
}
return mapObject;
}
/**
* ȡ��SEQֵ
*
* @param newSequence
* @return
*/
public static String GetNewSequence(String newSequence) {
String strSql = "SELECT " + newSequence.trim() + ".NEXTVAL FROM DUAl";
String SEQWF = null;
Statement stmt;
ResultSet rs;
try {
stmt = Dbutil.getConnection()
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strSql);
while (rs.next()) {
SEQWF = rs.getString(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
System.err.println("SEQWF" + SEQWF);
return SEQWF;
}