• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Persistence: Calling Stored Procedures from iBATIS

 
Bhim Upadhyaya
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want to quickly hookup stored procedures and do little performance testing with iBATIS, here is the code:

(NOTE: THE CODE MAY CONTAIN REDUNDANT/UNUSED/UNNECESSARY LOCs, PLEASE USE IT WITH YOUR JUDGEMENT. ALSO, THE CODE PRESENTED HERE IS NOT RELATED TO ANY ORGANIZATION, ALL NAMES USED ARE HYPOTHETICAL!)

Step 1: Sample Calling Method:

public static void callDBIBATIS(BufferedWriter bw){
Runtime runtime = Runtime.getRuntime();
long mem1 = 0l;
long mem2 = 0l;
List<CMCClass> classes = null;


try {
Reader reader = Resources.getResourceAsReader("SqlMapConfig_dbsw9079.xml");
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

bw.write("\n");
bw.write("\n");
bw.write("----------------------USING iBATIS-------------");
bw.write("\n");
bw.write("1. Total memory allocated for JVM: "+(runtime.totalMemory()/1024)+" KB");
mem1 = (runtime.totalMemory()-runtime.freeMemory())/1024;
bw.write("\n");
bw.write("2. BEFORE SP CALL: Total memory used: "+mem1+" KB");

classes = (List<CMCClass>)sqlMap.queryForList("CMCClass.selectCMCClass_SP_10");

bw.write("\n");
mem2 = (runtime.totalMemory()-runtime.freeMemory())/1024;
bw.write("3. AFTER SP CALL: Total memory used: "+mem2+" KB");
bw.write("\n");
bw.write("4. Additional memory used: "+(mem2-mem1)+" KB");

CMCClass cmcClass = null;
for (CMCClass cmc : classes) {
cmcClass = cmc;
System.out.print(" CSCS ID: " + cmcClass.getCscsId());
System.out.print(" GRGR CK: " + cmcClass.getGrgrCK());
System.out.print(" CSCS DESC " + cmcClass.getCscsDesc());
System.out.println("");
}

}catch (Exception e){
e.printStackTrace();
}
}


Step 2: iBATIS SQL Mapping File:


(SqlMapConfig.xml)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
<settings useStatementNamespaces="true"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<property name="JDBC.ConnectionURL"
value="YOUR-DB-CONNECTION-URL-HERE"/>
<property name="JDBC.Username" value="YOUR-DB-CONNECTION-USERNAME-HERE"/>
<property name="JDBC.Password" value="YOUR-DB-CONNECTION-PASSWORD-HERE"/>
</dataSource>
</transactionManager>
<sqlMap resource="CMCClass.xml"/>
</sqlMapConfig>


Step 3: Other Configuration / Mapping File(s):

(CMCClass.xml)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="CMCClass">

<resultMap id="cmcSearchResult" class="com.rivervalley.cmc_class.CMCClass">
<result property="cscsId" column="CSCS_ID" javaType="java.lang.String"/>
<result property="grgrCK" column="GRGR_CK" javaType="java.lang.Integer"/>
<result property="cscsDesc" column="CSCS_DESC" javaType="java.lang.String"/>
</resultMap>

<parameterMap id="selectCMCClass_SP_8_Call" class="com.rivervalley.ibatis.CMCSPParam">
<parameter property="startIndex" jdbcType="INT" javaType="java.lang.Integer" mode="IN"/>
<parameter property="endIndex" jdbcType="INT" javaType="java.lang.Integer" mode="IN"/>
</parameterMap>

<procedure id="selectCMCClass_SP_7" resultMap="cmcSearchResult">
{ call [MS\xxx].CMC_CLASS_SP_7()}
</procedure>

<procedure id="selectCMCClass_SP_10" resultMap="cmcSearchResult">
{ call [MS\xxx].CMC_CLASS_SP_10()}
</procedure>

<select id="getAllCMC" resultMap="cmcSearchResult">
SELECT * FROM [MS\bupadhy].CMC_CSCS_CLASS
</select>


</sqlMap>


Step 4: Sample Domain Class

(CMCClass.java)

package com.rivervalley.cmc_class;
public class CMCClass implements java.io.Serializable {

private String cscsId;
private String cscsDesc;
private int grgrCK;

public CMCClass() {
}

public CMCClass(String cscsDesc) {
this.cscsDesc = cscsDesc;
}

public String getCscsId() {
return this.cscsId;
}

public void setCscsId(String cscsId) {
this.cscsId = cscsId;
}

public String getCscsDesc() {
return this.cscsDesc;
}

public void setCscsDesc(String cscsDesc) {
this.cscsDesc = cscsDesc;
}
public int getGrgrCK() {
return grgrCK;
}

public void setGrgrCK(int grgrCK) {
this.grgrCK = grgrCK;
}

}


 
David Newton
Author
Rancher
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please UseCodeTags when posting code or configuration. Unformatted code and configuration is unnecessarily difficult to read. You can edit your post by using the button.
 
Cesar Coz
Ranch Hand
Posts: 32
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have a question.. here in this part of the code:



you are calling the 'query' with the method queryForList, but this method needs 2 parameters..

what version of Ibatis are you using ?
 
Bhim Upadhyaya
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
iBATIS 2.3.4, I think I did not have any syntantical issues and I got data from back-end.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic