retrieving value from database in java

Question

Possible Duplicate:
retrieving values from database in java

I am making AGAIN another program that retrieves the inputted data/values of fields from the database I created. but this time, my inputted value will be coming from the JtextField I created. I wonder what's wrong in here bec when I'm running it the output is always null.

in this program i will convert the inputted value of my JTextField into int. here it is:

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]); }

in my second class it retrieves the inputted values of fields from the database I created here's the code: import java.sql.*;

public class Room {

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;
}

}

i already insert some values in my 3moronsdb which are ( 1, Classic , 103). here's my TEST main class:

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());
}
}

when i'm running it it only gives me null output for the Room class but i am getting the output of the GuestsInfo class which is 'Ericka'. Can you help me guys? I know I ask this kind of problem yesterday but i really don't know what's wrong in here now..

Answer 1

The select statement in this method looks wrong. Shouldn't it be selecting information from the Room table??

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);
    }
}

Instead of using the column indices to retrieval the values, you should be trying to use the column names. While probably very rare, it is possible that the the database may return columns in a different order then you are expecting in your code.

(This could happen for any number of reasons, the database is updated, the database is re-created, the DB engine is changes it mind... :P)

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

Now, obviously, I know nothing about your database structure, so you'll need to update the column names appropriately. This would highlight when you make a mistake and select from the wrong table...

Also. You should be releasing your database resources when they are no longer needed

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){
    }
}

Otherwise you run the risk of running out of database resources :P

I also wouldn't be creating a Connection per class. I'd either create a single connection for the application or use a connection pool of some kind