Java tutorial
/** * Copyright (C) 2011-2015 Incapture Technologies LLC * <p> * This is an autogenerated license statement. When copyright notices appear below * this one that copyright supercedes this statement. * <p> * 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. * <p> * 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 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.JdbcTemplate; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.core.SqlInOutParameter; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.core.SqlParameter; 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 com.google.common.collect.ImmutableMap; 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 rapture.common.CallingContext; import rapture.common.EntitlementSet; import rapture.common.ForeignKey; import rapture.common.IEntitlementsContext; import rapture.common.StoredProcedureParams; import rapture.common.StoredProcedureResponse; import rapture.common.TableIndex; import rapture.common.TableMeta; 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; /** * 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<String, String> 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<String, String> 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<String> getTables() { String sql = sqlGenerator.constructListTables(schema); return jdbc.queryForList(sql, String.class); } @Override public TableMeta describeTable(String tableName) { Map<String, String> result = new HashMap<>(); String sql = sqlGenerator.constructDescribeTable(schema, tableName); for (Map<String, Object> 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<String, String> columns) { for (Map.Entry<String, String> 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<String> columnNames) { for (String columnName : columnNames) { jdbc.execute(sqlGenerator.constructDeleteTableColumns(schema, tableName, Arrays.asList(columnName))); } return refreshColumnTypeCache(tableName); } @Override public Boolean updateTableColumns(String tableName, Map<String, String> columns) { for (Map.Entry<String, String> 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<String, String> columnNames) { for (Map.Entry<String, String> 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<String, ?> values) { List<String> 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<? extends Map<String, ?>> values) { List<String> columnNames = new ArrayList<>(values.get(0).keySet()); List<List<String>> allCols = new ArrayList<>(); List<Object> allVals = new ArrayList<>(); for (Map<String, ?> 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<Map<String, Object>> 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; } @Override public List<Map<String, Object>> selectJoinedRows(List<String> tables, List<String> columnNames, String from, String where, List<String> order, Boolean ascending, int limit) { List<? super Object> 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<Map<String, Object>> selectRows(String table, final List<String> columnNames, String where, List<String> order, Boolean ascending, int limit) { List<? super Object> 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<List<Map<String, Object>>> createResultSetExtractor() { return new ResultSetExtractor<List<Map<String, Object>>>() { @Override public List<Map<String, Object>> extractData(ResultSet rs) throws SQLException, DataAccessException { List<Map<String, Object>> ret = new ArrayList<>(); ResultSetMetaData rsmd = rs.getMetaData(); int numColumns = rsmd.getColumnCount(); while (rs.next()) { Map<String, Object> 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<String, ?> values, String where) { List<String> 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<? super Object> args = new ArrayList<>(); where = convertToPreparedStatement(where, args); int res = jdbc.update(sqlGenerator.constructDelete(schema, tableName, where), args.toArray()); return res > 0; } @Override public Boolean createIndex(String tableName, String indexName, List<String> 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<TableIndex> getIndexes(String tableName) { List<TableIndex> result = new ArrayList<>(); String sql = sqlGenerator.constructGetIndexes(schema, tableName); for (Map<String, Object> 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<ForeignKey> getForeignKeys(String tableName) { List<ForeignKey> foreignKeys = new ArrayList<>(); String sql = sqlGenerator.constructGetForeignKeys(schema, tableName); for (Map<String, Object> 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<String> columnNames, String where, List<String> 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<String> tables, List<String> columnNames, String from, String where, List<String> order, Boolean ascending, int limit) { List<? super Object> 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<Map<String, Object>> next(String table, String cursorId, int count) { return getCursorResult(cursorId, count, true); } @Override public List<Map<String, Object>> 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<Map<String, Object>> 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<Map<String, Object>> ret = new ArrayList<>(); while (currentCount++ < count && !rs.isClosed() && (isForward ? rs.next() : rs.previous())) { Map<String, Object> 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, boolean alter) { List<String> createStatements = new ArrayList<>(); List<String> alterStatements = new ArrayList<>(); List<String> 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<Boolean>() { @Override public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException { ResultSetMetaData rsmd = rs.getMetaData(); Map<String, Integer> 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; } }); } @Override 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; } } @Override 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<String, Object> inParams = (params == null) ? null : params.getInParams(); Map<String, Integer> outParams = (params == null) ? null : params.getOutParams(); Map<String, Object> inOutParams = (params == null) ? null : params.getInOutParams(); if (inParams != null) { // Declare Parameters Map<String, Integer> inParamTypes = getInputParamTypes(inParams); for (Map.Entry<String, Integer> entry : inParamTypes.entrySet()) { call.declareParameters(new SqlParameter(entry.getKey(), entry.getValue())); } // Give Input Parameters for (Map.Entry<String, Object> entry : inParams.entrySet()) { paramSource.addValue(entry.getKey(), entry.getValue()); } } if (inOutParams != null) { Map<String, Integer> inOutParamTypes = getInputParamTypes(inOutParams); for (Map.Entry<String, Integer> entry : inOutParamTypes.entrySet()) { call.declareParameters(new SqlInOutParameter(entry.getKey(), entry.getValue())); } // Give Input Parameters for (Map.Entry<String, Object> entry : inOutParams.entrySet()) { paramSource.addValue(entry.getKey(), entry.getValue()); } } if (outParams != null) { for (Map.Entry<String, Integer> 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); } } @Override 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<String, Integer> getInputParamTypes(Map<String, Object> inParams) { Map<String, Integer> retMap = new HashMap<>(); for (Map.Entry<String, Object> 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<String> columnNames, boolean refreshOnce) { Map<String, Integer> 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; } }