Taha Taha - 6 months ago 11
Java Question

Data selected from mysql but the procedure throws NullPointerException

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());

}
}

Answer

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

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