java - 如何从一列中选择不同的类别数据？

Question

我的数据库中有一个名为 product 的表，包含列 name price 和 category，以及有 6 个项目与 2 个主要类别相关。比方说:

+----------+-------+-----------+
|  name    | price |  category |
+----------+-------+-----------+
|  mango   |  $58  |  fruits   |
|  avocado |  $74  |  fruits   |
|  apple   |  $20  |  fruits   |
|  carrot  |  $35  | vegetable |
|  onions  |  $40  | vegetable |
+----------+-------+-----------+

我想制作2个JComboBox，首先选择一个类别，然后再选择另一个JComboBox来加载所有相关名称(如果我选择fruit，然后其他 JComboBox 将显示所有水果项)。

这是我的代码，它从数据库中选择所有 name 并将其加载到组合框中。

  ArrayList<String> list = new ArrayList<String>();

        try {
            DBconnector db =  new DBconnector();
            con=  db.connect();
            String qr = "SELECT name FROM product";
            PreparedStatement stm = con.prepareStatement(qr);
            ResultSet rs = stm.executeQuery(qr);

            while(rs.next()){
                String name = rs.getString("name");
                //add group list to the arraylist
                list.add(name);
            }
            rs.close();

            //Populate the comboBox
            if(!list.isEmpty()){
            DefaultComboBoxModel model = new DefaultComboBoxModel(list.toArray());
            combItems.setModel(model);
            }else{
                System.out.println("List is empty...");
            }
        } catch (SQLException ex) {
            System.out.println("Data not retrieved");
            ex.printStackTrace();

        }
        return list;

有人可以帮帮我吗？

Answer 1

这使用了 where 子句选项和 distinct 并显示了如何将两个组合框与项目监听器连接起来:

            JFrame frm = new JFrame();
            JComboBox catBox = new JComboBox();
            final Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/sample","app","app");
            String getcatsQuery = "SELECT DISTINCT category from PRODUCT";
            PreparedStatement getcatsStatement = con.prepareStatement(getcatsQuery);
            ResultSet rs = getcatsStatement.executeQuery();
            List<String> list = new ArrayList<>();
            while (rs.next()) {
                String cat = rs.getString("category");
                //add group list to the arraylist
                list.add(cat);
            }
            rs.close();
            System.out.println("list = " + list);
            DefaultComboBoxModel catsModel = new DefaultComboBoxModel(list.toArray());
            catBox.setModel(catsModel);
            final JComboBox prodBox = new JComboBox();
            prodBox.addItem("             ");
            catBox.addItemListener(e -> {
                try {
                    String getprodsQuery = "SELECT name from PRODUCT where category=?";
                    PreparedStatement getprodsStatement = con.prepareStatement(getprodsQuery);
                    getprodsStatement.setString(1, catBox.getSelectedItem().toString());
                    ResultSet rs2 = getprodsStatement.executeQuery();
                    List<String> list2 = new ArrayList<>();
                    while (rs2.next()) {
                        String name = rs2.getString("name");
                        //add group list to the arraylist
                        list2.add(name);
                    }
                    rs.close();
                    DefaultComboBoxModel prodsModel = new DefaultComboBoxModel(list2.toArray());
                    prodBox.setModel(prodsModel);

                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            });
            catBox.setSelectedIndex(0);
            frm.setLayout(new FlowLayout());
            frm.add(catBox);
            frm.add(prodBox);
            frm.pack();
            frm.setVisible(true);