?
Solved

Select not working with Access database

Posted on 2011-05-02
16
Medium Priority
?
179 Views
Last Modified: 2012-05-11
hi, regarding the code below, i'm trying to get select to at the end of the class to print all data in the bookshelf to the console iwhen the button print 'print contents of bookshelf' is pressed on the gui but i'm getting a null pointer exception.  any suggestions please?
package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private int ID = 0;
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost)

	{
		  String sql = "INSERT INTO BookShelf VALUES(?, ?, ?, ?, ?, ?)";
			  
			  Connection link = null;
			  try{
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
				  PreparedStatement statement = link.prepareStatement(sql);
				  statement.setInt(1,ID);
				  statement.setString(2,Title);
				  statement.setString(3,Author);
				  statement.setString(4,Publisher);
				  statement.setInt(5,Year);
				  statement.setDouble(6,Cost);
				  statement.executeUpdate();
				  link.close();
				  
			  } catch (ClassNotFoundException e) {
		            System.out.println("Unable to load driver");
		            e.printStackTrace();
		        } catch (SQLException e) {
		            System.out.println("Cannot connect to the database");
		            e.printStackTrace();
		        } catch (Exception ex) {
		            System.out.println("Error " + ex.toString());
		            ex.printStackTrace();
		        }
			finally {
			    try { link.close() ; } catch(Exception e) { /* ignore */ }	
			}
		    } 
	
	
/**
 * Create the GUI frame with buttons and listeners
 */
	public BookGUI( )
    {
        // Create a new instance of BookShelf	
    	bookShelf = new BookShelf();
    	
        JFrame frame = this; 
     	
        // Create the main frame's menu bar.
        JMenuBar menubar = new JMenuBar();
    	frame.setJMenuBar(menubar);
    	frame.pack();
    	frame.setVisible(true);
    	
    	//  Create the File menu
    	JMenu fileMenu = new JMenu("File");
    	menubar.add(fileMenu);
    	
    	// Create a Quit option on the File menu
    	JMenuItem quitItem = new JMenuItem("Quit");
    	fileMenu.add(quitItem);
    	quitItem.addActionListener(this); 
    	
    	// create the About menu
    	JMenu aboutMenu = new JMenu ("About");
    	menubar.add(aboutMenu);
    	
    	JMenuItem infoItem = new JMenuItem("Info");
    	aboutMenu.add(infoItem);
    	infoItem.addActionListener(this);
    	
    	//Set the Size of the frame    	
    	setSize(WIDTH, HEIGHT);
      
    	// Create a new instance of the WindowDestroyer class
        addWindowListener(new WindowDestroyer( ));
        setTitle("GUI Assignment");
        Container content = getContentPane( );

        
        // Arrange the buttons on the GUI in a Flow Layout
        content.setLayout(new FlowLayout());
             
        // Set up the required buttons on the GUI and the corresponding listener for that action.
        JButton button1 = new JButton("Add Book");
        content.add(button1);
        button1.addActionListener(this); 
        
        JButton button2 = new JButton("Cost of BookShelf");
        content.add(button2);
        button2.addActionListener(this);
        
        JButton button3 = new JButton("Print contents of BookShelf");
        content.add(button3);
        button3.addActionListener(this);
        
        JButton button4 = new JButton("Highest Price Paid");
        content.add(button4);
        button4.addActionListener(this);
        
        }
	
    
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   ID = Integer.parseInt(JOptionPane.showInputDialog("ID"));
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       insertRow(ID, title, author, publisher, year, cost);
       

        String message =  "The following was successfully added to the database - The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Print contents of BookShelf"))
    {
       //String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       //JOptionPane.showMessageDialog(this, message);
    	
    	String select = "Select * from BookShelf";
    	
    	
    	try{
      	  // Step 5
      	while (results.next()){
      	System.out.println("ID:    " + results.getInt(1));
      	System.out.println("Title: " + results.getString(2) + " " + 
      	results.getString(3));
      	    System.out.println("Author:        " + results.getString(4));
      	    System.out.println("Publisher:        " + results.getString(5));
      	    System.out.println("Year:        " + results.getInt(6));
      	    System.out.println("Cost:        " + results.getDouble(7));
      	    System.out.println();
      	  }
      	 
    	
    	
    	//catch(SQLException e){
   	     //System.out.println("Error retrieving data");
   	    // e.printStackTrace();
   	   System.exit(1);
   	

    	} finally{
    
    	
    
    }
    	
    	
    	
    	
    	
}
    	
    
    
        //System.exit(  );
  }

 // end actionPerformed

Open in new window

0
Comment
Question by:t38
  • 8
  • 4
  • 4
16 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 668 total points
ID: 35508411
You're not creating any Statement and making the query to get a ResultSet
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35508430
Where is your executeQuery?
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 1332 total points
ID: 35508467
You should add something like these lines: (I din't check if those connection and satement are already defined in or open in this scope)

This is in general waht is necessary:

           link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");  <--- add this
            Statement stmt = link.createStatement(); <--- add this

          String select = "Select * from BookShelf";
          
          ResultSet result = stmt.executeQuery(selec);<--- add  this
          try{
              // Step 5
            while (results.next()){
            System.out.println("ID:    " + results.getInt(1));
            System.out.println("Title: " + results.getString(2) + " " +
            results.getString(3));
                System.out.println("Author:        " + results.getString(4));
                System.out.println("Publisher:        " + results.getString(5));
                System.out.println("Year:        " + results.getInt(6));
                System.out.println("Cost:        " + results.getDouble(7));
                System.out.println();
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 86

Expert Comment

by:CEHJ
ID: 35508482
You can use your other one as a template:
public void print() {
        String sql = "select * from BookShelf";

        Connection link = null;

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            link = DriverManager.getConnection("jdbc:odbc:BookShelf", "", "");

            Statement statement = link.createStatement();
            ResultSet = statement.executeQuery(sql);
	    // Now collect results
            link.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Unable to load driver");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Cannot connect to the database");
            e.printStackTrace();
        } catch (Exception ex) {
            System.out.println("Error " + ex.toString());
            ex.printStackTrace();
        } finally {
            try {
                link.close();
            } catch (Exception e) { /* ignore */
            }
        }
    }

Open in new window

0
 

Author Comment

by:t38
ID: 35508915
thanks to your both for your input, i amended the code as below and it prints the first entry in the database to the console and then then the following -

ID:    6
Title:        Java for Beginners
Author:        Peter Andre
Publisher:        Mills and Boon
Year:        2000
Cost:        20.0

Cannot connect to the database
java.sql.SQLException: ResultSet is closed
      at sun.jdbc.odbc.JdbcOdbcResultSet.checkOpen(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcResultSet.next(Unknown Source)
      at database.BookGUI.print(BookGUI.java:161)
      at database.BookGUI.actionPerformed(BookGUI.java:247)
      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
      at java.awt.Component.processMouseEvent(Unknown Source)
      at javax.swing.JComponent.processMouseEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)

package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private int ID = 0;
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost)

	{
		  String sql = "INSERT INTO BookShelf VALUES(?, ?, ?, ?, ?, ?)";
			  
			  Connection link = null;
			  try{
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
				  PreparedStatement statement = link.prepareStatement(sql);
				  statement.setInt(1,ID);
				  statement.setString(2,Title);
				  statement.setString(3,Author);
				  statement.setString(4,Publisher);
				  statement.setInt(5,Year);
				  statement.setDouble(6,Cost);
				  statement.executeUpdate();
				  link.close();
				  
			  } catch (ClassNotFoundException e) {
		            System.out.println("Unable to load driver");
		            e.printStackTrace();
		        } catch (SQLException e) {
		            System.out.println("Cannot connect to the database");
		            e.printStackTrace();
		        } catch (Exception ex) {
		            System.out.println("Error " + ex.toString());
		            ex.printStackTrace();
		        }
			finally {
			    try { link.close() ; } catch(Exception e) { /* ignore */ }	
			}
		    } 
	
	
/**
 * Create the GUI frame with buttons and listeners
 */
	public BookGUI( )
    {
        // Create a new instance of BookShelf	
    	bookShelf = new BookShelf();
    	
        JFrame frame = this; 
     	
        // Create the main frame's menu bar.
        JMenuBar menubar = new JMenuBar();
    	frame.setJMenuBar(menubar);
    	frame.pack();
    	frame.setVisible(true);
    	
    	//  Create the File menu
    	JMenu fileMenu = new JMenu("File");
    	menubar.add(fileMenu);
    	
    	// Create a Quit option on the File menu
    	JMenuItem quitItem = new JMenuItem("Quit");
    	fileMenu.add(quitItem);
    	quitItem.addActionListener(this); 
    	
    	// create the About menu
    	JMenu aboutMenu = new JMenu ("About");
    	menubar.add(aboutMenu);
    	
    	JMenuItem infoItem = new JMenuItem("Info");
    	aboutMenu.add(infoItem);
    	infoItem.addActionListener(this);
    	
    	//Set the Size of the frame    	
    	setSize(WIDTH, HEIGHT);
      
    	// Create a new instance of the WindowDestroyer class
        addWindowListener(new WindowDestroyer( ));
        setTitle("GUI Assignment");
        Container content = getContentPane( );

        
        // Arrange the buttons on the GUI in a Flow Layout
        content.setLayout(new FlowLayout());
             
        // Set up the required buttons on the GUI and the corresponding listener for that action.
        JButton button1 = new JButton("Add Book");
        content.add(button1);
        button1.addActionListener(this); 
        
        JButton button2 = new JButton("Cost of BookShelf");
        content.add(button2);
        button2.addActionListener(this);
        
        JButton button3 = new JButton("Print contents of BookShelf");
        content.add(button3);
        button3.addActionListener(this);
        
        JButton button4 = new JButton("Highest Price Paid");
        content.add(button4);
        button4.addActionListener(this);
        
        }
	public void print() {
        String sql = "select * from BookShelf";

        Connection link = null;

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            link = DriverManager.getConnection("jdbc:odbc:BookShelf", "", "");

            Statement statement = link.createStatement();
            results = statement.executeQuery(sql);
	    // Now collect results
            while (results.next()){
            	System.out.println("ID:    " + results.getInt(1));
            	   	System.out.println("Title:        " + results.getString(2));
            	     System.out.println("Author:        " + results.getString(3));
            	     System.out.println("Publisher:        " + results.getString(4));
            	     System.out.println("Year:        " + results.getInt(5));
            	     System.out.println("Cost:        " + results.getDouble(6));
            	     System.out.println();
            link.close();}
        } catch (ClassNotFoundException e) {
            System.out.println("Unable to load driver");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Cannot connect to the database");
            e.printStackTrace();
        } catch (Exception ex) {
            System.out.println("Error " + ex.toString());
            ex.printStackTrace();
        } finally {
            try {
                link.close();
            } catch (Exception e) { /* ignore */
            }
        }
    } 

    
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   ID = Integer.parseInt(JOptionPane.showInputDialog("ID"));
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       insertRow(ID, title, author, publisher, year, cost);
       

        String message =  "The following was successfully added to the database - The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Print contents of BookShelf"))
    {
       //String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       //JOptionPane.showMessageDialog(this, message);
    	
    	print();
    	
    	
    
    }
    	
}
//}
    	
    	
    	
}
    	
    
    
       // System.exit( );
  //}
  //}

 // end actionPerformed

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35508932
Should be
while (results.next()){
            	System.out.println("ID:    " + results.getInt(1));
            	   	System.out.println("Title:        " + results.getString(2));
            	     System.out.println("Author:        " + results.getString(3));
            	     System.out.println("Publisher:        " + results.getString(4));
            	     System.out.println("Year:        " + results.getInt(5));
            	     System.out.println("Cost:        " + results.getDouble(6));
            	     System.out.println();
}// MISSED THIS
            link.close();}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35508938
And then of course there's no brace after link.close();
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35508950
Just to be clear:
public void print() {
        String sql = "select * from BookShelf";

        Connection link = null;

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            link = DriverManager.getConnection("jdbc:odbc:BookShelf", "", "");

            Statement statement = link.createStatement();
            results = statement.executeQuery(sql);

            // Now collect results
            while (results.next()) {
                System.out.println("ID:    " + results.getInt(1));
                System.out.println("Title:        " + results.getString(2));
                System.out.println("Author:        " + results.getString(3));
                System.out.println("Publisher:        " + results.getString(4));
                System.out.println("Year:        " + results.getInt(5));
                System.out.println("Cost:        " + results.getDouble(6));
                System.out.println();
            }

            link.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Unable to load driver");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Cannot connect to the database");
            e.printStackTrace();
        } catch (Exception ex) {
            System.out.println("Error " + ex.toString());
            ex.printStackTrace();
        } finally {
            try {
                link.close();
            } catch (Exception e) { /* ignore */
            }
        }
    }

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35508974
When you see the error:
java.sql.SQLException: ResultSet is closed
it is understandable that you try to access your result set after
the conncetyion already close.
So while you need to iterate through your result set
in fact you closed connection inside the interation loop.

Normal flow is

while(result.next()){

String s = result.getString(1);

....
}

link.close() <--- when you are done with all loops
through result set

and you had it like that:
 


while(result.next()){

String s = result.getString(1);

....

link.close(); <--- closing connection within the ResultSet loop -- incorrect.
}






0
 

Author Comment

by:t38
ID: 35510623
Thanks for your input - I'll implement same later today and report back
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35510875
ok
0
 

Author Comment

by:t38
ID: 35513344
Thanks, that works fine.  CEJH, what was I doing wrong?  I'm just learning this stuff (you'd never guess...) and both of your input has been very helpful.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35513413
Well it was only a small error - you were closing the connection after reading only one row
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 1332 total points
ID: 35513417
Look a my post above - it explains very simply what you were doing wrong. Just closiung the connection iinside the resulktset traversal loop
0
 

Author Closing Comment

by:t38
ID: 35947493
Thanks for your assistance, thought it had already closed this.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35947711
:)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question