?
Solved

Query Results Program

Posted on 2011-10-19
9
Medium Priority
?
315 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:assaultkitty
9 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 36996782
>> at QueryApplication.displayResultSet(QueryApplication.java:152)

Which is line 152?
0
 

Author Comment

by:assaultkitty
ID: 36996866
This is line 152.
      
            boolean moreRecords = rs.next();
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36996878
Sounds like you don't have a valid ResultSet at that point
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:assaultkitty
ID: 36997043
Can you help me with the Result Set at the point?
0
 
LVL 27

Expert Comment

by:mrcoffee365
ID: 36997452
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
 
LVL 2

Accepted Solution

by:
mlempert earned 2000 total points
ID: 37000818
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
 

Author Comment

by:assaultkitty
ID: 37002293
You found the error. Thanks.  Now, I have a SQL error after compiling.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 37003713
>>Now, I have a SQL error after compiling.

Please post all errors, stack traces etc.
0
 

Author Comment

by:assaultkitty
ID: 37003729
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

864 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