ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jorge DeCastro" <jorge.decas...@flightcentre.co.uk>
Subject iBatis hangs query w/ multiple joins
Date Mon, 16 Jan 2006 10:42:47 GMT
Hi again,

My problem described below was due to the fact that SQL Server on our dev
environment grew too large. The SQL error thrown by iBatis after a long
wait was:

"The log file for database 'tempdb' is full. Back up the transaction log
for the database to free up some log space."

My question now is: if I ran the exact same query via straight JDBC or via
the SQL Server client console, the query ran successful. Through iBatis, I
could even get simpler queries to run, including the one below without the
additional join to TAXEstimate.
Is there any explanation why iBatis would hang on a query that executes
via straight JDBC?

Thanks
j.






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.


Mime
View raw message