java GUI and database queries

Question

tnccjavaMatt 0 Newbie Poster

16 Years Ago

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();
 }
}

java:swing microsoft:access

2 Contributors
7 Replies
910 Views
4 Days Discussion Span
Latest Post 16 Years Ago Latest Post by tnccjavaMatt

Dani AI

Generated 10 Months Ago

A short, practical summary tied to the thread: identified the right problems — do not reference GUI instance fields from static main, and get the query text from the text field at the moment of submission. The common runtime problems in the later posts come from running blocking JDBC code on the Event Dispatch Thread (EDT), initializing the query too early, and adding the wrong component to the UI (the JScrollPane must be added, not the raw JTextArea).

Move all query execution out of main and out of field initializers. Run the database work on a background thread (for example a SwingWorker) so the GUI stays responsive, then update the JTextArea on the EDT when the data is ready. A minimal pattern:

new SwingWorker<String,Void>() {
  protected String doInBackground() throws Exception {
    // open connection, run query, build a single String result, return it
  }
  protected void done() {
    queryResults.setText(get()); // update GUI on EDT
  }
}.execute();

For display: if results are plain text, build them with a StringBuilder and call setText or append on the JTextArea. Wrap the text area in a JScrollPane and add that scroll pane to the panel. For tabular output prefer a JTable with a DefaultTableModel: use ResultSetMetaData to create column headers, then add each row as an Object[] to the model for clean column alignment and sorting support.

To save to a text file, write the same constructed string (the one placed into the text area) using BufferedWriter (try-with-resources or a finally block on older JDKs). Always close ResultSet, Statement, and Connection in finally or via try-with-resources. Security/caution: allowing arbitrary SQL from a text field is risky — consider restricting to read-only SELECT statements or validating input. Also note that the old JDBC-ODBC bridge used in these posts is not present on modern JDKs; a dedicated Access JDBC driver (for example UCanAccess) or a different DB is recommended for current Java versions.

All 7 Replies

Ezzaral 2,714 Posting Sage

16 Years Ago

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.

Ezzaral 2,714 Posting Sage

16 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tnccjavaMatt 0 Newbie Poster · Answer 1 · 2009-12-02T23:24:50+00:00

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);
  
 }
}

tnccjavaMatt 0 Newbie Poster · Answer 2 · 2009-12-02T23:49:14+00:00

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 2,714 Posting Sage Team Colleague Featured Poster · Answer 3 · 2009-12-03T00:10:46+00:00

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 0 Newbie Poster · Answer 4 · 2009-12-03T11:14:34+00:00

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 0 Newbie Poster · Answer 5 · 2009-12-06T09:18:09+00:00

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);
 }
}

java GUI and database queries

Dani AI

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers