Solved

Receiving a NullPointerException from mysql.Statement.executeQuery

Posted on 2004-10-20
26
567 Views
Last Modified: 2010-03-31
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. :-)
0
Comment
Question by:FastBiscuit
  • 12
  • 4
  • 3
  • +5
26 Comments
 
LVL 8

Expert Comment

by:kiranhk
ID: 12364163
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
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 12364495
Hi FastBiscuit,

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


\t
0
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12364650
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
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12364674
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
 
LVL 8

Expert Comment

by:kiranhk
ID: 12364793
so that means sometimes ur cleaning.equals is returning a NPE.
i mean ur cleaning string might be null???
0
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12364959
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
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 12365031
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
 
LVL 92

Expert Comment

by:objects
ID: 12365206
Whats the name of the method you have posted above?
0
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12366165
objects,

public String getRoomTotal()

FB
0
 

Expert Comment

by:shashi71
ID: 12366551
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
 
LVL 2

Expert Comment

by:fredwangus
ID: 12367407
in "rs = stmt.executeQuery("SELECT * FROM pricing WHERE sq_ft = '" + sqFt + "'");"
did u checked whether the variable sqFt is null or not?
0
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12369218
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
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12369266
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Expert Comment

by:kiranhk
ID: 12370539
u can also try printing the values retrieved from the DB table to make sure there is no null in that either.
0
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12371737
Can anyone explain why this is happening so sparatically?
0
 
LVL 1

Expert Comment

by:nadhuvi
ID: 12378392
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
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12382236
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
 
LVL 1

Expert Comment

by:nadhuvi
ID: 12382308
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
 
LVL 2

Expert Comment

by:tdisessa
ID: 12385555
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
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12388208
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
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12395681
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
 
LVL 2

Expert Comment

by:tdisessa
ID: 12396852
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
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12417494
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
 
LVL 2

Accepted Solution

by:
tdisessa earned 500 total points
ID: 12422061
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
 
LVL 1

Author Comment

by:FastBiscuit
ID: 12483583
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
 
LVL 2

Expert Comment

by:tdisessa
ID: 12483669
Cool!  I'm glad you could find where you were keeping connections open.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now