Program Statement

Create a table which should contain at least the following fields: name, password, email-id, phone number. Write a Servlet/JSP to connect to that database and extract data from the tables and display them. Insert the details of the users who register with the website, whenever a new user clicks the submit button in the registration page.

The Real SQL + Servlet Code (MySQL + Tomcat)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    phone VARCHAR(15)
);

@WebServlet("/register")
public class RegisterServlet extends HttpServlet {
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        String name = req.getParameter("name");
        String password = req.getParameter("password");
        String email = req.getParameter("email");
        String phone = req.getParameter("phone");

        try (Connection con = DriverManager.getConnection(
                 "jdbc:mysql://localhost:3306/webtechlab", "root", "password")) {
            PreparedStatement ps = con.prepareStatement(
                "INSERT INTO users (name, password, email, phone) VALUES (?, ?, ?, ?)");
            ps.setString(1, name);
            ps.setString(2, password);
            ps.setString(3, email);
            ps.setString(4, phone);
            ps.executeUpdate();
            resp.sendRedirect("display");
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
}

@WebServlet("/display")
public class DisplayServlet extends HttpServlet {
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        resp.setContentType("text/html");
        PrintWriter out = resp.getWriter();
        out.println("<table border='1'><tr><th>Name</th><th>Email</th><th>Phone</th></tr>");
        try (Connection con = DriverManager.getConnection(
                 "jdbc:mysql://localhost:3306/webtechlab", "root", "password")) {
            ResultSet rs = con.createStatement().executeQuery("SELECT name, email, phone FROM users");
            while (rs.next()) {
                out.println("<tr><td>" + rs.getString("name") + "</td><td>"
                        + rs.getString("email") + "</td><td>" + rs.getString("phone") + "</td></tr>");
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
        out.println("</table>");
    }
}

Browser Simulation

The demo below reproduces the same flow client-side: a registration form inserts a row, and a table below extracts and displays every registered user — using localStorage in place of the MySQL table so it persists across runs, right here in the browser.

CO Mapping

CO5