Hi,
I built two testing over framwork ibatis, I had used JdbcDaoTemplate and
SqlMapDaoTemplate both implement the same query, the JDBC drivers is
oracle.
1) JdbcDaoTemplate
the average of method "execute()" is 80ms (with 10 execution)
2) SqlMapDaoTemplate
the average of method "execute()" is 300ms (with 10 execution) class
com.ibatis.sqlmap.engine.execution.SqlExecutor
why the method execute() of SqlMapDaoTemplate is slower than Jdbc if it
is
using the same framework?
----------------------------------------------------------
source JdbcDaoTemplate
----------------------------------------------------------
public class TestingJDBCDAO extends JdbcDaoTemplate implements
TestingPLDAO{
public TestingJDBCDAO (DaoManager daoManager) {
super(daoManager);
}
public List findBookingByFlightJDBC(FlightDBO flight) {
Collection result = new ArrayList();
try {
Connection conn = getConnection();
// Llamada a PL para buscar listado de vuelos.
int index = 1;
StringBuffer call = new StringBuffer();
call.append ("select * from book b, vlbk v ");
call.append(" where b.prbk_seq_cdg = v.prbk_seq_cdg ");
call.append(" and v.lnar_cdg_iata=?");
call.append(" and v.vlos_cdg=?");
call.append(" and v.vlos_fch=to_date(?,'dd/mm/yyyy') ");
call.append(" and v.vlbk_origen=?" );
call.append(" and v.vlbk_destino =?" );
// Crear statement y agregarlo a 'closer'.
PreparedStatement cstmt = conn.prepareStatement(call.toString());
// Se Asignan parámetros para el PL.
cstmt.setString ( index++, flight.getIataCode()); // 1
cstmt.setLong(index++, flight.getFlightNumber().longValue()); // 2
SimpleDate fecha = new SimpleDate(flight.getFlightDate());
cstmt.setString (index++, fecha.toString("dd/MM/yyyy") ); // 3
cstmt.setString( index++, flight.getFlightOrigin()); // 4
cstmt.setString( index++, flight.getFlightDestination()); // 5
LapseMeter l= new LapseMeter();
cstmt.execute(); // Ejecuta el PL.
System.out.println("time execute "+l.lapse());
ResultSet rs = cstmt.getResultSet(); // Ejecuta el PL.
// Obtiene cursor con los registros.
BookingDBO booking = null;
while ( rs.next()) {
booking = new BookingDBO();
booking.setBookingCode(new Long(rs.getLong("prbk_seq_cdg")));
booking.setCompanyCode(new Long(rs.getLong("book_empr_cdg")));
booking.setAirlineCode(rs.getString("lnar_cdg_iata"));
booking.setStatus(rs.getString("esbk_cdg_estd"));
booking.setBookingType(rs.getString("tpbk_cdg_booking"));
booking.setDocumentType(rs.getString("tdoc_cdg"));
booking.setUser(rs.getString("book_usuario"));
booking.setUserOffice(rs.getString("book_ofcn_usuario"));
booking.setDocumentOrigin(rs.getString("book_origen_doc"));
booking.setDocumentDestination(rs.getString
("book_destino_doc"));
booking.setFlightOrigin(rs.getString("book_origen_vuelo"));
booking.setFlightDestination(rs.getString
("book_destino_vuelo"));
//booking.setCreationDate( getSimpleDate
(rs,"book_fecha_gen").toDate() );
//booking.setFlightDate( getSimpleDate
(rs,"book_fecha_embarque").toDate() );
booking.setDocumentPrefix(new Long(rs.getLong
("book_pref_doc" )));
booking.setDocumentNumber(new Long(rs.getLong
("book_num_doc" )));
booking.setAgentCode(new Long(rs.getLong("book_clhl_cdg")));
booking.setAgentBranchCode(new Long(rs.getLong
("book_clhl_scrs")));
booking.setAgentContactName(rs.getString
("book_contacto_agente"));
booking.setAgentContactPhone (rs.getString
("book_fono_contacto"));
booking.setShipperCode(new Long(rs.getLong
("book_cdg_shipper")));
booking.setShipperBranchCode(new Long(rs.getLong
("book_scrs_shipper")));
booking.setShipperName(rs.getString("book_nmb_shipper"));
booking.setConsigneeCode(new Long(rs.getLong
("book_cdg_consignee")));
booking.setConsigneeName(rs.getString("book_nmb_consignee"));
booking.setConsigneeBranchCode(new Long(rs.getLong
("book_scrs_consignee")));
booking.setAverageRate(new Double(rs.getDouble("book_tarifa")));
booking.setRemarks(rs.getString("book_remarks"));
booking.setTotalWeight(new Double(rs.getDouble("book_gross")));
booking.setChargeableWeight(new Double(rs.getDouble
("book_chargeable")));
booking.setTotalVolume(new Double(rs.getDouble
("book_volumen_total")));
booking.setTotalPieces(new Long(rs.getLong
("book_total_piezas")));
booking.setAgentName(rs.getString("book_nmb_agente"));
booking.setAgentAcronym(rs.getString("book_acronimo_agente"));
booking.setShipperAcronym(rs.getString
("book_acronimo_shipper"));
booking.setConsigneeAcronym(rs.getString
("book_acronimo_consignee"));
booking.setContractId(rs.getString("cnto_id"));
booking.setContractRateType(rs.getString
("book_contrato_allocation"));
result.add(booking);
}
} catch (Exception x) {
x.printStackTrace();
}
return (List)result;
}
---------------------------------------
Source SqlMapDaoTemplate
---------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map
2.0//EN" "
http://www.ibatis.com/dtd/sql-map-2.dtd";<http://www.ibatis.com/dtd/sql-map-2.dtd%22>
<sqlMap namespace="Booking">
<typeAlias alias="flight"
type="com.lan.cargo.laboratorio.persistence.sqlembedded.dbo.FlightDBO"/>
<typeAlias alias="booking"
type="com.lan.cargo.laboratorio.persistence.sqlembedded.dbo.BookingDBO
"/>
<resultMap id="bookingResult" class="booking">
<result property="bookingCode" column="prbk_seq_cdg"/>
<result property="companyCode" column="book_empr_cdg" />
<result property="airlineCode" column="lnar_cdg_iata" />
<result property="status" column="esbk_cdg_estd" />
<result property="bookingType" column="tpbk_cdg_booking" />
<result property="documentType" column="tdoc_cdg" />
<result property="user" column="book_usuario" />
<result property="userOffice" column="book_ofcn_usuario" />
<result property="documentOrigin" column="book_origen_doc" />
<result property="documentDestination" column="book_destino_doc" />
<result property="flightOrigin" column="book_origen_vuelo" />
<result property="flightDestination" column="book_destino_vuelo" />
<result property="creationDate" column="book_fecha_gen" />
<result property="flightDate" column="book_fecha_embarque" />
<result property="documentPrefix" column="book_pref_doc" />
<result property="documentNumber" column="book_num_doc" />
<result property="agentCode" column="book_clhl_cdg" />
<result property="agentBranchCode" column="book_clhl_scrs" />
<result property="agentContactName" column="book_contacto_agente" />
<result property="agentContactPhone" column="book_fono_contacto" />
<result property="shipperCode" column="book_cdg_shipper" />
<result property="shipperBranchCode" column="book_scrs_shipper" />
<result property="shipperName" column="book_nmb_shipper" />
<result property="consigneeCode" column="book_cdg_consignee" />
<result property="consigneeName" column="book_nmb_consignee" />
<result property="consigneeBranchCode" column="book_scrs_consignee" />
<result property="averageRate" column="book_tarifa" />
<result property="remarks" column="book_remarks" />
<result property="totalWeight" column="book_gross" />
<result property="chargeableWeight" column="book_chargeable" />
<result property="totalVolume" column="book_volumen_total" />
<result property="totalPieces" column="book_total_piezas" />
<result property="agentName" column="book_nmb_agente" />
<result property="agentAcronym" column="book_acronimo_agente" />
<result property="shipperAcronym" column="book_acronimo_shipper" />
<result property="consigneeAcronym" column="book_acronimo_consignee" />
<result property="contractId" column="cnto_id" />
<result property="contractRateType"
column="book_contrato_allocation" />
</resultMap>
<!--***************************MAPPED
STATEMENTS**************************************-->
<select id="findBookingByFlight" resultMap="bookingResult"
parameterClass="flight" >
select
b.prbk_seq_cdg,
b.book_empr_cdg,
b.lnar_cdg_iata,
b.esbk_cdg_estd,
b.tpbk_cdg_booking,
b.tdoc_cdg,
b.book_usuario,
b.book_ofcn_usuario,
b.book_origen_doc,
b.book_destino_doc,
b.book_origen_vuelo,
b.book_destino_vuelo,
b.book_fecha_gen ,
b.book_fecha_embarque,
b.book_pref_doc,
b.book_num_doc,
b.book_clhl_cdg,
b.book_clhl_scrs,
b.book_contacto_agente,
b.book_acronimo_agente,
b.book_fono_contacto,
b.book_cdg_shipper,
b.book_scrs_shipper,
b.book_nmb_shipper,
b.book_cdg_consignee,
b.book_nmb_consignee ,
b.book_scrs_consignee,
b.book_tarifa,
b.book_remarks,
b.book_gross,
b.book_chargeable,
b.book_volumen_total,
b.book_total_piezas,
b.book_nmb_agente,
b.book_acronimo_shipper,
b.book_acronimo_consignee ,
b.cnto_id,
b.book_contrato_allocation
from book b, vlbk v
where b.prbk_seq_cdg = v.prbk_seq_cdg
and v.lnar_cdg_iata=#iataCode#
and v.vlos_cdg=#flightNumber#
and v.vlos_fch=#flightDate#
and v.vlbk_origen=#flightOrigin#
and v.vlbk_destino =#flightDestination#
</select>
</sqlMap>
------------------------------------------------
source SqlExecutor.java
-----------------------------------------------
errorContext.setMoreInfo("Check the parameters (set parameters
failed).");
request.getParameterMap().setParameters(request, ps, parameters);
errorContext.setMoreInfo("Check the statement (query failed).");
LapseMeter l= new LapseMeter();
ps.execute();
System.out.println("---*** execute "+l.lapse());
rs = ps.getResultSet();
