We are changing a lot of our DAO code so that it works the ORM way rather
than conventional CallableStatement way.
Right now we will be going from conventional DAO to iBatis way relative to
sql server...but later on we will be moving from iBatis (sql server) to
iBatis (oracle).
My question is, is there a way make mappings and code the related java code
such a way that when it comes time to move from sql server to oracle. we
dont have much hassle? All of our code will be using Stored Procedures.
Oracle stored procedures will be returning cursors, unlike sql server.
Following is the code I have in mind but if we take this approach then there
will have be a lot of changes made when we move from sql server to oracle:-
SQL Server:
mapping:
<!--procedure-->
<procedure id="getReportExcel" parameterMap="getReportParmExcel"
resultMap="select-first-results">
{call uiv_download_ineligible_report_list (?,?)}
</procedure>
<!--parameterMap-->
<parameterMap id="getImmigrationReportParmPhaExcel" class="map">
<parameter property="type" jdbcType="String" javaType="java.lang.String"
mode="IN"/>
<parameter property="month" jdbcType="int" javaType="java.lang.Integer"
mode="IN"/>
</parameterMap>
<!-- result Map-->
<resultMap id="select-first-results"
class="gov.hud.pih.eiv.data.vo.reports.ReportVO">
<result property="displayName" column="participant_name"/>
<result property="totalHouseholds" column="total_houses"/>
<result property="totalMembers" column="total_members"/>
</resultMap>
JavaCode:
HashMap parmMap = new HashMap();
parmMap.put("type", programType);
parmMap.put("month", reexamMonth);
List results_list
= getSqlMapClientTemplate().queryForList("report.getReportExcel", parmMap);
ReportVo vo = results_list.get(0);
Oracle:
iBatis
<!--procedure-->
<procedure id="getReportExcel" parameterMap="getReportParmExcel">
{call uiv_download_ineligible_report_list (?,?,?)}
</procedure>
<!--parameterMap-->
<parameterMap id="getReportParmExcel" class="map">
<parameter property="type" jdbcType="String" javaType="java.lang.String"
mode="IN"/>
<parameter property="month" jdbcType="int" javaType="java.lang.Integer"
mode="IN"/>
<parameter property="resultsReturned" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" resultMap="select-first-results"/>
</parameterMap>
<!--resultMap-->
<resultMap id="select-first-results"
class="gov.hud.pih.eiv.data.vo.reports.ReportVO">
<result property="displayName" column="participant_name"/>
<result property="totalHouseholds" column="total_houses"/>
<result property="totalMembers" column="total_members"/>
</resultMap>
Java:
HashMap parmMap = new HashMap();
parmMap.put("type", programType);
parmMap.put("month", reexamMonth);
getSqlMapClientTemplate().queryForList("report.getReportExcel", paramMap);
ReportVo vo
= (ReportVo )((ArrayList) paramMap.get("save_omb_expdate")).get(0);
Is this the best way to do when keeping in mind the transition from sql
server to oracle for future?
Thanks
