Excluding xerces/xercesImpl from your CXF project

I'm not sure how much time I spent trying to figure out why my new service was not working, if I followed the same steps I usually follow on all my past CXF apps;
The only difference was to include a new dependency which has more business logic.

Then comparing my last CXF project lib folder with my new one, I found xercesImpl*.jar was additional to my current project.
I just removed from my POM (added as exclusion from my new dependency) and Listo! my CXF Web Service is working as expected :D
<dependency> <groupId>GSB.core</groupId> <artifactId>GSB-core</artifactId> <version>1.0.0</version> <exclusions> <exclusion> <groupId>xerces</groupId> <artifactId>xercesImpl</artifactId> </exclusion> </exclusions> </dependency>

Quest Toad on Oracle 11g not supported

Are you already on Oracle Database 11g?

Nevertheless, this post is to warn all developers out there using Quest TOAD for Oracle.
The current release of TOAD is 9.1, this version is not supported with Oracle 11g (client). But even more important, the version that comes out half of November, TOAD 9.5 (see screenshot below which is a beta), isn't working with the Oracle 11g client neither!
And looking around I found this message coming from Quest: Unfortunately, it was confirmed by our team's technical lead that Toad version 9.5 only support Oracle server 11g and not Oracle client 11g. My only suggestion for you is to install another client on your machine that is 10gR2 version or lower."

It's hard to believe that you need to install an Oracle Server just to get your development environment (TOAD) running ;-) Nevertheless, a workaround is to install Oracle 10g client, which I don't want to do as I'm connecting to 11g databases.
Or go for SQL Developer, a free alternative of Oracle.

A screenshot of the error "OCI version 11.1.0.1.0 is not supported"


I'm using both SQL Developer and TOAD as I think both have strengths and points to improve.
This post isn't to favor for SQL Developer, it's just to warn you.

Conditions in One Line

Interesting point, how can I do 1 LINE If statements like in Java: a==b?true:false This is the answer: SELECT ACCT_ORDER.cidb_order_id, (CASE WHEN mtn_order_type = 'E' AND mtn_order_state = 'P' THEN 1 ELSE 0 END) I'll see on resultset cidb_order_id, 1 or 0 (segun sea el caso) The same sentence could be used on UPDATES: UPDATE CUST_ACCT_ORDER_STATS SET addline_pending_cnt = CASE WHEN addline_pending_cnt - my_add_count < 0 THEN 0 ELSE addline_pending_cnt - my_add_count END WHERE cust_id = my_cust_id AND acct_num = my_acct_num;

My first official LEFT JOIN

When I was at school I always was afraid of using LEFT OR RIGHT Joins, well, almost 6 years after I'm done with school, I had to do my first one: SELECT ACCT_ORDER.CUST_ID, ACCT_ORDER.ACCT_NUM, MTN_ORDER.mtn, MTN_ORDER.mtn_order_type, MTN_ORDER.mtn_order_state, MTN_ORDER.mtn_order_state_date, MTN_ORDER.bdy_mtn, MTN_ORDER.cidb_order_id, MTN_STATE.mtn_eligibility_state, MTN_STATE.mtn_eligibility_state_date, ACCT_ORDER.pos_order_id, ACCT_ORDER.pos_pre_order_id, ACCT_ORDER.pos_loc_cd, ACCT_ORDER.order_state, ACCT_ORDER.order_state_date FROM CUST_ACCT_ORDER ACCT_ORDER LEFT JOIN CUST_ACCT_MTN_ORDER MTN_ORDER ON ACCT_ORDER.acct_num = MTN_ORDER.acct_num AND ACCT_ORDER.cust_id = MTN_ORDER.cust_id AND ACCT_ORDER.cidb_order_id = MTN_ORDER.cidb_order_id LEFT JOIN CUST_ACCT_MTN_STATE MTN_STATE ON MTN_ORDER.cust_id = MTN_STATE.cust_id AND MTN_ORDER.acct_num = MTN_STATE.acct_num AND MTN_ORDER.mtn = MTN_STATE.mtn WHERE( (ACCT_ORDER.pos_order_id = p_pos_order_id AND ACCT_ORDER.pos_loc_cd = p_pos_loc_cd) OR ACCT_ORDER.pos_pre_order_id = p_pos_pre_order_id ) ORDER BY MTN_ORDER.mtn;

Add new parameter to Stored Procedure without impacting existing call

I have a Stored procedure already deployed in a production box, there is another porcess which wants to use it in a batch process, but the main batch process wants to control the transaction (commit/rollback). What can I do??
Add another parameter to my SP to handle the transaction or not, but If I do this the signature (DDL) of my SP is going to change!
There is one way to do this, something like adding polymorphism, on this case overload SP.
This is my original SP: CREATE OR REPLACE PROCEDURE dispo_pre_order ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_pos_order_id IN NUMBER, p_pos_loc_cd IN VARCHAR2, p_mtn IN NUMBER ) On my iBatis side I'm calling this SP with this number of Params (5), If I add a new param, I'll have to add a new one to my DDL, right?
Not necessarily, because If you add another parameter but you don't declare it as IN neither OUT, it will be optional!!!
Then I can do this: CREATE OR REPLACE PROCEDURE dispo_pre_order ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_pos_order_id IN NUMBER, p_pos_loc_cd IN VARCHAR2, p_mtn IN NUMBER, p_commit NUMBER DEFAULT 1 ) And nobody will notice that I change it, because you can call it with 5 params (The 6th param will be given by default in the signature: DEFAULT 1) or with 6 (assigning 0 for example to the 6th parameter)
Inside the body of my SP I'm doing something like this: IF p_commit = 1 THEN COMMIT; --Close Entire Tx END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); IF p_commit = 1 THEN ROLLBACK; --Roll back Tx END IF; RAISE; END;

Set Timeout when accessing Oracle with iBatis

Scenario:
There is a call which takes too much time, we don't want to wait until it's done, so in this case we can timeout our transaction and kill it on the Oracle side (well we don't kill/stop anything, we just abandon the process without any adverse impact);
Then we'll receive something like this from the DB:

ORA-01013: user requested cancel of current operation

How do you do that??
According to iBatis documentation, we can do it globally or for each statement/call we make by changing the value of defaultStatementTimeout
<settings cacheModelsEnabled="true" enhancementEnabled="true" lazyLoadingEnabled="true" maxRequests="128" maxSessions="10" maxTransactions="5" useStatementNamespaces="false" defaultStatementTimeout="5" /> defaultStatementTimeout This setting is an integer value that will be applied as the JDBC query timeout for all statements. This value can be overridden with the “statement” attribute of any mapped statement. If not specified, no query timeout will be set unless specified on the “statement” attribute of a mapped statement. The specified value is the number of seconds the driver will wait for a statement to finish. Note that not all drivers support this setting.

Or we can do it Specifically for each statement element by setting the attribute timeout, for example:
<procedure id="ShipPreOrder" parameterMap="paramsMapShipPreOrder" timeout="2"> {call ship_pre_order(?,?,?,?,?)} </procedure>

Create List of STRUCT based on a Query

This is my problem: I have a Stored Procedure which receives as parameter a List of Objects, this is my object
CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER AS OBJECT ( cust_id NUMBER, acct_num NUMBER, mtn NUMBER, cidb_order_id NUMBER, mtn_order_state VARCHAR(1), mtn_order_type VARCHAR(1), mtn_order_state_date DATE, bdy_mtn NUMBER )
In Oracle to create a new instance of this kind of object, the default Contructor is given by definition of the object, for example in this case to create a new instance of CUST_ACCNT_MTN_PRE_ORDER, my contructor will be something like this new CUST_ACCNT_MTN_PRE_ORDER(cust_id, acct_num, mtn, cidb_order_id, mtn_order_state, mtn_order_type, mtn_order_state_date, bdy_mtn)
And this is my Collection/List/Table of Objects
CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER_LIST AS TABLE OF CUST_ACCNT_MTN_PRE_ORDER
My SP is something like this (As you'll see I'm using the same objects I defined in that entry)

And my solution is something like this DECLARE p_list CUST_ACCNT_MTN_PRE_ORDER_LIST; BEGIN SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0)) BULK COLLECT INTO p_list FROM ( SELECT CUST_ID_NO AS CUST_ID, ACCT_NO AS ACCT_NUM, NPA , NXX , TLN, NVL(ESN_CHG_RSN_CD,'A') AS MTN_ORDER_TYPE, EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN FROM TEST_INIT_LOAD WHERE CUST_ID_NO = my_cust_id AND ACCT_NO = my_acct_num AND EQ_ORD_NO = my_pos_order_id AND ESN_CHG_RSN_CD IS NULL UNION SELECT CUST_ID_NO AS CUST_ID, ACCT_NO AS ACCT_NUM, NPA , NXX , TLN, 'U' AS MTN_ORDER_TYPE, EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN FROM TEST_INIT_LOAD WHERE CUST_ID_NO = my_cust_id AND ACCT_NO = my_acct_num AND EQ_ORD_NO = my_pos_order_id AND ESN_CHG_RSN_CD IS NOT NULL ); END;
The secret is in this line: SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0)) BULK COLLECT INTO p_list
I'm creating for each row an object of type CUST_ACCNT_MTN_PRE_ORDER and then the ResultSet is set INTO p_list variable using BULK COLLECT, which fetch all rows once


IF you want to see my Stored Proc, this is what I did

CREATE OR REPLACE PROCEDURE record_ship_temp_orders(   
 out_orders_processed   OUT   NUMBER
)
IS
my_cust_id    CUST_ACCT_ORDER.cust_id%TYPE; 
my_acct_num    CUST_ACCT_ORDER.acct_num%TYPE;
my_pos_order_id    CUST_ACCT_ORDER.pos_order_id%TYPE;
my_pos_pre_order_id    CUST_ACCT_ORDER.pos_pre_order_id%TYPE;
my_pos_loc_cd    CUST_ACCT_ORDER.pos_loc_cd%TYPE;
my_total_order_lines    CUST_ACCT_ORDER.total_order_lines%TYPE;
my_ord_add_lines_cnt    CUST_ACCT_ORDER.add_lines_cnt%TYPE;   
my_ord_upg_lines_cnt    CUST_ACCT_ORDER.upg_lines_cnt%TYPE;  
p_list  CUST_ACCNT_MTN_PRE_ORDER_LIST; 
OUT_CIDB_ORDER_ID NUMBER;

