I am new to database and recently started writing test cases for H2 database. I want to know how to test a stored procedure in Eclipse. I have seen the following:
http://www.h2database.com/html/features.html#user_defined_functions
How to CREATE PROCEDURE in H2
The sample code given in the h2database link,
"CREATE ALIAS NEXT_PRIME AS $$
String nextPrime(String value) {
return new BigInteger(value).nextProbablePrime().toString();
}
$$;
"
Where should this be declared?and how to run it?
PS - I have the H2 JAR file and am testing it.
If someone can tell me how to write a simple stored procedure in Java for H2, it would be of great help.
Also is there any equivalent of the following in H2?
"begin dbms_output" ?
Thanks.
There is no stored procedure and sql userdefined function in H2 database instead of that we use java methods and create a alias to refer that.We can call that methods using alias.
Below is a simple example:**
DROP ALIAS IF EXISTS MYFUNCTION;
CREATE ALIAS MYFUNCTION AS $$
String getTableContent(java.sql.Connection con) throws Exception {
String resultValue=null;
java.sql.ResultSet rs = con.createStatement().executeQuery(
" SELECT * FROM TABLE_NAME");
while(rs.next())
{
resultValue=rs.getString(1);
}
return resultValue;
}
$$;
You may have overlooked the examples in src/test/org/h2/samples/Function.java
. Here's a related example:
Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
Statement st = conn.createStatement();
st.execute("CREATE ALIAS getVersion FOR \"org.h2.engine.Constants.getVersion\"");
ResultSet rs;
rs = st.executeQuery("CALL getVersion()");
if (rs.next()) System.out.println("Version: " + rs.getString(1));
Console: Version: 1.4.191
Addendum: The feature is not limited to functions; aliased methods can execute arbitrary Java code. For example, the query()
method defined in Function.java
may be aliased and called as shown below:
Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
Statement st = conn.createStatement();
st.execute("CREATE ALIAS query FOR \"cli.Function.query\"");
rs = st.executeQuery("CALL query('SELECT NAME FROM INFORMATION_SCHEMA.USERS')");
while (rs.next()) {
System.out.println("User: " + rs.getString(1));
}
Console: User: SA
Note that cli.Function.query
is a copy of org.h2.samples.Function.query
.
Below is the way we used to implemented in our project. It might be helpful :)
package com.procedures;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CRITICAL_ACTIONS {
public static final int SAVE_ACTION(Connection connection) throws SQLException {
try {
Statement statement = connection.createStatement();
return statement.executeUpdate("INSERT INTO SCHEMA1.CRITICAL_ACTIONS(COLLEAGUE_ID,JOURNEY_ID,TYPE,PRODUCT,DESCRIPTION,META_DATA,STATUS) values('12345',11111111,'ABC','Lloyds','hellow','hello','START')");
} finally {
//connection.close();
}
}
public static final ResultSet FETCH_ACTION(Connection connection) throws SQLException {
try {
Statement statement = connection.createStatement();
return statement.executeQuery("SELECT * FROM SCHEMA1.CRITICAL_ACTIONS");
}finally {
connection.close();
}
}
}
Calling H2 Java Stored-procedure in Java :-
jdbcTemplate.update("CREATE ALIAS SAVE_ACTION FOR \"com.procedures.CRITICAL_ACTIONS.SAVE_ACTION\"");
jdbcTemplate.update("CREATE ALIAS FETCH_ACTION FOR \"com.procedures.CRITICAL_ACTIONS.FETCH_ACTION\"");
jdbcTemplate.getDataSource().getConnection().createStatement().execute("call SAVE_ACTION()");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With