The SingleSelectQueryComposer

From Apache OpenOffice Wiki
Jump to: navigation, search



The service com.sun.star.sdb.SingleSelectQueryComposer is a tool that analyzes and composes single select statement strings. It is a replacement for the service com.sun.star.sdb.SQLQueryComposer. The query composer is divided into two parts. The first part defines the analyzing of the single select statement. The service com.sun.star.sdb.SingleSelectQueryAnalyzer 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 com.sun.star.sdb.SingleSelectQueryComposer extends the service com.sun.star.sdb.SingleSelectQueryAnalyzer and provides methods for expanding a statement with filter, group by, having and order criteria. To get the new, extended statement, the methods from com.sun.star.sdb.SingleSelectQueryAnalyzer have to be used.

A query composer com.sun.star.sdb.SingleSelectQueryComposer is retrieved over the com.sun.star.lang.XMultiServiceFactory interface of a com.sun.star.sdb.Connection: 

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

The interface com.sun.star.sdb.XSingleSelectQueryAnalyzer 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()
 
  // GROUP BY
  string getGroup(); 
  com::sun::star::container::XIndexAccess getGroupColumns();
 
  // HAVING
  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 com.sun.star.sdb.SingleSelectQueryComposer: 

  public void testSingleSelectQueryComposer() {
      log.println("testing SingleSelectQueryComposer");
 
      try
      {
          XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(XNameAccess.class,
              ((XMultiServiceFactory)param.getMSF()).createInstance("com.sun.star.sdb.DatabaseContext"));
          // 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("com.sun.star.sdb.SingleSelectQueryComposer"));
          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)
              UnoRuntime.queryInterface(XSingleSelectQueryComposer.class,xQueryAna);
 
          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
          xComposer.setGroup("\"Identifier\"");
          assure("Query not identical:" + xQueryAna.getGroup() + " -> \"Identifier\"", xQueryAna.getGroup().equals("\"Identifier\""));
 
          log.println("check setOrder");
          // order by
          xComposer.setOrder("\"Identifier\"");
          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)
          UnoRuntime.queryInterface(XColumnsSupplier.class,xQueryAna);
          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\"");
          xComposer.setFilter(complexFilter);
          PropertyValue[][] aStructuredFilter = xQueryAna.getStructuredFilter();
          xComposer.setFilter("");
          xComposer.setStructuredFilter(aStructuredFilter);
          assure("Structured Filter not identical" , xQueryAna.getFilter().equals(complexFilter));
 
          log.println("check structured having");
          // structured having clause
          xComposer.setHavingClause(complexFilter);
          PropertyValue[][] aStructuredHaving = xQueryAna.getStructuredHavingFilter();
          xComposer.setHavingClause("");
          xComposer.setStructuredHavingFilter(aStructuredHaving);
          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 com.sun.star.sdb.DataColumn 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 com.sun.star.sdb.SingleSelectQueryAnalyzer 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 com.sun.star.sdbcx.XTablesSupplier provides access to the tables that are used in the FROM part of the SQL-Statement: 

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

The interface com.sun.star.sdbcx.XColumnsSupplier provides the selected columns, which are listed after the SELECT keyword: 

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

The interface com.sun.star.sdb.XParametersSupplier 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).
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/DevGuide/Database/The_SingleSelectQueryComposer&oldid=246357"
Personal tools
In other languages