Java 选择 Query 并将出现在 Netbeans & mysql 中的组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21519541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select Query and will appear to comboBox in Netbeans & mysql
提问by Rima Touya
I got problem on this code: String charitysql = "SELECT wardName, charityRoomID FROM tbl_charityward,tbl_charityroom2
WHERE tbl_charityward.charityWardID = tbl_charityroom2.charityWardID";
我在这段代码上遇到了问题： 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);
}
this is my tables structure:tbl_charityward
charityWardID int NOT NULL AUTO_INCREMENT,
wardName varchar(20),
status varchar(20),
PRIMARY KEY (charityWardID)
这是我的表结构：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,
status varchar(20),
charityWardID int,
PRIMARY KEY (charityRoomID, charityWardID),
FOREIGN KEY (charityWardID) REFERENCES tbl_charityward (charityWardID)
tbl_charityRoom2
charityRoomID INT NOT NULL AUTO_INCREMENT,
status varchar(20),
charityWardID int,
PRIMARY KEY (charityRoomID, charityWardID),
FOREIGN KEY (charityWardID) REFERENCES tbl_charityward (charityWardID)
this is my tables with values:tbl_charityward
+-------------+----------+--------+
|charityWardID| wardName | status |
+-------------+----------+--------+
|......1......| Surgical |..Open..|
|......2......| .Obygine |..Open..|
|......3......| Pediatric|..Open..|
+-------------+----------+--------+
这是我的值表：tbl_charityward
+-------------+----------+--------+
|charityWardID| wardName | status |
+-------------+----------+--------+
|......1......| Surgical |..Open..|
|......2......| .Obygine |..Open..|
|......3......| Pediatric|..Open..|
+-------------+----------+--------+
tbl_charityroom2
+-------------+--------+-------------+
|charityRoomID| status |charityWardID|
+-------------+--------+-------------+
|......1......|..Open..|......1......|
|......2......|..Open..|......1......|
|......3......|..Open..|......2......|
+-------------+--------+-------------+
tbl_charityroom2
+-------------+--------+-------------+
|charityRoomID| status |charityWardID|
+-------------+--------+-------------+
|......1......|..Open..|......1......|
|......2......|..Open..|......1......|
|......3......|..Open..|......2......|
+-------------+--------+-------------+
I have 2 comboBox:cb_ward2 = which contains wardNamecb_room2 = which contains CharityRoomID
我有 2 个组合框：cb_ward2 = which contains wardNamecb_room2 = which contains CharityRoomID
If I select a wardName from cb_ward2, then the cb_room2will show the corresponding charityRoomID.
如果我从 中选择一个病房名称cb_ward2，cb_room2则将显示相应的charityRoomID。
example:
I select Surgicaland the charityRoomID = 1, 2 will appear on cb_room2,
and when i select Obygine, the charityRoomID = 3 will only appear on cb_room2,
but when i select Pediatric, no charityRoomIDwill appear on cb_room2
例如：
我选择Surgical和charityRoomID = 1, 2 将出现在cb_room2，
当我选择Obygine，则charityRoomID = 3 只会出现在cb_room2，
但是当我选择Pediatric，没有charityRoomIDwill appear on cb_room2
I am using Netbeansand MYSQL
EDIT :
I am using Netbeansand MYSQL
EDIT :
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) {
}
回答by SpringLearner
The way you are using is not the proper way of using PreparedStatement
您使用的方式不是使用 PreparedStatement 的正确方式
Do like this
这样做
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();
回答by ravibagul91
As your basic requirement is to show data into two ComboBoxes, you can do it as:
由于您的基本要求是将数据显示为两个ComboBoxes，您可以这样做：
You can use a loadcombo()to load your cb_ward2combobox with database values.
您可以使用 aloadcombo()加载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);
}
}
Now you can use ActionListeneron cb_ward2as:
现在您可以将ActionListeneroncb_ward2用作：
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);
}
}
});
回答by Igzo
try using an arrayList !! it will give you a push like effect
尝试使用 arrayList ！！它会给你一个推动般的效果
something like this
像这样的东西
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;
}
}
you can ignore the catch and finally clause!! just close the stmt
你可以忽略 catch 和 finally 子句！！只需关闭 stmt