--This is the main cursor 
CURSOR cur_test_init_load
    IS
    SELECT CUST_ID_NO AS CUST_ID, 
            ACCT_NO AS ACCT_NUM, 
            EQ_ORD_NO AS POS_ORDER_ID, 
            NULL AS POS_PRE_ORDER_ID, 
            NETACE_LOC_ID AS POS_LOC_CD             
    FROM TEST_INIT_LOAD
    GROUP BY CUST_ID_NO, ACCT_NO, EQ_ORD_NO, NULL, NETACE_LOC_ID
    ORDER BY CUST_ID_NO, ACCT_NO, EQ_ORD_NO, NETACE_LOC_ID;
    
/******************************************************************************
   NAME:    record_ship_temp_orders
   PURPOSE:    Retrieve data loaded into TEST_INIT_LOAD to be processed by
     Record_Pre_Order and then shipped by Ship_Pre_Order.
   DATE:    Oct 2010
   Referenced by:    To be executed internally

******************************************************************************/    
    
BEGIN 
DBMS_OUTPUT.put_line('Starting prcess '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
OPEN cur_test_init_load;  
        IF cur_test_init_load%NOTFOUND THEN         
  DBMS_OUTPUT.put_line('No data found on TEST_INIT_LOAD');   
  CLOSE cur_test_init_load;
  raise_application_error(-20102, 'No data found on TEST_INIT_LOAD');
 END IF; 
 
 out_orders_processed := cur_test_init_load%ROWCOUNT; --Before the first fetch, %ROWCOUNT returns 0
 
 DBMS_OUTPUT.put_line('Start record '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
 LOOP --Iterate cursor
 FETCH cur_test_init_load INTO my_cust_id, my_acct_num, my_pos_order_id, my_pos_pre_order_id, my_pos_loc_cd;
 
 IF out_orders_processed = 0 THEN
  out_orders_processed := cur_test_init_load%ROWCOUNT; --Set a value to the OUT param
 END IF;
 
 EXIT WHEN cur_test_init_load%NOTFOUND;--Exits when process is completed

 DBMS_OUTPUT.put_line('Processing  '||my_cust_id||'-'||my_acct_num||', my_pos_order_id='||my_pos_order_id||', my_pos_pre_order_id='||my_pos_pre_order_id||', my_pos_loc_cd='||my_pos_loc_cd);
 
  --Calculate total_order_lines, add_lines_cnt & upg_lines_cnt
            SELECT count(1) AS TOTAL_ODER_LINES INTO my_total_order_lines
            FROM TEST_INIT_LOAD
            WHERE CUST_ID_NO = my_cust_id
              AND ACCT_NO =  my_acct_num
              AND EQ_ORD_NO = my_pos_order_id;
              
            SELECT count(1) AS ADD_LINES_CNT INTO my_ord_add_lines_cnt
            FROM TEST_INIT_LOAD
            WHERE CUST_ID_NO = my_cust_id
              AND ACCT_NO =  my_acct_num
              AND EQ_ORD_NO = my_pos_order_id
              AND ESN_CHG_RSN_CD IS NULL;
  
            SELECT count(1) AS UPG_LINES_CNT INTO my_ord_upg_lines_cnt
            FROM TEST_INIT_LOAD
            WHERE CUST_ID_NO = my_cust_id
              AND ACCT_NO =  my_acct_num
              AND EQ_ORD_NO = my_pos_order_id
              AND ESN_CHG_RSN_CD IS NOT NULL;
              

 DBMS_OUTPUT.put_line('my_total_order_lines  '||my_total_order_lines||', my_ord_add_lines_cnt='||my_ord_add_lines_cnt||', my_ord_upg_lines_cnt='||my_ord_upg_lines_cnt);
              
               --Generate List of Items to be inserted into CUST_ACCT_MTN_ORDER
            SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0)) BULK COLLECT INTO p_list
            FROM (  
                SELECT CUST_ID_NO AS CUST_ID, 
                            ACCT_NO AS ACCT_NUM,
                            NPA , NXX , TLN,
                            NVL(ESN_CHG_RSN_CD,'A') AS MTN_ORDER_TYPE,
                            EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN 
                FROM TEST_INIT_LOAD
                WHERE CUST_ID_NO = my_cust_id
                  AND ACCT_NO =  my_acct_num
                  AND EQ_ORD_NO = my_pos_order_id
                  AND ESN_CHG_RSN_CD IS NULL
  
                  UNION
  
                SELECT CUST_ID_NO AS CUST_ID, 
                            ACCT_NO AS ACCT_NUM,
                            NPA , NXX , TLN,
                            'U' AS MTN_ORDER_TYPE,
                            EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN
                FROM TEST_INIT_LOAD
                WHERE CUST_ID_NO = my_cust_id
                  AND ACCT_NO =  my_acct_num
                  AND EQ_ORD_NO = my_pos_order_id 
                  AND ESN_CHG_RSN_CD IS NOT NULL    
            ); 
            
     FOR i in 1 .. p_list.count
     LOOP
  DBMS_OUTPUT.put_line ('STRUCT '||i||'=> mtn='||p_list(i).mtn||', mtn_order_type='||p_list(i).mtn_order_type||', bdy_mtn='||p_list(i).bdy_mtn);
     END LOOP;  
     --Call SP's
      BEGIN 
   DBMS_OUTPUT.put_line('Calling Record_Pre_Order');
   RECORD_PRE_ORDER ( my_cust_id, my_acct_num, my_pos_order_id, my_pos_pre_order_id, my_pos_loc_cd, my_total_order_lines, my_ord_add_lines_cnt, my_ord_upg_lines_cnt, 0, p_list, OUT_CIDB_ORDER_ID );
   DBMS_OUTPUT.put_line('Calling Ship_Pre_Order');
   SHIP_PRE_ORDER ( my_cust_id, my_acct_num, my_pos_order_id, my_pos_loc_cd, OUT_CIDB_ORDER_ID ); 
   DBMS_OUTPUT.put_line('End record '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
   DBMS_OUTPUT.put_line('-------');
   p_list := NULL;
  EXCEPTION
      WHEN OTHERS
      THEN
      DBMS_OUTPUT.put_line ('***ERROR*** '||SQLERRM);
      out_orders_processed := out_orders_processed - 1; --Decrease failed orders
  END;
 END LOOP; 
DBMS_OUTPUT.put_line('Process completed');
DBMS_OUTPUT.put_line('End of process '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
CLOSE cur_test_init_load;
 
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
      RAISE;
END;
/

SQL Joins explained with Venn's Diagrams

SQL Joins explicados de forma gráfica con diagramas Venn

Una simple ayuda

El lenguaje SQL es aveces el gran olvidado por los desarrolladores, cada vez abundan mas los frameworks que abstraen al desarrollador del contacto con el modelo de negocio.

He escrito este documento, basándome en otros similares para ayudar a entender con un diagrama de Vann, los diferentes tipos de Join's que SQL92 soporta.

Existen tecnologías que abstraen completamente del modelo de negocio, para el desarrollador funcionan creando una serie de clases de Dominio que define el modelo, sin importar qué base de datos o de qué fabricante sea esta. Por ejemplo Ruby on Rails, GRails, ... usando un conjunto de tecnologías, como Hibernate configuradas por convención dentro del propio framework.

También es muy extendido el uso de aplicaciones que permiten modelar el negocio de forma gráfica, ERM (como DBSchema), y normalmente después se usa un ORM's que les hacen el trabajo sucio de forma elegante y segura.

Todo vale, pero la realidad de las empresas TIC es que necesitan profesionales serios y conscientes que entiendan y controlen todas las capas para que un sistema funciona como se espera y está diseñado, y no se deje nada al azar.

El desarrollador, muy frecuentemente recurre a activar trazas o aplicaciones de monitorización de actividad que nos desvelen, qué está haciendo nuestro framework con nuestro modelo de negocio. Existen aplicaciones que esnifan directamente de la base de datos esta información para que podamos analizarla, el obsoleto Profile y Analyzer que incluía Microsoft SQL Server es un ejemplo de ellos.

Finalmente cuando tenemos delante la query sucede que hace tanto tiempo que no trabajamos con SQL que no entendemos qué hace exactamente, sobre todo si está trabajando con tablas relacionadas y nos encontramos con una consulta que afecta a varias entidades.

Espero que este documento ayude a reducir el tiempo invertido a descubrir porqué se comporta una SQL de un modo u otro.

Convenciones Este documento asume que siempre que la "TABLA A" esta a la izquierda y la "TABLA B" a la derecha de las sentencias. Para los ejemplos vamos a utilizar 2 tablas que van a contener los siguientes datos: id name | id name 1 Roberto | 1 Alex 2 Juan | 2 Carlos 3 Rubén | 3 Juan 4 Carlos | 4 Saúl
INNER JOIN SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name id name | id name 2 Juan | 2 Carlos 4 Carlos | 3 Juan El resultado son solo el conjunto de registros que coinciden en ambas tablas.
FULL OUTER JOIN SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name id name | id name 1 Roberto | null null 2 Juan | 3 Juan 3 Rubén | null null 4 Carlos | 2 Carlos null null | 1 Alex null null | 4 Saúl El resultado es el conjunto total de registros de ambas tablas, coincidiendo aquellos registros cuando sea posible. Si no hay conicidencias, se asignan nulos.
FULL OUTER JOIN WHERE SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null id name | id name 1 Roberto | null null 3 Rubén | null null null null | 1 Alex null null | 4 Saúl El resultado es un conjunto de records únicos en la TablaA y en la TablaB, hacemos el Full Outer Join y excluimos los registros que no queremos con el Where
LEFT OUTER JOIN SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name id name | id name 1 Roberto | null null 2 Juan | 3 Juan 3 Rubén | null null 4 Carlos | 2 Carlos El resultado son todos los registros de la TablaA, y si es posible las coincidencias de la TablaB. Si no hay coincidencias, el lado derecho mostrará nulos.
LEFT OUTER JOIN WHERE SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null id name | id name 1 Roberto | null null 3 Rubén | null null El resultado es un conjunto de registros que solo están en la TablaA, no en la TablaB. Hacemos lo mismo que en un Left Outer Join, pero eliminamos los registros que no queremos de la TablaB con el Where
CROSS JOIN Existe también la posibilidad de cruzar todos los registros con todos (producto cartesiano), imposible de dibujar con un diagramas Venn SELECT * FROM TableA CROSS JOIN TableB Imaginarse el resultado de todos los registros por todos es muy fácil, si tenemos 4 registros en cada tabla 4 x 4 = 16.	¡Ojo al hacer esto en tablas con muchos registros!

Validation Query on WebSphere DataSource

Do you remember this?? (for those who have worked before with DBCP : validationQuery)
<dataSource type="DBCP"> <property name="driverClassName" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> <property name="initialSize" value="2"/> <property name="maxActive" value="10"/> <property name="maxIdle" value="2"/> <property name="maxWait" value="-1"/> <property name="removeAbandoned" value="true"/> <property name="removeAbandonedTimeout" value="300"/> <property name="logAbandoned" value="true"/> <property name="testWhileIdle" value="true"/> <property name="testOnReturn" value="true"/> <property name="testOnBorrow" value="true"/> <property name="validationQuery" value="${validationQuery}"/> </dataSource>
I was tired to look for this attribute in Websphere and I found it :P

Tip: On DB2 the equivalent to SELECT 1 FROM DUAL is SELECT 1 FROM SYSIBM.SYSDUMMY1

Pass Collection of Objects with Attributes to a Stored Procedure using iBatis

In my last entry I had a requirement which needed to pass a List to an Oracle Stored Procedure, well, that thing is history.
But now the problem becomes more complex, because, what happen if I'm expecting on my Stored Proc and Type (STRUCT)
This is my Collection in Oracle! CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER AS OBJECT( cust_id NUMBER, acct_num NUMBER, mtn NUMBER, cidb_order_id NUMBER, mtn_order_state VARCHAR(1), mtn_order_type VARCHAR(1), mtn_order_state_date DATE, bdy_mtn NUMBER ); CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER_LIST AS TABLE OF CUST_ACCNT_MTN_PRE_ORDER;
And this is my Stored Proc (this is just a sample which receives the TABLE of Records) CREATE OR REPLACE PROCEDURE test_list_params ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_list IN CUST_ACCNT_MTN_PRE_ORDER_LIST ) IS BEGIN FOR i in 1 .. p_list.count LOOP INSERT INTO TESTING_SP (CUST_ID, ACCT_NUM, MTN, CIDB_ORDER_ID, MTN_ORDER_STATE, MTN_ORDER_TYPE, BDY_MTN) VALUES (p_cust_id, p_acct_num, p_list(i).mtn, p_list(i).cidb_order_id, p_list(i).mtn_order_state, p_list(i).mtn_order_type, p_list(i).bdy_mtn); DBMS_OUTPUT.put_line ('STRUCT '||i); DBMS_OUTPUT.put_line ('mtn='||p_list(i).mtn||', cidb_order_id='||p_list(i).cidb_order_id||', mtn_order_state='||p_list(i).mtn_order_state); DBMS_OUTPUT.put_line ('mtn_order_type='||p_list(i).mtn_order_type||', bdy_mtn='||p_list(i).bdy_mtn); END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Some other kind of error occurred.'); raise_application_error(-20102, 'Some other kind of error occurred.'); ROLLBACK; END;
This is the table where I'm going to INSERT CREATE TABLE TESTING_SP( CUST_ID NUMBER, ACCT_NUM NUMBER, MTN NUMBER, CIDB_ORDER_ID NUMBER, MTN_ORDER_STATE VARCHAR2(1 BYTE), MTN_ORDER_TYPE VARCHAR2(1 BYTE), MTN_ORDER_STATE_DATE DATE DEFAULT SYSDATE, BDY_MTN NUMBER ) TABLESPACE ACSS_DATA01;
Obviously, the solution is Create a Handler like I did on my last entry, but is not going to be as simple, because I need to Map my Oracle STRUCT with my Java Code, so I have to do an domain object (POJO) coupled to Oracle and my Handler. import java.sql.Connection; import java.sql.SQLException; import java.util.Date; import oracle.jdbc.driver.OracleTypes; import oracle.jpub.runtime.MutableStruct; import oracle.sql.Datum; import oracle.sql.ORAData; import oracle.sql.ORADataFactory; import oracle.sql.STRUCT; /** * Java Implementation of an Oracle STRUCT/TYPE.<BR> * This is a nasty looking model because it implements ORAData and ORADataFactory. * * It's really important to follow the order/index of each attribute on the Oracle Type (see getter/setter methods) * * <PRE> * CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER AS OBJECT( * cust_id NUMBER, * acct_num NUMBER, * mtn NUMBER, * cidb_order_id NUMBER, * mtn_order_state VARCHAR(1), * mtn_order_type VARCHAR(1), * mtn_order_state_date DATE, * bdy_mtn NUMBER ) * </PRE> * * @author Juan Cruz * @since 09/20/2010 * * @see oracle.sql.ORAData * @see oracle.sql.ORADataFactory * @see oracle.sql.STRUCT * */ public class CustomerAccountMtnOrderStruct implements ORAData, ORADataFactory { public static final String SQL_NAME = "CUST_ACCNT_MTN_PRE_ORDER"; public static final int SQL_TYPECODE = OracleTypes.STRUCT; protected MutableStruct struct; static int[] sqlTypes = { OracleTypes.NUMBER, OracleTypes.NUMBER, OracleTypes.NUMBER, OracleTypes.NUMBER, OracleTypes.VARCHAR, OracleTypes.VARCHAR, OracleTypes.DATE, OracleTypes.NUMBER }; static ORADataFactory[] oraDataFactories = new ORADataFactory[sqlTypes.length]; static final CustomerAccountMtnOrderStruct myInstance = new CustomerAccountMtnOrderStruct(); private Long customerId; private Integer accountNumber; private Long mtn; private Integer cidbOrderId; private String mtnOrderState; private String mtnOrderType; private Date mtnOrderStateDate; private Long buddyMtn; protected CustomerAccountMtnOrderStruct(boolean init) { if (init) { struct = new MutableStruct(new Object[sqlTypes.length], sqlTypes, oraDataFactories); } } public CustomerAccountMtnOrderStruct() { this(true); } public Datum toDatum(Connection conn) throws SQLException { Datum datum = struct.toDatum(conn, SQL_NAME); return datum; } public ORAData create(Datum datum, int sqlType) throws SQLException { return create(null, datum, sqlType); } protected ORAData create(CustomerAccountMtnOrderStruct customerAccountMtnOrderStruct, Datum datum, int sqlType) throws SQLException { if (datum == null) { return null; } if (customerAccountMtnOrderStruct == null) { customerAccountMtnOrderStruct = new CustomerAccountMtnOrderStruct(false); } customerAccountMtnOrderStruct.struct = new MutableStruct((STRUCT) datum, sqlTypes, oraDataFactories); return customerAccountMtnOrderStruct; } public static ORADataFactory getORADataFactory() { return myInstance; } public Long getCustomerId() throws SQLException { return (Long)struct.getAttribute(0); } public void setCustomerId(Long customerId) throws SQLException { this.customerId = customerId; struct.setAttribute(0, customerId); } public Integer getAccountNumber() throws SQLException { return (Integer)struct.getAttribute(1); } public void setAccountNumber(Integer accountNumber) throws SQLException { this.accountNumber = accountNumber; struct.setAttribute(1, accountNumber); } public Long getMtn() throws SQLException { return (Long)struct.getAttribute(2); } public void setMtn(Long mtn) throws SQLException { this.mtn = mtn; struct.setAttribute(2, mtn); } public Integer getCidbOrderId() throws SQLException { return (Integer)struct.getAttribute(3); } public void setCidbOrderId(Integer cidbOrderId) throws SQLException { this.cidbOrderId = cidbOrderId; struct.setAttribute(3, cidbOrderId); } public String getMtnOrderState() throws SQLException { return (String)struct.getAttribute(4); } public void setMtnOrderState(String mtnOrderState) throws SQLException { this.mtnOrderState = mtnOrderState; struct.setAttribute(4, mtnOrderState); } public String getMtnOrderType() throws SQLException { return (String)struct.getAttribute(5); } public void setMtnOrderType(String mtnOrderType) throws SQLException { this.mtnOrderType = mtnOrderType; struct.setAttribute(5, mtnOrderType); } public Date getMtnOrderStateDate() throws SQLException { return (Date)struct.getAttribute(6); } public void setMtnOrderStateDate(Date mtnOrderStateDate) throws SQLException { this.mtnOrderStateDate = mtnOrderStateDate; struct.setAttribute(6, mtnOrderStateDate); } public Long getBuddyMtn() throws SQLException { return (Long)struct.getAttribute(7); } public void setBuddyMtn(Long buddyMtn) throws SQLException { this.buddyMtn = buddyMtn; struct.setAttribute(7, buddyMtn); } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((accountNumber == null) ? 0 : accountNumber.hashCode()); result = prime * result + ((cidbOrderId == null) ? 0 : cidbOrderId.hashCode()); result = prime * result + ((customerId == null) ? 0 : customerId.hashCode()); result = prime * result + ((mtn == null) ? 0 : mtn.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; CustomerAccountMtnOrderStruct other = (CustomerAccountMtnOrderStruct) obj; if (accountNumber == null) { if (other.accountNumber != null) return false; } else if (!accountNumber.equals(other.accountNumber)) return false; if (cidbOrderId == null) { if (other.cidbOrderId != null) return false; } else if (!cidbOrderId.equals(other.cidbOrderId)) return false; if (customerId == null) { if (other.customerId != null) return false; } else if (!customerId.equals(other.customerId)) return false; if (mtn == null) { if (other.mtn != null) return false; } else if (!mtn.equals(other.mtn)) return false; return true; } public String toString() { StringBuffer buffer = new StringBuffer(); buffer.append("[CustomerAccountMtnOrderStruct:"); buffer.append(" customerId: "); buffer.append(customerId); buffer.append(" accountNumber: "); buffer.append(accountNumber); buffer.append(" mtn: "); buffer.append(mtn); buffer.append(" cidbOrderId: "); buffer.append(cidbOrderId); buffer.append(" mtnOrderState: "); buffer.append(mtnOrderState); buffer.append(" mtnOrderStateDate: "); buffer.append(mtnOrderStateDate); buffer.append(" mtnOrderType: "); buffer.append(mtnOrderType); buffer.append(" buddyMtn: "); buffer.append(buddyMtn); buffer.append("]"); return buffer.toString(); } }
Once we have our Mapper class, then let's do the Handler!!! import java.sql.Connection; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.List; import oracle.jdbc.driver.OracleTypes; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import com.ibatis.sqlmap.client.extensions.ParameterSetter; import com.ibatis.sqlmap.client.extensions.ResultGetter; import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback; import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry; import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection; import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil; import com.vzw.common.globalrouter.dao.db.domain.CustomerAccountMtnOrder; import com.vzw.common.globalrouter.dao.db.typehandler.struct.CustomerAccountMtnOrderStruct; public class CustomerAccountMtnPreOrderListTypeHandlerCallBack implements TypeHandlerCallback { private static final String STRUCT_NAME = CustomerAccountMtnOrderStruct.SQL_NAME; private static final String ARRAY_NAME = "CUST_ACCNT_MTN_PRE_ORDER_LIST"; /*static { JdbcTypeRegistry.setType(STRUCT_NAME, OracleTypes.STRUCT); JdbcTypeRegistry.setType(ARRAY_NAME, OracleTypes.ARRAY); };*/ @SuppressWarnings("unchecked") public void setParameter(ParameterSetter parameterSetter, Object param) throws SQLException { if (param ==null) { parameterSetter.setNull(Types.ARRAY); }else{ List<CustomerAccountMtnOrder> listCustomerAccountMtnOrder = (ArrayList<CustomerAccountMtnOrder>) param; Connection nativeConnection = (Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)parameterSetter.getPreparedStatement().getConnection()); CustomerAccountMtnOrderStruct[] structArray = new CustomerAccountMtnOrderStruct[listCustomerAccountMtnOrder.size()]; for(int i=0; i<listCustomerAccountMtnOrder.size(); i++){ CustomerAccountMtnOrder customerAccountMtnOrder = listCustomerAccountMtnOrder.get(i); structArray[i] = transformCustomerAccountMtnOrder(customerAccountMtnOrder); } ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(ARRAY_NAME, nativeConnection); ARRAY dataArray = new ARRAY(arrayDescriptor, nativeConnection, structArray); parameterSetter.setArray(dataArray); } } public Object valueOf(String arg0) { // TODO Auto-generated method stub return null; } public Object getResult(ResultGetter resultGetter) throws SQLException { // TODO Auto-generated method stub return null; } private CustomerAccountMtnOrderStruct transformCustomerAccountMtnOrder(final CustomerAccountMtnOrder customerAccountMtnOrder) throws SQLException { CustomerAccountMtnOrderStruct customerAccountMtnOrderStruct = new CustomerAccountMtnOrderStruct(); customerAccountMtnOrderStruct.setAccountNumber(customerAccountMtnOrder.getAccountNumber()); customerAccountMtnOrderStruct.setBuddyMtn(customerAccountMtnOrder.getBuddyMtn()); customerAccountMtnOrderStruct.setCidbOrderId(customerAccountMtnOrder.getCidbOrderId()); customerAccountMtnOrderStruct.setCustomerId(customerAccountMtnOrder.getCustomerId()); customerAccountMtnOrderStruct.setMtn(customerAccountMtnOrder.getMtn()); customerAccountMtnOrderStruct.setMtnOrderState(customerAccountMtnOrder.getMtnOrderState()); customerAccountMtnOrderStruct.setMtnOrderStateDate(customerAccountMtnOrder.getMtnOrderStateDate()); customerAccountMtnOrderStruct.setMtnOrderType(customerAccountMtnOrder.getMtnOrderType()); return customerAccountMtnOrderStruct; } }
And this is how it looks my Map (XML) <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="SP"> <parameterMap id="parametersMapTestListParams" class="java.util.Map"> <parameter property="customerId" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN" /> <parameter property="accountNumber" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN" /> <parameter property="listCustomerAccountMtnOrder" typeHandler="com.vzw.common.globalrouter.dao.db.typehandler.CustomerAccountMtnPreOrderListTypeHandlerCallBack" mode="IN"/> </parameterMap> <procedure id="TestListParams" parameterMap="parametersMapTestListParams"> {call ${env.qualifier}.test_list_params(?,?,?)} </procedure> </sqlMap>

In case you have to receive the Array from your Stored Proc, you just have to write in your Handler somethig like this: public Object getResult(ResultGetter getter) { ArrayList result = new ArrayList(); try { ARRAY array = (ARRAY)getter.getObject(); Object[] employees = (Object[])array.getArray(); for (Object emp: employees) { STRUCT s = (STRUCT)emp; Employee e = new Employee(); e.setId(((BigDecimal)s.getAttributes()[0]).longValue()); e.setName((String)s.getAttributes()[1]); e.setDateOfBirth((Date)s.getAttributes()[2]); result.add(e); } } catch (Exception e) { e.printStackTrace(); } return result; }

References: Using iBatis with a stored function returning a Collection Type and Oracle UDT Collections as IN and OUT parameters with iBatis

Pass Collection to Stored Procedure using iBatis

Today I had a really crazy idea: "Why not making my query work with a list of parameters like I do with Hibernate using the operator IN???"
It sounds simple, Isn't it??? Ok, then try to do it with an Stored Procedure in Oracle :P
Then, this is my Stored Proc (I had to create a Type to have support for Arrays in Oracle, because an Array is not a simple-provided data type): CREATE OR REPLACE TYPE NUMBER_ARRAY AS TABLE OF NUMBER; CREATE OR REPLACE PROCEDURE retrieve_mtn_eligibility_state ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_array_mtn IN NUMBER_ARRAY, p_recordset OUT SYS_REFCURSOR ) AS BEGIN OPEN p_recordset FOR SELECT mtn_eligibility_state, mtn_eligibility_state_date, mtn, acct_num, cust_id FROM CUST_ACCT_MTN_STATE WHERE cust_id = p_cust_id AND acct_num = p_acct_num AND mtn IN ( SELECT * FROM TABLE(p_array_mtn) ); --TODO: Verify MTN given has CUST_ACCT_MTN_ORDER.bdy_mtn dbms_output.put_line('This is the content of that array'); FOR i IN 1 .. p_array_mtn.count LOOP dbms_output.put_line(i||'=' || p_array_mtn(i)); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Some other kind of error occurred.'); END; / As you can see, my dear visitor, I'm expecting some simple parameters and I'm returning a ResultSet, really straight forward, but the problem comes with the Array of NUMBER (that's why I created my

