Query Results Program

This is the error message that I am getting with this program.  Can you help me?

--jGRASP exec: java QueryApplication

Exception in thread "main" java.lang.NullPointerException
      at QueryApplication.displayResultSet(QueryApplication.java:152)
      at QueryApplication.getTable(QueryApplication.java:141)
      at QueryApplication.<init>(QueryApplication.java:99)
      at QueryApplication.main(QueryApplication.java:231)

 ----jGRASP wedge2: exit code for process is 1.
 ----jGRASP: operation complete.

// QueryApplication.java

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.lang.ClassNotFoundException;

public class QueryApplication extends JFrame
{
       // database URL                              
   static final String DATABASE_URL = "jdbc:mysql://localhost:3306/books";
   
      private Connection connection;
      private Statement statement;
      private ResultSet resultSet;
      private ResultSetMetaData rsMetaData;
      private JTable table;
      private JComboBox inputQuery;
      private JButton submitQuery;
      private JTextField input;
      
      public QueryApplication()
      {
            super( "Select Query.  Click Submit to see results." );
            
            
      // establish connection to database  
      String driver = "com.mysql.jdbc.Driver";
            
            try
            {
      Class.forName( driver ).newInstance();

      connection = DriverManager.getConnection(
            DATABASE_URL, "deitel", "deitel" );
            } // end try
            catch( Exception ex )
            {
                  ex.printStackTrace();
            } // end catch
            String names[] =
            {"All authors", "All publishers", "All books",
            "A specific author", "A specific publisher"};
            
            //  if connected to database set up GUI
            inputQuery = new JComboBox( names );
            submitQuery = new JButton( "Submit Query" );
            submitQuery.addActionListener(
                        new ActionListener()
            {
            public void actionPerformed( ActionEvent event )
            {
                  getTable();
            } // end method actionPerformed
            }
      );
      JPanel topPanel = new JPanel();
      input = new JTextField( 20 );
      input.addActionListener( new ActionListener()
      {
            public void actionPerformed( ActionEvent event )
            {
            try
            {
                  String query = input.getText();
                  statement = connection.createStatement();
                  resultSet = statement.executeQuery( query );
                  displayResultSet( resultSet );
            } // end try
            catch( SQLException sqlex )
            {
                  sqlex.printStackTrace();
            }  // end catch
            } // end method actionPerformed
      });
            
      JPanel centerPanel = new JPanel();
      centerPanel.setLayout( new FlowLayout() );
      centerPanel.add( new JLabel
            ( "Enter query, author, or publisher: " ) );
      centerPanel.add( input );
      topPanel.setLayout( new BorderLayout() );
      topPanel.add( inputQuery, BorderLayout.NORTH );
      topPanel.add( centerPanel, BorderLayout.CENTER );
      topPanel.add( submitQuery, BorderLayout.SOUTH );
      table = new JTable( 4, 4 );
      Container c = getContentPane();
      c.setLayout( new BorderLayout() );
      c.add( topPanel, BorderLayout.NORTH );
      c.add( table, BorderLayout.CENTER );
      
      getTable();
      setSize( 500, 500 );
      setVisible( true );
      } // end constructor QueryApplication
private void getTable()
{
      try
      {
            int selection = inputQuery.getSelectedIndex();
            String query = null;
            
            switch( selection )
            {
                  case 0:
                        query = "SELECT" + "FROM Authors";
                        break;
                  case 1:
                        query = "SELECT" + "FROM Publishers";
                        break;
                  case 2:
                        query = "SELECT" + "FROM TITLES";
                        break;
                  case 3:
                        query = "SELECT Authors.LastName, Authors.FirstName, "
                        + "Titles.Title, Titles.Price, " + "Titles.ISBN FROM "
                        + "Titles INNER JOIN( AuthorISBN INNER JOIN Authors ON"
                        + " AuthorISBN.AuthorID = Authors.AuthorISBN) ON " 
                        + "Titles.ISBN = AuthorISBN.ISBN Where Authors.LastName"
                        +" = '" + input.getText() + "' ORDER BY " +
                        "Authors.LastName, Authors.FirstName ASC";
                        break;
                  case 4:
                        query = "SELECT Publishers.PublisherName, Titles.Title, " 
                        + "Titles.Price, Titles.ISBN FROM Titles INNER JOIN "
                        + "Publishers ON Publishers.PublisherID = "
                        + "Titles.PublisherID WHERE Publishers.PublisherName = '"
                        + input.getText() + "'ORDER BY Titles.Title ASC";
                        break;
            } // end switch
            statement = connection.createStatement();


            displayResultSet( resultSet );
            } // end try
            catch( SQLException sqlex )
            {
                  sqlex.printStackTrace();
            } // end catch
            } // end method getTable
private void displayResultSet( ResultSet rs )
                              throws SQLException
{
      // position first record
      boolean moreRecords = rs.next();
      // if there is no records display message
      if( !moreRecords )
      {
            JOptionPane.showMessageDialog( this,
                  "ResultSet containied no records" );
            setTitle( "No records to display" );
            return;
      } // end if
      Vector columnHeads = new Vector();
      Vector rows = new Vector();
      
      try
      {
            // get column heads
            ResultSetMetaData rsmd = rs.getMetaData();
            for( int i = 1; i <= rsmd.getColumnCount(); ++i )
            columnHeads.addElement( rsmd.getColumnName( i ) );
            
            // get row data
            do
            {
                  rows.addElement( getNextRow( rs, rsmd ) );
                  } while( rs.next() );
                  
                        // display table with ResultSet contents
            table = new JTable( rows, columnHeads );
            JScrollPane scroller = new JScrollPane( table );
            Container c = getContentPane();
            c.remove( 1 );
            c.add( scroller, BorderLayout.CENTER );
            c.validate();
            } // end try
            catch( SQLException sqlex )
            {
                  sqlex.printStackTrace();
            } // end catch
} // end method displayResultSet

private Vector getNextRow( ResultSet rs,
            ResultSetMetaData rsmd ) throws SQLException
{
      Vector currentRow = new Vector();
      
      for( int i = 1; i <= rsmd.getColumnCount(); ++i )
      
      switch( rsmd.getColumnType( i ) )
      {
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                  currentRow.addElement( rs.getString( i ) );
            case Types.INTEGER:
                  currentRow.addElement( rs.getLong( i ) );
                  break;
            case Types.REAL:
                  currentRow.addElement( new Float(
                  rs.getDouble( i ) ));
                  break;
            default:
                  System.out.println( "Type was: " +
                  rsmd.getColumnTypeName( i ) );
      } // end switch
      return currentRow;
} // end getNextRow
public void shutDown()
{
      try
      {
            connection.close();
      } // end try
      catch( SQLException sqlex )
      {
            System.err.println( "Unable to disconnect" );
            sqlex.printStackTrace();
      } // end catch
} // end method shutDown

public static void main( String[] args )
{
      final QueryApplication app = new QueryApplication();
      app.addWindowListener(
            new WindowAdapter()
      {
            public void windowClosing( WindowEvent event )
            {
                  app.shutDown();
                  System.exit( 0 );
            } // end method windowClosing
      }
      );
            
} // end method main
} // end class QueryApplication

