JDBC pooling, MVC, and OO newbie...

[J Sellers]

I hope this question doesn't come across dumb and that it makes sense. I am fairly new to Java and OOP in general.

As I mentioned in a previous post, I am teaching myself servlets and everything that goes with them by "porting" a PHP application to Java. I've got my initial testing servlet working with JDBC connection pooling and I've done it a few different ways, my question is which one is correct when it comes to following the MVC design pattern? Is it ok to have the database connection setup in my controller (servlet) and then passed to my methods (models) or should I have anything database related in the model class?

Here is some code on what I'm working with. I have an application specific web.xml and context.xml files that setup my database listener/connection info. This listener is called via the containers contextInitialized() method where it looks up the datasource and then makes it usable for the rest of the app:

    public void contextInitialized(ServletContextEvent event) {
        
        try {
			// Obtain our environment naming context
			Context envCtx = (Context) new InitialContext().lookup("java:comp/env");

			// Look up our data source
			DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");

			event.getServletContext().setAttribute("DBCPool", ds);
		} catch(NamingException e){ e.printStackTrace();
		}
    }

Next, the actual servlet in this setup creates a reference to the datasource in it's init() method, creates an actual connection in it's doGet() method, and then passes that connection to my methods/models so they can connect to the database. Here is the datasource/connection setup in the servlet:

    
public void init() throws ServletException {
		try {
			//Create a datasource for pooled connections.
			datasource = (DataSource) getServletContext().getAttribute("DBCPool");
		} catch (Exception e) {
			throw new ServletException(e.getMessage());
		}
	}
......
......
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		String variableOne = null;
		variableOne = request.getParameter("blah");
		
		try {
			conn = datasource.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		ModelClass testObject = new ModelClass(variableOne, conn);

		request.setAttribute("myObject", testObject);
		
		RequestDispatcher view = request.getRequestDispatcher("/overview.jsp");
		view.forward(request, response);
		
	}

And the code from ModelClass:

    
public class ModelClass {
Connection conn = null;
.....
.....
	public ModelClass(String varOne, Connection myConn){
		setSomeData(varOne);
                setSomeOtherData();
		conn = myConn;
	}

	public void setSomeOtherData(){

        Statement stmt = null;
        ResultSet rs = null;
        //int rowCount = 0;
        
        try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("big ugly sql query");
			
			
            ................
            ...............
            rs.close();
            stmt.close();
            conn.close();
            
        } catch (SQLException e){
        	e.getMessage();
        }
		
	}

This works and everything, but from what I've read regarding MVC, it sounds like I might want to move the database stuff out of the servlet and into my model classes, is that right? The other night I created a DBUtils class that handled everything and then instantiated it in the servlet init() method. I moved all the connection creation stuff outside of the servlet code and into my model code.
So I guess I have a few questions:
- Is the code up above "ok" from a design sense or does the database stuff need to be moved around?
- Is it "ok" to use contextInitialized() to setup my datasource or should I do everything from a database utility class which is instantiated via init()?
- In regards to the previous question, should I even have any database related logic in the servlet init() method or just save it all for the utility class?

I apologize for the long post, but I wanted to use some of the actual code to illustrate what I was doing.
TIA

[Rosco Duncan]

Hello there,

That is a very good question with respect to MVC.

As I understand it, generally speaking, the conroller will usually handle the request, resolve your model object and pass it to the view for display. In the scenario you have, the controller handles the request, creates a shell model object (ie it has no data of its own) and is allowing the model object to dynamically access additional data via the database connection. While there is nothing wrong with doing this, you should consider whether or not the data you are looking up is really related to that model object, and if so, whether it could have been loaded up by the controller as part of its resolution of the model object.

Possibly this would be more obvious if your model object was a little more realistic. Consider a Person class which has fields firstName and lastName. If you also had a PersonController class, this would handle the request by looking up the person data (first name and last name) in the database, create a Person object (instance of the Person class) with the firstName and lastName data, and pass this on to the appropriate view. Once you have this, do you really need your particular person to access any more from the database?

IMOP I think you should leave the database access code in the Controllers if you can. There are some architectures where the model object may be able to dynamically lookup (lazy load) some of its properties, perhaps for efficiency reasons, but this is an exception to the rule. In such cases it would then be good practice to pass the model object some sort of interface to a repository rather than the jdbc connection itself. This is because its ususally good practice to shift out any specific persistence logic away from the core classes. Technologies like Hibernate come in handy here.

Does this make sense?

[Rosco Duncan]

Hello there,

That is a very good question with respect to MVC.

As I understand it, generally speaking, the conroller will usually handle the request, resolve your model object and pass it to the view for display. In the scenario you have, the controller handles the request, creates a shell model object (ie it has no data of its own) and is allowing the model object to dynamically access additional data via the database connection. While there is nothing wrong with doing this, you should consider whether or not the data you are looking up is really related to that model object, and if so, whether it could have been loaded up by the controller as part of its resolution of the model object.

Possibly this would be more obvious if your model object was a little more realistic. Consider a Person class which has fields firstName and lastName. If you also had a PersonController class, this would handle the request by looking up the person data (first name and last name) in the database, create a Person object (instance of the Person class) with the firstName and lastName data, and pass this on to the appropriate view. Once you have this, do you really need your particular person to access any more from the database?

IMOP I think you should leave the database access code in the Controllers if you can. There are some architectures where the model object may be able to dynamically lookup (lazy load) some of its properties, perhaps for efficiency reasons, but this is an exception to the rule. In such cases it would then be good practice to pass the model object some sort of interface to a repository rather than the jdbc connection itself. This is because its ususally good practice to shift out any specific persistence logic away from the core classes. Technologies like Hibernate come in handy here.

Does this make sense?

[J Sellers]

Rosco, I think I am following you. Using your advice in my scenario, would I actually have the sql query string (the statement and the result set) in the controller (servlet) and just have a bunch of basic getters and setters in the model class that I passed the results to?
This is where I start getting a bit confused. To me, it seems like logically you would place any code that deals with that object in its class file, whether it's a basic set/get method or a more in depth sql query. When you wanted to perform that action you would just call that objects method.

Thanks for your response, I think it cleared up a few things.

[Rosco Duncan]

Yep, sounds like you are on the right track.

Your model class will probably start with just getters and setters for the various proerties that your object is modelling. In the case of Person that I used earlier, it would just be getters and setters for firstName and lastName.

For now you just want to put the database access code into the servlet as this is your Controller. When you are just thinking about MVC like this, then the sevlet is the right place because it is doing all of the Controller role. You should note tho that most modern java MVC webapps flesh out the Controller side of things a lot more than just a simple servlet. While there is always one or more servlets involved, data access like what you are doing here with JDBC is usually delegated to a separate layer (or layers) of code. The classes in this layer are often know as Data Access Objects (DAOs), and are simply responsible for persisting and loading the model objects from the database.

These days you are also well advised to familiarise yourself, and probably ultimately use a web framework like Spring MVC or Struts. These take the Model View Controller pattern and give you a lot of support for the sorts of things you will find yourself doing time and again.

Good luck!

[J Sellers]

Rosco, thanks for the help and advice, I appreciate it.