query issue

[giuseppe D'Amico]

HI, I have a servlet that performs two differents query to a mysql db in function of the task,so only one at a time is used but one after the other in very fast sequence, each of them return a json, I have two identical methods parameters_list and potentials_list, but only parameters_list works properly the second one obtain from mysql an empty resultset, I can not figure why. Here it is the relevant code for the two methods:
Parameters_list is the working one

public void parameters_list(int pv_id,PrintWriter out)
	{
		Consts c=new Consts();
		String query=String.format(c.queryParameters, pv_id);
		ArrayList<HashMap<String, String>> rs = null;
		try
		{
			rs=queryDb4Parameters(out,"parafarmacie",query);
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jsonEncode(rs,out,query);
        //out.println("<p> json: "+json+"</p>");
        
        	public ArrayList<HashMap<String, String>> queryDb4Parameters(PrintWriter out,String db,String query) throws ServletException
	{
		ArrayList<HashMap<String, String>> parameters=new ArrayList<HashMap<String,String>>();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		Consts c=new Consts();
		
		try
		{
			//SELECT IF( rpp.tc_clpot_id =1, rpp.valore,  'valore non disponibile' ) AS potenziale,cod_cliente, pv.pv_id, cod_mmas, nome1 AS ragione_sociale, indirizzo, cap, comune, provincia, tel1 AS telefono, cf_pi AS codice_fiscale,nome2 as titolare,fax,cliente FROM pv JOIN rel_pv_pot rpp ON rpp.pv_id = pv.pv_id JOIN tc_clpot tc ON tc.tc_clpot_id = rpp.tc_clpot_id where ";

			Class.forName("com.mysql.jdbc.Driver");
			con =DriverManager.getConnection("jdbc:mysql://localhost:3306/"+db,c.user,c.passwd);
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			// displaying records
			while(rs.next())
			{
					HashMap<String, String> p = new HashMap<String, String>();
					p.put("parametro", rs.getString("parametro"));
					p.put("valore",rs.getString("valore"));
					parameters.add(p);
				//  out.println("<p>"+rs.getObject(2).toString()+"</p>");
		
			}
			con.close();
			//ArrayList<HashMap<String, Object>> result = new ArrayList<HashMap<String,Object>>();
			HashMap<String, Object> data=new HashMap<String, Object>();
			//data.put("success",true );
			data.put("result", parameters);
			//gson.toJson(data, out);
			
			  }catch (SQLException e) {
				  throw new ServletException("Servlet Could not display records.", e);
			  } catch (ClassNotFoundException e) {
			  throw new ServletException("JDBC Driver  not found.", e);
			  } finally {
			  try {
			  if(rs != null) {
			  rs.close();
			  rs = null;
			  }
			  if(stmt != null) {
			  stmt.close();
			  stmt = null;
			  }
			  if(con != null)
			  {
				  con.close();
				  con = null;
			  }
			  } catch (SQLException e) {}
		} //eof try
		return parameters;
	}
		
	}

and the code for potentials_list that does not work

	public void potentials_list(int pv_id,PrintWriter out)
	{
		Consts c=new Consts();
		//inserisco lo id nella clausola where della query
		String query=String.format(c.queryPotentials, pv_id);
		ArrayList<HashMap<String, String>> rs = null;
		try
		{
			rs=queryDb4Potentials(out,"parafarmacie",query);
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jsonEncode(rs,out,query);
        //out.println("<p> json: "+json+"</p>");
        
//        out.close();
		
	}

public ArrayList<HashMap<String, String>> queryDb4Potentials(PrintWriter out,String db,String query) throws ServletException
	{
		ArrayList<HashMap<String, String>> potentials=new ArrayList<HashMap<String,String>>();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		Consts c=new Consts();
		
		try
		{
			//SELECT IF( rpp.tc_clpot_id =1, rpp.valore,  'valore non disponibile' ) AS potenziale,cod_cliente, pv.pv_id, cod_mmas, nome1 AS ragione_sociale, indirizzo, cap, comune, provincia, tel1 AS telefono, cf_pi AS codice_fiscale,nome2 as titolare,fax,cliente FROM pv JOIN rel_pv_pot rpp ON rpp.pv_id = pv.pv_id JOIN tc_clpot tc ON tc.tc_clpot_id = rpp.tc_clpot_id where ";

			Class.forName("com.mysql.jdbc.Driver");
			con =DriverManager.getConnection("jdbc:mysql://localhost:3306/"+db,c.user,c.passwd);
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			//out.println("<p> len rs "+rs.getFetchSize()+" </p>");
			
			// displaying records
			while(rs.next())
			{
					HashMap<String, String> p = new HashMap<String, String>();
					p.put("parametro", rs.getString("parametro"));
					p.put("fascia",rs.getString("fascia"));
					p.put("valore",rs.getString("valore"));
					potentials.add(p);
				//  out.println("<p>"+rs.getObject(2).toString()+"</p>");
		
			}
			con.close();
			//ArrayList<HashMap<String, Object>> result = new ArrayList<HashMap<String,Object>>();
			HashMap<String, Object> data=new HashMap<String, Object>();
			//data.put("success",true );
			data.put("result", potentials);
			//gson.toJson(data, out);
			
			  }catch (SQLException e) {
				  throw new ServletException("Servlet Could not display records.", e);
			  } catch (ClassNotFoundException e) {
			  throw new ServletException("JDBC Driver  not found.", e);
			  } finally {
			  try {
			  if(rs != null) {
			  rs.close();
			  rs = null;
			  }
			  if(stmt != null) {
			  stmt.close();
			  stmt = null;
			  }
			  if(con != null)
			  {
				  con.close();
				  con = null;
			  }
			  } catch (SQLException e) {}
		} //eof try
		return potentials;
	}

both methods
use a third one for json encoding

	public void jsonEncode(ArrayList<HashMap<String, String>> data,PrintWriter out,String query)
	{
		Gson gson = new GsonBuilder().create();
		HashMap<String, Object> result=new HashMap<String, Object>();
		result.put("results", data);
		result.put("query", query);
		result.put("success", true);
		gson.toJson(result, out);
	}

the two methods are the same apart the query and the key I use for the json to return, so do not understand whi potential_list obtain a void resultSet in queryDb4Potentials, I wish someone can help me,
thanks in advance

[Jeanne Boyarsky]

What do you mean by "doesn't work." Does it throw an exception or just not return any data?

Also, there is a lot of code here. Could you try replying to this post by just showing what is different between the two examples?

[Abhinav Kansal]

Did you ran the query independently and saw the results? However the code looks alright.

I'm not sure why you have used two different methods for the same logic. I would suggest to modularize these methods and try running the core logic within a single method. To me, it just seems to be a problem with too much code duplication rather than some technical issue.

[giuseppe D'Amico]

Thanks for your reply, I solved, just a stupid mistake.