Struts for Beginners: Data Base Connections

Back | Tutorial Home | Next

Now let’s convert our dumb login program to an intelligent login program which connects to the data base and validates the user name and password before spitting out the response. We will use open source database My SQL. You can use any other database you are comfortable with.

This tutorial section uses the general Data Source and pooling provided by Tomcat. Alternatively you can also use Struts datasources which is not very popular and is not covered in this tutorial.

Create a database firing the following query in the My SQL client

create database DOC_MANAGER;

Create a table to store login data

CREATE TABLE CMS_USER(
ID INT AUTO_INCREMENT,
USER_NAME VARCHAR(80),
PASSWORD VARCHAR(80),
CONSTRAINT CMS_USER_PK PRIMARY KEY (ID)
);

Insert values in the table

insert into cms_user (user_name, password) values ('admin', 'admin123');

Configuring My SQL in Tomcat Server

Download official JDBC driver for My SQL at dev.mysql.com/downloads/

Copy downloaded My SQL driver to <Tomcat Home>\webapps\lib.

Change the UserLoginAction.java to accommodate the login checking code. We will use the general database access methodology we used in Tomcat.

Import following classes

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;

Do following changes in the execute method

boolean loginFlag = false;

Change execute method to create following variables

Connection conn=null; // For database connection
ResultSet rs = null; // For database connection
boolean loginFlag = false; // flag for login check

Now go ahead and write database access code.

if (isCancelled(request)){
			return mapping.findForward("logincancel");
		}
		else {
			try{
				Context context = new InitialContext();
				Context envContext  = (Context)context.lookup("java:/comp/env");
				DataSource dataSource =(DataSource)envContext.lookup("jdbc/docmanager");
				conn = dataSource.getConnection();
				LoginForm loginForm = (LoginForm) form;
				PreparedStatement statement = conn.prepareStatement(
				"SELECT USER_NAME, PASSWORD FROM CMS_USER WHERE USER_NAME=? AND PASSWORD=?");
				statement.setString(1,loginForm.getuserName());
				statement.setString(2,loginForm.getPassword());
				rs = statement.executeQuery();
				
				while(rs.next()) {
					loginFlag=true;
				}
			}
			catch(Exception e) {
				System.out.println("db message: " + e.getMessage());
			}
			finally{
			conn.close();
			}
			if (loginFlag)
				return mapping.findForward("loginsuccess");
			else
				return mapping.findForward("loginfailure");
			
		}

The complete code listing of UserLoginAction.java

package com.salsa;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionForm;

public class UserLoginAction extends Action {
public ActionForward execute(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception {
		System.out.println("UserLoginAction::execute()");
		Connection conn=null;
		ResultSet rs = null;
		boolean loginFlag = false;
		if (isCancelled(request)){
			return mapping.findForward("logincancel");
		}
		else {
			try{
				Context context = new InitialContext();
				Context envContext  = (Context)context.lookup("java:/comp/env");
				DataSource dataSource =(DataSource)envContext.lookup("jdbc/docmanager");
				conn = dataSource.getConnection();
				LoginForm loginForm = (LoginForm) form;
				PreparedStatement statement = conn.prepareStatement(
				"SELECT USER_NAME, PASSWORD FROM CMS_USER WHERE USER_NAME=? AND PASSWORD=?");
				statement.setString(1,loginForm.getuserName());
				statement.setString(2,loginForm.getPassword());
				rs = statement.executeQuery();
				
				while(rs.next()) {
					loginFlag=true;
				}
			}
			catch(Exception e) {
				System.out.println("db message: " + e.getMessage());
			}
			finally{
			conn.close();
			}
			if (loginFlag)
				return mapping.findForward("loginsuccess");
			else
				return mapping.findForward("loginfailure");
			
		}	
	}
}

Configure context and datasource as shown below in server.xml.

<Context path="/salsa-tutorial" docBase="salsa-tutorial" debug="1" reloadable="true" crossContext="true"
useNaming="true">

<Resource name="jdbc/docmanager" auth="Container"
type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/doc_manager"
username="dbuser" password="dbpassword" maxActive="20" maxIdle="10"
maxWait="-1"/>
</Context>

Add a new forward in struts-config.xml for failed logins after database check.

<forward name="loginfailure" path="/loginFailure.jsp" />

Finally create a new file loginFailure.jsp

<html>
<head>
<title>
Login Status
</title>
</head>
<body>
Wrong User Name or Password
</body>
</html>

To check your login program fire URL http://localhost:8080/salsa-tutorial/loginForm.jsp in your browser.

For simplicity this tutorial has database access code in the execute method of the UserLoginAction.java. However in real time systems this code goes into separate classes called DAO (Data Access Object).

Back | Tutorial Home | Next