JNDI Looup in Servlet- Design Question

[foramit]

I will appreciate if someone answers/clarifies my following questions.
I am using Tomcat Connection pooling.

1. If I understand correctly- with Connection Pooling - Server will take care of opening the connections with DB
So if the Max users allowed is 10, it means that at any given time 10 Concurrent users will be allowed access to DB.

2. Where do I place the code for doing the context lookup?
I have many servlets called by JSP pages and these servlets will be accessing Database.
2 (a) Shall I place the context lookup code in each of the servlets Init Method, but by doing so I am writing the same code again and again.
If I understand correctly the Context lookup code is following 3 lines or Can I squeeze more code in Init()
Context i = new InitialContext();
Context e = (Context) i.lookup("java:comp/env");
DataSource d = (DataSource) e.lookup("jdbc/mytestdb");

Again if I have 30 Servlets, is this still a good idea?

2(b) If there is an alternative to 2 (a), like having another class or something, please elaborate on that
and which part of code should be in this alternative.

My application will be using JSP pages to call servlets that access Database to update or display results.

Thank you very much.

 

[sedj]

The golden rule for me is that every time you want a connection, you should perform this code :

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("resource");
Connection c = null;
if (ds != null) {
  c = ds.getConnection();
}

It will probably be a good idea to put this code in a static helper class, eg :

public class DbHelper {
  public static Connection getConnection(String jndiName) {
    Context ctx = new InitialContext();
    DataSource ds = (DataSource)ctx.lookup("resource");
    Connection c = null;
    if (ds != null) {
      c = ds.getConnection();
    }
    
    return c;
  }
}

Then whenever you need a connection from your JSP or servlet, you just do this :

Connection c = DbHelper.getConnection("java:comp/env/jdbc/poolName");

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[foramit]

Thanks for reply.<p>
So now I have the following

DbHelper.java

public class DbHelper {
 
     
	public static Connection getConnection(String jndiName){
		System.out.println(jndiName);
		
		
		try {	
	    Context ctx = new InitialContext();
		DataSource datasource = (DataSource)ctx.lookup(jndiName);
		Connection con = null;
			if (datasource != null) {
			   con = datasource.getConnection(); 
		      } 
			return con;
	}  catch (NamingException ex){
	       throw new RuntimeException("Init: Cannot get connection" + ex);
       }  catch ( SQLException e){
           throw new RuntimeException("Cannot get Connections from Datasource");
       }
       
	}
	 
}

and My Serlvet code is

ublic class AuthServlet1 extends HttpServlet { 
	private final static String DATASOURCENAME  = "java:comp/env/jdbc/schooljsp";
	
	public void doGet(HttpServletRequest req, HttpServletResponse res)
		throws ServletException, IOException {
		
		res.setContentType("text/html");
		PrintWriter out = res.getWriter();
	 try {
	     Connection conn = DbHelper.getConnection(DATASOURCENAME);
         
	     Statement stmt = conn.createStatement();

	     ResultSet rs = stmt.executeQuery("select * from SIGNUP");

	      out.println("SIGNUP<br>");
	     while (rs.next()) {
	          out.print(rs.getString(1));
	          out.print(" ");
	          out.print(rs.getInt(2));
	          out.println("<br>");
	       }
	     rs.close();
	     stmt.close();
	   }catch (Exception e) { System.out.println("Error e : " + e); }
	   
	}

}

I am assuming that this is very robust, or can I make it more robust?
The servlet code is just an eample in real app there are many servlet and doing a variety of things.
Thanks,

 

[sedj]

Why do you assume your code is robust ? When you assume, you make mistakes

I would consider this code more robust than yours ...

[codepublic class DbHelper {


public static Connection getConnection(String jndiName) throws SQLException {
System.out.println(jndiName);


try {
Context ctx = new InitialContext();
DataSource datasource = (DataSource)ctx.lookup(jndiName);
Connection con = null;
if (datasource != null) {
con = datasource.getConnection();
}

if (con == null) {
throw new SQLException("Connection returned by datasource was null for jndiName : " +jndiName);
}

return con;
} catch (NamingException ex){
throw new SQLException("Init: Cannot get connection : " + ex);
}

}

}
[/code]

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[sedj]

Why do you assume your code is robust ? When you assume, you make mistakes

I would consider this code more robust than yours ...

public class DbHelper {
 
     
    public static Connection getConnection(String jndiName) throws SQLException {
        System.out.println(jndiName);
        

    try {    
        Context ctx = new InitialContext();
        DataSource datasource = (DataSource)ctx.lookup(jndiName);
        Connection con = null;
        if (datasource != null) {
            con = datasource.getConnection(); 
        } 
        
        if (con == null) {
            throw new SQLException("Connection returned by datasource was null for jndiName : " +jndiName);
        }
        
        return con;
    }  catch (NamingException ex){
        throw new SQLException("Init: Cannot get connection : " + ex);
    }
       
    }
     
}

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk