All, I am trying to call a SQL procedure from Java user Mybatis, But i am getting "java.sql.SQLException: Missing IN or OUT parameter at index:: 1". Googling this doesnt give me the right answers. Below is my code what i wrote. I am using mybatis first time, Can anyone please guide me.
Mepper.xml :---
<select id="addHardwareList" statementType="CALLABLE" parameterType="java.util.HashMap">
{
call P_HW_SW_CRUD.SAVE_HARDWARE_INVENTORY(
#{pDataIn,javaType=Object,jdbcType=ARRAY,jdbcTypeName=TAB_IDESK_HW_INFO,mode=IN,typeHandler=com.tristar.idesk.mybatis.mapper.HardwareInventoryHandler}
#{pMsgCodeOut,javaType=Long,jdbcType=DECIMAL,mode=OUT}
)
}
</select>
Here is where i setup my MAP --
public void addUpdateInventory(JsonNode jsonNode) {
Map<String, ?> inventoryMap = getMapFromJSONAddUpdate(jsonNode);
HashMap<String,Object> returnMap = new HashMap<String,Object>();
Long pErrodCodeOut = null;
String pErrorMsg = null;
returnMap.put("pDataIn", (List<HardwareInventoryBean>) inventoryMap.get("beanHardwareList"));
returnMap.put("pErrorCodeOut", pErrodCodeOut);
//returnMap.put("pErrodMsgOut", pErrorMsg);
hardwareInventoryMapper.addHardwareList(returnMap);
}
I also implemented a custom type handler to map my java bean attributes to oracleType Object.
public class HardwareInventoryHandler implements TypeHandler{
public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
try{
//new RecLoaderService().getJSONObj("REC_ICAST_CLAIMANT_INFO", "TAB_ICAST_CLAIMANT_INFO", jsonObject, i, ps);
System.out.println(parameter);
List<HardwareInventoryBean> hwInventoryList = (List<HardwareInventoryBean>) parameter;
System.out.println("Parameter Here --"+parameter);
StructDescriptor structDescriptor = StructDescriptor.createDescriptor(InventoryConstants.HW_DB_REC_TYPE_NAME, ps.getConnection());
STRUCT[] structs = null;
structs = new STRUCT[hwInventoryList.size()];
for (int index = 0; index < hwInventoryList.size(); index++) {
HardwareInventoryBean hwInventoryBean = hwInventoryList.get(index);
Object[] params = new Object[12];
params[0] = hwInventoryBean.getHwId();
params[1] = hwInventoryBean.getHwTypeId();
params[2] = hwInventoryBean.getHwModelId();
params[3] = hwInventoryBean.getAssetId();
params[4] = hwInventoryBean.getDateReceived();
params[5] = hwInventoryBean.getHwPrice();
params[6] = hwInventoryBean.getHwStatus();
params[7] = hwInventoryBean.getHwWarranty();
params[8] = hwInventoryBean.getCreatedBy();
params[9] = hwInventoryBean.getCreatedDate();
params[10] = hwInventoryBean.getModifiedBy();
params[11] = hwInventoryBean.getModifiedDate();
System.out.println("---------> " +hwInventoryBean.getHwId());
STRUCT struct = new STRUCT(structDescriptor, ps.getConnection(), params);
structs[index] = struct;
}
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(InventoryConstants.HW_DB_TEB_TYPE_NAME, ps.getConnection());
ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), structs);
ps.setArray(i, oracleArray);
}
catch(Exception e){
e.printStackTrace();
}
}
public JSONObject getResult(ResultSet rs, String columnName)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public JSONObject getResult(ResultSet rs, int columnIndex)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
public JSONObject getResult(CallableStatement cs, int columnIndex)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
Mapper Interface ----
public void addHardwareList(HashMap<String, Object> returnMap);
My SQL Procedure code is :-
create or replace PACKAGE BODY P_HW_SW_CRUD AS -- body
PROCEDURE save_hardware_inventory (hw IN TAB_IDESK_HW_INFO, nu OUT NUMBER) IS
ERROR_CODE NUMBER(10);
ERROR_DESC VARCHAR2(200);
BEGIN
nu := 10;
FOR i IN 1 .. hw.COUNT
loop
if(hw(i).HW_ID <> 0) then
INSERT INTO IDESK_HW_INVENTORY VALUES (IDESK_HW_INVENTORY_seq.nextval, hw(i).hwTypeId , hw(i).hwModelId,hw(i).assetId
, hw(i).dateReceived , hw(i).hwPrice ,hw(i).hwStatus , hw(i).hwWarrenty , hw(i).createdBy , sysdate,hw(i).modifiedBy, sysdate);
else
update IDESK_HW_INVENTORY set HW_TYPE_ID=hw(i).hwTypeId,HW_MODEL_ID=hw(i).hwModelId,ASSET_ID=hw(i).assetId,DATE_RECEIVED=hw(i).dateReceived,HW_PRICE=hw(i).hwPrice,HW_STATUS=hw(i).hwStatus,HW_WARRENTY=hw(i).hwWarrenty,MODIFIED_BY=hw(i).modifiedBy,MODIFIED_DATE=sysdate where HW_ID=hw(i).HW_ID;
end If;
end loop;
Exception
when others then
ROLLBACK;
ERROR_CODE := SQLCODE;
ERROR_DESC := SQLERRM;
END save_hardware_inventory ;
PROCEDURE save_software_inventory (sw IN TAB_IDESK_SW_INFO ) IS
ERROR_CODE NUMBER(10);
ERROR_DESC VARCHAR2(200);
BEGIN
FOR i IN 1 .. sw.COUNT
loop
if(sw(i).SW_ID <> 0) then
INSERT INTO IDESK_SW_INVENTORY VALUES (IDESK_SW_INVENTORY_seq.nextval, sw(i).SW_VENDOR_ID , sw(i).SW_VERSION_TITLE,sw(i).QUANTITY
, sw(i).PRICE , sw(i).CONTRACT_ACOUNT_NUM ,sw(i).SW_STATUS , sw(i).CREATED_BY ,sysdate, sw(i).MODIFIED_BY , sysdate);
else
update IDESK_SW_INVENTORY set SW_VENDOR_ID=sw(i).SW_VENDOR_ID,SW_VERSION_TITLE=sw(i).SW_VERSION_TITLE,QUANTITY=sw(i).QUANTITY,PRICE=sw(i).PRICE,CONTRACT_ACOUNT_NUM=sw(i).CONTRACT_ACOUNT_NUM,SW_STATUS=sw(i).SW_STATUS,MODIFIED_BY=sw(i).MODIFIED_BY,MODIFIED_DATE=sysdate where SW_ID=sw(i).SW_ID;
end If;
end loop;
Exception
when others then
ROLLBACK;
ERROR_CODE := SQLCODE;
ERROR_DESC := SQLERRM;
END save_software_inventory ;
END P_HW_SW_CRUD ;
I resolve this issue by using the innermost delegate connection
((DelegatingConnection)ps.getConnection()).getInnermostDelegate().
Next error was because of typo error in mapper.xml("," missing in the select id in mapper.xml). See below for the correct syntax.
select id="addHardwareList"
statementType="CALLABLE" parameterType="java.util.HashMap">
{
call P_HW_SW_CRUD.SAVE_HARDWARE_INVENTORY(
#{pDataIn,javaType=Object,
jdbcType=ARRAY,jdbcTypeName=TAB_IDESK_HW_INFO,
mode=IN,typeHandler=com.tristar.idesk.mybatis.mapper.HardwareInventoryHandler}
#{pMsgCodeOut,javaType=Long,jdbcType=DECIMAL,mode=OUT}
)
}