The SingleSelectQueryComposer

From Apache OpenOffice Wiki
Jump to: navigation, search

The service is a tool that analyzes and composes single select statement strings. It is a replacement for the service The query composer is divided into two parts. The first part defines the analyzing of the single select statement. The service hides the complexity of parsing and evaluating a single select statement, and provides methods for accessing a statements filter, group by, having and order criteria, as well as the corresponding select columns and tables. If supported, the service gives access to the parameters contained in the single select statement.

The second part of the query composer modifies the single select statement. The service extends the service and provides methods for expanding a statement with filter, group by, having and order criteria. To get the new, extended statement, the methods from have to be used.

A query composer is retrieved over the interface of a

  com::sun::star::uno::XInterface createInstance( [in] string aServiceSpecifier )

The interface is used to supply the SingleSelectQueryComposer with the necessary information. It has the following methods:

  // provide SQL string
  void setQuery( [in] string command)
  string getQuery()
  // filter
  string getFilter()
  sequence< sequence< com::sun::star::beans::PropertyValue > > getStructuredFilter()
  string getGroup(); 
  com::sun::star::container::XIndexAccess getGroupColumns();
  string getHavingClause(); 
  sequence< sequence<com::sun::star::beans::PropertyValue> > getStructuredHavingFilter();
  // control the ORDER BY clause
  string getOrder()
  com::sun::star::container::XIndexAccess getOrderColumns();

The example below shows a simple test case for the

  public void testSingleSelectQueryComposer() {
      log.println("testing SingleSelectQueryComposer");
          XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(XNameAccess.class,
          // we use the first datasource
          XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(XDataSource.class, 
              xNameAccess.getByName( "Bibliography" )); 
          log.println("check XMultiServiceFactory");
          XMultiServiceFactory xConn = (XMultiServiceFactory)UnoRuntime.queryInterface(XMultiServiceFactory.class, xDS.getConnection(new String(),new String()));
          log.println("check getAvailableServiceNames");
          String[] sServiceNames = xConn.getAvailableServiceNames();
          assure("Service 'SingleSelectQueryComposer' not supported" ,sServiceNames[0].equals(""));
          XSingleSelectQueryAnalyzer xQueryAna = (XSingleSelectQueryAnalyzer)
              UnoRuntime.queryInterface(XSingleSelectQueryAnalyzer.class,xConn.createInstance( sServiceNames[0]));
          log.println("check setQuery");
          xQueryAna.setQuery("SELECT * FROM \"biblio\"");
          assure("Query not identical", xQueryAna.getQuery().equals("SELECT * FROM \"biblio\""));
          // XSingleSelectQueryComposer
          XSingleSelectQueryComposer xComposer = (XSingleSelectQueryComposer)
          log.println("check setFilter");
          // filter
          xComposer.setFilter("\"Identifier\" = 'BOR02b'");
          assure("Query not identical:" + xQueryAna.getFilter() + " -> \"Identifier\" = 'BOR02b'", xQueryAna.getFilter().equals("\"Identifier\" = 'BOR02b'"));
          log.println("check setGroup");
          // group by
          assure("Query not identical:" + xQueryAna.getGroup() + " -> \"Identifier\"", xQueryAna.getGroup().equals("\"Identifier\""));
          log.println("check setOrder");
          // order by
          assure("Query not identical:" + xQueryAna.getOrder() + " -> \"Identifier\"", xQueryAna.getOrder().equals("\"Identifier\""));
          log.println("check setHavingClause");
          // having
          xComposer.setHavingClause("\"Identifier\" = 'BOR02b'");
          assure("Query not identical:" + xQueryAna.getHavingClause() + " -> \"Identifier\" = 'BOR02b'", xQueryAna.getHavingClause().equals("\"Identifier\" = 'BOR02b'"));
          log.println("check getOrderColumns");
          // order by columns
          XIndexAccess xOrderColumns = xQueryAna.getOrderColumns();
          assure("Order columns doesn't exist -> \"Identifier\"", xOrderColumns != null && xOrderColumns.getCount() == 1 && xOrderColumns.getByIndex(0) != null);
          log.println("check getGroupColumns");
          // group by columns
          XIndexAccess xGroupColumns = xQueryAna.getGroupColumns();
          assure("Group columns doesn't exist -> \"Identifier\"", xGroupColumns != null && xGroupColumns.getCount() == 1 && xGroupColumns.getByIndex(0) != null);
          log.println("check getColumns");
          // XColumnsSupplier
          XColumnsSupplier xSelectColumns = (XColumnsSupplier)
          assure("Select columns doesn't exist", xSelectColumns != null && xSelectColumns.getColumns() != null && xSelectColumns.getColumns().getElementNames().length != 0);
          log.println("check structured filter");
          // structured filter
          xQueryAna.setQuery("SELECT \"Identifier\", \"Type\", \"Address\" FROM \"biblio\" \"biblio\"");
          PropertyValue[][] aStructuredFilter = xQueryAna.getStructuredFilter();
          assure("Structured Filter not identical" , xQueryAna.getFilter().equals(complexFilter));
          log.println("check structured having");
          // structured having clause
          PropertyValue[][] aStructuredHaving = xQueryAna.getStructuredHavingFilter();
          assure("Structured Having Clause not identical" , xQueryAna.getHavingClause().equals(complexFilter));
      catch(Exception e)
          assure("Exception catched: " + e,false);

In the previous code example, a query command is passed to setQuery(), then the criteria for WHERE, and GROUP BY, and HAVING, and ORDER BY is added. The WHERE expressions are passed without the WHERE keyword to setFilter(), and the method setOrder(), with comma-separated ORDER BY columns or column numbers, is provided.

As an alternative, add WHERE conditions using appendFilterByColumn(). This method expects a service providing the name and the value for the filter. Similarly, the method appendOrderByColumn() adds columns that are used for ordering. The same applies to appendGroupByColumn() and appendHavingFilterByColumn(). These columns can come from the RowSet.

The Orignal property at the service holds the original single select statement.

The methods getQuery(), getFilter() and getOrder() return the complete SELECT, WHERE and ORDER BY part of the single select statement as a string.

The method getStructuredFilter() returns the filter split into OR levels. Within each OR level, filters are provided as AND criteria, with the name of the column and the filter condition string.

The interface provides access to the tables that are used in the FROM part of the SQL-Statement:

  com::sun::star::container::XNameAccess getTables()

The interface provides the selected columns, which are listed after the SELECT keyword:

  com::sun::star::container::XNameAccess getColumns()

The interface provides the parameters, which are used in the where clause:

  com::sun::star::container::XIndexAccess getParameters()
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages