Forum:

JDBC and Relational Databases

Keep getting Null Pointer Exception

Blake Edward

Ranch Hand

Posts: 124

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I am trying to build a small application at work. I have a DB set up and I have connected to it with SQL WorkBench and I wrote a simple program to read and write to it. Problem was that after so many iterations and so much editing the code seemed muddled, the DB was not being found, and I could not find my mistake. So, I built a small local version that does the same thing except it points to a local DB I made. I am using Tomcat because that is what the production environment uses. So the following code is a simple app that is based on a tutorial and on my needs. It is local on my machine only. I keep getting a NullPointerException at line 160.

When I run the file the page renders with a form that contains 18 <td> to be filled in. The page posts to itself, just to keep things simple. When I press the submit button I get an exception at line 160.

So, it seems that my connection to my DB works or I would get a SQLException at line 40.

It looks like data.setData is simply returning NULL at line 160, but I can't see my mistake and I can't figure out how to solve the issue. The method that I am using returns an int and I am wondering if that is the correct return type for this method.

Yes I am a novice and this is painful due to lack of mentorship at work. If you have any suggestions I would greatly value your feedback. Be as honest as you need to be.

Thanks!

<%--
    Document   : index
    Created on : Jan 6, 2016, 6:43:50 PM
    Author     : bx
