Cannot invoke "java.sql.Connection.close()" because "c" is null

Question

I'm trying to make a connection pool in my project on Java Servlets. My code is given below:

ConnectionPool.java

package dbconn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ConnectionPool {
    private static ConnectionPool pool = null;
    private static DataSource dataSource = null;
    private ConnectionPool() {
        try {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/ita2");
        } catch (NamingException e) {
            System.out.println(e);
        }
    }
    public static synchronized ConnectionPool getInstance() {
        if (pool == null) {
            pool = new ConnectionPool();
        }
        return pool;
    }
    public Connection getConnection() {
        Connection con = null;
        String dbName = "ita2";
        String userName = "root";
        String password = "";
        String hostname = "localhost";
        String port = "3306";
        String jdbcUrl = "jdbc:mysql://" + hostname + ":" + port + "/" + dbName + "?user=" + userName+ "&password=" + password; 

        try
        {
            try
            {
                Class.forName("com.mysql.jdbc.Driver");
            }
            catch (ClassNotFoundException e)
            {
                e.printStackTrace();
            }
            con = DriverManager.getConnection(jdbcUrl);
            System.out.println("Post establishing a DB connection - "+con);
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }
    public void freeConnection(Connection c) {
        try {
            c.close();
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

StudentDB.java

public class StudentDB {
    public static int insert(Student user) {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        

        String query
                = "INSERT INTO Student (id, fname, lname, uname, email, password, salt) "
                + "VALUES (?, ?, ?, ?, ?, ?,?)";
        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, user.getSid());
            ps.setString(2, user.getFirstName());
            ps.setString(3, user.getLastName());
            ps.setString(4, user.getUsername());
            ps.setString(5, user.getEmail());
            ps.setString(6, user.getPassword());
            ps.setString(7,user.getSalt());
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            return 0;
        } finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }
}

StudentRegister.java

import java.io.IOException;
import java.security.NoSuchAlgorithmException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

@WebServlet("/StudentRegister")
public class StudentRegister extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String url = "/index.html";

        // get current action
        String action = request.getParameter("action");

        if (action == null) {
            action = "join";  // default action
        }

        // perform action and set URL to appropriate page
        if (action.equals("join")) {
            url = "/index.jsp";    // the "join" page
        }
        else if (action.equals("add")) {
            // get parameters from the request
            String firstName = request.getParameter("firstname");
            String lastName = request.getParameter("lastname");
            String username = request.getParameter("username");
            String password = request.getParameter("password");
            String email = request.getParameter("email");
            String sid = request.getParameter("sid");
            HttpSession session = request.getSession(true);
            session.setAttribute("studentid",sid);
            System.out.println("student id is "+ session.getAttribute("studentid"));

            // check strength requirements
            String message1;
            try {
                PasswordUtil.checkPasswordStrength(password);
                message1 = "";
            } catch (Exception e) {
                message1 = e.getMessage();
            }
            request.setAttribute("message", message1);

            // hash and salt password
            String hashedPassword;
            String salt = "";
            String saltedAndHashedPassword;
            try {
                hashedPassword = PasswordUtil.hashPassword(password);
                salt = PasswordUtil.getSalt();
                saltedAndHashedPassword = PasswordUtil.hashAndSaltPassword(password);

            } catch (NoSuchAlgorithmException ex) {
                hashedPassword = ex.getMessage();
                saltedAndHashedPassword = ex.getMessage();
            }
            request.setAttribute("HashedPassword", hashedPassword);
            request.setAttribute("Salt", salt);
            request.setAttribute("SaltedAndHashedPassword", saltedAndHashedPassword);

            // store data in User object
            Student user = new Student(sid, username, saltedAndHashedPassword, firstName, lastName, email,salt);

            System.out.println("Salt before saving " + user);

            // validate the parameters
            String message;

            {    message = "";
            url = "/thanks.jsp";
            StudentDB.insert(user);
            }
            request.setAttribute("user", user);
            request.setAttribute("message", message);
        }
        getServletContext()
        .getRequestDispatcher(url)
        .forward(request, response);
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        doPost(request, response);
    }
}

While running the StudentRegister servlet, I'm encountering the error given in the title above, and it has to do with the freeConnection() method in the ConnectionPool.java program. Is it due to a failed lookup/non-existent lookup link? It should be noted that I'm using a local WAMP server for my purpose. Any help would be appreciated!

EDIT
Here is the entire stack trace:

java.lang.NullPointerException: Cannot invoke "java.sql.Connection.prepareStatement(String)" because "connection" is null
    at dbconn.StudentDB.insert(StudentDB.java:18)
    at student.StudentRegister.doPost(StudentRegister.java:83)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:831)

Could you share the stacktrace? You should have more logs in your app. The cause could be different. I see a lot of 'exception cases' which let the application continues its run. — sigur, Dec 03 '21 at 17:04
Stack trace doesn't match title of post - although you're likely getting both (because of the `finally`). The implementation of `ConnectionPool.getConnection()` _can_ return null which is ignored by `StudentDB`. Look at logs for more info... — , Dec 03 '21 at 17:11
I expect at least a 'suppressed' exception at DriverManager.getConnection(jdbcUrl), where you print the stack trace. I suggest you to take a look at https://stackoverflow.com/questions/2839321/connect-java-to-a-mysql-database — sigur, Dec 03 '21 at 18:02
Don't use such methods to close resources, use [try-with-resources](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html). Also your `getConnection()` method is fundamentally flawed in that it returns `null` when an exception occurs when creating the connection. Don't return null, either just throw the `SQLException` or wrap it in your own exception to propagate the error. — Mark Rotteveel, Dec 03 '21 at 18:09

score 0 · Answer 1 · answered Dec 03 '21 at 16:58

0

Check that the connection isn't null before you attempt to close it.

public void freeConnection(Connection c) {
    if (c == null) {
        return;
    }
    try {
        c.close();
    } catch (SQLException e) {
        System.out.println(e);
    }
}

answered Dec 03 '21 at 16:58

Elliott Frisch

198,278
20
158
249

But won't that just compound the problem? In my case I get ```Cannot invoke "java.sql.Connection.prepareStatement(String)" because "connection" is null``` from the StudentDB.java program – Ramon Zarate Dec 03 '21 at 17:02
@RamonZarate The exception is coming from the close call. This will fix that. Won't fix any bugs you haven't mentioned in `getConnection()`; but it is odd that `getConnection()` doesn't invoke `pool.getConnection()` – Elliott Frisch Dec 03 '21 at 17:04
Well, that is because I have invoked it someplace else; in the StudentDB.java program – Ramon Zarate Dec 03 '21 at 17:10

Cannot invoke "java.sql.Connection.close()" because "c" is null

1 Answers1