Fill text fields on a web page using glassfish/mysql

[hornet7288]

I am working on a project where I need to be able to create a record, update a record, clear a web form and fill the text fields on the webpage when an employee ID number is entered and the "view" button is pressed. I have the other 3 functions working, but can't seem to figure out the last part. Any pointers would be great!

Thanks!

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

public class Exercise extends HttpServlet {
// Use a prepared statement to store a student into the database

    private PreparedStatement pstmt;

    /**
     * Initialize variables
     *
     * @throws javax.servlet.ServletException
     */
    @Override
    public void init() throws ServletException {
        initializeJdbc();
    }

    /**
     * Process the HTTP Post request
     *
     * @param request
     * @param response
     * @throws javax.servlet.ServletException
     * @throws java.io.IOException
     */
    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String ID;
        String lastName;
        String firstName;
        String mi;
        String address;
        String city;
        String state;
        String telephone;

        if (request.getParameter("insert") != null) {
            // Invoke FirstServlet's job here.
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
            // Obtain parameters from the client
            ID = request.getParameter("ID");
            lastName = request.getParameter("lastName");
            firstName = request.getParameter("firstName");
            mi = request.getParameter("mi");
            address = request.getParameter("address");
            city = request.getParameter("city");
            state = request.getParameter("state");
            telephone = request.getParameter("telephone");
            try {
                storeStudent(ID, lastName, firstName, mi, address, city, state, telephone);
                out.println(firstName + " " + lastName + " is now registered in the database");

            } catch (SQLException ex) {
                out.println("Error: " + ex.getMessage());
            } finally {
                out.close(); // Close stream
            }
            ///////////////////////////////////////////////
        } else if (request.getParameter("view") != null) {
            //Initialize the database
            init();
            PrintWriter out = response.getWriter();
            out.println("view");
            //////////////////////////////////////////////
        } else if (request.getParameter("update") != null) {
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
            // Obtain parameters from the client
            ID = request.getParameter("ID");
            lastName = request.getParameter("lastName");
            firstName = request.getParameter("firstName");
            mi = request.getParameter("mi");
            address = request.getParameter("address");
            city = request.getParameter("city");
            state = request.getParameter("state");
            telephone = request.getParameter("telephone");

            try {
                //Loading the driver
                Class.forName("com.mysql.jdbc.Driver");
                System.out.println("Driver loaded");
                //Start connection
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost", "user", "pass");
                System.out.println("Database Connected");
                //Statement to delete the tuple before it is updated
                pstmt = conn.prepareStatement("delete from staffinfo where id = " + ID + ";");
                pstmt.executeUpdate();
            } catch (ClassNotFoundException | SQLException ex) {
            }
            //Write the updated information back into the tuple that was deleted
            try {
                init();
                storeStudent(ID, lastName, firstName, mi, address, city, state, telephone);
                out.println(firstName + " " + lastName + " has been updated in the database");
                

            } catch (SQLException ex) {
                out.println("Error: " + ex.getMessage());
            } finally {
                out.close(); // Close stream
            }
        }
    }

    /**
     * Initialize database connection
     */
    private void initializeJdbc() {
        try {
// Load the JDBC driver
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded");
// Establish a connection
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost", "user", "pass");
            System.out.println("Database connected");
// Create a Statement
            pstmt = conn.prepareStatement("insert into staffinfo "
                    + "(ID, lastName, firstName, mi, address, city, state, telephone) "
                    + "values (?, ?, ?, ?, ?, ?, ?, ?)");
        } catch (ClassNotFoundException | SQLException ex) {
        }
    }

    /**
     * Store a student record to the database
     */
    private void storeStudent(String ID, String lastName,
            String firstName, String mi, String address, String city,
            String state, String telephone) throws SQLException {
        pstmt.setString(1, ID);
        pstmt.setString(2, lastName);
        pstmt.setString(3, firstName);
        pstmt.setString(4, mi);
        pstmt.setString(5, address);
        pstmt.setString(6, city);
        pstmt.setString(7, state);
        pstmt.setString(8, telephone);
        pstmt.executeUpdate();
    }
}

[Tolls]

First off, you have state in your servlet. That is, you have given your servlet an attribute that is not constant, the PreparedStatement.
Servlets should not have state as they won't work terribly well in the multi threaded environment that is a web application.

You're also not closing your connection and, at least in the view part of your code, you call init(), which creates another connection. Eventually you'll overload the database, either with too many connections or too many open cursors (active queries).

So, what I would suggest is throw away the init(), change the initializeJdbc to a simple getConnection method that returns a connection (you can skip Class.forname part, it's not needed), and break that doGet down into methods for each individual action, so insertStudent, viewStudent, updateStudent. Inside there you'll getConnection, do whatever SQL, and then close the connection...using a try with resources will make that a bit simpler.

So, that's the structure tidied up.

As for your view, the first step is to do a query to get the student. Can you write that bit?

[hornet7288]

I believe that I can figure that out. Thanks for all of the helpful pointers!