7 replies to this topic

[brbcoding]

Hi CodeCall...

I'm working on a group project for class, in which I must use a MS SQL database, and JSP, HTML, CSS, etc... To make a site that includes a registration and a login. So far, my registration works just fine. Here is the code I've come up with for that:

		  <%
		  try{
		  // load the driver and create the connection
		  Connection con = null;
		  Class.forName("...");  // load the driver
		  con = DriverManager.getConnection("...");

		  String fname = request.getParameter("fname");
		  String lname = request.getParameter("lname");
		  String streetnumber = request.getParameter("snumber");
		  String streetname = request.getParameter("sname");
		  String city = request.getParameter("city");
		  String state = request.getParameter("state");
		  String zip = request.getParameter("zip");
		  String email = request.getParameter("email");
		  String theusername = request.getParameter("theusername");
		  String pw1 = request.getParameter("pw1");
		  String pw2 = request.getParameter("pw2");
		  String phone = request.getParameter("phone");
		  String country = request.getParameter("country");
		  String first3 = email.substring(0,3);
		  int rand = (int)(Math.random() * 1000 + 1);
		  String rand1 = Integer.toString(rand);

		 if(pw1.equals(pw2))
		 {

		  PreparedStatement prep = con.prepareStatement("Insert into Applicant (FirstName,LastName,Password,ApplicantUserName,AddressID) values (?,?,?,?,?) ");

		  prep.setString(1,fname);
		  prep.setString(2,lname);
		  prep.setString(3,pw1);
		  prep.setString(4,theusername);
		  prep.setString(5,first3+rand1);

		  PreparedStatement addressprep = con.prepareStatement ("Insert into Address (StreetNo,StreetName,City,State,ZipCode,EmailAddress,TelephoneNo,Country,AddressID) values (?,?,?,?,?,?,?,?,?) ");
		  addressprep.setString(1,streetnumber);
		  addressprep.setString(2,streetname);
		  addressprep.setString(3,city);
		  addressprep.setString(4,state);
		  addressprep.setString(5,zip);
		  addressprep.setString(6,email);
		  addressprep.setString(7,phone);
		  addressprep.setString(8,country);
		  addressprep.setString(9,first3+rand1);

		  int result = prep.executeUpdate();
		  int result2 = addressprep.executeUpdate();

		  out.println(result+"Registration Successful!<br/>");
		  out.println(result2+"Done.");

		  prep.close();

		 }
		  else
		   {
			  out.println("Passwords must match
");
		  }

		  con.close();

		 }
		  catch(Exception ex)
		  {
			  out.println("Sorry the database is unavailable");
			  out.println(ex.toString());
		  }


		%>

I've got two tables, one of which is an Applicants table, and one of which is a Company table. Each has it's own registration page, and it's own login page. Unfortunately, I don't have time to normalize the database and make a Users table (and this is a group project, they decided against it?) But, that's neither here nor there...

So, I've hit a block, and have basically been banging my head on the keyboard trying to figure out the login part of this... I guess I'm really not positive as to how to approach it. Should I be using Beans? Servlets? Or can I do it all through another JSP page? I mean, is there any way to do anything like this?:

Terrible, horrid pseudocode below.

//obviously not using correct syntax, or the entire code...

try {
connection...

String username = request.getParameter("username");
String password = request.getParameter("password");
boolean valid = true;

PreparedStatement prep = con.prepareStatement("Select from Applicants password where username = "username" ");

//something about password exists or not here...

if(password == null) { //or something

valid = false;

}
if(valid == false) {
go back to login w/ error
else {
go to welcome page
}
}

The above is kind of my thought process as to how I think it should work, but I may be going at it totally wrong. Do I need to use ResultSet to check the cells in the database? Argghhh I've been banging away for weeks at this now, and just have tried so many different things that I don't know where to re-start. Thanks in advance, I really appreciate it.

[brbcoding]

Okay, I've come to the conclusion that for my idea to work (still don't know if it will), I need to use a resultset... Maybe something like:

        String password = request.getParameter("userName");
        String password = request.getParameter("password");


        PreparedStatement prep = con.prepareStatement("SELECT ApplicantUserName,Password FROM Applicant");
        ResultSet result = prep.executeQuery();
        while(result.next()){
        String s = result.getString(1);
        String t = result.getString(2);
        }
        if(s == username && t == password) {
        Success Page!
        }else{
        Failure Page (Redirect to Login w/ error message.)
        }
//that should get me each string out of the cells...
}

Something like that? Dunno if that'll work or if I'm creating a big bad bug.

