Stored Procedure in H2 Database

Question

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.

Answer 1

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;
}
$$;

Answer 2

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.

Answer 3

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()");

Answer 4

Stored procedure in H2 database is same as java methods.So write java methods and can invoke using aliases.

Answer 5

The H2 is not supporting stored procedures. In place of stored procedure we can create a function which returns an output like a stored-procedures. Same as we're using in registerInOut parameters. For example, if your QueryConst looks like this:

public static final String INSERT_EMPLOYEE = "{call INSERT_EMPLOYEE(?,?,?)}";

then,

We can use schema.sql(which executes before @Test)

DROP ALIAS IF EXISTS INSERT_EMPLOYEE;
CREATE ALIAS INSERT_EMPLOYEE FOR "com.test.EmployeeDaoImplTest.updateEmpStoredproc";

package com.test;
@ContextConfiguration(locations = { "classpath:configxmltest.xml" })
@RunWith(SpringJUnit4ClassRunner.class)
@Sql(scripts = { "classpath:schema.sql" }, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
public class EmployeeDaoImplTest {
    
    public static final String INSERT_EMPLOYEE = "{call INSERT_EMPLOYEE(?,?,?)}";


    @Autowired
    EmployeeDaoImpl employeeDaoTest;
    

and other dependencies....(if any)

    @Test
    public void testUpdateEmployee() {
        ..ur logic if any input data settings
        assertEquals("Inserted Successfully", employeeDaoTest.updateEmployee(input, INSERT_EMPLOYEE));
    }

    public static ResultSet updateEmpStoredproc(String name, String w, Integer i) throws SQLException {
        SimpleResultSet rs = new SimpleResultSet();
        rs.addColumn("input", Types.VARCHAR, 255, 0);
        rs.addColumn("error", Types.VARCHAR, 255, 0);
        rs.addColumn("count", Types.INTEGER, 10, 0);
        rs.addRow(0, "Inserted Successfully");
        rs.addRow(1, 10);
        return rs;
    }
  }