7.4. Query Extensions
Prev | Chapter 7. Enterprise Features | Next

## 7.4. Query Extensions

JDOQL is designed to be back-end agnostic, meaning that the query language can be translated into any of a number of back-end languages. This is important for the JDO specification, because it permits true portability among fundamentally different data stores. However, when dealing with a relational data store, it is often necessary to use SQL-specific operations to harness the power and features of the relational data store. Kodo JDO Enterprise Edition allows developers to optimize and customize their datastore queries by adding new custom operations to JDOQL. In addition, Kodo provides some built-in extensions to simplify some common queries outside the JDOQL specification.

### 7.4.1. Using extensions in queries

To enable query extensions, set the `com.solarmetric.kodo.EnableQueryExtensions` property to `true`. This will enable query extensions and add the standard Kodo extensions listed below.

Extensions are used in filters with the given format:

- ext:tagname(argument)

- lit:tagname(argument)

Extensions thus begin with `ext` or `lit`, then a colon ":", and then the extension tag name. Extensions also take a single constant argument.

Extensions can be either Methods or Literals. Literals can be thought of as self-contained SQL WHERE expressions which by themselves can evaluate to a SQL boolean value. Methods, on the other hand, act upon a given field in the query.

For example, to use Kodo's stringContains extension (which is a Method) one would create a filter that would look like the following code fragment that searches for cities with the substring "ford" (e.g. Hartford):

Example 7.8. Using Kodo's StringContains extension

    Query q = pm.newQuery (City.class, true);
    q.setIgnoreCache (true); // extensions work purely upon the datastore
    q.setFilter ("name.ext:stringContains (\"ford\"");
    Collection c = (Collection) q.execute ();

### 7.4.2. Included Kodo Extensions

Kodo includes some default extensions which are detailed below

- lit:sqlEmbed

  Embeds argument right into the where clause as-is

- ext:stringContains

  Searches for fields that contain a substring. Uses `String.indexOf()` when checking in-memory, and `colname LIKE %argument%` when compiling to SQL.

- ext:caseInsStarts

  Requires DB support for UPPER (). Translates to UPPER (column) LIKE UPPER (argument%)

- ext:caseInsEnds

  Requires DB support for UPPER (). Translates to UPPER (column) LIKE UPPER (%argument)

- ext:caseInsContains

  Requires DB support for UPPER (). Translates to UPPER (column) LIKE UPPER (%argument%)

### 7.4.3. Writing custom extensions

Developers can write their own extensions by implementing one of two interfaces:

- com.solarmetric.kodo.impl.jdbc.query.MethodListener: A listener that will listen on its tag for filters calling ext. The above stringContains implements this interface. The primary method to implement is transform (String column, String argument). This method takes a given column and the argument passed to the filter and returns the SQL based upon those two parameters.

- com.solarmetric.kodo.impl.jdbc.query.LiteralListener: A listener that will listen on its tag for filters calling lit. Kodo's sqlEmbed extension implements this interface. The primary method to implement is embed (String argument) which return sql to embed into the WHERE clause.

In addition each listener must also provide Kodo with the tag that it is listening for with `public String getTag()` . If the listener is being loaded via properties (see below), it also should be instanceable by reflection (i.e. public empty constructor).

### 7.4.4. Configuring Extensions

There are primarily two means of registering user extensions with Kodo's querying system, each with benefits and tradeoffs depending on the application situation. The two can be mixed and matched depending on the needs of the application. However, Kodo extensions are loaded automatically by the system.

- Registration by properties

  Listeners are registered by setting the com.solarmetric.kodo.QueryFilterListeners property to a comma separate list of listener classes. Listeners registered in this fashion must be able to be loaded via reflection. The extensions handled by these listeners apply for all datastore queries associated with a given PersistenceManagerFactory. This is a good way to install common listeners.

- Runtime query by query basis

  Extensions can be registered with a given query via the `com.solarmetric.kodo.query.extensions.ExtensionHelper` helper class as shown in the following code fragment:

  Example 7.9. Filter extensions usage example

      public void addListener(Query q)
      {
        ExtensionHelper.registerListener(q, new CaseListener());

        // lets have one that also listens on a separate tag
        ExtensionsHelper.registerListener(q, new CaseListener(CaseListener.UPPER));
      }

      public static class CaseListener implements MethodListener
      {
        public static final int LOWER=0;
        public static final int UPPER=1;

        private int type = LOWER;

        public CaseListener () {}
        public CaseListener (int type)
        {
          this.type = type;
        }

        public String getTag ()
        {
          switch (type)
          {
            case UPPER:
              return "likeUpper";
            case LOWER:
            default:
              return "likeLower";
          }
        }

        public String transform (String column, String argument)
        {
          switch (type)
          {
            case UPPER:
              return column + " LIKE UPPER (" + argument + ")";
            case LOWER:
            default:
              return column + " LIKE LOWER (" + argument + ")";
          }
        }
      }

  When registering listeners on a query by query basis, listeners do not transfer from query to query. However, it is possible to do more customized construction of Listeners. For example, a listener could be constructed with complicated SQL based on application data which only gets hints from the argument.

Prev | Up | Next
7.3. Datastore Cache | Home | 7.5. Fetch Groups