getting specific row values of database table into an arraylist

Question

I am trying to get certain row records from a db_table into an array list. though this has been done many times, mine is a bit more different. suppose I have a table in my database like this below:

    +---------+--------+--------+
    |  NAME   | BILL   | CONTACT|
    +---------+--------+--------+
    |  james  |   400  | 024669 |            
    |  Mavis  |   700  | 025550 |              
    |  john   |   650  | 029510 |         
    |  bendo  |   340  | 023579 |
    +---------+--------+--------+

and I want an array to display records like this:

[james,400,024669]
[Mavis,700, 025550]
[John,650, 029510]
[bendoo,340,023579]

please how do I achieve that. the code I wrote gave me only a last array. and its:

    ArrayList<String> inner = null;

    try{
    String sql="select Name,Score,Contact from members";
    pst=conn.prepareStatement(sql);
    rs=pst.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnsNumber = rsmd.getColumnCount();
    while (rs.next()) {
    inner = new ArrayList<>(); 
    for(int i=1; i<=columnsNumber; i++){
    inner.add(rs.getString(i));
    }    

}
        System.out.println(inner);
    }catch(Exception e){
}

but this is the only System.out.PrintIn displayed:

[bendoo,340,023579]
BUILD SUCCESSFUL (total time: 2 minutes 13 seconds)

please how do I get this below as my result:

[james,400,024669]
[Mavis,700, 025550]
[John,650, 029510]
[bendoo,340,023579]

Rodney Nart · Accepted Answer · 2018-05-23 19:31:10Z

1

You need to add inner to a global list. Otherwise you lose the row value every time, and keep only the last one.

Your code should be changed to something like:

ArrayList<String> inner = null;

try {
  String sql = "select Name, Score, Contact from members";
  pst = conn.prepareStatement(sql);
  rs = pst.executeQuery();
  List<List<String>> allRows = new ArrayList<List<String>>(); // add this!

  while (rs.next()) {
    inner = new ArrayList<>(); 
    for(int i=1; i<=columnsNumber; i++){
      inner.add(rs.getString(i));
    }
    System.out.println("ROW: "+row); // Add this line.
    allRows.add(inner); // add this line!
  }   

  for (List<String> row : allRows) {
    System.out.println(row);
  }
} catch (SQLException e) {
  e.printStackTrace();
}

please this is what I have fully so far..

 ArrayList<String> inner = null;

try {
  String sql = "select Name,Score, Contact from members";
  pst = conn.prepareStatement(sql);
  rs = pst.executeQuery();
  ResultSetMetaData rsmd = rs.getMetaData();
  int columnsNumber = rsmd.getColumnCount();
  List<List<String>> allRows = new ArrayList<List<String>>(); // add this!

  while (rs.next()) {
    inner = new ArrayList<>(); 
    for(int i=1; i<=columnsNumber; i++){
      inner.add(rs.getString(i));
    }
    allRows.add(inner); // add this line!
  }   

  for (List<String> row : allRows) {
    System.out.println(row);
  }
} catch (SQLException e) {
}
    try{
    // initialise SMS object. 
    ZenophSMS sms = new ZenophSMS(); 
    sms.setUser("[email protected]"); 
    sms.setPassword("xxxxxxx");
    sms.authenticate();
    // set message parameters.
    sms.setMessage("Hello {$name}, your score is  {$score} . please come for you money ");  
    sms.setSenderId("NICE ONE"); 
    sms.setMessageType(MSGTYPE.TEXT);

    // add destinations. 
    for ( ArrayList<String> recpt : allRows) sms.addRecipient(recpt.get(2), new String[] {recpt.get(0), recpt.get(1)}, false);

    // submit the message. Since the total number of destinations is 
    // less than 400, the submit status of the destinations will be returned. 
    List<String[]> resp = sms.submit();

    // show the status 
    for (String[] dest : resp) 
    { REQSTATUS st = REQSTATUS.fromInt(Integer.parseInt(dest[0]));
    if (null != st) 
        switch (st) {
            case SUCCESS:
             System.out.println("Submitted: %s"+ dest[1]);
                break;
            case ERR_INSUFF_CREDIT:
               System.out.println("Insufficient Credits: %s"+dest[1]);
                break;
            default:
             System.out.println("Failed: %s"+ dest[1]);
                break;
        }
}

    }
catch (SMSException ex) { System.out.println("Error: " + ex.getMessage()); }
catch (Exception ex) { System.out.println("Error: " + ex.getMessage()); }

this line cant find the array 'allRows'. please what do I do.its giving me an error message that variable cannot be found.

 for ( ArrayList<String> recpt : allRows) sms.addRecipient(recpt.get(2),new String[] {recpt.get(0), recpt.get(1)}, false);

edited May 23, 2018 at 19:31

Rodney Nart

12112 bronze badges

answered May 23, 2018 at 18:45

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

The Impaler Over a year ago

I added an extra System.out.println(). Do you see all four rows displayed now? If you don't, then your database data is bad.

Rodney Nart Over a year ago

I see it bro..thank you but I need additional help please. am using this with an sms api to send personalized messages. and unfortunately they array'allRows' doesn't seem recorgnized by sms 'send method'. will display the sample

Rodney Nart Over a year ago

ok I have it solved. thanks very much @The_Impaler. just a little slight mistake from my side. two try and catch blocks caused the problem. thank you all once again

MikeS · Accepted Answer · 2018-05-23 18:45:01Z

1

Move "inner = new ArrayList<>();" before the "while" statement. You are reinitializing the arraylist for every row.

answered May 23, 2018 at 18:45

MikeS

1,7641 gold badge9 silver badges13 bronze badges

Collectives™ on Stack Overflow

getting specific row values of database table into an arraylist

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related