• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Select not working with Access database

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
t38
Asked:
t38
  • 8
  • 4
  • 4
3 Solutions
 
CEHJCommented:
You're not creating any Statement and making the query to get a ResultSet
0
 
for_yanCommented:
Where is your executeQuery?
0
 
for_yanCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
CEHJCommented:
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
 
t38Author Commented:
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
 
CEHJCommented:
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
 
CEHJCommented:
And then of course there's no brace after link.close();
0
 
CEHJCommented:
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
 
for_yanCommented:
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
 
t38Author Commented:
Thanks for your input - I'll implement same later today and report back
0
 
CEHJCommented:
ok
0
 
t38Author Commented:
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
 
CEHJCommented:
Well it was only a small error - you were closing the connection after reading only one row
0
 
for_yanCommented:
Look a my post above - it explains very simply what you were doing wrong. Just closiung the connection iinside the resulktset traversal loop
0
 
t38Author Commented:
Thanks for your assistance, thought it had already closed this.
0
 
CEHJCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now