JSP RSS JSP RSS

JSP database connectivity according to Model View Controller (MVC) Model 2

Closed Thread

•
•
Join Date: Dec 2004
Posts: 4,114
Reputation: peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of 
Solved Threads: 470
Moderator
Featured Poster
peter_budo's Avatar
peter_budo peter_budo is offline Offline
Code tags enforcer

JSP database connectivity according to Model View Controller (MVC) Model 2

 
8
  #1
Aug 22nd, 2008
After spending some time on JSP section of this forum as many before me, I come to the conclusion that we are in need of "proper" tutorial on this topic. This tutorial is not intended to teach you everything from top to bottom, it is just a starting point to show you what we mean by "DO NOT USE JSP FOR DIRECT DATABASE CONNECTIVITY!". In simple terms and examples I will try to explain the basics of MVC model 2. Therefore content has been kept as simple as possible to make it easier for beginners to grasp the basics without getting into a lot of application design jargon. That being said, there are many ways for this application being designed in a better manner and it's left as an exercise to the reader.

Short description
The tutorial will show simple example of a school login system, where after submitting correct user ID and password user is redirected to the next page in accordance with user group assignment
  • student - will see his personal details
  • teacher - will see his name on the top of the page with table of all students (user ID, first name, last name, email address and phone number)
  • administrator - will see his name on the top of the page with tables of all teachers and all students (user ID, first name, last name, email address and phone number)

Requirements
  • Knowledge of HTML
  • Basic knowledge of Java - knowledge of Exception handling is a MUST
  • Basics of SQL - previous experience with MySQL will be an advantage
  • Installation of Java on your pc (Download)
  • Installation of Tomcat server on your pc or access to some Java web hosting (Tomcat download, recommended configuration in tutorial)
  • Installation of MySQL Community Server on your machine or access to one through web hosting (MySQL Community Server Download, installation & configuration of the server {please ignore the rest of that guide}). I use this DB as I'm familiar with it and in my opinion is very easy to use. However this example can be applied to any database( doesn't matter if it is Oracle, MS SQL, Access or for these looking for something new without need of DB server SQLite or Derby) with just small adjustment to connection string.
  • Please download:
    • MySQL Connector/J which is connection driver for Java development
    • JSTL library, please download binaries in 1.1.2 package (use of the library will be discussed later on)
  • Your favourite IDE
To develop this example I used jdk1.6.0_05, Tomcat 6.0.16 and MySQL 5.0.45

Model View Controller Model 2
For those of you who never come across MVC Model 2, here is an example
model2architecture.jpg
I think the image is self-explanatory, however if you not sure you can find more info at
www.java-samples.com.

Instructions
Lets start our little project with a database set up.
Note: All files will be available for download at the end of the tutorial.
SQL Syntax (Toggle Plain Text) 
  1. CREATE TABLE  `danijsptutorial`.`user` (
  2.   `uid` CHAR(8) NOT NULL,
  3.   `password` VARCHAR(20) NOT NULL,
  4.   `firstName` VARCHAR(30) NOT NULL,
  5.   `lastName` VARCHAR(60) NOT NULL,
  6.   `address1` VARCHAR(100) NOT NULL,
  7.   `address2` VARCHAR(100) DEFAULT NULL,
  8.   `city` VARCHAR(50) NOT NULL,
  9.   `postCode` VARCHAR(10) NOT NULL,
  10.   `email` VARCHAR(50) DEFAULT NULL,
  11.   `phone` INT(15) DEFAULT NULL,
  12.   `userGroup` VARCHAR(7) NOT NULL,
  13.   PRIMARY KEY  (`uid`)
  14. )
This is sort of the database solution that will satisfies your teacher for the simple school assignment. However under real deployment it will quickly fall down. The design makes it difficult to assign an user to more then one group. For example at university and PhD candidate is a student but often in same time he is a teacher. Same goes for teacher, I know at least 5 teachers that beside their teaching role keep a close contact with IT admin team and have administrator rights. Beside making changes to user groups there is an issue of extended search time need to go through all record. Therefore following design would be more appropriate.
User table, to keep personal info
sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE `user` (
  2.   `uid` CHAR(8) NOT NULL,
  3.   `password` VARCHAR(20) NOT NULL,
  4.   `firstName` VARCHAR(30) NOT NULL,
  5.   `lastName` VARCHAR(60) NOT NULL,
  6.   `address1` VARCHAR(100) NOT NULL,
  7.   `address2` VARCHAR(100) DEFAULT NULL,
  8.   `city` VARCHAR(50) NOT NULL,
  9.   `postCode` VARCHAR(10) NOT NULL,
  10.   `email` VARCHAR(50) DEFAULT NULL,
  11.   `phone` INT(15) DEFAULT NULL,
  12.   PRIMARY KEY  (`uid`)
  13. )