Books.sql
assaultkittyAsked:
Who is Participating?
 
mlempertCommented:
In getTable() it does not look like you are executing the query so resultSet is not set to anything.
statement = connection.createStatement();

resultSet = statment.executeQuery(query);        //<-- Add this line.

displayResultSet( resultSet );

Open in new window

0
 
CEHJCommented:
>> at QueryApplication.displayResultSet(QueryApplication.java:152)

Which is line 152?
0
 
assaultkittyAuthor Commented:
This is line 152.
      
            boolean moreRecords = rs.next();
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
CEHJCommented:
Sounds like you don't have a valid ResultSet at that point
0
 
assaultkittyAuthor Commented:
Can you help me with the Result Set at the point?
0
 
mrcoffee365Commented:
Check if rs is null before you try to get a value from it.

So something like

boolean moreRecords = false;
if( rs != null ) moreRecords = rs.next();
0
 
assaultkittyAuthor Commented:
You found the error. Thanks.  Now, I have a SQL error after compiling.
0
 
CEHJCommented:
>>Now, I have a SQL error after compiling.

Please post all errors, stack traces etc.
0
 
assaultkittyAuthor Commented:
I fixed the error.  Here is the solution.  Thanks for the help.

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.lang.ClassNotFoundException;

public class QueryApplication extends JFrame
{
       // database URL                              
   static final String DATABASE_URL = "jdbc:mysql://localhost:3306/books";
   
      private Connection connection;
      private Statement statement;
      private ResultSet resultSet;
      private ResultSetMetaData rsMetaData;
      private JTable table;
      private JComboBox inputQuery;
      private JButton submitQuery;
      private JTextField input;
      
