Receiving a NullPointerException from mysql.Statement.executeQuery

This problem happens only 1% of the time in an application I'm putting together at:
http://www.cleanriteincorporated.com/calculator.jsp

Every so often, when you're adding a room to the calculator, a NullPointerException is being thrown while looking up the pricing of services.  Is this a fault of my code or a fault of the setup on the backend?

My code is as follows:
...
            connect = null;
            stmt = null;
            rs = null;

            try
            {
                connect = dbc.getConnection();
                stmt = connect.createStatement();
                rs = stmt.executeQuery("SELECT * FROM pricing WHERE sq_ft = '" + sqFt + "'");

                if (rs.next())
                {

                    if (cleaning.equals("true"))
                    {
                        total += Double.parseDouble(rs.getString("cleaning"));
                    }

                    if (scotchgard.equals("true"))
                    {
                        total += Double.parseDouble(rs.getString("scotchgard"));
                    }

                    if (petOdor.equals("true"))
                    {
                        total += Double.parseDouble(rs.getString("pet_odor"));
                    }
                }
            }
            catch (SQLException e)
            {
                System.out.print(e.getMessage());
            }
            finally
            {
                dbc.closeCon(stmt);
            }
...

Here is the exception:
Location: /calculator.jsp
Internal Servlet Error:

javax.servlet.ServletException:
     at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:460)
     at calculator_27._jspService(calculator_27.java:665)
     at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java)
     at org.apache.tomcat.facade.ServletHandler.doService(ServletHandler.java:574)
     at org.apache.tomcat.core.Handler.invoke(Handler.java:322)
     at org.apache.tomcat.core.Handler.service(Handler.java:235)
     at org.apache.tomcat.facade.ServletHandler.service(ServletHandler.java:485)
     at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:917)
     at org.apache.tomcat.core.ContextManager.service(ContextManager.java:833)
     at org.apache.tomcat.modules.server.Ajp13Interceptor.processConnection(Ajp13Interceptor.java:341)
     at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:494)
     at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:516)
     at java.lang.Thread.run(Thread.java:479)
Root cause:
java.lang.NullPointerException:
     at org.gjt.mm.mysql.Statement.executeQuery(Statement.java:124)
     at com.cleanrite.calc.Pricing.getRoomTotal(Pricing.java:72)
     at com.cleanrite.calc.Action.add(Action.java:123)
     at com.cleanrite.calc.Action.doAction(Action.java:50)
     at calculator_27._jspService(calculator_27.java:154)
     at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java)
     at org.apache.tomcat.facade.ServletHandler.doService(ServletHandler.java:574)
     at org.apache.tomcat.core.Handler.invoke(Handler.java:322)
     at org.apache.tomcat.core.Handler.service(Handler.java:235)
     at org.apache.tomcat.facade.ServletHandler.service(ServletHandler.java:485)
     at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:917)
     at org.apache.tomcat.core.ContextManager.service(ContextManager.java:833)
     at org.apache.tomcat.modules.server.Ajp13Interceptor.processConnection(Ajp13Interceptor.java:341)
     at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:494)
     at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:516)
     at java.lang.Thread.run(Thread.java:479)

I don't believe this to be a fault of my code.  I'd like someone to tell me differently. :-)
LVL 1
FastBiscuitAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
tdisessaConnect With a Mentor Commented:
One Java fix would be to try a different MySQL driver, also.  As I said, the one you are using was
developed in 1999.  There should be some better ones out there now.

Though, this might be a problem with MySQL.

Is this a default install of MySQL?  Can you increase the max number of concurrent connections?
0
 
kiranhkCommented:
did u check out whether all the rows fetched have data for the all sqft condition given.

also which line is  com.cleanrite.calc.Pricing.getRoomTotal(Pricing.java:72)


I believe for some particular sqft there is no data in the column cleaning/scotchgard/pet_odor if indeed the null pointer is generated by this block of code.