TYPE NUMBER_ARRAY AS TABLE OF NUMBER

)

The solution to this problem is easier than it seems, just create a Handler dude!!! import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection; import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil; import com.ibatis.sqlmap.client.extensions.ParameterSetter; import com.ibatis.sqlmap.client.extensions.ResultGetter; import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback; public class NumberArrayTypeHandlerCallBack implements TypeHandlerCallback { @SuppressWarnings("unchecked") public void setParameter(ParameterSetter setter, Object parameter) throws SQLException { if(parameter == null) { setter.setNull(Types.ARRAY); } else if(parameter instanceof ArrayList) { Statement stmt = setter.getPreparedStatement(); Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection()); ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection); parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray()); } setter.setObject(parameter); } public Object getResult(ResultGetter getter) throws SQLException { return null; } public Object valueOf(String arg0) { return null; } } The magic begins when I transform the Connection (because I have to work at the connection level) provided by the Application Server (WebSphere on this case - If this happens to me on another App Server or with another Connection Pool, I'm going to be in trouble, because the issue will be how to retrieve a Native java.sql.Connection, but that will be another story).
Let's analyze this monster:
* Get Native Connection Connection nativeConnection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) stmt.getConnection());
*Create my ArrayDescriptor for my Type (these NUMBER_ARRAY is my TYPE I created previously) ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBER_ARRAY", nativeConnection);
*Create my parameter, an ARRAY (al fin y al cabo) and we're set! parameter = new ARRAY(desc, nativeConnection, ((ArrayList) parameter).toArray());

Finally this is my mapping file for my stored procedure <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="SP"> <parameterMap id="objectParameters" class="java.util.Map"> <parameter property="cust_id" jdbcType="INTEGER" javaType="java.lang.String" mode="IN" /> <parameter property="acc_num" jdbcType="INTEGER" javaType="java.lang.String" mode="IN" /> <parameter property="array_mtn" jdbcType="NUMBER_ARRAY" typeName="NUMBER_ARRAY" typeHandler="mx.com.company.common.db.typehandler.NumberArrayTypeHandlerCallBack" mode="IN"/> <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/> </parameterMap> <resultMap id="resultSetMap" class="mx.com.company.MyDomainObject" > <result column="mtn_eligibility_state" property="mtnEligibilityState" /> <result column="mtn_eligibility_state_date" property="mtnEligibilityStateDate" /> </resultMap> <procedure id="retrieve_mtn_eligibility_state" parameterMap="objectParameters" resultMap="resultSetMap" > {call ${env.qualifier}.retrieve_mtn_eligibility_state(?,?,?,?)} </procedure> </sqlMap>

And my code lived happily ever after!!! Abur!!!

JDBC url for oracle RAC

If you have to connect to oracle RAC (Real Application Cluster) using JDBC with thin driver, the classic url:

jdbc:oracle:thin:@<HOST>:1521:<SID>

doesn’t work and you get the error ORA – 12505.
Instead, you must use this url:

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service)))

If you have an oracle client, like toad, check the tsnames.ora for the correct values of SERVICE_NAME and host.

Log SQL Statements on Hibernate

Hibernate, in the end, is all about dispatching SQL statements. SQL is at the heart of communication with an RDBMS system, and it is extremely important when struggling through performance problems or other bugs, that you know what is going on. After all, knowing the executed SQL allows you to determine the number of queries to complete an O/R mapping task, not to mention that seeing SQL queries is critical to understanding how to optimize the queries via indices and other database settings.
There are two ways (due to the legacy of Hibernate) to enable SQL logging. The first is to simply enable SQL logging in the Hibernate configuration By setting the hibernate.show_sql to TRUE

This is a nice quick and dirty solution, but it is relatively inflexible. SQL statements are always logged to System.out when that property is enabled, and on some servers, System.out isn't accessible by the average developer; or is cluttered with a million other log statements.

Alternatively, Hibernate uses the Apache-Jakarta Commons Logging package, which means that it will utilize log4j , java.util.logging , or potentially another logging framework. Typically, log messages are sent to commons logging, and then they are dispatched to one of these logging frameworks. Then, those logging frameworks determine where the message should be sent (log files, sockets, emails, database records, system out, system err, etc). It is easy to see how using these log frameworks will increase the flexibility of log statements. With Hibernate, simply setting the org.hibernate.SQL logger to 'DEBUG' or 'ALL' will ensure that all SQL statements are logged to the console.


If you set up a log4j category for org.hibernate.type
Get it to write out to the same log file as the org.hibernate.SQL
The type one will list the parameters for you after the SQL e.g.