      public QueryApplication()
      {
            super( "Select Query.  Click Submit to see results." );
            
            
      // establish connection to database  
      String driver = "com.mysql.jdbc.Driver";
            
            try
            {
      Class.forName( driver ).newInstance();

      connection = DriverManager.getConnection(
            DATABASE_URL, "deitel", "deitel" );
            } // end try
            catch( Exception ex )
            {
                  ex.printStackTrace();
            } // end catch
            String names[] =
            {"All authors", "All publishers", "All books",
            "A specific author", "A specific publisher"};
            
            //  if connected to database set up GUI
            inputQuery = new JComboBox( names );
            submitQuery = new JButton( "Submit Query" );
            submitQuery.addActionListener(
                        new ActionListener()
            {
            public void actionPerformed( ActionEvent event )
            {
                  getTable();
            } // end method actionPerformed
            }
      );
      JPanel topPanel = new JPanel();
      input = new JTextField( 20 );
      input.addActionListener( new ActionListener()
      {
            public void actionPerformed( ActionEvent event )
            {
            try
            {
                  String query = input.getText();
                  statement = connection.createStatement();
                  resultSet = statement.executeQuery( query );
                  displayResultSet( resultSet );
            } // end try
            catch( SQLException sqlex )
            {
                  sqlex.printStackTrace();
            }  // end catch
            } // end method actionPerformed
      });
            
      JPanel centerPanel = new JPanel();
      centerPanel.setLayout( new FlowLayout() );
      centerPanel.add( new JLabel
            ( "Enter query, author, or publisher: " ) );
      centerPanel.add( input );
      topPanel.setLayout( new BorderLayout() );
      topPanel.add( inputQuery, BorderLayout.NORTH );
      topPanel.add( centerPanel, BorderLayout.CENTER );
      topPanel.add( submitQuery, BorderLayout.SOUTH );
      table = new JTable( 4, 4 );
      Container c = getContentPane();
      c.setLayout( new BorderLayout() );
      c.add( topPanel, BorderLayout.NORTH );
      c.add( table, BorderLayout.CENTER );
      
      getTable();
      setSize( 500, 500 );
      setVisible( true );
      } // end constructor QueryApplication
private void getTable()
{
      try
      {
            int selection = inputQuery.getSelectedIndex();
            String query = null;
            
            switch( selection )
            {
                  case 0:
                        query = "SELECT * FROM Authors";
                        break;
                  case 1:
                        query = "SELECT * FROM Publishers";
                        break;
                  case 2:
                        query = "SELECT * FROM TITLES";
                        break;
                  case 3:
                        query = "SELECT Authors.LastName, Authors.FirstName, "
                        + "Titles.Title, Titles.Price, " + "Titles.ISBN FROM "
                        + "Titles INNER JOIN( AuthorISBN INNER JOIN Authors ON"
                        + " AuthorISBN.AuthorID = Authors.AuthorISBN) ON " 
                        + "Titles.ISBN = AuthorISBN.ISBN Where Authors.LastName"
                        +" = '" + input.getText() + "' ORDER BY " +
                        "Authors.LastName, Authors.FirstName ASC";
                        break;
                  case 4:
                        query = "SELECT Publishers.PublisherName, Titles.Title, " 
                        + "Titles.Price, Titles.ISBN FROM Titles INNER JOIN "
                        + "Publishers ON Publishers.PublisherID = "
                        
                        + "Titles.PublisherID WHERE Publishers.PublisherName = '"
                        + input.getText() + "'ORDER BY Titles.Title ASC";
                        break;
            } // end switch
            statement = connection.createStatement();

            resultSet = statement.executeQuery(query);  

            displayResultSet( resultSet );
            } // end try
            catch( SQLException sqlex )
            {
                  sqlex.printStackTrace();
            } // end catch
            } // end method getTable
private void displayResultSet( ResultSet rs )
                              throws SQLException
{
      // position first record
      boolean moreRecords = rs.next();
      // if there is no records display message
      if( !moreRecords )
      {
            JOptionPane.showMessageDialog( this,
                  "ResultSet containied no records" );
            setTitle( "No records to display" );
            return;
      } // end if
      Vector columnHeads = new Vector();
      Vector rows = new Vector();
      
      try
      {
            // get column heads
            ResultSetMetaData rsmd = rs.getMetaData();
            for( int i = 1; i <= rsmd.getColumnCount(); ++i )
            columnHeads.addElement( rsmd.getColumnName( i ) );
            
            // get row data
            do
            {
                  rows.addElement( getNextRow( rs, rsmd ) );
                  } while( rs.next() );
                  
                        // display table with ResultSet contents
            table = new JTable( rows, columnHeads );
            JScrollPane scroller = new JScrollPane( table );
            Container c = getContentPane();
            c.remove( 1 );
            c.add( scroller, BorderLayout.CENTER );
            c.validate();
            } // end try
            catch( SQLException sqlex )
            {
                  sqlex.printStackTrace();
            } // end catch
} // end method displayResultSet

private Vector getNextRow( ResultSet rs,
            ResultSetMetaData rsmd ) throws SQLException
{
      Vector currentRow = new Vector();
      
      for( int i = 1; i <= rsmd.getColumnCount(); ++i )
      
      switch( rsmd.getColumnType( i ) )
      {
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                  currentRow.addElement( rs.getString( i ) );
                  break;
            case Types.INTEGER:
                  currentRow.addElement( new Long ( rs.getLong( i )) );
                  break;
            case Types.REAL:
                  currentRow.addElement( new Float(
                  rs.getDouble( i ) ));
                  break;
            default:
                  System.out.println( "Type was: " +
                  rsmd.getColumnTypeName( i ) );
      } // end switch
      return currentRow;
} // end getNextRow
public void shutDown()
{
      try
      {
            connection.close();
      } // end try
      catch( SQLException sqlex )
      {
            System.err.println( "Unable to disconnect" );
            sqlex.printStackTrace();
      } // end catch
} // end method shutDown

public static void main( String[] args )
{
      final QueryApplication app = new QueryApplication();
      app.addWindowListener(
            new WindowAdapter()
      {
            public void windowClosing( WindowEvent event )
            {
                  app.shutDown();
                  System.exit( 0 );
            } // end method windowClosing
      }
      );
            
} // end method main
} // end class QueryApplication
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.

All Courses

From novice to tech pro — start learning today.