/*
* Copyright 2004 The Apache Software Foundation.
*
* Licensed under the Apache License, Version 2.0 (the "License")
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.agila.util;
import org.apache.agila.util.XMLUtil;
import org.apache.agila.engine.Instance;
import org.apache.agila.engine.InstanceID;
import org.apache.agila.engine.Token;
import org.apache.agila.engine.TokenID;
import org.apache.agila.impl.BusinessProcessImpl;
import org.apache.agila.impl.InstanceImpl;
import org.apache.agila.impl.TokenImpl;
import org.apache.agila.impl.dao.AgilaDAO;
import org.apache.agila.model.BusinessProcess;
import org.apache.agila.model.BusinessProcessID;
import org.apache.agila.model.NodeID;
import org.apache.agila.services.InstanceServiceInfo;
import org.apache.agila.services.InstanceInfo;
import org.apache.agila.services.task.Task;
import org.apache.agila.services.task.TaskID;
import org.apache.agila.services.task.TaskImpl;
import org.apache.agila.services.user.GroupID;
import org.apache.agila.services.user.UserID;
import org.apache.agila.services.user.UserInfo;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutput;
import java.io.ObjectOutputStream;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
/**
* An intermediary between the database and the system. Creates a persistence
* session using JDBC. All JDBC calls pass through this class
*
* TODO maybe creating a session factory is a cool todo so that system will
* be abstracted how the persistence layer is done (JDO, O/R, JDBC)
*/
public class JDBCUtil implements AgilaDAO {
private static JDBCUtil jdbcUtil;
private String url = null;
private String uname = null;
private String passwd = null;
public static JDBCUtil getInstance() {
if( jdbcUtil == null ) {
jdbcUtil = new JDBCUtil();
}
return jdbcUtil;
}
private JDBCUtil() {
try {
// Create the connection
InputStream input = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
if (input == null) {
input = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
}
System.out.println("JDBCUtil : input = " + input);
Properties properties = new Properties();
properties.load(input);
System.out.println("using driver = " + properties.getProperty("jdbc.driver"));
Class.forName(properties.getProperty("jdbc.driver"));
url = properties.getProperty( "jdbc.url" );
uname = properties.getProperty( "jdbc.username");
passwd = properties.getProperty( "jdbc.password");
} catch( Exception e ) {
e.printStackTrace();
}
}
public Connection getConnection() {
try {
return DriverManager.getConnection( url, uname, passwd);
}
catch(SQLException e) {
e.printStackTrace();
}
return null;
}
public BusinessProcessID addGraph( BusinessProcess businessProcess ) {
BusinessProcessID retVal = null;
Connection connection = null;
try {
String sql = "insert into bpm_businessprocess" +
" ( businessprocessid, name, xml ) values ( ?, ?, ? )";
int businessProcessID = generateKey();
connection = getConnection();
PreparedStatement statement = connection.prepareStatement( sql );
statement.setInt( 1, businessProcessID );
statement.setString( 2, businessProcess.getName() );
statement.setString( 3, businessProcess.getGraphAsXML() );
statement.execute();
statement.close();
retVal = new BusinessProcessID( businessProcessID );
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public BusinessProcessID getGraphIDByName( String name ) {
BusinessProcessID retVal = null;
Connection connection = null;
try {
String sql = "select businessprocessid, name" +
" from bpm_businessprocess" +
" where name = '" + name + "'";
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
int id = result.getInt( "businessprocessid" );
retVal = new BusinessProcessID( id );
result.close();
statement.close();
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public BusinessProcess getGraphByID( BusinessProcessID businessProcessID ) {
BusinessProcessImpl retVal = null;
Connection connection = null;
try {
String sql = "select businessprocessid, xml" +
" from bpm_businessprocess" +
" where businessprocessid = " + businessProcessID.getID();
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
String xml = result.getString( "xml" );
retVal = (BusinessProcessImpl)XMLUtil.deserializeXML(
new StringReader( xml ) );
retVal.setBusinessProcessID( businessProcessID );
result.close();
statement.close();
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public List getAllProcessGraphs() {
List retVal = new ArrayList();
Connection connection = null;
try {
String sql = "select businessprocessid from bpm_businessprocess";
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
while( result.next() ) {
retVal.add( getGraphByID( new BusinessProcessID(
result.getInt( "businessprocessid" ) ) ) );
}
result.close();
statement.close();
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
/**
* Inserts a new instance in the database
*/
public Instance newInstance( BusinessProcessID businessProcessID,
Map params) {
Instance retVal = null;
Connection connection = null;
try {
BusinessProcess businessProcess = getGraphByID( businessProcessID );
String sql = "insert into bpm_instance" +
" ( instanceid, processid, graphname, params, status )" +
" values ( ?, ?, ?, ?, ? )";
int instanceID = generateKey();
connection = getConnection();
PreparedStatement preparedStatement =
connection.prepareStatement( sql );
preparedStatement.setInt( 1, instanceID );
preparedStatement.setInt( 2, businessProcessID.getID() );
preparedStatement.setString( 3, businessProcess.getName() );
preparedStatement.setBytes( 4, SerializeUtil.serializeAppParam( params ) );
preparedStatement.setInt( 5, Instance.STATUS_RUNNING );
preparedStatement.execute();
preparedStatement.close();
// Retrieve the created instance
retVal = getInstanceByID( new InstanceID( instanceID ) );
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
/**
* Retrieve the instance service info.
*/
public InstanceServiceInfo getInstanceServiceInfo() {
InstanceServiceInfo retVal = null;
Connection connection = null;
try {
int stopped = 0;
int suspended = 0;
int running = 0;
int completed = 0;
String sql = "select * from bpm_instance";
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
while( result.next() ) {
int status = result.getInt( "status" );
switch( status ) {
case Instance.STATUS_STOPPED:
stopped++;
break;
case Instance.STATUS_SUSPENDED:
suspended++;
break;
case Instance.STATUS_RUNNING:
running++;
break;
case Instance.STATUS_COMPLETE:
completed++;
break;
}
}
result.close();
statement.close();
retVal = new InstanceServiceInfo( running, suspended, stopped,
completed );
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public void saveInstance( Instance instance ) {
Connection connection = null;
try {
String sql = "update bpm_instance"
+ " set params = ?, stepdate = ?, status = ?"
+ " where instanceid = " + instance.getInstanceID().getID();
connection = getConnection();
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement( sql );
statement.setBytes( 1,
SerializeUtil.serializeAppParam( instance.getInstanceVariables() ) );
if( instance.getStartDate() != null ) {
statement.setDate( 2, new java.sql.Date(
instance.getStartDate().getTime() ) );
}
else {
statement.setDate(2,null);
}
statement.setInt( 3, instance.getStatus() );
statement.execute();
connection.commit();
statement.close();
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public Instance getInstanceByID( InstanceID instanceID ) {
InstanceImpl retVal = null;
Connection connection = null;
try {
String sql = "select * from bpm_instance" +
" where instanceid = " + instanceID.getID();
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
retVal = new InstanceImpl();
retVal.setInstanceID( new InstanceID(
result.getInt( "instanceid" ) ) );
retVal.setBusinessProcessID( new BusinessProcessID(
result.getInt( "processid" ) ) );
retVal.setGraphName( result.getString( "graphname" ) );
retVal.setInstanceVariables(
SerializeUtil.deserializeAppParam( result.getBytes( "params" ) ) );
retVal.setStartDate( result.getDate( "startdate" ) );
retVal.setLastStepDate( result.getDate( "stepdate" ) );
retVal.setStatus( result.getInt( "status" ) );
result.close();
statement.close();
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public List listInstanceInfo() {
List retVal = null;
Connection connection = null;
try {
String sql = "select instanceid from bpm_instance";
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
retVal = new ArrayList();
while( result.next() ) {
Instance instance = getInstanceByID(
new InstanceID( result.getInt( "instanceid" ) ) );
InstanceInfo ii = new InstanceInfo(instance);
retVal.add(ii);
}
result.close();
statement.close();
}
catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public TaskID insertTask( Task task ) {
TaskID retVal = null;
Connection connection = null;
try {
String sql = "insert into bpm_task ( id, userid, groupid, description," +
" expiration, tokenid, status, instanceid )" +
" values ( ?, ?, ?, ?, ?, ?, ?, ? )";
int taskID = generateKey();
connection = getConnection();
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement( sql );
statement.setInt( 1, taskID );
UserID userID = task.getUserID();
if (userID == null) {
statement.setNull( 2, Types.INTEGER);
}
else {
statement.setInt( 2, userID.getID() );
}
GroupID groupID = task.getGroupID();
if (groupID == null) {
statement.setNull( 3, Types.INTEGER);
}
else {
statement.setInt( 3, groupID.getID() );
}
statement.setString( 4, task.getTaskDescription() );
statement.setDate( 5, new java.sql.Date(
task.getTaskExpirationDate().getTime() ) );
statement.setInt( 6, task.getSourceTokenID().getID() );
statement.setInt( 7, task.getTaskStatus() );
statement.setInt( 8, task.getInstanceID().getID() );
statement.execute();
connection.commit();
statement.close();
retVal = new TaskID( taskID );
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
// TODO could use a value object here to update only the fields that have changed
public TaskID updateTask( Task task ) {
TaskID retVal = null;
Connection connection = null;
try {
String sql = "update bpm_task set userid = ?, groupid = ?, description = ?," +
" expiration = ?, tokenid = ?, status = ?, instanceid = ?" +
" where id = ?";
connection = getConnection();
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement( sql );
UserID userID = task.getUserID();
if (userID == null) {
statement.setNull( 1, Types.INTEGER);
}
else {
statement.setInt( 1, userID.getID() );
}
GroupID groupID = task.getGroupID();
if (groupID == null) {
statement.setNull( 2, Types.INTEGER);
}
else {
statement.setInt( 2, groupID.getID() );
}
statement.setString( 3, task.getTaskDescription() );
statement.setDate( 4, new java.sql.Date(
task.getTaskExpirationDate().getTime() ) );
statement.setInt( 5, task.getSourceTokenID().getID() );
statement.setInt( 6, task.getTaskStatus() );
statement.setInt( 7, task.getInstanceID().getID() );
statement.setInt( 8, task.getTaskID().getID() );
statement.execute();
connection.commit();
statement.close();
retVal = task.getTaskID();
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public Task getTaskByID( TaskID taskID ) {
TaskImpl retVal = null;
Connection connection = null;
try {
String sql = "select * from bpm_task where id = " + taskID.getID();
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
retVal = new TaskImpl();
retVal.setTaskID( new TaskID( result.getInt( "ID" ) ) );
retVal.setUserID( new UserID( result.getInt( "userid" ) ) );
retVal.setTeamID( new GroupID( result.getInt( "groupid" ) ) );
retVal.setTaskDescription( result.getString( "description" ) );
retVal.setTaskExpirationDate( result.getDate( "expiration" ) );
retVal.setSourceTokenID( new TokenID(
result.getInt( "tokenid" ) ) );
retVal.setTaskStatus( result.getInt( "status" ) );
retVal.setInstanceID( new InstanceID(
result.getInt( "instanceid" ) ) );
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public List getTasksForUser( UserID userID, int status ) {
List retVal = null;
Connection connection = null;
try {
String sql = "select ID from bpm_task" +
" where userid = " + userID.getID();
if (status != Task.TASK_ALL) {
sql += " and status = " + status;
}
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
retVal = new ArrayList();
while( result.next() ) {
retVal.add( getTaskByID( new TaskID(
result.getInt( "ID" ) ) ) );
}
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public List getTasksForInstance( InstanceID instanceID, int status ) {
List retVal = null;
Connection connection = null;
try {
String sql = "select ID from bpm_task" +
" where instanceid = " + instanceID.getID();
if (status != Task.TASK_ALL) {
sql += " and status = " + status;
}
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
retVal = new ArrayList();
while( result.next() ) {
retVal.add( getTaskByID( new TaskID(
result.getInt( "ID" ) ) ) );
}
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public int setTaskStatusForInstance(InstanceID instanceID, int oldStatus, int newStatus) {
Connection connection = null;
try {
String sql = "update bpm_task set status = " + newStatus + " where instanceid = " + instanceID.getID();
if (oldStatus != Task.TASK_ALL) {
sql += " and status = " + oldStatus;
}
connection = getConnection();
Statement statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
statement.close();
return rows;
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public List getTasksForGroups(GroupID[] groupIDs, int status) {
List retVal = new ArrayList();
if (groupIDs.length > 0) {
Connection connection = null;
try {
StringBuffer buffer = new StringBuffer("select id from bpm_task where groupid in (");
for (int i = 0; i < groupIDs.length; i++) {
GroupID groupID = groupIDs[i];
if (i > 0) {
buffer.append(", ");
}
buffer.append(Integer.toString(groupID.getID()));
}
buffer.append(")");
if (status != Task.TASK_ALL) {
buffer.append(" and status = ");
buffer.append(Integer.toString(status));
}
String sql = buffer.toString();
System.out.println("Evaluating sql: " + sql);
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
while( result.next() ) {
retVal.add( getTaskByID( new TaskID(
result.getInt( "ID" ) ) ) );
}
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
return retVal;
}
public boolean lockTaskForUser(TaskID taskID, UserID userID) {
Connection connection = null;
try {
String sql = "update bpm_task set userid = " + userID.getID() + " where id = " + taskID.getID() + " and userid is null";
connection = getConnection();
Statement statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
statement.close();
return rows == 1;
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void unlockTaskForUser(TaskID taskID, UserID userID) {
Connection connection = null;
try {
String sql = "update bpm_task set userid = null where id = " + taskID.getID() + " and userid = " + userID.getID();
connection = getConnection();
Statement statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
statement.close();
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* Create a token with only the InstanceID given
*/
/*
public Token newToken( InstanceID instanceID ) {
Token retVal = null;
try {
String sql = "insert into bpm_token ( tokenid, instanceid," +
" active ) values ( ?, ?, ? )";
int tokenID = generateKey();
PreparedStatement statement = connection.prepareStatement( sql );
statement.setInt( 1, tokenID );
statement.setInt( 2, instanceID.getID() );
statement.setBoolean( 3, true );
statement.execute();
statement.close();
retVal = getTokenByID( new TokenID( tokenID ) );
} catch( SQLException e ) {
throw new RuntimeException( e );
}
return retVal;
}
*/
public Token newToken( InstanceID instanceID, NodeID nodeID, int state ) {
Token retVal = null;
Connection connection = null;
try {
String sql = "insert into bpm_token ( tokenid, instanceid," +
" nodeid, state, active ) values ( ?, ?, ?, ?, ? )";
int tokenID = generateKey();
connection = getConnection();
PreparedStatement statement = connection.prepareStatement( sql );
statement.setInt( 1, tokenID );
statement.setInt( 2, instanceID.getID() );
statement.setInt( 3, nodeID.getID() );
statement.setInt( 4, state );
statement.setBoolean( 5, true );
statement.execute();
statement.close();
retVal = getTokenByID( new TokenID( tokenID ) );
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
// TODO could use a VO here
public boolean saveToken( Token token ) {
boolean retVal = false;
Connection connection = null;
try {
String sql = "update bpm_token set instanceid = ?, nodeid = ?," +
" state = ?, active = ? where tokenid = ?";
connection = getConnection();
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement( sql );
if( token.getInstanceID() != null ) {
statement.setInt( 1, token.getInstanceID().getID() );
}
statement.setInt( 2, token.getCurrentNodeID().getID() );
statement.setInt( 3, token.getCurrentState() );
statement.setBoolean( 4, token.isActive() );
statement.setInt( 5, token.getTokenID().getID() );
statement.execute();
connection.commit();
statement.close();
retVal = true;
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public Token getTokenByID( TokenID tokenID ) {
TokenImpl retVal = null;
Connection connection = null;
try {
String sql = "select * from bpm_token" +
" where tokenid = " + tokenID.getID();
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
retVal = new TokenImpl();
retVal.setTokenID( new TokenID( result.getInt( "tokenid" ) ) );
retVal.setInstanceID( new InstanceID(
result.getInt( "instanceid" ) ) );
retVal.setCurrentNodeID(new NodeID(result.getInt("nodeid")));
retVal.setCurrentState( result.getInt( "state" ) );
retVal.setActive( result.getBoolean( "active" ) );
result.close();
statement.close();
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public List getActiveTokensForInstance( InstanceID instanceID ) {
List retVal = null;
Connection connection = null;
try {
String sql = "select tokenid from bpm_token" +
" where instanceid = " + instanceID.getID() +
" and active = 1";
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
retVal = new ArrayList();
while( result.next() ) {
retVal.add( getTokenByID( new TokenID(
result.getInt( "tokenid" ) ) ) );
}
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public UserInfo getUserFromPrincipal(String principalName) {
UserInfo retVal = null;
Connection connection = null;
try {
String sql = "select userid from bpm_userinfo where principal = '" + principalName + "'";
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
retVal = getUserInfo( new UserID( result.getInt( "userid" ) ) );
result.close();
statement.close();
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public UserInfo getUserInfo(UserID id) {
UserInfo retVal = null;
Connection connection = null;
try {
String sql = "select * from bpm_userinfo where userid = " + id;
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
retVal = new UserInfo();
retVal.setUserName( result.getString( "name" ) );
retVal.setUserPrincipal( result.getString( "principal" ) );
retVal.setUserPassword( result.getString( "password" ) );
retVal.setAdmin(result.getBoolean("isadmin"));
retVal.setUserID( new UserID( result.getInt( "userid" ) ) );
result.close();
statement.close();
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public UserID addUser(UserInfo newUser) {
UserID retVal = null;
Connection connection = null;
try {
String sql = "insert into bpm_userinfo values( ?, ?, ?, ?, ? )";
int userID = generateKey();
connection = getConnection();
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement( sql );
statement.setInt( 1, userID );
statement.setString( 2, newUser.getUserName() );
statement.setString( 3, newUser.getUserPassword() );
statement.setString( 4, newUser.getUserPrincipal() );
statement.setBoolean( 5, newUser.isAdmin() );
statement.execute();
connection.commit();
statement.close();
retVal = new UserID( userID );
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
public List listAllUserInfo() {
List retVal = null;
Connection connection = null;
try {
String sql = "select userid from bpm_userinfo";
connection = getConnection();
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
retVal = new ArrayList();
while( result.next() ) {
retVal.add( getUserInfo( new UserID( result.getInt( "userid" ) ) ) );
}
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
// TODO vo could be used here
public void saveUser( UserInfo existingUser ) {
Connection connection = null;
try {
String sql = "update bpm_userinfo set password = ?, principal = ?, isadmin = ? where userid = ?";
connection = getConnection();
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement( sql );
statement.setString( 1, existingUser.getUserPassword() );
statement.setString( 2, existingUser.getUserPrincipal() );
statement.setBoolean( 3, existingUser.isAdmin() );
statement.setInt( 4, existingUser.getUserID().getID() );
statement.execute();
connection.commit();
statement.close();
} catch( SQLException e ) {
throw new RuntimeException( e );
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* Generates a unique key
*
* TODO needs some improvement
*/
public int generateKey() {
int retVal = 0;
Connection connection = null;
try {
connection = getConnection();
connection.setAutoCommit(false);
// Remove previous value
String sql = "delete from bpm_unique_key";
Statement statement = connection.createStatement();
statement.execute( sql );
connection.commit();
statement.close();
// Insert new value
sql = "insert into bpm_unique_key ( tstamp ) values ( ? )";
PreparedStatement preparedStatement =
connection.prepareStatement( sql );
preparedStatement.setDate( 1, new java.sql.Date(
new java.util.Date().getTime() ) );
preparedStatement.execute();
connection.commit();
preparedStatement.close();
// Retrieve new key
sql = "select * from bpm_unique_key";
statement = connection.createStatement();
ResultSet result = statement.executeQuery( sql );
result.next();
retVal = result.getInt( "next_hi" );
result.close();
statement.close();
} catch( SQLException e ) {
e.printStackTrace();
}
finally {
if (connection != null) {
try {
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
return retVal;
}
}