loginbean.java

the musiccollection struts 1 application i netbeans implementation (strut for dummies book source)

package dummies.struts.music;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts.util.ModuleException;

/**
 * @author Mike Robinson
 *
 */
public class LoginBean
{
	private DataSource dataSource = null;
	Log log = LogFactory.getLog(LoginBean.class);	// commons logging reference
	
	/**
	 * Constructor
	 * @param dataSource
	 */
	public LoginBean(DataSource dataSource)
	{
		this.dataSource = dataSource;
	}
	
	/**
	 * validates user's email and password against db records
	 * creates a user DTO if user is valid
	 * @param email
	 * @param password
	 * @return
	 */
	public UserDTO validateUser(String email, String password) throws ModuleException
	{
		UserDTO user = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sQuery = "";
		try
		{
			con = dataSource.getConnection();
			stmt = con.createStatement();
			sQuery = "SELECT * FROM users " + "WHERE email = '" + email + "' " + "AND password = '" + password + "'";
			rs = stmt.executeQuery(sQuery);
			if (rs.next())
			{
				// Create new user transfer object
				user = new UserDTO();
				user.setFirstName(rs.getString("fname"));
				user.setLastName(rs.getString("lname"));
				user.setId(rs.getInt("id"));
				user.setEmail(rs.getString("email"));
				
				// update user login information
				sQuery = "UPDATE users SET lastlogin=now(),numlogins=numlogins+1 where id=" + user.getId();
				int result = stmt.executeUpdate(sQuery);
			}
		}
		catch (SQLException se)
		{
			log.error("Error in validating user.");
			log.error("SQL statement = " + sQuery);
			se.printStackTrace();
			ModuleException me = new ModuleException("error.db.sql");
			throw me;
		}
		
		// Be sure to always try and close ResultSet, Statement, and Connection
		// Note that because we are using a connection pool, closing the Connection
		// does Not automatically result in closing the ResultSet and Statement.
		// Therefore, we do so explicitly here as a best practice.
		finally
		{
			try
			{
				if (rs != null) rs.close();
			}
			catch (SQLException se)
			{
				log.error("Error in closing ResultSet.");
				se.printStackTrace();
				ModuleException me = new ModuleException("error.db.sql");
				throw me;
			}
			try
			{
				if (stmt != null) stmt.close();
			}
			catch (SQLException se)
			{
				log.error("Error in closing Statement.");
				se.printStackTrace();
				ModuleException me = new ModuleException("error.db.sql");
				throw me;
			}
			try
			{
				if (con != null) con.close();
			}
			catch (SQLException se)
			{
				log.error("Error in closing Connection.");
				se.printStackTrace();
				ModuleException me = new ModuleException("error.db.sql");
				throw me;
			}
		}
		return user;
	}
}