001package com.identityworksllc.iiq.common.table; 002 003import com.identityworksllc.iiq.common.iterators.ColumnConfig; 004import com.identityworksllc.iiq.common.iterators.ResultSetIterator; 005import com.identityworksllc.iiq.common.query.NamedParameterStatement; 006import sailpoint.api.SailPointContext; 007import sailpoint.tools.GeneralException; 008import sailpoint.tools.JdbcUtil; 009import sailpoint.tools.Util; 010 011import java.sql.Connection; 012import java.sql.ResultSet; 013import java.sql.SQLException; 014import java.util.ArrayList; 015import java.util.Arrays; 016import java.util.List; 017import java.util.Map; 018import java.util.Objects; 019import java.util.concurrent.atomic.AtomicBoolean; 020 021/** 022 * An extension of Table to run a SQL query and export it. You must provide a 023 * Connection (or way of getting one) and a list of column specs. 024 * 025 * The meat of the querying takes place in {@link ResultSetIterator}. 026 */ 027public class QueryTable implements AutoCloseable, StyleTarget { 028 /** 029 * The column specs, recognized by {@link ColumnConfig}. At this 030 * time that is Strings, other ColumnConfig objects (which will be cloned), or 031 * ReportColumnConfig objects. 032 */ 033 private final List<Object> columns; 034 /** 035 * The connection, which is assumed open until close() is invoked 036 */ 037 private final Connection connection; 038 /** 039 * The context 040 */ 041 private final SailPointContext context; 042 043 /** 044 * Set to true on render() 045 */ 046 private final AtomicBoolean frozen; 047 048 /** 049 * The Table to be populated by the query output 050 */ 051 private final Table table; 052 053 /** 054 * Constructs a n ew QueryTable with the given context, connection, and column 055 * specifications. The column specs should be some object recognized by 056 * the reporting class {@link ColumnConfig}. 057 * 058 * @param context The context 059 * @param connection The SQL connection, which must be open and ready to query 060 * @param columns A non-empty list of column specs 061 */ 062 public QueryTable(SailPointContext context, Connection connection, List<Object> columns) { 063 this.connection = Objects.requireNonNull(connection); 064 this.context = Objects.requireNonNull(context); 065 066 if (columns == null || columns.isEmpty()) { 067 throw new IllegalArgumentException("For QueryTable, 'columns' must contain at least one column specification"); 068 } 069 070 this.table = new Table(); 071 this.columns = new ArrayList<>(columns); 072 this.frozen = new AtomicBoolean(); 073 } 074 075 /** 076 * Constructs a new QueryTable with the given context and connection, as well 077 * as a string list of column tokens. 078 * 079 * @param context The context 080 * @param connection The SQL connection, which must be open and ready to query 081 * @param columns A non-empty list of column tokens 082 */ 083 public QueryTable(SailPointContext context, Connection connection, String... columns) { 084 this(context, connection, Arrays.asList(columns)); 085 } 086 087 /** 088 * Constructs a new QueryTable with the given context and connection info, as well 089 * as a string list of column tokens. 090 * 091 * @param context The context 092 * @param connectionInfo A map of connection info, with the same keys specified by connectors 093 * @param columns A non-empty list of column tokens 094 */ 095 public QueryTable(SailPointContext context, Map<String, Object> connectionInfo, String... columns) throws GeneralException { 096 this(context, JdbcUtil.getConnection(connectionInfo), columns); 097 } 098 099 /** 100 * Constructs a new QueryTable with the given context and connection info, as well 101 * as a string list of column tokens. 102 * 103 * @param context The context 104 * @param connectionInfo A map of connection info, with the same keys specified by connectors 105 * @param columns A non-empty list of column specs 106 */ 107 public QueryTable(SailPointContext context, Map<String, Object> connectionInfo, List<Object> columns) throws GeneralException { 108 this(context, JdbcUtil.getConnection(connectionInfo), columns); 109 } 110 111 /** 112 * Adds an output column to this QueryTable 113 * 114 * @param columnConfig The column config 115 * @return This object, for call chaining 116 */ 117 public QueryTable addColumn(Object columnConfig) { 118 if (columnConfig != null) { 119 this.columns.add(columnConfig); 120 } 121 return this; 122 } 123 124 /** 125 * Closes the connection 126 * 127 * @throws SQLException on failure to close the connection 128 */ 129 @Override 130 public void close() throws SQLException { 131 if (this.connection != null) { 132 this.connection.close(); 133 } 134 } 135 136 /** 137 * Executes the given query with the given options. The query will be run 138 * via {@link NamedParameterStatement}, so the arguments must be of a type 139 * recognized by that class. 140 * 141 * @param queryString The query string, which must not be null or empty 142 * @param arguments The list of arguments, if any 143 * @return This object, for call chaining 144 * @throws SQLException if any SQL failures occur 145 * @throws GeneralException if any IIQ failures occur 146 */ 147 public QueryTable executeQuery(String queryString, Map<String, Object> arguments) throws SQLException, GeneralException { 148 if (this.frozen.get()) { 149 throw new IllegalArgumentException("QueryTable.executeQuery() cannot be invoked twice for the same table"); 150 } 151 if (Util.isNullOrEmpty(queryString)) { 152 throw new IllegalArgumentException("The query passed to executeQuery() must not be null"); 153 } 154 try (NamedParameterStatement statement = new NamedParameterStatement(connection, queryString)) { 155 statement.setParameters(arguments); 156 try (ResultSet results = statement.executeQuery()) { 157 ResultSetIterator rsi = new ResultSetIterator(results, this.columns, context); 158 159 // This is a ListOrderedMap, so the keys ought to be in order 160 Map<String, String> fieldHeaderMap = rsi.getFieldHeaderMap(); 161 table.row().header(); 162 for(String key : fieldHeaderMap.keySet()) { 163 String header = fieldHeaderMap.get(key); 164 table.cell(header); 165 } 166 167 while(rsi.hasNext()) { 168 Map<String, Object> nextRow = rsi.nextRow(); 169 table.row(); 170 for(String key : fieldHeaderMap.keySet()) { 171 Object value = nextRow.get(key); 172 if (value == null) { 173 table.cell(""); 174 } else if (value instanceof List) { 175 table.cell(value); 176 } else { 177 table.cell(Util.otoa(value)); 178 } 179 } 180 } 181 182 this.frozen.set(true); 183 } 184 } 185 186 return this; 187 } 188 189 /** 190 * Gets the underlying table's CSS classes 191 * @see Table#getCssClasses() 192 */ 193 @Override 194 public List<String> getCssClasses() { 195 return table.getCssClasses(); 196 } 197 198 /** 199 * @see Table#getStyle() 200 */ 201 @Override 202 public String getStyle() { 203 return table.getStyle(); 204 } 205 206 /** 207 * Renders the table resulting from a query 208 * @return The rendered HTML 209 * @see Table#render() 210 */ 211 public String render() { 212 if (!this.frozen.get()) { 213 throw new IllegalStateException("Execute a query via executeQuery() first"); 214 } 215 return this.table.render(); 216 } 217 218 /** 219 * @see Table#setColumnCellOptions(int, CellOption...) 220 */ 221 public QueryTable setColumnCellOptions(int column, CellOption... options) throws GeneralException { 222 table.setColumnCellOptions(column, options); 223 return this; 224 } 225 226 /** 227 * @see Table#setColumnStyle(int, String) 228 */ 229 public QueryTable setColumnStyle(int column, String style) { 230 table.setColumnStyle(column, style); 231 return this; 232 } 233 234 /** 235 * @see Table#setCssClasses(List) 236 */ 237 public void setCssClasses(List<String> cssClasses) { 238 table.setCssClasses(cssClasses); 239 } 240 241 /** 242 * @see Table#setExtraColumnStyle(int, String) 243 */ 244 public QueryTable setExtraColumnStyle(int column, String style) { 245 table.setExtraColumnStyle(column, style); 246 return this; 247 } 248 249 /** 250 * @see Table#setRowCellOptions(int, CellOption...) 251 */ 252 public QueryTable setRowCellOptions(int row, CellOption... options) throws GeneralException { 253 table.setRowCellOptions(row, options); 254 return this; 255 } 256 257 /** 258 * @see Table#setRowStyle(int, String) 259 */ 260 public QueryTable setRowStyle(int row, String style) { 261 table.setRowStyle(row, style); 262 return this; 263 } 264 265 /** 266 * @see Table#setStyle(String) 267 */ 268 public void setStyle(String style) { 269 table.setStyle(style); 270 } 271 272 /** 273 * @see Table#withClass(String...) 274 */ 275 public QueryTable withClass(String... cssClasses) { 276 table.withClass(cssClasses); 277 return this; 278 } 279 280 /** 281 * @see Table#withHeaderRow(CellOption...) 282 */ 283 public QueryTable withHeaderRow(CellOption... options) throws GeneralException { 284 table.withHeaderRow(options); 285 return this; 286 } 287 288 289}