[brbcoding]

		  try{
		  // load the driver and create the connection
		Connection con = null;
		Class.forName("...");  // load the driver
		con = DriverManager.getConnection("...");

		String username = request.getParameter("userName");
		String password = request.getParameter("password");					

		PreparedStatement prep = con.prepareStatement("SELECT ApplicantUserName,Password FROM Applicant WHERE ApplicantUserName=? AND Password=?");
		prep.setString(1,username);
		prep.setString(2,password);
		ResultSet result = prep.executeQuery();
		if(result.next()){
			out.println("Welcome!");
					   }
			   else{
			out.println("Incorrect Username or Password.");
		}

		 }

Actually this works to tell me if a user and password exist in my db... Does this work or is it bad?

Oh and sorry about using the forum to work through my problem, but maybe someone else will have the same one sometime?

[wim DC]

Selecting a user record from the DB with a where clause of username and password is propably the most common way to do this.

You'll want to store something in the session (request.getSession().setAttribute(..., ...) ) so on the other pages you know whether or not the user is logged in.

Should I be using Beans? Servlets? Or can I do it all through another JSP page

Yes, yes and yes.
You CAN do it all trough JSP, but it's generally accepted in the Java community that ANY line of code in a JSP is a line too much.

[brbcoding]

Fantastic! I haven't gotten to the session part yet, but that I'm working on now... I wonder why we are learning to put all of this on a JSP if it's not the right way to do it. Mind=Blown. Thank you

[wim DC]

I wonder why we are learning to put all of this on a JSP if it's not the right way to do it. Mind=Blown. Thank you

Cause it makes the dive into java web applications less painful since there is little extra you need to learn to write code in JSPs.
There's just a few new classes like HttpServletRequest (=request) and objects (like out, which I believe is a PrintWriter).

Starting Java web apps is propably confusing enough with all the other stuff that's new like a web.xml, web server (likely to be tomcat) etc...

[brbcoding]

Well, if anyone is interested, this is what I came up with... It works how I need it to. Like I said, the database was not normalized, so it could have been greatly simplified if it was (by using some sort of a users table). Critique is welcomed, I am relatively new to JSP stuff, so every learning experience counts.

<%
          try{
          // load the driver and create the connection
        Connection con = null; 
        Class.forName("...");  
        con = DriverManager.getConnection("...");     
        String username = request.getParameter("userName");
        String password = request.getParameter("password");                    
        String usertype = request.getParameter("radios");
        String appsession = "appsession";
        String compsession = "compsession";
        String redirect = "error.html";
        String cl = "companylanding.jsp";
        String al = "applicantlanding.jsp";
        if(username==null || password==null || usertype==null){
            response.sendRedirect(redirect);
                   }
        if(usertype.equals("applicant")){
        PreparedStatement prep = con.prepareStatement("SELECT ApplicantUserName,Password FROM Applicant WHERE ApplicantUserName=? AND Password=?");
        prep.setString(1,username);
        prep.setString(2,password);
        ResultSet result = prep.executeQuery();
            if(result.next()){
            session.setAttribute("username", username);
            session.setAttribute("appsession", appsession);
            response.sendRedirect(al);

        } 
        else {
            prep.close();
            con.close();
            response.sendRedirect(redirect);

         }
               }
        if(usertype.equals("company")){
            PreparedStatement prepcompany = con.prepareStatement("SELECT CompanyUserName,Password,CompanyName FROM Company WHERE CompanyUserName=? AND Password=?");
            prepcompany.setString(1,username);
            prepcompany.setString(2,password);
            ResultSet result = prepcompany.executeQuery();

            if(result.next()){
                session.setAttribute("username", username);
                session.setAttribute("compsession",compsession);
                session.setAttribute("companyname",result.getString(3));
                response.sendRedirect(cl);
                               }
                       else{
                prepcompany.close();
                con.close();
                response.sendRedirect(redirect);
            }
        }        
                 }

          catch(Exception ex)
          {
              out.println("Sorry the database is unavailable");
              out.println(ex.toString());
          }

        %>

[wim DC]

else {
    prep.close();
    con.close();
    response.sendRedirect(redirect);
}

prep is out of scope.
resultset isn't closed.

If everything is successful, nothing gets closed at all!
Closing of such connections, statements etc is usually done in a try-finally block so it always will get executed.
Pretty ugly though :

} finally {
   if (resultSet != null) {
    try {
	 resultSet.close();
    } catch (SQLException e) {
    }
   }
   if (ps != null) {
    try {
	 ps.close();
    } catch (SQLException e) {
    }
   }
   if (connnection != null) {
    try {
	 connnection.close();
    } catch (SQLException e) {
    }
   }
  }