java GUI and database queries

[tnccjavaMatt]

I have to write a program that creates a GUI that prompts a user to enter a query, then return the result to the same screen. All the examples I've found in my text book and other course material only shows how to account for a predetermined query. How do I account for a dynamically changing query?
The database I have to access is Microsoft Access and I am supposed to use SQL based queries.

Also, does the query code go inside main or an actionperformed method?

Code so far (GUI and database connection written thus far)

import javax.swing.*;
import javax.swing.border.*;
import java.awt.*;
import java.awt.GridLayout;
import java.awt.event.*;
import java.sql.*;


public class DataBase extends JFrame implements ActionListener 
{
 public DataBase()
 {
   
  JPanel northpanel = new JPanel();
  JPanel southpanel = new JPanel();
  JPanel eastpanel = new JPanel();
  eastpanel.setLayout(new GridLayout(1,2));
  
  JButton submitQuery = new JButton("Submit Query");
  JButton saveQuery = new JButton("Save Query");
  JTextField queryField = new JTextField(18);
 
  JScrollBar jscbHort = new JScrollBar(JScrollBar.HORIZONTAL);
  JScrollBar jscbVert = new JScrollBar(JScrollBar.VERTICAL);
  JTextArea queryResults = new JTextArea("This is the query result", 5, 20);
  southpanel.setBorder(new TitledBorder("Query Results"));
  
  submitQuery.addActionListener(this);
  saveQuery.addActionListener(this);
  
  northpanel.add(queryField);
  eastpanel.add(submitQuery);
  eastpanel.add(saveQuery);
  southpanel.add(queryResults);
  
  add(northpanel, BorderLayout.NORTH);
  add(southpanel, BorderLayout.SOUTH);
  add(eastpanel, BorderLayout.EAST);
  

 }
 
 public void actionPerformed(ActionEvent e)
 {
  	 
 }
 
  
 public static void main(String[] args)throws SQLException, ClassNotFoundException
 {
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  System.out.println("Drive loaded");
  
  Connection connection = DriverManager.getConnection("jdbc:odbc:skiclub");
  System.out.println("Database connected");

  
  
  DataBase frame = new DataBase();
  //set the window title
  frame.setTitle("JAVA ITP220 - DataBase Program");
  //set the window location
  frame.setLocationRelativeTo(null);
  //specify what happens when the close button is clicked
  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  //set the window size
  frame.setSize(600, 250);
  //display the window
  frame.setVisible(true);
  
  connection.close();
 }
}

[Ezzaral]

If you need to retrieve info about the columns in the result set, you can get that with ResultSet.getMetaData(). The simpler way would be to just loop the result and append your column values with the String.valueOf( rs.getObject(index) ) with whatever delimiter you want to use.

[tnccjavaMatt]

If you need to retrieve info about the columns in the result set, you can get that with ResultSet.getMetaData(). The simpler way would be to just loop the result and append your column values with the String.valueOf( rs.getObject(index) ) with whatever delimiter you want to use.

