Tip: How To Extend HSQLDB RDBMS With Java Functions

HSQLDB is an unique high performing, high quality Java based relational database which can be very easily extended with simple Java static functions. Here we will see how you can easily extend the database capability with a real-life example.

I wanted to do a case-insensitive LIKE comparison, specifically get rows where the column data contains the target string. The where clause using LIKE would be: “Column Name” LIKE ‘Target String’.

Unfortunately SQL LIKE is case sensitive. A simple way to accomplish this would be write a static function to do the comparison. He is the function I wrote in my Util.java file (a collection of static java utility functions) to do the comparison:

public static boolean containsMatch(String target, String search) {
return target.toLowerCase().contains(search.toLowerCase());
}

To use it I executed the following SQL query:

select distinct "e-Biz Manager" from Sheet where "com.taragana.myexcel.Util.containsMatch"("e-Biz Manager", 'emily')


Note that I enclosed the function invocation in double quote. The column name is enclosed in double quotes because it contains space. The actual data is enclosed in single quotes.
Also note that I can shorten the name of the function invoked with an alias statement.

Isn’t this simplicity defined? I find HSQLDB an extremely versatile database which has served me over the years, highly recommended. And as for performance? It beats any other database hands down including but not limited to MySQL, Oracle etc.