Java, looping through result set

Question

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)};
String actuate[] = {rs4.getString(2)};
asString = Arrays.toString(actuate);

Answer 1

Modern Java

Other Answers here are good, but Java has evolved since they were posted. Let's address those improvements with some example code.

Your example code and data do not align. So I must invent another scenario.

id_	species_	name_
1	cat	Fluffy
2	dog	Rover
3	cat	Spot
4	cat	Tuna

Text blocks

Java now has text blocks. This new syntax for string literals makes embedding SQL code easier. No more clumsy string concatenation.

Also you omitted the statement terminator in your SQL. I suggest always including the semicolon to avoid problems.

String sql = """
        SELECT species_, COUNT(*)
        FROM animal_ 
        GROUP BY species_
        ;
        """ ;

Try-with-resources

As others suggested, use a PreparedStatement to avoid SQL Injection security attacks. Not relevant here, as you have no user input to protect against.

Nowadays PreparedStatement, and Statement, are AutoCloseable. So you can use try-with-resources syntax to automatically close them after use. This simplifies your JDBC coding.

Ditto for Connection objects.

try 
(
    Connection connection = myDataSource.getConnection() ;
    Statement statement = connection.createStatement() ;
    ResultSet resultSet = statement.executeQuery ( sql ) ;
)
{
    while ( resultSet.next() ) 
    {
        String species = resultSet.getString( 1 ) ;
        int count = resultSet.getInt( 2 ) ;
        System.out.println( species + " = " + count ) ;
    }
} 
catch ( SQLException e )
{
    …
}

dog = 1

cat = 3

Records

You may want to define a class to hold the data returned by the result set.

If the intent of your custom class will be the transparent communication of immutable data, then define your class as a record.

In a record, the constructor, getters, equals & hashCode, and toString are created implicitly by the compiler.

Records are very convenient this kind of JDBC database work because a record can be defined locally within a method, as well as the usual ways of nested within a class or as a standalone class. (By the way, enums and interfaces can also now be local.)

record SpeciesCount ( String species , int count ) {}

Streams

Streams make easy work of looping through a collection. In our case, we can easily report our resulting collection of SpeciesCount objects.

results.stream().forEach( … ) ;

Or more simply:

results.forEach( … ) ;

We can use a method reference with our stream for compact syntax, such as System.out :: println.

results.forEach( System.out :: println ) ;

SequencedCollection

We can sort the results with an ORDER BY added to our SQL query.

In Java 21+, a new super-interface above List and NavigableSet/SortedSet etc. has been added: SequencedCollection. See JEP 431: Sequenced Collections.

Final example code

String sql = """
        SELECT species_, COUNT(*)
        FROM animal_ 
        GROUP BY species_
        ORDER BY species_
        ;
        """ ;

record SpeciesCount ( String species , int count ) {}
SequencedCollection < SpeciesCount > results = new ArrayList<>() ;
try 
(
    Connection connection = myDataSource.getConnection() ;
    Statement statement = connection.createStatement() ;
    ResultSet resultSet = statement.executeQuery ( sql ) ;
)
{
    while ( resultSet.next() ) 
    {
        String species = resultSet.getString( 1 ) ;
        int count = resultSet.getInt( 2 ) ;
        SpeciesCount result = new SpeciesCount( species , count ) ;
        results.add( result ) ;
    }
} 
catch ( SQLException e )
{
    …
}

results.forEach( System.out :: println ) ;  // Dump results to console. 

SpeciesCount[species=cat, count=3]

SpeciesCount[species=dog, count=1]

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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 :)