How To Return Oracle Cursor Using Mybatis in Oracle stored procedure call

In some cases it is necessary to get the set of data in the same procedure call. Sample example is demonstrated below.

Employee class to hold the single employee.

public class EMPLOYEE {
private Integer EMP_ID;
private String EMP_NAME;

public void setEMP_ID(Integer eMP_ID) {
EMP_ID = eMP_ID;
}
public Integer getEMP_ID() {
return EMP_ID;
}
public void setEMP_NAME(Integer eMP_NAME) {
EMP_NAME = eMP_NAME;
}
public String getEMP_NAME() {
return EMP_NAME;
}
}

Code To Access Cursor in java

Map map = new HashMap();
map.put("EMP_ID",11111);
myConnection.insert("cursor_exec", map);
List emp = (List) map.get("EMP");
for (int i = 0; i < emp.size(); i++) {
System.out.println(emp.get(i).getEMP_ID() || emp.get(i).getEMP_NAME() );
}

Mybatis Mapping for returning Oracle Cursor during procedure call

<resultMap id="getCursor" type="office.main.EMPLOYEE">

</resultMap>
<select id="cursor_exec" statementType="CALLABLE" parameterType="java.util.Map">
{call
proc_name(#{EMP_ID,jdbcType=NUMERIC},#{EMP,jdbcType=CURSOR,mode=OUT,javaType=java.sql.ResultSet,resultMap=getCursor})}
</select>

How To Pass or Parameterize Name of Table in Sql Select Statements in Mybatis

Table name can be passed to Sql queries in Mybatis. Instead of the #{PARAM_NAME} used in general WHERE condition "${TABLE_NAME}" is used to parameterize table name .

Below is the sample example shown below.

<select id="getName" resultType="java.lang.String" parameterType="java.util.Map">
SELECT COL_NAME1 FROM ${TABLE_NAME}
WHERE PARAM_1=#{PARAM_1}
AND PARAM_2=#{PARAM_2}
AND PARAM_3=#{PARAM_3}
</select>

Function and Procedure call Mybatis

Oracle functions and procedures can be executed through Mybatis called using "<select></select>" XML descriptors. Depending on the behaviour of the procedure "<update></update>" and "<delete></delete>" descriptors can also be used. Sample of function and procedure call is presented below.

<select id="getEmpName" parameterType="java.util.Map" resultType="java.lang.String">
select getEmpName(#EMP_ID) from dual
</select>

<update id="proc_emp_update" statementType="CALLABLE" parameterType="java.util.Map" >
{call
emp_update(#{EMP_ID},#{EMP_NAME},#{DEPT_ID,jdbcType=NUMERIC},#

{DEPT_NAME,jdbcType=VARCHAR,mode=OUT})
}
</update>


The getEmpName function returns employee name. The statementType="CALLABLE" knows that is the procedure invocation. The jdbcType=NUMERIC must be provided for nullable fields during the procedure invocation. The jdbcType can be supported data type provided by Mybatis such as jdbcType=DATE for Date , jdbcType=VARCHAR for String, jdbcType=CURSOR for Cursor. mode=OUT defines that it is out parameter returned by the procedure.

Using Sql Queries in Mybatis

Mybatis provides XML descriptors such as "<select></select>", "<insert></insert>", "<update></update>","<delete></delete>" for SQL DML statements. The DML statements can be accessed through "id" property in the XML descriptors. The "parameters" descriptor defines the user parameters passed to the DML statements and "resultType" descriptor defines the type of result the SQL statement returns. The parameters are used in the SQL statments with #{PARAMETER_NAME}. Here is the sample of basic DML operations in Mybatis.

<select id="getEmployee" parameterType="java.util.Map" resultType="java.util.Map">
select * from employee
where emp_id=#{EMP_ID}
and emp_name like #{EMP_NAME}||'%'
</select>


<insert id="insertEmployee" parameterType="java.util.Map">
insert into employee(emp_id,emp_name) values (#{EMP_ID},#{EMP_NAME})
</insert>


<update id="updateEmployee" parameterType="java.util.Map">
update employee set emp_name=#{EMP_NAME}
where emp_id=#{EMP_ID}
</update>


<delete id="deleteEmployee" parameterType="java.util.Map">
delete from employee
where emp_id=#{EMP_ID}
</delete>


Unlike SQL statements in oracle semi-colon must be avoided in the DML statements at the end.