User group table, to manage number of groups with various privileges
sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE `usergroup` (
  2.   `groupid` VARCHAR(10) NOT NULL,
  3.   `groupName` VARCHAR(20) DEFAULT NOT NULL,
  4.   PRIMARY KEY  (`groupid`)
  5. )
Group mapping table, to associate user with one or more of the groups
sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE  `danijsptutorial`.`usergroup_mapping` (
  2.   `uid` CHAR(8) NOT NULL,
  3.   `groupid` VARCHAR(10) NOT NULL,
  4.   KEY `groupid` (`groupid`),
  5.   KEY `uid` (`uid`),
  6.   CONSTRAINT `uid` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`),
  7.   CONSTRAINT `groupid` FOREIGN KEY (`groupid`) REFERENCES `usergroup` (`groupid`)
  8. ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
I decided to go for fixed size of user ID (uid) that should be of 8 characters in length. User ID is also unique key (primary key) of the table and the value cannot be NULL when you creating a new record. Then there is a password that can be 20 characters max. As you will see in validation process, I also check that the password is at least 8 characters long to be considered valid for database check out process. The current process require manual data entry, this may satisfies small business but will make unnecessary extra work load on database admins of larger companies.

Project folder structure:
daniwebtutorial
  • jsp - folder
    • index.jsp
    • student.jsp
    • teacher.jsp
    • admin.jsp
  • css - folder
    • style1.css
    • style2.css
    • style3.css
  • img - folder
    • photo.jpg
  • WEB-INF - folder
    • web.xml
    • classes - folder
      • LoginServlet.java
      • model - folder
        • DataMannager.java
        • Validation.java
      • bean - folder
        • UserBean.java
    • lib - folder
      • mysql-connector-java-5.1.5-bin.jar
      • or any other library you need to use (JSLT, JSF etc.)


Login screen - index.jsp.
JSP Syntax (Toggle Plain Text) 
  1. <%@ page language="java" contentType="text/html"%>
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  3. <html xmlns="http://www.w3.org/1999/xhtml">
  4. <head>
  5. <title>Daniweb Tutorial - Login</title>
  6. <link rel="stylesheet" type="text/css" href="css/style1.css" />
  7. </head>
  8.  
  9. <body>
  10. <div class="error">
  11.     	<% String e = (String) session.getAttribute("error" );
  12. 			if(e != null)
  13. 			{
  14. 				out.print(e); }%>
  15. </div>
  16.  
  17. <form method="post" action="controller/login">
  18.  
  19.     <table align="center">
  20.         <tr style="border-bottom:none;">
  21.             <th class="leftLogo">&lt;DANI</th>
  22.             <th class="rightLogo">WEB&gt;</th>
  23.         </tr>
  24.         <tr>
  25.             <td class="tdOne">Username : </td>
  26.             <td class="tdTwo"><input type="text" id="userName" name="userName" size="15" maxlength="8" /></td>
  27.         </tr>
  28.         <tr>
  29.             <td class="tdOne">Password : </td>
  30.             <td class="tdTwo"><input type="password" id="password" name="password" size="15" maxlength="20" /></td>
  31.          </tr>
  32.  
  33.         	<td colspan="2" align="right"><input type="submit" value="Login" /></td>
  34.         </tr>
  35.     </table>
  36. </form>
  37.  
  38. </body>
  39. </html>
The page holds small form consisting of two input text fields for username and password. There is no on-page validation and data are validated only by servlet, once submit button is pressed. On the line 11 - 15 you can see simple session retrieval, where we check if servlet dispatched any error message. If the first login attempt String value retrieved from the session is null, no error message will show. However if you have been returned back to this page, error message will be displayed above the login form. Login process may fail because you submitted data that did not match criteria of validation process, or one/two of submitted identification entries did not match with the database records.
Now lets assume we have correct login data, we entered them in the login screen and hit the submit button. Forms' action element will send request server which will take decision based upon configurations made in web.xml file located in WEB-INF folder of our project. Here, server will find that the requested need to be passed on LoginServlet. Hiding real destination of the request is common technique know as servlet mapping (nice "how to" tutorial can be found here). In the web.xml file we declared that if we call controller/login the server will actually use LoginServlet.
xml Syntax (Toggle Plain Text) 
  1. <servlet>
  2.        <servlet-name>LoginServlet</servlet-name>
  3.        <servlet-class>LoginServlet</servlet-class>
  4. </servlet>
  5.  
  6. <servlet-mapping>
  7.        <servlet-name>LoginServlet</servlet-name>
  8.        <url-pattern>/controller/login</url-pattern>
  9. </servlet-mapping>
While we are on web.xml I will quickly mention that in the same place you can also initialize parameters to be used by your whole project and it is good place to store your database connection data in a similar manner to this
xml Syntax (Toggle Plain Text) 
  1. <servlet>
  2.        <init-param>
  3. 	    	<param-name>jdbcDriver</param-name>
  4. 	    	<param-value>com.mysql.jdbc.Driver</param-value>
  5. 	    </init-param>
  6. 	    <init-param>
  7. 		    <param-name>dbURL</param-name>
  8. 		    <param-value>jdbc:mysql://localhost/danijsptutorial</param-value>
  9. 	    </init-param>
  10. 	  	<init-param>
  11. 		    <param-name>dbUserName</param-name>
  12. 		    <param-value>YOUR_DATABASE_USERNAME</param-value>
  13. 	    </init-param>
  14. 	  	<init-param>
  15. 		    <param-name>dbPassword</param-name>
  16. 		    <param-value>YOUR_DATABASE_PASSWORD</param-value>
  17. 	    </init-param>
  18.     </servlet>

LoginServlet
Java Syntax (Toggle Plain Text) 
  1. public void init(ServletConfig config) throws ServletException
  2. {
  3. 	super.init(config);
  4. 	dataManager = new DataManager();
  5.         dataManager.setDbURL(config.getInitParameter("dbURL"));
  6.         dataManager.setDbUserName(config.getInitParameter("dbUserName"));
  7.         dataManager.setDbPassword(config.getInitParameter("dbPassword"));
  8. 	try
  9. 	{
  10. 		Class.forName(config.getInitParameter("jdbcDriver"));
  11. 	}
  12. 	catch (Exception ex)
  13. 	{
  14. 		System.out.println("Initialize connector string");
  15. 		ex.printStackTrace();
  16. 	}
  17. }
With the use of the init() method we can tells the servlet any information about itself and its environment( in our scenario it will be database URL address, name of the driver to be used, plus username and password to the database). Servlet initialisation will provide DataManager, which is Model component, with the database parameters needed by DriverManager. Once driver is loaded, it will register itself with DriverManager and when we call method getConnection() it will return a Connection object for the given database. This will only happend if the driver loaded correctly.
java Syntax (Toggle Plain Text) 
  1. public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
  2. {		
  3. 	/* 
  4. 	 * Get user data fro submited form
  5. 	 */
  6. 	String userName = request.getParameter("userName");
  7. 	String password = request.getParameter("password");
  8.  
  9. 	if(val.validate(userName, password))
  10. 	{
  11. 		userBean = new UserBean();
  12. 		userBean = dataManager.getUserData(userName, password);
  13.  
  14. 		try
  15. 		{
  16. 			if(!userBean.getUid().equals(null))
  17. 				dbOK = true;
  18. 		}
  19. 		catch(NullPointerException npe)
  20. 		{
  21. 			System.out.println("Error on DB return");
  22. 			npe.printStackTrace();
  23. 			strError = "Invalid username or password.";
  24. 			dbOK = false;				
  25. 		}
  26. 	}
  27. 	else
  28. 	{
  29. 		strError = "Invalid username or password.";
  30. 		dbOK = false;
  31. 	}
  32.         // more code will follow
  33. }
Method doPost() starts our "real business". With the use of interface HttpServletRequest we retrieve data submitted by user from the index page. In the if statement we will use boolean outcome of validation process, from val.validate() method of class Validation,as a key for "unlocking" database to our needs.
java Syntax (Toggle Plain Text) 
  1. package model;
  2.  
  3. public class Validation {
  4.  
  5.     public Validation() {}
  6.  
  7.     public boolean validate(String userName, String password)
  8. 	{
  9. 		if(userName.length() != 8 || (password.length() < 8 || password.length() > 20))
  10. 		{
  11. 			return false;
  12. 		}
  13. 		return true;
  14. 	}    
  15. }
The validation process is very simple. We only check if user ID is exactly 8 characters long and the password in interval of 8 to 20 characters. No extra checks for special characters or characters that have to be part of submitted data. If validation returns false else condition applied with error message set up and dealt with in code later on. However if validation returns true submitted login data are passed to DataMannager method getUserData(). Method should return object type UserBean. In the next step we check if our request returned any data by checking if UserBean object is equal to null or not. Boolean variable dbOK will keep our success or failure with validation process and can also change depending on outcome of our database querying. Lets follow our database request.

DataMannager
Java Syntax (Toggle Plain Text) 
  1. public class DataManager 
  2. {
  3. 	private String dbURL = "";
  4. 	private String dbUserName = "";
  5. 	private String dbPassword = "";
  6.  
  7. 	public void setDbURL(String dbURL)
  8. 	{
  9. 		this.dbURL = dbURL;
  10. 	}
  11.  
  12. 	public String getDbURL()
  13. 	{
  14. 		return dbURL;
  15. 	}
  16.  
  17. 	public void setDbUserName(String dbUserName) 
  18. 	{
  19. 		this.dbUserName = dbUserName;
  20.     }
  21.  
  22.     public String getDbUserName() 
  23.     {
  24.     	return dbUserName;
  25.     }
  26.  
  27.     public void setDbPassword(String dbPassword) 
  28.     {
  29.     	this.dbPassword = dbPassword;
  30.     }
  31.  
  32.     public String getDbPassword() 
  33.     {
  34.     	return dbPassword;
  35.     }
  36.  
  37.     /*
  38.      * Open database connection
  39.      */
  40.     public Connection getConnection() 
  41.     {
  42. 	    Connection conn = null;
  43. 	    try {
  44. 	      conn = DriverManager.getConnection(getDbURL(), getDbUserName(),
  45. 	          getDbPassword());
  46. 	      }
  47. 	    catch (SQLException e) {
  48. 	      System.out.println("Could not connect to DB");
  49. 	      e.printStackTrace();
  50. 	      }
  51. 	    return conn;
  52.     }
  53.  
  54.     /*
  55.      * Close open database connection
  56.      */
  57.     public void putConnection(Connection conn) 
  58.     {
  59. 	    if (conn != null) 
  60. 	    {
  61. 	    	try 
  62. 	    	{ 
  63. 	    		conn.close(); 
  64. 	    	}
  65. 	      catch (SQLException e) {
  66. 	      	System.out.println("Enable to close DB connection");
  67. 	      	e.printStackTrace(); }
  68. 	    }
  69.     }
  70. }
As we can see, the above part is very simple with use of setter and getter methods we store database connectivity data from servlet initialisation for further use and provide methods to open and also close database connection. The request that been sent to servlet for processing in its early stage already initialized variables need it for connection string. The methods in the need of connection will simply call on getConnection() method to acquire connection "link" with database, getUserData() method is one of them.
Java Syntax (Toggle Plain Text) 
  1. /*
  2. * Retrive data of single user
  3. */
  4. public UserBean getUserData(String userName, String password)
  5. {	
  6. 	UserBean userBean = new UserBean();
  7. 	Connection conn = getConnection();	
  8.     if (conn != null) 
  9.     {
  10.     	ResultSet rs = null;
  11.     	Statement stmt = null;
  12. 	try
  13. 	{
  14. 	String strQuery = 
  15. 	"SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup "
  16. 	+"FROM user u, usergroup ug WHERE uid='"+userName+"' AND password='"+password+"' AND groupName IN"
  17. 	+" (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))";
  18. 		stmt = conn.createStatement();
  19. 		rs = stmt.executeQuery( strQuery);	
  20.  
  21. 		while(rs.next())
  22. 		{
  23. 			userBean.setUid(rs.getString("uid"));
  24. 			userBean.setFirstName(rs.getString("firstName"));
  25. 			userBean.setLastName(rs.getString("lastName"));
  26. 			userBean.setAddress1(rs.getString("address1"));
  27. 			userBean.setAddress2(rs.getString("address2"));
  28.  
  29. 			userBean.setCity(rs.getString("city"));
  30. 			userBean.setPostCode(rs.getString("postCode"));
  31. 			userBean.setEmail(rs.getString("email"));
  32. 			userBean.setPhone(rs.getInt("phone"));
  33. 			userBean.setUserGroup(rs.getString("userGroup"));
  34. 		}				
  35. 	}//end of try
  36. 	catch(SQLException ex)
  37. 	{			
  38. 	System.out.println("SQLException:" + ex.getMessage());								
  39. 	}
  40. 	finally 
  41. 	{
  42.        	if (stmt != null) 
  43.        	{
  44.        		try { stmt.close(); }
  45.        		catch (SQLException e) { e.printStackTrace();}
  46.        	}
  47.        	putConnection(conn);	        
  48.        }//end of finally
  49.    }//end of if
  50.    return userBean;
  51. }
In LoginServlet we made call for getUserData() method. Any retrieved data will be stored in UserBean object. Once we successfully receive new connection to database, we initialize Statement object necessary for query execution and ResultSet interface that will get us the result of the query. The returned result has cursor pointing before the first row of retrieved data and we need to move this cursor one position forward to get first record. This is done with next() method. The most suitable solution for retrieving data, as long as there is something to get, is while statement. It will repeat the routine inside the brackets as long as there is a record to read. Routine performs reading from ResultSet with help of getString() and getInt() methods. Retrieved data are store in the UserBean object. Invocation of Statement object and retrieving of ResultSet can throw SQL exception, therefore we need to run them in safe environment, try-catch clause. After either success of failure in retrieving data from database, we always need to close the Statement with stmt.close() and the Connection with method call putConnection(). Forgetting to do this will result in another set of SQL exceptions. The last thing left is to return whatever is currently in our UserBean object.

Use of classic Statement as seen in above snippet is security liability as it does allow possibility of SQL injection exploits. To avoid the bad fate of easy victim to hackers you should use PreparedStatement. (This security vulnerability is explained in Tomcat - The Definitive Guide, chapter 6 - Tomcat Security, SQL injection pg 152 or pg 27 in linked PDF document.) Secondly SQL in a PreparedStatement is precompiled by the database for faster execution which in large database operation will improve performance. However by warned! This is not always so, malformed query will significantly slow execution and it does not matter if it is Statement or PreparedStatement. Bellow is our getUserData() method with use of PreparedStatement
java Syntax (Toggle Plain Text) 
  1. public UserBean getUserData(String userName, String password)
  2. {	
  3. 	UserBean userBean = new UserBean();
  4. 	Connection conn = getConnection();	
  5.     if (conn != null) 
  6.     {
  7.     	ResultSet rs = null;
  8.     	PreparedStatement preparedStatement = null;
  9. 	try
  10. 	{
  11. 		String strQuery = 
  12. 		"SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup "
  13. 		+"FROM user u, usergroup ug WHERE uid=? AND password=? AND groupName IN"
  14. 		+" (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))";
  15.  
  16. 		preparedStatement = conn.prepareStatement(strQuery);
  17. 		preparedStatement.setString(1,userName);
  18. 		preparedStatement.setString(2,password);
  19. 		rs = preparedStatement.executeQuery();
  20.  
  21. 		while(rs.next())
  22. 		{
  23. 			userBean.setUid(rs.getString("uid"));
  24. 			userBean.setFirstName(rs.getString("firstName"));
  25. 			userBean.setLastName(rs.getString("lastName"));
  26. 			userBean.setAddress1(rs.getString("address1"));
  27. 			userBean.setAddress2(rs.getString("address2"));
  28. 			userBean.setCity(rs.getString("city"));
  29. 			userBean.setPostCode(rs.getString("postCode"));
  30. 			userBean.setEmail(rs.getString("email"));
  31. 			userBean.setPhone(rs.getInt("phone"));
  32. 			userBean.setUserGroup(rs.getString("userGroup"));
  33. 		}				
  34. 	}//end of try
  35. 	catch(SQLException ex){ex.printStackTrace();}
  36. 	finally 
  37. 	{
  38.        	try 
  39.        	{ 
  40.        		rs.close();
  41.        		preparedStatement.close(); 
  42.        	}
  43.          	catch (SQLException e) {e.printStackTrace();}
  44.          	putConnection(conn);	        
  45.        }//end of finally
  46.    }//end of if
  47.    return userBean;
  48. }
The question marks in this section of SQL query 
FROM user u, usergroup ug WHERE uid=? AND password=? AND groupName IN
marks the positions where the variables supplied by user will be entered. As you seen in this case I use only setString() method
java Syntax (Toggle Plain Text) 
  1. preparedStatement.setString(1,userName);
  2. preparedStatement.setString(2,password);
However PreparedStatement class provide wide range of setter methods to pass on numerous type of variables to the query.
Our SQL query is combination of two different queries
sql Syntax (Toggle Plain Text) 
  1. SELECT uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup FROM user WHERE uid=? AND password=?
to obtain basic user data; and
sql Syntax (Toggle Plain Text) 
  1. SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid)
that returns name of the user group our user belongs to.

Back to LoginServlet after query execution.
Java Syntax (Toggle Plain Text) 
  1. public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
  2. {		
  3. //Previous discussed section of LoginServlet code here
  4.  
  5. HttpSession session = request.getSession(true);
  6.  
  7. if(dbOK)
  8. {
  9. 	//redirect to student/teacher/admin pages
  10. 	if(userBean.getUserGroup().equals("student"))
  11.  
  12. 	{
  13. 		session.setAttribute( "userBean", userBean);
  14. 		RequestDispatcher dispatcher = request.getRequestDispatcher("/student.jsp");
  15. 		dispatcher.forward( request, response);				
  16. 	}
  17. 	else if(userBean.getUserGroup().equals("teacher"))
  18. 	{
  19. 		session.setAttribute( "userBean", userBean);				
  20. 		students = new ArrayList<UserBean>(dataManager.getUsersList("student"));
  21. 		session.setAttribute("students", students);
  22. 		RequestDispatcher dispatcher = request.getRequestDispatcher("/teacher.jsp");
  23. 		dispatcher.forward( request, response);	
  24. 	}
  25. 	else if(userBean.getUserGroup().equals("admin"))
  26. 	{
  27. 		session.setAttribute( "userBean", userBean);
  28. 		students = new ArrayList<UserBean>(dataManager.getUsersList("student"));
  29. 		session.setAttribute("students", students);
  30. 		teachers = new ArrayList<UserBean>(dataManager.getUsersList("teacher"));
  31. 		session.setAttribute("teachers", teachers);
  32. 		RequestDispatcher dispatcher = request.getRequestDispatcher("/admin.jsp");
  33. 		dispatcher.forward( request, response);	
  34. 	}
  35. }
  36. else
  37. {
  38. 	//Error after DB login checkout, redirect back to index.jsp
  39. 	session.setAttribute( "error", strError);
  40.  
  41. 	RequestDispatcher dispatcher = request.getRequestDispatcher("/index.jsp");
  42. 	dispatcher.forward( request, response);	
  43. }//Close else	
  44. }
Now we are on the final leg of our quest. Firstly we make a request for session. Method call getSession(boolean) will either return existing session associated with this request or, if there is no session and boolean is true , it will create new session for us. Boolean variable dbOK will determin if we move forward or return where we started, go back to login screen with the error on the top of the page.
Lets assume we successfully read data from DB so we take on the "if" part of the route. Here we check to which user group the person login in to system belongs.
  • If it is a student, we set UserBean object to session, set ReguestDispatcher for student view of the page and forward data
  • If it is teacher we set up the session for user data, run another query similar to our first to get the list of all students (uid, first name, last name, email address and phone number) and forward this list with other session variable to teachers' page view
  • In case that user is in admin group, we repeat the above steps for teacher and also query database for list of all teachers

Assuming the request came from administrator we will finally present the user with the retrieved data, in this case admin.jsp
JSP Syntax (Toggle Plain Text) 
  1. <%@ page contentType="text/html; charset=utf-8" language="java"%>
  2. <%@ page import="java.util.ArrayList"%>
  3. <%@ page import="beans.UserBean"%>
  4. <jsp:useBean id="userBean" class="beans.UserBean" scope="session" />
  5. <jsp:useBean id="students" type="ArrayList<beans.UserBean>" scope="session" />
  6. <jsp:useBean id="teachers" type="ArrayList<beans.UserBean>" scope="session" />
  7. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  8. <html xmlns="http://www.w3.org/1999/xhtml">
  9. <head>
  10. <title>Admin Access Point</title>
  11. <link rel="stylesheet"  type="text/css" href="css/style3.css" />
  12. </head>
  13.  
  14. <body>
  15. <h1>Administrator <%=userBean.getFirstName() %> <%= userBean.getLastName() %></h1><br /><br />
  16.  
  17. <table>
  18. 	<tr>
  19.     	<th colspan="5" style="background-color:#7c2f97;">Teachers records</th>
  20.     </tr>
  21. 	<tr style="background-color:#f0a64e;">
  22.     	<th class="border">Teacher ID</th>
  23.         <th class="border">First Name</th>
  24.         <th class="border">Last Name</th>
  25.         <th class="border">Email</th>
  26.         <th class="border">Telephone</th>
  27.     </tr>
  28.     <%for(int i=0; i < teachers.size(); i++)
  29. 	{
  30. 		UserBean user = new UserBean();
  31. 		user = (UserBean) teachers.get(i);
  32. 		%>
  33.         <tr>
  34.         	<td><%= user.getUid() %></td>
  35.             <td><%= user.getFirstName() %></td>
  36.             <td><%= user.getLastName() %></td>
  37.             <td><%= user.getEmail() %></td>
  38.             <td><%= user.getPhone() %></td>
  39.         </tr>
  40.         <%}%>
  41. </table><br /><br />
  42.  
  43. <table>
  44. 	<tr>
  45.     	<th colspan="5" style="background-color:#7c2f97;">Students records</th>
  46.     </tr>
  47. 	<tr style="background-color:#f0a64e;">
  48.     	<th class="border">Student ID</th>
  49.         <th class="border">First Name</th>
  50.         <th class="border">Last Name</th>
  51.         <th class="border">Email</th>
  52.         <th class="border">Telephone</th>
  53.     </tr>
  54.     <%for(int i=0; i < students.size(); i++)
  55. 	{
  56. 		UserBean user = new UserBean();
  57. 		user = (UserBean) students.get(i);
  58. 		%>
  59.         <tr>
  60.         	<td><%= user.getUid() %></td>
  61.             <td><%= user.getFirstName() %></td>
  62.             <td><%= user.getLastName() %></td>
  63.             <td><%= user.getEmail() %></td>
  64.             <td><%= user.getPhone() %></td>
  65.         </tr>
  66.         <%}%>
  67. </table>
  68. </body>
  69. </html>
In the first lines we are using JSP directives ( directives are messages to the JSP container that enable us to include content from other resource) to declare content type and import packages as required, in this case ArrayList and the UserBean. The process is followed by retrieval of session objects. Please note that
JSP Syntax (Toggle Plain Text) 
  1. <jsp:useBean id="students" type="ArrayList<beans.UserBean>" scope="session" />
is a more elegant way of commonly known
JSP Syntax (Toggle Plain Text) 
  1. <%
  2. HttpSession session = request.getSession( );
  3. ArrayList<beans.UserBean> students = new ArrayList<beans.UserBean>();
  4. session.setAttribute("students", students);
  5. %>
after creating the bean instance, it's just a matter of accessing the student list in the session using a c:forEach tag.

As you see from above code presenting collected data from the database is complicated by opening and closing number of brackets. Often it is difficult to keep track and mistakes are easily made and difficult to correct for beginners.
</tr>
    <%for(int i=0; i < students.size(); i++)
	{
		UserBean user = new UserBean();
		user = (UserBean) students.get(i);
		%>
        <tr>
        	<td><%= user.getUid() %></td>
            <td><%= user.getFirstName() %></td>
            <td><%= user.getLastName() %></td>
            <td><%= user.getEmail() %></td>
            <td><%= user.getPhone() %></td>
        </tr>
        <%}%>
With growing amount of data to by displayed, in variety of statements, logic will quickly become very confusing. Can you imagine number of lines to go through if we did not moved all our business logic code (LoginServlet, DataManager, Validation) to server side? Fortunately we can also address to the problem of scriptlets through implementation of JSTL - Java Server Pages Standard Tag Library.
Note: At this stage make sure you placed jstl.jar and standard.jar into your lib folder.
The advantages of JSTL over scriptlets are:
  • they enhance readability
  • they simplify enhancibility and maintainability
  • it facilitates parallel development wherein even page designers can use JSTL to define the markup while developer deals with implementing the logic. This is made possible because JSTL is valid XML markup, scriptlets are just chunks of Java code
JSTL consists of five tag libraries with following functionalities
  1. Core - variable support, flow control, URL management and miscellaneous
    (c:catch, c:choose, c:ferEach, c:forTokens, c:if, c:import, c:otherwise, c:out, c:param, c:redirect, c:remove, c:set, c:url and c:when)
  2. i18n - locale, message formatting, and number and date formatting
    (fmt:bundle, fmt:formatDate, fmt:formatNumber, fmt:message, fmt:param, fmt:parseDate, fmt:parseNumber, fmt:requestEncoding, fmt:setBundle, fmt:setLocale, fmt:setTimeZone and fmt:timeZone)
  3. Functions - collection length and string manipulation
    (fn:contains, fn:containsIgnoreCase, fn:endsWith, fn:escapeXml, fn:indexOf, fn:join, fn:length, fn:replace, fn:split, fn:startsWith, fn:substring, fn:substringAfter, fn:substringBefore, fn:toLowerCase, fn:toUpperCase and fn:trim)
  4. Database - SQL
    (sql:dateParam, sql:param, sql:query, sql:setDataSource, sql:transaction and sql:update)
  5. XML - XML core, flow control and transformation
    (x:choose, x:forEach, x:if, x:otherwise, x:out, x:param, x:parse, x:set, x:transform and x:when)
Compare these two snippets to display students data
1) without JSTL
html Syntax (Toggle Plain Text) 
  1. <%@ page import="java.util.ArrayList"%>
  2. <%@ page import="beans.UserBean"%>
  3. <jsp:useBean id="students" type="ArrayList<beans.UserBean> " scope="session" />
  4.  
  5. /* Rest of the code till relevant section */
  6.  
  7. <table> 
  8. 	<tr> 
  9.     	<th colspan="5" style="background-color:#7c2f97;"> Students records</th> 
  10.     </tr> 
  11. 	<tr style="background-color:#f0a64e;"> 
  12.     	<th class="border"> Student ID</th> 
  13.         <th class="border"> First Name</th> 
  14.         <th class="border"> Last Name</th> 
  15.         <th class="border"> Email</th> 
  16.         <th class="border"> Telephone</th> 
  17.     </tr> 
  18.     <%for(int i=0; i < students.size(); i++)
  19. 	{
  20. 		UserBean user = new UserBean();
  21. 		user = (UserBean) students.get(i);
  22. 		%> 
  23.         <tr> 
  24.         	<td> <%= user.getUid() %> </td> 
  25.             <td> <%= user.getFirstName() %> </td> 
  26.             <td> <%= user.getLastName() %> </td> 
  27.             <td> <%= user.getEmail() %> </td> 
  28.             <td> <%= user.getPhone() %> </td> 
  29.         </tr> 
  30.         <%}%> 
  31. </table>
2) with JSTL
html Syntax (Toggle Plain Text) 
  1. <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
  2.  
  3.  
  4. /* Rest of the code till relevant section */
  5.  
  6.  
  7. <table>
  8. 	<tr>
  9.     	<th colspan="5" style="background-color:#7c2f97;">Students records</th>
  10.     </tr>
  11. 	<tr style="background-color:#f0a64e;">
  12.     	<th class="border">Student ID</th>
  13.         <th class="border">First Name</th>
  14.         <th class="border">Last Name</th>
  15.         <th class="border">Email</th>
  16.         <th class="border">Telephone</th>
  17.     </tr>
  18.     <c:forEach var="student" items="${students}">
  19.     	<tr>
  20.     		<td>${student.uid}</td>
  21.     		<td>${student.firstName}</td>
  22.     		<td>${student.lastName}</td>
  23.     		<td>${student.email}</td>
  24.     		<td>${student.phone}</td>
  25.     	</tr>
  26.     </c:forEach>     
  27. </table>
JSTL is based on Expression Language (EL) which is similar to HTML or XML syntax and it make it easier for us to access certain type of information. I will not cover all in depth info on this. However I will give you quick info on expressions used in the above example, plus I point you to additional learning resources for you to use.
In the example above we need to retrieve the collection stored in the session object "students" and iterate through to display all wanted data stored in it. JSTL equivalent to well know and used for loop is tag <c:forEach>. The tags has two main attributes
  • items - the collection over which to iterate
  • var - name of the attribute to expose (make available/accessible) the current item
Note: The order of the attributes is insignificant as they will work either way.
To display attributes of current item, we will call the attributes directly by the names instead of addressing them through getter methods ${student.firstName} .

Only one "ugly" scriplet left in our project and that is in index.jsp
JSP Syntax (Toggle Plain Text) 
  1. <% String e = (String) session.getAttribute("error" );
  2. if(e != null)
  3. {
  4. 	out.print(e); 
  5. }%>
This will be replaced by EL representation of if statement
JSP Syntax (Toggle Plain Text) 
  1. <c:if test="${error != null}">
  2. ${error}
  3. <c:set var="error" scope="request" value="${null}"/>
  4. </c:if>
The expression will get error object from the session. It will check if there is any value stored in it, in case the error has been initialised by LoginServlet, our login attempted failed, display the message inside the error object.
Here are some learning resources as promised: JavaServer Pages Standard Tag Library from The J2EE 1.4 Tutorial, books Head First Servlets & JSP by O'Reilly publishing or JSTL in Action by Manning Publications Co.


Big thank you goes to ~s.o.s~ for his worthy comments and help on this topic. In doing so you help me understand things I just implemented "by the book" as touched at school or what I read somewhere.
Thank you ~s.o.s~


Further improvement
  • Adding count to unsuccessful login attempts which once exceeded, will block further attempts for a period of time and may forward a message to administrator
  • Extending project functionality, adding unique options to each user group, for example student access to enrolled modules, access to module marks, participation in surveys or online tests for selected modules. Teachers adding new study materials for the modules, creating new surveys/exams, adding marks to student personal records etc.
  • Provide logout process, that will destroy session and invalidate possibility of getting access to previously used/view content by hitting go back button in the browser
  • To improve performance of database connection where establishing new connection may sometimes take even few second can be helped with implementation of Connection Pooling. If you decide to implement your own Pooling mechanism some tips can be handy or maybe you will give a go to one of already existing Pooling Tools
  • Lastly, many of us like to keep an eye on our "babies", how do they perform, are there any exception happening; and many other events in web site life cycle. Therefore implementation of logging mechanism is one of must have. For start you may try Apache log4j (some examples)

You can upload attached sql file through database tools such as phpMyAdmin or MySQL Query Browser.
There are 3 admin JSP documents:
  • admin.jsp used by the project
  • admin_no_jstl.jsp to show page with scriplets
  • admin_jstl.jsp to show page with the use of JSTL
To see page either with JSTL or without it,save one of the last two documents through "Save as" option in your IDE and give it a name of admin.jsp.
Bellowed attached screen shots shows login page with error message and administrator page view after successful login into system.
Do not forget to change username and password for database access inside the web.xml file!
Last edited by peter_budo; Dec 2nd, 2008 at 7:00 am. Reason: Updating tutorial
Attached Images
File Type: png login_screen_error.png (13.0 KB, 150 views)
File Type: png admin_view.png (18.6 KB, 159 views)
Attached Files
File Type: zip danijsptutorial-database.zip (1.5 KB, 270 views)
File Type: zip daniwebtutorial.zip (25.1 KB, 312 views)
Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group
Quick reply to this message  
Closed Thread

Tags
•
•
•
•
Tags
connection, database, jsp, mvc2, mvcmodel2, servlet, tutorial

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the JSP Forum
Thread Tools Search this Thread



.net 2008 access acquisition ada ado.net age ajax amd app array asp asp.net aspandmssqlserver2005 aspconnection avatar backup bigbrother bluegene breach business c# check chips connectingtodatabaseinuse connection daniweb data database databaseconnection development dos economy energy enterprise enterprisesoftware exam frames glassfish hacker hacking hardware ibm ibm.news images intelibm introduction java javascript jsp levels linux map medicine memory microsoft msmsql mssql mssqlserver2005 mvc2 myregfun mysql news openoffice opensource operatingsystem oracle pc php ping professor ps3 recession record redhat response runtime russia security server servlet simpledb software sql sqlserver ssl sun supercomputer supercomputing survey technology trends tutorial ubuntu uk vb vb6 visual working x86
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC