Custom JDBC connection listener

[Vinodh Sa]

Hi all,

I have a query here, i m not sure whether this is technically possible or not.

Is it possible to write and implement a listener class which will be called for every JDBC connection created/closed from the appplication server to DB server.

Whenever my application requests for a connection object to Oracle DB Server then this listener should be called and same way while closing. (similar to httpsessionlistener class).

Is there any way by which i can achieve this ???

P.S. I m using Weblogic 9.2 application server and Oracle 10G with JSF 1.1 framework

[Jeanne Boyarsky]

I can think of two ways. One is to write a wrapper for the datasource/connection so you know when a connection is requested. The other is to use aspect oriented programming to do the same thing.

[Vinodh Sa]

Jeanne Boyarsky wrote:I can think of two ways. One is to write a wrapper for the datasource/connection so you know when a connection is requested. The other is to use aspect oriented programming to do the same thing.

Thanks Jeanne. Wrapper class logic worked.

[Ireneusz Kordal]

On oracle you can also track sessions using after logon and before logoff triggers.

[Vinodh Sa]

Ireneusz Kordal wrote:On oracle you can also track sessions using after logon and before logoff triggers.

Hi Ireneusz, Can you explain in a little brief about this ?

[Ireneusz Kordal]

Vinodh Sa wrote:

Ireneusz Kordal wrote:On oracle you can also track sessions using after logon and before logoff triggers.

Hi Ireneusz, Can you explain in a little brief about this ?

I show you a simple example.
These triggers are created at schema level (by user test):

drop table logons;

CREATE TABLE logons(
  name varchar2(100),
  date_time timestamp
);

CREATE OR REPLACE TRIGGER LogUserConnects
  AFTER LOGON ON SCHEMA
BEGIN
  INSERT INTO logons( name, date_time ) VALUES ( 'User logged', systimestamp );
END;
/

CREATE OR REPLACE TRIGGER LogUserDisconnects
  BEFORE LOGOFF ON SCHEMA
BEGIN
  INSERT INTO logons( name, date_time ) VALUES ( 'User logged out', systimestamp );
END;
/

Then we fire a little program in java that connects to and disconnects from oracle

        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:test/test@//kokodrilko:1521/xe");

        for (int i = 0; i < 10; i++) {
            Connection conn = ods.getConnection();
            conn.close();
        }

and here is the result:

SQL> select * from logons
  2  ;

NAME			  DATE_TIME
------------------------- ------------------------------
User logged		  19-DEC-11 01.47.18.106033 PM
User logged out 	  19-DEC-11 01.47.18.139496 PM
User logged		  19-DEC-11 01.47.18.227407 PM
User logged out 	  19-DEC-11 01.47.18.235796 PM
User logged		  19-DEC-11 01.47.18.313816 PM
User logged out 	  19-DEC-11 01.47.18.326856 PM
User logged		  19-DEC-11 01.47.18.417327 PM
User logged out 	  19-DEC-11 01.47.18.426528 PM
User logged		  19-DEC-11 01.47.18.504479 PM
User logged out 	  19-DEC-11 01.47.18.516806 PM
User logged		  19-DEC-11 01.47.18.592677 PM
User logged out 	  19-DEC-11 01.47.18.599671 PM
User logged		  19-DEC-11 01.47.18.675348 PM
User logged out 	  19-DEC-11 01.47.18.682162 PM
User logged		  19-DEC-11 01.47.18.754058 PM
User logged out 	  19-DEC-11 01.47.18.765186 PM
User logged		  19-DEC-11 01.47.18.845538 PM
User logged out 	  19-DEC-11 01.47.18.856290 PM
User logged		  19-DEC-11 01.47.18.941072 PM
User logged out 	  19-DEC-11 01.47.18.955374 PM
User logged		  19-DEC-11 01.50.51.827308 PM

21 rows selected.

The last 21st login is because I logged as test with sqlplus - triggers are fired always, after each login and logoff.


edit
These triggers must be carefully tested, especially afterlogon trigger, because when error occurs this prevents user from login in.
In this case DBA must drop this trigger.

[Vinodh Sa]

Ireneusz Kordal wrote:

Vinodh Sa wrote:

Ireneusz Kordal wrote:On oracle you can also track sessions using after logon and before logoff triggers.

Hi Ireneusz, Can you explain in a little brief about this ?

I show you a simple example.
These triggers are created at schema level (by user test):

drop table logons;

CREATE TABLE logons(
  name varchar2(100),
  date_time timestamp
);

CREATE OR REPLACE TRIGGER LogUserConnects
  AFTER LOGON ON SCHEMA
BEGIN
  INSERT INTO logons( name, date_time ) VALUES ( 'User logged', systimestamp );
END;
/

CREATE OR REPLACE TRIGGER LogUserDisconnects
  BEFORE LOGOFF ON SCHEMA
BEGIN
  INSERT INTO logons( name, date_time ) VALUES ( 'User logged out', systimestamp );
END;
/

Then we fire a little program in java that connects to and disconnects from oracle

        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:test/test@//kokodrilko:1521/xe");

        for (int i = 0; i < 10; i++) {
            Connection conn = ods.getConnection();
            conn.close();
        }

and here is the result:

SQL> select * from logons
  2  ;

NAME			  DATE_TIME
------------------------- ------------------------------
User logged		  19-DEC-11 01.47.18.106033 PM
User logged out 	  19-DEC-11 01.47.18.139496 PM
User logged		  19-DEC-11 01.47.18.227407 PM
User logged out 	  19-DEC-11 01.47.18.235796 PM
User logged		  19-DEC-11 01.47.18.313816 PM
User logged out 	  19-DEC-11 01.47.18.326856 PM
User logged		  19-DEC-11 01.47.18.417327 PM
User logged out 	  19-DEC-11 01.47.18.426528 PM
User logged		  19-DEC-11 01.47.18.504479 PM
User logged out 	  19-DEC-11 01.47.18.516806 PM
User logged		  19-DEC-11 01.47.18.592677 PM
User logged out 	  19-DEC-11 01.47.18.599671 PM
User logged		  19-DEC-11 01.47.18.675348 PM
User logged out 	  19-DEC-11 01.47.18.682162 PM
User logged		  19-DEC-11 01.47.18.754058 PM
User logged out 	  19-DEC-11 01.47.18.765186 PM
User logged		  19-DEC-11 01.47.18.845538 PM
User logged out 	  19-DEC-11 01.47.18.856290 PM
User logged		  19-DEC-11 01.47.18.941072 PM
User logged out 	  19-DEC-11 01.47.18.955374 PM
User logged		  19-DEC-11 01.50.51.827308 PM

21 rows selected.

The last 21st login is because I logged as test with sqlplus - triggers are fired always, after each login and logoff.


edit
These triggers must be carefully tested, especially afterlogon trigger, because when error occurs this prevents user from login in.
In this case DBA must drop this trigger.

Hi Ireneusz, Thanks for your response. Will check the code and revert.