Parameter jenis java.util.List dalam iBatis 2 SQL Map

Contoh hantar list sebagai parameter dalam sqlmapping iBatis
Contoh ni hantar list dalam 1 parameter map jenis java.util.Map dengan var "p_dept_code_list"
....

 <select id="ims_getSummary" parameterClass="java.util.Map" resultMap="ims_summary_resultMap">  
 select   
  SSL_ANALYSIS_ID, SSL_STAFF_ID, SSL_QUALIFICATION_LEVEL, SSL_UNIVERSITY_NAME, SSL_STUDY_MODE, SSL_DATE_FROM, SSL_DATE_TO,  
  COUNT(SLE_EXTENSION_TYPE) TOTAL_EXTENSION,  
  sm_staff_name staff_name, sm_dept_code dept_code  
 from SSL, SM, SLE  
 where SM_STAFF_ID = SSL_STAFF_ID  
 and SSL_STAFF_ID = SLE_STAFF_ID  
 and SSL_QUALIFICATION_LEVEL = SLE_QUALIFICATION_LEVEL  
 <iterate prepend="AND" open="(" close=")" property="p_dept_code_list" conjunction="OR" >  
 SM_DEPT_CODE=#p_dept_code_list[]#  
 </iterate>  
 group by SSL_ANALYSIS_ID, SSL_STAFF_ID, SSL_QUALIFICATION_LEVEL, SSL_UNIVERSITY_NAME, SSL_STUDY_MODE, SSL_DATE_FROM, SSL_DATE_TO,   
 SM_STAFF_NAME, sm_dept_code  
 order by sm_staff_name, ssl_qualification_level  
 </select>  

....
sql yang dijana mungkin AND ( SM_DEPT_CODE = 'val1' OR SM_DEPT_CODE = 'val2' ....... )

Ibatis 2: SQL Map untuk Oracle DB Procedure.

 <procedure id="StaffAppraisal.updateKra" parameterClass="map">  
      <![CDATA[  
       {  
           #p_return_val,jdbcType=DECIMAL,mode=OUT# = call appraisal.update_kra(   
                #p_staff_id,jdbcType=VARCHAR,mode=IN#,   
                #p_appraisal_seq,jdbcType=VARCHAR,mode=IN#,  
                #p_session_code,jdbcType=VARCHAR,mode=IN#,   
                #p_year,jdbcType=VARCHAR,mode=IN#,  
                #p_kra,jdbcType=VARCHAR,mode=IN#,   
                #p_errmsg,jdbcType=VARCHAR,mode=OUT#  
           )  
       }  
      ]]>  
 </procedure>  

Dev info : 
Codename= APAR
DB = Oracle 10G

Contoh DAO (tiada kena mengena dengan map di atas):
...

      public String sendEmail (String formOwnerId, String emailType, String refCode)  
      {  
           String errorMesej = null;  
           Map<String,String> param = new HashMap<String,String>();  
           param.put("p_staff_id"     , formOwnerId);  
           param.put("p_email_type", emailType);  
           param.put("p_ref"          , refCode);  
           param.put("p_errmsg"     , refCode);  
           this.getSqlMapClientTemplate().update("EMAIL.sendEmail", param);  
           errorMesej = param.get("p_errmsg");  
           return errorMesej;  
      }  

...


How to call an Oracle 10G procedure in ibatis 2?

Ibatis: Invalid column name

java.sql.SQLException: Invalid column name

1. Semak dan pastikan semua colum dalam sql ada pada result-mapping.

at com.ibatis.sqlmap.engine.type.StringTypeHandler.getResult(StringTypeHandler.java:35)
at com.ibatis.sqlmap.engine.mapping.result.ResultMap.getPrimitiveResultMappingValue(ResultMap.java:619)
at com.ibatis.sqlmap.engine.mapping.result.ResultMap.getResults(ResultMap.java:345)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:384)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:300)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:189)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:295)
at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:1)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:293)

Ibatis 2: ORA-00911: invalid character error

org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested
exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/icms/biz/dao/ibatis/maps/StaffExpertise.xml.
--- The error occurred while applying a parameter map.
--- Check the STEXP.getActSEBSId-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00911: invalid character 

        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLEx
ceptionTranslator.java:97)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(Abstrac
tFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(Abstrac
tFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(Abstrac
tFallbackSQLExceptionTranslator.java:80)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212
)

IBATIS 2 SQL Map: LIKE + '%'

Jumpa 1 isu dengan SQL Mapping apabila cuba gunakan LIKE dengan '%' dalam SQL Map framework IBATIS 2 

IBATIS 2 SQL Map : Contoh hantar lebih 1 parameter gunakan java.util.Map

Contoh kita ada function dalam DAO IMPL:

public List<StaffSupervise> getExtSuperviseList(String staffId, String superviseCategory, String communityServiceType)

{

Map<String,String> paramMap = new HashMap<String, String>();

paramMap.put("p_staff_id", staffId);

paramMap.put("p_supervise_category", superviseCategory);

paramMap.put("p_comm_srvc_type", communityServiceType);

try{

@SuppressWarnings("unchecked")

List<StaffSupervise> list = this.getSqlMapClientTemplate().queryForList("STAFF_SUPERVISE.getStaffExtSuperv", paramMap);

return list;

} catch (Exception e){

e.printStackTrace();

return new ArrayList<StaffSupervise>();

}

}

Utk SQL Mapping pula 

(since structure CV-Online dgn APR berbeza sikit, abaikan perbezaan naming mapping.)

  <select id="STAFF_SUPERVISE.getStaffExternalSuperviseByCategoryAndSrvcType" resultMap="StaffSuperviseResultMap_3" parameterClass="java.util.Map" >

select ss.*,

om_name institution_desc, ql_desc level_desc, null duration_month

from staff_supervise ss, organization_main, qualification_level

where ss_institution = om_seq(+)

and ss_supervise_level = ql_code(+)

and ss_supervise_category = #p_supervise_category#

and ss_supervise_type = #p_comm_srvc_type#

and ss_staff_id = #p_staff_id#

  </select>

Dev info:

Code = APR, CV-Online

Framework = SPRING 2.5.6, IBATIS 2

Ibatis : Retrieve value from Oracle DB's function

SQL Mapping

 <parameterMap class="map" id="getJobDescParam">  
      <parameter property="p_job_code" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>  
 </parameterMap>  
 <select id="getJobDesc" parameterMap="getJobDescParam" resultClass="java.lang.String">  
      select hr_util.get_job_desc(?) from dual  
 </select>  

Contoh kod DAO

      public String getJobDesc(String jobCode)   
      {  
           String returnString;  
           Map<String, Object> map = new HashMap<String, Object>();  
           map.put("p_job_code", jobCode);  
           // Reference com.icms.biz.dao.ibatis.maps.StaffMain.xml  
           returnString = (String)getSqlMapClientTemplate().queryForObject("getJobDesc",map);  
           if(StringUtils.isEmpty(returnString)){  
                returnString = "-";  
           }  
           return returnString;  
      }