Search code examples
mysqljsptomcatjsp-tagssqlexception

Get SQLexception error before giving the user input in jsp page

I like to create a registration form where user can register his or her data and the given data will be saved on a database. I created a database HospitalM using mysql workbench 8.0. I ran the project, the jsp file for form creation on Tomcat server. The pid is a primary key in the table called patient in the database HospitalM.

When I run the form on server a form generated but the form did not wait for input data and throw a message SQLException caught: Column 'pid' cannot be null.

I will share my code and the form in the below. Can any one give me some hints why it did not work?

JSP code: (name of the jsp file insert.jsp)

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>

<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
 <table>
<tr><td>Pid</td><td><input type="text" name="pid" required /></td></tr>
<tr><td>Name</td><td><input type="text" name="patientname" required /></td></tr>
<tr><td>Address</td><td><input type="text" name="patientaddress" ></td></tr>
<tr><td>Phone number</td><td><input type="number" name="Ph" required /></td></tr>
<tr><td>Email</td><td><input type="email" name="email" required /></td></tr>
<tr><td>Type</td><td><input type="text" name="patienttype" ></td></tr>
<tr><td></td><td><input type="submit" name="submit" value="register"></td></tr>

</table>

<%
  
  try {
    
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/hospitalm","root","1234");
    out.println ("database successfully opened.");
    String sql = "INSERT INTO patient (pid, pname, address, phone, email_id,ptype) values (?, ?, ?,?,?,?)";
    PreparedStatement statement = con.prepareStatement(sql);
    statement.setString(1, request.getParameter("pid"));
    statement.setString(2, request.getParameter("pname"));
    statement.setString(3, request.getParameter("address"));
    statement.setString(4, request.getParameter("phone"));
    statement.setString(5, request.getParameter("email_id"));
    statement.setString(6,request.getParameter("ptype"));
    int row = statement.executeUpdate();
    if (row > 0) {
        System.out.println("One row inserted.");
    }
  }
  catch(SQLException e) {
    out.println("SQLException caught: " +e.getMessage());
  }


%>
</body>
</html>

The sql query for patient table:

create table patient(pid varchar(10),
                    pname varchar(40) not null,
                    address varchar(10),
                    phone numeric(10,0) not null,
                    email_id varchar(20) not null,
                    ptype varchar(2),
                    primary key(pid),
                    check(email_id like '%_@__%.__%'));

The output that I got

enter image description here

Solution

  • The problem here is that when you first access the page, all of that code gets executed, but there was no request to process.

    So, during the first access, all of those request parameters are null.

    As suggested by Piotr, you could use the request method to get for whether it's a GET or a POST, and only execute the code on the POST.

    The proper answer to that goes much deeper into how these kinds of things are architected along with the page workflow, but that's a much deeper topic that to go in to here.

    For now, the simple issue is that on the first access, there are no parameters, so you're essentially trying to do:

    statement.setString(1, null);

    Which is illegal (to set a column to an explicit SQL NULL value in JDBC, you use the statement.setNull(1) method).