1. Introduction
In this tutorial, we’ll learn how we can write stored procedures in Java for use with the H2 database engine. We’ll see what they are, how we can create them and how we can make use of them.
2. What Are Stored Procedures?
A stored procedure is a mechanism that many database engines support, allowing us to create our own custom functionality within our database.
Just as with any other programming language, we can create a new procedure with a name and a set of input parameters that will perform the necessary functionality and then return an appropriate result. We then store these directly in the database.
Within the H2 database engine, these are referred to as User-Defined Functions, although these are the same concept under a different name. This name serves to differentiate them from the built-in functions that H2 already provides to us, such as UPPER() or DATEDIFF().
We’ll see the name Stored Procedures used by most database engines, so we just need to remember that, in H2, stored procedures and user-defined functions are the same thing. This allows us to use user-defined functions in H2 to replace functionality that we’d implement with stored procedures in other database engines.
This has a wide variety of uses, including all the normal uses for stored procedures, but also allowing us to replace them with stub versions if we’re using H2 for testing purposes.
3. Using Functions in H2
We can use functions directly in our SQL queries in H2. For example, it’s not unusual to see SQL statements like:
SELECT * FROM posts WHERE UPPER(title) = UPPER(?)
This applies the UPPER() function to both the title column and the bind parameter, effectively making the query case-insensitive.
The same applies to user-defined functions. Once we create them, they behave just like built-in functions, and we can use them in the same way:
SELECT * FROM numbers WHERE IS_PRIME(number) = TRUE
Here, IS_PRIME() is a user-defined function. It’s not something that H2 provides for us. Instead, we’d have had to write it ourselves. However, using it is exactly the same as if it were a built-in function.
4. Creating User-Defined Functions
Now that we’ve seen how to use our user-defined functions, we need to actually be able to create our own. There are two ways to do this in H2: we can either provide the source code of the function directly to the database, or we can write the function in Java and inform H2 that it exists.
4.1. Providing Source Code
We create a new user-defined function using the CREATE ALIAS command in our database. This command takes the function name and a string that serves as the function’s source code:
CREATE ALIAS SAY_HELLO AS '
String sayHello() {
return "Hello, World!";
}
';
This creates a user-defined function that always returns the string “Hello, World!”. Once we’ve done this, we can call the function as expected:
SELECT SAY_HELLO();
This then executes our new function and returns its result.
To create a user-defined function, we only need to provide a Java method definition. H2 automatically wraps this in the appropriate boilerplate to create a compilable class and then compile it into a real Java class on the classpath that we can call. As such, we can also reference other classes:
CREATE ALIAS JAVA_TIME_NOW AS '
String javaTimeNow() {
return java.time.Instant.now().toString();
}
';
This references the java.time.Instant class from the JVM, but the exact same can be done for any other class on the classpath – including from both our own code and our dependencies. The only requirement is that the classloader that has loaded H2 has access to the classes that we want to call.
However, needing to fully qualify every class can get unwieldy. As such. H2 allows us to separate our function into two parts, with the string @CODE separating the two:
CREATE ALIAS JAVA_TIME_NOW AS '
import java.time.Instant;
@CODE
String javaTimeNow() {
return Instant.now().toString();
}
';
The first part, which comes above the class definition, allows us to include import statements. The second part is the function definition that we’ve already seen. This then lets us write the same code as before, only much cleaner because we can import other classes as normal.
4.2. Pre-Compiled Code
In addition to specifying the Java code for our function directly in the user-defined function, we can instead create one that points to code that exists on the classpath. This means that we can write our code in whatever way we want, as long as it ends up in a way that’s accessible by H2. The only caveats here are that the target method must be static, and both the method and the containing class must be public.
We create this form of user-defined function using the CREATE ALIAS statement again, only this time we point to the fully-qualified function name instead of providing the source code:
CREATE ALIAS JAVA_RANDOM FOR "java.lang.Math.random";
This produces the same result as if we had written the code ourselves, and we can call it in the same way:
SELECT JAVA_RANDOM();
Here, we’ve pointed to a method from the Java standard library – Math.random(). However, we can just as easily point to any method anywhere on the classpath, including our own code:
CREATE ALIAS HELLO FOR "com.baeldung.h2functions.CompiledFunctionUnitTest.hello";
This then lets us do anything from the user-defined function that we can do from a static method – for example, querying remote services, accessing Spring beans, whatever makes sense for our needs.
5. Method Parameters
We’ve seen how to write our own user-defined functions and use them from queries. However, typically we want to be able to provide values to these functions for them to be useful.
Much of the time, this works exactly as we’d expect. As long as we use parameter types that follow the same data-type conversion rules as JDBC, they’ll work correctly:
CREATE ALIAS IS_ODD AS '
Boolean isOdd(Integer value) {
if (value == null) {
return null;
}
return (value % 2) != 0;
}
';
This function takes a single parameter of any type compatible with Integer and returns a Boolean:
SELECT IS_ODD(5); -- True
Notably, since we’re using boxed types, we can also be called with a NULL value, and we need to handle this:
SELECT IS_ODD(NULL); -- NULL
If we wish, we can instead accept primitive types:
CREATE ALIAS IS_ODD AS '
boolean isOdd(int value) {
return (value % 2) != 0;
}
';
Doing so means that we can never call the function with a NULL value. If that happens, H2 won’t call the function and instead returns a NULL for us.
5.1. Accessing the Database
As a special case, we can also accept a method parameter of type java.sql.Connection. This must be the method’s first parameter and will receive the same database connection used for the current query. This then allows us to interact with the database from within our user-defined function.
Let’s look at an example:
CREATE ALIAS SUM_BETWEEN AS '
int sumBetween(Connection con, int lower, int higher) throws SQLException {
try (Statement statement = con.createStatement()) {
ResultSet rs = statement.executeQuery("SELECT number FROM numbers");
int result = 0;
while (rs.next()) {
int value = rs.getInt(1);
if (value > lower && value < higher) {
result += value;
}
}
return result;
}
}
';
This user-defined function executes another query and performs logic on the returned results. In this case, it’s selecting a set of numbers from a table and summing all of the ones that fall in a certain range.
Notably, we’re provided with the same connection that the user-defined function was called with. This means that it participates in the same transaction and can access everything exactly the same as code from outside the function.
6. Exceptions
In some cases, our user-defined function may need to throw an exception to signal an error. H2 allows us to do this and handles it as we’d expect.
We can simply write our user-defined function to throw any exceptions we need, exactly the same as any other Java code:
CREATE ALIAS EXCEPTIONAL AS '
int exceptional() {
throw new IllegalStateException("Oops");
}
';
Since this is standard Java code, we need to declare any checked exceptions we want to throw in the method signature:
CREATE ALIAS EXCEPTIONAL AS '
import java.io.IOException;
@CODE
int exceptional() throws IOException {
throw new IOException("Oops");
}
'
When this happens, H2 automatically catches the exception and wraps it in an SQLException so that it can correctly come across the JDBC API:
SQLException exception = assertThrows(SQLException.class, () -> statement.executeQuery("SELECT EXCEPTIONAL()"));
assertTrue(exception.getCause() instanceof IllegalStateException);
assertEquals("Oops", exception.getCause().getMessage());
Here we can see that the cause of the SQLException is exactly what was thrown from our user-defined function.
7. Conclusion
In this article, we’ve taken a brief look at how to write our own user-defined functions within our H2 database and how to make use of them. The next time you need to write custom code inside your H2 database, why not give it a try?
As usual, all of the examples from this article are available over on GitHub.
