Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Message-id: <fc.000f7f3e02675de7000f7f3e02675de7.2675ff7@flightcentre.co.uk>
Date: Thu, 12 Jan 2006 13:39:55 +0000
Subject: iBatis hangs query w/ multiple joins
To: user-java@ibatis.apache.org
From: "Jorge DeCastro" <jorge.decastro@flightcentre.co.uk>
MIME-Version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hi there,

I'm using iBatis to access a SQLServer db and I'm running into the
following problem. If I run the code below via straight JDBC it runs
perfectly.

private static final String SQL_STATEMENT = "SELECT DISTINCT "+ 
		"TOP 10 " +
		" PubAir.PubAirID," +
		" PubAir.FromGeoAirPort," +
		" PubAir.ToGeoAirPort, " +
		" PubAirAgreement.ValidFromDate," +
		" PubAirAgreement.ValidToDate," +
		" PubAirPrice.ADTCostAmount," +
		" TAXEstimate.TaxAmount," +
		" TAXEstimate.CurrencyCode" +
		" FROM PubAirRoutingPrice" +
                  " INNER JOIN PubAirPrice ON
PubAirRoutingPrice.PubAirPriceID = PubAirPrice.PubAirPriceID" +
		" INNER JOIN PubAir INNER JOIN PubAirAgreement ON
PubAir.PubAirAgreementID = PubAirAgreement.PubAirAgreementID" +
		" INNER JOIN PubAirRouting ON PubAir.PubAirID = PubAirRouting.PubAirID
ON PubAirRoutingPrice.PubAirRoutingID = PubAirRouting.PubAirRoutingID" +
		" INNER JOIN TAXEstimate ON TAXEstimate.InboundFromGeoCode =
dbo.PubAir.ToGeoAirPort" +
		" WHERE (PubAirPrice.ADTCostAmount > 0)" +
		" AND (PubAir.FromGeoAirPort LIKE ?)" +
		" AND (PubAir.ToGeoAirPort LIKE ?)";

	public List findFaresFromToAirportsWithinDatesViaStraightJDBC(
			String fromAirport,
			String toAirport, 
			String airlineFilter, 
			String type,
			Date startDate, 
			Date endDate, 
			Integer records) {

		logger.debug("Retrieving '" + records + "' fares from '" + fromAirport
				+ "' to '" + toAirport + "', start date '" + startDate
				+ "', end date '" + endDate + "'");
		
		DataSource ds = getDataSource();
		Connection c = null;
		PreparedStatement ps = null;
		ArrayList l = new ArrayList();
		try{
			c = ds.getConnection();
			ps = c.prepareStatement(SQL_STATEMENT);
			ps.setString(1, fromAirport + "%");
			ps.setString(2, toAirport + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				Air a = new Air();
				a.setFromAirport(rs.getString("FromGeoAirPort"));
				a.setToAirport(rs.getString("ToGeoAirPort"));
				a.getAirPrice().setAdultCost(rs.getBigDecimal("ADTCostAmount"));
				a.getAirPrice().setTax(rs.getBigDecimal("TaxAmount"));
				l.add(a);
			}
		}catch(Exception e){
			log.debug("error handling jdbc connection", e);
		}finally{
			if (ps != null){
				try{
					ps.close();
				}catch(Exception ignored){
				}
			}
			if (c != null){
				try{
					c.close();
				}catch(Exception ignored){
				}
			}
			
		}
		logger.debug("Retrieved '" + l.size() + "' fares");
		return l;
	}


The same query running on the SQLServer client console works fine. Now if
I run it through iBatis as I intend, the query hangs forever with no error
reported on the logs.
I'm buffled because it was my understandnig that iBatis was just doing the
ResultSet wrapping and unwrapping for me, and nothing else fancy (like
hibernate). Hence, it should simply execute a preparedStatement, populate
the beans according to my settings, and return a list.
Below are my mappings. Note that the "getCompanyCodes" query works just
fine via iBatis, which seems to tell me iBatis is having problems with the
joins.

<cacheModel id="faresCache" type="LRU" readOnly="true">
	  <flushInterval hours="24"/>
	  <property name="size" value="1000" />
	</cacheModel>

	<typeAlias alias="airfare" type="com.flightcentre.model.Air" />

    <resultMap id="getPubAirJoinAgreementJoinPrice" class="airfare">
		<result property="id" column="PubAirID"/>
		<result property="fromAirport" column="FromGeoAirPort"/>
		<result property="toAirport" column="ToGeoAirPort"/>
		<result property="airAgreement.validFromDate" column="ValidFromDate" />
		<result property="airAgreement.validToDate" column="ValidToDate" />
		<result property="airPrice.adultCost" column="ADTCostAmount" />
		<result property="airPrice.tax" column="TaxAmount" />
	</resultMap>

	<select id="findFaresFromToAirportsWithinDates"
resultMap="getPubAirJoinAgreementJoinPrice" parameterClass="java.util.Map"
cacheModel="faresCache">
	
	  SELECT DISTINCT 
		TOP #records# 
			PubAir.PubAirID, 
			PubAir.FromGeoAirPort, 
			PubAir.ToGeoAirPort, 
			PubAirAgreement.ValidFromDate, 
			PubAirAgreement.ValidToDate, 
			PubAirPrice.ADTCostAmount, 
			TAXEstimate.TaxAmount 
			
		FROM
			PubAirRoutingPrice 
		INNER JOIN
			PubAirPrice ON PubAirRoutingPrice.PubAirPriceID =
PubAirPrice.PubAirPriceID 
		INNER JOIN
			PubAir 
		INNER JOIN
			PubAirAgreement ON PubAir.PubAirAgreementID =
PubAirAgreement.PubAirAgreementID 
		INNER JOIN
			PubAirRouting ON PubAir.PubAirID = PubAirRouting.PubAirID ON
PubAirRoutingPrice.PubAirRoutingID = PubAirRouting.PubAirRoutingID 
		INNER JOIN
			TAXEstimate ON TAXEstimate.InboundFromGeoCode = dbo.PubAir.ToGeoAirPort
			
		WHERE     
			(PubAirPrice.ADTCostAmount > 0) 
		AND 
			(PubAir.FromGeoAirPort LIKE #fromAirport#) 
		AND 
			(PubAir.ToGeoAirPort LIKE #toAirport#)

		ORDER BY 
			PubAirPrice.ADTCostAmount
	
	</select>

and on the java DAO:

public List findFaresFromToAirportsWithinDates(
			String fromAirport,
			String toAirport, 
			String airlineFilter, 
			String type,
			Date startDate, 
			Date endDate, 
			Integer records) {

		Map parameters = new HashMap();
		parameters.put("fromAirport", fromAirport + "%");
		parameters.put("toAirport", toAirport + "%");
		parameters.put("airlineFilter", airlineFilter + "%");
		parameters.put("type", type + "%");
		parameters.put("startDate", startDate);
		parameters.put("endDate", endDate);
		parameters.put("records", records);
		logger.debug("Retrieving '" + records + "' fares from '" + fromAirport
				+ "' to '" + toAirport + "', start date '" + startDate
				+ "', end date '" + endDate + "'");
		List l = getSqlMapClientTemplate().queryForList(
				"findFaresFromToAirportsWithinDates", parameters);
		logger.debug("Retrieved '" + l.size() + "' fares");
		return l;
	}


Any help will be highly appreciated.
chrs
j.