Results 1 to 5 of 5
  1. #1
    dxg
    dxg is offline Member
    Join Date
    Sep 2010
    Posts
    10
    Rep Power
    0

    Default Returning/Handling MySQL ResultSet

    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.



    Java Code:
    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;
    	}
    }

  2. #2
    doWhile is online now Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    7

    Default

    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

  3. #3
    dxg
    dxg is offline Member
    Join Date
    Sep 2010
    Posts
    10
    Rep Power
    0

    Default

    I like this ^^^
    Thank you.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,967
    Rep Power
    19

    Default

    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.

  5. #5
    dxg
    dxg is offline Member
    Join Date
    Sep 2010
    Posts
    10
    Rep Power
    0

    Default

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

Similar Threads

  1. Returning a ResultSet to custom JTable Model
    By Kenjitsuka in forum New To Java
    Replies: 9
    Last Post: 10-16-2010, 09:17 PM
  2. MySQL/JDBC Mysql query output
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-13-2009, 01:57 AM
  3. Help with Resultset
    By xxAlemanxx in forum JDBC
    Replies: 6
    Last Post: 06-24-2008, 11:09 AM
  4. MySql ResultSet
    By sandeeprao.techno in forum Advanced Java
    Replies: 7
    Last Post: 06-19-2008, 07:41 AM
  5. ResultSet example
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-20-2008, 08:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •