Results 1 to 2 of 2
  1. #1
    FMV
    FMV is offline Member
    Join Date
    May 2011
    Posts
    1
    Rep Power
    0

    Default Returning result set to a servlet

    I have the following difficulty with regards to java:
    I have an html page with two checkboxes, the user ticks one checkboxes
    and according to which checkbox is ticked, certain data is viewed. If the
    first checkbox is ticked a list of books is viewed and if the second checkbox is
    ticked a list of cds is viewed. I am checking which checkbox is ticked by means of a servlet.
    The data is stored in a database using mySql.
    My problem is that I created a class which handles the connection to the database,
    and then when a checkbox is ticked, a stored procedure is invoked which
    returs the result back to the class. I created a method which does this, and the
    method returns the Result Set which I want to pass to the servlet in order to
    be able to print the data.
    However this approach is not working, can you please tell me what I am doing wrong?

    This is the DatabaseConnection class:

    Java Code:
    package exotiqueDatabaseConnection;
    
    import java.sql.*;
    import java.io.PrintWriter;
    import java.io.Serializable;
    
    public class ConnectDatabase implements Serializable {
    	
    	Statement stmt;
    	Connection conn;
    	ResultSet rs;
    	String username;
    	String password;
    	String driver;
    	String url;
    	
    	public ConnectDatabase()
    	{
    		try{
    		username="root";
    		password="does001";
    		
    		driver="com.mysql.jdbc.Driver";
    		Class.forName(driver);
    		
    		url="jdbc:mysql://localhost/exotique";
    		conn=DriverManager.getConnection(url, username, password);
    		
    		stmt=conn.createStatement();
    		
    		}
    		catch(ClassNotFoundException cnfe)
    		{
    			
    		}
    		catch (SQLException sq)
    		{
    			
    		}
    		
    		
    		
    	}
    	
    	public void populateUsers(String usernameIn, String emailIn, String passwordIn)
    	{
    		try{
    			
    	/*	username="root";
    		password="does001";
    		
    		driver="com.mysql.jdbc.Driver";
    		try {
    			Class.forName(driver);
    		} catch (ClassNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    		url="jdbc:mysql://localhost/exotique";
    		conn=DriverManager.getConnection(url, username, password);
    		
    		stmt=conn.createStatement();*/
    		
    			
    		CallableStatement cs=conn.prepareCall("{call populateusers(?,?,?)}");
    		cs.setString(1, usernameIn);
    		cs.setString(2, emailIn);
    		cs.setString(3, passwordIn);
    		cs.execute();
    		}
    		catch(SQLException sq)
    		{
    			System.out.print(sq.getMessage());
    		}
    		catch(Exception ex)
    		{
    			System.out.print(ex.getMessage());
    		}
    		finally
    		{
    			 close(conn);
    	         close(rs);
    	         close(stmt);
    		}
    	}
    	
    	@SuppressWarnings("finally")
    	public ResultSet showAllBooks()
    	{
    		try{
    			
    		
    			
    			CallableStatement cs=conn.prepareCall("{call getAllBooks}");
    			
    			cs.execute();
                rs=cs.getResultSet();
                
                
    
               
    		}
    		catch(SQLException sq)
    		{
    			System.out.print(sq.getMessage());
    		}
    		catch(Exception ex)
    		{
    			System.out.print(ex.getMessage());
    		}
    		finally
    		{
    			 close(conn);
    	         close(rs);
    	         close(stmt);
    	         return rs;
    		}
    		
    		
    	}
    	
    	public  void close(Statement stmt)
        {
            if(stmt!=null)
            {
                try{
                    stmt.close();
                }
                catch(SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
        
        public  void close(Connection conn)
        {
            if(conn!=null)
            {
                try
                {
                    conn.close();
                }
                catch(SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
        
        public void close(ResultSet rs)
        {
            if(rs!=null)
            {
                try
                {
                    rs.close();
                }
                catch(SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
    
    	
    
    }
    This is the servlet class:

    Java Code:
    package exotiqueServlets;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.*;
    
    import exotiqueDatabaseConnection.*;
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * Servlet implementation class ViewProductsServlet
     */
    @WebServlet("/ViewProductsServlet")
    public class ViewProductsServlet extends HttpServlet {
    	private static final long serialVersionUID = 1L;
           
       
    	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		
    		
    		ConnectDatabase cd2=new ConnectDatabase();
    		 ResultSet rst=cd2.showAllBooks();
    		
    		
    		try{
    			
    			
    			
    		response.setContentType("text/html");
    		PrintWriter out = response.getWriter();
    		
    		String retreive=request.getParameter("retreive");
    		
    		CallableStatement cs=conn.prepareCall("{call getAllBooks}");
    		
    		cs.execute();
            	rst=cs.getResultSet();
    		
    		if(retreive!=null){
    		String[] checkboxes=request.getParameterValues("cb");
    		
    		out.println("<html>");
    		out.println("<head>");
    		out.println("<title>Request</title>");
    		//out.println("retreiving results");
    		out.println("<br>");
    		
    		for(String s: checkboxes)
    		{
    			
    			
    			
    			if(s.equals("allBooks"))
    			{
    				
    				
    				
    				
    				
    				while(rst.next())
    		        {
    		            out.println("Product title: "+rst.getString("title"));
    		            out.println("Product details: "+ rst.getString("description"));
    		            out.println("Product price: "+ rst.getDouble("price"));
    		            out.println("Product overall rating: "+ rst.getInt("overallRating"));
    		            out.println("Product price: "+ rst.getString("author"));
    		            out.println("<br>");
    		        }
    				
    				
    			}
    			
    			if(s.equals("allcds"))
    			{
    				
    				out.println("all cds");
    			}
    				
    		        
    		        
    			
    			
    		  
    		
    		
    		          
    		}
    		
    		out.println("</html>");
    		out.println("</head>");
    		
    		}
    		 
    		}
    		catch (SQLException er)
            {
               er.printStackTrace();
            }
    		
    		catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
               
                cd2.close(rst);
            }
    		
    		
    		
    		}
    	}
    Any help is greatly appreciated, thanks in advance

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,853
    Rep Power
    19

    Default

    You (correctly) close your resources in your db access layer (eg showAllBooks).
    Consequently the ResultSet (which is simply a cursor into the database, so rqeuires an active connection and statement) is unusable in the Servlet.

    Now, you should not be passing ResultSets around. Your showAllBooks should do what it does at the moment, but convert the ResultSet into a List of some class that represents your book data. That is what should be returned to the Servlet.

    That also means that you no longer need a close() method on that database class, since it will close everything after each query. Essentially it becomes a Data Access Object (DAO).

    ETA: Oh, and for connections on web servers you should look into connection pooling.

Similar Threads

  1. Result set not returning values
    By karthikeyan_raju in forum Advanced Java
    Replies: 5
    Last Post: 04-14-2011, 05:24 AM
  2. call servlet result page in frameset
    By reubenmk in forum Java Servlet
    Replies: 0
    Last Post: 08-05-2010, 09:44 AM
  3. Replies: 18
    Last Post: 07-19-2010, 12:18 PM
  4. Replies: 1
    Last Post: 12-02-2008, 12:23 AM
  5. Replies: 1
    Last Post: 08-07-2007, 03:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •