Java, looping through result set

In Java, I have a query like this:

String querystring1= "SELECT rlink_id, COUNT(*)"
                   + "FROM dbo.Locate  "
                   + "GROUP BY rlink_id ";

The table rlink_id has this data:

Sid        lid
 3           2
 4           4
 7           3
 9           1

How do I extract these values with a Java ResultSet?

Here is what I have so far:

String show[] = {rs4.getString(1)};
St开发者_开发技巧ring actuate[] = {rs4.getString(2)};
asString = Arrays.toString(actuate);

List<String> sids = new ArrayList<String>();
List<String> lids = new ArrayList<String>();

String query = "SELECT rlink_id, COUNT(*)"
             + "FROM dbo.Locate  "
             + "GROUP BY rlink_id ";

Statement stmt = yourconnection.createStatement();
try {
    ResultSet rs4 = stmt.executeQuery(query);

    while (rs4.next()) {
        sids.add(rs4.getString(1));
        lids.add(rs4.getString(2));
    }
} finally {
    stmt.close();
}

String show[] = sids.toArray(sids.size());
String actuate[] = lids.toArray(lids.size());

Result Set are actually contains multiple rows of data, and use a cursor to point out current position. So in your case, rs4.getString(1) only get you the data in first column of first row. In order to change to next row, you need to call next()

a quick example

while (rs.next()) {
    String sid = rs.getString(1);
    String lid = rs.getString(2);
    // Do whatever you want to do with these 2 values
}

there are many useful method in ResultSet, you should take a look :)

• The ResultSet should be closed.
• PreparedStatement might protect agains SQL injection.
• The PreparedStatement should be eventually closed.

So the snippet might look like this:

PreparedStatement ps = connection.prepareStatement(sql);
try(ResultSet rs = ps.executeQuery()){
    while(rs.next()) {
        String val = rs.getString(1);
    }
}

The problem with your code is :

     String  show[]= {rs4.getString(1)};
     String actuate[]={rs4.getString(2)};

This will create a new array every time your loop (an not append as you might be assuming) and hence in the end you will have only one element per array.

Here is one more way to solve this :

    StringBuilder sids = new StringBuilder ();
    StringBuilder lids = new StringBuilder ();

    while (rs4.next()) {
        sids.append(rs4.getString(1)).append(" ");
        lids.append(rs4.getString(2)).append(" ");
    }

    String show[] = sids.toString().split(" "); 
    String actuate[] = lids.toString().split(" ");

These arrays will have all the required element.