Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Custom SQL Functions in GridGain IMDB

DZone's Guide to

Custom SQL Functions in GridGain IMDB

Free Resource
As you may already know, you can use any standard SQL construct in your queries, including functions like min(), max(), sum(), etc ... GridGain also supports custom SQL functions that can be used in your SQL cache queries. This is useful if you have some custom logic that you would like to use in your query expressions.
Let's suppose that we want to create a custom function to convert an integer into a hexadecimal string. The implementation of such a function might look like this:
public class MySqlFunctions {
    @GridCacheQuerySqlFunction(alias = "to_hex", deterministic = true)
    public static String toHex(int i) {
        return Integer.toHexString(i);
    }
}
Custom SQL functions must satisfy the following criteria:

  1. They must be declared as public static methods.
  2. They must be annotated with the @GridCacheQuerySqlFunction annotation.

Now that we've implemented the function, we need to add a containing class to the GridH2IndexingSpi configuration:
<property name="indexingSpi">
    <list>
        <bean class="org.gridgain.grid.spi.indexing.h2.GridH2IndexingSpi">
            <!-- Add a class with our custom function to SPI. -->
            <property name="indexCustomFunctionClasses"
                value="com.mycompany.MySqlFunctions"/>
        </bean>
    </list>
</property>
Now we can simply use our SQL function in a query:
GridCacheQuery<Integer, String> qry = cache.createQuery(GridCacheQueryType.SQL, String.class,
    "to_hex(_key) <> _val").
    .execute()
    .get();
Topics:

Published at DZone with permission of Dmitriy Setrakyan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}