Over a million developers have joined DZone.

Custom SQL Functions in GridGain IMDB

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

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">
        <bean class="org.gridgain.grid.spi.indexing.h2.GridH2IndexingSpi">
            <!-- Add a class with our custom function to SPI. -->
            <property name="indexCustomFunctionClasses"
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").

Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.


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

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}