package org.apache.slide.projector.processor.sql;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.apache.slide.projector.Context;
import org.apache.slide.projector.ProcessException;
import org.apache.slide.projector.Processor;
import org.apache.slide.projector.Result;
import org.apache.slide.projector.descriptor.AnyValueDescriptor;
import org.apache.slide.projector.descriptor.ArrayValueDescriptor;
import org.apache.slide.projector.descriptor.ParameterDescriptor;
import org.apache.slide.projector.descriptor.ResultDescriptor;
import org.apache.slide.projector.descriptor.ResultEntryDescriptor;
import org.apache.slide.projector.descriptor.StateDescriptor;
import org.apache.slide.projector.descriptor.StringValueDescriptor;
import org.apache.slide.projector.i18n.DefaultMessage;
import org.apache.slide.projector.i18n.ErrorMessage;
import org.apache.slide.projector.i18n.ParameterMessage;
import org.apache.slide.projector.value.ArrayValue;
import org.apache.slide.projector.value.MapValue;
import org.apache.slide.projector.value.NumberValue;
import org.apache.slide.projector.value.StreamableValue;
import org.apache.slide.projector.value.StringValue;
import org.apache.slide.projector.value.Value;
/**
* @version $Revision: 1.4 $
*/
public class SQLProcessor implements Processor {
private final static String STATEMENT = "statement";
private final static String VALUES = "values";
private final static String RESULT = "result";
private final static String ROW_COUNT = "row-count";
private final static ParameterDescriptor[] parameterDescriptors = new ParameterDescriptor[] {
new ParameterDescriptor("statement", new ParameterMessage("update/statement"), new StringValueDescriptor()),
new ParameterDescriptor("values", new ParameterMessage("update/values"), new ArrayValueDescriptor(new AnyValueDescriptor()), new ArrayValue(new Value[0]))
};
private final static ResultDescriptor resultDescriptor = new ResultDescriptor(
new StateDescriptor[] { StateDescriptor.OK_DESCRIPTOR },
new ResultEntryDescriptor[] {
new ResultEntryDescriptor(ROW_COUNT, new DefaultMessage("sql/row-count"), NumberValue.CONTENT_TYPE, false),
new ResultEntryDescriptor(RESULT, new DefaultMessage("sql/result"), ArrayValue.CONTENT_TYPE, false)
});
public Result process(Map parameter, Context context) throws Exception {
String statemenet = parameter.get(STATEMENT).toString();
Value []values = ((ArrayValue)parameter.get(VALUES)).getArray();
javax.naming.Context ctx = new InitialContext();
if ( ctx == null ) throw new ProcessException(new ErrorMessage("noInitialContextAvailable"));
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
Result result = new Result(StateDescriptor.OK);
if (ds != null) {
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
Connection conn = null;
try {
conn = ds.getConnection();
if(conn != null) {
preparedStatement = conn.prepareStatement(statemenet);
for ( int i = 0; i < values.length; i++ ) {
// FIXME: We need a mapping for every sql type that should be supported
if ( values[i] instanceof StringValue ) {
preparedStatement.setString(i+1, values[i].toString());
} else if ( values[i] instanceof NumberValue ) {
preparedStatement.setInt(i+1, ((NumberValue)values[i]).getNumber().intValue());
} else if ( values[i] instanceof StreamableValue ) {
preparedStatement.setBinaryStream(i+1, ((StreamableValue)values[i]).getInputStream(), ((StreamableValue)values[i]).getContentLength());
}
}
if ( preparedStatement.execute() ) {
resultSet = preparedStatement.getResultSet();
List resultSetResources = new ArrayList();
ResultSetMetaData metaData = resultSet.getMetaData();
while ( resultSet.next() ) {
Map rowMap = new HashMap();
int columnCount = metaData.getColumnCount();
for ( int i = 0; i < columnCount; i++ ) {
String key = metaData.getColumnLabel(i+1);
Object object = resultSet.getObject(i+1);
if ( object instanceof String ) {
rowMap.put(key, new StringValue((String)object));
} else if ( object instanceof Integer ) {
rowMap.put(key, new NumberValue((Integer)object));
}
}
resultSetResources.add(new MapValue(rowMap));
}
Value[] resultEntries = new Value[resultSetResources.size()];
result.addResultEntry(RESULT, new ArrayValue((Value[] )resultSetResources.toArray(resultEntries)));
} else {
result.addResultEntry(ROW_COUNT, new NumberValue(new BigDecimal(preparedStatement.getUpdateCount())));
}
conn.close();
}
} catch (SQLException e) {
throw new ProcessException(new ErrorMessage("sqlException"), e);
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (resultSet != null) {
try { resultSet.close(); } catch (SQLException e) { ; }
resultSet = null;
}
if (preparedStatement != null) {
try { preparedStatement.close(); } catch (SQLException e) { ; }
preparedStatement = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) { ; }
conn = null;
}
}
}
return result;
}
public ParameterDescriptor[] getParameterDescriptors() {
return parameterDescriptors;
}
public ResultDescriptor getResultDescriptor() {
return resultDescriptor;
}
}