Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – JDBC connection object returns null in Servlet/Web

I’m stuck with a servlet problem – my database connection is turning up null. The weird part is, the same database code works fine in another class. I’ve double-checked my configuration, loaded the JDBC driver, and tested it on Tomcat, but the connection in the servlet stays stubbornly null.

Type Exception Report

Message Cannot invoke "java.sql.Connection.prepareStatement(String)" because "connection" 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 "connection" is null

SingletonConnection.java:

    public class SingletonConnection {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/CATALOGUE";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";
    private static Connection connection;
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
    
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        return connection;
    }
}

ProduitdaoImp.java

    public class ProduitdaoImp implements IProduitdao {
    @Override
    public List<Produit> chercher(String mc) {
        Connection connection = SingletonConnection.getConnection();
    
        List<Produit> produits = new ArrayList<Produit>();
        try {
    
    PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM PRODUITS                                                                      WHERE DESIGNATION LIKE ?");
            preparedStatement.setString(1, "%" + mc + "%");
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Produit p = new Produit();
                p.setId(rs.getInt("ID"));
                p.setPrix(rs.getDouble("PRIX"));
                p.setQuantite(rs.getLong("QUANTITE"));
                p.setDesignation(rs.getString("DESIGNATION"));
                produits.add(p);
    
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return produits;
    }  
}

ControleurServlet.java

public class ControleurServlet extends HttpServlet {
    public IProduitdao iProduitdao;
    
    @Override
    public void init() throws ServletException {
        iProduitdao = new ProduitdaoImp();
    }
    
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws   ServletException, IOException {
        String path = request.getServletPath();
        if (path.equals("/index.do")) {
            request.getRequestDispatcher("Produits.jsp").forward(request, response);
    
        } else if (path.equals("/chercher.do")) {
            String mot = request.getParameter("motcle");
    
            ProduitModel produitModel = new ProduitModel();
    
            produitModel.setMotCle(mot);
    
            List<Produit> produits = iProduitdao.chercher(mot);
            produitModel.setProduits(produits);
            request.setAttribute("model", produitModel);
            request.getRequestDispatcher("Produits.jsp").forward(request, response);
    
        }
    
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    }
}

Produits.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
    <title>Produits</title>
    <link rel="stylesheet" type="text/css" href="css/bootstrap.css">
</head>
<body>
<p></p>
<div>
    <div>
        <div > Recherche des produits</div>
        <div ></div>
        <form method="get" action="chercher.do">
        <label>Mot CLe</label>
            <input type="text" name="motcle"/>

        <button type="submit">Chercher</button>
        </form>
        <table>
            <tr>
            <th>ID</th><th>DESIGNATIONNs</th><th>PRIX</th><th>QUANTITE</th>
            </tr>
            <c:forEach items="${model.produits}" var="p">
                <tr>
                    <td>${p.id}</td>
                    <td>${p.designation}</td>
                    <td>${p.prix}</td>
                    <td>${p.quantite}</td>
                </tr>
            </c:forEach>
        </table>
    </div>
</div>
</body>
</html>
Tags:

2

Answers


  1. 0

    A database connection is like a facial tissue… Grab a fresh one, use it, and dispose. Never use a single connection simultaneously within or across threads. Your singleton approach is ill-advised, and doomed to fail in confusing ways.

    Store your connection credentials details, such as username, password, server address, port number, all in a DataSource object. Keep that DataSource object available while your app runs. Ask that cached DataSource object for a new connection each time your app needs access to the database.

    While learning, you can hard-code your connection credentials. In real work, we externalize those details so they can be changed without needing to recompile the app. Use JNDI to retrieve the credentials as a DataSource object. Tomcat has a feature where it can produce the DataSource object in a JNDI-compliant manner.

    By the way, Class.forName to load a JDBC driver has been unnecessary for many years. In modern Java, JDBC drivers auto-load via SPI.

    I suspect you are using an outdated and misinformed tutorial on JDBC.

    To learn more, search Stack Overflow. You will find much info including full working example apps, some authored by me.

    Tip: Use static reluctantly. It’s not object-oriented. Frequent use may indicate poor design.

    Login or Signup to reply.
  2. 0

    @Basil and others have given you lots of good advice, and if you had followed it you wouldn’t have gotten into this mess.

    But what is actually going wrong?

    If you are getting NPEs when trying to use the connection (supposedly) returned by getConnection():

    • that means that the private connection field is null,
    • that means that the static init block hasn’t assigned a value to connection, and
    • that means that an exception must have been thrown and caught in the try … catch, and the e.printStackTrace() output must have gone to Tomcat’s standard output.

    So, look in the server logs (including the "catalina.out" output file) for the output from the e.printStackTrace() call. It should be a stacktrace for a ClassNotFoundException or an SQLException. And it will have happened before the NPE. That stacktrace will tell you what actually went wrong to cause the NPE.

    But follow Basil’s advice! And also, log your exceptions rather than using printStackTrace().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search