Using sqls in JSP - What is the best practice?

Question

Say, You have an application which lists down users in your application. Ideally, if you were writing code to achieve this in Java, irrespective of what your UI layer is, I would think that you would write code which retrieves result set from the database and maps it to your application object. So, in this scenario, you are looking at your ORM / Data layer doing its thing and creating a list of "User" objects.

Let's assume that your User object looks as follows:

public class User {

      private String userName;
      private int    userid;
}

You can now use this list of "User" objects, in any UI. (Swing / Webapp). Now, imagine a scenario, where you have to list down the userName and a count of say, departments or whatever and this is a very specific screen in a webapp. So you are looking a object structure like this:

public class UserViewBean {

            private String userName;
            private int    countDepartments;
}

The easiest way of doing this is writing SQL for retrieving department count along with user name in one query. If I you to write such a query, where would you have this query? In your jsp? But, if you were doing this in a MVC framework, would you move this query to your data layer, get the result set, convert it to UserViewBean and send it to your jsp in request scope? If you write queries directly into jsps/if you are making use of connections directly in JSP, isn't that a bad practice?

I know, some of you might say, 'hey, you got your object composition wrong! if department is linked to user, you would want to create a list of departments in your User object' - Yes, I agree. But, think of this scenario - Say, I don't need this department count information anywhere else in my application other than this one screen. Are you saying that whereever I load my User object from the database, I would have to load a list of dependency objects, even if I won't be using them? How long will your object graph get with all the relational integrity? Yes, I do know that you have ORMs for this very reason, so that you get benefits of lazy loading and stuff, but I dont have the privilage to use one.

The bottom line question here is:

• Would you write sqls in to your JSP if it serves just one screen? OR

• Would you compose an anemic object that caters to your view and make your business layer return this object for this screen - just to make it look a bit OOish? OR

• irrespective of what your screen demands, would you compose your objects such that an object graph is loaded and you would get the size of that list?

What is the best practice here?

Answer 1

I would never put SQL in a JSP. I would use Spring MVC or Struts controllers, or servlets to contain all of that type of logic. It allows for better error handling among other things (you can forward to error pages when queries fail).

If you really must do this, use the JSTL SQL tags.

Answer 2

Would you write sqls in to your JSP if it serves just one screen?

In a prototype, just as a quick hack - maybe. In any other situation, not to mention a production environment - NEVER.

Use a proper MVC framework to separate business logic from presentation.

Answer 3

I am not even sure that JSP should be used, but for trivial applications. If you really have to use them, use MVC pattern or encapsulate your logic in a JavaBean.

Answer 4

Have a look at JPA which allow you to do object manipulations which then is persisted in the database

Answer 5

Personally, I take a simple pragmatic approach. If I was writing screen that just displays a list of users with their deparment count, so that the entire code is maybe a page, and I don't expect this code to be used on any other screen, I'd probably just throw it all in the JSP. Yes, I know there are all the MVC purists who will say, "business logic should never go in a JSP". But aside from a dogmatic rule, why not? What would it hurt in a case like this?

If I found that I had two screens, maybe one where I had to simply display the list and another where I had to do some additional processing on the list, then I would certainly pull the common code out into a class that was called from both places.

I believe that the criteria should be: What produces the most maintainable code? What is shortest and easiest to understand? What produces the least linkages between modules? etc.

I adamantly refuse to accept the principle: "In some cases this approach leads to problems, therefore never use it." If sometimes it leads to problems, then don't use it in the cases where it leads to problems. Or worse, "Somebody wrote it in a book, therefore it cannot be questioned." Sure, there are some rules that are valid 99.99% of the time, so it gets to be pointless to check if this particular case is an exception. But there are lots of rules that are good 51% of the time and people leap from "mostly" to "always".

Answer 6

I wouldn't put SQL in a jsp for fear of forgetting it in future maintenance. Think of the poor guy maintaining your code-- poor guy = you in 10 months or whenever the database is restructured-- and at least put all SQL in the same general region.