0
 
Tommy BraasCommented:
Hi FastBiscuit,

Why aren't you using rs.getDouble(...) instead of Double.parseDouble(rs.getString(...))?


\t
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
FastBiscuitAuthor Commented:
Kiranhk,

Yes, all rows fetched will have data for the sqft value given.. at least I believe this is the case.  It would seem that it's very random about the sqft value chosen by the user that returns the NPE.  By this I mean, if you as a user go in and choose the first possible sqft value and all services.. and then select the second possible sqft value and all services.. etc.. it is very random as to when it will throw the exception.

Furthermore, I can go back and do the exact same search again.. and it won't result in a NPE.

The line of code in question:
if (rs.next()) (line 70)
{ (line 71)
(line 72: Before I do my rs.getString()'s)

FB
0
 
FastBiscuitAuthor Commented:
OrangeHead,

This is my first database project.. I was trying to keep everything simple by only using String values.  It bit me on that line you are questioning.. but I don't think that would throw a NPE.

Your thoughts?

FB
0
 
kiranhkCommented:
so that means sometimes ur cleaning.equals is returning a NPE.
i mean ur cleaning string might be null???
0
 
FastBiscuitAuthor Commented:
cleaning by default is equal to "true".

scotchgard and pet_odor by default are "false".

When these variables are reset, they're reset to true or false, but never to null.

FB
0
 
Tommy BraasCommented:
FastBiscuit,
> Your thoughts?

Well, you would let the APIs do what they are supposed to do.

Make sure that you close the statement after each execution, and preferrably use PreparedStatement instead.

Since the NPE occurs inside the JDBC driver, it might be that the maximum number of statements concurrently allowed open on a connection has been exceeded and that is why the error happens.

stmt.close() will close it.

You can keep the connection around for the duration of your application.

\t
0
 
objectsCommented:
Whats the name of the method you have posted above?
0
 
FastBiscuitAuthor Commented:
objects,

public String getRoomTotal()

FB
0
 
shashi71Commented:
FB,

I do not think there is any prob with ur code although it can be improved as suggested by OrangeHead. It also helps writing exact col names instead of '*'.

How are you obtaining the connection.. from a pool at webserver or creating from the JSP code ?

Also check if there is any SQLException being thrown in the server log.
0
 
fredwangusCommented:
in "rs = stmt.executeQuery("SELECT * FROM pricing WHERE sq_ft = '" + sqFt + "'");"
did u checked whether the variable sqFt is null or not?
0
 
FastBiscuitAuthor Commented:
Shashi71,

I'm obtaining the connection from a "database" class.  Method getConnection() returns a connection.  I'm also utilizing this class to close the connection when I'm finished.. as well as the stmt variable.

I'll need to contact my hosting company to find out about getting access to the logs.

FB
0
 
FastBiscuitAuthor Commented:
fredwangus,

I am not checking to see if the variable is null or not.  It, like the other variables I'm using is never set to null.  It is initialized as an empty string and reset to that every cycle through.

I'll try putting in a check for null prior to running the query..

FB
0
 
kiranhkCommented:
u can also try printing the values retrieved from the DB table to make sure there is no null in that either.
0
 
FastBiscuitAuthor Commented:
Can anyone explain why this is happening so sparatically?
0
 
nadhuviCommented:
Hi ,
Your code looks ok but since u have a NPE i will just highlight wherre are the possible places u will get a NPE so that u can correct it
1)
                    if (cleaning.equals("true"))  or                  if (scotchgard.equals("true"))  or                     if (petOdor.equals("true"))

    solution :
 add a print statment and ensure it is not null ( plz dont say i initailized to empty string by default )


2)                     total += Double.parseDouble(rs.getString("cleaning"));   or         total += Double.parseDouble(rs.getString("scotchgard")); or  
                         total += Double.parseDouble(rs.getString("pet_odor"));
