JSP and Database Review


Searching Databases

  1. The first step is to compose your SQL query and test it,  for example:
Select crn, course, title, instructor from CourseSchedule where title like 'Intro%' and instructor like 'p%'
  1. Next  identify the parts of the query that you want the user to specify, in the case  the title and instructor pattern
  2. Create an HTML form that allows the user to enter the values:
       <form name="ClassSearch" method="get" action="SearchCourse.jsp" onSubmit ="return checkForm();">
            Enter part or all of the title : <input type="text" name="title" size="30"><br/>
             Enter part or all of the instructors name: <input type="text" name="instructor" size="30"><br/>
            <input type ="submit" value="search"><br/>
            <input type ="reset" value="clear"><br />
     </form>

  1. Now create Javascript to verify the form fields before submission,  this code is linked to the form tag using the onSubmit event. Make sure the method returns a true/false value and uses the same names for the form and its fields that the form tag did.
<SCRIPT TYPE="text/javascript">
<!--
  function checkForm()
  {
    var valid = true;
    var message = "Errors: \n";

    with(document.ClassSearch)
    {
     if(title.value == "")
     {
       valid = false;
       message = message + "title field is blank\n";
       title.style.borderColor = "red"; // red border
     }
     else
        title.style.borderColor = "gray"; // gray border  
    }

    if(!valid)
      alert(message);
    return valid;
  }
// -->
</SCRIPT>
  1. Create your jsp to process the form,  the name of the jsp needs to match the action in the form tag of step 3. The names used in the request.getParameter calls need to match the names used on the form.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Courses</h1>
        <%
        try{
          Connection con = null;
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  // load the driver, then connect to the db
          con = DriverManager.getConnection("jdbc:sqlserver://rubble.student.ad.fgcu.edu:1433;databaseName=sp12ism3232x;user=s12ISM3232;password=s12ISM3232;");
         
           String title = request.getParameter("title");
           String instructor = request.getParameter("instructor");
           if(title != null && instructor != null)
           {
            PreparedStatement prep = con.prepareStatement("Select crn, course, title, instructor from CourseSchedule where title like ? and instructor like ?");
            prep.setString(1,"%"+title+"%");  // replace the first ? with user input for the title
            prep.setString(2,instructor+"%");  // replace the second ? with user input for the instructor
            ResultSet rs = prep.executeQuery();  // issue the SELECT statement
      
            out.println("<table border=1>");  // start a table
            while(rs.next())  // process records in the result set
            {
             out.println("<tr>");  // start a row
             out.println("<td>"+rs.getString(1)+"</td>");  // crn
             out.println("<td>"+rs.getString(2)+"</td>");  // course
             out.println("<td>"+rs.getString(3)+"</td>");  // title
             out.println("<td>"+rs.getString(4)+"</td>");  // instructor
             out.println("</tr>");   // end the row                          
            }
            out.println("</table>"); // end the table
            prep.close();       // release the preparedStatement              
           }
           con.close();  // close the connection
          
          }
          catch(Exception ex)
          {
           out.println("Sorry, system is not currently available<br/>");
           out.println(ex.toString());
          }
       
        %>
    </body>
</html>


Adding records to a database

  1. The first step is to compose your SQL insert command and test it,  for example:
Insert into Students values ('12345','jones','barb','hellokitty')
  1. Next  identify the parts of the query that you want the user to specify, in the case name, id, and password values
  2. Create an HTML form that allows the user to enter the values:
         <form name="CreateStudent" action="CreateStudent.jsp" method="post" onSubmit="return checkForm();">
        Student ID : <input type ="text" size="12" value="" name="id" /><br />   
        Last name : <input type ="text" size="20" value="" name="last" /><br />   
        First name: <input type ="text" size="20" value="" name="first" /><br />    
        Password : <input type ="password" size="12" value="" name="pw1" /><br />
        Re-enter Password : <input type ="password" size="12" value="" name="pw2" /><br />
        <input type ="submit" value="Save" />  <input type="reset" value="clear" /> <br />
       </form>  

  1. Now create Javascript to verify the form fields before submission,  this code is linked to the form tag using the onSubmit event. Make sure the method returns a true/false value and uses the same names for the form and its fields that the form tag did.
