Difference between revisions of "Documentation/DevGuide/Database/The SQLQueryComposer"

Latest revision as of 13:46, 21 December 2020

The service XSQLQueryComposerFactory is a tool that composes SQL SELECT strings. It hides the complexity of parsing and evaluating SQL statements, and provides methods to configure an SQL statement with filtering and ordering criteria.

The com.sun.star.sdb.SQLQueryComposer service is deprecated. Though you can still use it in your programs, you are encouraged to replace it with the SingleSelectQueryComposer service.

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

  com::sun::star::sdb::XSQLQueryComposer createQueryComposer()

Its interface com.sun.star.sdb.XSQLQueryComposer is used to supply the SQLQueryComposer with the necessary information. It has the following methods:

  // provide SQL string
  void setQuery( [in] string command)
  string getQuery()
  string getComposedQuery()
 
  // control the WHERE clause
  void setFilter( [in] string filter)
  void appendFilterByColumn( [in] com::sun::star::beans::XPropertySet column)
  string getFilter()
  sequence< sequence< com::sun::star::beans::PropertyValue > > getStructuredFilter()
 
  // control the ORDER BY clause
  void setOrder( [in] string order)
  void appendOrderByColumn( [in] com::sun::star::beans::XPropertySet column, [in] boolean ascending)
  string getOrder()

In the above method, a query command, such as "SELECT Identifier, Address, Author FROM biblio" is passed to setQuery(), then the criteria for WHERE 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. These columns could come from the RowSet.

Retrieve the resulting SQL string from getComposedQuery().

The methods getQuery(), getFilter() and getOrder() return the SELECT, WHERE and ORDER BY part of the SQL command 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 following example prints the structured filter.

  // prints the structured filter
  public static void printStructeredFilter(XMultiServiceFactory _rMSF) throws com.sun.star.uno.Exception {
      XNameAccess xNameAccess = (XNameAccess)UnoRuntime.queryInterface(
          XNameAccess.class, _rMSF.createInstance("com.sun.star.sdb.DatabaseContext"));
      // we use the first datasource
      XDataSource xDS = (XDataSource)UnoRuntime.queryInterface(
          XDataSource.class, xNameAccess.getByName("Bibliography")); 
      XConnection con = xDS.getConnection("", "");
      XQueriesSupplier xQuerySup = (XQueriesSupplier)UnoRuntime.queryInterface(
          XQueriesSupplier.class, con); 
 
      XNameAccess xQDefs = xQuerySup.getQueries();
 
      XPropertySet xQuery = (XPropertySet) UnoRuntime.queryInterface(
          XPropertySet.class,xQDefs.getByName("Query1"));
      String sCommand = (String)xQuery.getPropertyValue("Command");
 
      XSQLQueryComposerFactory xQueryFac = (XSQLQueryComposerFactory) UnoRuntime.queryInterface(
          XSQLQueryComposerFactory.class, con); 
 
      XSQLQueryComposer xQComposer = xQueryFac.createQueryComposer();
      xQComposer.setQuery(sCommand);
 
      PropertyValue aFilter [][] = xQComposer.getStructuredFilter();
      for (int i=0; i<aFilter.length; ) {
          System.out.println("( ");
          for (int j=0; j<aFilter[i].length; ++j)
              System.out.println("Name: " + aFilter[i][j].Name + " Value: " + aFilter[i][j].Value);
              System.out.println(")");
              ++i;
              if (i<aFilter.length )
                  System.out.println(" OR ");
          }
      }
  }

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()