NullPointerException in Java/MySQL application

Question

I'm building a Movie Rating CRUD application and whenever I click on the 'Categories' link, which is supposed to access the database and display the categories and the list of movies available, I receive an error message:

java.lang.NullPointerException
Database.ConnectionPool.freeConnection(ConnectionPool.java:40)
Database.ItemDB.getItems(ItemDB.java:59)
Controllers.CatalogController.processRequest(CatalogController.java:33)
Controllers.CatalogController.doGet(CatalogController.java:86)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

ItemDB.java

public static ArrayList<Item> getItems() {
    
    ArrayList<Item> items = new ArrayList<Item>();
    
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    
    String query = "SELECT * FROM Item";
            
    try {
        ps = connection.prepareStatement(query);
        rs = ps.executeQuery();
        
        while(rs.next()) {
            Item item = new Item();
            item.setItemCode(rs.getString("itemCode"));
            item.setItemName(rs.getString("itemName"));
            item.setCatalogCategory(rs.getString("CatalogCategory"));
            item.setItemDescription(rs.getString("descript"));
            item.setItemRating(rs.getString("itemRating"));
            item.setImageUrl(rs.getString("imageURL"));
            items.add(item);
        }
        return items;
        
    } catch (SQLException e) {
        System.out.println(e);
        return null;
    } finally {
        DBUtil.closeResultSet(rs);
        DBUtil.closePreparedStatement(ps);
        pool.freeConnection(connection);
    }
}

ConnectionPool.java

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

Context.xml

<Resource 
          auth="Container" 
          driverClassName="com.mysql.jdbc.Driver" 
          logAbandoned="true" 
          maxActive="100" maxIdle="30" maxWait="10000" 
          name="jdbc/MovieThoughts"  removeAbandoned="true" 
          removeAbandonedTimeout="60" type="javax.sql.DataSource" 
          url="jdbc:mysql://localhost:3306/root" 
          username="MovieThoughts" password=[my_password] />

I've tested the connection in my MySQL workbench and it can establish the connection in there.

You should be using try-with-resources anyway. – chrylis -cautiouslyoptimistic- Jul 12 '20 at 00:45 — chrylis -cautiouslyoptimistic-, Jul 12 '20 at 00:45

score 1 · Accepted Answer · answered Jul 12 '20 at 01:28

By looking at it, I'd say pool.getConnection() returns null and that causes an NPE at connection.prepareStatement(query), which is swallowed when the finally block throws its own NPE.

In general, you should try to write your finally blocks so that they can't throw exceptions, both because of the swallowing above and because if an exception occurs part of the finally code might be skipped.

eg. in your code if DBUtil.closeResultSet(rs) throws (which might very well be impossible, depending on how DBUtil is implemented), the following two statements will be skipped, possibly resulting in leaking a connection.

Since Java 7 (IIRC), the preferred way to deal with all this is the try-with-resource construct (see https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html), which basically deals with the boilerplate for you.

score -1 · Answer 2 · answered Jul 12 '20 at 01:13

Hard to tell usually i got that erreur if i wrongly type password

did you try another way than using the connection pool by using the dataSource directly. Maybe something wrongly configure with the connection pool.

Context initCtx = new InitialContext();
Context ctx = (Context) initCtx.lookup("java:comp/env");

DataSource ds = (DataSource) ctx.lookup("jdbc/MovieThoughts");

Connection conn = ds.getConnection();

score -1 · Answer 3 · answered Jul 12 '20 at 02:55

-1

Try disable maxActive="100" maxIdle="30" maxWait="10000"

answered Jul 12 '20 at 02:55

Mattyas yahya

53
9

NullPointerException in Java/MySQL application

ItemDB.java

ConnectionPool.java

Context.xml

3 Answers3