<SCRIPT TYPE="text/javascript">
<!--
  function checkForm()
  {
    var valid = true;
    var message = "Errors: \n";

    with(document.CreateStudent)
    {
     if(id.value == "")
     {
       valid = false;
       message = message + "id field is blank\n";
       id.style.borderColor = "red"; // red border
     }
     else
        id.style.borderColor = "gray"; // gray border  
    }

    if(!valid)
      alert(message);
    return valid;
  }
// -->
</SCRIPT>

  1. Create your jsp to process the form,  the name of the jsp needs to match the action in the form tag of step 3. The names used in the request.getParameter calls need to match the names used on the form.

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Welcome New Victim</title>
    </head>
    <body>
        <h1>Newest Cannon Fodder</h1>
       <%
          try{
          // load the driver and create the connection
          Connection con = null;
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  // load the driver, create the connection
          con = DriverManager.getConnection("jdbc:sqlserver://rubble.student.ad.fgcu.edu:1433;databaseName=sp12ism3232x;user=s12ISM3232;password=s12ISM3232;");
 
          String id = request.getParameter("id");  // retrieve the form parameters
          String last = request.getParameter("last");
          String first = request.getParameter("first");
          String pw1 = request.getParameter("pw1");
          String pw2 = request.getParameter("pw2");
         
         if(pw1.equals(pw2))
         {
         
          PreparedStatement prep = con.prepareStatement("Insert into Students values (?,?,?,?) ");
          prep.setString(1,id);  // insert form parameters into sql statement
          prep.setString(2,last);
          prep.setString(3,first);
          prep.setString(4,pw1);
   
         
          int result = prep.executeUpdate();  // perform the insert into command
          out.println(result+" students added!<br/>");
       
          prep.close(); // close the prepared statement
         
         }
          else
           {
              out.println("Passwords must match<br />");
          }
             
          con.close(); // close the connection
         
         }
          catch(Exception ex)
          {
              out.println("Sorry the database is unavailable");
          }
         
      
        %>
    </body>
</html>



Deleting a record with known data


  1. The first step is to compose your SQL insert command and test it,  for example:
delete from Students where id = '12345'
  1. Next  identify the parts of the query that you want the user to specify, in this case the id
  2. Create an HTML form that allows the user to enter the values:
            
        <form name="DeleteStudent"  action="DeleteStudent.jsp" method="post" onSubmit="return checkForm();>
            ID: <Input type="text" size="15" name="studentid" /><br />
            <input type="submit" value="Delete">    
        </form>

  1. Now create Javascript to verify the form fields before submission,  this code is linked to the form tag using the onSubmit event. Make sure the method returns a true/false value and uses the same names for the form and its fields that the form tag did.
<SCRIPT TYPE="text/javascript">
<!--
  function checkForm()
  {
    var valid = true;
    var message = "Errors: \n";

    with(document.DeleteStudent)
    {
     if(studentid.value == "")
     {
       valid = false;
       message = message + "id field is blank\n";
       studentid.style.borderColor = "red"; // red border
     }
     else
        studentid.style.borderColor = "gray"; // gray border  
    }

    if(!valid)
      alert(message);
    return valid;
  }
