Ned to change servlet code to pure java

[Jas Oberai]

Hi Friends,
I am trying to use the Tomcat Data Sources,connection pooling.The example I saw used servlets,can you please tell me how to change the servlet to equivalent java code,as i dont know servlets.I have marked(----) the statements,which need to be changed.I have marked the last one(return results),because I dont know where to fit that statement.

  
package bean;
import java.io.*;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
import java.util.*;
 
public class ResSet
{
  private DataSource dataSource;
  ResultSet results;
 
  public void ResSet()throws Exception {
    try {
  --------    Context init = new InitialContext();
   ---------   Context ctx = (Context) init.lookup("java:comp/env");
   --------   dataSource = (DataSource) ctx.lookup("jdbc/conversion");
    }
    catch (NamingException ex) {
        ex.printStackTrace();
    }
  }
 
  public ResultSet getResults(String Query) throws Exception {
 
    Connection con = null;
    try {
      synchronized (dataSource) {
        con = dataSource.getConnection();
      }
      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
      results = stmt.executeQuery(Query);
  --------    return results;
      }
    catch (Exception ex)
    {
        if (ex instanceof SQLException) {
        SQLException sqlex = (SQLException) ex;
          sqlex.printStackTrace();
      }
    }
    finally {
      try {       con.close();
   } catch (Exception ex) {}
    }
  }
}
 

[Jeanne Boyarsky]

Jas,
Connection pooling uses a datasource. This works if your class runs on the server (servlet, ejb, ...) It also works if a servlet or ejb is calling your class. If this is not the case, you need to substitute the part of the code that gets the connection with the DriverManager code (from your other post.)

As for returning the resultset, I'm not sure what the confusion is. Are you trying to process it?

[Jas Oberai]

Thanks jeanne again,
I have a jsp,which fires in the query to this class,which returns the resultset to another java class,which cooks the data and sends it back to the jsp.I have worked on the code,I just want to know when and where should I call the close() method.Here's my new code:

 
 import java.util.*;
 
public class ResSet
{
  private Connection con;
  private Statement st;
  ResultSet results;
  private DataSource ds;
 
  public ResSet() 
  {
    try{
      Context init = new InitialContext();
      Context ctx = (Context) init.lookup("java:comp/env");
      ds = (DataSource) ctx.lookup("jdbc/conversion");
      
    }catch(Exception e){e.printStackTrace();}
 }
 
 public ResultSet getResults(String Query)throws Exception{
   con = ds.getConnection();
   st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
   results = st.executeQuery(Query);
   return results;
 }
 
 public void close()
  {
    try { st.close(); } catch (Exception ex) {}  
    try { con.close(); } catch (Exception ex) {}
  }
}
 

[Jeanne Boyarsky]

Jas,
The close method looks good.

"which returns the resultset to another java class,which cooks the data" - This is the class that needs to call your newly written close method.

[Jas Oberai]

Thanks Jeanne,
After making changes to Tomcat and configuring the connection pool,I am able to reduce the database access time vastly.But there is still a delay,how can i check the no.of connections that are open,because i am not calling the close routine anywhere.Please help me again.

[Jeanne Boyarsky]

Jas,
For that you need a profiling tool.