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

Question

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

I believe you're missing a quotation mark; `\"" + group...` should be `"\"" + group...` — Aarowaim, Apr 01 '14 at 17:29
No, that's not it. It's something to do with the logic of the select statement. Thanks anyway — user3058242, Apr 01 '14 at 17:32

score 0 · Accepted Answer · edited May 23 '17 at 10:26

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?

Yes the second solution works as I intended, thank you very much ;) — user3058242, Apr 01 '14 at 20:13

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

1 Answers1