// -->
</SCRIPT>

  1. Create your jsp to process the form,  the name of the jsp needs to match the action in the form tag of step 3. The names used in the request.getParameter calls need to match the names used on the form.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Delete Student</title>
    </head>
    <body>
        <h1>So long its been great to know you</h1>
        <%
          try{
          // load the driver and create the connection
          Connection con = null;
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  // load the driver, create the connection
          con = DriverManager.getConnection("jdbc:sqlserver://rubble.student.ad.fgcu.edu:1433;databaseName=sp12ism3232x;user=s12ISM3232;password=s12ISM3232;");
          String id = request.getParameter("studentid");
          PreparedStatement prep = con.prepareStatement("delete from Students where StudentId = ?");
          prep.setString(1,id);  // replace the parameter
          int result = prep.executeUpdate();  // perform the delete
          out.println(result+" students deleted!<br/>");  // report the result
         
          prep.close(); // close the prepared statement
          con.close();  // close the connection
         
         }
          catch(Exception ex)
          {
              out.println("Sorry the database is unavailable");
          }
         
      
        %>
    </body>


Delete a record without known information

  1. In this case you need to provide a search mechanism to allow the user to find the record they want to delete
  2. Create a search form and jsp like the search example above
  3. Modify the jsp to output an extra column of data with a link to the delete jsp.  This link needs to provide the parameter required by the delete.jsp
    1. link should have the form of the xxx.jsp?param1=value1&param2=value2& ...
    2. the ? separates the jsp name from the parameters
    3. the = separates the parameter name from the parameter value
    4. the & separates parameters (if there are more than 1)
    5. parameter names need to match those used in the jsp's request.getParameter calls.
    6. There should be no spaces in the url,  use replaceAll("\\s","%20")  to convert to proper form, note, some older systems will want you to replace spaces with a + instead of a +

          // partial code
     ResultSet rs = prep.executeQuery();
     out.println("<table border=1>");  // start the table
     while(rs.next())
     {
      out.println("<tr>");
      out.println("<td>"+rs.getString(1)+"</td>");  // id
      out.println("<td>"+rs.getString(2)+"</td>");  // last
      out.println("<td>"+rs.getString(3)+"</td>");  // first
      out.println("<td>"+rs.getString(4)+"</td>");  // password
      out.println("<td>");
      String delUrl = "DeleteStudent.jsp?studentid="+rs.getString(1);
      delUrl = delUrl.replaceAll("\\s","%20");
      out.println("<a href='"+delUrl+"'>delete</A>");
      out.println("</td>");
      out.println("<td>");
      String updUrl = "UpdateStudentForm.jsp?studentid="+rs.getString(1)+"&lastname="+rs.getString(2)+"&firstname="+rs.getString(3)+"&password="+rs.getString(4);
      updUrl = updUrl.replaceAll("\\s","%20");
      out.println("<a href='"+updUrl+"'>update</A>");
      out.println("</td>");
      out.println("</tr>");                                      
     }
     out.println("</table>");
 

Updating a record

  1. When updating a record its good to provide the user with the existing values preloaded into the form.  To make this happen, you will need a seqeuence of four html and jsp files
    1. A search form that will allow the user to find the record they want to change
    2. A jsp to process the search results allowing the user to select the record for updating
    3. A jsp that builds an update form using the data from the search results
    4. A jsp that updates the record
  2. The search form will follow the same rules as all other search forms, that is, it needs to check the fields with javascript and invoke the proper jsp.
  3. The jsp that displays the search results needs to output an extra column of data with a link to the update jsp.  This link needs to provide the parameter required by the update jsp
    1. link should have the form of the xxx.jsp?param1=value1&param2=value2& ...
    2. the ? separates the jsp name from the parameters
    3. the = separates the parameter name from the parameter value
    4. the & separates parameters (if there are more than 1)
    5. parameter names need to match those used in the jsp's request.getParameter calls.
    6. There should be no spaces in the url,  use replaceAll("\\s","%20")  to convert to proper form, note, some systems will want you to replace spaces with a + instead of a +

          // partial code
     ResultSet rs = prep.executeQuery();
     out.println("<table border=1>");  // start the table
     while(rs.next())
     {
      out.println("<tr>");
      out.println("<td>"+rs.getString(1)+"</td>");  // id
      out.println("<td>"+rs.getString(2)+"</td>");  // last
      out.println("<td>"+rs.getString(3)+"</td>");  // first
      out.println("<td>"+rs.getString(4)+"</td>");  // password
      out.println("<td>");
      String delUrl = "DeleteStudent.jsp?studentid="+rs.getString(1);
      delUrl = delUrl.replaceAll("\\s","%20");
      out.println("<a href='"+delUrl+"'>delete</A>");
      out.println("</td>");
      out.println("<td>");
      String updUrl = "UpdateStudentForm.jsp?studentid="+rs.getString(1)+"&lastname="+rs.getString(2)+"&firstname="+rs.getString(3)+"&password="+rs.getString(4);
      updUrl = updUrl.replaceAll("\\s","%20");
      out.println("<a href='"+updUrl+"'>update</A>");
      out.println("</td>");
      out.println("</tr>");                                      
     }
     out.println("</table>");

  1. Create a jsp that builds an update form using the data from the search results,  the name of the jsp needs to match the name used in the link tag of step 3. The names used in the request.getParameter calls need to match the names used on the link.  The form should include a javascript to check the fields.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Update Student</title>