solution:
the reason is rs.getString will give a String Object and  suppose if the object is NULL and u try to parse the NULL object u will get a NPE.  So if u very sure it is a double value then u can use total+= rs.getDouble("cleaning")  or u have to check for null before u apply parse ok..

hope this will help u...

cheers
nadhuvi


0
 
FastBiscuitAuthor Commented:
Nadhuvi,

I'll add in the checks for null sometime this weekend.  I hit the thing at least 50 times last night and couldn't get it to return a NPE.  It's going to be diffucult to test a solution.  Perhaps time will be the best test.

FB
0
 
nadhuviCommented:
good i should be alright if u add the null check..bcz i doubt the line this one (Double.parseDouble(rs.getString("cleaning"));   ) is causing the NPE

good luck
0
 
tdisessaCommented:
From reading the stack trace, I'd like to highlight these lines:

java.lang.NullPointerException:
     at org.gjt.mm.mysql.Statement.executeQuery(Statement.java:124)
     at com.cleanrite.calc.Pricing.getRoomTotal(Pricing.java:72)
     ...

---

This means that Null Pointer Exception is happening in the executeQuery method of your
JDBC driver code.  So, either one of two things are happening:

1.  Your program call executeQuery with a parameter of null.
2.  Your created statement loses it database connection (somehow) and when it tries
to execute your query, it tries to execute it on a null database connection object.

Since it happens sporadically, I would guess the 2nd option is happening.

I'm going to see if I can find some source to "org.gjt.mm.mysql.Statement" and
check out line 124 in Statement.java (in the executeQuery method).
 
0
 
FastBiscuitAuthor Commented:
Awesome tdisessa.. this is more along the lines of what I wanted to hear.  When I originally set up my code, it was doing 3 connects to the database during each iteration through the getRoomTotal() method.  I was getting NPE quite often.  I changed it to do only one connect to the DB and this helped.. some.  Let me know what you find out from the mysql source code.

FB
0
 
FastBiscuitAuthor Commented:
All:

I have added in an additional catch that will send me an e-mail and identify what exactly is null.. if and when an npe is thrown again.  I haven't been able to produce a npe in the past few days.  Anyhow, the code is as follows:

...
            catch (NullPointerException npe)
            {
                StringBuffer sb = new StringBuffer();
                sb.append("The following variable is null: ");

                if (connect == null)
                {
                    sb.append("connect");
                }
                if (sqFt == null)
                {
                    sb.append("sqFt");
                }
                if (cleaning == null)
                {
                    sb.append("cleaning");
                }
                if (scotchgard == null)
                {
                    sb.append("scotchgard");
                }
                if (petOdor == null)
                {
                    sb.append("petOdor");
                }

                mail.addTo("webmaster@cleanriteincorporated.com");
                mail.setFrom("webmaster@cleanriteincorporated.com");
                mail.setSubject("NullPointerException received");
                mail.setBody(sb.toString());
                try
                {
                    mail.send();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
...
0
 
tdisessaCommented:
Ok, here is the code from the latest source, dated 1999/02/26 03:30:56, for the executeQuery method(line numbers added):

90          /**                        
91           * Execute a SQL statement that retruns a single ResultSet                        
92           *                        
93           * @param Sql typically a static SQL SELECT statement                        
94           * @return a ResulSet that contains the data produced by the query                        
95           * @exception java.sql.SQLException if a database access error occurs
96           */                        
97                              
98          public java.sql.ResultSet executeQuery(String Sql) throws java.sql.SQLException      
99          {                        
100            if (Driver.trace) {                  
101                Object[] Args = {Sql};                  
102                Debug.methodCall(this, "executeQuery", Args);                  
103            }                  
104                                    
105            if (_escapeProcessing) {                  
106                Sql = _Escaper.escapeSQL(Sql);                        
107            }                        
108                                           
109            if (_Results != null) {                        
110                _Results.close();                        
111            }                        
112                                    
113            // If there isn't a limit clause in the SQL                        
114            // then limit the number of rows to return in                         
115            // an efficient manner. Only do this if                        
116            // setMaxRows() hasn't been used on any Statements                        
117            // generated from the current Connection (saves                        
118            // a query, and network traffic).                        
119                                    
120            synchronized (_Conn.getMutex()) {                              
121                String OldCatalog = null;                              
122                                                
123                if (!_Conn.getCatalog().equals(_Catalog)) {                                          
124                 OldCatalog = _Conn.getCatalog();                                          
125                 _Conn.setCatalog(_Catalog);                                          
126                }                                          
127                                                
128                if (_Conn.useMaxRows()) {                                          
129                                                
130                  // We need to execute this all together                                    
131                  // So synchronize on the Connection's mutex (because
132                  // even queries going through there synchronize                                    
133                  // on the connection                                    
134                                                
135                if (Sql.toUpperCase().indexOf("LIMIT") != -1) {
136                    _Results = _Conn.execSQL(Sql, _max_rows);                                    
137                  }                                    
138                  else {                                    
139                      if (_max_rows <= 0) {
...

You can see that line 124 is completely unrelated to what you pass into the method (since
I'm pretty sure the 1999 version of the code is the same version that you are using).

And since the Exception doesn't specify a deeper stack trace into _Conn.getCatalog(),
you're NPE is a result of the _Conn = null.  _Conn is defined as a member variable
in Statement of type Conneciton (duh).   It is set to the Connection variable passed
to the Statement constructor.  So, there must be a null Connection object being passed
to the Statement constructor (there is no check in the Statement constructor for
whether the Connection object is null or not).  Which doesn't make sense since it
is the Connection itself instantiating the Statement object and passing
to it a reference to itself (new Statement(this, ...)).  And, the code uses the _Conn
object in line 123, so that would throw a NPE earlier than this line.  So, it looks like you are losing your Connection at an arbitrary point in the driver code.

If you get another error, I'd like to see if it is the same line number.  It probably
will not be the same line number.  But, at least this error seems not to be related
to your code (passing null parameters and such).

Check out this tool:  http://grinder.sourceforge.net/.  Use it to build a script that will
stress your jsp.  Then, load up your jsp with some huge amount of users.  I can
help walk you through this process if you want.  Before you load your jsp, turn on
all the debugging possible and watch the logs.
0
 
FastBiscuitAuthor Commented:
tdisessa -

A friend at work had a similar tool already installed.  I had him hit (add 10 rooms and remove 10 rooms and repeat) the page some 3,000 times within a 5 minute time bracket.  Out of that number, ~180 threw NPE and I was notified through e-mail that connect is the variable that is null.

Where do I go from here?  Is there something I can do to seemlessly continue with the task.. to go back and retry getting an connection, etc.?

Your assistance up to this point has been appreciated.

FB
0
 
FastBiscuitAuthor Commented:
Sorry it has taken me so long to get back with you.

I have solved my problem!

I had 2 classes:
1) a database utility class that had a method for closing the connection to the database and;
2) a pricing class that requested a connection and then closed it via the method in the first class.. or so I thought.

My assumption was that the connection variable was being shared in-between classes.  I was incorrect in this assumption.  Within the pricing class, I was only passing the statement variable back in to be closed.  The connection (because it wasn't be passed back in) was not being closed.

My web hosting company would only allow 20 concurrent open connections to the database.  I was therefore losing my connection in high stress situations.

I have modified my database utility class method closeCon() to accept a connection variable as well as a statement variable as parameters.

I ran a similar stress test on the jsp and received NO NullPointerExceptions.

tdisessa, I'm assigning the full 500 points to you for your assistance/guidance.

Thanks,
FB
0
 
tdisessaCommented:
Cool!  I'm glad you could find where you were keeping connections open.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.