To trace an existing J2EE application (or a legacy application,legacy here means the ones still not using CDI) at database layer is not easy, especially if that application does not have any reference to the user whom you want to trace. A cumbersome way would be to pass the user name or id from the view layer to each method you call on model layer and then pass it further down to class method from which you obtain the database connection. But, there is a far easy solution that i am going to discuss in this post. The solution that i am going to discuss can be used to enable database tracing for any legacy application and that too far easily.
The major issue with the existing applications is that they cannot access HttpSession from the model layer and hence cannot obtain the user id or user name of the user. To overcome this scenario we can use ThreadLocal class or any implementation of it (in this post i am going to use slf4j MDC class). A ThreadLocal variable is local to the currently executing thread and it cannot be altered by a concurrent thread,so we can use this variable to store the user information. But in case of web applications, during a user’s session, it is most likely that each of his/her request will be handled by a separate thread, So to ensure that the user’s information is kept stored in ThreadLocal variable, we can use a filter which can take the user id from the HttpSession variable and store it in the ThreadLocal variable. Also to avoid memory leaks we can remove the variable once a request is completed. Once this variable is stored it can be accessed from any class that is called by this thread, hence we easily achieve the goal of getting the information we need to enable the trace at database layer. The following code snippets show how it can be achieved.
The Filter Class :-
import org.slf4j.MDC;
public class UserIdInjectingFilter implements Filter{
public void doFilter(ServletRequest request, ServletResponse response,
FilterChain chain) throws IOException, ServletException {
HttpSession session=((HttpServletRequest)request).getSession(false);
if(session!=null){
if(session.getAttribute("userID")!=null){
//Here we populate the MDC
MDC.put("userID", (String)session.getAttribute("userID"));
}
}
chain.doFilter(request, response);
finally{
//Be sure to remove it, will cause memory leaks and permgen out of space errors. if not done so
MDC.remove("userID");
}
}
The central database connection management class methods:-
.....
private Connection connection=null;
Connection getDBConnection(){
CallableStatement cs=null;
try{
Context ctx=new InitialContext();
Context initContext = new InitialContext();
DataSource ds=(DataSource)initContext.lookup("jdbc/TestDS");
connection=ds.getConnection();
//get the value from thread local variable
String userId=MDC.get("userID");
cs=connection.prepareCall("begin set_cid(?,?,?); end;");
cs.setString(1, userId);
String invokingMethodName=Thread.currentThread().getStackTrace()[3].getMethodName();
String invokingClassName=Thread.currentThread().getStackTrace()[3].getClassName();
cs.setString(2,invokingClassName);
cs.setString(3,invokingMethodName);
cs.executeUpdate();
}catch(NamingException nameEx){
// handle exception here
}
// Be Specific :-)
catch(Exception sqlEx){
// catch your exception here
}
finally{
try {
cs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return connection;
}
/**
*Before closing the connection unset the bunch of identifiers
*/
public void closeConnection(){
//Bunch of close statements
if(connection != null && connection.isClosed()==false){
CallableStatement cs=connection.prepareCall("begin clear_cid(); end;");
cs.executeUpdate();
cs.close();
connection.close();
}
}catch(SQLException sqlEx){
// handle your exception here
}
}
.....
The PL/SQL procedures to set the identifiers:
create or replace procedure set_cid(p_cid varchar2,p_module_id varchar2,p_method_id varchar2)
is
begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO (p_cid);
DBMS_APPLICATION_INFO.SET_MODULE (p_module_id,p_method_id);
end set_cid;
create or replace procedure clear_cid
is
begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO (' ');
DBMS_APPLICATION_INFO.SET_MODULE ('','');
end clear_cid;
The query to see the details:-
select client_info,module,action from v$session
Hope this helps !