<SCRIPT TYPE="text/javascript">
<!--
  function checkForm()
  {
    var valid = true;
    var message = "Errors: \n";

    with(document.UpdateStudent)
    {
     if(id.value == "")
     {
       valid = false;
       message = message + "id field is blank\n";
       id.style.borderColor = "red"; // red border
     }
     else
        id.style.borderColor = "gray"; // gray border
    
    }

    if(!valid)
      alert(message);
    return valid;

  }

// -->
</SCRIPT>
    </head>
    <body>
        <%
         String id = request.getParameter("studentid");
         String last = request.getParameter("lastname");
         String first = request.getParameter("firstname");
         String pw = request.getParameter("password");
        %>
        <h1>Update</h1>
       
        <form name="UpdateStudent" action="UpdateStudent.jsp" method="post" onSubmit="return checkForm();>
        Student ID : <input type ="text" size="12" value="<%=id %>" name="id" /><br />   
        Last name : <input type ="text" size="20" value="<%= last %>" name="last" /><br />   
        First name: <input type ="text" size="20" value="<%= first %>" name="first" /><br />    
        Password : <input type ="password" size="12" value="<%= pw %>" name="pw1" /><br />
        Re-enter Password : <input type ="password" size="12" value="<%= pw %>" name="pw2" /><br />
        <input type ="submit" value="Save" />  <input type="reset" value="Reset" /> <br />
        </form>
    </body>
</html>

  1. Create a jsp to process the update form,  the name of the jsp needs to match the action in the form tag of step 5. The names used in the request.getParameter calls need to match the names used on the form.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Update</h1>
         <%
          try{
          // load the driver and create the connection
          Connection con = null;
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  // load the driver, create the connection
          con = DriverManager.getConnection("jdbc:sqlserver://rubble.student.ad.fgcu.edu:1433;databaseName=sp12ism3232x;user=s12ISM3232;password=s12ISM3232;");
 
          String id = request.getParameter("id");
          String last = request.getParameter("last");
          String first = request.getParameter("first");
          String pw1 = request.getParameter("pw1");
          String pw2 = request.getParameter("pw2");
         
          if(pw1.equals(pw2))  // check to be sure passwords match
         {
         
          PreparedStatement prep = con.prepareStatement("update Students set lastname =?, firstname=?, password = ? where StudentId = ?");
          prep.setString(1,last);
          prep.setString(2,first);
          prep.setString(3,pw1);
          prep.setString(4,id);
         
          int result = prep.executeUpdate();
          out.println(result+" students updated!<br/>");
       
          prep.close();  // close the prepared statement
         
         }
          else
           {
              out.println("Passwords must match<br />");
          }
             
          con.close();  // close the connection
         
         }
          catch(Exception ex)
          {
              out.println("Sorry the database is unavailable");
          }
         
      
        %>
    </body>
</html>



Other notes: