Function
The following example shows getting a student record by id from oracle function.
The function requires two parameters. The first one is student id which is
mapped as IN parameter and the other is a cursor
representing the student record from the function as a return value.
The cursor is mapped to java resultset as an OUT parameter.
01<?xml version="1.0" encoding="UTF-8"?>
02<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
05<sqlMap namespace="Student">
07 <resultMap id="studentFunRM" class="com.bethecoder.tutorials.ibatis.common.Student" >
08 <result property="studentId" column="STUDENT_ID" />
09 <result property="firstName" column="FIRST_NAME" />
10 <result property="lastName" column="LAST_NAME" />
11 <result property="age" column="AGE" />
12 <result property="hobby" column="HOBBY" />
13 <result property="phone" column="PHONE" />
16 <parameterMap id="funcParams" class="map">
17 <parameter property="resultset" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" />
18 <parameter property="studentId" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN" />
21 <procedure id="student_by_id_fun" parameterMap="funcParams" resultMap="studentFunRM">
23 { ? = call student_by_id( ? ) }
02create or replace package types
04 type cursorType is ref cursor;
08create or replace function student_by_id (studentId IN NUMBER) return types.cursorType is
09 l_cursor types.cursorType;
11 open l_cursor for SELECT * FROM STUDENT where STUDENT_ID = studentId;
package com.bethecoder.tutorials.ibatis.tests.basic; import java.io.IOException; import java.io.Reader; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import com.bethecoder.tutorials.ibatis.common.Student; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder; public class Function { /** * @param args * @throws IOException * @throws SQLException */ public static void main ( String [] args ) throws IOException, SQLException { Reader reader = Resources.getResourceAsReader ( "SqlMapConfig.xml" ) ; SqlMapClient sqlMapClent = SqlMapClientBuilder.buildSqlMapClient ( reader ) ; Map<String, Object> dataMap = new HashMap<String, Object> () ; dataMap.put ( "studentId" , 1 ) ; Student stud = ( Student ) sqlMapClent.queryForObject ( "Student.student_by_id_fun" , dataMap ) ; System.out.println ( stud ) ; dataMap = new HashMap<String, Object> () ; dataMap.put ( "studentId" , 4 ) ; stud = ( Student ) sqlMapClent.queryForObject ( "Student.student_by_id_fun" , dataMap ) ; System.out.println ( stud ) ; } }
Student[1, Jim, Attic, 32, Painting, +919999999999]
Student[4, Arjun, Mishra, 28, Football, +917777777777]
