Hi Clinton,
Thank you very much by your information, the environment is:
application server: weblogic 8.1
transaction manager: JNDI WL8.1
EJB class with three DAO's, IBATIS (Framework DAO, PLSQLDAO, JDBCDAO)
I thought that Ibatis had the same perfomance that native JDBC, butI believe
that there is something bad in the source developed
Here is the source test:
-----------------------------------------------------------------
JDBC Native:
-----------------------------------------------------------------
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 =?" );
CallableStatement cstmt = conn.prepareCall(call.toString());
closer.add(cstmt);
setParameter(cstmt, index++, flight.getIataCode()); // 1
setParameter(cstmt, index++, flight.getFlightNumber().longValue
()); // 2
setParameter(cstmt, index++, new SimpleDate(flight.getFlightDate
())); // 3
setParameter(cstmt, index++, flight.getFlightOrigin()); // 4
setParameter(cstmt, index++, flight.getFlightDestination()); // 5
ResultSet rs = cstmt.executeQuery(); // Ejecuta el PL.
BookingDBO booking = null;
closer.add(rs);
for (boolean first = true; rs.next();) {
-----------------------------------------------------------------
PL/SQL:
-----------------------------------------------------------------
call.append("{ call cbkg_testing.getbookingbyflight
(?,?,?,?,?,?)");
CallableStatement cstmt = conn.prepareCall(call.toString());
closer.add(cstmt);
setParameter(cstmt, index++, flight.getIataCode()); // 1
setParameter(cstmt, index++, flight.getFlightNumber().longValue
()); // 2
setParameter(cstmt, index++, new SimpleDate(flight.getFlightDate
())); // 3
setParameter(cstmt, index++, flight.getFlightOrigin()); // 4
setParameter(cstmt, index++, flight.getFlightDestination()); // 5
cstmt.registerOutParameter(index, AbstractDAO.getCursorType());
cstmt.execute(); // Ejecuta el PL.
// Obtiene cursor con los registros.
BookingDBO booking = null;
ResultSet rs = getResultSet(cstmt, index);
PLSQL
PROCEDURE getBookingByFlight(
flightCode IN vlbk.lnar_cdg_iata%type,
flightNumber IN vlbk.vlos_cdg%type,
flightDate VARCHAR2,
origin VARCHAR2,
destination VARCHAR2,
io_cursor OUT t_cursor
) IS
-- Declara variables y cursores!...
excErrorDatosIn EXCEPTION;
BEGIN
OPEN io_cursor FOR
select * from book b, vlbk v
where b.prbk_seq_cdg = v.prbk_seq_cdg
and v.lnar_cdg_iata=flightCode
and v.vlos_cdg=flightNumber
and v.vlos_fch=to_date(flightDate,'dd/mm/yyyy')
and v.vlbk_origen=origin
and v.vlbk_destino =destination;
-----------------------------------------------------------------
IBATIS with framework DAO:
-----------------------------------------------------------------
SQL-MAP.xml
<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>
<cacheModel id="flight-cache" type ="LRU" readOnly="false"
serialize="false" >
<flushInterval hours="24"/>
<flushOnExecute statement="findBookingByFlight"/>
</cacheModel>
<!--***************************MAPPED
STATEMENTS**************************************-->
<select id="findBookingByFlight" resultMap="bookingResult"
cacheModel="flight-cache" parameterClass="flight" >
select * 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>
******************* sql-map-config.xml ***********************************
<properties resource="sqlembedded/sql-map-config.properties" />
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
useStatementNamespaces="false"
maxRequests="512"
maxSessions="128"
maxTransactions="32"
/>
<transactionManager type="JDBC" >
<!-- dataSource type="JNDI">
<property name="DataSource" value="bookingds"/>
</dataSource -->
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
</dataSource>
</transactionManager >
<sqlMap resource="sqlembedded/bookingcollections.xml" />
