Returning/Handling MySQL ResultSet

[dxg]

Hello all,

I have two classes which interact with each other.
Class A queries a MySQL DB and creates a ResultSet.
Class B uses the ResultSet and displays it via GUI.


I am looking for the most efficient (fast) way of handling this situation.
Currently I obtain a ResultSet from Class A, using Class B. Following this I cycle through the ResultSet and display appropriate information.

Would it be wiser (faster) to leave the ResultSet in Class A and return an ArrayList rather than the entire ResultSet?

Please see code below if necessary.

Thank you in advance for any support/information.

public class DBQuery
{
	private final String DRIVER = "com.mysql.jdbc.Driver";
	private final String DATABASE_URL = "jdbc:mysql://localhost/javadb";

	public DBQuery(){}

	//query database
	public ArrayList<String> query(String query, String temporaryString)
	{
		Connection connection = null; //manages connection
		Statement statement = null; //query statement
		ResultSet resultSet = null; //manages results
		
		ArrayList<String> temporaryArray = new ArrayList<String>();
		
		//connect and query database
		try
		{
			//load driver class
			Class.forName( DRIVER );
			
			//establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, "root", "");
				
			//create statement for querying database;
			statement = connection.createStatement();
			
			//query database
		 	resultSet = statement.executeQuery(query);
		 	
		 	while (resultSet.next()) 
		 	{	
		 		temporaryArray.add(resultSet.getString(temporaryString));
		 	}
		}
		catch ( SQLException sqlException )
		{
			System.out.println("SQLException triggered.");
			sqlException.printStackTrace();
		}
		catch ( ClassNotFoundException classNotFound )
		{
			System.out.println("ClassNotFoundException triggered.");
			classNotFound.printStackTrace();
		}
		finally
		{
			try
			{
				resultSet.close();
				statement.close();
				connection.close();
			}
			catch ( Exception exception )
			{
				System.out.println("Exception triggered in finally clause.");
				exception.printStackTrace(); 
			}
		}
		return temporaryArray;
	}







public class GUI extends JFrame implements ActionListener
{
	private static final long serialVersionUID = 1L; 
	private JComboBox countryList = new JComboBox();
	private JTextArea textArea;

	public GUI() 
	{
		super("Airport locator v1.0");
		setLayout(new FlowLayout()); 
		
		//declare local variables
		JLabel label;
		JButton search;
		JScrollPane scrollPane;
		
		//initialize variables
		label = new JLabel("Select Country:");
		search = new JButton("Search");
		textArea = new JTextArea(10, 50);
		scrollPane = new JScrollPane(textArea); 
		countryList = addItems(countryList, "xxxxxxxxxxx", getData("SELECT DISTINCT country FROM geoname ORDER BY country ASC", "country"));
		
		//associate action with selected objects
		search.addActionListener(this);

		//add components to frame
		add(label);
		add(countryList);
		add(search);
		add(scrollPane);
	}
	
	//Perform action once Search is pressed
	public void actionPerformed(ActionEvent ae)
	{
		String selectedCountry;
		selectedCountry = (String)countryList.getSelectedItem();
		textArea.setText("");
		textArea = addItems(textArea, false, getData("SELECT asciiname FROM geoname WHERE country = '" + selectedCountry + "' ORDER BY asciiname ASC", "asciiname"));
	}

	//Insert ArrayList objects into JTextArea
	private JTextArea addItems(JTextArea jTextArea, boolean editable, ArrayList<String> arrayList)
	{
		String newline = "\n";
		for (int i = 0; i < arrayList.size(); i++) 
			jTextArea.append(arrayList.get(i) + newline); 
		if(!editable)	
			jTextArea.setEditable(false);
		
		return jTextArea;
	}
	
	//Insert ArrayList objects into JComboBox
	private JComboBox addItems(JComboBox jComboBox, String width, ArrayList<String> arrayList)
	{
		jComboBox.setPrototypeDisplayValue(width);
		for (int i = 0; i < arrayList.size(); i++) 
			jComboBox.addItem(arrayList.get(i));
		return jComboBox;
	}
	
	//Query MySQL and return resultSet in ArrayList format
	private ArrayList<String> getData(String query, String column)
	{
		DBQuery dbQuery = new DBQuery();
		ArrayList<String> arrayList = dbQuery.query(query, column);
		return arrayList;
	}
}

[doWhile]

Would it be wiser (faster) to leave the ResultSet in Class A and return an ArrayList rather than the entire ResultSet?

I suggest you try it and profile the two to answer this question. My suggestion would be to remove the SQL from the gui entirely. This can turn into a very ugly maintenance problem, restricts your ability to reuse code, and really tightly couples one thing to another. An alternative would be to hide all the querying in the objects that need to query (separate from a GUI), and retrieve the data as objects. My .02

[dxg]

I like this ^^^
Thank you.

[Tolls]

Might I suggest a single query that returns a List of some class that contains both the country and the asciiname values? Then add this list to the combo box ratehr than just the Strings. Saves a round trip to the db.

Also, as pointed out, you really don't want to pass ResultSets around. That's generally a source of bugs. ResultSets should be closed where they're opened (in general), otherwise it's easy to forget to close them and you end up with resource leaks.

Other comments. I'd learn how to use PreparedStatements. If you do the change I suggest in the first paragraph then you won't actually need one, but you shouldn't be concatenating your SQL together. This would mean changing your structure slightly. Though, going with the change above, you'd have to anyway.

It's good that you have split the GUI from the db work, but your GUI shouldn't have queries in it. Give your db layer a getCountryList() method. In fact, create a Country class (country, asciiname) as your model and a CountryDao for your data access.

[dxg]

I do not like the query statements in the GUI class either, thank you for the exceptional suggestions; and thorough analysis. :)