Okay, I'm starting to understand simple database programming, but I'm still running into issues and I simply do not understand the whys.
I posted my updated code (sections are commented out that may be right, just need some guidance).
I need to accept a user entered query (variable queryField declared at top - this is currently causing an error in line 84

ResultSet rsMembers = stmt.executeQuery(queryField);

Also, after query is accepted, how do I display results back to the gui (variable queryResults)?

import javax.swing.*;
import javax.swing.border.*;
import java.awt.*;
import java.awt.GridLayout;
import java.awt.event.*;
import java.sql.*;


public class DataBase extends JFrame implements ActionListener 
{
  JButton submitQuery = new JButton("Submit Query");
  JButton saveQuery = new JButton("Save Query");
  JTextField queryField = new JTextField(25);

 
 public DataBase()
 {
   
  JPanel northpanel = new JPanel();
  JPanel southpanel = new JPanel();
  JPanel eastpanel = new JPanel();
  eastpanel.setLayout(new GridLayout(1,2));
  
   
  JScrollBar jscbHort = new JScrollBar(JScrollBar.HORIZONTAL);
  JScrollBar jscbVert = new JScrollBar(JScrollBar.VERTICAL);
  JTextArea queryResults = new JTextArea("This is the query result", 5, 20);
  southpanel.setBorder(new TitledBorder("Query Results"));
  
  submitQuery.addActionListener(this);
  saveQuery.addActionListener(this);
  
  northpanel.add(queryField);
  eastpanel.add(submitQuery);
  eastpanel.add(saveQuery);
  southpanel.add(queryResults);
  
  add(northpanel, BorderLayout.NORTH);
  add(southpanel, BorderLayout.SOUTH);
  add(eastpanel, BorderLayout.EAST);
  

 }
 
 public void actionPerformed(ActionEvent e)
 {
  	 
 }
 
  
 public static void main(String[] args)throws SQLException, ClassNotFoundException
 {
  Connection connection;
  //String queryMembers = "select id, fname, mname, lname, phonenum, ability from members";
  //String queryBookings = "select memberid, tripid from BOOKINGS";
  //String querySkitrips = "select id, day, resort, capacity, booked from SKITRIPS";
  
  Statement stmt;
  
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   System.out.println("Drive loaded");
  }
  catch (java.lang.ClassNotFoundException e)
  {
   System.err.print("ClassNotFoundException: ");
	System.err.println(e.getMessage());
  }
  
  try
  {
   connection = DriverManager.getConnection("jdbc:odbc:skiclub");
   System.out.println("Database connected");
   stmt = connection.createStatement();
   //ResultSet rsMembers = stmt.executeQuery("SELECT id, fname, mname, lname, phonenum, ability FROM members");
   //ResultSet rsBookings = stmt.executeQuery("SELECT memberid, tripid FROM BOOKINGS");
   //ResultSet rsSkitrips = stmt.executeQuery("SELECT id, day, resort, capacity, booked FROM SKITRIPS");
 /*  
	ResultSet rsMembers = stmt.executeQuery(queryMembers);
	ResultSet rsBookings = stmt.executeQuery(queryBookings);
	ResultSet rsSkitrips = stmt.executeQuery(querySkitrips);
*/
	ResultSet rsMembers = stmt.executeQuery(queryField);
	ResultSetMetaData rsmdMembers = rsMembers.getMetaData();
	//ResultSetMetaData rsmdBookings = rsBookings.getMetaData();
	//ResultSetMetaData rsmdSkitrips = rsSkitrips.getMetaData();	
	
	
   stmt.close();
   connection.close();
   }
   catch(SQLException ex)
   {
    System.err.print("SQLException: ");
	 System.err.println(ex.getMessage());
   }

  DataBase frame = new DataBase();
  //set the window title
  frame.setTitle("JAVA ITP220 - DataBase Program");
  //set the window location
  frame.setLocationRelativeTo(null);
  //specify what happens when the close button is clicked
  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  //set the window size
  frame.setSize(600, 250);
  //display the window
  frame.setVisible(true);
  
 }
}

[Ezzaral]

You want to submit the text of "queryField", not the component itself.

[tnccjavaMatt]

You want to submit the text of "queryField", not the component itself.

Good catch, though even when I add

String query = queryField.getText();

I still get error: non-static variable query cannot be referenced from a static context

[Ezzaral]

main() shouldn't be accessing those variables directly. They are instance variables of your database class. Most of that code should be in your action listener for the submit button.

[tnccjavaMatt]

I'm getting a 'invalid use of null pointer' error. Why and how do I fix?
Also, how do I take the ResultSet and ResultSetMetaData query answers and display result in the GUI result pane and make it scrollable?

import javax.swing.*;
import javax.swing.border.*;
import java.awt.*;
import java.awt.GridLayout;
import java.awt.event.*;
import java.sql.*;


public class DataBase extends JFrame implements ActionListener 
{
  JButton submitQuery = new JButton("Submit Query");
  JButton saveQuery = new JButton("Save Query");
  JTextField queryField = new JTextField(25);
  String query = queryField.getText();

 
 public DataBase()
 {
  JPanel northpanel = new JPanel();
  JPanel southpanel = new JPanel();
  JPanel eastpanel = new JPanel();
  eastpanel.setLayout(new GridLayout(1,2));
  
  JScrollBar jscbHort = new JScrollBar(JScrollBar.HORIZONTAL);
  JScrollBar jscbVert = new JScrollBar(JScrollBar.VERTICAL);
  JTextArea queryResults = new JTextArea("This is the query result", 5, 20);
  southpanel.setBorder(new TitledBorder("Query Results"));
  
  submitQuery.addActionListener(this);
  saveQuery.addActionListener(this);
  queryField.addActionListener(this);
  
  northpanel.add(queryField);
  eastpanel.add(submitQuery);
  eastpanel.add(saveQuery);
  southpanel.add(queryResults);
  
  add(northpanel, BorderLayout.NORTH);
  add(southpanel, BorderLayout.SOUTH);
  add(eastpanel, BorderLayout.EAST);
 }
 
 public void actionPerformed(ActionEvent e)
 {
   Connection connection;
  //String queryMembers = "select id, fname, mname, lname, phonenum, ability from members";
  //String queryBookings = "select memberid, tripid from BOOKINGS";
  //String querySkitrips = "select id, day, resort, capacity, booked from SKITRIPS";
  
  Statement stmt;
  
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   System.out.println("Drive loaded");
  }
  catch (java.lang.ClassNotFoundException f)
  {
   System.err.print("ClassNotFoundException: ");
	System.err.println(f.getMessage());
  }
  
