How can I do the following using Methods?

Question

This program is going to open a dialog box from where the user will make a selection from 7 choices. Depending on the choice selected, I am going to use SQL statements to extract the data from the DataBase and print the it onto the screen.

What I am wondering is, I want to do these steps each in their own methods, and just call them from main() rather than have a very LARGE Switch statement. I cant seem to figure out how to do this WITHOUT having to make a connection for EVERY method.

import javax.swing.*;
import java.sql.*;


public class PC_05__PopulationDatabase {

public static void main(String[] args) throws SQLException {
    final String DB_URL = "jdbc:derby:CityDB";

    // List of actions
    String[] arr = new String[7];
    arr[0] = "1) Sort Cities by Population - ASCENDING ORDER";
    arr[1] = "2) Sort Cities by Population - DESCENDING ORDER";
    arr[2] = "3) Sort Cities by Name";
    arr[3] = "4) Get Total Population of ALL Cities";
    arr[4] = "5) Get Average Population of ALL Cities";
    arr[5] = "6) Get Highest Population";
    arr[6] = "7) Get Lowest Population";

    try {
        // Open Connection
        Connection conn = DriverManager.getConnection(DB_URL);
        System.out.println("Connection created to Population Database.");

        // Statement and Result
        Statement stmt = conn.createStatement();
        String sqlStatement;
        ResultSet result;

        // Selection dialog
        Object userAction = JOptionPane.showInputDialog(null, "Select an action: ", "Actions", JOptionPane.PLAIN_MESSAGE, null, arr, null);
        String selection  = userAction.toString();

        int choice = Integer.parseInt(selection);

        // Selection Actions
        switch(choice) {
            case 0:

                break;
            case 1:

                break;
            case 2:

                break;
            case 3:

                break;
            case 4:

                break;
            case 5:

                break;
            case 6:

                break;
        }


        sqlStatement = "SELECT Description FROM Coffee";
        result = stmt.executeQuery(sqlStatement);


        // Close Connection
        conn.close();
        System.out.println("Connection closed.");
    }
    catch(Exception ex) {
        System.out.println("ERROR: " + ex.getMessage());
    }

}

public static void sortPopAscending() {

}

public static void sortPopDescending() {

}

public static void sortName() {

}

public static void totalPop() {

}

public static void avgPop() {

}

public static void highestPop() {

}

public static void lowestPop() {

}

}

score 1 · Accepted Answer · answered Dec 09 '13 at 03:14

1

Here's how I've solved similar problems:

Make Connection conn be an instance variable of your class. Don't make it static, or you'll kick yourself if you ever need more than one simultaneous connection.
Open the database connection
While you want the user to do stuff: have the user make a selection, then call a separate function which has the appropriate SQL for that selection. You're going to need something to sort out which function to call, be it a switch statement, anonymous class, etc.
Close the database connection

answered Dec 09 '13 at 03:14

musical_coder

3,886
3
15
18

Can you be more specific? I made "Connection conn" an instance variable. Now I just open the connection from main() again? What difference does this make? OR are you saying I make a connection in every method BUT close it within main, when finished? I'm confused. – fantom Dec 09 '13 at 03:44
1

Thought I was pretty specific, but again, open the connection just once in `main()`, use it for however many user selections you need, then close it. The beauty of this approach is that you don't have to make unnecessary open and close calls, which are expensive. Do, however, be sure to close your `Statement` and `ResultSet` objects immediately after each time you use them. – musical_coder Dec 09 '13 at 07:26
My bad man, I'm not familiar with Inheritance so I hit a bump when I got the idea to use Methods but was confused as to what will be recognized and what wont, inside the methods. EDIT -- and I close the Statement and ResultSet within EACH method? Cant I just close it ONCE at the end of Main()? – fantom Dec 09 '13 at 09:00
1

In this case, no. I'd close the PreparedStatement and ResultSet objects right away, however many times they occur. There are ways to reuse PreparedStatements (see http://stackoverflow.com/questions/2467125/reusing-a-preparedstatement-multiple-times), but for a simple program like this it's not worth the trouble. If my answer helped solve your problem, please mark it as accepted. Thanks! – musical_coder Dec 09 '13 at 15:30

How can I do the following using Methods?

1 Answers1