1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 23 package com.sun.star.report; 24 25 import com.sun.star.beans.PropertyVetoException; 26 import com.sun.star.beans.UnknownPropertyException; 27 import com.sun.star.beans.XPropertySet; 28 import com.sun.star.container.NoSuchElementException; 29 import com.sun.star.container.XIndexAccess; 30 import com.sun.star.container.XNameAccess; 31 import com.sun.star.lang.IllegalArgumentException; 32 import com.sun.star.lang.IndexOutOfBoundsException; 33 import com.sun.star.lang.WrappedTargetException; 34 import com.sun.star.lang.XComponent; 35 import com.sun.star.sdb.CommandType; 36 import com.sun.star.sdb.XCompletedExecution; 37 import com.sun.star.sdb.XParametersSupplier; 38 import com.sun.star.sdb.XQueriesSupplier; 39 import com.sun.star.sdb.XSingleSelectQueryComposer; 40 import com.sun.star.sdb.tools.XConnectionTools; 41 import com.sun.star.sdbc.SQLException; 42 import com.sun.star.sdbc.XConnection; 43 import com.sun.star.sdbc.XParameters; 44 import com.sun.star.sdbc.XRowSet; 45 import com.sun.star.task.XInteractionHandler; 46 import com.sun.star.uno.Exception; 47 import com.sun.star.uno.UnoRuntime; 48 import com.sun.star.uno.XComponentContext; 49 50 import java.lang.reflect.Method; 51 52 import java.math.BigDecimal; 53 54 import java.util.ArrayList; 55 import java.util.HashMap; 56 import java.util.Iterator; 57 import java.util.List; 58 import java.util.Map; 59 import java.util.logging.Level; 60 import java.util.logging.Logger; 61 62 import org.apache.commons.logging.Log; 63 import org.apache.commons.logging.LogFactory; 64 65 66 /** 67 * Very primitive implementation, just to show how this could be used ... 68 * 69 */ 70 public class SDBCReportDataFactory implements DataSourceFactory 71 { 72 73 private static final String ESCAPEPROCESSING = "EscapeProcessing"; 74 75 private class RowSetProperties 76 { 77 78 final Boolean escapeProcessing; 79 final int commandType; 80 final Integer maxRows; 81 final String command; 82 final String filter; 83 RowSetProperties(final Boolean escapeProcessing, final int commandType, final String command, final String filter, final Integer maxRows)84 public RowSetProperties(final Boolean escapeProcessing, final int commandType, final String command, final String filter, final Integer maxRows) 85 { 86 this.escapeProcessing = escapeProcessing; 87 this.commandType = commandType; 88 this.command = command; 89 this.filter = filter; 90 this.maxRows = maxRows; 91 } 92 equals(Object obj)93 public boolean equals(Object obj) 94 { 95 if (obj == null) 96 { 97 return false; 98 } 99 if (getClass() != obj.getClass()) 100 { 101 return false; 102 } 103 final RowSetProperties other = (RowSetProperties) obj; 104 if (this.escapeProcessing != other.escapeProcessing && (this.escapeProcessing == null || !this.escapeProcessing.equals(other.escapeProcessing))) 105 { 106 return false; 107 } 108 if (this.commandType != other.commandType) 109 { 110 return false; 111 } 112 if (this.maxRows != other.maxRows && (this.maxRows == null || !this.maxRows.equals(other.maxRows))) 113 { 114 return false; 115 } 116 if ((this.command == null) ? (other.command != null) : !this.command.equals(other.command)) 117 { 118 return false; 119 } 120 if ((this.filter == null) ? (other.filter != null) : !this.filter.equals(other.filter)) 121 { 122 return false; 123 } 124 return true; 125 } 126 hashCode()127 public int hashCode() 128 { 129 int hash = 3; 130 hash = 59 * hash + (this.escapeProcessing != null ? this.escapeProcessing.hashCode() : 0); 131 hash = 59 * hash + this.commandType; 132 hash = 59 * hash + (this.maxRows != null ? this.maxRows.hashCode() : 0); 133 hash = 59 * hash + (this.command != null ? this.command.hashCode() : 0); 134 hash = 59 * hash + (this.filter != null ? this.filter.hashCode() : 0); 135 return hash; 136 } 137 } 138 139 class ParameterDefinition 140 { 141 142 int parameterCount = 0; 143 private ArrayList parameterIndex = new ArrayList(); 144 } 145 private static final Log LOGGER = LogFactory.getLog(SDBCReportDataFactory.class); 146 public static final String COMMAND_TYPE = "command-type"; 147 public static final String ESCAPE_PROCESSING = "escape-processing"; 148 public static final String GROUP_EXPRESSIONS = "group-expressions"; 149 public static final String MASTER_VALUES = "master-values"; 150 public static final String MASTER_COLUMNS = "master-columns"; 151 public static final String DETAIL_COLUMNS = "detail-columns"; 152 public static final String UNO_FILTER = "Filter"; 153 private static final String APPLY_FILTER = "ApplyFilter"; 154 private static final String UNO_COMMAND = "Command"; 155 private static final String UNO_ORDER = "Order"; 156 private static final String UNO_APPLY_FILTER = "ApplyFilter"; 157 private static final String UNO_COMMAND_TYPE = "CommandType"; 158 private final XConnection connection; 159 private final XComponentContext m_cmpCtx; 160 private static final int FAILED = 0; 161 private static final int DONE = 1; 162 private static final int RETRIEVE_COLUMNS = 2; 163 private static final int RETRIEVE_OBJECT = 3; 164 private static final int HANDLE_QUERY = 4; 165 private static final int HANDLE_TABLE = 5; 166 private static final int HANDLE_SQL = 6; 167 private final Map rowSetProperties = new HashMap(); 168 private final Map parameterMap = new HashMap(); 169 private boolean rowSetCreated = false; 170 SDBCReportDataFactory(final XComponentContext cmpCtx, final XConnection connection)171 public SDBCReportDataFactory(final XComponentContext cmpCtx, final XConnection connection) 172 { 173 this.connection = connection; 174 m_cmpCtx = cmpCtx; 175 } 176 queryData(final String command, final Map parameters)177 public DataSource queryData(final String command, final Map parameters) throws DataSourceException 178 { 179 try 180 { 181 if (command == null) 182 { 183 return new SDBCReportData(null); 184 } 185 int commandType = CommandType.COMMAND; 186 final String commandTypeValue = (String) parameters.get(COMMAND_TYPE); 187 if (commandTypeValue != null) 188 { 189 if ("query".equals(commandTypeValue)) 190 { 191 commandType = CommandType.QUERY; 192 } 193 else if ("table".equals(commandTypeValue)) 194 { 195 commandType = CommandType.TABLE; 196 } 197 else 198 { 199 commandType = CommandType.COMMAND; 200 } 201 } 202 final Boolean escapeProcessing = (Boolean) parameters.get(ESCAPE_PROCESSING); 203 final String filter = (String) parameters.get(UNO_FILTER); 204 final Integer maxRows = (Integer) parameters.get("MaxRows"); 205 final RowSetProperties rowSetProps = new RowSetProperties(escapeProcessing, commandType, command, filter, maxRows); 206 207 final Object[] p = createRowSet(rowSetProps, parameters); 208 final XRowSet rowSet = (XRowSet) p[0]; 209 210 if (command.length() != 0) 211 { 212 final ParameterDefinition paramDef = (ParameterDefinition) p[1]; 213 fillParameter(parameters, rowSet, paramDef); 214 rowSetCreated = rowSetCreated && (maxRows == null || maxRows == 0); 215 216 final XCompletedExecution execute = (XCompletedExecution) UnoRuntime.queryInterface(XCompletedExecution.class, rowSet); 217 if (rowSetCreated && execute != null && paramDef.parameterCount > 0) 218 { 219 final XInteractionHandler handler = (XInteractionHandler) UnoRuntime.queryInterface(XInteractionHandler.class, m_cmpCtx.getServiceManager().createInstanceWithContext("com.sun.star.sdb.InteractionHandler", m_cmpCtx)); 220 execute.executeWithCompletion(handler); 221 } 222 else 223 { 224 rowSet.execute(); 225 } 226 } 227 228 rowSetCreated = false; 229 return new SDBCReportData(rowSet); 230 } 231 catch (Exception ex) 232 { 233 rowSetCreated = false; 234 throw new DataSourceException(ex.getMessage(), ex); 235 } 236 } 237 getOrderStatement(final int commandType, final String command, final List groupExpressions)238 private String getOrderStatement(final int commandType, final String command, final List groupExpressions) 239 { 240 final StringBuffer order = new StringBuffer(); 241 final int count = groupExpressions.size(); 242 if (count != 0) 243 { 244 try 245 { 246 final String quote = connection.getMetaData().getIdentifierQuoteString(); 247 final XComponent[] hold = new XComponent[1]; 248 final XNameAccess columns = getFieldsByCommandDescriptor(commandType, command, hold); 249 if (columns != null) 250 { 251 for (int i = 0; i < count; i++) 252 { 253 final Object[] pair = (Object[]) groupExpressions.get(i); 254 String expression = (String) pair[0]; 255 256 if (!expression.startsWith(quote) && columns.hasByName(expression)) 257 { 258 XPropertySet column; 259 try 260 { 261 column = UnoRuntime.queryInterface(XPropertySet.class, columns.getByName(expression)); 262 expression = quote + column.getPropertyValue("TableName") + quote + "." + quote + expression + quote; 263 } 264 catch (Exception ex) 265 { 266 Logger.getLogger(SDBCReportDataFactory.class.getName()).log(Level.SEVERE, null, ex); 267 expression = quote + expression + quote; 268 } 269 } 270 expression = expression.trim(); // Trim away white spaces 271 272 if (expression.length() > 0) 273 { 274 order.append(expression); 275 if (order.length() > 0) 276 { 277 order.append(' '); 278 } 279 final String sorting = (String) pair[1]; 280 if (sorting == null || sorting.equals(OfficeToken.FALSE)) 281 { 282 order.append("DESC"); 283 } 284 if ((i + 1) < count) 285 { 286 order.append(", "); 287 } 288 } 289 } 290 } 291 } 292 catch (SQLException ex) 293 { 294 LOGGER.error("ReportProcessing failed", ex); 295 } 296 } 297 return order.toString(); 298 } 299 getFieldsByCommandDescriptor(final int commandType, final String command, final XComponent[] out)300 private XNameAccess getFieldsByCommandDescriptor(final int commandType, final String command, final XComponent[] out) throws SQLException 301 { 302 final Class[] parameter = new Class[3]; 303 parameter[0] = int.class; 304 parameter[1] = String.class; 305 parameter[2] = out.getClass(); 306 final XConnectionTools tools = (XConnectionTools) UnoRuntime.queryInterface(XConnectionTools.class, connection); 307 try 308 { 309 tools.getClass().getMethod("getFieldsByCommandDescriptor", parameter); 310 return tools.getFieldsByCommandDescriptor(commandType, command, out); 311 } 312 catch (NoSuchMethodException ex) 313 { 314 } 315 316 throw new SQLException(); 317 } 318 getComposer(final XConnectionTools tools, final String command, final int commandType)319 private XSingleSelectQueryComposer getComposer(final XConnectionTools tools, 320 final String command, 321 final int commandType) 322 { 323 final Class[] parameter = new Class[2]; 324 parameter[0] = int.class; 325 parameter[1] = String.class; 326 try 327 { 328 final Object[] param = new Object[2]; 329 param[0] = commandType; 330 param[1] = command; 331 final Method call = tools.getClass().getMethod("getComposer", parameter); 332 return (XSingleSelectQueryComposer) call.invoke(tools, param); 333 } 334 catch (NoSuchMethodException ex) 335 { 336 } 337 catch (IllegalAccessException ex) 338 { 339 // should not happen 340 // assert False 341 } 342 catch (java.lang.reflect.InvocationTargetException ex) 343 { 344 // should not happen 345 // assert False 346 } 347 348 return null; 349 } 350 fillParameter(final Map parameters, final XRowSet rowSet, final ParameterDefinition paramDef)351 private void fillParameter(final Map parameters, 352 final XRowSet rowSet, final ParameterDefinition paramDef) 353 throws SQLException, 354 UnknownPropertyException, 355 PropertyVetoException, 356 IllegalArgumentException, 357 WrappedTargetException 358 { 359 final ArrayList masterValues = (ArrayList) parameters.get(MASTER_VALUES); 360 if (masterValues != null && !masterValues.isEmpty()) 361 { 362 final XParameters para = (XParameters) UnoRuntime.queryInterface(XParameters.class, rowSet); 363 364 for (int i = 0; 365 i < masterValues.size(); 366 i++) 367 { 368 Object object = masterValues.get(i); 369 if (object instanceof BigDecimal) 370 { 371 object = ((BigDecimal) object).toString(); 372 } 373 final Integer pos = (Integer) paramDef.parameterIndex.get(i); 374 para.setObject(pos + 1, object); 375 } 376 } 377 } 378 createRowSet(final RowSetProperties rowSetProps, final Map parameters)379 private final Object[] createRowSet(final RowSetProperties rowSetProps, final Map parameters) 380 throws Exception 381 { 382 final ArrayList detailColumns = (ArrayList) parameters.get(DETAIL_COLUMNS); 383 if (rowSetProperties.containsKey(rowSetProps) && detailColumns != null && !detailColumns.isEmpty()) 384 { 385 return new Object[] 386 { 387 rowSetProperties.get(rowSetProps), parameterMap.get(rowSetProps) 388 }; 389 } 390 391 rowSetCreated = true; 392 final XRowSet rowSet = (XRowSet) UnoRuntime.queryInterface(XRowSet.class, m_cmpCtx.getServiceManager().createInstanceWithContext("com.sun.star.sdb.RowSet", m_cmpCtx)); 393 final XPropertySet rowSetProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, rowSet); 394 395 rowSetProp.setPropertyValue("ActiveConnection", connection); 396 rowSetProp.setPropertyValue(ESCAPEPROCESSING, rowSetProps.escapeProcessing); 397 rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(rowSetProps.commandType)); 398 rowSetProp.setPropertyValue(UNO_COMMAND, rowSetProps.command); 399 400 if (rowSetProps.filter != null) 401 { 402 rowSetProp.setPropertyValue("Filter", rowSetProps.filter); 403 rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.valueOf(rowSetProps.filter.length() != 0)); 404 } 405 else 406 { 407 rowSetProp.setPropertyValue(APPLY_FILTER, Boolean.FALSE); 408 } 409 410 if (rowSetProps.maxRows != null) 411 { 412 rowSetProp.setPropertyValue("MaxRows", rowSetProps.maxRows); 413 } 414 415 final XConnectionTools tools = (XConnectionTools) UnoRuntime.queryInterface(XConnectionTools.class, connection); 416 fillOrderStatement(rowSetProps.command, rowSetProps.commandType, parameters, tools, rowSetProp); 417 final ParameterDefinition paramDef = createParameter(parameters, tools, rowSetProps, rowSet); 418 419 rowSetProperties.put(rowSetProps, rowSet); 420 parameterMap.put(rowSetProps, paramDef); 421 422 return new Object[] 423 { 424 rowSet, paramDef 425 }; 426 } 427 createParameter(final Map parameters, final XConnectionTools tools, RowSetProperties rowSetProps, final XRowSet rowSet)428 private ParameterDefinition createParameter(final Map parameters, 429 final XConnectionTools tools, 430 RowSetProperties rowSetProps, final XRowSet rowSet) 431 throws SQLException, 432 UnknownPropertyException, 433 PropertyVetoException, 434 IllegalArgumentException, 435 WrappedTargetException 436 { 437 final ParameterDefinition paramDef = new ParameterDefinition(); 438 final XSingleSelectQueryComposer composer = getComposer(tools, rowSetProps.command, rowSetProps.commandType); 439 if (composer != null) 440 { 441 final XPropertySet rowSetProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, rowSet); 442 if ((Boolean) rowSetProp.getPropertyValue(APPLY_FILTER)) 443 { 444 composer.setFilter((String) rowSetProp.getPropertyValue("Filter")); 445 } 446 // get old parameter count 447 final ArrayList detailColumns = (ArrayList) parameters.get(DETAIL_COLUMNS); 448 final ArrayList handledColumns = new ArrayList(); 449 final XParametersSupplier paraSup = (XParametersSupplier) UnoRuntime.queryInterface(XParametersSupplier.class, composer); 450 if (paraSup != null) 451 { 452 final XIndexAccess params = paraSup.getParameters(); 453 if (params != null) 454 { 455 final int oldParameterCount = params.getCount(); 456 paramDef.parameterCount = oldParameterCount; 457 if (detailColumns != null) 458 { 459 for (int i = 0; i < oldParameterCount; i++) 460 { 461 try 462 { 463 final XPropertySet parameter = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, params.getByIndex(i)); 464 if (parameter != null) 465 { 466 final String name = (String) parameter.getPropertyValue("Name"); 467 for (int j = 0; j < detailColumns.size(); j++) 468 { 469 if (name.equals(detailColumns.get(j))) 470 { 471 handledColumns.add(name); 472 paramDef.parameterIndex.add(i); 473 --paramDef.parameterCount; 474 break; 475 } 476 } 477 } 478 } 479 catch (IndexOutOfBoundsException ex) 480 { 481 Logger.getLogger(SDBCReportDataFactory.class.getName()).log(Level.SEVERE, null, ex); 482 } 483 } 484 } 485 } 486 } 487 final ArrayList masterValues = (ArrayList) parameters.get(MASTER_VALUES); 488 if (masterValues != null && !masterValues.isEmpty() && paramDef.parameterIndex.size() != detailColumns.size()) 489 { 490 // Vector masterColumns = (Vector) parameters.get("master-columns"); 491 492 // create the new filter 493 final String quote = connection.getMetaData().getIdentifierQuoteString(); 494 final StringBuffer oldFilter = new StringBuffer(); 495 oldFilter.append(composer.getFilter()); 496 if (oldFilter.length() != 0) 497 { 498 oldFilter.append(" AND "); 499 } 500 int newParamterCounter = 1; 501 for (final Iterator it = detailColumns.iterator(); it.hasNext(); 502 ++newParamterCounter) 503 { 504 final String detail = (String) it.next(); 505 if (!handledColumns.contains(detail)) 506 { 507 //String master = (String) masterIt.next(); 508 oldFilter.append(quote); 509 oldFilter.append(detail); 510 oldFilter.append(quote); 511 oldFilter.append(" = :link_"); 512 oldFilter.append(newParamterCounter); 513 if (it.hasNext()) 514 { 515 oldFilter.append(" AND "); 516 } 517 paramDef.parameterIndex.add(newParamterCounter + paramDef.parameterCount - 1); 518 } 519 } 520 521 composer.setFilter(oldFilter.toString()); 522 523 final String sQuery = composer.getQuery(); 524 rowSetProp.setPropertyValue(UNO_COMMAND, sQuery); 525 rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND)); 526 } 527 } 528 return paramDef; 529 } 530 fillOrderStatement(final String command, final int commandType, final Map parameters, final XConnectionTools tools, final XPropertySet rowSetProp)531 void fillOrderStatement(final String command, 532 final int commandType, final Map parameters, 533 final XConnectionTools tools, 534 final XPropertySet rowSetProp) 535 throws SQLException, 536 UnknownPropertyException, 537 PropertyVetoException, 538 IllegalArgumentException, 539 WrappedTargetException, 540 NoSuchElementException 541 { 542 final StringBuffer order = new StringBuffer(getOrderStatement(commandType, command, (ArrayList) parameters.get(GROUP_EXPRESSIONS))); 543 if (order.length() > 0 && commandType != CommandType.TABLE) 544 { 545 String statement = command; 546 final XSingleSelectQueryComposer composer = getComposer(tools, command, commandType); 547 if (composer != null) 548 { 549 statement = composer.getQuery(); 550 composer.setQuery(statement); 551 final String sOldOrder = composer.getOrder(); 552 if (sOldOrder.length() > 0) 553 { 554 order.append(','); 555 order.append(sOldOrder); 556 composer.setOrder(""); 557 statement = composer.getQuery(); 558 } 559 } 560 else 561 { 562 if (commandType == CommandType.QUERY) 563 { 564 final XQueriesSupplier xSupplyQueries = (XQueriesSupplier) UnoRuntime.queryInterface(XQueriesSupplier.class, connection); 565 final XNameAccess queries = xSupplyQueries.getQueries(); 566 if (queries.hasByName(command)) 567 { 568 final XPropertySet prop = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, queries.getByName(command)); 569 final Boolean escape = (Boolean) prop.getPropertyValue(ESCAPEPROCESSING); 570 rowSetProp.setPropertyValue(ESCAPEPROCESSING, escape); 571 final String queryCommand = (String) prop.getPropertyValue(UNO_COMMAND); 572 statement = "SELECT * FROM (" + queryCommand + ")"; 573 } 574 575 } 576 else 577 { 578 statement = "SELECT * FROM (" + command + ")"; 579 } 580 } 581 rowSetProp.setPropertyValue(UNO_COMMAND, statement); 582 rowSetProp.setPropertyValue(UNO_COMMAND_TYPE, Integer.valueOf(CommandType.COMMAND)); 583 } 584 rowSetProp.setPropertyValue("Order", order.toString()); 585 } 586 } 587 588