  try
  {
   connection = DriverManager.getConnection("jdbc:odbc:skiclub");
   System.out.println("Database connected");
   stmt = connection.createStatement();
   //ResultSet rsMembers = stmt.executeQuery("SELECT id, fname, mname, lname, phonenum, ability FROM members");
   //ResultSet rsBookings = stmt.executeQuery("SELECT memberid, tripid FROM BOOKINGS");
   //ResultSet rsSkitrips = stmt.executeQuery("SELECT id, day, resort, capacity, booked FROM SKITRIPS");
 /*  
	ResultSet rsMembers = stmt.executeQuery(queryMembers);
	ResultSet rsBookings = stmt.executeQuery(queryBookings);
	ResultSet rsSkitrips = stmt.executeQuery(querySkitrips);
*/
	ResultSet rsMembers = stmt.executeQuery(query);
	ResultSetMetaData rsmdMembers = rsMembers.getMetaData();
	//ResultSetMetaData rsmdBookings = rsBookings.getMetaData();
	//ResultSetMetaData rsmdSkitrips = rsSkitrips.getMetaData();	
	
	
   stmt.close();
   connection.close();
   }
   catch(SQLException ex)
   {
    System.err.print("SQLException: ");
	 System.err.println(ex.getMessage());
   }
	 
 }
 
  
 public static void main(String[] args)
 {
  DataBase frame = new DataBase();
  //set the window title
  frame.setTitle("JAVA ITP220 - DataBase Program");
  //set the window location
  frame.setLocationRelativeTo(null);
  //specify what happens when the close button is clicked
  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  //set the window size
  frame.setSize(600, 250);
  //display the window
  frame.setVisible(true);
 }
}

[tnccjavaMatt]

So I finally figured out how to get database queries to work. Its outputting to the screen correctly. How do I get it to output to gui result screen as well as a text file?

import javax.swing.*;
import javax.swing.border.*;
import java.awt.*;
import java.awt.GridLayout;
import java.awt.event.*;
import java.sql.*;


public class DataBase extends JFrame implements ActionListener 
{
  JButton submitQuery = new JButton("Submit Query");
  JButton saveQuery = new JButton("Save Query");
  JTextField queryField = new JTextField(25);
  JTextArea queryResults = new JTextArea(5, 20);
  JScrollPane scrollingResult = new JScrollPane(queryResults);

 
 public DataBase()
 {
  JPanel northpanel = new JPanel();
  JPanel southpanel = new JPanel();
  JPanel eastpanel = new JPanel();
  eastpanel.setLayout(new GridLayout(1,2));
    
  southpanel.setBorder(new TitledBorder("Query Results"));
  
  submitQuery.addActionListener(this);
  saveQuery.addActionListener(this);
  queryField.addActionListener(this);
  
  northpanel.add(queryField);
  eastpanel.add(submitQuery);
  eastpanel.add(saveQuery);
  southpanel.add(queryResults);
  
  add(northpanel, BorderLayout.NORTH);
  add(southpanel, BorderLayout.SOUTH);
  add(eastpanel, BorderLayout.EAST);
 }
 
 public void actionPerformed(ActionEvent e)
 {
  Connection connection;
    
  Statement stmt;
  
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   System.out.println("Drive loaded");
  }
  catch (java.lang.ClassNotFoundException f)
  {
   System.err.print("ClassNotFoundException: ");
	System.err.println(f.getMessage());
  }
  
  try
  {
   connection = DriverManager.getConnection("jdbc:odbc:skiclub");

   System.out.println("Database connected");
   stmt = connection.createStatement();
   String query = queryField.getText();
   
	ResultSet resultSet = stmt.executeQuery(query);
	
	ResultSetMetaData rsMetaData = resultSet.getMetaData();
	for (int i = 1; i <= rsMetaData.getColumnCount(); i++)
	 {
	  System.out.printf("%-12s\t", rsMetaData.getColumnName(i));
	  // gather and format column names for display
     //somestringvariable += String.format("%-30s\t", rsMetaData.getColumnName(i));
     // gather carriage-return and newline character display     
	  //somestringvariable += "\r\n";
	 } 
	System.out.println();
	
	while (resultSet.next())
	{
	 for (int i = 1; i <= rsMetaData.getColumnCount(); i++)
	  {
	   System.out.printf("%-12s\t", resultSet.getObject(i));
	   // gather and format data rows for display
      //someotherstringvariable += String.format("%-30s\t", resultSet.getObject(i));
      // gather carriage-return and newline character for row display
      //someotherstringvariable += "\r\n";
	  }
	 System.out.println();
	}
	
   stmt.close();
   connection.close();
   }
   catch(SQLException ex)
   {
    System.err.print("SQLException: ");
	 System.err.println(ex.getMessage());
   }
 }
 
  
 public static void main(String[] args)
 {
  DataBase frame = new DataBase();
  //set the window title
  frame.setTitle("JAVA ITP220 - DataBase Program");
  //set the window location
  frame.setLocationRelativeTo(null);
  //specify what happens when the close button is clicked
  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  //set the window size
  frame.setSize(600, 250);
  //display the window
  frame.setVisible(true);
 }
}