I'm trying to execute this code to open a java GUI to run queries on the oracle express database I have set up but at the actionPerformed() class, I keep getting java.sql.SQLException errors.

What I would like for it to do is when you hit the button, that table from the database is displayed. Also the JTextField (jtf) across the top part of the GUI is supposed to take custom queries and be executed when ENTER is hit. Any steps in the right direction is much appreciated.

Also for security purposes, at DriverManager.getConnection(......), I took out the URL and login info. If it's necessary to have let me know.

import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class myguidemo extends JFrame implements ActionListener{

	//variables for oracle
	Connection connection;
	Statement stmt;
	String query = "SELECT * FROM DOCTOR";
	ResultSet results, execute;
	ResultSetMetaData md;
	int numrows, numcols;
	AbstractTableModel dataModel;
	Object [][] data;
		
	//variables for GUI
	JTextField jtf;
	JTable jtb;
	JPanel jp;
	JScrollPane jsp;
	JButton button1, button2, button3, button4, button5;
	JLabel label;

		public myguidemo() throws Exception{
                	
                		//driver and connection
                		Class.forName("oracle.jdbc.driver.OracleDriver");
                		connection = DriverManager.getConnection("jdbc:oracle:thin:@x.x.x.x:1521:XE","user","pass");
                		
                		//create the GUI
                		jtf = new JTextField("Enter SQL command here");
                                add(jtf,BorderLayout.NORTH);
                		jtb = new JTable();
                                add(jtb,BorderLayout.CENTER);
                                jp = new JPanel();
                		add(jp,BorderLayout.SOUTH);
                                
                                
                		//query the oracle database, program defaults to show the DOCTOR table
                                stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                                results = stmt.executeQuery(query);
                                
                                
                                //get the number of columns and rows for JTable
                                md = results.getMetaData();
                		numcols = md.getColumnCount();
                		numrows = 0;
                		while(results.next())
                			numrows++;
                		results.beforeFirst();
                		data = new Object[numrows][numcols];
                		results.next();
                		for(int i=0; i<numrows; i++){
                			for(int j=1; j<=numcols; j++){
                				data[i][j-1] = results.getString(j);
                			}
                			results.next();
                		}
                                
                                //get the data from oracle
                		dataModel = new AbstractTableModel(){
                                	public int getColumnCount() {return numcols;}
                                	public int getRowCount() {return numrows;}
                                	public Object getValueAt(int row, int col) {return data[row][col];}
                                };
                                
                                //put the query output in the JTable and add scrolling
                                jtf.addActionListener(this);
                                jtb = new JTable(dataModel);
                                jsp = new JScrollPane(jtb);
                                add(jsp);
                                label = new JLabel("");
                                
                                //names buttons
                                button1 = new JButton("Doctor");
                                button2 = new JButton("Medication");
                                button3 = new JButton("Patient");
                                button4 = new JButton("Prescription");
                                button5 = new JButton("Supplier");

                                //add buttons to bottom panel
                                jp.add(button1);
                                jp.add(button2);
                                jp.add(button3);
                                jp.add(button4);
                                jp.add(button5);
                                //use label to check that query variable works properly
                                jp.add(label);

                                //makes clicking the button an ActionEvent
                                button1.addActionListener(this);
                                button2.addActionListener(this);
                                button3.addActionListener(this);
                                button4.addActionListener(this);
                                button5.addActionListener(this);

                                //set title, size, and close
                                setTitle("Java/Oracle");
                                setSize(800, 600);
                                setDefaultCloseOperation(EXIT_ON_CLOSE);
                                setVisible(true);

                }

                public void actionPerformed(ActionEvent e){
                	if (e.getSource() == jtf){
                		query = jtf.getText();
                		label.setText(query);
                                jtf.setText("");
                        }
                        
                        
                        if (e.getSource() == button1){
                        	jtb.setBackground(Color.RED);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM DOCTOR";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button2){
                        	jtb.setBackground(Color.GREEN);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM MEDICATION";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button3){
                        	jtb.setBackground(Color.BLUE);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM PATIENT";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button4){
                        	jtb.setBackground(Color.ORANGE);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM PRESCRIPTION";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button5){
                        	jtb.setBackground(Color.YELLOW);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM SUPPLIER";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                }

                public static void main(String args[]) throws Exception{
                	new myguidemo();  
                }
}

What is necessary is for you to provide exact errors you getting from SQLException so you better catch that exception and print stack. Also your current program arrangement is very bad. You should move that database connectivity to its own class, and don't forget to close the connection when you do not need it (which you didn't do and that is more likely your SQLException about)

Sorry for the messy code. I just started using java last week and rushed into this. This is the error I get when I try to compile:

c:\java>javac myguidemo.java
myguidemo.java:127: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:136: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:145: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:154: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:163: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
5 errors

c:\java>

Is it because what I'm trying to get the buttons to do is written wrong? Thanks for the help.

Would putting the connection code in a separate class require creating another java file or is it possible to have it all in one .java file?

You should put it in separated class. Have look for example at this JSP tutorial which shows databases connectivity from web application. However the idea is same

After I looked through your guide, I moved the connection code to the separate classes. I still get the same errors as I was before:

c:\java>javac myguidemo.java
myguidemo.java:159: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:168: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:177: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:186: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
myguidemo.java:195: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                                execute = stmt.executeQuery(query);
                                                           ^
5 errors

c:\java>

I'm not quite sure if I am using the proper syntax on the buttons, since that's where the errors are occurring. Here is what my code looks like now(again I took out the IP and login info for security reasons):

import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class myguidemo extends JFrame implements ActionListener{

	//variables for oracle
	Connection connection;
	Statement stmt;
	String query = "SELECT * FROM DOCTOR"; //default query displayed when program starts
	ResultSet results, execute;
	ResultSetMetaData md;
	int numrows, numcols;
	AbstractTableModel dataModel;
	Object [][] data;
		
	//variables for GUI
	JTextField jtf;
	JTable jtb;
	JPanel jp;
	JScrollPane jsp;
	JButton button1, button2, button3, button4, button5;
	JLabel label;

		public myguidemo() throws Exception{
                	
                		//driver and connection
                		//Class.forName("oracle.jdbc.driver.OracleDriver");
                		//connection = DriverManager.getConnection("jdbc:oracle:thin:@x.x.x.x","user","password");
                		
                		//create the GUI
                		jtf = new JTextField("Enter SQL command here");
                                add(jtf,BorderLayout.NORTH);
                		jtb = new JTable();
                                add(jtb,BorderLayout.CENTER);
                                jp = new JPanel();
                		add(jp,BorderLayout.SOUTH);
                                
                                
                		//query the oracle database, program defaults to show the DOCTOR table
                                stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                                results = stmt.executeQuery(query);
                                
                                
                                //get the number of columns and rows for JTable
                                md = results.getMetaData();
                		numcols = md.getColumnCount();
                		numrows = 0;
                		while(results.next())
                			numrows++;
                		results.beforeFirst();
                		data = new Object[numrows][numcols];
                		results.next();
                		for(int i=0; i<numrows; i++){
                			for(int j=1; j<=numcols; j++){
                				data[i][j-1] = results.getString(j);
                			}
                			results.next();
                		}
                                
                                //get the data from oracle
                		dataModel = new AbstractTableModel(){
                                	public int getColumnCount() {return numcols;}
                                	public int getRowCount() {return numrows;}
                                	public Object getValueAt(int row, int col) {return data[row][col];}
                                };
                                
                                //put the query output in the JTable and add scrolling
                                jtf.addActionListener(this);
                                jtb = new JTable(dataModel);
                                jsp = new JScrollPane(jtb);
                                add(jsp);
                                label = new JLabel("");
                                
                                //names buttons
                                button1 = new JButton("Doctor");
                                button2 = new JButton("Medication");
                                button3 = new JButton("Patient");
                                button4 = new JButton("Prescription");
                                button5 = new JButton("Supplier");

                                //add buttons to bottom panel
                                jp.add(button1);
                                jp.add(button2);
                                jp.add(button3);
                                jp.add(button4);
                                jp.add(button5);
                                //use label to check that query variable works properly
                                jp.add(label);

                                //makes clicking the button an ActionEvent
                                button1.addActionListener(this);
                                button2.addActionListener(this);
                                button3.addActionListener(this);
                                button4.addActionListener(this);
                                button5.addActionListener(this);

                                //set title, size, and close
                                setTitle("Java/Oracle");
                                setSize(800, 600);
                                setDefaultCloseOperation(EXIT_ON_CLOSE);
                                setVisible(true);
                                
                                stmt.close();
                                //connection.close();

                }
                
                
                
                //Opens the database connection
                public Connection getConnection(){
                	Connection conn = null;
                	try{
                		conn = DriverManager.getConnection("jdbc:oracle:thin:@x.x.x.x","user","password");
                	}
                	catch (SQLException e){
                		System.out.println("Could not connect");
                		e.printStackTrace();
                	}
                	return conn;
                }
                //Close the open connection
                public void putConnection(Connection conn){
                	if(conn != null){
                		try{
                			conn.close();
                		}
                		catch (SQLException e){
                			System.out.println("Enable to close connection");
                			e.printStackTrace();
                		}
                	}
                }

                
                
                public void actionPerformed(ActionEvent e){
                	if (e.getSource() == jtf){
                		query = jtf.getText();
                		label.setText(query);
                                jtf.setText("");
                        }
                        
                        
                        if (e.getSource() == button1){
                        	jtb.setBackground(Color.RED);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM DOCTOR";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button2){
                        	jtb.setBackground(Color.GREEN);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM MEDICATION";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button3){
                        	jtb.setBackground(Color.BLUE);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM PATIENT";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button4){
                        	jtb.setBackground(Color.ORANGE);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM PRESCRIPTION";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                        if (e.getSource() == button5){
                        	jtb.setBackground(Color.YELLOW);
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM SUPPLIER";
                        	label.setText(query);
                        	jtf.setText("");
                        	//execute query
                        	execute = stmt.executeQuery(query);
                        }
                }

                public static void main(String args[]) throws Exception{
                	new myguidemo();  
                }
}

The error is pretty obvious and has nothing to do with sql:

unreported exception java.sql.SQLException; must be caught or declared to be thrown

You are calling a method that throws an exception, and you don't catch it.
I mean why did you surround the DriverManager.getConnection with a try-catch?

Alright. I managed to clear up my previous error for the most part. Now my code compiles w/o any error.

Another question I had is that I'm trying to get the buttons, when pressed, to display the data from the query variable. I created a new method handleButton in an attempt to execute the query. I'm having trouble either passing the variable to the method or I'm going about it all wrong. Any steps in the right direction would be really appreciated(the main focus now is under the methods actionPerformed and handleButton)

Here is the updated code:

import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class myguidemo extends JFrame implements ActionListener{

	//variables for oracle
	Connection connection;
	Statement stmt;
	String query = "SELECT * FROM DOCTOR"; //default query displayed when program starts
	ResultSet results;
	ResultSetMetaData md;
	int numrows, numcols;
	AbstractTableModel dataModel;
	Object [][] data;
		
	//variables for GUI
	JTextField jtf;
	JTable jtb;
	JPanel jp;
	JScrollPane jsp;
	JButton button1, button2, button3, button4, button5;
	JLabel label;

		public myguidemo(){ //constructor
                	
			try{	
                		//driver and connection
                		Class.forName("oracle.jdbc.driver.OracleDriver");
                		connection = DriverManager.getConnection("jdbc:oracle:thin:@x.x.x.x", "USER", "PASS");
                	}
                	catch(ClassNotFoundException cnfex){System.out.print("Failed to load JDBC driver");}
                	catch(SQLException sqlex){System.out.print("Unable to connect to server");}
                	catch(Exception e){e.printStackTrace();}
                	
                		
                	try{
                                
                		//query the oracle database, program defaults to show the DOCTOR table
                                stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                                results = stmt.executeQuery(query);
                                
                                
                                //get the number of columns and rows for JTable
                                md = results.getMetaData();
                		numcols = md.getColumnCount();
                		numrows = 0;
                		while(results.next())
                			numrows++;
                		results.beforeFirst();
                		data = new Object[numrows][numcols];
                		results.next();
                		for(int i=0; i<numrows; i++){
                			for(int j=1; j<=numcols; j++){
                				data[i][j-1] = results.getString(j);
                			}
                			results.next();
                		}
                	}
                	catch(Exception ee){ee.printStackTrace();}
                                
                                //get the data from oracle
                		dataModel = new AbstractTableModel(){
                                	public int getColumnCount() {return numcols;}
                                	public int getRowCount() {return numrows;}
                                	public Object getValueAt(int row, int col) {return data[row][col];}
                                };
                                
                                
                                //create the GUI
                		jtf = new JTextField("Enter SQL command here");
                                add(jtf,BorderLayout.NORTH);
                		jtb = new JTable();
                                add(jtb,BorderLayout.CENTER);
                                jp = new JPanel();
                		add(jp,BorderLayout.SOUTH);
                		
                		
                                //put the query output in the JTable and add scrolling
                                jtf.addActionListener(this);
                                jtb = new JTable(dataModel);
                                jsp = new JScrollPane(jtb);
                                add(jsp);
                                label = new JLabel("Displaying: " + query);
                                
                                //names buttons
                                button1 = new JButton("Doctor");
                                button2 = new JButton("Medication");
                                button3 = new JButton("Patient");
                                button4 = new JButton("Prescription");
                                button5 = new JButton("Supplier");

                                //add buttons to bottom panel
                                jp.add(button1);
                                jp.add(button2);
                                jp.add(button3);
                                jp.add(button4);
                                jp.add(button5);
                                //use label to check that query variable works properly
                                jp.add(label);

                                //makes clicking the button an ActionEvent
                                button1.addActionListener(this);
                                button2.addActionListener(this);
                                button3.addActionListener(this);
                                button4.addActionListener(this);
                                button5.addActionListener(this);

                                //set title, size, and close
                                setTitle("Java/Oracle");
                                setSize(800, 600);
                                setDefaultCloseOperation(EXIT_ON_CLOSE);
                                setVisible(true);
                }
                
                //method to handle buttons and textfield
                public void handleButton(String abc) throws Exception{
                	try{
                		results = stmt.executeQuery(query);
                	}
                	catch(Exception ab){System.out.print("Unable to handle button");}
                }
                
                public void actionPerformed(ActionEvent e){
                	if (e.getSource() == jtf){
                		query = jtf.getText();
                		label.setText("Displaying: " + query);
                                jtf.setText("");
                        }
                        if (e.getSource() == button1){
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM DOCTOR";
                        	label.setText("Displaying: " + query);
                        	jtf.setText("");
                        	//execute query
                        	try{
                        		handleButton(query);
                        	}
                        	catch(Exception ex){System.out.print("Unable to handle query");}
                        }
                        if (e.getSource() == button2){
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM MEDICATION";
                        	label.setText("Displaying: " + query);
                        	jtf.setText("");
                        	//execute query
                        	try{
                        		handleButton(query);
                        	}
                        	catch(Exception ex){System.out.print("Unable to handle query");}
                        }
                        if (e.getSource() == button3){
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM PATIENT";
                        	label.setText("Displaying: " + query);
                        	jtf.setText("");
                        	//execute query
                        	try{
                        		handleButton(query);
                        	}
                        	catch(Exception ex){System.out.print("Unable to handle query");}
                        }
                        if (e.getSource() == button4){
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM PRESCRIPTION";
                        	label.setText("Displaying: " + query);
                        	jtf.setText("");
                        	//execute query
                        	try{
                        		handleButton(query);
                        	}
                        	catch(Exception ex){System.out.print("Unable to handle query");}
                        }
                        if (e.getSource() == button5){
                        	//put query in JLabel in JPanel to verify button works, reset JTextField text
                        	query = "SELECT * FROM SUPPLIER";
                        	label.setText("Displaying: " + query);
                        	jtf.setText("");
                        	//execute query
                        	try{
                        		handleButton(query);
                        	}
                        	catch(Exception ex){System.out.print("Unable to handle query");}
                        }
                }

                public static void main(String args[]){
                	new myguidemo();  
                }
}

You misunderstand concept in the tutorial that I linked. This should be out too

public myguidemo(){ //constructor
                	
			try{	
                		//driver and connection
                		Class.forName("oracle.jdbc.driver.OracleDriver");
                		connection = DriverManager.getConnection("jdbc:oracle:thin:@x.x.x.x", "USER", "PASS");
                	}
                	catch(ClassNotFoundException cnfex){System.out.print("Failed to load JDBC driver");}
                	catch(SQLException sqlex){System.out.print("Unable to connect to server");}
                	catch(Exception e){e.printStackTrace();}
                	
                		
                	try{
                                
                		//query the oracle database, program defaults to show the DOCTOR table
                                stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                                results = stmt.executeQuery(query);

What you should only do is call an method from your Java-to-DB class. That class would acquire connection from connection manager, execute query in called class and return result back to GUI in appropriate format ready to by displayed.

Thanks for your help! My initial problems are all solved. I'm very close to a finished product. Thanks guys!!!!

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.