CODEWITHSUNDEEP
javaresultset
Alex2330
Alex2330

Reputation: 347

java convert resultset to a list

I want to convert my Resultset to List of object . This is my query:

 String querystring1= "SELECT userID, privilege"
               + "FROM dbo.User order by userID, privilege";


 userID  privilege
 ------------------
 1001    read 
 1001   write
 1001   execute 
 1001   delete
 1006    execute 
 1006    read
 1006    write
 1007   read
 1007    write

I have a class User defined like :

public class User {
   private int userID;
   private List<String> userPrivelege;
}

I want to have as an output to a list of Users, and this is my implemented code:

   String previousId = null;  
   List<String> PrivList= new ArrayList<String>();
   List<User> allUserList= new ArrayList<User>();

   while(result_set.next()) {
      String userID = result_set.getString("userID");
      String privilege = result_set.getString("privilege");
      if (previousId == null) { // first time
         User user = new User();
         PrivList.add(privilege);
         previousId=userID;
      } else if (previousId.equals(userID) {
         PrivList.add(privilege);
      } else {
         user.setUserPrivilege(PrivList);
         allUserList.add(user);
         PrivList.clear();
         previousId=null;
      }
   }

The problem is, other than the first user object created, all the next one are always missing the first value which means user 1006 will have 2 privileges other than 3. Any idea?

Upvotes: 1

Views: 6562

Answers (5)

Anonymous
Anonymous

Reputation: 86148

  • As far as I can see, you are never assigning the user ID to the User object?
  • PrivList.clear(); clears the list that you have just assigned to a user. Instead create a new list, as davidxxx also said.
  • As anchreg said, after you have created a user and added it to the list, you need to initialize the next user in the same way as you did the first time.
  • After your loop terminates, if previousId is not null (that is, any user previlege was processed at all), you need to assign values to the last user and add it to the list in the same way as in the last else case in the loop.

All of this said a more elegant solution could be coded using streams.

Upvotes: 2

Alberto
Alberto

Reputation: 795

In m y opinion, I think that is not a good model. You should have a second table with privileges related with an user. But to solve your question:

List<String> privList= new ArrayList<String>();
Map<Integer, List<String>> hmUserPrivileges = HashMap<Integer, String>();

while(result_set.next()) {
    int userID = result_set.getInt("userID");
    String privilege = result_set.getString("privilege");

    if (!hmUserPrivileges.contains(userID)) {
        privList= new ArrayList<String>();
    }

    privList.add(privilege);
    hmUserPrivileges.put(userID, privList);
}

List<User> allUserList = new ArrayList<User>();
Iterator<Entry<Integer, List<String>>> iterator = hmUserPrivileges.entrySet().iterator();
while(iterator.hasNext()) {
    Entry<Integer, List<String>> entry = iterator.next();
    User user = new User()

    user.setUserID(entry.getKey());
    user.setUserPrivelege(entry.getValue());

    allUserList.add(user);
}

Upvotes: 1

Antho Christen
Antho Christen

Reputation: 1329

That's because You Miss the First privilege of the New User here,

  else {
             user.setUserPrivilege(PrivList);
             allUserList.add(user);
             PrivList.clear();
             previousId=null;
    }

Do the privilege initialization for the next user here.

PrivList = new ArrayList<>(); PrivList.add(privilege);

Upvotes: 2

Laurent B
Laurent B

Reputation: 2220

You need to create a new instance of PrivList for each user.

Additionnaly, you need to add a privilege for the next user, otherwise you lose that information on the loop.

Edits shown by the <-- comments.

while(result_set.next()) {
    String userID = result_set.getString("userID");
    String privilege = result_set.getString("privilege");
    if (previousId == null) { // first time
        User user = new User();
        PrivList.add(privilege);
        previousId=userID;
    } else if (previousId.equals(userID) {
        PrivList.add(privilege);
    } else { 
        // nex user
        user.setUserPrivilege(PrivList);
        allUserList.add(user);
        PrivList = new ArrayList<>();  // <--
        PrivList.add(privilege);       // <--
        previousId=null;
    }
}

Upvotes: 1

davidxxx
davidxxx

Reputation: 131326

All User objects refer the same object List of privileges as you don't create a new instance for each User. Instead you clear only the List.
Consequently, all Users are set with 2 privileges as the last User that you handle has 2 privileges.

So replace :

 PrivList.clear();

by :

 PrivList = new ArrayList<>();

Upvotes: 2

Related Questions