2006-07-28 09:57:12,061 DEBUG org.hibernate.SQL - insert into BASKET_LINE_ALLOC (LAST_UPDATED, QUANTITY, CUSTOMER_REF, NOTES, BRANCH_ID, FUND_ID, TEMPLATE_ID, 
BASKET_LINE_ALLOC_ID) values (?, ?, ?, ?, ?, ?, ?, ?) 
2006-07-28 09:57:12,081 DEBUG org.hibernate.type.TimestampType - binding '2006-07-28 09:57:12' to parameter: 1 
2006-07-28 09:57:12,081 DEBUG org.hibernate.type.IntegerType - binding '3' to parameter: 2 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.StringType - binding '' to parameter: 3 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.StringType - binding '' to parameter: 4 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding '511' to parameter: 5 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding '512' to parameter: 6 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding null to parameter: 7 
2006-07-28 09:57:12,082 DEBUG org.hibernate.type.LongType - binding '180030' to parameter: 8 

@Autowired and @Qualifier

"Your best friends could be @Autowired and @Qualifier"
I have to migrate data from DB2 to Oracle, both databases have the same structure, well, my Oracle has better design, but I'm reusing the some objects, like POJO's and DAO's, the only thing I changed were my mappings, because I have to keep my mappings for DB2 and for Oracle.
If I have the same DAO for both databases, what can I do?
daoApplicationContext.xml <?xml version="1.0" encoding="UTF-8" ?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jee="http://www.springframework.org/schema/jee" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd"> <!-- ========================= Data Sources ============================= --> <bean id="db2DS" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="COM.ibm.db2.jdbc.app.DB2Driver"/> <property name="url" value="jdbc:db2:best"/> <property name="username" value="${userId}"/> <property name="password" value="${pwd}"/> </bean> <bean id="oracleDS" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> <property name="url" value="jdbc:oracle:thin:@twsifcmtzdt01.tdc.vzwcorp.com:1535:mkssbxdb"/> <property name="username" value="${userId}"/> <property name="password" value="${pwd}"/> </bean> <!--============================= SessionFactory ===============================--> <bean id="sessionFactoryDB2" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource" ref="db2DS" /> <property name="configLocation"> <value>classpath:com/vzw/dvs/mapping/mappingDB2.cfg.xml</value> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect"> org.hibernate.dialect.DB2Dialect </prop> <prop key="hibernate.show_sql">false</prop> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.order_updates">true</prop> <prop key="hibernate.generate_statistics">true</prop> <prop key="hibernate.use_sql_comments">false</prop> <prop key="hibernate.bytecode.use_reflection_optimizer"> false </prop> <prop key="hibernate.jdbc.batch_size">15</prop> <prop key="hibernate.jdbc.batch_versioned_data"> true </prop> <prop key="hibernate.query.factory_class"> org.hibernate.hql.ast.ASTQueryTranslatorFactory </prop> <prop key="hibernate.connection.isolation">4</prop> </props> </property> </bean> <bean id="sessionFactoryOracle" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource" ref="oracleDS" /> <property name="configLocation"> <value>classpath:com/vzw/dvs/mapping/mappingOracle.cfg.xml</value> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect"> org.hibernate.dialect.Oracle10gDialect </prop> <prop key="hibernate.show_sql">false</prop> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.order_updates">true</prop> <prop key="hibernate.generate_statistics">true</prop> <prop key="hibernate.use_sql_comments">false</prop> <prop key="hibernate.bytecode.use_reflection_optimizer"> false </prop> <prop key="hibernate.jdbc.batch_size">15</prop> <prop key="hibernate.jdbc.batch_versioned_data"> true </prop> <prop key="hibernate.query.factory_class"> org.hibernate.hql.ast.ASTQueryTranslatorFactory </prop> <prop key="hibernate.connection.isolation">4</prop> </props> </property> </bean> <!-- ============================ DAOs DB2 ================================== --> <bean id="UserDAOdb2" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryDB2" /> </bean> <!-- ============================ DAOs Oracle ================================== --> <bean id="UserDAO" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryOracle" /> </bean> </beans>
As you can see, I have 2 different DAO's sharing the same Interface & Implementation, but each one belongs to a different "sessionFactory" <bean id="UserDAOdb2" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryDB2" /> </bean> <bean id="UserDAO" class="com.vzw.dvs.core.persistence.dao.impl.UserDAOImpl"> <property name="sessionFactory" ref="sessionFactoryOracle" /> </bean> serviceApplicationContext.xml <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd"> <!-- =============== JTA ==========================================--> <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"> <property name="sessionFactory"> <ref bean="sessionFactoryOracle" /> </property> </bean> <!--====================== Transaction Managers with Annotations =====================--> <tx:annotation-driven transaction-manager="transactionManager" /> <!--====================== Enable Autowire =====================--> <context:annotation-config /> <!--================== Implementation of Services =====================--> <bean id="MigrateTablesService" class="com.vzw.dvs.core.business.service.web.impl.MigrateTablesServiceImpl"/> </beans>
Nothing special, right? But now here it comes the interesting part, because @Autowired by itself it will inject the given bean in your ServiceImpl, but you must have only one bean of that type. In my case I have more than 1, so @Autowired will throw an error when loading the context.
Then it comes @Qualifier which will match based on the given name, and problem solved chavos!!!
public class MigrateTablesServiceImpl implements MigrateTablesService { private static final Logger log = Logger.getLogger(MigrateTablesServiceImpl.class); @Autowired @Qualifier("UserDAOdb2") private UserDAO userDAOdb2; @Autowired @Qualifier("UserDAO") private UserDAO userDAO; @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public boolean doMigrateUser() { log.debug("Migrating User"); boolean result = false; try{ List<User> listItems = this.userDAOdb2.getAll(); for(User transientInstance: listItems){ log.debug("Item retrieved from DB2 "+transientInstance); User user = new User(); BeanUtils.copyProperties(transientInstance, user); //Trim Strings user.setDbUserId(StringUtils.trim(transientInstance.getDbUserId())); user.setBudgetmanagerind(StringUtils.trim(transientInstance.getBudgetmanagerind())); user.setDashboardaccess(StringUtils.trim(transientInstance.getDashboardaccess())); user.setDirectorusercd(StringUtils.trim(transientInstance.getDirectorusercd())); user.setEmail(StringUtils.trim(transientInstance.getEmail())); user.setFirstname(StringUtils.trim(transientInstance.getFirstname())); user.setFunctionalareacd(StringUtils.trim(transientInstance.getFunctionalareacd())); user.setHomephone(StringUtils.trim(transientInstance.getHomephone())); user.setInternalit(StringUtils.trim(transientInstance.getInternalit())); user.setLastname(StringUtils.trim(transientInstance.getLastname())); user.setMobilephone(StringUtils.trim(transientInstance.getMobilephone())); user.setPassword(StringUtils.trim(transientInstance.getPassword())); user.setUserid(StringUtils.trim(transientInstance.getUserid())); user.setUserstatuscd(StringUtils.trim(transientInstance.getUserstatuscd())); user.setVpid(StringUtils.trim(transientInstance.getVpid())); user.setUtilizationtarget(StringUtils.trim(transientInstance.getUtilizationtarget())); user.setWorkphone(StringUtils.trim(transientInstance.getWorkphone())); if(user.getDbTimestamp()==null){ user.setDbTimestamp(new Date()); } this.userDAO.save(user); } result = true; }catch (Exception ex) { log.warn(ex); } log.debug("Done"); return result; }
Notes: I used org.springframework.beans.BeanUtils to create a copy of my bean (pojo) returned from DB2 into a non-persistent Pojo which I'll manipulate before inserting into Oracle (DB2 completes each column value with spaces to achieve the given size of that column, so each time you query a DB2 column you'll get a lot of extra spaces)

EmbeddedId in Hibernate, AnnotatedClasses

I had to do some inserts to a table with a composite PK:



Nothing special, I decided to use Hibernate with annotations (but what about the PK: just create another class -duh, but How can I tell hibernate this is my PK? @EmbeddedId and @Embeddable):

@Embeddable

Defines my composite PK @Embeddable public class RimMessageId implements Serializable { private static final long serialVersionUID = -1L;//Just 4 fun ;) @Column(name = "dvs_server", length = 10, nullable = false) private String server; @Column(name = "dvs_yyyy", length = 4, nullable = false) private String year; @Column(name = "dvs_month", length = 2, nullable = false) private String month; @Column(name = "dvs_dd", length = 2, nullable = false) private String day; @Column(name = "dvs_hh", length = 2, nullable = false) private String hour; @Column(name = "dvs_mm", length = 2, nullable = false) private String minute; @Column(name = "dvs_ss", length = 2, nullable = false) private String second; @Column(name = "dvs_millis", length = 6, nullable = false) private String millis; //Gette's and Setter's }

@EmbeddedId

Includes my composite Pk in my main Pojo @Entity @Table(name = "RIM_MESSAGES") public class RimMessage implements Serializable { private static final long serialVersionUID = 1L;;//Just 4 fun ;) @EmbeddedId private RimMessageId id; @Column(name = "mdn", length = 10) private String mdn; @Column(name = "bun", length = 20) private String bun; @Column(name = "evt", length = 6) private String evt; @Column(name = "bsh", length = 2) private String bsh; @Column(name = "bex", length = 10) private String bex; @Column(name = "lng", length = 3) private String lng; @Column(name = "rim_tod", length = 4) private String rimTod; @Column(name = "rim_doy", length = 8) private String rimDoy; @Column(name = "rim_ip", length = 15) private String rimIp; //Gette's and Setter's }
If you want to include in your sessionFactory (daoApplicationContext.xml) my annotated pojos, add this entry <property name="annotatedClasses"> <list> <value>com.vzw.gsb.core.persistence.integration.RimMessage</value> </list> </property>
As a reminder, if you want to use a propeties file to provide parameter for your DataSource, you have to do this: <!-- ========================= Properties ============================= --> <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location"> <value>classpath:rim-rtr-messages.properties</value> </property> </bean> <!-- ========================= Data Sources ============================= --> <bean id="myDataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${driverClassName}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </bean>

How to test servlet using Telnet

Essentially what I need to do is just make an HTTP Call using Telnet.

$> telnet 127.0.0.1 9086

Ok, I'm connected, and now what??
I know which servlet I want to test, I know which parameters is expecting, then "let's do it":

Which method should I use? POST or GET?? let's use POST (due to well known limitations with GET)

This is my URL:

/MyWebApp/proxyServlet?operation=doSomethingNice&consumerName=myActiveMQConsumers&server=192.168.5.122:9080&targetServlet=/MyOtherWebApp/utilsServlet

I'll finish my command with the HTTP version I want to use: HTTP/1.0 (if I use 1.1 it'll keep you connected)

And finally let's put all together:
POST /MyWebApp/proxyServlet?operation=doSomethingNice&consumerName=myActiveMQConsumers&server=192.168.5.122:9080&targetServlet=/MyOtherWebApp/utilsServlet HTTP/1.0
Important: Once you have copied this full line in your telnet body, please type ENTER twice.

How do I know if this works? You should see this on your telnet console:

HTTP/1.0 200 OK
Content-Type: text/xml;charset=ISO-8859-1
Cache-Control: no-cache
Content-Language: en-US
Content-Length: 21
Date: Thu, 19 Aug 2010 18:38:45 GMT
Server: WebSphere Application Server/6.1

true

Connection to host lost.

Small Proxy App based on Apache Mina 2.0.0-RC1

What I'm going to do is develop a small app which basically is going to broadcast a message received to another host in a defined port.
My small app is just a standalone app based on Apache Mina, is going to be listening on "X" port and it's going to broadcast message received to another app which is a blackbox. So simple, Isn't it?


What do you need?

• mina-filter-compression-2.0.0-RC1.jar
• mina-integration-xbean-2.0.0-RC1.jar
• slf4j-log4j12-1.5.0.jar
• mina-integration-beans-2.0.0-RC1.jar
• jzlib-1.0.7.jar
• jaxen-1.1.1.jar
• mina-transport-apr-2.0.0-RC1.jar
• log4j-1.2.15.jar
• slf4j-api-1.5.0.jar
• mina-core-2.0.0-RC1.jar

Or simple, you can get the full distribution and get them all in one single file.
This jar is just a luxury, but it's necesary, because I don't want to reinvent the wheel: commons-lang-2.3.jar

Well, time to work, the first thing you should code is the main class, my ProxyServer and 2 IoHandlers (ProxyHandlerAdapter & ClientHandlerAdapter).

And this is my code
public class GSBProxyServer { private static Logger logger = Logger.getLogger(GSBProxyServer.class); private int initPoolSize = Runtime.getRuntime().availableProcessors()+1; private int maxPoolSize = 20; private int keepAliveTimeInSecs = 10;//for each thread in our pool private static SocketAcceptor acceptor; private static ExecutorService executor; public GSBProxyServer(){ LogUtils.initialize(); if(logger.isInfoEnabled()){ logger.info("GSB TCP Proxy server starting."); } try { if(logger.isDebugEnabled()){ logger.debug("loding properties file."); } ReceiverFactory.load(Constants.PROPERTIES_FILE); } catch (Exception e) { e.printStackTrace(); logger.error("Tcp Receiver factory load failed."); System.exit(-1); } IoBuffer.setUseDirectBuffer(false);//switch the default buffer type to 'heap' IoBuffer.setAllocator(new SimpleBufferAllocator()); //IoBuffer.setAllocator(new CachedBufferAllocator()); executor = new OrderedThreadPoolExecutor(initPoolSize, maxPoolSize, keepAliveTimeInSecs, TimeUnit.SECONDS);//Ordered/UnorderedThreadPoolExecutor has prevention mechanism against OutOfMemoryError, so you will prefer these two classes to other Executor implementations probably. acceptor = new NioSocketAcceptor(initPoolSize); acceptor.getFilterChain().addLast("codec",new ProtocolCodecFilter(new TextLineCodecFactory(Charset.forName("UTF-8")))); acceptor.getFilterChain().addLast("threadPool", new ExecutorFilter(executor)); //acceptor.getSessionConfig().setReadBufferSize(2048);//by default is 2048 acceptor.getSessionConfig().setIdleTime(IdleStatus.BOTH_IDLE, 1); acceptor.getSessionConfig().setTcpNoDelay(true); try { acceptor.setHandler(new ProxyHandlerAdapter()); acceptor.bind(new InetSocketAddress(Constants.PORT_NUMBER)); if(logger.isInfoEnabled()){ logger.info("GSB TCP Proxy server started. Listening on port " + Constants.PORT_NUMBER); } } catch (Exception e) { e.printStackTrace(); stop(); } } public static void main(String[] args) throws IOException { new GSBProxyServer(); } public static void stop() { logger.info("GSB TCP Proxy server stopping."); if (acceptor != null) { acceptor.unbind(); acceptor = null; } if (executor != null) { executor.shutdown(); executor = null; } } }

public class ProxyHandlerAdapter extends IoHandlerAdapter { private static Logger logger = Logger.getLogger(ProxyHandlerAdapter.class); private ClientHandlerAdapter clientHandlerAdapter; public ProxyHandlerAdapter(){ this.clientHandlerAdapter = new ClientHandlerAdapter(); } /** * When exception happens and it's caugth * * @param session * @param throwable */ public void exceptionCaught(IoSession session, Throwable throwable) throws Exception { throwable.printStackTrace(); session.close(false); } /** * Event for message received * * @param session * @param msg */ @SuppressWarnings("unchecked") public void messageReceived(IoSession session, Object msg) throws Exception { String str = msg.toString(); long initialTime = System.currentTimeMillis(); if(logger.isDebugEnabled()){ logger.debug("Entry messageReceived"); } try{ if(logger.isInfoEnabled()){ logger.info("messageReceived: " +str +" -from local:"+session.getLocalAddress()+" remote:"+session.getRemoteAddress()); } if (StringUtils.equalsIgnoreCase("quit", StringUtils.trim(str))) { session.close(true); if (clientHandlerAdapter != null) { clientHandlerAdapter.shutdown(); } return; } this.clientHandlerAdapter.broadcastMessage(str);//call clientHandler which is going to broadcast msg if(logger.isInfoEnabled()){ logger.info("send session acknowledge"); } session.write("ACK\r\n"); }catch (Exception ex) { logger.error(ex); ex.printStackTrace(); } if(logger.isDebugEnabled()){ logger.debug("Exit messageReceived: "+(System.currentTimeMillis()-initialTime)); } } }

public class ClientHandlerAdapter extends IoHandlerAdapter { private static Logger logger = Logger.getLogger(ClientHandlerAdapter.class); private int initPoolSize = Runtime.getRuntime().availableProcessors()+1; private int maxPoolSize = Constants.NUMBER_THREADS; private int keepAliveTimeInSecs = 5;//for each thread in our pool private int connectionTimeoutInMillis = 5 * 1000;//by default 5 seconds private SocketConnector socketConnector; private ExecutorService executor; public ClientHandlerAdapter(){ socketConnector = new NioSocketConnector(initPoolSize); executor = new OrderedThreadPoolExecutor(initPoolSize, maxPoolSize, keepAliveTimeInSecs, TimeUnit.SECONDS); socketConnector.getFilterChain().addLast("codec",new ProtocolCodecFilter(new TextLineCodecFactory(Charset.forName("UTF-8")))); socketConnector.getFilterChain().addLast("threadPool", new ExecutorFilter(executor));//One thread pool for most events socketConnector.getFilterChain().addLast("threadPool-msgSent", new ExecutorFilter(initPoolSize, maxPoolSize, keepAliveTimeInSecs, TimeUnit.SECONDS, IoEventType.MESSAGE_SENT)); socketConnector.getFilterChain().addLast("threadPool-msgReceived", new ExecutorFilter(initPoolSize, maxPoolSize, keepAliveTimeInSecs, TimeUnit.SECONDS, IoEventType.MESSAGE_RECEIVED)); socketConnector.setHandler(this); socketConnector.getSessionConfig().setTcpNoDelay(true);//No delays on TCP communication socketConnector.getSessionConfig().setKeepAlive(false); socketConnector.setConnectTimeoutMillis(connectionTimeoutInMillis);//set timeout } /** * When exception happens and it's caugth * * @param session * @param throwable */ public void exceptionCaught(IoSession session, Throwable t) throws Exception { t.printStackTrace(); try{ CloseFuture closeFuture = session.close(true); //closeFuture.awaitUninterruptibly();// Wait until the connection is closed? closeFuture.setClosed();// Now connection should be closed. }catch (Exception ignore) { //ignore } } /** * Broadcast message * * @param message */ @SuppressWarnings("unchecked") public void broadcastMessage(final String message) throws Exception { long initialTime = System.currentTimeMillis(); if(logger.isDebugEnabled()){ logger.debug("Entry broadcastMessage"); } try{ String appName = StringUtils.substring(message, 3, 6); if(logger.isDebugEnabled()){ logger.debug("appName:" + appName); } List<Receiver> aTcpReceiverList = ReceiverFactory.applicationMap.get(appName); if(aTcpReceiverList!=null){ for(Receiver aTcpReceiver: aTcpReceiverList){ try{ this.sendMessage(aTcpReceiver, message); }catch (Exception ex) { logger.error("Error trying to connect to "+aTcpReceiver.getHostName()+":"+aTcpReceiver.getPortNumber()+". "+ex.getMessage()); try{ this.sendMessage(aTcpReceiver, message); }catch (Exception newEx) { logger.error("Error trying to connect <<again>> to "+aTcpReceiver.getHostName()+":"+aTcpReceiver.getPortNumber()+". "+newEx.getMessage()); ex.printStackTrace(); } } } } }catch (Exception ex) { logger.error(ex); ex.printStackTrace(); } if(logger.isDebugEnabled()){ logger.debug("Exit broadcastMessage: "+(System.currentTimeMillis()-initialTime)); } } /** * Sends a given message to a Receiver * * @param aTcpReceiver * @param message * @throws Exception * * @see com.vzw.gsb.network.Receiver */ @SuppressWarnings("unused") private void sendMessageOld(final Receiver aTcpReceiver, final String message) throws Exception{ long initialTime = System.currentTimeMillis(); if(logger.isDebugEnabled()){ logger.debug("Entry sendMessage"); } IoSession session = null; ConnectFuture connectFuture = socketConnector.connect(new InetSocketAddress(aTcpReceiver.getHostName(),aTcpReceiver.getPortNumber())); connectFuture.awaitUninterruptibly();//Wait until the connection attempt is finished. session = connectFuture.getSession(); session.write(message);//write message to remote server if(logger.isInfoEnabled()){ logger.info("message sent to: "+aTcpReceiver.getHostName()+":"+aTcpReceiver.getPortNumber()); } if(logger.isDebugEnabled()){ logger.debug("Exit sendMessage: "+(System.currentTimeMillis()-initialTime)); } //connectFuture.cancel();//Cancels the connection attempt and notifies all threads waiting for this future? } /** * Sends a given message to a Receiver. * I used a IoFutureListener on my ConnectFuture to wait for acknowledgment that the client has connected to the server. * Once we know we are connected, we can start writing data to the server * * @param aTcpReceiver * @param message * @throws Exception * * @see com.vzw.gsb.network.Receiver * @see org.apache.mina.core.future.IoFutureListener * @see org.apache.mina.core.future.ConnectFuture */ private void sendMessage(final Receiver aTcpReceiver, final String message) throws Exception{ long initialTime = System.currentTimeMillis(); if(logger.isDebugEnabled()){ logger.debug("Entry sendMessage"); } ConnectFuture connectFuture = socketConnector.connect(new InetSocketAddress(aTcpReceiver.getHostName(),aTcpReceiver.getPortNumber())); connectFuture.addListener(new IoFutureListener<IoFuture>(){ public void operationComplete(IoFuture future) { /*long initialTime = System.currentTimeMillis(); if(logger.isInfoEnabled()){ logger.info("Entry operationComplete"); }*/ ConnectFuture connFuture = (ConnectFuture)future; if(connFuture.isConnected()){ IoSession session = future.getSession(); session.write(message);//write message to remote server if(logger.isInfoEnabled()){ logger.info("message sent to: "+aTcpReceiver.getHostName()+":"+aTcpReceiver.getPortNumber()); } }else{ logger.error("Timeout trying to connecto to: "+aTcpReceiver.getHostName()+":"+aTcpReceiver.getPortNumber()); } /*if(logger.isInfoEnabled()){ logger.info("Exit operationComplete: "+(System.currentTimeMillis()-initialTime)); }*/ } }); if(logger.isDebugEnabled()){ logger.debug("Exit sendMessage: "+(System.currentTimeMillis()-initialTime)); } } /** * Event when message is received (ACK) from target server * * @param session * @param message */ @Override public void messageReceived(IoSession session, Object message) throws Exception { long initialTime = System.currentTimeMillis(); if(logger.isInfoEnabled() && StringUtils.isNotEmpty(message.toString())){//ACK coming from remote server logger.info("messageReceived: " +message);//+" -from local:"+session.getLocalAddress()+" remote:"+session.getRemoteAddress()+", closing session & closeFuture"); } if(logger.isDebugEnabled() && StringUtils.isNotEmpty(message.toString())){ logger.debug("Entry messageReceived"); } try{//once ACK comes, I close this session CloseFuture closeFuture = session.close(true); //closeFuture.awaitUninterruptibly();// Wait until the connection is closed? closeFuture.setClosed();// Now connection should be closed. if(logger.isDebugEnabled() && StringUtils.isNotEmpty(message.toString())){ logger.debug("isFutureClosed?"+closeFuture.isClosed()+", isSessionClosing?"+session.isClosing()); } }catch (Exception ignore) { //ignore } if(logger.isDebugEnabled() && StringUtils.isNotEmpty(message.toString())){ logger.debug("Exit messageReceived: "+(System.currentTimeMillis()-initialTime)); } } /** * Release all resources (SocketConnector) and ExecutorService * * @throws Exception */ public void shutdown() throws Exception { if(socketConnector!=null){ socketConnector.dispose(); } if (executor != null) { executor.shutdown(); } } }

How can I test it??? Just by create a client using java.net.Socket or just by using Telnet to that particular port

I didn't include other classes because they're not important

Log4J tips

Add thread name to my log file; sometimes we want to know which thread is being executed, well, try this:
ConversionPattern=%d [%5p] [%t] %c{1}.%m%n

That's right just by adding "%t", you can see which thread is doing what.

Spring MVC - Binding VO to a Form

Problema:
I have a VO and I wanted to populate it on my JSP and on my controller just process the VO already populated.

Solution:
@Controller @RequestMapping(value = "/loggers/*") public class LoggersConfigurationController extends BaseCustomController { private static final Logger log = Logger .getLogger(LoggersConfigurationController.class); @SuppressWarnings("unchecked") @RequestMapping(value = "configureLoggers.do", method = RequestMethod.GET) public String showScreen(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) { if (!this.isLoggedIn(request)) { return "redirect:/authentication/notLoggedIn.do"; } log.debug("Show Screen when RequestMethod.GET"); //By doing this I'm preparing my diabolic plan!!! LoggerVO loggerVO = new LoggerVO(); modelMap.put("LoggerVO", loggerVO); return "configuration/loggersConfiguration"; } @SuppressWarnings("unchecked") @RequestMapping(value = "configureLoggers.do", method = RequestMethod.POST) public String updateLogger( HttpServletRequest request, HttpServletResponse response, ModelMap modelMap, @ModelAttribute(value="LoggerVO") LoggerVO loggerVO) { if (!this.isLoggedIn(request)) { return "redirect:/authentication/notLoggedIn.do"; } log.debug("Update logger when RequestMethod.POST"); List<String> messages = new ArrayList<String>(); log.debug("@ModelAttribute="+loggerVO); LogUtils.getInstance().setLogger(loggerVO); messages.add(loggerVO.getName()+" updated successfully to "+loggerVO.getLevel()); modelMap.put("KEY_LIST_MESSAGES", messages); //return "configuration/loggersConfiguration"; return "index-tabbed"; } }
On my POST method I'll handle my VO already populated.
This entry will do 50% of the job @ModelAttribute(value="LoggerVO") LoggerVO loggerVO
On my JSP I'll map using spring's tag library <%@taglib prefix="spring" uri="http://www.springframework.org/tags" %> <%@taglib prefix="form" uri="http://www.springframework.org/tags/form" %> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <H2>Loggers Configuration</H2> <form:form id="loggersForm" name="loggersForm" modelAttribute="LoggerVO" commandName="LoggerVO" action="/${pageContext.servletContext.servletContextName}/loggers/configureLoggers.do" method="POST"> <table width="100%"> <tr> <td><label for="name">Logger Name</label></td> <td> <form:select path="name" items="${applicationScope.KEY_LIST_LOGGERS}" itemValue="name" itemLabel="name"/> </td> </tr> <tr> <td><label for="level">Level</label></td> <td> <form:select path="level" items="${applicationScope.KEY_LIST_LOG_LEVELS}"/> </td> </tr> </table> <p> <input type="submit" value="Update"> </p> <div style="visibility: hidden"> <form:select path="appenderVOs"/> </div> </form:form> <c:if test="${!empty requestScope.KEY_LIST_MESSAGES}"> <script>alert("${requestScope.KEY_LIST_MESSAGES}");</script> </c:if>
By doing this modelAttribute="LoggerVO" commandName="LoggerVO"
I'm matching what I have on my form and what I have on my Controller <form:form id="loggersForm" name="loggersForm" modelAttribute="LoggerVO" commandName="LoggerVO" action="/${pageContext.servletContext.servletContextName}/loggers/configureLoggers.do" method="POST">
I hope this will help you!
Salu2

Cross Domain issue - Ajax

Who say yes???
I was working in a GUI for controlling components in different servers from one server - stopping, starting, purging queues & consumers; finally beans
The firSt thing it came to my mind was: "let's do it with AJAX to avoid multiple calls and decrease the traffic!!!"
Which AJAX framework should I choose??? Let's learn jQuery, because that one has a bunch of cool features which will be really helpfull to develop my GUI (check this site: flowplayer.org they have a bunch of demos which are really easy to follow and all based on jQuery)
My first issue was learnign that Ajax call, but after a few hours I could do my first ajax call using jQuery (I have a servlet which returns XML or JSON)

Now what??? If I'm accessing the servlet in the same context - domain, everything works fine, but... If I try to access my servlet but in another server

Tons tuve que eccharle coco
My solution was to create a ProxyServlet which basically will take my request and pass it to the remote server & servlet using an HttpClient and in the same way, it will get the response coming from the remote servlet, in other words, my ProxyServlet (in the same domain - context) will be the only one talking to my Ajax call, eventhough ProxyServlet will be talking to the remote server & servlet. Then I did it again  

This is my code:
import java.io.IOException; import java.util.HashMap; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.httpclient.DefaultHttpMethodRetryHandler; import org.apache.commons.httpclient.HttpClient; import org.apache.commons.httpclient.HttpException; import org.apache.commons.httpclient.MultiThreadedHttpConnectionManager; import org.apache.commons.httpclient.methods.PostMethod; import org.apache.commons.httpclient.params.HttpConnectionManagerParams; import org.apache.commons.httpclient.params.HttpMethodParams; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; /** * ProxyServlet * Makes http request to an external resource and returns the response gotten. * This servlet is my workaround for Cross Domain (same origin policy) issue. * * @author Juan Cruz * @since Aug 11 2010 */ public class ProxyServlet extends HttpServlet { private static final Logger log = Logger.getLogger(ProxyServlet.class); private HttpClient httpClient = new HttpClient(); @Override public void init() throws ServletException { super.init(); this.initializeHttpClient(); } @Override protected void doGet(HttpServletRequest requqest, HttpServletResponse response) throws ServletException, IOException { this.doPost(requqest, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/xml"); response.setHeader("Cache-Control", "no-cache"); String server = StringUtils.trim(request.getParameter("server")); String targetServlet = StringUtils.trim(request.getParameter("targetServlet")); Map<String, String> parameters = new HashMap<String, String>(); for(Object paramName : request.getParameterMap().keySet()){//Get only String parameters if (paramName instanceof String && request.getParameter(paramName+"") instanceof String) { parameters.put(paramName+"", request.getParameter(paramName+"")); } } String result = this.sendRequest(server,targetServlet, parameters); if(result!=null){ response.getWriter().write(result); }else{ response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR); } } /** * Send request to desired target * * @param server * @param targetServlet * @param parameters * @return String */ private String sendRequest(final String server, final String targetServlet, final Map<String, String> parameters) { log.debug("sendRequest "+server+targetServlet+", "+parameters); String responseBody = null; // Create a method instance. PostMethod method = new PostMethod("http://"+server+targetServlet); // Provide custom retry handler is necessary method.getParams().setParameter(HttpMethodParams.RETRY_HANDLER, new DefaultHttpMethodRetryHandler(3, false)); //add parameters for(String paramName : parameters.keySet()){ method.addParameter(paramName, parameters.get(paramName)); } try { httpClient.executeMethod(method); int statusCode = method.getStatusCode(); log.debug("HTTP status code:" +statusCode); if(statusCode==HttpServletResponse.SC_OK){ responseBody = method.getResponseBodyAsString(); }else{ log.warn(server+targetServlet+" responded with statusCode="+statusCode); } } catch (HttpException ex) { log.error("Fatal protocol violation: " + ex.getMessage(),ex); } catch (IOException ex) { log.error("Fatal transport error: " + ex.getMessage(),ex); } finally { // Release the connection. method.releaseConnection(); } return responseBody; } /** * Initialize ConnectionManager for HttpClient */ private void initializeHttpClient(){ HttpConnectionManagerParams managerParams = new HttpConnectionManagerParams(); //maximum number of connections allowed. managerParams.setMaxTotalConnections(10); managerParams.setDefaultMaxConnectionsPerHost(10); //check to see if the server closed the connection managerParams.setStaleCheckingEnabled(true); //time (ms) to wait for data after a connection is established managerParams.setSoTimeout(3000); //time (ms) to wait for a connection managerParams.setConnectionTimeout(3000); MultiThreadedHttpConnectionManager connectionManager = new MultiThreadedHttpConnectionManager(); connectionManager.setParams(managerParams); httpClient.setHttpConnectionManager(connectionManager); } }
This is my main servlet (the one which returns XML or JSON using json-simple project) import java.io.IOException; import java.util.ArrayList; import java.util.List; import java.util.Set; import java.util.TreeSet; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang.BooleanUtils; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.json.simple.JSONValue; import com.vzw.common.gsbsocketapp.consumer.ActiveMQDLConsumer; import com.vzw.common.gsbsocketapp.consumer.MOMConsumer; import com.vzw.common.gsbsocketapp.util.AppContext; /** * UtilsServlet * * @author Juan Cruz * @since July 15 2010 */ public class UtilsServlet extends HttpServlet { private static final long serialVersionUID = -6124305504614666753L; private static final Logger log = Logger.getLogger(UtilsServlet.class); @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String operation = request.getParameter("operation"); String consumerName = StringUtils.trim(request.getParameter("consumerName")); String suffix = StringUtils.trim(request.getParameter("suffix")); Boolean isJsonEnabled = BooleanUtils.toBoolean(StringUtils.trim(request.getParameter("json"))); Boolean result = false; if(StringUtils.isNotEmpty(operation)){ response.setContentType("text/xml"); response.setHeader("Cache-Control", "no-cache"); if(StringUtils.equalsIgnoreCase(operation, "getBeans")){ StringBuffer sb = new StringBuffer(); List<String> listBeans = this.getBeans(suffix); for(String beanName : listBeans){ sb.append("<bean>"); sb.append(beanName); sb.append("</bean>"); } if(isJsonEnabled){ response.getWriter().write(JSONValue.toJSONString(listBeans)); }else{ response.getWriter().write("<beans>" + sb.toString() + "</beans>"); } } if(StringUtils.equalsIgnoreCase(operation, "startMOMConsumer")){ this.startMOMConsumer(consumerName); result = Boolean.TRUE; if(isJsonEnabled){ response.getWriter().write(JSONValue.toJSONString(result)); }else{ response.getWriter().write("<result>"+result+"</result>"); } } if(StringUtils.equalsIgnoreCase(operation, "stopMOMConsumer")){ this.stopMOMConsumer(consumerName); result = Boolean.TRUE; if(isJsonEnabled){ response.getWriter().write(JSONValue.toJSONString(result)); }else{ response.getWriter().write("<result>"+result+"</result>"); } } if(StringUtils.equalsIgnoreCase(operation, "isStartedMOMConsumer")){ result = this.isStartedMOMConsumer(consumerName); if(isJsonEnabled){ response.getWriter().write(JSONValue.toJSONString(result)); }else{ response.getWriter().write("<result>"+result+"</result>"); } } if(StringUtils.equalsIgnoreCase(operation, "startAllConsumers")){ this.startAllConsumers(suffix); result = Boolean.TRUE; if(isJsonEnabled){ response.getWriter().write(JSONValue.toJSONString(result)); }else{ response.getWriter().write("<result>"+result+"</result>"); } } if(StringUtils.equalsIgnoreCase(operation, "stopAllConsumers")){ this.stopAllConsumers(suffix); result = Boolean.TRUE; if(isJsonEnabled){ response.getWriter().write(JSONValue.toJSONString(result)); }else{ response.getWriter().write("<result>"+result+"</result>"); } } if(StringUtils.equalsIgnoreCase(operation, "purgeDLQConsumer")){ this.purgeConsumer(consumerName); result = Boolean.TRUE; if(isJsonEnabled){ response.getWriter().write(JSONValue.toJSONString(result)); }else{ response.getWriter().write("<result>"+result+"</result>"); } } }else{ //Nada que mostrar response.setStatus(HttpServletResponse.SC_NO_CONTENT); } } /** * Search beans on Spring ApplicationContext with given suffix * * @param suffix * @return List<String> */ private List<String> getBeans(final String suffix){ if(log.isDebugEnabled()){ log.debug("getBeans (with suffix)=" +suffix); } List<String> listBeans = new ArrayList<String>(); String[] beanDefinitionNames = AppContext.getAppContext().getBeanDefinitionNames(); Set<String> listBeansFiltered = new TreeSet<String>();//Sort them & avoid duplicates for(String beanName : beanDefinitionNames){//filter by suffix provided if(StringUtils.trim(beanName).endsWith(suffix)){ listBeansFiltered.add(beanName); } } for(String item : listBeansFiltered){ listBeans.add(item); } if(log.isDebugEnabled()){ log.debug("beans retrieved =" +listBeans.size()); } return listBeans; } /** * Start consumer given * * @param consumerBeanId */ @SuppressWarnings("unchecked") private void startMOMConsumer(final String consumerBeanId){ if(log.isDebugEnabled()){ log.debug("startMOMConsumer beanId=" +consumerBeanId); } List<MOMConsumer>momConsumers = (List<MOMConsumer>)AppContext.getAppContext().getBean(consumerBeanId); for (MOMConsumer momConsumer : momConsumers) { if(!momConsumer.isStarted()){//start if is not started momConsumer.execute(); } } if(log.isDebugEnabled()){ log.debug(consumerBeanId+" started"); } } /** * Call execute method on given consumer * * @param consumerBeanId */ private void purgeConsumer(String consumerBeanId) { if(log.isDebugEnabled()){ log.debug("purgeConsumer beanId=" +consumerBeanId); } ActiveMQDLConsumer activeMQDLConsumer = (ActiveMQDLConsumer)AppContext.getAppContext().getBean(consumerBeanId); activeMQDLConsumer.execute(); if(log.isDebugEnabled()){ log.debug(consumerBeanId+" purged"); } } /** * Stop consumer given * * @param consumerBeanId */ @SuppressWarnings("unchecked") private void stopMOMConsumer(final String consumerBeanId){ if(log.isDebugEnabled()){ log.debug("stopMOMConsumer beanId=" +consumerBeanId); } List<MOMConsumer> momConsumers = (List<MOMConsumer>)AppContext.getAppContext().getBean(consumerBeanId); for (MOMConsumer momConsumer : momConsumers) { momConsumer.stop(); } if(log.isDebugEnabled()){ log.debug(consumerBeanId+" stopped"); } } /** * Verify state of given consumer - bean * * @param consumerBeanId * @return boolean */ @SuppressWarnings("unchecked") private boolean isStartedMOMConsumer(final String consumerBeanId){ boolean started = false; List<MOMConsumer> momConsumers = (List<MOMConsumer>)AppContext.getAppContext().getBean(consumerBeanId); for (MOMConsumer momConsumer : momConsumers) { started = momConsumer.isStarted(); } if(log.isDebugEnabled()){ log.debug("isStarted beanId=" +consumerBeanId+"?"+started); } return started; } /** * Start all consumers with given suffix * * @param suffix */ @SuppressWarnings("unchecked") private void startAllConsumers(final String suffix){ if(log.isDebugEnabled()){ log.debug("startAllConsumer suffix=" +suffix); } List<String> listBeans = this.getBeans(suffix); for(String consumerBeanId: listBeans){ List<MOMConsumer> momConsumers = (List<MOMConsumer>)AppContext.getAppContext().getBean(consumerBeanId); for (MOMConsumer momConsumer : momConsumers) { momConsumer.execute(); } } if(log.isDebugEnabled()){ log.debug(listBeans.size()+" beans started"); } } /** * Stop all consumers with given suffix * * @param suffix */ @SuppressWarnings("unchecked") private void stopAllConsumers(final String suffix){ if(log.isDebugEnabled()){ log.debug("stopAllConsumers suffix=" +suffix); } List<String> listBeans = this.getBeans(suffix); for(String consumerBeanId: listBeans){ List<MOMConsumer> momConsumers = (List<MOMConsumer>)AppContext.getAppContext().getBean(consumerBeanId); for (MOMConsumer momConsumer : momConsumers) { momConsumer.stop(); } } if(log.isDebugEnabled()){ log.debug(listBeans.size()+" beans stopped"); } } }

And this now, these are portions of my jQuery code:
var jsArray = new Array(); function parseXmlResponse(xml) { $(xml).find("bean").each(function(index) { jsArray.push($(this).text()); }); } function executeAjaxCallWithXML(){ $.ajax({ type: 'POST', url: 'http://'+server+'/GSBSocketAppWeb/proxyServlet', data: "operation="+operation+"&suffix="+suffix+"&server="+server+"&targetServlet=/GSBSocketAppWeb/utilsServlet", dataType: "xml", success: parseXmlResponse, async: false, error: function(xmlHttpRequest, textStatus, errorThrown){ alert(textStatus+", code: "+xmlHttpRequest.status); jsArray = new Array(); } }); } function executePostCallWithJson(){ $.post('http://'+server+'/GSBSocketAppWeb/proxyServlet?operation='+operation+'&suffix='+suffix+'&json=true&server='+server+'&targetServlet=/GSBSocketAppWeb/utilsServlet',function(data, textStatus){ // this will give us an array of objects var arrayItems = JSON.parse(data); for(var x=0; x < arrayItems.length; x++) { jsArrayBrowsers.push(arrayItems[x]); } }, 'text'); } function executeAjaxCallwithJson(){ //We can use .ajax or .get / .post $.ajax({ type: 'POST', url: 'http://'+server+'/GSBSocketAppWeb/proxyServlet', data: "operation="+operation+"&consumerName="+consumerName+"&json=true&server="+server+"&targetServlet=/GSBSocketAppWeb/utilsServlet", dataType: "json", success: function(data, textStatus){ if(data){ alert(consumerName+' was purged successfully'); button.value = 'Purged'; } }, error: function(xmlHttpRequest, textStatus){ alert(textStatus+", code: "+xmlHttpRequest.status); jsArrayBrowsers = new Array(); } }); }

You can get the additional JavaScript libraries from: JSON2 and my version of jQuery Tools