You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jorge DeCastro <jo...@flightcentre.co.uk> on 2006/01/12 14:39:55 UTC
iBatis hangs query w/ multiple joins
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.