Cannot get a connection using JDBC

Question

I have tried to build a web application using JAVA.

This is the code of connection between JDBC and MySQL server. The code did not run.

import java.sql.*;

public class RegisterDao {
    
    private String dburl = "jdbc:mysql://localhost:3306/hospitalm";
    private String dbuname = "root";
    private String dbpassword = "1234";
    private String dbdriver = "com.mysql.jdbc.Driver";
    
    public void loadDriver(String dbDriver) {
        try {
            Class.forName(dbDriver);
        } catch(ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    
    public Connection getConnection() {
        Connection con = null;
        try {
            con = DriverManager.getConnection(dburl, dbuname, dbpassword);
            System.out.println("Connection was established");
        } catch(SQLException e){
            e.printStackTrace();    
        }
        
        return con;
    }
    
    public String insert(Member member) {
        loadDriver(dbdriver);

        Connection con = getConnection();
        String result = "data entered successfully";
        String sql = "insert into hospitalm.patient values(?, ?, ?, ?, ?)";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, member.getPid());
            ps.setString(2, member.getName());
            ps.setString(3, member.getAddress());
            
            ps.setString(4, member.getEmail());
            ps.setString(5, member.getType());
            ps.executeUpdate();
        } catch (SQLException e){
            e.printStackTrace();
            result = "Data not entered successfully";
        }
    
        return result;
    }
    
}

Can anyone let me know what is the error on the above code?

The SQL table is like:

CREATE TABLE patient(
    pid VARCHAR(10),
    pname VARCHAR(40) NOT NULL,
    address VARCHAR(10),
    email_id VARCHAR(20) NOT NULL,
    ptype VARCHAR(2),
    PRMARY KEY(pid),
    CHECK (email_id like '%_@__%.__%'),
    CHECK (ptype in ('G','S'))
);

I got HTTP Status 500 Internal Server error.

Type: Exception Report

Message: Cannot invoke "java.sql.Connection.prepareStatement(String)" because "con" is null

Description: The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception:

java.lang.NullPointerException: Cannot invoke "java.sql.Connection.prepareStatement(String)" because "con" is null
  RegisterDao.insert(RegisterDao.java:47)
  Register.doPost(Register.java:47)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
  org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

Note The full stack trace of the root cause is available in the server logs.

The servlet code that I added:

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        String Pid = request.getParameter("pid");
        String Name = request.getParameter("patientname");
        String Address = request.getParameter("patientaddress");
        
        String Email = request.getParameter("email");
        String Type = request.getParameter("patienttype");
        
        Member member = new Member(Pid, Name, Address, Email, Type);
        RegisterDao rDao= new RegisterDao();
        String result = rDao.insert(member);
        response.getWriter().print(result);
        
        
        //doGet(request, response);
    }

}

What are the possible causes of getting this type of error?

I suspect the issue is that `DriverManager.getConnection()` is throwing an exception which is being caught in the `catch` block, but you're not seeing the error because `e.printStackTrace()` is not logging it to where you expect. As a result the `getConnection()` method returns `null` and breaks calling code. Try re-raising the `SQLException` rather than printing the stacktrace. — Will Keeling, Nov 26 '21 at 09:21
The error message clearly states the reason: `because "con" is null`. So for some reason your `getConnection()` helper method returns `null`. For the novice users it would be useful to read the official Oracle's tutorial ["JDBC Database Access"](https://docs.oracle.com/javase/tutorial/jdbc/index.html) for step by step instructions etc. As a side note: loading the class driver (`Class.forName(dbDriver)`) is [not required](https://stackoverflow.com/a/7662950/814702) already since [Java 6](https://en.wikipedia.org/wiki/Java_(programming_language)#Versions). — informatik01, Nov 26 '21 at 09:30
If you are doing web application, you should really utilize `javax.sql.DataSource` and configure your datasource externally to your application. Using a `DriverManager` is not really recommended approach in web apps. Here is sample configuration for open source OpenLiberty server - https://openliberty.io/docs/21.0.0.4/relational-database-connections-JDBC.html — Gas, Nov 26 '21 at 09:44
Replace all occurrences of `e.printStackTrace();` by `throw new RuntimeException(e);` (or preferably `throw new YourDaoException(e);`) for better and sooner error reporting. Right now you're plainly continuing the code logic with null variables in case of exceptions instead of immediately aborting the code logic. Most probably it's just a `ClassNotFoundException` on the JDBC driver, which is a common starter's mistake. See abovelinked dupes to get started as to connecting to a DB in a Servlet based application. — BalusC, Nov 26 '21 at 10:34
@WillKeeling Can you please take a look https://stackoverflow.com/questions/70130366/taking-input-variables-from-a-tablehtml-and-supply-the-values-as-a-variable-na — Encipher, Nov 26 '21 at 21:56

Cannot get a connection using JDBC

0 Answers0

Linked