Taking input variables from a table(HTML) and supply the values as a variable name for a database table

I would like to create a form for a patient database system. Then I will give some input data to each textbox of the form and the data will be stored in a MySQL database.

I wrote a JSP file as follows

<%@ 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, Pid);
    statement.setString(2, Name);
    statement.setString(3, "Forrest");
    statement.setDouble(4, 664208158);
    statement.setString(5, "[email protected]");
    statement.setString(6,"G");
    int row = statement.executeUpdate();
    if (row > 0) {
        System.out.println("One row inserted.");
    }
  }
  catch(SQLException e) {
    out.println("SQLException caught: " +e.getMessage());
  }


%>
</body>
</html>

This two lines throw error

    statement.setString(1, Pid);
    statement.setString(2, Name);

If I input some values like 'p101' for Pid and 'John' for Name then I can easily entered a record on the database hospitalM.

But if I try Pid and Name which are the variable names for 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>

I got error.

How could I solve this?

Solution

JSP does not automagically bind request parameters in to Java variables, which is what you're trying to do here.

In order to get the parameters, you need to query the request object directly. Something akin to:

statement.setString(1, request.getParameter("Pid"));
statement.setString(2, request.getParameter("Name"));

Addenda:

Regarding double (or other non-strings), there's two things you can do.

One, you can convert them on the fly:

statement.setDouble(4, Double.parseDouble(requests.getParameter("phone")));

Second, you can rely on the SQL driver to do it for you.

statement.setString(4, requests.getParameter("phone"));

The "setXXX" in JDBC is based on the value that you're setting, NOT the data type of the column in the database. Inevitably, most of the time, whatever you set in the JDBC statement is going to be turned in to a string before being shipped up to the DB anyway. So, you can just use setString and be done with it and let the driver/db figure it out.

Either way, if you send in badly formatted data (i.e. "Tom" for the phone number), you're going to get an exception, either from the Double.parseDouble or when the DB tries to convert it during the insert. But that's a separate issue from simply setting parameters.