CODEWITHSUNDEEP
androidsqllistjdbc
Heartcroft
Heartcroft

Reputation: 1712

Filling an object list from MS SQL Server

I've got this user class (this is a reduced version for the example, the real one has more parameters but they are implemented the same way):

public class User {

    private int _userID;
    private String _fullName;

    public User(){

    }

    public User(int userID, String fullName){
        this._userID = userID;

        this._fullName = fullName;
    }

    int getUserID(){
        return this._userID;
    }

    String getFullName(){
        return this._fullName;
    }
    void setUserID(int userID){
        this._userID = userID;
    }

    void setFullName(String fullName){
        this._fullName = fullName;
    }
}

And I want to retrieve a list from my MS SQL Server of this type of objects in Android, I'm using this method inside the connector helper class (the class in charge to make connection to the server using JDBC):

public List<User> getUsers(int ID){

        java.sql.ResultSet result = null;
        List<User> users = new ArrayList<User>();
        User user = new User();
        try {
             connection = this.getConnection();
             if (connection != null) {

                    //QUERY
                    String statement = "SELECT * Users WHERE groupID = " 
                                    + ID;
                    Statement select = connection.createStatement();

                    //Calls Query
                    result = select.executeQuery(statement);
                    while (result.next()){
                        user.setUserID(result.getInt("UserID"));
                        user.setFullName(result.getString("FullName"));
                        System.out.println(result.getString("FullName"));

                        //Adds to the list
                        users.add(user);

                    }


                    result.close();
                    result = null;
                    closeConnection();
             } 
             else {
                    System.out.println("Error: No active Connection");
             }
         }  catch (Exception e) {
             e.printStackTrace();
            }

        return users;
    }

The data is retrieved well from the server according to the System.out.println I'm using in every iteration of the while, the problem is that the list is always filled with repeated information about the last user I retrieve, to clarify:

If I got users A, B and C, when I read user A, list has this structure:[A], when I read user B, list is:[B,B], when I read user C: [C,C,C], etc. So basically all the objects in the list are being overwritten by the last one read.

I've been struggling with this for hours, hope someone can spot the problem because I can't, thanks in advance for the help.

Upvotes: 0

Views: 229

Answers (1)

JB Nizet
JB Nizet

Reputation: 691913

You instantiate a single User object before your loop, and then modify the same User object at each iteration. So you end up with N times the same User object added to your list. You must recreate a new User at each iteration:

while (result.next()){
    User user = new User();
    user.setUserID(result.getInt("UserID"));
    ...

Upvotes: 1

Related Questions