Using Ibatis to invoke Oracle stored procedures and functions in Java



Hibernate provides a comprehensive database encapsulation mechanism of the "fully automated" ORM implementation, "fully automatic" ORM implementation of POJO and database tables mapping between, as well as the automatic generation and execution of SQL.
Ibatis is "semi-automatic" and does not automatically generate SQL execution for programmers during run time. The specific SQL needs to be written by the programmer and mapped to the specified POJO by mapping the configuration file to the parameters required by SQL and the resulting field returned. More flexible to give programmers more space to play.

The following code uses Ibatis-sqlmap-2.jar, which is tested in Windows Tomcat and passed in WebLogic under Linux.

First, use Ibatis to invoke Oracle stored procedures
1, create test stored procedures, name: Testpro

CREATE OR REPLACE PROCEDURE testpro (mylevel in VARCHAR2, myresult out  pls_integer) is

BEGIN
  myresult:=0;  
  If mylevel = ' A ' then 
     myresult:=0;
  End If;
  
  If mylevel = ' B ' then 
     myresult:=-1;
  End If;
  
  If mylevel = ' C ' then 
     myresult:=1;
  End If;
End;

2, call the stored procedure sqlmap file configuration

<parametermap id= "Testproparammap" class= "Java.util.HashMap" >
	<parameter property= "Mylevel" jdbctype= "VARCHAR" javatype= "java.lang.String" mode= "in"/> <parameter property=
	"Myresult" jdbctype= "NUMERIC" Javatype= "Java.lang.Integer" mode= "Out"/>
</parameterMap>
<procedure id= "Testoraclepro" parametermap= "Testproparammap" >
	{call Testpro (?,?)} 
</procedure>

3. Invoke the DAO layer code of the stored procedure

Public Object Testprodao (map map) {return
	getsqlmapclienttemplate (). queryForObject ("Ticket.testoraclepro", map) ;
}

4, Front call

Map Mappro = new HashMap ();
Mappro.put ("Mylevel", "B");
Object Rs=xxxxdao.testprodao (Mappro);
Logutil.debug ("rs=" +rs);
Logutil.debug ("myresult=" +mappro.get ("Myresult"));

Results:
Rs=null
Myresult=-1
From the test results, the return result is in the map, the output parameter is accessed as a key to access the

Second, using Ibatis to invoke Oracle functions

1, create test function, Name: Testfun

CREATE OR REPLACE FUNCTION testfun (mylevel in VARCHAR2) return number as

  myresult      Pls_integer: = 0;

BEGIN
  If mylevel = ' A ' then 
     myresult:=0;
  End If;
  
  If mylevel = ' B ' then 
     myresult:=-1;
  End If;
  
  If mylevel = ' C ' then 
     myresult:=1;
  End If;
  return myresult;
End;

2, call the function of the Sqlmap file configuration

<parametermap id= "Testfunparammap" class= "Java.util.HashMap" >
	<parameter property= "Myresult" Jdbctype= "NUMERIC" javatype= "Java.lang.Integer" mode= "Out"/> <parameter property= "mylevel" jdbctype= "
	VARCHAR "javatype=" java.lang.String "mode=" in "/>
</parameterMap>
<procedure id=" Testoraclefun "parametermap=" Testfunparammap ">
	{? = Call Testfun (?)} 
</procedure>

3, call the function of the DAO layer code

Public Object Testfundao (map map) {return
	getsqlmapclienttemplate (). queryForObject ("Ticket.testoraclefun", map) ;
}

4, Front call

Map mapfun = new HashMap ();
Mapfun.put ("Mylevel", "B");
Object Rs=xxxxdao.testprodao (mapfun);
Logutil.debug ("rs=" +rs);
Logutil.debug ("myresult=" +mapfun.get ("Myresult"))

Results:
Rs=null
Myresult=-1
From the test results, the return result is in the map, the output parameter is accessed as a key to access the