Stored procedures are a common way to encapsulate reusable logic within a database. While H2 is an in-memory Java SQL database primarily used for development and testing, it also supports Java-based stored procedures and user-defined functions. Let us delve into understanding how Java H2 stored procedures can be written, registered, and invoked effectively using user-defined functions and Java methods.
1. What Are Stored Procedures?
Stored procedures are precompiled blocks of SQL code stored in the database, often used to encapsulate business logic, simplify access to complex queries, and boost performance. In H2, stored procedures can be written directly in Java and registered with the database engine.
2. Using Functions in H2
H2 allows Java methods to be used as SQL functions or procedures. These methods are either static or instance methods that follow certain conventions. They must be registered using the `CREATE ALIAS` SQL statement.
3. Code Example
The following Java utility class demonstrates basic arithmetic operations, string handling, and exception management.
// File: H2Utility.java
import java.sql.SQLException;
public class H2Utility {
// Add two numbers
public static int add(int a, int b) {
return a + b;
}
// Greet the user
public static String greet(String name) {
return "Hello, " + name + "!";
}
// Division with exception handling
public static double safeDivide(int numerator, int denominator) throws SQLException {
if (denominator == 0) {
throw new SQLException("Cannot divide by zero");
}
return (double) numerator / denominator;
}
// Combine all: compute (a + b) / c and greet the user
public static String computeAndGreet(String name, int a, int b, int c) throws SQLException {
int sum = add(a, b);
double result = safeDivide(sum, c);
return greet(name) + " The computed result is: " + result;
}
}
This code example demonstrates a simple Java utility class named H2Utility that provides basic arithmetic operations and user interaction methods. It includes a method add to sum two integers, a greet method that returns a personalized greeting, and a safeDivide method that performs division while handling division-by-zero errors by throwing a SQLException. The computeAndGreet method combines these functionalities: it first adds two numbers, safely divides the sum by a third number, and then returns a greeting message along with the result. This structured approach demonstrates error handling and modular function design in Java.
3.1 Calling the Java Methods via SQL in H2
Once the Java class H2Utility is compiled and available in the classpath, you can expose its methods to H2 SQL using the CREATE ALIAS command. This enables calling Java functions directly from SQL queries — useful for embedding custom logic or performing complex computations inside the database layer.
3.1.1 Compile the Java Class
javac H2Utility.java
This will generate H2Utility.class which should be available in your working directory or on the classpath.
3.1.2 Start the H2 Database Console
java -cp h2*.jar;. org.h2.tools.Server
Open your browser and go to http://localhost:8082. Connect to a test database (e.g., jdbc:h2:mem:testdb for an in-memory DB or jdbc:h2:~/test for a file-based one). Make sure the compiled class is accessible via the console’s classpath.
3.2 Register Utility Methods as SQL Functions in H2
To use the methods from the H2Utility Java class directly within SQL queries, you can register them as aliases in the H2 database. The following SQL statements create aliases for each method, allowing seamless invocation of Java logic from H2 SQL commands.
-- Register the 'add' method to perform integer addition CREATE ALIAS IF NOT EXISTS ADD FOR "H2Utility.add"; -- Register the 'greet' method to return a greeting message CREATE ALIAS IF NOT EXISTS GREET FOR "H2Utility.greet"; -- Register the 'safeDivide' method to perform division with exception handling CREATE ALIAS IF NOT EXISTS SAFE_DIVIDE FOR "H2Utility.safeDivide"; -- Register the 'computeAndGreet' method to combine arithmetic and greeting logic CREATE ALIAS IF NOT EXISTS COMPUTE_AND_GREET FOR "H2Utility.computeAndGreet";
3.3 Execute SQL Queries Using the Registered Functions
H2 allows you to invoke Java methods registered as SQL aliases, enabling a seamless bridge between Java logic and database queries. This section demonstrates how to use the previously registered methods through SQL and interprets the results returned by the H2 engine.
3.3.1 Simple Addition
The ADD function invokes the Java method H2Utility.add to compute the sum of two integers.
SELECT ADD(10, 5);
When executed, the following output is returned:
| ADD(10, 5) | |------------| | 15 |
3.3.2 Greeting a User
The GREET function maps to H2Utility.greet and returns a personalized greeting string based on the given name.
SELECT GREET('Yatin');
When executed, the following output is returned:
| GREET('Yatin') |
|----------------|
| Hello, Yatin! |
3.3.3 Safe Division (With and Without Errors)
The SAFE_DIVIDE function corresponds to the H2Utility.safeDivide method. It safely divides two numbers and throws an exception if the denominator is zero.
SELECT SAFE_DIVIDE(10, 2);
When executed, the following output is returned:
| SAFE_DIVIDE(10, 2) | |---------------------| | 5.0 |
If the denominator is zero, H2 throws an exception as defined in the Java method:
SELECT SAFE_DIVIDE(10, 0);
When executed, the following error is thrown:
org.h2.jdbc.JdbcSQLDataException: Cannot divide by zero
3.3.4 Combined Procedure: Arithmetic + Greeting
The COMPUTE_AND_GREET function internally calls add, safeDivide, and greet to perform addition, division, and generate a user-friendly message in a single SQL call.
SELECT COMPUTE_AND_GREET('Yatin', 10, 20, 5);
When executed, the following output is returned:
| COMPUTE_AND_GREET | |--------------------------------------------| | Hello, Yatin! The computed result is: 6.0 |
4. Conclusion
This example shows how to integrate Java methods with SQL in H2 by creating user-defined functions and stored procedures. It demonstrates parameter passing, exception handling, and combining logic into a useful utility function. H2’s flexibility with Java makes it a great choice for embedded testing or advanced SQL manipulation in Java applications.
