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

Learn how real real-time monitoring is critical for DevOps. Because you can't build what you can't see.

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

Get real-time alerts and visualizations across your cloud infrastructure for real real-time cloud monitoring. Try it FREE now

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 }}