How to populate jComboBox using select statement and join table in Java

everyone.

I've got a little task to do but I'm a bit stuck, basically I have two tables in mySQL one called 'muscleGroup' and 'muscleExercise'. The first table is essentially a list of categories for different exercises containing two columns 'group_id' and 'groupName'. Than I'm having the exercise table, and as a foreign key I'm using the 'group_id' column from the 'muscleGroup' table.

What I'm trying do now is to have a 'muscleGroupComboBox' which contains the values of 'groupName' column, and than based on what you have selected I need to populate another comboBox with the exercises that belongs to this specific group of muscles.

Here is my method to populate the muscleGroup comboBox.

private void populateMuscleGroupComboBox() {
    String comboBoxQuery = "SELECT groupName FROM musclegroup";
    ResultSet rs;
    PreparedStatement statement;
    try {
        statement = con.prepareStatement(comboBoxQuery);
        rs = statement.executeQuery();
        while (rs.next()) {
            String groupName = rs.getString("groupName");
            groupComboBox1.addItem(groupName);
            groupComboBox2.addItem(groupName);
            groupComboBox3.addItem(groupName);
            groupComboBox4.addItem(groupName);
            groupComboBox5.addItem(groupName);
            groupComboBox6.addItem(groupName);

        }
    } catch (SQLException ex) {
        System.out.println(ex.toString());
    }
}

This is my second method to populate the exercise ComboBox.

private void populateExerciseComboBox(String group) {
    String comboBoxQuery = "SELECT muscleGroup.group_id, muscleGroup.groupName, muscleExercise.exerciseName FROM muscleGroup,muscleExercise WHERE muscleGroup.groupName =\"" + group + "\"";
    ResultSet rs;
    PreparedStatement statement;
    try {
        statement = con.prepareStatement(comboBoxQuery);
        rs = statement.executeQuery();
        while (rs.next()) {
            String exerciseName = rs.getString("muscleExercise.exerciseName");
            jComboBox7.addItem(exerciseName);

        }
    } catch (SQLException ex) {
        System.out.println(ex.toString());
    }
}

Here I call the methods

    populateMuscleGroupComboBox();
    populateExerciseComboBox(groupComboBox1.getSelectedItem().toString());

Now this kind of work but there is something wrong with my Select statement as it returns all the exercises from the exercise table, I know its to do with the WHERE clause but I'm not sure how to make it work.

   "SELECT muscleGroup.group_id, muscleGroup.groupName, muscleExercise.exerciseName FROM    muscleGroup,muscleExercise WHERE muscleGroup.groupName =\"" + group + "\"";

Help would be appriciated

Answers


You aren't restricting where muscleExercise and muscleGroup are the same group id... see the amended WHERE predicate below.

"SELECT muscleGroup.group_id, muscleGroup.groupName, muscleExercise.exerciseName FROM muscleGroup,muscleExercise WHERE muscleGroup.groupName =\"" + group + "\" and muscleExercise.group_id = muscleGroup.group_id";

OR... (preferred)

SELECT  muscleGroup.group_id, muscleGroup.groupName, muscleExercise.exerciseName 
FROM    muscleGroup
JOIN    muscleExercise USING(group_id)
WHERE   muscleGroup.groupName =\"" + group + "\" 

actually you should use prepared statements and parametrize group instead of just adding it in the query. How does Java's PreparedStatement work?


Need Your Help

Generating a random rule for property based test

erlang quickcheck triq

I am using Triq (erlang quickcheck) and I am having trouble generating a set of nice rules for my program.

UITableViewDataSource Done Loading Signal to UITableViewDelegate

iphone objective-c uitableview

I have separate files for the UITableViewDataSource and UITableViewDelegate