--%>
<%@page import="java.sql.*"%>
<% Class.forName("com.mysql.jdbc.Driver");%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Admin Data</title>
    </head>
    <body>
        <h1>Insert Data Into The DB</h1>
       
        <%!
        public class Data {
            String URL = "jdbc:derby://localhost:1527/spokespersondemo";
            String User = "nbuser";
            String Password = "nbuser";
 
            Connection connection = null;
            PreparedStatement insertData = null;
            //ResultSet resultSet = null;
 
            public Data(){
                try {
                    connection = DriverManager.getConnection(URL, User, Password);
                    insertData = connection.prepareStatement(
 
                        "INSERT INTO APP.SPOKESPERSONS (type, geo, countries, organization, division,"
                        + "mgr_host, press_trained, topics_covered, last_name, first_name, wwid, email,"
                        + "date_added, last_trained, refresh_training, removed, date_removed, comments)"
                        + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
 
                        //System.out.println(connection);
                       
                }catch (SQLException e) {
                e.printStackTrace();
                }
            }
 
                public int setData(String type, String geo, String countries, String organization, String division,
                        String mgr_host, String press_trained, String topics_covered, String last_name, String first_name, String wwid, String email,
                        String date_added, String last_trained, String refresh_training, String removed, String date_removed, String comments){
 
                    int result = 0;
 
                    try{
                        insertData.setString(1, type);
                        insertData.setString(2, geo);
                        insertData.setString(3, countries);
                        insertData.setString(4, organization);
                        insertData.setString(5, division);
                        insertData.setString(6, mgr_host);
                        insertData.setString(7, press_trained);
                        insertData.setString(8, topics_covered);
                        insertData.setString(9, last_name);
                        insertData.setString(10, first_name);
                        insertData.setString(11, wwid);
                        insertData.setString(12, email);
                        insertData.setString(13, date_added);
                        insertData.setString(14, last_trained);
                        insertData.setString(15, refresh_training);
                        insertData.setString(16, removed);
                        insertData.setString(17, date_removed);
                        insertData.setString(18, comments);
                        result = insertData.executeUpdate();
                    }catch (SQLException e) {
                e.printStackTrace();
                }
 
                return result;
            }
        }%>
           
        <%
            int result = 0;
            if(request.getParameter("submit") != null){
           
            String type = new String();
            String geo = new String();
            String countries = new String();
            String organization = new String();
            String division = new String();
            String mgr_host = new String();
            String press_trained = new String();
            String topics_covered = new String();
            String last_name = new String();
            String first_name = new String();
            String wwid = new String();
            String email = new String();
            String date_added = new String();
            String last_trained = new String();
            String refresh_training = new String();
            String removed = new String();
            String date_removed = new String();
            String comments = new String();
           
            if (request.getParameter("type") != null){
                type = request.getParameter("type");
            }
            if (request.getParameter("geo") != null){
                geo = request.getParameter("geo");
            }
            if (request.getParameter("countries") != null){
                countries = request.getParameter("countries");
            }
            if (request.getParameter("organization") != null){
                organization = request.getParameter("organization");
            }
            if (request.getParameter("division") != null){
                division = request.getParameter("division");
            }
            if (request.getParameter("mgr_host") != null){
                mgr_host = request.getParameter("mgr_host");
            }
            if (request.getParameter("press_trained") != null){
                press_trained = request.getParameter("press_trained");
            }
            if (request.getParameter("topics_covered") != null){
                topics_covered = request.getParameter("topics_covered");
            }
            if (request.getParameter("last_name") != null){
                last_name = request.getParameter("last_name");
            }
            if (request.getParameter("first_name") != null){
                first_name = request.getParameter("first_name");
            }
            if (request.getParameter("wwid") != null){
                wwid = request.getParameter("wwid");
            }
            if (request.getParameter("email") != null){
                email = request.getParameter("email");
            }
            if (request.getParameter("date_added") != null){
                date_added = request.getParameter("date_added");
            }
            if (request.getParameter("last_trained") != null){
                last_trained = request.getParameter("last_trained");
            }
            if (request.getParameter("refresh_training") != null){
                refresh_training = request.getParameter("refresh_training");
            }
            if (request.getParameter("removed") != null){
                removed = request.getParameter("removed");
            }
            if (request.getParameter("date_removed") != null){
                date_removed = request.getParameter("date_removed");
            }
            if (request.getParameter("comments") != null){
                comments = request.getParameter("comments");
            }
           
            
            
            Data data = new Data();
            result = data.setData(type, geo, countries, organization, division, mgr_host, press_trained, topics_covered,
            last_name, first_name, wwid, email, date_added, last_trained, refresh_training, removed, date_removed, comments);
            }
        %>        
       
        
        <form name="dataForm" action="index.jsp" method="POST">
            <table border="0">
               
                <tbody>
                    <tr>
                        <td>Type</td>
                        <td><input type="text" name="type" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Geo</td>
                        <td><input type="text" name="geo" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Country</td>
                        <td><input type="text" name="countries" value="" size="64" /></td>
                        
                    </tr>
                    <tr>
                        <td>Organization</td>
                        <td><input type="text" name="organization" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Division</td>
                        <td><input type="text" name="division" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Mgr/Host</td>
                        <td><input type="text" name="mgr_host" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Press Trained</td>
                       <td><input type="text" name="press_trained" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Topics Covered</td>
                        <td><input type="text" name="topics_covered" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Last Name</td>
                        <td><input type="text" name="last_name" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>First Name</td>
                        <td><input type="text" name="first_name" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>WWID</td>
                        <td><input type="text" name="wwid" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Email</td>
                        <td><input type="text" name="email" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Date Added</td>
                        <td><input type="text" name="date_added" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Last Trained</td>
                        <td><input type="text" name="last_trained" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Refresh Training</td>
                        <td><input type="text" name="refresh_training" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Removed</td>
                        <td><input type="text" name="removed" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Date Removed</td>
                        <td><input type="text" name="date_removed" value="" size="64" /></td>
                       
                    </tr>
                    <tr>
                        <td>Comments</td>
                        <td><input type="text" name="comments" value="" size="64" /></td>
                       
                    </tr>
                </tbody>
            </table>
            <input type="reset" value="Clear" name="clear" />
            <input type="submit" value="Submit" name="submit" />
 
        </form>
       
    </body>
</html>

“You may never get to touch the Master, but you can tickle his creatures.”
― Thomas Pynchon, Gravity's Rainbow

Tapas Chand

Ranch Hand

Posts: 624

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

First of all move the Java code to a Java file. Never write a single line of Java code in JSP. It is deprecated since around 14 years.
Then I feel it will be very easy for you to identify the root cause of the exception.

Blake Edward

Ranch Hand

Posts: 124

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

The point here is just to get everything on one page so it can be easily viewed. This jsp file replicates what I am trying to do. Whether it stays in this form is yet to be determined. If enough people agree about not using jsp's and can tell me why it is bad practice then that's advice I am willing to take. The initial/original version doesn't have the DB info embedded in it anyway, so it is split up and the page lives in the Web-INF folder and is password protected.

Thank you!

“You may never get to touch the Master, but you can tickle his creatures.”
― Thomas Pynchon, Gravity's Rainbow

Maneesh Godbole

Bartender

Posts: 11497

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Your problem is with DB connectivity. No body would like the read the JSP which frankly speaking is unrelated.

So start by writing a small code which connects to the DB and does some test CRUD operations.
Test it. If it works, compare it with your existing code. If not, post the full stack trace with the relevant code so we can take a look.
All this is usually called SSCCE
(Most probably, while writing the SSCCE you will figure out the problem yourself.

)

Good advice by Tapas above. JSPs should be responsible for presentation not logic. Once you fix the DB issue, you might also want to consider refactoring your project accordingly.

[How to ask questions] [Donate a pint, save a life!] [Onff-turn it on!]

Dave Tolls

Rancher

Posts: 4801

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Blake Edward wrote:The point here is just to get everything on one page so it can be easily viewed. This jsp file replicates what I am trying to do. Whether it stays in this form is yet to be determined. If enough people agree about not using jsp's and can tell me why it is bad practice then that's advice I am willing to take. The initial/original version doesn't have the DB info embedded in it anyway, so it is split up and the page lives in the Web-INF folder and is password protected.

Thank you!

The structure of a Java web app is servlets do the work, JSPs display the results.

This has been the case for 15 years or more.

One of the main issues with code in JSPs is that you can't test them or debug them in any easy fashion.

For example, to take our code, if that was in a normal Java class, called by a servlet, you could then detach the class and test it on its own.
At the moment I am guessing you have to fire up the server to test code changes to that lot.

Maneesh Godbole

Bartender

Posts: 11497

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Check out this excellent article on why it is a good idea to separate view and logic.
http://www.javaranch.com/journal/200603/Journal200603.jsp#a5

[How to ask questions] [Donate a pint, save a life!] [Onff-turn it on!]

Roel De Nijs

Sheriff

Posts: 11604

178

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Blake Edward wrote:The point here is just to get everything on one page so it can be easily viewed. This jsp file replicates what I am trying to do. Whether it stays in this form is yet to be determined. If enough people agree about not using jsp's and can tell me why it is bad practice then that's advice I am willing to take. The initial/original version doesn't have the DB info embedded in it anyway, so it is split up and the page lives in the Web-INF folder and is password protected.

I agree with all other replies: a JSP page should only be used to display results not to connect to your database and execute queries. When I started in 2004 that was already the best practice and it didn't change.

And another few remarks:
1/ is there a specific reason why you are initializing your variables with an empty String using its default constructor instead of the more concise empty String literal ""?
2/ why are you doing the null check for every parameter? Why not simply get parameter value from request like thisIf the parameter does not exist, null will be returned (and will be written to the database table).

SCJA, SCJP (1.4 | 5.0 | 6), SCJD
OCAJP 7

Blake Edward

Ranch Hand

Posts: 124

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Maneesh Godbole wrote:Your problem is with DB connectivity. No body would like the read the JSP which frankly speaking is unrelated.

So start by writing a small code which connects to the DB and does some test CRUD operations.
Test it. If it works, compare it with your existing code. If not, post the full stack trace with the relevant code so we can take a look.
All this is usually called SSCCE
(Most probably, while writing the SSCCE you will figure out the problem yourself. )

Good advice by Tapas above. JSPs should be responsible for presentation not logic. Once you fix the DB issue, you might also want to consider refactoring your project accordingly.

All in all, there was some great advice here. I just really needed a push into the right direction. The rules of SSCCE was just that push. I kept whittling down my problem. I created a test program for my DB's. I first created one for the local DB which was successful, then created one for my actual DB. I started sifting through the errors and found that the issue was with the certification. Since the DB is confidential I need a cert and a key store. I had gone through this process but there was a problem with the URL string. Anyway, much thanks to everyone that responded. I think I can finally see the end of this project coming up.

Also, thanks for the advice regarding jsp's and keeping the code separate. I am amazed at how many tutorials and well respected books don't preach this philosophy.

“You may never get to touch the Master, but you can tickle his creatures.”
― Thomas Pynchon, Gravity's Rainbow

Dave Tolls

Rancher

Posts: 4801

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Blake Edward wrote:Also, thanks for the advice regarding jsp's and keeping the code separate. I am amazed at how many tutorials and well respected books don't preach this philosophy.

It's all too common, sadly.
It is possibly my most frequent rant...

Roel De Nijs

Sheriff

Posts: 11604

178

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Dave Tolls wrote:It's all too common, sadly.

Absolutely! I'm really baffled how often a new topic has been created with a JSP containing some business logic code (often to connect to a database and execute a query). And for me that's really strange as it was already considered a (very) bad practice when I began as a Java developer (now more than 10 years ago).

SCJA, SCJP (1.4 | 5.0 | 6), SCJD
OCAJP 7

Tapas Chand

Ranch Hand

Posts: 624

I like...

posted 8 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Dave Tolls wrote:It's all too common, sadly...

Yes, absolutely.
In my case, in a practical exam during studies, I did the same thing, connected to DB from JSP and displayed results.
The examiner told me this is not the correct way to do even if it produced the required output and cut 50% of the marks.

It was a good lesson for me.

Blake Edward

Ranch Hand

Posts: 124

I like...

posted 8 years ago

3
Number of slices to send:

Optional 'thank-you' note:

Send

I appreciate all the remarks about how to correctly write a JSP. It really drives home the point in a positive way without making me feel to shameful.

Java Ranch Rocks The Code Forum World!

“You may never get to touch the Master, but you can tickle his creatures.”
― Thomas Pynchon, Gravity's Rainbow

There will be plenty of time to discuss your objections when and if you return. The cargo is this tiny ad:

Gift giving made easy with the permaculture playing cards

https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing