java - java中从数据库中获取值

Question

这个问题已经有答案了:

已关闭 10 年前。

Possible Duplicate:
retrieving values from database in java

我正在制作另一个程序，从我创建的数据库中检索输入的数据/字段值。但这一次，我输入的值将来 self 创建的 JtextField。我想知道这里出了什么问题，因为当我运行它时，输出始终为空。

在此程序中，我会将 JTextField 的输入值转换为 int。这是:

public class ButtonHandler implements ActionListener
{
public void actionPerformed(ActionEvent e)
{
if(e.getSource() == extendB)
{
    ExtensionForm extend = new ExtensionForm();
    extend.setVisible(true);
    }
else if(e.getSource()== searchB)            
{
//get text from the textField 
String guest = guestIDTF.getText();
//parse the string to integer for retrieving of data
int id = Integer.parseInt(guest);
GuestsInfo guestInfo = new GuestsInfo(id);
Room roomInfo = new Room(id);
String labels[] = {guestInfo.getFirstName()+" "+guestInfo.getLastName(),""+roomInfo.getRoomNo(),roomInfo.getRoomType(),guestInfo.getTime(),"11:00"}; for(int z = 0; z<labels.length; z++) { labelDisplay[z].setText(labels[z]); }

在我的第二个类中，它从我创建的数据库中检索输入的字段值这是代码: 导入java.sql.*;

公开课室{

private String roomType;
private int  guestID, roomNo;

private Connection con;
private PreparedStatement statement;

public Room(){
    try {
         Class.forName("com.mysql.jdbc.Driver");
         con = DriverManager.getConnection(
                      "jdbc:mysql://localhost:3306/3moronsdb","root","");           
    } 
    catch (Exception e) {
         e.printStackTrace();
    }
}
public Room(int guestsID)
{
    this();
    try{
        statement = con.prepareStatement("SELECT * FROM guest WHERE guestID=?");
        statement.setInt(1, guestID);
        ResultSet rs = statement.executeQuery();
        while(rs.next()){
            this.guestID = rs.getInt(1);
            this.roomType = rs.getString(2);
            this.roomNo = rs.getInt(3);
        }
    }catch(Exception e){
        System.out.print(e);
    }
}
//Constructor for setting rate
public Room(String roomType, int roomNo)
{
    this();
    try
    {   
        statement = con.prepareStatement("Insert into room(roomType, roomNo) values(?,?)");
            statement.setString(1, roomType);
            statement.setInt(2, roomNo);
            statement.executeUpdate();
    }
    catch(Exception e)
    {
        e.printStackTrace();        
        return;
    }
}
//getting roomType
public String getRoomType(){
    return roomType;
}
//getting roomNo
public int getRoomNo(){
    return roomNo; 
}
//getting guestID
public int getGuestId(){
    return guestID;
}

}

我已经在我的 3moronsdb 中插入了一些值(1、Classic、103)。这是我的测试主类:

public class TestMain {
    public static void main(String [] a){

        GuestsInfo guest = new GuestsInfo(1); //note that this instantiation is the other class which i just
 ask the other day.. (https://stackoverflow.com/questions/12762835/retrieving-values-from-database-in-java)   
        Room rum = new Room(1);
    System.out.print(rum.getRoomType()+"  "+ guest.getFirstName());
}
}

当我运行它时，它只给我 Room 类的空输出，但我得到了 GuestInfo 类的输出，即“Ericka”。你们能帮我吗？我知道我昨天问了这类问题，但我现在真的不知道这里出了什么问题..

Answer 1

此方法中的 select 语句看起来错误。难道不应该从 Room 表中选择信息吗？

public Room(int guestsID)
{
    this();
    try{
        // This line looks wrong, shouldn't this be selecting from the
        // room table??
        // statement = con.prepareStatement("SELECT * FROM room WHERE guestID=?");
        // instead???
        statement = con.prepareStatement("SELECT * FROM guest WHERE guestID=?");
        statement.setInt(1, guestID);
        ResultSet rs = statement.executeQuery();
        while(rs.next()){
            this.guestID = rs.getInt(1);
            this.roomType = rs.getString(2);
            this.roomNo = rs.getInt(3);
        }
    }catch(Exception e){
        System.out.print(e);
    }
}

您应该尝试使用列名称，而不是使用列索引来检索值。虽然可能非常罕见，但数据库可能会以与您在代码中期望的顺序不同的顺序返回列。

(发生这种情况的原因有多种，数据库已更新，数据库已重新创建，数据库引擎改变了主意......:P)

while(rs.next()){
    this.guestID = rs.getInt("guestID");
    this.roomType = rs.getString("roomType");
    this.roomNo = rs.getInt("roomNumber");
}

现在，显然，我对您的数据库结构一无所知，因此您需要适本地更新列名称。当您犯错误并从错误的表中选择时，这会突出显示...

还有。当不再需要数据库资源时，您应该释放它们

PreparedStatement statement = null;
try{
    statement = con.prepareStatement("SELECT * FROM guest WHERE guestID=?");
    statement.setInt(1, guestID);
    ResultSet rs = statement.executeQuery();
    while(rs.next()){
        this.guestID = rs.getInt(1);
        this.roomType = rs.getString(2);
        this.roomNo = rs.getInt(3);
    }
}catch(Exception e){
    System.out.print(e);
} finally {
    try {
        statement.close();
    }catch(Exception e){
    }
}

否则您将面临数据库资源耗尽的风险:P

我也不会为每个类创建一个Connection。我要么为应用程序创建单个连接，要么使用某种连接池