Solved

JDBC connection and storing in session till it expires?

Posted on 2012-03-16
8
222 Views
Last Modified: 2012-08-13
I have implemented logging mechanism in Oracle by the way of a stored procedure. And in java, I am having a class which has different methods that is called from different places of my application. It has constructor which creates sql connection. The methods make calls to the stored procedure with appropriate parameters. But once the user comes to the site, I want the jdbc connection to be made to oracle and use the same connection throughout the user session. This is accomplished by putting the connection object into the session. Then by retrieving the connection object from session before any method call is made, I make the object's method call. And I want to close the connection finally when the user session ends.

To make it clear, plz see the sample code format,

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
 
 public class DbTest { 
   private static String url = "jdbc:oracle:thin:@localhost:7001:xe";
   private static String username = "";
   private static String password = ""; 
   private Connection conn = null; 
 
 public DbTest() {  
    Class.forName("oracle.jdbc.driver.OracleDriver");
     conn = DriverManager.getConnection(url, username, password);
     }   

public void log1(String param1, String param2...) { 
 try {
  CallableStatement stmt = conn.prepareCall("{call SP (?, ?)}");
   stmt.setString(1, param1);
  stmt.setString(2, param2);
  stmt.execute(); 
  } 
catch (SQLException e) { 
  e.printStackTrace(); 
  }  
}  

public void log2(String param1, String param2...) {
 ... 
... 
}  
} //End of DbTest class


   import DbTest;
  public class userEnteringClass {

  public void caller() {
  DbTest dbtest = (DbTest)session.getAttribute("DbTest");
 if (dbtest == null)
 { 
dbtest = new dbtest();
 session.setAttribute("DbTest", dbtest);
 }
  .... 
 dbtest.log1(param1, param2);
 ....
 ....
 dbtest.log2(param1, param2);
 ...
 } 
}

Open in new window


Hope you got what I am trying to do.
If this is not a good way, then plz suggest a better way. If possible with a sample code. Thanks for any help
0
Comment
Question by:weblogicme
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 37728162
perhpas this would help which shows how to call method befioere session expires:

http://stackoverflow.com/questions/3943368/invoke-method-just-before-session-expires
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37728191
I think thsi is on the similar subject, check the accepted solution:

http://stackoverflow.com/questions/7756054/how-to-call-a-method-before-the-session-object-is-destroyed
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37728194
also read comments below the solution (same that I posted above):

http://stackoverflow.com/questions/7756054/how-to-call-a-method-before-the-session-object-is-destroyed

the comments  seem to be useful
0
 
LVL 20

Expert Comment

by:Sathish David Kumar N
ID: 37728371
User Iterceptor concept ... in that you can check pre- request and post-request also
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 11

Accepted Solution

by:
petiex earned 500 total points
ID: 37730882
The short answer is, don't do that. Use connection pooling. Whenever a database transaction is required, get a connection from the connection pool, do the transaction in a try block that catches SQL or other exceptions, and close the connection in a finally block to return it to the pool. Whatever you are using as an application- or web- server, google that with "connection pool", and you will likely get a good lead on how to do this.

Under your current scenario, if you have more concurrent sessions than available db connections, you run out of connections. It seems that a single user can exhaust your database by logging in and closing his browser repeatedly, because there's really no way of knowing when somebody ends a session (e.g., by closing the browser) before the session expires. Multiple abandoned sessions = multiple open connections = overburdened db server and angry DBAs.
0
 

Author Comment

by:weblogicme
ID: 37733123
So I can implement connection pooling as mentioned here?
http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html

It mentiones "The wrapper approach works particularly well in an Enterprise Bean that uses Bean-managed persistence for two reasons: 1) Only one Driver class is loaded per Bean, and 2) specific connection details are handled outside the Bean. "

For my DBLog approach , is it ok to put it in wrapper class? Because I am not creating any entity bean...
0
 
LVL 11

Expert Comment

by:petiex
ID: 37739237
I've honestly never had to worry about the implementation of a Connection Pool, because the app servers I have used, Websphere and Glassfish, both have a built in connection pool facility which you configure to point to your data sources using their respective web-based admin consoles. You just need to provide it the login credentials, db name and server address, port number, any proprietary db access jar files, and a jndi name to look it up by. Then in your application, you access the data source using a JNDI lookup.

You might look into whether the app server or web server you are using has such a facility built-in. The odds are pretty good that it does.

If you only have one data source, it might actually be simpler to roll your own Connection Pool as described in that tutorial you linked to. As far as I can tell, there is no requirement to be using entity beans; they just point out that it's particularly well suited for them.
0
 

Author Closing Comment

by:weblogicme
ID: 37797779
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JAVA part two 5 57
Java Options in Zimbra Server 2 44
java stored proc example 9 19
servlet filter example 37 37
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now