选择Query并将出现在Netbeans和mysql中的组合框中

Question

我在此代码上遇到问题:
String charitysql=“select wardName,charityRoomID FROM tbl_charityward,tbl_charityroom2
where tbl_charityward.charitywardid=tbl_charityroom2.charitywardid”；


 try { 

        pst = conn.prepareStatement(charitysql);
        rs = pst.executeQuery();

            while (rs.next()) {
                String wardname = rs.getString("wardName");
                cb_ward2.addItem(wardname);
                String roomid = rs.getString("charityRoomID");
                cb_room2.addItem(roomid);                    

            }

        }
        catch(Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }

这是我的表结构:
tbl_charityward
charityWardID int NOT NULL AUTO_INCREMENT，
wardName varchar(20)、
status varchar(20)、
PRIMARY KEY(charityWardID)

tbl_charityroom2
charityRoomID INT NOT NULL AUTO_INCREMENT，
状态varchar(20)，
charityWardID INT，
主键（charityRoomID,charityWardID)，
外键（charityWardID）引用tbl_charityward(charityWardID)

这是我的表，其值为:
tbl_charityward
+----------+--------+-------+
charityWardID wardName status
+------------+------+
1......外科手术..打开..
......2.......Obygine..打开..
......3......儿科..开..
+----------+--------+--------+


tbl_charityroom2
+----------+--------+
charityRoomID status charitywardid
+-----------+--------+
.....1......Open...1......1......
.....2......Open...1......
.....3......Open...2......
+

我有2个组合框:
cb_ward2=其中包含wardname
cb_room2=其中包含charityroomid

如果从cb_ward2中选择一个wardName，则cb_room2将显示相应的charityroomid。

示例:
我选择surgical并且charityroomid=1,2将出现在CB_ROOM2上，
并且当我选择obygine时，charityroomid=3将只出现在CB_ROOM2上，
但是当我选择peditatrial时，CB_ROOM2上不会出现charityroomid

我正在使用Netbeans和MYSQL

编辑：

import java.sql.*;<br>
import javax.swing.*;<br>
public class addBed extends javax.swing.JFrame {

    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pst = null;

    /**
     * Creates new form addBed
     */
    public addBed() {
        initComponents();
    }

    void loadcombo() {


            try {


            String charitysql = "SELECT wardName, charityRoomID FROM tbl_charityward, tbl_charityroom2 WHERE tbl_charityward.charityWardID = ?";  
            pst = conn.prepareStatement(charitysql);
            pst.setInt(1,tbl_charityroom2.charityWardID);
            rs = pst.executeQuery();

                while (rs.next()) {

                    cb_ward2.addItem(rs.getString(1));

                    cb_room2.addItem(rs.getString(2));                    

                }

            }
            catch(Exception e) {
                JOptionPane.showMessageDialog(null, e);
            }




    }
    private void formWindowOpened(java.awt.event.WindowEvent evt) {                                  
       conn = myconn.ConnectDb();
       loadcombo();
    }                                 

    private void btn_add2ActionPerformed(java.awt.event.ActionEvent evt) {                                         

    }     




Answer 1

尝试使用arrayList！！它会给你一种像推动一样的效果

类似这样的东西

    ArrayList<String> categories = new ArrayList<>();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        System.out.println("\ntrying connection");
        conn = DriverManager.getConnection("jdbc:mysql://localhost/products?user=root&password=");
        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT DISTINCT categorie FROM product");
        while (rs.next()) {
            categories.add(rs.getString(1));
        }
        for (String item : categories) {
            ComboBoxCategorie.addItem(item);
        }

    } catch (SQLException ex) {
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
            } // ignore
            stmt = null;
        }
    }

您可以忽略catch和finally子句！！关闭stmt即可

Answer 2

由于您的基本需求是将数据显示在两个组合框中，您可以这样做：

可以使用LoadCombo()加载带有数据库值的CB_WARD2组合框。

void loadcombo() {
    try
    {
     Connection conn=null;
 PreparedStatement pst=null;
 ResultSet rs=null;
// Your database connections 

     String charitysql = "SELECT wardName FROM tbl_charityward";
     pst = conn.prepareStatement(charitysql);
     rs = pst.executeQuery();
    while(rs.next()){                            
        cb_ward2.addItem(rs.getString(1));
    }
    con.close();
    }
    catch(Exception e)
    {
        System.out.println("Error"+e);
    }    
}

现在可以在CB_Ward2上使用ActionListener作为：

cb_ward2.addActionListener(new ActionListener(){


    public void actionPerformed(ActionEvent ae){
        try
    {
        Connection con=null;
        PreparedStatement pst=null,pst1=null;
        ResultSet rs=null,rs1=null;
    //your database connection
                Object name=cb_ward2.getSelectedItem();
                    pst=con.preparedStatement("select charityWardID from tbl_charityward where wardName=?");
                pst.setObject(1,name);
                    rs= pst.executeQuery();
        if(rs.next())
        {
                      pst1=con.preparedStatement("select charityRoomID from tbl_charityroom2 where charityWardID=?");
                      pst1.setInt(1,rs.getInt(1));
                      rs1=pst1.executeQuery();
                      while(rs1.next()){
                      cb_room2.addItem(rs1.getInt(1));
                      }
            }
                con.close();
    }
    catch(Exception e)
    {
        System.out.println("GG"+e);
    }

    }


    });

Answer 3

您正在使用的方式不是PreparedStatement的正确使用方式

这样做

String charitysql = "SELECT wardName, charityRoomID FROM tbl_charityward,tbl_charityroom2 
WHERE tbl_charityward.charityWardID = ?";
pst = conn.prepareStatement(charitysql);
pst.setInt(1,tbl_charityroom2.charityWardID);
rs = pst.executeQuery();