Solved

Why Servlet is not updating database

Posted on 2009-04-01
7
662 Views
Last Modified: 2013-11-24
I'm using netbeans 6.5.1, oracle xe, in a Tomcat bundle. Netbeans is showing no errors of the servlett code. When I fill out the form from the browser the data isn't entering into the Student table. Could someone check my code or give me suggestions on how I can get this to work in netbeans. Again, I'm not getting erros!
*********SERVLET CODE***********
 
package UNNCPK;
 
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
 
/**
 *
 * @author Valuede Customer
 */
public class StudentForm extends HttpServlet {
 
    /** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
    }
 
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
                HttpSession session = request.getSession();
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
 
        out.println("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Strict//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd'>");
        out.println("<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en' lang='en'>");
        out.println("<head>");
        out.println("<link rel='stylesheet' href='images/Techmania.css' type='text/css' />");
        out.println("<title>49ers Student Sign-up Form</title>");
        out.println("</head>");
        out.println("<body>");
        out.println("<div id='wrap'>");
        out.println("<div id='header'>");
        out.println("<h1 id='logo-text'>49ers<span class='gray'>Student</span></h1>");
        out.println("<h2 id='slogan'><a href='http://uncc.edu/'><img src='images/UNCCcrown.gif' width='70' height='41' alt='firefox' class='align-right' /></h2>");
        out.println("<div id='header-tabs'>");
        out.println("<ul>");
        out.println("<li id='current'><a href='LoginPage'><span>Home</span></a></li>");
        out.println("<li><a href='ClassOffering'><span>Classed Offered</span></a></li>");
        out.println("<li><a href='LoginPage'><span>Detail Schedule</span></a></li>");
        out.println("<li><a href='FacultyPage'><span>Faculty</span></a></li>");
        out.println("<li><a href='LoginPage'><span>Enrollment</span></a></li>");
        out.println("<li><a href='LoginPage'><span>Support</span></a></li>	");
        out.println("</ul>");
        out.println("</div>");
        out.println("</div>");
        out.println("<div id='content-wrap'>");
        out.println("<div id='main'>");
        out.println("<a name='TemplateInfo'></a>");
        out.println("<h1>Enter your Information</h1>");
        out.println("<h3>Student Form</h3>");
        out.println("<form action='ClassOffering' method = 'post'>");
        out.println("<p>");
        out.println("<label>Social Security Number</label>");
        out.println("<input name='SSN' value=' ' type='text' size='30' />");
        out.println("<label>First Name</label>");
        out.println("<input name='FIRSTNAME' value=' 'type= 'text' size= '30' />");
        out.println("<label>Last Name</label>");
        out.println("<input name='LASTNAME'  value='  'type= 'text' size='30' />");
        out.println("<label> Create Your User Name</label>");
        out.println("<input name='USERNAME'value='  'type= 'text' size='30' />");
        out.println("<label>Create Your Password</label>");
        out.println("<input name='PASSWORD' value='password 'type= 'text' size= '30' />");
        out.println("<label>City</label>");
        out.println("<input name='CITY' value='  ' type= 'text' size='30' />");
        out.println("<label>Major</label>");
        out.println("<input name='MAJOR' value='  ' type= 'text' size='30' />");
        out.println("<label>Minor</label>");
        out.println("<input name= 'MINOR' value='   ' type='text' size='30' />");
        out.println("<label>State</label>");
        out.println("<input name='STATE' value='  ' type='text' size='30' />");
        out.println("<label>Zip</label>");
        out.println("<input name='ZIP' value=' ' type='text' size='30'/>");
        out.println("<input class='button' type='submit' />");
        out.println("</p>");
        out.println("</form>");
        out.println("<br />");
        out.println("</div>");
        out.println("<div id='sidebar'>");
        out.println("<h1>Sidebar Menu</h1>");
        out.println("<ul class='sidemenu'>");
        out.println("<li><a href='LoginPage'>Home</a></li>");
        out.println("<li><a href='ClassOffering'>Classes Offered</a></li>");
        out.println("<li><a href='FacultyPage'>Faculty Support</a></li>");
        out.println("<li><a href=''>Your Schdele</a></li>");
        out.println("<li><a href=''>Enrollment</a></li>");
        out.println("</ul>");
        out.println("<h1>Search</h1>");
        out.println("<form method='post' id='search' action='#'>");
        out.println("<p>");
        out.println("<input name='search_query' class='textbox' type='text' /> ");
        out.println("<input name='search' class='searchbutton' value='Search' type='submit' />");
        out.println("</p>");
        out.println("</form>");
        out.println("<h1>Wise Words</h1>");
        out.println("<p>What do we put here</p>");
        out.println("<p class='align-right'>- Aristotle</p>");
        out.println("<h1>Support Styleshout</h1>");
        out.println("<p><a href='http://www.google.com/'>google</a> </p>");
        out.println("</div>");
        out.println("</div>");
        out.println("<div id='footer'>");
        out.println("<span id='footer-left'>&copy; 2009 <strong>Group 8</strong> ");
        out.println("Design by: <strong><a href='index.html'>Mario</a></strong> ");
        out.println("Valid: <a href='http://validator.w3.org/check?uri=referer'>XHTML</a>");
        out.println("<a href='http://jigsaw.w3.org/css-validator/check/referer'>CSS</a>");
        out.println("</span>");
        out.println("<span id='footer-right'>");
        out.println("<a href='http://www.uncc.edu'>UNCC Home</a> | ");
        out.println("<a href='https://selfservice.uncc.edu/pls/BANPROD/twbkwbis.P_GenMenu?name=homepage'>" +
                "Banner Self Service </a>");
        out.println("</span>");
        out.println("</div>");
        out.println("</div>");
 
 
 
 
            String SSN = request.getParameter("SSN");
            String FIRSTNAME = request.getParameter("FIRSTNAME");
            String LASTNAME = request.getParameter("LASTNAME");
            String USERNAME = request.getParameter("USERNAME");
            String PASSWORD = request.getParameter("PASSWORD");
            String CITY = request.getParameter("CITY");
            String MAJOR = request.getParameter("MAJOR");
            String MINOR = request.getParameter("MINOR");
            String STATE = request.getParameter("STATE");
            String ZIP = request.getParameter("ZIP");
 
 
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
 
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:xe[uncc on UNCC]",
                    "UNCC",
                    "UNCC");
 
            Statement stmt = (Statement)conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT COUNT (*) FROM STUDENT WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");
             if (rs.next()==true)
             {
               stmt.executeUpdate("UPDATE STUDENT SET StdSNN = '"+SSN+"'AND StdFirstName ='"+FIRSTNAME+"'AND StdLastName='"+LASTNAME+"' AND StdUserName='"+USERNAME+"' StdPassword='"+PASSWORD+"' AND StdCity='"+CITY+"' AND StdMajor='"+MAJOR +"' AND StdMinor ='"+MINOR+"'AND StdState='"+STATE+"' StdZip='"+ZIP+"' WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");
             } else
             { stmt.executeUpdate ("INSERT INTO STUDENT (StdSSN, StdFirstName, StdLastName, StdUserName, StdPassword, StdCity, StdMajor, StdMinor, StdState, StdZip, StdClass, StdAdvisor, StdGPA VALUES ('"+SSN+"', '"+FIRSTNAME+"', '"+LASTNAME+"', '"+USERNAME+"','"+PASSWORD+"','"+CITY+"','"+MAJOR +"','"+MINOR +"','"+STATE+"','"+ZIP+"', '', '', '')");
                }
 
 
            } catch(SQLException e) {
                out.println("SQLException: " + e.getMessage() + "<BR>");
                 while((e = e.getNextException()) != null)
                 out.println(e.getMessage() + "<BR>");
            } catch(ClassNotFoundException e) {
               out.println("ClassNotFoundException: " + e.getMessage() + "<BR>");
            } finally {
              //Clean up resources, close the connection.
              if(conn != null) {
                  try {
                     conn.close();
                 } catch (Exception ignored) {
                 }
 
                }
 
            }
            out.println("</body> ");
            out.println("</html>");
            out.close();
       }
     protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        }
}
 
 
**************SQL TABLE***************************
 
create table Student
( StdSSN Char(11) NOT NULL,
  StdFirstName VARCHAR2(50) NOT NULL,
  StdLastName VARCHAR2(50) NOT NULL,
  StdUserName VARCHAR2 (25) NOT NULL,
  StdPassword VARCHAR2 (12) NOT NULL,
  StdCity VARCHAR2 (50) NOT NULL,
  StdMajor VARCHAR2(50),
  StdMinor VARCHAR2(50),
  StdState CHAR(2) NOT NULL,
  StdZip CHAR(10) NOT NUll, 
  StdClass CHAR(2),
  StdAdvisor VARCHAR2(50) NOT NULL,
  StdGPA DECIMAL (3,2),
  Constraint PKStudent Primary Key (StdSSN),
  Constraint FKDeptID FOREIGN KEY (StdMajor)
  REFERENCES Department (DeptID)
);

Open in new window

0
Comment
Question by:percivalevans
  • 3
  • 3
7 Comments
 
LVL 5

Expert Comment

by:trungnt8
ID: 24046100
Your form is POST, but your doPost method is empty?
0
 
LVL 3

Expert Comment

by:kingsob
ID: 24047602
as trungnt8 said, your goPost method is empty. the values returned by request.getParameter are all going to be null, and since your database table does not accept null values, it will likely throw some kind of exception

you are going to want someting like this... if it were me tho, i would seperate the html out into a seperate html file, and then just post it to your servlet


Derek
*********SERVLET CODE***********
 
package UNNCPK;
 
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
 
/**
 *
 * @author Valuede Customer
 */
public class StudentForm extends HttpServlet {
 
