Easy way to call stored procedure in Java

Question

When call a stored procedure I use this code:

    connection = getConnection();
    stmt = connection.prepareCall("{call MPLOGIN (?, ?, ?, ?, ?, ?)}");
    stmt.setString("VUSCD", uscd);
    stmt.setString("VPWD", pwd);
    stmt.setString("VPCSQ", pcsq);
    stmt.setString("VHWID", hwid);
    stmt.registerOutParameter("VLOGID", OracleTypes.VARCHAR);
    stmt.registerOutParameter("VKQ", OracleTypes.VARCHAR);
    stmt.execute();
    String vlogid = stmt.getString("VLOGID");
    String vkq = stmt.getString("VKQ");

write this boring wrapper for few procedure is not problem but if there are hundreds of procedure, it is really a nightmare Is there any easier way to call store procedure than this way? Edit: I think a code generator which use the procedure's parameters from DB is the elagant way but I google for nothing in java

Answer 1

You could, maybe create a generic wrapper, something along these lines:

public Map<String, String> SPWrapper(String call, Map<String, String> inParams, Map<String, OracleTypes> outParams)
{
    connection = getConnection();
    try 
    {
        stmt = connection.prepareCall(call);
        for(String inParam : inParams.keys())
        {
            stmt.setString(inParam, inParams.get(inParam));
        }
        for(String outParam : outParams.keys())
        {
            stmt.registerOutParameter(outParam, outParams.get(outParam));
        }

        stmt.execute();

        Map<String,String> results = new HashMap<String, String>();
        for(String outParam : outParams.keys())
        {
            results.put(outParam, stmt.getString(outParam));
        }

        return results;
    }
    catch (Exception e)
    {
        //LOG Exception
        return new HashMap<String, String>();
    }
    finally
    {
        connection.close();   //Do not leave connections open.
    }
}

You would still need to pass in the call and declare the variables, but at least you now have one generic wrapper to handle all your calls.

Answer 2

I like to use the MyBatis data mapper framework for such problems. An extensive example for working with MyBatis and stored procedures can be found at http://loianegroner.com/2011/03/ibatis-mybatis-working-with-stored-procedures/