This website uses cookies for visitor traffic analysis. By using the website, you agree with storing the cookies on your computer.More information

Use OBridge together with JVx

OBridge is a nice Java FOSS project. The description according to the website:

OBridge generates standard Java source code for calling PL/SQL stored procedures and functions.

It's focus is on Oracle but this wasn't a limitation for us to test it with JVx.

Most of you know that JVx has generic support for procedure/function calls which is DB independent. The implementation is not really type-safe but it's simple.
If type-safety is preferred, you could use OBridge for your application.

I write about this library, because it's super small and simple. We love small and simple things :)

Assume, we have following PL/Sql procedure:

CREATE OR REPLACE PROCEDURE execProcedure(pNumber IN OUT NUMBER,
                                          pInText IN VARCHAR2,
                                          pOutText OUT VARCHAR2) IS
  nr NUMBER := pNumber;
BEGIN
  pOutText := 'Out: '|| pOutText ||' In: '|| pInText;

  pNumber := pNumber + pNumber;
END execProcedure;

and function:

CREATE OR REPLACE FUNCTION execFunction(pNumber IN OUT NUMBER,
                                        pInText IN VARCHAR2,
                                        pOutText OUT VARCHAR2) RETURN VARCHAR2 IS
  res VARCHAR2(200);
  nr NUMBER := pNumber;
BEGIN
  pOutText := 'Out: '|| pOutText ||' In: '|| pInText;

  pNumber := pNumber + pNumber;

  RETURN 'IN-Param Nr: '|| nr;
END execFunction;

 

With JVx, the procedure and function call will look like following JUnit test:

@Test
public void testCall() throws Exception
{
    DBAccess dba = DBAccess.getDBAccess("jdbc:oracle:thin:@localhost:xe", "test", "test");
    dba.open();
   
    /**
     * Procedure call.
     */

    OutParam ouTextParam = new OutParam(InOutParam.SQLTYPE_VARCHAR);
    dba.executeProcedure("execProcedure", BigDecimal.valueOf(25),
                         "Hello JVx' procedure", ouTextParam);

    Assert.assertEquals("Out:  In: Hello JVx' procedure", ouTextParam.getValue());

    /**
     * Function call.
     */
   
    ouTextParam = new OutParam(InOutParam.SQLTYPE_VARCHAR);
    Object oResult = dba.executeFunction("execFunction", Types.VARCHAR,
                                         BigDecimal.valueOf(25),
                                         "Hello JVx' function", ouTextParam);
   
    Assert.assertEquals("IN-Param Nr: 25", oResult);
    Assert.assertEquals("Out:  In: Hello JVx' function", ouTextParam.getValue());
}

 

and the same with OBridge:

@Test
public void testCall() throws Exception
{
    DBAccess dba = DBAccess.getDBAccess("jdbc:oracle:thin:@localhost:1521:xe", "test", "test");
    dba.open();
   
    /**
     * Procedure call.
     */

    Execprocedure proc = ProceduresAndFunctions.execprocedure(BigDecimal.valueOf(35),
                                           "Hello OBridge procedure", dba.getConnection());
   
    Assert.assertEquals("Out:  In: Hello OBridge procedure", proc.getPouttext());

    /**
     * Function call.
     */
   
    Execfunction func = ProceduresAndFunctions.execfunction(BigDecimal.valueOf(35),
                                            "Hello OBridge function", dba.getConnection());
   
    Assert.assertEquals("IN-Param Nr: 35", func.getFunctionReturn());
    Assert.assertEquals("Out:  In: Hello OBridge function", func.getPouttext());
}

 

The OBridge code saves two LoC for the call, but it needs some additional classes and packages in your application. If you change your procedure or function definition in the database, you have to recreate the Java files.
This is not needed with pure JVx, but it's your choice.

The code generation is not tricky, simply follow the official documentation. Our steps:

  • Create the file config.xml in the working directory of your project
    <?xml version="1.0" encoding="UTF-8"?>

    <configuration>
        <jdbcUrl>jdbc:oracle:thin:test/test@localhost:1521:xe</jdbcUrl>
        <sourceRoot>./src/</sourceRoot>
        <rootPackageName>com.sibvisions.apps.obridge.db</rootPackageName>
        <packages>
            <entityObjects>objects</entityObjects>
            <converterObjects>converters</converterObjects>
            <procedureContextObjects>context</procedureContextObjects>
            <packageObjects>packages</packageObjects>
        </packages>
    </configuration>

  • Run the main class: org.obridge.OBridge with argument: -c config.xml
    (or use -c fullqualified_path_to_config.xml)
 

If you love our generic built-in solution, you don't need OBridge but if you prefer type-safety it's definitely an option.

categories Development, Fun | rjahn | 26/02/2016 | comments Comments (0)

categories

© SIB Visions GmbH