Got stucked in IBatis code !!

[sonalisawant]

Hi,
I am new to iBatis. I am stucked at some point. I am attaching code here.

here is my OrderHistory.xml file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">



<sqlMap namespace="OrderHistory">

<resultMap id="resultOrderHistory" class="com.exit41.orderperfect.customeridentificat ion.core.OrderSummary">
<result property="orderSummaryId" column="orderSummaryId" columnIndex="1"/>
<result property="orderId" column="orderId" columnIndex="2"/>
<result property="customerId" column="customerId" columnIndex="3"/>
<result property="orderDate" column="orderDate" columnIndex="4"/>
<result property="orderTime" column="orderTime" columnIndex="5"/>
<result property="orderTotal" column="orderTotal" columnIndex="6"/>
<result property="orderStore" column="orderStoreId" columnIndex="7"/>
<result property="agentId" column="agentId" columnIndex="8"/>
<result property="orderStatus" column="status" columnIndex="9"/>
<result property="orderMode" column="mode" columnIndex="10"/>
<!-- i want to map these fileds here
identifier
identifierType -->
</resultMap>


<select id="retriveOrderHistoryByIdentifier" resultMap="resultOrderHistory">

select os.orderSummaryId, os.orderId, os.customerId, os.orderDate, os.orderTime, os.orderTotal, os.orderStoreId, os.agentId, ost.status, om.mode
from OrderSummary os, OrderStatus ost, OrderMode om, Customer cust, CustomerProfile cp, IdentifierType idType
where os.customerId = (select cstp.customerId from CustomerProfile cstp, IdentifierType it
where cstp.identifier = '#identifier#' and it.identifierType = '#identifierType#' and it.identifierTypeId = cstp.identifierTypeId)
and cust.customerId = os.customerId and om.orderModeId = os.orderModeId and ost.orderStatusId = os.orderStatusId
and idType.identifierTypeId = cp.identifierTypeId and cust.customerId = cp.customerId
order by os.orderDate desc;

</sqlMap>


and here is my IdentifierType.java file
package com.exit41.orderperfect.customeridentification.cor e;

import javax.print.attribute.EnumSyntax;

public class IdentifierType extends EnumSyntax {

public static final IdentifierType phone = new IdentifierType(0);
public static final IdentifierType email = new IdentifierType(1);
public static final IdentifierType loyaltyCard = new IdentifierType(2);
public static final IdentifierType rfidCard = new IdentifierType(3);

public IdentifierType(int value) {
super(value);

}
}


Now i want map identifier and identifierType to resultMap in .xml file. and there is table for identifierType having fields identifierTypeId and identifierType in database.

Can anybody help me in this?

Thanks in advance,
Sonali.

[shailesh.sutar]

search for how to pass parameters to select

Try using parameterMap to query as

<parameterMap id="identifierParam" class="map" >
		<parameter property="identifier"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
		<parameter property="identifierType" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>	
</parameterMap>

 <select id="retriveOrderHistoryByIdentifier" parameterMap="identifierParam" resultMap="resultOrderHistory">
- - - Your Query - - - 
 </select>

And use HashMap in java to pass parameters to select statement.

Can you simplify Query as :

select	os.orderSummaryId, os.orderId, os.customerId, 
		os.orderDate, os.orderTime, os.orderTotal, os.orderStoreId, 
		os.agentId, ost.status, om.mode
from	OrderSummary os, 
		OrderStatus ost, 
		OrderMode om, 
		Customer cust, 
		CustomerProfile cp, 
		IdentifierType idType
where	       cp.identifier = '#identifier#' 						
and		idType.identifierType = '#identifierType#' 					
and		cust.customerId = os.customerId 
and		om.orderModeId = os.orderModeId 
and		ost.orderStatusId = os.orderStatusId
and		idType.identifierTypeId = cp.identifierTypeId 
and		cust.customerId = cp.customerId
order by os.orderDate desc;

[sonalisawant]

Hi Shailesh,
Thanks for your help. I did in the same way you told me and its working now.Thanks.

Regards,
Sonali.

[shailesh.sutar]

IBatis is really very good if you like to write ur own SQL.
Even performance is very close to JDBC.