joinbean.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 JoinBean
{
	private DataSource dataSource = null;
	Log log = LogFactory.getLog(JoinBean.class);	// commons logging reference
	
	/**
	 * Constructor
	 * @param dataSource
	 */
	public JoinBean(DataSource dataSource)
	{
		this.dataSource = dataSource;
	}
	
	/**
	 * creates user account
	 * returns a user DTO if account is created ok
	 * @param email
	 * @param password
	 * @return UserDTO
	 */
	public UserDTO createUser(String fname, String lname, 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 = "INSERT INTO users (fname,lname,email,password,lastlogin,numlogins,created)";
			sQuery += " values('" + fname + "','" + lname + "','" + email + "','" + password + "',now(),1,now())";
			int result = stmt.executeUpdate(sQuery);
			if(result == 1)	// insertion went ok, retrieve record to get id
			{
				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"));
				}
			}
		}
		catch (SQLException se)
		{
			if(se.getLocalizedMessage().indexOf("Duplicate") == -1)
			{
				log.error("Error in creating 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;
	}
}