Java EE (Java Enterprise Edition) General Discussion

I am using Eclipse 3.1 along with Tomcat 5.0, MySQL 4.1, J2EE1.4. I could set up the JNDI Dataresource connection pooling and tested with small test servlet. Now thinking of having common methods for getting connection / closing / commiting ....etc.

I wrote following. [Please let me know whether it is correct way of doing it - as i am not very sure]

package common;


import java.sql.*;
import javax.sql.*;
import javax.naming.*;

import org.apache.log4j.Logger;

public final class connectionManager {

	private static Logger logger = Logger.getLogger(common.connectionManager.class);

	public connectionManager() {}

	public static Connection getConn () throws NamingException, SQLException
	{
//JNDI DataSource connection pooling


		Connection conn = null;
		try{

			Context initContext = new InitialContext();
			Context envContext  = (Context)initContext.lookup("java:/comp/env");
			DataSource ds = (DataSource)envContext.lookup("jdbc/TQ3DB");
			conn = ds.getConnection();
		}catch (NamingException ne) {
		    new GlobalExceptionHandler(logger, ne);
			conn = null;
			throw new NamingException();
		}catch (SQLException e){
			new GlobalExceptionHandler(logger, e);
			conn = null;
			throw new SQLException();
		}
		return conn;

  	}//getConnection


	public static void commit(Connection conn) throws SQLException
	{
		conn.commit();
	}

	public static void rollback(Connection conn) throws SQLException
	{
		conn.rollback();
	}

  	public static void setAutoCommit(Connection conn, boolean autoCommit)
                                    throws SQLException
  	{
  		conn.setAutoCommit(autoCommit );
  	}

	public static void closeConnection(Connection conn) throws SQLException{
		if (conn != null) {
			conn.close();
			conn = null;
		}
	}//closeConnection


	public static void closeResources(ResultSet oRS, PreparedStatement pstmt) throws SQLException
	{
		if (oRS != null) {
			oRS.close();
			oRS = null;
		}
		if (pstmt != null) {
				pstmt.close();
				pstmt = null;
		}
	} // closeResources

}//ConnectionManager

I am having a login form which submits user name and password. I am checking this against the database. Following is the servlet to do that.

package login;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import common.*;

public class loginServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException {		
		doPost(request, response);
	}//doGet
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException,IOException{

		String userId = request.getParameter("userId");
		String password = request.getParameter("password");

		/************************************************************
		** call a method to validate the password which will return the 
		** User Name for authorized users and null string for un-authorised. 
		**********************************************************/
		String uName = validateUser(userId, password);
		//if uName is null .. user is not authorized.
		if (uName == null){
			//redirect to jsp page with error message
		    RequestDispatcher rd = 
		    	getServletContext().getRequestDispatcher("/jsps/mainmenu.jsp");
		    if (rd != null){
		    	rd.forward(request,response);
		     }
		}
		else{
			// the user is valid - create a seesion for this user.
			HttpSession userSession = request.getSession(true);
			// put the user name session variable.
			userSession.setAttribute("userName", uName);
			//redirect to Main menu page
			RequestDispatcher rd = 
				getServletContext().getRequestDispatcher("/jsps/mainmenu.jsp");
			if (rd != null){
				rd.forward(request,response);
			}
		}
	}// end of doPost
	
	private String validateUser(String userId, String password)
			throws SQLException{

		String returnVal = null;
		connectionManager cm = new connectionManager();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet oRS = null;

		try{
			//get the connection
			conn = cm.getConn ();
			//get records from user table for this user id and password
			String sQry = "SELECT  user_login FROM user "
					+ "where user_login = ? AND user_pwd = ? ";
			
			pstmt = conn.prepareStatement(sQry);
			pstmt.setString(1, userId);
			pstmt.setString(2, password);
			oRS = pstmt.executeQuery();

			//check for record
			if (oRS.next()) {
				returnVal = oRS.getString("user_login");
			}else {returnVal = null;}
			
   		}catch (Exception e){  		
   			returnVal = null;
		}finally{
			cm.closeResources(oRS, pstmt);
			cm.closeConnection(conn);
		}
		
		return returnVal;
	}


}// end of servlet class

But i am unable to compile it and i am also getting lots of warnings.

I am getting error at line
1)String uName = validateUser(userId, password);
Unhandled exception type SQLException loginServlet.java TQ3/WEB-INF/src/login line

2)For loginServlet Declaration (This warning is valid for all servlets) :

3)The serializable class DBTest does not declare a static final serialVersionUID field of type long loginServlet.java

4)The static method getConn() from the type connectionManager should be accessed in a static way

5)The static method closeResources(ResultSet, PreparedStatement) from the type connectionManager should be accessed in a static way

6)The static method closeConnection(Connection) from the type connectionManager should be accessed in a static way


Definitely I am doing it wrong but exactly where? I am having very strong doubt the way i am using connections is not the correct way. Pls help me.