Null point exception Error

[morya123]

i am using this following code to compare to columns from two different tables

but i am getting null point exception because some of my fields in columns

Like first_name is empty in few rows!!!


are empty!!!


how can i go beyond those null values and could print whatever there in the database!

my code!!

SpySearchServlet.java

import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*; 
import java.util.*;
public class SpySearchServlet extends HttpServlet{

	public void doPost(HttpServletRequest pRequest , HttpServletResponse pResponse)throws ServletException,java.io.IOException {
	Connection Con = null;
	List<Map>  lSearchList = new ArrayList<Map>();
		try{
			List<Map>  SearchCriteriaList = new ArrayList<Map>();
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			Con = DriverManager.getConnection("jdbc:odbc:orcl","test", "test");
			Statement Stat=Con.createStatement();
			ResultSet Res = Stat.executeQuery("Select SORT_KEY,FIRST_NAME,SECOND_NAME from NegativeList");
			while (Res.next()){
				Map lMap = new HashMap();
				lMap.put("FIRST_NAME",Res.getString("FIRST_NAME"));
				lMap.put("SECOND_NAME",Res.getString("SECOND_NAME"));
				lMap.put("SORT_KEY",Res.getString("SORT_KEY"));
				SearchCriteriaList.add(lMap);
			}
			Stat.close();
			//Res.close();

			PreparedStatement SearchQuery = Con.prepareStatement(" Select * from EmpInfo where Long_Name like ? or Long_Name like ? or Long_Name like ?");
			for ( int IntI  = 0 ; IntI < SearchCriteriaList .size();  IntI ++ ){
				Map CriteriaMap = SearchCriteriaList.get(IntI);
			

				System.out.println("Searching for "+ CriteriaMap );
				SearchQuery.setString(1,"%"+CriteriaMap.get("FIRST_NAME").toString()+"%");
				SearchQuery.setString(2,"%"+CriteriaMap.get("SECOND_NAME").toString()+"%");
				SearchQuery.setString(3,"%"+CriteriaMap.get("SORT_KEY").toString()+"%");
				ResultSet Resl = SearchQuery.executeQuery();

				while(Resl.next() ){
					Map lInfoMap = new HashMap();
					lInfoMap.putAll(CriteriaMap);
					lInfoMap.put("Long_Name" ,Resl.getString("Long_Name"));
					lInfoMap.put("Emp_Id" ,Resl.getString("Emp_Id"));
					lSearchList.add(lInfoMap);
				}
				//Resl.close();

			}
		


			System.out.println(lSearchList );
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{Con.close();}catch(Exception e){}

		}
	RequestDispatcher Dispatcher = pRequest.getRequestDispatcher("SpySearch.jsp");
	pRequest.setAttribute("SearchList",lSearchList);
	Dispatcher .forward(pRequest,pResponse);

	}//end of Post 

	
}

throwing null point exception in 34th line!!

otherwise the code is working fine!!!

[Fubarable]

Which line is the 34th? Also, could you kindly back off on the bold and exclamation marks? It makes your post a bit difficult to read.

[morya123]

lMap.put("SECOND_NAME",Res.getString("SECOND_NAME" ));


this is the line!

[Fubarable]

this is the line!

OK! That is the line!

But are you absolutely sure that this line is throwing a NullPointerException and not another line, or not throwing an SQLException?

[morya123]

yes i am sure because in my database second_name columns are empty in few rows!!!

[r035198x]

Just check if the returned value is null or not using an if test before processing.

P.S. I would throw away that ugly List<Map> and create a class to hold the database row data instead. I would also use the JDBC driver and java code naming conventions.

[Tolls]

First off, you're not closing your statements or your resultset in the finally block. Indeed, you're not closing your resultsets at all. This is Not Good.

Secondly, I'm pretty sure this could be done as a single SQL query. I have yet to encounter a "do one query, cycle through the results and do a second query on those" that could not be done as a single query.

Finally, as r035198x says, you should be using a class to represent each row of results, which are then stuck in a List. That Map is horrible.

[morya123]

not clear !!! plzz explain!!!

another thing is that, if i remove second_name and only compare other columns !!! code is working fine!!!!!

[Tolls]

Nice collection of exclamation marks.

Are you sure the null pointer exception isn't being thrown here:

SearchQuery.setString(2,"%"+CriteriaMap.get("SECOND_NAME").toString()+"%");

?

This is where I would expect it to be, and it is (give or take a line) line 34 of what you posted above? If SECOND_NAME is null (that is the entry in the Map is null) then the toString call can't happen, which would be your NPE. So stick a check around it, or remove the toString().

ETA: Of course if each of your results were in a nice class of their own you could write your own toString() which would do all this for you.
Oh, and looking at that code again I might be wrong about the single SQL. Though I'm not entirely sure what it is you're trying to do.

[morya123]

if u could suggest better one plzzz suggest!!!

actually i am trying to compare two tables!!!

i.e LONG NAME from one table and FIRST_NAME ,SECOND_NAME and sort_key from another table.

[r035198x]

Which of the advices given in this thread so far did you not understand?