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.