How to create a custom actor filter that calsl to a Oracle database procedure?
Hi experts,
I'm using Bonita Comunity 7.2.3.
I'd need to implement a custom actor filter that calls to an Oracle database procedure. That procedure will return the user_name of the person to assign the task.
Can anybody provide me a example?
Thanks a lot,
Jose.
Hi Sean,
I've solved my issue and my Custom Actor Filter calling to database is working.
Was missing to go to Configure->Process Dependencies->Others-> Add ojdbc6.jar
Here is the code for everybody who need it:
In Oracle:
x_errbuf OUT VARCHAR2,
p_user_name IN VARCHAR2,
x_supervisor_name OUT VARCHAR2) IS
BEGIN
x_retcode := 0;
x_errbuf := 'Procedure succesfully completed';
x_supervisor_name := 'mauro.zetticci';
END;
*
*/
package org.mycompany.connector;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.bonitasoft.engine.connector.ConnectorValidationException;
import org.bonitasoft.engine.exception.BonitaException;
import org.bonitasoft.engine.filter.UserFilterException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OracleDriver;
/**
* The actor filter execution will follow the steps 1 - setInputParameters() -->
* the actor filter receives input parameters values 2 -
* validateInputParameters() --> the actor filter can validate input parameters
* values 3 - filter(final String actorName) --> execute the user filter 4 -
* shouldAutoAssignTaskIfSingleResult() --> auto-assign the task if filter
* returns a single result
*/
public class ApexSupervisorImpl extends AbstractApexSupervisorImpl {
static CallableStatement cs = null;
static Connection conn = null;
public void validateInputParameters() throws ConnectorValidationException {
}
public List<Long> filter(String actorName) throws UserFilterException {
try {
long processInstanceId = getExecutionContext()
.getProcessInstanceId();
long processInitiator = getAPIAccessor().getProcessAPI()
.getProcessInstance(processInstanceId).getStartedBy();
long supervisorId = 0;
// Call database to get supervisor userName
try {
String supervisor = getApexSupervisor(getAPIAccessor()
.getIdentityAPI().getUser(processInitiator)
.getUserName());
supervisorId = getAPIAccessor().getIdentityAPI()
.getUserByUserName(supervisor).getId();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return Arrays.asList(new Long[] { Long.valueOf(supervisorId) });
} catch (BonitaException e) {
throw new UserFilterException(e);
}
}
public boolean shouldAutoAssignTaskIfSingleResult() {
Boolean autoAssignO = (Boolean) getInputParameter("autoAssign");
return autoAssignO == null ? true : autoAssignO.booleanValue();
}
public static Connection getConnection() throws SQLException {
String username = "hr";
String password = "hr";
String thinConn = "jdbc:oracle:thin:@localhost:1521:XE";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(thinConn, username,
password);
conn.setAutoCommit(false);
return conn;
}
public static void closeConnection() {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
public static String getApexSupervisor(String pUserName)
throws SQLException {
conn = getConnection();
String sql = "{call get_apex_supervisor(?,?,?,?)}";
cs = conn.prepareCall(sql);
cs.registerOutParameter(1, Types.INTEGER);
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.setString(3, pUserName);
cs.registerOutParameter(4, java.sql.Types.VARCHAR);
cs.execute();
Integer retcode = cs.getInt(1);
String errbuf = cs.getString(2);
String supervisor = cs.getString(4);
closeConnection();
return supervisor;
}
}
Comments
Could I create a database connector and use it from the Actor Filter?
Hi Sean,
I have the working Java code that connects to Oracle and executes the procedure.
I've added it into the custom actor filter, but I get this exception:
*Caused by: org.bonitasoft.engine.core.filter.exception.SUserFilterExecutionException: java.util.concurrent.ExecutionException: java.lang.NoClassDefFoundError: oracle/jdbc/OracleDriver
*
The filter compiles in Bonita Studio fine. How can I resolve this dependency problem?
Thanks!