/**
* Copyright (C) 2011-2015 Incapture Technologies LLC
*
* This is an autogenerated license statement. When copyright notices appear below
* this one that copyright supercedes this statement.
*
* Unless required by applicable law or agreed to in writing, software is distributed
* on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
* or implied.
*
* Unless explicit permission obtained in writing this software cannot be distributed.
*/
package rapture.repo.jdbc;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.TreeMap;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.SqlInOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import rapture.common.*;
import rapture.common.exception.ExceptionToString;
import rapture.common.exception.RaptureExceptionFactory;
import rapture.kernel.Kernel;
import rapture.repo.jdbc.context.ConverterContext;
import rapture.repo.jdbc.context.StatementContext;
import rapture.repo.jdbc.context.StatementType;
import rapture.structured.Cache;
import rapture.structured.InMemoryCache;
import rapture.structured.SqlGenerator;
import rapture.structured.StructuredStore;
import rapture.util.IDGenerator;
import com.google.common.collect.ImmutableMap;
/**
* Created by yanwang on 4/8/15.
*/
public abstract class JDBCStructuredStore implements StructuredStore {
private static Logger log = Logger.getLogger(JDBCStructuredStore.class);
protected TransactionAwareDataSource dataSource;
protected JdbcTemplate jdbc;
protected SqlGenerator sqlGenerator;
protected String instance = "default";
protected String schema;
private Cache cache;
@Override
public void setInstance(String instanceName) {
if (!StringUtils.isBlank(instanceName)) {
instance = instanceName;
}
dataSource = getDataSource(instance);
jdbc = new JdbcTemplate(dataSource);
sqlGenerator = getSqlGenerator();
cache = new InMemoryCache();
}
@Override
public void setConfig(Map config, String authority) {
schema = authority;
jdbc.execute(sqlGenerator.constructCreateSchema(schema));
}
@Override
public TransactionAwareDataSource getDataSource() {
return dataSource;
}
protected abstract TransactionAwareDataSource getDataSource(String instance);
@Override
public void drop() {
jdbc.execute(sqlGenerator.constructDropSchema(schema));
}
@Override
public boolean commit(String txId) {
try {
dataSource.commit(txId);
return true;
} catch (SQLException e) {
log.error("Failed to commit " + txId, e);
return false;
}
}
@Override
public boolean rollback(String txId) {
try {
dataSource.rollback(txId);
return true;
} catch (SQLException e) {
log.error("Fail to rollback " + txId, e);
return false;
}
}
@Override
public Boolean createTableUsingSql(CallingContext context, String sql) {
try {
CreateTable createTable = (CreateTable) CCJSqlParserUtil.parse(sql);
checkTableEntitlement(context, StatementType.CREATE_TABLE, createTable.getTable());
jdbc.execute(createTable.toString());
String tableName = createTable.getTable().getName();
return tableExists(tableName) && refreshColumnTypeCache(tableName);
} catch (ClassCastException e) {
throw RaptureExceptionFactory.create("Not a create table statement " + sql);
} catch (JSQLParserException e) {
throw RaptureExceptionFactory.create("Failed to parse statement " + sql, e);
}
}
@Override
public Boolean createTable(String tableName, Map columns) {
jdbc.execute(sqlGenerator.constructCreateTable(schema, tableName, columns));
return tableExists(tableName) && refreshColumnTypeCache(tableName);
}
@Override
public Boolean dropTable(String tableName) {
jdbc.execute(sqlGenerator.constructDropTable(schema, tableName));
cache.removeColumnTypes(tableName);
return !tableExists(tableName);
}
@Override
public Boolean tableExists(String tableName) {
return jdbc.queryForObject(sqlGenerator.constructTableExists(schema, tableName), Boolean.class);
}
@Override
public List getTables() {
String sql = sqlGenerator.constructListTables(schema);
return jdbc.queryForList(sql, String.class);
}
@Override
public TableMeta describeTable(String tableName) {
Map result = new HashMap<>();
String sql = sqlGenerator.constructDescribeTable(schema, tableName);
for (Map row : jdbc.queryForList(sql)) {
String columnName = (String) row.get("column_name");
String columnType = (String) row.get("data_type");
Object length = row.get("character_maximum_length");
if (length != null) {
columnType += "(" + length + ")";
}
result.put(columnName, columnType);
}
TableMeta tm = new TableMeta();
tm.setRows(result);
return tm;
}
@Override
public Boolean addTableColumns(String tableName, Map columns) {
for (Map.Entry entry : columns.entrySet()) {
jdbc.execute(sqlGenerator.constructAddTableColumns(schema, tableName, ImmutableMap.of(entry.getKey(), entry.getValue())));
}
return refreshColumnTypeCache(tableName);
}
@Override
public Boolean deleteTableColumns(String tableName, List columnNames) {
for (String columnName : columnNames) {
jdbc.execute(sqlGenerator.constructDeleteTableColumns(schema, tableName, Arrays.asList(columnName)));
}
return refreshColumnTypeCache(tableName);
}
@Override
public Boolean updateTableColumns(String tableName, Map columns) {
for (Map.Entry entry : columns.entrySet()) {
jdbc.execute(sqlGenerator.constructUpdateTableColumns(schema, tableName, ImmutableMap.of(entry.getKey(), entry.getValue())));
}
return refreshColumnTypeCache(tableName);
}
@Override
public Boolean renameTableColumns(String tableName, Map columnNames) {
for (Map.Entry entry : columnNames.entrySet()) {
jdbc.execute(sqlGenerator.constructRenameTableColumns(schema, tableName, ImmutableMap.of(entry.getKey(), entry.getValue())));
}
return refreshColumnTypeCache(tableName);
}
@Override
public Boolean insertUsingSql(CallingContext context, String sql) {
try {
Insert insert = (Insert) CCJSqlParserUtil.parse(sql);
checkTableEntitlement(context, StatementType.INSERT, insert.getTable());
// if there is sub query, check entitlements on those tables
if (insert.getSelect() != null) {
SelectConverter selectConverter = getSelectConverter();
insert.getSelect().getSelectBody().accept(selectConverter);
checkEntitlements(context, selectConverter.getContext());
}
return jdbc.update(insert.toString()) > 0;
} catch (ClassCastException e) {
throw RaptureExceptionFactory.create("Not an insert statement: " + sql);
} catch (JSQLParserException e) {
throw RaptureExceptionFactory.create("Failed to parse statement: " + sql, e);
}
}
@Override
public Boolean insertRow(String table, Map values) {
List columnNames = new ArrayList(values.keySet());
int res = jdbc.update(sqlGenerator.constructInsertPreparedStatement(schema, table, Arrays.asList(columnNames)), values.values().toArray(),
getTypes(table, columnNames, true));
return res > 0;
}
@Override
public Boolean insertRows(String table, List> values) {
List columnNames = new ArrayList(values.get(0).keySet());
List> allCols = new ArrayList<>();
List allVals = new ArrayList<>();
for (Map row : values) {
allCols.add(new ArrayList<>(row.keySet()));
allVals.addAll(row.values());
}
int res = jdbc.update(sqlGenerator.constructInsertPreparedStatement(schema, table, allCols), allVals.toArray(),
multiplyAndFlatten(values.size(), getTypes(table, columnNames, true)));
return res == values.size();
}
@Override
public List> selectUsingSql(CallingContext context, String sql) {
try {
// visit select statement
Select select = (Select) CCJSqlParserUtil.parse(sql);
SelectConverter selectConverter = getSelectConverter();
select.getSelectBody().accept(selectConverter);
checkEntitlements(context, selectConverter.getContext());
// execute query
String preparedSql = selectConverter.getBuffer().toString();
Object[] args = selectConverter.getExpressionConverter().getValues().toArray();
return jdbc.query(preparedSql, args, createResultSetExtractor());
} catch (ClassCastException e) {
throw RaptureExceptionFactory.create("Not a select statement: " + sql);
} catch (JSQLParserException e) {
throw RaptureExceptionFactory.create("Failed to parse statement: " + sql, e);
}
}
private SelectConverter getSelectConverter() {
SelectConverter selectConverter = new SelectConverter();
PreparedExpressionConverter expressionConverter = new PreparedExpressionConverter(selectConverter,
selectConverter.getBuffer(), selectConverter.getContext());
selectConverter.setExpressionVisitor(expressionConverter);
return selectConverter;
}
public List> selectJoinedRows(List tables, List columnNames, String from, String where,
List order, Boolean ascending, int limit) {
List args = new ArrayList<>();
where = convertToPreparedStatement(where, args);
String sql = sqlGenerator.constructSelectJoin(tables, columnNames, from, where, order, ascending, limit);
return jdbc.query(sql, args.toArray(), createResultSetExtractor());
}
@Override
public List> selectRows(String table, final List columnNames, String where, List order, Boolean ascending, int limit) {
List args = new ArrayList<>();
where = convertToPreparedStatement(where, args);
String sql = sqlGenerator.constructSelect(schema, table, columnNames, where, order, ascending, limit);
return jdbc.query(sql, args.toArray(), createResultSetExtractor());
}
@SuppressWarnings({"rawtypes", "unchecked"})
private String convertToPreparedStatement(String where, Collection args) {
if (StringUtils.isBlank(where)) {
return where;
}
try {
Expression expression = CCJSqlParserUtil.parseCondExpression(where);
PreparedExpressionConverter expressionConverter = getSelectConverter().getExpressionConverter();
expression.accept(expressionConverter);
// TODO pass in context to check entitlements
// checkEntitlements(expressionConverter.getContext());
args.addAll(expressionConverter.getValues());
return expressionConverter.getBuffer().toString();
} catch (JSQLParserException e) {
throw RaptureExceptionFactory.create("Failed to parse where clause (" + where + ")", e);
}
}
private void checkEntitlements(CallingContext context, ConverterContext visitorContext) {
for (StatementContext statementContext : visitorContext.getStatementContexts()) {
StatementType statementType = statementContext.getStatementType();
for (final Table table : statementContext.getTables()) {
checkTableEntitlement(context, statementType, table);
}
}
}
private void checkTableEntitlement(CallingContext context, StatementType statementType, final Table table) {
String entitlementPath = getEntitlementPath(statementType);
log.debug(String.format("check %s entitlement %s on table %s", statementType, entitlementPath, table.getFullyQualifiedName()));
Kernel.getKernel().validateContext(context, entitlementPath, new IEntitlementsContext() {
@Override
public String getDocPath() {
return table.getName();
}
@Override
public String getAuthority() {
return StringUtils.isBlank(table.getSchemaName()) ? schema : table.getSchemaName();
}
@Override
public String getFullPath() {
return String.format("%s/%s", getAuthority(), table.getName());
}
});
}
private String getEntitlementPath(StatementType statementType) {
switch (statementType) {
case SELECT:
return EntitlementSet.Structured_selectRows.getPath();
case INSERT:
return EntitlementSet.Structured_insertRow.getPath();
case UPDATE:
return EntitlementSet.Structured_updateRows.getPath();
case DELETE:
return EntitlementSet.Structured_deleteRows.getPath();
case CREATE_TABLE:
return EntitlementSet.Structured_createTable.getPath();
default:
throw RaptureExceptionFactory.create("Unsupported statement type " + statementType);
}
}
private ResultSetExtractor>> createResultSetExtractor() {
return new ResultSetExtractor>>() {
@Override
public List> extractData(ResultSet rs) throws SQLException, DataAccessException {
List> ret = new ArrayList>();
ResultSetMetaData rsmd = rs.getMetaData();
int numColumns = rsmd.getColumnCount();
while (rs.next()) {
Map m = new TreeMap(String.CASE_INSENSITIVE_ORDER);
for (int i = 1; i <= numColumns; i++) {
m.put(rsmd.getColumnLabel(i), rs.getObject(i));
}
ret.add(m);
}
return ret;
}
};
}
@Override
public Boolean updateUsingSql(CallingContext context, String sql) {
try {
Update update = (Update) CCJSqlParserUtil.parse(sql);
// check entitlements on tables
for (Table table : update.getTables()) {
checkTableEntitlement(context, StatementType.UPDATE, table);
}
// if there is sub query, check entitlements on those tables
if (update.getSelect() != null) {
SelectConverter selectConverter = getSelectConverter();
update.getSelect().getSelectBody().accept(selectConverter);
checkEntitlements(context, selectConverter.getContext());
}
// execute query
return jdbc.update(update.toString()) > 0;
} catch (ClassCastException e) {
throw RaptureExceptionFactory.create("Not an update statement: " + sql);
} catch (JSQLParserException e) {
throw RaptureExceptionFactory.create("Failed to parse statement: " + sql, e);
}
}
@Override
public Boolean updateRows(String tableName, Map values, String where) {
List columnNames = new ArrayList(values.keySet());
int res = jdbc.update(sqlGenerator.constructUpdatePreparedStatement(schema, tableName, columnNames, where), values.values().toArray(),
getTypes(tableName, columnNames, true));
return res > 0;
}
@Override
public Boolean deleteUsingSql(CallingContext context, String sql) {
try {
Delete delete = (Delete) CCJSqlParserUtil.parse(sql);
if (delete.getWhere() == null) {
throw RaptureExceptionFactory.create("Where clause not specified in delete statement: " + sql);
}
checkTableEntitlement(context, StatementType.DELETE, delete.getTable());
// check entitlements on any sub queries
SelectConverter selectConverter = getSelectConverter();
delete.getWhere().accept(selectConverter.getExpressionVisitor());
checkEntitlements(context, selectConverter.getContext());
return jdbc.update(delete.toString()) > 0;
} catch (ClassCastException e) {
throw RaptureExceptionFactory.create("Not a delete statement: " + sql);
} catch (JSQLParserException e) {
throw RaptureExceptionFactory.create("Failed to parse statement: " + sql, e);
}
}
@Override
public Boolean deleteRows(String tableName, String where) {
List args = new ArrayList<>();
where = convertToPreparedStatement(where, args);
int res = jdbc.update(sqlGenerator.constructDelete(schema, tableName, where), args.toArray());
return res > 0;
}
public Boolean createIndex(String tableName, String indexName, List columnNames) {
jdbc.execute(sqlGenerator.constructCreateIndex(schema, tableName, indexName, columnNames));
return true;
}
@Override
public Boolean dropIndex(String indexName) {
jdbc.execute(sqlGenerator.constructDropIndex(schema, indexName));
return true;
}
@Override
public List getIndexes(String tableName) {
List result = new ArrayList<>();
String sql = sqlGenerator.constructGetIndexes(schema, tableName);
for (Map row : jdbc.queryForList(sql)) {
String indexName = (String) row.get("indexname");
String indexDef = (String) row.get("indexdef");
int beginIndex = indexDef.lastIndexOf("(");
int endIndex = indexDef.lastIndexOf(")");
String columns = indexDef.substring(beginIndex + 1, endIndex);
TableIndex index = new TableIndex();
index.setName(indexName);
index.setColumns(Arrays.asList(columns.split(", ")));
result.add(index);
}
return result;
}
@Override
public String getPrimaryKey(String tableName) {
String sql = sqlGenerator.constructGetPrimaryKey(schema, tableName);
return jdbc.queryForObject(sql, String.class);
}
@Override
public List getForeignKeys(String tableName) {
List foreignKeys = new ArrayList<>();
String sql = sqlGenerator.constructGetForeignKeys(schema, tableName);
for (Map row : jdbc.queryForList(sql)) {
ForeignKey key = new ForeignKey();
key.setColumn((String) row.get("column_name"));
key.setForeignTable((String) row.get("foreign_table_name"));
key.setForeignColumn((String) row.get("foreign_column_name"));
foreignKeys.add(key);
}
return foreignKeys;
}
@Override
public String getDdl(String table, Boolean includeTableData) {
StringBuilder ret = new StringBuilder(sqlGenerator.constructCreateTable(getDataSource(), schema, table, includeTableData));
if (includeTableData != null && includeTableData) {
ret.append(sqlGenerator.constructInserts(this, schema, table));
}
ret.append("// BEGIN ALTER_BLOCK DO NOT REMOVE THIS LINE\n\n");
ret.append("/*****\n");
ret.append("If the schema is edited provide commands here to match any changes made above\n");
ret.append("Ensure that commands can be executed safely more than once.\n");
ret.append("Examples:\n\n");
ret.append("ALTER TABLE tablename DROP COLUMN IF EXISTS oldcolumn ;\n");
ret.append("DO $$\n");
ret.append("BEGIN\n");
ret.append(" ALTER TABLE tablename ADD COLUMN columnname TEXT ;\n");
ret.append("EXCEPTION\n");
ret.append(" WHEN duplicate_column THEN null;\n");
ret.append("END;\n");
ret.append("$$;\n");
ret.append("*****/\n");
return ret.toString();
}
@Override
public String getCursorUsingSql(CallingContext context, String sql) {
try {
// check entitlements on tables
Select select = (Select) CCJSqlParserUtil.parse(sql);
SelectConverter selectConverter = getSelectConverter();
select.getSelectBody().accept(selectConverter);
checkEntitlements(context, selectConverter.getContext());
// execute query
ResultSet rs = getPreparedStatementForCursor(sql).executeQuery();
String uuid = IDGenerator.getUUID();
cache.putCursor(uuid, rs);
return uuid;
} catch (ClassCastException e) {
throw RaptureExceptionFactory.create("Not a select statement: " + sql, e);
} catch (JSQLParserException e) {
throw RaptureExceptionFactory.create("Failed to parse statement: " + sql, e);
} catch (SQLException e) {
throw RaptureExceptionFactory.create(String.format("Sql Exception executing cursor query [%s]", e.getMessage()));
}
}
/**
* Get a forwards and backwards scrollable ResultSet that is read-only
*
* @param sql
* @return
*/
private PreparedStatement getPreparedStatementForCursor(String sql) throws SQLException {
return jdbc.getDataSource().getConnection()
.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
}
@Override
public String getCursor(String table, List columnNames, String where, List order, Boolean ascending, int limit) {
try {
ResultSet rs = getPreparedStatementForCursor(sqlGenerator.constructSelect(schema, table, columnNames, where, order, ascending, limit))
.executeQuery();
String uuid = IDGenerator.getUUID();
cache.putCursor(uuid, rs);
return uuid;
} catch (SQLException e) {
throw RaptureExceptionFactory.create(String.format("Sql Exception executing cursor query [%s]", e.getMessage()));
}
}
@Override
public String getCursorForJoin(List tables, List columnNames, String from,
String where, List order, Boolean ascending, int limit) {
List args = new ArrayList<>();
where = convertToPreparedStatement(where, args);
String sql = sqlGenerator.constructSelectJoin(tables, columnNames, from, where, order, ascending, limit);
try {
PreparedStatement pstmt = getPreparedStatementForCursor(sql);
for (int i = 0; i < args.size(); i++) {
StatementCreatorUtils.setParameterValue(pstmt, i + 1, SqlTypeValue.TYPE_UNKNOWN, args.get(i));
}
ResultSet rs = pstmt.executeQuery();
String uuid = IDGenerator.getUUID();
cache.putCursor(uuid, rs);
return uuid;
} catch (SQLException e) {
throw RaptureExceptionFactory.create(String.format("Sql Exception executing cursor for joined query [%s]", e.getMessage()));
}
}
@Override
public List> next(String table, String cursorId, int count) {
return getCursorResult(cursorId, count, true);
}
@Override
public List> previous(String table, String cursorId, int count) {
return getCursorResult(cursorId, count, false);
}
@Override
public Boolean closeCursor(String table, String cursorId) {
cache.removeCursor(cursorId);
return true;
}
private List> getCursorResult(String cursorId, int count, boolean isForward) {
ResultSet rs = cache.getCursor(cursorId);
if (rs == null) {
throw RaptureExceptionFactory.create(String.format("Invalid cursorId [%s] provided. No existing cursor in cache.", cursorId));
}
try {
int currentCount = 0;
ResultSetMetaData rsmd = rs.getMetaData();
int numColumns = rsmd.getColumnCount();
List> ret = new ArrayList>();
while (currentCount++ < count && !rs.isClosed() && (isForward ? rs.next() : rs.previous())) {
Map row = new TreeMap(String.CASE_INSENSITIVE_ORDER);
for (int i = 1; i <= numColumns; i++) {
row.put(rsmd.getColumnLabel(i), rs.getObject(i));
}
ret.add(row);
}
return ret.isEmpty() ? null : ret;
} catch (SQLException e) {
log.error(ExceptionToString.format(e));
throw RaptureExceptionFactory.create(String.format("SQL Exception while traversing ResultSet: [%s]", e.getMessage()));
}
}
@Override
public void executeDdl(String ddl) {
executeDdl(ddl, false);
}
//@Override
public void executeAlterDdl(String ddl) {
executeDdl(ddl, true);
}
private void executeDdl(String ddl, boolean alter) {
List createStatements = new ArrayList<>();
List alterStatements = new ArrayList<>();
List updateStatements = new ArrayList<>();
Scanner scanner = new Scanner(ddl);
boolean inAlterBlock = false;
while (scanner.hasNextLine()) {
String line = scanner.nextLine();
if (line.contains("ALTER_BLOCK")) {
inAlterBlock = !inAlterBlock;
} else if (line.startsWith("INSERT INTO")) {
updateStatements.add(line);
} else if (inAlterBlock) {
alterStatements.add(line);
} else {
createStatements.add(line);
}
}
scanner.close();
if (alter) {
jdbc.update(StringUtils.join(alterStatements, "\n"));
} else {
jdbc.execute(StringUtils.join(createStatements, "\n"));
if (!CollectionUtils.isEmpty(updateStatements)) {
jdbc.batchUpdate(updateStatements.toArray(new String[updateStatements.size()]));
}
}
}
protected Boolean refreshColumnTypeCache(final String tableName) {
return jdbc.query(sqlGenerator.constructSelect(schema, tableName, null, "1=0", null, null, -1), new ResultSetExtractor() {
@Override
public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException {
ResultSetMetaData rsmd = rs.getMetaData();
Map columnType = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
columnType.put(rsmd.getColumnLabel(i), rsmd.getColumnType(i));
}
cache.putColumnTypes(tableName, columnType);
return true;
}
});
}
public Boolean createProcedureCallUsingSql(CallingContext context, String rawSql) {
try {
// TODO RAP-3548 Need to parse rawSql and check entitlements
// Execute query
jdbc.execute(rawSql);
return true;
} catch (BadSqlGrammarException e) {
log.error(e.getSQLException());
return false;
}
}
public StoredProcedureResponse callProcedure(CallingContext context, String procName, StoredProcedureParams params) {
// TODO RAP-3548 Need to check entitlements
SimpleJdbcCall call = new SimpleJdbcCall(jdbc)
.withProcedureName(procName)
.withoutProcedureColumnMetaDataAccess();
MapSqlParameterSource paramSource = new MapSqlParameterSource();
Map inParams = params.getInParams();
Map outParams = params.getOutParams();
Map inOutParams = params.getInOutParams();
if (inParams != null) {
// Declare Parameters
Map inParamTypes = getInputParamTypes(inParams);
for (Map.Entry entry : inParamTypes.entrySet()) {
call.declareParameters(new SqlParameter(entry.getKey(), entry.getValue()));
}
// Give Input Parameters
for (Map.Entry entry : inParams.entrySet()) {
paramSource.addValue(entry.getKey(), entry.getValue());
}
}
if (inOutParams != null) {
Map inOutParamTypes = getInputParamTypes(inOutParams);
for (Map.Entry entry : inOutParamTypes.entrySet()) {
call.declareParameters(new SqlInOutParameter(entry.getKey(), entry.getValue()));
}
// Give Input Parameters
for (Map.Entry entry : inOutParams.entrySet()) {
paramSource.addValue(entry.getKey(), entry.getValue());
}
}
if (outParams != null) {
for (Map.Entry entry : outParams.entrySet()) {
call.declareParameters(new SqlOutParameter(entry.getKey(), entry.getValue()));
}
}
try {
return packageStoredProcedureReturn(call.execute(paramSource), true);
} catch (BadSqlGrammarException e) {
log.error(e.getSQLException());
return packageStoredProcedureReturn(null, false);
}
}
public Boolean dropProcedureUsingSql(CallingContext context, String rawSql) {
try {
// TODO RAP-3548 Need to parse rawSql and check entitlements
// Execute query
jdbc.execute(rawSql);
return true;
} catch (BadSqlGrammarException e) {
log.error(e.getSQLException());
return false;
}
}
protected Map getInputParamTypes(Map inParams) {
Map retMap = new HashMap();
for (Map.Entry entry : inParams.entrySet()) {
String clazz = entry.getValue().getClass().getSimpleName();
switch (clazz) {
case "String":
retMap.put(entry.getKey(), Types.VARCHAR);
break;
case "Integer":
retMap.put(entry.getKey(), Types.INTEGER);
break;
case "Float":
retMap.put(entry.getKey(), Types.FLOAT);
break;
case "Double":
retMap.put(entry.getKey(), Types.DOUBLE);
break;
case "Character":
retMap.put(entry.getKey(), Types.CHAR);
break;
case "Boolean":
retMap.put(entry.getKey(), Types.BOOLEAN);
break;
default:
throw RaptureExceptionFactory.create("Unsupported class for param type");
}
}
return retMap;
}
private StoredProcedureResponse packageStoredProcedureReturn(Map returnedObj, Boolean callSuccessful) {
StoredProcedureResponse spr = new StoredProcedureResponse();
spr.setCallSuccessful(callSuccessful);
if (callSuccessful) {
spr.setSingleValueReturn(returnedObj);
}
return spr;
}
/**
* Used to get the SQL types of a table name for use with PreparedStatements
*
* The 'refreshOnce' argument is used in case the cache is not populated yet, it will try to populate it once and then try again.
*
* @param tableName
* @param columnNames
* @param refreshOnce
* @return
*/
private int[] getTypes(String tableName, List columnNames, boolean refreshOnce) {
Map columnType = cache.getColumnTypes(tableName);
if (columnType == null) {
if (refreshOnce) {
refreshColumnTypeCache(tableName);
return getTypes(tableName, columnNames, false);
} else {
throw RaptureExceptionFactory.create(String.format("Invalid table specified as an argument: [%s]", tableName));
}
}
int[] types = new int[columnNames.size()];
for (int i = 0; i < types.length; i++) {
Integer ctype = columnType.get(columnNames.get(i));
if (ctype == null) {
if (refreshOnce) {
refreshColumnTypeCache(tableName);
return getTypes(tableName, columnNames, false);
} else {
throw RaptureExceptionFactory.create(String.format("Invalid column specified as an argument: [%s]", columnNames.get(i)));
}
}
types[i] = ctype;
}
return types;
}
/**
* given an array of integers make 'multiple' copies of it and then flatten into a single array
*
* @param multiple
* @param types
* @return
*/
private int[] multiplyAndFlatten(int multiple, int[] types) {
int[] ret = new int[multiple * types.length];
int index = 0;
for (int i = 0; i < multiple; i++) {
for (int j = 0; j < types.length; j++) {
ret[index++] = types[j];
}
}
return ret;
}
}