    /** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
    }
 
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
                HttpSession session = request.getSession();
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
 
        out.println("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Strict//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd'>");
        out.println("<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en' lang='en'>");
        out.println("<head>");
        out.println("<link rel='stylesheet' href='images/Techmania.css' type='text/css' />");
        out.println("<title>49ers Student Sign-up Form</title>");
        out.println("</head>");
        out.println("<body>");
        out.println("<div id='wrap'>");
        out.println("<div id='header'>");
        out.println("<h1 id='logo-text'>49ers<span class='gray'>Student</span></h1>");
        out.println("<h2 id='slogan'><a href='http://uncc.edu/'><img src='images/UNCCcrown.gif' width='70' height='41' alt='firefox' class='align-right' /></h2>");
        out.println("<div id='header-tabs'>");
        out.println("<ul>");
        out.println("<li id='current'><a href='LoginPage'><span>Home</span></a></li>");
        out.println("<li><a href='ClassOffering'><span>Classed Offered</span></a></li>");
        out.println("<li><a href='LoginPage'><span>Detail Schedule</span></a></li>");
        out.println("<li><a href='FacultyPage'><span>Faculty</span></a></li>");
        out.println("<li><a href='LoginPage'><span>Enrollment</span></a></li>");
        out.println("<li><a href='LoginPage'><span>Support</span></a></li>	");
        out.println("</ul>");
        out.println("</div>");
        out.println("</div>");
        out.println("<div id='content-wrap'>");
        out.println("<div id='main'>");
        out.println("<a name='TemplateInfo'></a>");
        out.println("<h1>Enter your Information</h1>");
        out.println("<h3>Student Form</h3>");
        out.println("<form action='ClassOffering' method = 'post'>");
        out.println("<p>");
        out.println("<label>Social Security Number</label>");
        out.println("<input name='SSN' value=' ' type='text' size='30' />");
        out.println("<label>First Name</label>");
        out.println("<input name='FIRSTNAME' value=' 'type= 'text' size= '30' />");
        out.println("<label>Last Name</label>");
        out.println("<input name='LASTNAME'  value='  'type= 'text' size='30' />");
        out.println("<label> Create Your User Name</label>");
        out.println("<input name='USERNAME'value='  'type= 'text' size='30' />");
        out.println("<label>Create Your Password</label>");
        out.println("<input name='PASSWORD' value='password 'type= 'text' size= '30' />");
        out.println("<label>City</label>");
        out.println("<input name='CITY' value='  ' type= 'text' size='30' />");
        out.println("<label>Major</label>");
        out.println("<input name='MAJOR' value='  ' type= 'text' size='30' />");
        out.println("<label>Minor</label>");
        out.println("<input name= 'MINOR' value='   ' type='text' size='30' />");
        out.println("<label>State</label>");
        out.println("<input name='STATE' value='  ' type='text' size='30' />");
        out.println("<label>Zip</label>");
        out.println("<input name='ZIP' value=' ' type='text' size='30'/>");
        out.println("<input class='button' type='submit' />");
        out.println("</p>");
        out.println("</form>");
        out.println("<br />");
        out.println("</div>");
        out.println("<div id='sidebar'>");
        out.println("<h1>Sidebar Menu</h1>");
        out.println("<ul class='sidemenu'>");
        out.println("<li><a href='LoginPage'>Home</a></li>");
        out.println("<li><a href='ClassOffering'>Classes Offered</a></li>");
        out.println("<li><a href='FacultyPage'>Faculty Support</a></li>");
        out.println("<li><a href=''>Your Schdele</a></li>");
        out.println("<li><a href=''>Enrollment</a></li>");
        out.println("</ul>");
        out.println("<h1>Search</h1>");
        out.println("<form method='post' id='search' action='#'>");
        out.println("<p>");
        out.println("<input name='search_query' class='textbox' type='text' /> ");
        out.println("<input name='search' class='searchbutton' value='Search' type='submit' />");
        out.println("</p>");
        out.println("</form>");
        out.println("<h1>Wise Words</h1>");
        out.println("<p>What do we put here</p>");
        out.println("<p class='align-right'>- Aristotle</p>");
        out.println("<h1>Support Styleshout</h1>");
        out.println("<p><a href='http://www.google.com/'>google</a> </p>");
        out.println("</div>");
        out.println("</div>");
        out.println("<div id='footer'>");
        out.println("<span id='footer-left'>&copy; 2009 <strong>Group 8</strong> ");
        out.println("Design by: <strong><a href='index.html'>Mario</a></strong> ");
        out.println("Valid: <a href='http://validator.w3.org/check?uri=referer'>XHTML</a>");
        out.println("<a href='http://jigsaw.w3.org/css-validator/check/referer'>CSS</a>");
        out.println("</span>");
        out.println("<span id='footer-right'>");
        out.println("<a href='http://www.uncc.edu'>UNCC Home</a> | ");
        out.println("<a href='https://selfservice.uncc.edu/pls/BANPROD/twbkwbis.P_GenMenu?name=homepage'>" +
                "Banner Self Service </a>");
        out.println("</span>");
        out.println("</div>");
        out.println("</div>");
 
 
 
 
       }
     protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
            String SSN = request.getParameter("SSN");
            String FIRSTNAME = request.getParameter("FIRSTNAME");
            String LASTNAME = request.getParameter("LASTNAME");
            String USERNAME = request.getParameter("USERNAME");
            String PASSWORD = request.getParameter("PASSWORD");
            String CITY = request.getParameter("CITY");
            String MAJOR = request.getParameter("MAJOR");
            String MINOR = request.getParameter("MINOR");
            String STATE = request.getParameter("STATE");
            String ZIP = request.getParameter("ZIP");
 
 
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
 
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:xe[uncc on UNCC]",
                    "UNCC",
                    "UNCC");
 
            Statement stmt = (Statement)conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT COUNT (*) FROM STUDENT WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");
             if (rs.next()==true)
             {
               stmt.executeUpdate("UPDATE STUDENT SET StdSNN = '"+SSN+"'AND StdFirstName ='"+FIRSTNAME+"'AND StdLastName='"+LASTNAME+"' AND StdUserName='"+USERNAME+"' StdPassword='"+PASSWORD+"' AND StdCity='"+CITY+"' AND StdMajor='"+MAJOR +"' AND StdMinor ='"+MINOR+"'AND StdState='"+STATE+"' StdZip='"+ZIP+"' WHERE StdFirstName = '"+FIRSTNAME+"' AND StdLastName = '"+LASTNAME+"'");
             } else
             { stmt.executeUpdate ("INSERT INTO STUDENT (StdSSN, StdFirstName, StdLastName, StdUserName, StdPassword, StdCity, StdMajor, StdMinor, StdState, StdZip, StdClass, StdAdvisor, StdGPA VALUES ('"+SSN+"', '"+FIRSTNAME+"', '"+LASTNAME+"', '"+USERNAME+"','"+PASSWORD+"','"+CITY+"','"+MAJOR +"','"+MINOR +"','"+STATE+"','"+ZIP+"', '', '', '')");
                }
 
 
            } catch(SQLException e) {
                out.println("SQLException: " + e.getMessage() + "<BR>");
                 while((e = e.getNextException()) != null)
                 out.println(e.getMessage() + "<BR>");
            } catch(ClassNotFoundException e) {
               out.println("ClassNotFoundException: " + e.getMessage() + "<BR>");
            } finally {
              //Clean up resources, close the connection.
              if(conn != null) {
                  try {
                     conn.close();
                 } catch (Exception ignored) {
                 }
 
                }
 
            }
            out.println("</body> ");
            out.println("</html>");
            out.close();
        }

Open in new window

0
 

Author Comment

by:percivalevans
ID: 24061143
I'm new to adding a Java GUI interface to databases as you can see. The servlets that I'm using are from my partner for another class. Strange, he has on some servelts with the doGet and doPost like my original and like the one Derek suggested. Derek, I did copy and paste your servelt and corrected the errors. However when I tested I tested, no data entered the table. Maybe you guys have another suggest or maybe an ideal tutorial online? If I can get one to work, I can figure out my issue with one I think. No matter what thanks for your help and effort!
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 3

Expert Comment

by:kingsob
ID: 24065585
are you sure the action is correct for your form? it should post to whatever your servlet is called in your web.xml file

here is a simple example  
http://www.roseindia.net/servlets/ServletUserEnquiryForm.shtml
they have the html in a seperate file... however you could easily add this to the doGet method


Derek
0
 

Author Comment

by:percivalevans
ID: 24068296
I think we are almost there. I changed the post name and I get this error:

"SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: localhost:1521:xe[uncc on UNCC]"

I did a tsnping to my database and got a "Failed to resolved name" The "SID" is "xe[uncc on UNCC]" In netbeans 6.5, I have a connection to my database. I see the tables using the same connection descriptor in netbeans.
0
 
LVL 3

Assisted Solution

by:kingsob
kingsob earned 250 total points
ID: 24084026
im not familiar with oracle, so I don't think i'm going be able to help you with this problem

i looked around, and found this

http://www.adp-gmbh.ch/ora/misc/identifiers.html#sid

maybe it will help


Derek
0
 

Accepted Solution

by:
percivalevans earned 0 total points
ID: 24091822
I thank you for helping along with the servlet. Just in case anyone is reviewing this for troubleshooting, my issue was not fixed however I now know that my issue is with Oracle XE.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mockito example issue 8 90
maven project error 5 80
eclipse shortcuts 9 68
jmss example java 2 33
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question