title: Using sqls in JSP - What is the best practice?
body: <p>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. </p>
  
  <p>Let's assume that your User object looks as follows: </p>
  
  <pre><code>public class User {
  
        private String userName;
        private int    userid;
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>public class UserViewBean {
  
              private String userName;
              private int    countDepartments;
  }
  </code></pre>
  
  <p>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? </p>
  
  <p>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. </p>
  
  <p>The bottom line question here is: </p>
  
  <ul>
  <li><p><strong><em>Would you write sqls in to your JSP if it serves just one screen?</em></strong>  OR</p></li>
  <li><p><strong><em>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?</em></strong>  OR </p></li>
  <li><strong><em>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?</em></strong></li>
  </ul>
  
  <p>What is the best practice here? </p>