Data selected from mysql but the procedure throws NullPointerException

Question

I'm trying to select some rows from mysql and put it into an ArrayList in a java class

this is the query :

"SELECT user_id,book_id,preference FROM rating LIMIT %d OFFSET %d"

I try to verify the data before storing it in the ArrayList

ratesDATA bk = new ratesDATA();
                bk.setUser_id(rs.getLong("user_id"));
                bk.setBook_id(rs.getLong("book_id"));
                bk.setPref(rs.getDouble("preference"));
                System.out.println("TTTTNNNN"+rs.getLong("user_id")+"/"+rs.getLong("book_id")+"/"+rs.getDouble("preference"));

                list.add(bk);

this what I found in the GlassFish Server Logs: (I limit the rows to 10 rows using a servlet)

------- 10 correct rows and then Exception ------

Infos:   TTTTNNNN1/0/4.0
Infos:   TTTTNNNN1/3/3.0
Infos:   TTTTNNNN1/4/3.0
Infos:   TTTTNNNN1/5/1.0
Infos:   TTTTNNNN1/6/4.0
Infos:   TTTTNNNN1/8/2.0
Infos:   TTTTNNNN2/0/3.0
Infos:   TTTTNNNN2/1/3.0
Infos:   TTTTNNNN2/4/2.0
Infos:   TTTTNNNN2/7/1.0
 java.lang.NullPointerException
    at com.vaannila.ratesDATA.loadList(ratesDATA.java:134)
    at com.servlet.nextRatesPage.doGet(nextRatesPage.java:57)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)

this is the full code of procedure :

public void loadList(int firstrow,int rowcount) 
    {

            com.mysql.jdbc.PreparedStatement ps = null;  



    list= new ArrayList<ratesDATA>();
            ResultSet rs = null;  

             String url="jdbc:mysql://127.0.0.1:3306/pfadb";
            String name="root";
            String pw="root";
            String driver="com.mysql.jdbc.Driver";    


            Connection connexion=null;


try
{    
    Class.forName(driver).newInstance();
    connexion=DriverManager.getConnection(url,name,pw);        
    String SQL_SUBLIST = "SELECT user_id,book_id,preference  FROM rating LIMIT %d OFFSET %d";

//String q = String.format(SQL_SUBLIST, firstrow, rowcount);
String q = String.format(SQL_SUBLIST, rowcount, firstrow); 

           Statement commande=connexion.createStatement();
              rs=commande.executeQuery(q);                 
               while (rs.next())
                {
                ratesDATA bk = new ratesDATA();
                bk.setUser_id(rs.getLong("user_id"));
                bk.setBook_id(rs.getLong("book_id"));
                bk.setPref(rs.getDouble("preference"));
                System.out.println("TTTTNNNN"+rs.getLong("user_id")+"/"+rs.getLong("book_id")+"/"+rs.getDouble("preference"));

                list.add(bk);

                }

                } 

                catch(Exception e)
                {
                e.printStackTrace();

                }
                finally
                {
                try
                {
                connexion.close();
                ps.close();
                }
                catch(Exception e)
                {
                e.printStackTrace();
                }
                }     








    }

And this is the full code of servlet :

protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
       int page = 1;
        try{
                    int recordsPerPage = 10;
        if(request.getParameter("page") != null)
            page = Integer.parseInt(request.getParameter("page"));
        ratesDATA dao = new ratesDATA();
                dao.loadList((page-1)*recordsPerPage,recordsPerPage);

        List<ratesDATA> list = dao.getList();



        int noOfRecords = dao.getNoOfRecords();
        int noOfPages = (int) Math.ceil(noOfRecords * 1.0 / recordsPerPage);

        request.setAttribute("rateslist", list);
        request.setAttribute("noOfPages", noOfPages);
        request.setAttribute("currentPage", page);
        RequestDispatcher view = request.getRequestDispatcher("adminRates.jsp");
        view.forward(request, response); 


                } catch (Exception ex) {
                    System.out.println("TTTTTTTTTTTTTTTT"+ex.getMessage());

        }
    }

spencer7593 · Answer 1 · 2016-05-21T23:53:56.637

0

Close the statement before you close the connection.

What does ps refer to?

    ps.close();

edited May 21 '16 at 23:53

answered May 21 '16 at 23:48

spencer7593

106,611
15
112
140

Thank for the reply this small code was the source of my problem – Taha May 22 '16 at 12:32

score 0 · Answer 2 · answered May 22 '16 at 02:30

0

Change code at finally. Don't need try block again and change ps to rs.

finally {
    rs.close();
    connexion.close(); 
}

answered May 22 '16 at 02:30

Kaung Yee Hein

46
5

Thank you for the help the NullPointerException disappear – Taha May 22 '16 at 12:27

Data selected from mysql but the procedure throws NullPointerException

2 Answers2